INFORMATION_SCHEMA를 사용하여 테이블 메타데이터 가져오기

INFORMATION_SCHEMA는 데이터세트, 테이블, 뷰의 메타데이터에 액세스할 수 있는 일련의 뷰입니다.

INFORMATION_SCHEMA.TABLESINFORMATION_SCHEMA.TABLE_OPTIONS 뷰를 쿼리하여 프로젝트의 테이블 및 뷰에 대한 메타데이터를 검색할 수 있습니다. 또한 INFORMATION_SCHEMA.COLUMNSINFORMATION_SCHEMA.COLUMN_FIELD_PATHS 뷰를 쿼리하여 테이블의 열(필드)에 대한 메타데이터를 검색할 수 있습니다.

TABLESTABLE_OPTIONS 뷰에는 뷰에 대한 대략적인 정보도 포함되어 있습니다. 자세한 내용은 INFORMATION_SCHEMA.VIEWS 뷰를 대신 쿼리하세요.

필수 권한

테이블 정보를 가져오려면 데이터세트에 대한 READER 역할을 할당받거나 bigquery.tables.get 권한이 있는 프로젝트 수준의 IAM 역할을 할당받아야 합니다. 프로젝트 수준의 bigquery.tables.get 권한을 할당받으면 프로젝트의 모든 테이블 정보를 가져올 수 있습니다. 사전 정의된 프로젝트 수준의 모든 IAM 역할에는 bigquery.jobUserbigquery.user제외bigquery.tables.get 권한이 포함되어 있습니다.

또한 bigquery.user 역할을 할당받은 사용자에게는 bigquery.datasets.create 권한이 있습니다. 따라서 bigquery.user 역할을 할당받은 사용자는 자신이 생성한 모든 데이터세트의 테이블 정보를 가져올 수 있습니다. bigquery.user 역할이 할당된 사용자가 데이터세트를 만들면 이 사용자에게 데이터세트에 대한 OWNER 액세스 권한이 부여됩니다. 데이터세트에 대한 OWNER 액세스 권한이 있는 사용자는 해당 데이터세트와 데이터세트에 포함된 모든 테이블을 완벽하게 제어할 수 있습니다.

BigQuery의 IAM 역할과 권한에 대한 자세한 내용은 액세스 제어를 참조하세요. 데이터세트 수준 역할에 대한 자세한 내용은 데이터세트 기본 역할을 참조하세요.

TABLES

INFORMATION_SCHEMA.TABLES 뷰를 쿼리하면 쿼리 결과에 데이터세트의 각 테이블 또는 뷰에 하나의 행이 포함됩니다.

INFORMATION_SCHEMA.TABLES 뷰에 대한 쿼리에는 데이터세트 한정자가 있어야 합니다. 쿼리를 제출하는 사용자는 테이블이나 뷰가 포함된 데이터세트에 액세스할 수 있어야 합니다.

INFORMATION_SCHEMA.TABLES 뷰에는 다음과 같은 스키마가 있습니다.

열 이름 데이터 유형
TABLE_CATALOG STRING 데이터세트가 포함된 프로젝트 이름
TABLE_SCHEMA STRING 테이블이나 뷰가 포함된 데이터세트 이름(또는 datasetId라고 함)
TABLE_NAME STRING 테이블 또는 뷰의 이름(또는 tableId라고 함)
TABLE_TYPE STRING 테이블 유형:
IS_INSERTABLE_INTO STRING 테이블의 DML INSERT 문 지원 여부에 따라 YES 또는 NO
IS_TYPED STRING 값은 항상 NO
CREATION_TIME TIMESTAMP 테이블 생성 시간

데이터세트 속성에 대한 자세한 내용은 REST API 문서의 데이터세트 리소스 페이지를 참조하세요. 테이블 및 뷰 속성에 대한 자세한 내용은 REST API 문서의 테이블 리소스 페이지를 참조하세요.

예시 1:

추후 사용을 위해 예약된 is_typed를 제외하고 INFORMATION_SCHEMA.TABLES 뷰에서 모든 열을 검색합니다. 반환되는 메타데이터는 기본 프로젝트인 myprojectmydataset에 있는 모든 테이블의 메타데이터입니다.

mydataset에는 다음 테이블이 포함되어 있습니다.

  • mytable1: 표준 BigQuery 테이블
  • myview1: BigQuery 뷰

INFORMATION_SCHEMA.TABLES 뷰에 대한 쿼리에는 데이터세트 한정자가 있어야 합니다. 쿼리를 제출하는 사용자는 테이블이 포함된 데이터세트에 액세스할 수 있어야 합니다.

기본 프로젝트가 아닌 프로젝트에 쿼리를 실행하려면 데이터세트에 프로젝트 ID를 `[PROJECT_ID]`.[DATASET].INFORMATION_SCHEMA.[VIEW] 형식으로 추가합니다(예: `myproject`.mydataset.INFORMATION_SCHEMA.TABLES).

쿼리를 실행하려면 다음 단계를 따르세요.

