외부에서 파티션을 나눈 데이터를 쿼리하려면 BigLake 테이블 또는 외부 테이블을 만들어야 합니다.
BigLake 테이블을 사용하면 테이블 수준에서 세분화된 보안을 시행할 수 있으므로 이를 사용하는 것이 좋습니다.
BigLake 및 외부 테이블에 대한 자세한 내용은 BigLake 테이블 소개 및 외부 테이블 소개를 참조하세요.
테이블 정의 파일에서 적절한 옵션을 설정하여 Hive 파티션 나누기 지원을 사용 설정할 수 있습니다.
관리되는 파티션을 나눈 테이블 쿼리에 대한 상세 안내는 파티션을 나눈 테이블 소개를 참조하세요.
하이브 파티션 키는 Cloud Storage에서 데이터를 쿼리할 때 일반 열로 표시됩니다.
데이터는 기본 Hive 파티션 나누기 레이아웃을 따라야 합니다.
예를 들어 다음 파일의 키-값 쌍은 기본 레이아웃을 따라 등호 부호(=)를 구분자로 사용하는 디렉터리로 구성되어 있으며, 파티션 키의 순서는 항상 동일합니다.
CUSTOM 스키마를 사용하려면 소스 URI 프리픽스 필드에 스키마를 지정해야 합니다. CUSTOM 스키마를 사용하면 각 파티션 키의 유형을 지정할 수 있습니다.
값은 지정된 유형에 유효하도록 파싱해야 하며, 그렇지 않으면 쿼리가 실패합니다.
예를 들어 source_uri_prefix 플래그를 gs://my_bucket/my_table/{dt:DATE}/{val:STRING}로 설정하면 BigQuery가 val을 STRING으로, dt를 DATE로 처리하고, gs://my_bucket/my_table을 일치하는 파일의 소스 URI 프리픽스로 사용합니다.
파티션 프루닝
BigQuery는 가능한 경우 파티션 키의 쿼리 조건자를 사용하여 파티션을 프루닝합니다. 이는 BigQuery가 불필요한 파일을 읽는 것을 방지하고 성능을 향상하는 데 도움이 됩니다.
쿼리에서 파티션 키의 조건자 필터
외부에서 파티션을 나눈 테이블을 만들 때 HivePartitioningOptions에서 requirePartitionFilter 옵션을 사용 설정하여 파티션 키에 조건부 필터를 사용하도록 요구할 수 있습니다.
이 옵션이 사용 설정된 경우 WHERE 절을 지정하지 않고 외부에서 파티션을 나눈 테이블을 쿼리하려면 Cannot query over table <table_name> without a filter over column(s)
<partition key names> that can be used for partition elimination과 같은 오류가 발생합니다.
제한사항
하이브 파티션 나누기 지원은 모든 URI에 대해 파티션 인코딩 바로 앞에서 끝나는 공통의 소스 URI 프리픽스를 가정하여 구축됩니다(예: gs://BUCKET/PATH_TO_TABLE/).
파티션을 나눈 Hive 테이블의 디렉터리 구조는 동일한 파티션 나누기 키가 동일한 순서로 표시된다고 가정하며, 이때 파티션 키는 테이블당 최대 10개입니다.
[[["이해하기 쉬움","easyToUnderstand","thumb-up"],["문제가 해결됨","solvedMyProblem","thumb-up"],["기타","otherUp","thumb-up"]],[["이해하기 어려움","hardToUnderstand","thumb-down"],["잘못된 정보 또는 샘플 코드","incorrectInformationOrSampleCode","thumb-down"],["필요한 정보/샘플이 없음","missingTheInformationSamplesINeed","thumb-down"],["번역 문제","translationIssue","thumb-down"],["기타","otherDown","thumb-down"]],["최종 업데이트: 2025-09-04(UTC)"],[[["\u003cp\u003eBigQuery enables querying partitioned data from Cloud Storage, Amazon S3, and Azure Blob Storage using external tables or BigLake tables, with the latter recommended for enhanced security.\u003c/p\u003e\n"],["\u003cp\u003ePartitioned data must adhere to the default Hive partitioning layout, where key-value pairs are encoded as directories with an equal sign separator, and support file formats such as Avro, CSV, JSON, ORC, and Parquet.\u003c/p\u003e\n"],["\u003cp\u003eBigQuery offers three modes for Hive partition schema detection: \u003ccode\u003eAUTO\u003c/code\u003e for automatic key name and type detection, \u003ccode\u003eSTRINGS\u003c/code\u003e for string conversion, and \u003ccode\u003eCUSTOM\u003c/code\u003e for user-specified schemas.\u003c/p\u003e\n"],["\u003cp\u003ePredicate filters on partition keys can be used to enhance query performance by allowing BigQuery to prune partitions and avoid reading unnecessary files.\u003c/p\u003e\n"],["\u003cp\u003eEnabling the \u003ccode\u003erequirePartitionFilter\u003c/code\u003e option ensures that queries on externally partitioned tables include a filter on partition keys, although certain \u003ccode\u003eWHERE\u003c/code\u003e clause requirements need to be respected for partition elimination to occur.\u003c/p\u003e\n"]]],[],null,["# Use externally partitioned data\n===============================\n\nYou can use BigQuery external tables to query partitioned data in\nthe following data stores:\n\n- [Cloud Storage](/bigquery/docs/create-cloud-storage-table-biglake#create-biglake-partitioned-data)\n- [Amazon Simple Storage Service (Amazon S3)](/bigquery/docs/omni-aws-create-external-table#create-biglake-table-partitioned)\n- [Azure Blob Storage](/bigquery/docs/omni-azure-create-external-table#create-biglake-table-partitioned)\n\nThe external partitioned data must use a [default Hive partitioning layout](#supported_data_layouts)\nand be in one of the following formats:\n\n- Avro\n- CSV\n- JSON\n- ORC\n- Parquet\n\nTo query externally partitioned data,\nyou must create a\n[BigLake table](/bigquery/docs/create-cloud-storage-table-biglake)\nor an\n[external table](/bigquery/docs/external-data-cloud-storage#create-external-table-partitioned).\nWe recommend using BigLake tables because they let you enforce\nfine-grained security at the table level.\nFor information about BigLake and external tables, see\n[Introduction to BigLake tables](/bigquery/docs/biglake-intro)\nand [Introduction to external tables](/bigquery/docs/external-tables).\n\nYou enable Hive partitioning support by setting the appropriate\noptions in the [table definition file](/bigquery/docs/external-table-definition#create_a_definition_file_for_hive-partitioned_data).\nFor instructions about querying managed partitioned tables, see\n[Introduction to partitioned tables](/bigquery/docs/partitioned-tables).\n\nPartition schema\n----------------\n\nThe following sections explain the [default Hive partitioned\nlayout](#supported_data_layouts) and the [schema detection modes](#detection_modes) that\nBigQuery supports.\n\nTo avoid reading unnecessary files and to improve performance, you can use\n[predicate filters on partition keys in queries](#partition_pruning).\n\n### Supported data layouts\n\nHive partition keys appear as normal columns when you query data from\nCloud Storage.\nThe data must follow a default Hive partitioned layout.\nFor example, the following files follow the default layout---the\nkey-value pairs are configured as directories with an equal sign (=) as a separator,\nand the partition keys are always in the same order: \n\n gs://my_bucket/my_table/dt=2019-10-31/lang=en/my_filename\n gs://my_bucket/my_table/dt=2018-10-31/lang=fr/my_filename\n\nThe common source URI prefix in this example is `gs://my_bucket/my_table`.\n\n### Unsupported data layouts\n\nIf the partition key names are not encoded in the directory path,\npartition schema detection fails. For example, consider the following path,\nwhich does not encode the partition key names: \n\n gs://my_bucket/my_table/2019-10-31/en/my_filename\n\nFiles where the schema is not in a consistent order also fail detection.\nFor example, consider the following two files with inverted partition\nkey encodings: \n\n gs://my_bucket/my_table/dt=2019-10-31/lang=en/my_filename\n gs://my_bucket/my_table/lang=fr/dt=2018-10-31/my_filename\n\n### Detection modes\n\nBigQuery supports three modes of Hive partition schema detection:\n\n- `AUTO`: Key names and types are automatically detected. The following types\n can be detected:\n\n - [STRING](/bigquery/docs/reference/standard-sql/data-types#string_type)\n - [INTEGER](/bigquery/docs/reference/standard-sql/data-types#integer_types)\n - [DATE](/bigquery/docs/reference/standard-sql/data-types#date_type)\n\n For example, `/date=2018-10-18/`.\n - [TIMESTAMP](/bigquery/docs/reference/standard-sql/data-types#timestamp_type)\n\n For example, `/time=2018-10-18 16:00:00+00/`.\n- `STRINGS`: Key names are automatically converted to `STRING` type.\n\n- `CUSTOM`: Partition key schema is encoded as specified in the source URI\n prefix.\n\n#### Custom partition key schema\n\nTo use a `CUSTOM` schema, you must specify the schema in the source URI prefix\nfield. Using a `CUSTOM` schema lets you specify the type for each partition key.\nThe values must validly parse as the specified type or the query fails.\n\nFor example, if you set the `source_uri_prefix` flag to\n`gs://my_bucket/my_table/{dt:DATE}/{val:STRING}`,\nBigQuery treats `val` as a STRING, `dt` as a DATE, and\nuses `gs://my_bucket/my_table` as the source URI prefix for the matched files.\n\n### Partition pruning\n\nBigQuery prunes partitions when possible using query predicates on\nthe partition keys. This lets BigQuery avoid reading\nunnecessary files, which helps improve performance.\n\n### Predicate filters on partition keys in queries\n\nWhen you create an externally partitioned table, you can require the use of\npredicate filters on partition keys by enabling the `requirePartitionFilter`\noption under\n[HivePartitioningOptions](/bigquery/docs/reference/rest/v2/tables#hivepartitioningoptions).\n\nWhen this option is enabled, attempts to query the externally partitioned table\nwithout specifying a `WHERE` clause produce the following error:\n`Cannot query over table \u003ctable_name\u003e without a filter over column(s)\n\u003cpartition key names\u003e that can be used for partition elimination`.\n| **Note:** There must be at least one predicate that\n| only references one or more partition keys for the filter to be considered\n| eligible for partition elimination. For example, for a table with partition key\n| `val` and column `f` in the file, both of the following\n| `WHERE` clauses satisfy the requirement:\n|\n| \u003cbr /\u003e\n|\n| `WHERE val = \"key\"`\n|\n| `WHERE val = \"key\" AND f = \"column\"`\n|\n| However, `WHERE (val = \"key\" OR f = \"column\")` is not sufficient.\n\nLimitations\n-----------\n\n- Hive partitioning support is built assuming a common source URI prefix for all URIs that ends immediately before partition encoding, as follows: `gs://`\u003cvar translate=\"no\"\u003eBUCKET\u003c/var\u003e`/`\u003cvar translate=\"no\"\u003ePATH_TO_TABLE\u003c/var\u003e`/`.\n- The directory structure of a Hive partitioned table is assumed to have the same partitioning keys appear in the same order, with a maximum of ten partition keys per table.\n- The data must follow a [default Hive partitioning layout](#supported_data_layouts).\n- The Hive partitioning keys and the columns in the underlying files cannot overlap.\n- Support is for\n [GoogleSQL](/bigquery/docs/reference/standard-sql/query-syntax) only.\n\n- All [limitations](/bigquery/external-data-sources#external_data_source_limitations)\n for querying external data sources stored on Cloud Storage apply.\n\nWhat's next\n-----------\n\n- Learn about [partitioned tables](/bigquery/docs/partitioned-tables).\n- Learn how to [use SQL in BigQuery](/bigquery/docs/introduction-sql)."]]