Console

  1. GCP Console에서 BigQuery 웹 UI를 엽니다.

    BigQuery 웹 UI로 이동

  2. 쿼리 편집기 상자에 다음과 같은 표준 SQL 쿼리를 입력합니다. INFORMATION_SCHEMA에는 표준 SQL 구문이 필요합니다. 표준 SQL은 GCP Console의 기본 구문입니다.

    SELECT
     * EXCEPT(is_typed)
    FROM
     mydataset.INFORMATION_SCHEMA.TABLES
    
  3. 실행을 클릭합니다.

명령줄

query 명령어를 사용하고 --nouse_legacy_sql 또는 --use_legacy_sql=false 플래그를 사용하여 표준 SQL 구문을 지정합니다. INFORMATION_SCHEMA 쿼리에는 표준 SQL 구문이 필요합니다.

쿼리를 실행하려면 다음 명령어를 입력합니다.

bq query --nouse_legacy_sql \
'SELECT * EXCEPT(is_typed) FROM mydataset.INFORMATION_SCHEMA.TABLES'

결과는 다음과 같이 표시됩니다.

  +----------------+---------------+----------------+------------+--------------------+---------------------+
  | table_catalog  | table_schema  |   table_name   | table_type | is_insertable_into |    creation_time    |
  +----------------+---------------+----------------+------------+--------------------+---------------------+
  | myproject      | mydataset     | mytable1       | BASE TABLE | YES                | 2018-10-29 20:34:44 |
  | myproject      | mydataset     | myview1        | VIEW       | NO                 | 2018-12-29 00:19:20 |
  +----------------+---------------+----------------+------------+--------------------+---------------------+
  

예시 2:

다음 예시는 INFORMATION_SCHEMA.TABLES 뷰에서 BASE TABLE 유형의 모든 테이블을 검색합니다. is_typed 열은 제외됩니다. 반환되는 메타데이터는 기본 프로젝트인 myprojectmydataset에 있는 테이블의 메타데이터입니다.

INFORMATION_SCHEMA.TABLES 뷰에 대한 쿼리에는 데이터세트 한정자가 있어야 합니다. 쿼리를 제출하는 사용자는 테이블이 포함된 데이터세트에 액세스할 수 있어야 합니다.

기본 프로젝트가 아닌 프로젝트에 쿼리를 실행하려면 데이터세트에 프로젝트 ID를 `[PROJECT_ID]`.[DATASET].INFORMATION_SCHEMA.[VIEW] 형식으로 추가합니다(예: `myproject`.mydataset.INFORMATION_SCHEMA.TABLES).

쿼리를 실행하려면 다음 단계를 따르세요.

Console

  1. GCP Console에서 BigQuery 웹 UI를 엽니다.

    BigQuery 웹 UI로 이동

  2. 쿼리 편집기 상자에 다음과 같은 표준 SQL 쿼리를 입력합니다. INFORMATION_SCHEMA에는 표준 SQL 구문이 필요합니다. 표준 SQL은 GCP Console의 기본 구문입니다.

    SELECT
     * EXCEPT(is_typed)
    FROM
     mydataset.INFORMATION_SCHEMA.TABLES
    WHERE
     table_type="BASE TABLE"
    
  3. 실행을 클릭합니다.

명령줄

query 명령어를 사용하고 --nouse_legacy_sql 또는 --use_legacy_sql=false 플래그를 사용하여 표준 SQL 구문을 지정합니다. INFORMATION_SCHEMA 쿼리에는 표준 SQL 구문이 필요합니다.

쿼리를 실행하려면 다음 명령어를 입력합니다.

bq query --nouse_legacy_sql \
'SELECT * EXCEPT(is_typed) FROM mydataset.INFORMATION_SCHEMA.TABLES
WHERE table_type="BASE TABLE"'

결과는 다음과 같이 표시됩니다.

  +----------------+---------------+----------------+------------+--------------------+---------------------+
  | table_catalog  | table_schema  |   table_name   | table_type | is_insertable_into |    creation_time    |
  +----------------+---------------+----------------+------------+--------------------+---------------------+
  | myproject      | mydataset     | mytable1       | BASE TABLE | NO                 | 2018-10-31 22:40:05 |
  +----------------+---------------+----------------+------------+--------------------+---------------------+
  

TABLE_OPTIONS

INFORMATION_SCHEMA.TABLE_OPTIONS 뷰를 쿼리하면 쿼리 결과에 데이터세트의 각 테이블 또는 뷰에 대한 행이 하나씩 포함됩니다.

INFORMATION_SCHEMA.TABLE_OPTIONS 뷰에 대한 쿼리에는 데이터세트 한정자가 있어야 합니다. 쿼리를 제출하는 사용자는 테이블이나 뷰가 포함된 데이터세트에 액세스할 수 있어야 합니다.

INFORMATION_SCHEMA.TABLE_OPTIONS 뷰에는 다음과 같은 스키마가 있습니다.

열 이름 데이터 유형
TABLE_CATALOG STRING 데이터세트가 포함된 프로젝트 이름
TABLE_SCHEMA STRING 테이블이나 뷰가 포함된 데이터세트 이름(또는 datasetId라고 함)
TABLE_NAME STRING 테이블 또는 뷰의 이름(또는 tableId라고 함)
OPTION_NAME STRING 옵션 표의 이름 값 중 하나
OPTION_TYPE STRING 옵션 표의 데이터 유형 값 중 하나
OPTION_VALUE STRING 옵션 표의 값 옵션 중 하나
옵션 표
OPTION_NAME OPTION_TYPE OPTION_VALUE
partition_expiration_days FLOAT64 파티션을 나눈 테이블의 모든 파티션 기본 수명(일)
expiration_timestamp FLOAT64 테이블의 기본 수명(일)
kms_key_name STRING 테이블을 암호화하는 데 사용된 Cloud KMS 키의 이름
friendly_name STRING 테이블을 설명하는 이름
description STRING 테이블에 대한 설명
labels ARRAY<STRUCT<STRING, STRING>> 테이블의 라벨을 나타내는 STRUCT 배열

데이터세트 속성에 대한 자세한 내용은 REST API 문서의 데이터세트 리소스 페이지를 참조하세요. 테이블 및 뷰 속성에 대한 자세한 내용은 REST API 문서의 테이블 리소스 페이지를 참조하세요.

예시 1:

다음 예는 INFORMATION_SCHEMATA.TABLE_OPTIONS 뷰를 쿼리하여 기본 프로젝트(myproject)의 mydataset에 있는 모든 테이블의 기본 테이블 만료 시간을 검색합니다.

INFORMATION_SCHEMA.TABLE_OPTIONS 뷰에 대한 쿼리에는 데이터세트 한정자가 있어야 합니다. 쿼리를 제출하는 사용자는 테이블이 포함된 데이터세트에 액세스할 수 있어야 합니다.

기본 프로젝트가 아닌 프로젝트에 쿼리를 실행하려면 데이터세트에 프로젝트 ID를 `[PROJECT_ID]`.[DATASET].INFORMATION_SCHEMA.[VIEW] 형식으로 추가합니다(예: `myproject`.mydataset.INFORMATION_SCHEMA.TABLE_OPTIONS).

쿼리를 실행하려면 다음 단계를 따르세요.

Console

  1. GCP Console에서 BigQuery 웹 UI를 엽니다.

    BigQuery 웹 UI로 이동

  2. 쿼리 편집기 상자에 다음과 같은 표준 SQL 쿼리를 입력합니다. INFORMATION_SCHEMA에는 표준 SQL 구문이 필요합니다. 표준 SQL은 GCP Console의 기본 구문입니다.

    SELECT
     *
    FROM
     mydataset.INFORMATION_SCHEMA.TABLE_OPTIONS
    WHERE
     option_name="expiration_timestamp"
    
  3. 실행을 클릭합니다.

명령줄

query 명령어를 사용하고 --nouse_legacy_sql 또는 --use_legacy_sql=false 플래그를 사용하여 표준 SQL 구문을 지정합니다. INFORMATION_SCHEMA 쿼리에는 표준 SQL 구문이 필요합니다.

쿼리를 실행하려면 다음 명령어를 입력합니다.

bq query --nouse_legacy_sql \
'SELECT * FROM mydataset.INFORMATION_SCHEMA.TABLE_OPTIONS
WHERE option_name="expiration_timestamp"'

결과는 다음과 같이 표시됩니다.

  +----------------+---------------+------------+----------------------+-------------+--------------------------------------+
  | table_catalog  | table_schema  | table_name |     option_name      | option_type |             option_value             |
  +----------------+---------------+------------+----------------------+-------------+--------------------------------------+
  | myproject      | mydataset     | mytable1   | expiration_timestamp | TIMESTAMP   | TIMESTAMP "2020-01-16T21:12:28.000Z" |
  | myproject      | mydataset     | mytable2   | expiration_timestamp | TIMESTAMP   | TIMESTAMP "2021-01-01T21:12:28.000Z" |
  +----------------+---------------+------------+----------------------+-------------+--------------------------------------+
  

예시 2:

다음 예는 테스트 데이터가 포함된 mydataset의 모든 테이블에 대한 메타데이터를 검색합니다. 이 쿼리는 description 옵션의 값을 사용하여 설명의 아무 곳에서나 'test'가 포함된 테이블을 찾습니다. mydataset는 기본 프로젝트인 myproject에 있습니다.

기본 프로젝트가 아닌 프로젝트에 쿼리를 실행하려면 데이터세트에 프로젝트 ID를 `[PROJECT_ID]`.[DATASET].INFORMATION_SCHEMA.[VIEW] 형식으로 추가합니다(예: `myproject`.mydataset.INFORMATION_SCHEMA.TABLE_OPTIONS).

쿼리를 실행하려면 다음 단계를 따르세요.

Console

  1. GCP Console에서 BigQuery 웹 UI를 엽니다.

    BigQuery 웹 UI로 이동

  2. 쿼리 편집기 상자에 다음과 같은 표준 SQL 쿼리를 입력합니다. INFORMATION_SCHEMA에는 표준 SQL 구문이 필요합니다. 표준 SQL은 GCP Console의 기본 구문입니다.

    SELECT
     *
    FROM
     mydataset.INFORMATION_SCHEMA.TABLE_OPTIONS
    WHERE
     option_name="description" AND option_value LIKE "%test%"
    
  3. 실행을 클릭합니다.

명령줄

query 명령어를 사용하고 --nouse_legacy_sql 또는 --use_legacy_sql=false 플래그를 사용하여 표준 SQL 구문을 지정합니다. INFORMATION_SCHEMA 쿼리에는 표준 SQL 구문이 필요합니다.

쿼리를 실행하려면 다음 명령어를 입력합니다.

bq query --nouse_legacy_sql \
'SELECT * FROM mydataset.INFORMATION_SCHEMA.TABLE_OPTIONS
WHERE option_name="description" AND option_value LIKE "%test%"'

결과는 다음과 같이 표시됩니다.

  +----------------+---------------+------------+-------------+-------------+--------------+
  | table_catalog  | table_schema  | table_name | option_name | option_type | option_value |
  +----------------+---------------+------------+-------------+-------------+--------------+
  | myproject      | mydataset     | mytable1   | description | STRING      | "test data"  |
  | myproject      | mydataset     | mytable2   | description | STRING      | "test data"  |
  +----------------+---------------+------------+-------------+-------------+--------------+
  

COLUMNS

INFORMATION_SCHEMA.COLUMNS 뷰를 쿼리하면 쿼리 결과에 데이터세트의 각 테이블 또는 뷰에 대한 행이 하나씩 포함됩니다.

INFORMATION_SCHEMA.COLUMNS 뷰에 대한 쿼리에는 데이터세트 한정자가 있어야 합니다. 쿼리를 제출하는 사용자는 테이블이 포함된 데이터세트에 액세스할 수 있어야 합니다.

INFORMATION_SCHEMA.COLUMNS 뷰에는 다음과 같은 스키마가 있습니다.

열 이름 데이터 유형
TABLE_CATALOG STRING 데이터세트가 포함된 프로젝트 이름
TABLE_SCHEMA STRING 테이블이 포함된 데이터세트 이름(또는 datasetId라고 함)
TABLE_NAME STRING 테이블 또는 뷰의 이름(또는 tableId라고 함)
COLUMN_NAME STRING 열의 이름
ORDINAL_POSITION INT64 테이블에 있는 열의 1부터 시작하는 오프셋. _PARTITIONTIME 또는 _PARTITIONDATE와 같은 의사 열이면 값은 NULL입니다.
IS_NULLABLE STRING 열의 모드가 NULL 값을 허용하는지 여부에 따라 YES 또는 NO
DATA_TYPE STRING 열의 표준 SQL 데이터 유형
IS_GENERATED STRING 값이 항상 NEVER
GENERATION_EXPRESSION STRING 값이 항상 NULL
IS_STORED STRING 값이 항상 NULL
IS_HIDDEN STRING 열이 _PARTITIONTIME 또는 _PARTITIONDATE와 같은 의사 열인지 여부에 따라 YES 또는 NO
IS_UPDATABLE STRING 값이 항상 NULL
IS_SYSTEM_DEFINED STRING 열이 _PARTITIONTIME 또는 _PARTITIONDATE와 같은 의사 열인지 여부에 따라 YES 또는 NO
IS_PARTITIONING_COLUMN STRING 열이 파티션 나누기 열인지 여부에 따라 YES 또는 NO
CLUSTERING_ORDINAL_POSITION STRING 테이블 클러스터링 열에 있는 해당 열의 1부터 시작하는 오프셋. 테이블이 클러스터링된 테이블이 아니면 값은 NULL입니다.

데이터세트 속성에 대한 자세한 내용은 REST API 문서의 데이터세트 리소스 페이지를 참조하세요. 테이블 및 뷰 속성에 대한 자세한 내용은 REST API 문서의 테이블 리소스 페이지를 참조하세요.

다음 예에서는 census_bureau_usa 데이터세트에 있는 population_by_zip_2010 테이블의 INFORMATION_SCHEMA.COLUMNS 뷰에서 메타데이터를 검색합니다. 이 데이터세트는 BigQuery 공개 데이터세트 프로그램의 일부입니다.

이 테이블은 bigquery-public-data 프로젝트에 있으므로 데이터세트에 프로젝트 ID를 `[PROJECT_ID]`.[DATASET].INFORMATION_SCHEMA.[VIEW] 형식으로 추가합니다(예: `bigquery-public-data`.census_bureau_usa.INFORMATION_SCHEMA.TABLES).

다음 열은 현재 나중에 사용하도록 예약되어 있으므로 쿼리 결과에서 제외됩니다.

  • IS_GENERATED
  • GENERATION_EXPRESSION
  • IS_STORED
  • IS_UPDATABLE

INFORMATION_SCHEMA.COLUMNS 뷰에 대한 쿼리에는 데이터세트 한정자가 있어야 합니다. 쿼리를 제출하는 사용자는 테이블이 포함된 데이터세트에 액세스할 수 있어야 합니다.

쿼리를 실행하려면 다음 안내를 따르세요.

Console

  1. GCP Console에서 BigQuery 웹 UI를 엽니다.

    BigQuery 웹 UI로 이동

  2. 쿼리 편집기 상자에 다음과 같은 표준 SQL 쿼리를 입력합니다. INFORMATION_SCHEMA에는 표준 SQL 구문이 필요합니다. 표준 SQL은 GCP Console의 기본 구문입니다.

    SELECT
     * EXCEPT(is_generated, generation_expression, is_stored, is_updatable)
    FROM
     `bigquery-public-data`.census_bureau_usa.INFORMATION_SCHEMA.COLUMNS
    WHERE
     table_name="population_by_zip_2010"
    
  3. 실행을 클릭합니다.

명령줄

query 명령어를 사용하고 --nouse_legacy_sql 또는 --use_legacy_sql=false 플래그를 사용하여 표준 SQL 구문을 지정합니다. INFORMATION_SCHEMA 쿼리에는 표준 SQL 구문이 필요합니다.

쿼리를 실행하려면 다음을 입력하세요.

bq query --nouse_legacy_sql \
'SELECT * EXCEPT(is_generated, generation_expression, is_stored, is_updatable)
FROM `bigquery-public-data`.census_bureau_usa.INFORMATION_SCHEMA.COLUMNS
WHERE table_name="population_by_zip_2010"'

결과는 다음과 같이 표시됩니다. 가독성을 위해 table_catalogtable_schema는 결과에서 제외됩니다.

+------------------------+-------------+------------------+-------------+-----------+-----------+-------------------+------------------------+-----------------------------+
|       table_name       | column_name | ordinal_position | is_nullable | data_type | is_hidden | is_system_defined | is_partitioning_column | clustering_ordinal_position |
+------------------------+-------------+------------------+-------------+-----------+-----------+-------------------+------------------------+-----------------------------+
| population_by_zip_2010 | zipcode     |                1 | NO          | STRING    | NO        | NO                | NO                     |                        NULL |
| population_by_zip_2010 | geo_id      |                2 | YES         | STRING    | NO        | NO                | NO                     |                        NULL |
| population_by_zip_2010 | minimum_age |                3 | YES         | INT64     | NO        | NO                | NO                     |                        NULL |
| population_by_zip_2010 | maximum_age |                4 | YES         | INT64     | NO        | NO                | NO                     |                        NULL |
| population_by_zip_2010 | gender      |                5 | YES         | STRING    | NO        | NO                | NO                     |                        NULL |
| population_by_zip_2010 | population  |                6 | YES         | INT64     | NO        | NO                | NO                     |                        NULL |
+------------------------+-------------+------------------+-------------+-----------+-----------+-------------------+------------------------+-----------------------------+
  

COLUMN_FIELD_PATHS

INFORMATION_SCHEMA.COLUMN_FIELD_PATHS 뷰를 쿼리하면 RECORD(또는 STRUCT) 열에 있는 중첩된 각 열마다 행이 하나씩 쿼리 결과에 포함됩니다.

INFORMATION_SCHEMA.COLUMN_FIELD_PATHS 뷰에 대한 쿼리에는 데이터세트 한정자가 있어야 합니다. 쿼리를 제출하는 사용자는 테이블이 포함된 데이터세트에 액세스할 수 있어야 합니다.

INFORMATION_SCHEMA.COLUMN_FIELD_PATHS 뷰에는 다음과 같은 스키마가 있습니다.

열 이름 데이터 유형
TABLE_CATALOG >STRING 데이터세트가 포함된 프로젝트 이름
TABLE_SCHEMA STRING 테이블이 포함된 데이터세트 이름(또는 datasetId라고 함)
TABLE_NAME STRING 테이블 또는 뷰의 이름(또는 tableId라고 함)
COLUMN_NAME STRING 열의 이름
FIELD_PATH STRING `RECORD` 또는 `STRUCT` 열에 있는 중첩된 열 경로
DATA_TYPE STRING 열의 표준 SQL 데이터 유형
DESCRIPTION STRING 열의 설명

데이터세트 속성에 대한 자세한 내용은 REST API 문서의 데이터세트 리소스 페이지를 참조하세요. 테이블 및 뷰 속성에 대한 자세한 내용은 REST API 문서의 테이블 리소스 페이지를 참조하세요.

다음 예에서는 github_repos 데이터세트에 있는 commits 테이블의 INFORMATION_SCHEMA.COLUMN_FIELD_PATHS 뷰에서 메타데이터를 검색합니다. 이 데이터세트는 BigQuery 공개 데이터세트 프로그램의 일부입니다.

이 테이블은 bigquery-public-data 프로젝트에 있으므로 데이터세트에 프로젝트 ID를 `[PROJECT_ID]`.[DATASET].INFORMATION_SCHEMA.[VIEW] 형식으로 추가합니다(예: `bigquery-public-data`.github_repos.INFORMATION_SCHEMA.COLUMN_FIELD_PATHS).

commits 테이블에는 다음과 같은 중첩된 열과 중첩 및 반복되는 열이 포함됩니다.

  • author: 중첩 RECORD
  • committer: 중첩 RECORD
  • trailer: 중첩 및 반복 RECORD
  • difference: 중첩 및 반복 RECORD

이 쿼리는 authordifference 열에 대한 메타데이터를 검색합니다.

INFORMATION_SCHEMA.COLUMN_FIELD_PATHS 뷰에 대한 쿼리에는 데이터세트 한정자가 있어야 합니다. 쿼리를 제출하는 사용자는 테이블이 포함된 데이터세트에 액세스할 수 있어야 합니다.

쿼리를 실행하려면 다음 안내를 따르세요.

Console

  1. GCP Console에서 BigQuery 웹 UI를 엽니다.

    BigQuery 웹 UI로 이동

  2. 쿼리 편집기 상자에 다음과 같은 표준 SQL 쿼리를 입력합니다. INFORMATION_SCHEMA에는 표준 SQL 구문이 필요합니다. 표준 SQL은 GCP Console의 기본 구문입니다.

    SELECT
     *
    FROM
     `bigquery-public-data`.github_repos.INFORMATION_SCHEMA.COLUMN_FIELD_PATHS
    WHERE
     table_name="commits"
     AND column_name="author"
     OR column_name="difference"
    
  3. 실행을 클릭합니다.

명령줄

query 명령어를 사용하고 --nouse_legacy_sql 또는 --use_legacy_sql=false 플래그를 사용하여 표준 SQL 구문을 지정합니다. INFORMATION_SCHEMA 쿼리에는 표준 SQL 구문이 필요합니다.

쿼리를 실행하려면 다음을 입력하세요.

bq query --nouse_legacy_sql \
'SELECT * FROM `bigquery-public-data`.github_repos.INFORMATION_SCHEMA.COLUMN_FIELD_PATHS
WHERE table_name="commits" AND column_name="author" OR column_name="difference"'

결과는 다음과 같이 표시됩니다. 가독성을 위해 table_catalogtable_schema는 결과에서 제외됩니다.

  +------------+-------------+---------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------+-------------+
  | table_name | column_name |     field_path      |                                                                      data_type                                                                      | description |
  +------------+-------------+---------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------+-------------+
  | commits    | author      | author              | STRUCT<name STRING, email STRING, time_sec INT64, tz_offset INT64, date TIMESTAMP>                                                                  | NULL        |
  | commits    | author      | author.name         | STRING                                                                                                                                              | NULL        |
  | commits    | author      | author.email        | STRING                                                                                                                                              | NULL        |
  | commits    | author      | author.time_sec     | INT64                                                                                                                                               | NULL        |
  | commits    | author      | author.tz_offset    | INT64                                                                                                                                               | NULL        |
  | commits    | author      | author.date         | TIMESTAMP                                                                                                                                           | NULL        |
  | commits    | difference  | difference          | ARRAY<STRUCT<old_mode INT64, new_mode INT64, old_path STRING, new_path STRING, old_sha1 STRING, new_sha1 STRING, old_repo STRING, new_repo STRING>> | NULL        |
  | commits    | difference  | difference.old_mode | INT64                                                                                                                                               | NULL        |
  | commits    | difference  | difference.new_mode | INT64                                                                                                                                               | NULL        |
  | commits    | difference  | difference.old_path | STRING                                                                                                                                              | NULL        |
  | commits    | difference  | difference.new_path | STRING                                                                                                                                              | NULL        |
  | commits    | difference  | difference.old_sha1 | STRING                                                                                                                                              | NULL        |
  | commits    | difference  | difference.new_sha1 | STRING                                                                                                                                              | NULL        |
  | commits    | difference  | difference.old_repo | STRING                                                                                                                                              | NULL        |
  | commits    | difference  | difference.new_repo | STRING                                                                                                                                              | NULL        |
  +------------+-------------+---------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------+-------------+
  

고급 예

다음 고급 예제는 기본 프로젝트 myproject에 있는 mydataset의 테이블에 대한 메타데이터를 검색하기 위해 INFORMATION_SCHEMA.TABLES, TABLE_OPTIONS, COLUMNS 뷰를 쿼리합니다. mydataset는 2개의 테이블을 포함합니다.

  • mytable1: github_repos 공개 데이터세트의 commits 테이블과 동일한 스키마 사용
  • mytable2: census_bureau_usa 공개 데이터세트의 population_by_zip_2010 테이블과 동일한 스키마 사용

이 결과는 테이블을 다시 만드는 데 필요한 DDL 문을 어셈블하도록 맞춤설정 함수에서 사용됩니다. 그러면 쿼리 결과에서 DDL 문을 사용하여 mydataset에서 테이블을 다시 만들 수 있습니다.

기본 프로젝트가 아닌 프로젝트에 쿼리를 실행하려면 데이터세트에 프로젝트 ID를 `[PROJECT_ID]`.[DATASET].INFORMATION_SCHEMA.[VIEW] 형식으로 추가합니다(예: `myproject`.mydataset.INFORMATION_SCHEMA.TABLES).

쿼리를 실행하려면 다음 단계를 따르세요.

Console

  1. GCP Console에서 BigQuery 웹 UI를 엽니다.

    BigQuery 웹 UI로 이동

  2. 쿼리 편집기 상자에 다음과 같은 표준 SQL 쿼리를 입력합니다. INFORMATION_SCHEMA에는 표준 SQL 구문이 필요합니다. 표준 SQL은 GCP Console의 기본 구문입니다.

    CREATE TEMP FUNCTION MakePartitionByExpression(
      column_name STRING, data_type STRING
    ) AS (
      IF(
        column_name = '_PARTITIONTIME',
        'DATE(_PARTITIONTIME)',
        IF(
          data_type = 'TIMESTAMP',
          CONCAT('DATE(', column_name, ')'),
          column_name
        )
      )
    );
    
    CREATE TEMP FUNCTION MakePartitionByClause(
      columns ARRAY<STRUCT<column_name STRING, data_type STRING, is_nullable STRING,  is_partitioning_column STRING, clustering_ordinal_position INT64>>
    ) AS (
      IFNULL(
        CONCAT(
          'PARTITION BY ',
          (SELECT MakePartitionByExpression(column_name, data_type)
           FROM UNNEST(columns) WHERE is_partitioning_column = 'YES'),
          '\n'),
        ''
      )
    );
    
    CREATE TEMP FUNCTION MakeClusterByClause(
      columns ARRAY<STRUCT<column_name STRING, data_type STRING, is_nullable STRING,  is_partitioning_column STRING, clustering_ordinal_position INT64>>
    ) AS (
      IFNULL(
        CONCAT(
          'CLUSTER BY ',
          (SELECT STRING_AGG(column_name, ', ' ORDER BY clustering_ordinal_position)
            FROM UNNEST(columns) WHERE clustering_ordinal_position IS NOT NULL),
          '\n'
        ),
        ''
      )
    );
    
    CREATE TEMP FUNCTION MakeNullable(data_type STRING, is_nullable STRING)
    AS (
      IF(not STARTS_WITH(data_type, 'ARRAY<') and is_nullable = 'NO', ' NOT NULL', '')
    );
    
    CREATE TEMP FUNCTION MakeColumnList(
      columns ARRAY<STRUCT<column_name STRING, data_type STRING, is_nullable STRING,  is_partitioning_column STRING, clustering_ordinal_position INT64>>
    ) AS (
      IFNULL(
        CONCAT(
          '(\n',
          (SELECT STRING_AGG(CONCAT('  ', column_name, ' ', data_type,  MakeNullable(data_type, is_nullable)), ',\n')
           FROM UNNEST(columns)),
          '\n)\n'
        ),
        ''
      )
    );
    
    CREATE TEMP FUNCTION MakeOptionList(
      options ARRAY<STRUCT<option_name STRING, option_value STRING>>
    ) AS (
      IFNULL(
        CONCAT(
          'OPTIONS (\n',
          (SELECT STRING_AGG(CONCAT('  ', option_name, '=', option_value), ',\n') FROM UNNEST(options)),
          '\n)\n'),
        ''
      )
    );
    
    WITH Components AS (
      SELECT
        CONCAT('`', table_catalog, '.', table_schema, '.', table_name, '`') AS table_name,
        ARRAY_AGG(
          STRUCT(column_name, data_type, is_nullable, is_partitioning_column, clustering_ordinal_position)
          ORDER BY ordinal_position
        ) AS columns,
        (SELECT ARRAY_AGG(STRUCT(option_name, option_value))
         FROM mydataset.INFORMATION_SCHEMA.TABLE_OPTIONS AS t2
         WHERE t.table_name = t2.table_name) AS options
      FROM mydataset.INFORMATION_SCHEMA.TABLES AS t
      LEFT JOIN mydataset.INFORMATION_SCHEMA.COLUMNS
      USING (table_catalog, table_schema, table_name)
      WHERE table_type = 'BASE TABLE'
      GROUP BY table_catalog, table_schema, t.table_name
    )
    SELECT
      CONCAT(
        'CREATE OR REPLACE TABLE ',
        table_name,
        '\n',
        MakeColumnList(columns),
        MakePartitionByClause(columns),
        MakeClusterByClause(columns),
        MakeOptionList(options))
    FROM Components
    

명령줄

query 명령어를 사용하고 --nouse_legacy_sql 또는 --use_legacy_sql=false 플래그를 사용하여 표준 SQL 구문을 지정합니다. INFORMATION_SCHEMA 쿼리에는 표준 SQL 구문이 필요합니다.

쿼리를 실행하려면 다음을 입력하세요.

QUERY_TEXT=$(cat <<ENDQUERY
CREATE TEMP FUNCTION MakePartitionByExpression(
  column_name STRING, data_type STRING
) AS (
  IF(
    column_name = '_PARTITIONTIME',
    'DATE(_PARTITIONTIME)',
    IF(
      data_type = 'TIMESTAMP',
      CONCAT('DATE(', column_name, ')'),
      column_name
    )
  )
);
CREATE TEMP FUNCTION MakePartitionByClause(
  columns ARRAY<STRUCT<column_name STRING, data_type STRING, is_nullable STRING,  is_partitioning_column STRING, clustering_ordinal_position INT64>>
) AS (
  IFNULL(
    CONCAT(
      'PARTITION BY ',
      (SELECT MakePartitionByExpression(column_name, data_type)
       FROM UNNEST(columns) WHERE is_partitioning_column = 'YES'),
      '\n'),
    ''
  )
);
CREATE TEMP FUNCTION MakeClusterByClause(
  columns ARRAY<STRUCT<column_name STRING, data_type STRING, is_nullable STRING,  is_partitioning_column STRING, clustering_ordinal_position INT64>>
) AS (
  IFNULL(
    CONCAT(
      'CLUSTER BY ',
      (SELECT STRING_AGG(column_name, ', ' ORDER BY clustering_ordinal_position)
        FROM UNNEST(columns) WHERE clustering_ordinal_position IS NOT NULL),
      '\n'
    ),
    ''
  )
);
CREATE TEMP FUNCTION MakeNullable(data_type STRING, is_nullable STRING)
AS (
  IF(not STARTS_WITH(data_type, 'ARRAY<') and is_nullable = 'NO', ' NOT NULL', '')
);
CREATE TEMP FUNCTION MakeColumnList(
  columns ARRAY<STRUCT<column_name STRING, data_type STRING, is_nullable STRING,  is_partitioning_column STRING, clustering_ordinal_position INT64>>
) AS (
  IFNULL(
    CONCAT(
      '(\n',
      (SELECT STRING_AGG(CONCAT('  ', column_name, ' ', data_type,  MakeNullable(data_type, is_nullable)), ',\n')
       FROM UNNEST(columns)),
      '\n)\n'
    ),
    ''
  )
);
CREATE TEMP FUNCTION MakeOptionList(
  options ARRAY<STRUCT<option_name STRING, option_value STRING>>
) AS (
  IFNULL(
    CONCAT(
      'OPTIONS (\n',
      (SELECT STRING_AGG(CONCAT('  ', option_name, '=', option_value), ',\n') FROM UNNEST(options)),
      '\n)\n'),
    ''
  )
);
WITH Components AS (
  SELECT
    CONCAT('\`', table_catalog, '.', table_schema, '.', table_name, '\`') AS table_name,
    ARRAY_AGG(
      STRUCT(column_name, data_type, is_nullable, is_partitioning_column, clustering_ordinal_position)
      ORDER BY ordinal_position
    ) AS columns,
    (SELECT ARRAY_AGG(STRUCT(option_name, option_value))
     FROM mydataset.INFORMATION_SCHEMA.TABLE_OPTIONS AS t2
     WHERE t.table_name = t2.table_name) AS options
  FROM mydataset.INFORMATION_SCHEMA.TABLES AS t
  LEFT JOIN mydataset.INFORMATION_SCHEMA.COLUMNS
  USING (table_catalog, table_schema, table_name)
  WHERE table_type = 'BASE TABLE'
  GROUP BY table_catalog, table_schema, t.table_name
)
SELECT
  CONCAT(
    'CREATE OR REPLACE TABLE ',
    table_name,
    '\n',
    MakeColumnList(columns),
    MakePartitionByClause(columns),
    MakeClusterByClause(columns),
    MakeOptionList(options))
FROM Components
ENDQUERY
)
bq query --nouse_legacy_sql "$QUERY_TEXT"

다음과 유사하게 출력됩니다.

+-------------------------------------------------------------------------------------------------------------------------------------------------------------------+
|                                                                                f0_                                                                                |
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| CREATE OR REPLACE TABLE `myproject.mydataset.population_by_zip_2010`                                                                                              |
| (                                                                                                                                                                 |
|   zipcode STRING NOT NULL,                                                                                                                                        |
|   geo_id STRING,                                                                                                                                                  |
|   minimum_age INT64,                                                                                                                                              |
|   maximum_age INT64,                                                                                                                                              |
|   gender STRING,                                                                                                                                                  |
|   population INT64                                                                                                                                                |
| )                                                                                                                                                                 |
| OPTIONS (                                                                                                                                                         |
|   expiration_timestamp=TIMESTAMP "2019-04-17T02:10:32.055Z"                                                                                                       |
| )                                                                                                                                                                 |
| CREATE OR REPLACE TABLE `myproject.mydataset.commits`                                                                                                             |
| (                                                                                                                                                                 |
|   commit STRING,                                                                                                                                                  |
|   tree STRING,                                                                                                                                                    |
|   parent ARRAY<STRING>,                                                                                                                                           |
|   author STRUCT<name STRING, email STRING, time_sec INT64, tz_offset INT64, date TIMESTAMP>,                                                                      |
|   committer STRUCT<name STRING, email STRING, time_sec INT64, tz_offset INT64, date TIMESTAMP>,                                                                   |
|   subject STRING,                                                                                                                                                 |
|   message STRING,                                                                                                                                                 |
|   trailer ARRAY<STRUCT<key STRING, value STRING, email STRING>>,                                                                                                  |
|   difference ARRAY<STRUCT<old_mode INT64, new_mode INT64, old_path STRING, new_path STRING, old_sha1 STRING, new_sha1 STRING, old_repo STRING, new_repo STRING>>, |
|   difference_truncated BOOL,                                                                                                                                      |
|   repo_name ARRAY<STRING>,                                                                                                                                        |
|   encoding STRING                                                                                                                                                 |
| )                                                                                                                                                                 |
| OPTIONS (                                                                                                                                                         |
|   expiration_timestamp=TIMESTAMP "2019-04-17T03:12:03.248Z"                                                                                                       |
| )                                                                                                                                                                 |
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------+

다음 단계

이 페이지가 도움이 되었나요? 평가를 부탁드립니다.

다음에 대한 의견 보내기...

도움이 필요하시나요? 지원 페이지를 방문하세요.