Cloud Storage 데이터 쿼리

BigQuery는 다음 형식의 Cloud Storage 데이터 쿼리를 지원합니다.

  • 쉼표로 구분된 값(CSV)
  • JSON(줄바꿈으로 구분)
  • Avro
  • ORC
  • Parquet
  • Datastore 내보내기
  • Firestore 내보내기

BigQuery는 다음 저장소 등급의 Cloud Storage 데이터 쿼리를 지원합니다.

  • Standard
  • Nearline
  • Coldline
  • 보관처리

Cloud Storage 외부 데이터 소스를 쿼리하려면 데이터에 대한 Cloud Storage URI 경로를 제공하고 데이터 소스를 참조하는 테이블을 생성하세요. Cloud Storage 데이터 소스를 참조하는 데 사용되는 테이블은 영구 테이블 또는 임시 테이블일 수 있습니다.

Cloud Storage에 저장된 데이터를 쿼리할 때는 데이터세트와 Cloud Storage 버킷의 위치를 고려해야 합니다.

시작하기 전에

사용자에게 이 문서의 각 작업을 수행하는 데 필요한 권한을 부여하는 Identity and Access Management(IAM) 역할을 부여합니다. 태스크를 수행하는 데 필요한 권한(있는 경우)이 태스크의 '필요한 권한' 섹션에 나열됩니다.

Cloud Storage URI 검색

Cloud Storage 데이터 소스를 사용하여 외부 테이블을 만들려면 Cloud Storage URI를 제공해야 합니다.

Cloud Storage URI는 버킷 이름과 객체(파일 이름)로 구성됩니다. 예를 들어 Cloud Storage 버킷 이름이 mybucket이고 데이터 파일 이름이 myfile.csv라면 버킷 URI는 gs://mybucket/myfile.csv가 됩니다. 데이터가 여러 개의 파일로 분리되어 있으면 URI에 와일드 카드를 사용할 수 있습니다. 자세한 내용은 Cloud Storage 요청 URI를 참조하세요.

BigQuery는 처음 이중 슬래시 다음에 슬래시 여러 개가 연속으로 포함된 소스 URI를 지원하지 않습니다. Cloud Storage 객체 이름에는 연속된 슬래시('/') 문자 여러 개가 포함될 수 있습니다. 하지만 BigQuery는 연속된 슬래시 여러 개를 단일 슬래시로 변환합니다. 예를 들어 소스 URI gs://bucket/my//object//name은 Cloud Storage에서는 유효하지만 BigQuery에서는 작동하지 않습니다.

Cloud Storage URI를 가져오려면 다음 안내를 따르세요.

  1. Cloud Storage 콘솔을 엽니다.

    Cloud Storage Console

  2. 소스 데이터가 포함된 객체(파일) 위치로 이동합니다.

  3. Cloud Storage Console 맨 위에서 객체 경로를 확인합니다. URI를 만들기 위해 gs://bucket/file을 적절한 경로로 바꿉니다(예: gs://mybucket/myfile.json). bucket은 Cloud Storage 버킷 이름이고 file은 데이터가 포함된 객체(파일) 이름입니다.

영구 외부 테이블과 임시 외부 테이블

영구 테이블이나 임시 테이블을 사용하여 BigQuery에서 외부 데이터 소스를 쿼리할 수 있습니다. 영구 테이블은 데이터 세트에 생성되고 외부 데이터 소스에 연결된 테이블입니다. 테이블은 영구적이므로 액세스 제어를 사용하여 기본 외부 데이터 소스에 대한 액세스 권한이 있는 다른 사용자와 테이블을 공유할 수 있으며 언제든지 테이블을 쿼리할 수 있습니다.

임시 테이블을 사용하여 외부 데이터 소스를 쿼리하는 경우 쿼리를 포함하고 외부 데이터 소스에 연결된 비영구 테이블을 만드는 명령어를 사용합니다. 임시 테이블을 사용하는 경우 BigQuery 데이터 세트 중 하나에 테이블을 만들지 않습니다. 테이블이 데이터 세트에 영구적으로 저장되지 않으므로 다른 사용자와 테이블을 공유할 수 없습니다. 임시 테이블을 사용하여 외부 데이터 소스를 쿼리하면 외부 데이터에 대한 일회성 임시 쿼리 또는 ETL(추출, 변환, 로드) 프로세스에 유용합니다.

영구 외부 테이블을 사용한 Cloud Storage 데이터 쿼리

필수 권한

영구 테이블을 사용하여 Cloud Storage에서 외부 데이터를 쿼리하려면 다음을 수행할 권한이 필요합니다.

  • 프로젝트 수준 이상에서 쿼리 작업을 실행합니다.
  • 외부 데이터를 가리키는 테이블을 만듭니다.
  • 테이블에 액세스합니다.

외부 데이터가 Cloud Storage에 저장되는 경우 데이터가 포함된 버킷에 액세스할 수 있는 권한도 필요합니다.

BigQuery에서 외부 테이블을 만들고 쿼리할 수 있는 권한

BigQuery에서 외부 테이블을 만들고 쿼리하려면 다음 IAM 권한이 필요합니다.

  • bigquery.tables.create
  • bigquery.tables.getData
  • bigquery.jobs.create

다음과 같이 사전 정의된 각 IAM 역할에는 BigQuery에서 외부 테이블을 만들고 쿼리하는 데 필요한 권한이 포함되어 있습니다.

  • roles/bigquery.dataEditor
  • roles/bigquery.dataOwner
  • roles/bigquery.admin(bigquery.jobs.create 권한 포함)
  • roles/bigquery.user(bigquery.jobs.create 권한 포함)
  • roles/bigquery.jobUser(bigquery.jobs.create 권한 포함)

또한 bigquery.datasets.create 권한이 있으면 만들 데이터 세트에서 외부 테이블을 만들고 액세스할 수 있습니다. 그래도 데이터를 쿼리하려면 bigquery.jobs.create 권한이 필요합니다.

BigQuery의 IAM 역할과 권한에 대한 자세한 내용은 사전 정의된 역할 및 권한을 참조하세요.

Cloud Storage 버킷에서 외부 데이터를 쿼리할 수 있는 권한

Cloud Storage 버킷에서 외부 데이터를 쿼리하려면 다음 IAM 권한이 필요합니다.

  • storage.objects.get
  • storage.objects.list(URI 와일드 카드를 사용하는 경우 필수)

사전 정의된 IAM 역할 roles/storage.objectViewer에는 Cloud Storage 버킷에서 외부 데이터를 쿼리하는 데 필요한 모든 권한이 포함됩니다.

Compute Engine 인스턴스의 액세스 범위

Compute Engine 인스턴스에서 Cloud Storage 소스에 연결된 외부 테이블을 쿼리해야 하는 경우 인스턴스에 최소한 Cloud Storage 읽기 전용 액세스 범위(https://www.googleapis.com/auth/devstorage.read_only)가 있어야 합니다.

범위는 Cloud Storage를 포함한 Google Cloud 제품에 대한 Compute Engine 인스턴스의 액세스를 제어합니다. 인스턴스에서 실행되는 애플리케이션은 인스턴스에 연결된 서비스 계정을 사용하여 Google Cloud APIs를 호출합니다.

기본 Compute Engine 서비스 계정으로 실행되도록 Compute Engine 인스턴스를 설정하면 인스턴스에 기본적으로 https://www.googleapis.com/auth/devstorage.read_only 범위를 포함한 여러 기본 범위가 부여됩니다.

대신 커스텀 서비스 계정으로 인스턴스를 설정하는 경우 인스턴스에 https://www.googleapis.com/auth/devstorage.read_only 범위를 명시적으로 부여해야 합니다.

Compute Engine 인스턴스에 범위를 적용하는 방법은 인스턴스의 서비스 계정 및 액세스 범위 변경을 참조하세요. Compute Engine 서비스 계정에 대한 자세한 내용은 서비스 계정을 참조하세요.

영구 외부 테이블 만들기 및 쿼리

다음 방법으로 외부 데이터 소스에 연결된 영구 테이블을 만들 수 있습니다.

영구 테이블을 사용하여 외부 데이터 소스를 쿼리하려면 외부 데이터 소스와 연결된 BigQuery 데이터 세트에 테이블을 만듭니다. 데이터는 BigQuery 테이블에 저장되지 않습니다. 테이블은 영구적이므로 액세스 제어를 사용하여 기본 외부 데이터 소스에 대한 액세스 권한이 있는 다른 사용자와 테이블을 공유할 수 있습니다.

영구 외부 테이블을 만들 때 다음과 같은 방법으로 스키마를 지정할 수 있습니다.

외부 테이블을 만들려면 다음 안내를 따르세요.

Console

  1. Cloud Console에서 BigQuery 페이지를 엽니다.

BigQuery로 이동

  1. 탐색기 패널에서 프로젝트를 확장하고 데이터 세트를 선택합니다.

  2. 작업 옵션을 펼치고 테이블 만들기를 클릭합니다.

  3. 테이블 만들기 페이지의 소스 섹션에서 다음을 수행합니다.

    • 다음 항목으로 테이블 만들기에서 Google Cloud Storage를 선택합니다.

    • GCS 버킷에서 파일 선택 필드에서 파일/Cloud Storage 버킷을 찾거나 Cloud Storage URI를 입력합니다. Cloud Console에서는 URI가 여러 개 포함될 수 없지만 와일드 카드는 지원됩니다. Cloud Storage 버킷은 만들려는 테이블이 포함된 데이터 세트와 같은 위치에 있어야 합니다.

    • 파일 형식에 사용 중인 데이터의 형식을 선택합니다.

  4. 테이블 만들기 페이지의 대상 섹션에서 다음을 수행합니다.

    • 데이터 세트에서 적절한 데이터 세트를 선택합니다.
    • 테이블 유형이 외부 테이블로 설정되어 있는지 확인합니다.
    • 테이블 필드에 BigQuery에 만들려는 테이블의 이름을 입력합니다.

  5. 스키마 섹션에서 스키마 자동 감지를 사용 설정하거나 스키마를 수동으로 지정할 수 있습니다.

    • 스키마 자동 감지를 사용 설정하려면 자동 감지 옵션을 선택합니다.

    • 스키마를 수동으로 지정하려면 자동 감지 옵션을 선택하지 않은 상태로 두고 다음 중 하나를 수행합니다.

      • 텍스트로 편집을 사용 설정하고 테이블 스키마를 JSON 배열로 입력합니다.

  6. 테이블 만들기를 클릭합니다.

영구 테이블이 생성된 후에는 기본 BigQuery 테이블인 것처럼 이 테이블에 대해 쿼리를 실행할 수 있습니다. 쿼리가 완료되면 결과를 CSV 또는 JSON 파일로 내보내거나, 테이블로 저장하거나, Google 스프레드시트에 저장할 수 있습니다.

SQL

CREATE EXTERNAL TABLE DDL 문을 실행하여 영구 외부 테이블을 만들 수 있습니다. 스키마를 명시적으로 지정할 수 있습니다. 스키마를 지정하지 않으면 BigQuery가 스키마 자동 감지를 사용하여 외부 테이블에서 스키마를 유추합니다.

다음 예시에서는 스키마 자동 감지를 사용하여 Cloud Storage에 저장된 CSV 파일에 연결되는 sales라는 외부 테이블을 만듭니다.

  1. Cloud 콘솔에서 BigQuery 페이지로 이동합니다.

    BigQuery로 이동

  2. 쿼리 편집기에서 다음 문을 입력합니다.

    CREATE OR REPLACE EXTERNAL TABLE mydataset.sales
  OPTIONS (
  format = 'CSV',
  uris = ['gs://mybucket/sales.csv']);

  3. 실행을 클릭합니다.

쿼리를 실행하는 방법에 대한 자세한 내용은 대화형 쿼리 실행을 참조하세요.

다음 예시에서는 스키마를 명시적으로 지정하고 CSV 파일의 첫 번째 행을 건너뜁니다.

CREATE OR REPLACE EXTERNAL TABLE mydataset.sales (
  Region STRING,
  Quarter STRING,
  Total_Sales INT64
) OPTIONS (
    format = 'CSV',
    uris = ['gs://mybucket/sales.csv'],
    skip_leading_rows = 1);

bq

외부 테이블을 만들려면 bq mk 명령어를 --external_table_definition 플래그와 함께 실행합니다. 이 플래그에는 테이블 정의 파일 경로 또는 인라인 테이블 정의가 포함됩니다.

옵션 1: 테이블 정의 파일

bq mkdef 명령어를 사용하여 테이블 정의 파일을 만든 후 다음과 같이 bq mk 명령어에 파일 경로를 전달합니다.

bq mkdef --source_format=SOURCE_FORMAT \
  BUCKET_URL > DEFINITION_FILE

bq mk --table \
  --external_table_definition=DEFINITION_FILE \
  DATASET_NAME.TABLE_NAME \
  SCHEMA

각 항목의 의미는 다음과 같습니다.

  • SOURCE_FORMAT은 외부 데이터 소스의 형식입니다(예: CSV).
  • BUCKET_URI는 Cloud Storage URI입니다.
  • DEFINITION_FILE은 로컬 머신에 있는 테이블 정의 파일의 경로입니다.
  • DATASET_NAME은 테이블이 포함된 데이터 세트의 이름입니다.
  • TABLE_NAME: 만드는 테이블의 이름
  • SCHEMA는 JSON 스키마 파일 경로를 지정하거나 field:data_type,field:data_type,... 형식으로 스키마를 지정합니다.

예시:

bq mkdef --source_format=CSV gs://mybucket/sales.csv > mytable_def

bq mk --table --external_table_definition=mytable_def \
  mydataset.mytable \
  Region:STRING,Quarter:STRING,Total_sales:INTEGER

스키마 자동 감지를 사용하려면 mkdef 명령어에서 --autodetect=true 플래그를 설정하고 스키마를 생략합니다.

bq mkdef --source_format=CSV --autodetect=true \
  gs://mybucket/sales.csv > mytable_def

bq mk --table --external_table_definition=mytable_def \
  mydataset.mytable

옵션 2: 인라인 테이블 정의

테이블 정의 파일을 만드는 대신 테이블 정의를 bq mk 명령어에 직접 전달할 수 있습니다.

bq mk --table \
  --external_table_definition=@SOURCE_FORMAT=BUCKET_URI \
  DATASET_NAME.TABLE_NAME \
  SCHEMA

각 항목의 의미는 다음과 같습니다.

  • SOURCE_FORMAT은 외부 데이터 소스의 형식입니다(예: CSV).
  • BUCKET_URI는 Cloud Storage URI입니다.
  • DATASET_NAME는 테이블이 포함된 데이터 세트의 이름입니다.
  • TABLE_NAME은 만드는 테이블의 이름입니다.
  • SCHEMA: JSON 스키마 파일 경로를 지정하거나 field:data_type,field:data_type,... 형식으로 스키마를 지정. 스키마 자동 감지를 사용하려면 이 인수를 생략하세요.

예:

bq mkdef --source_format=CSV gs://mybucket/sales.csv > mytable_def
bq mk --table --external_table_definition=mytable_def \
  mydataset.mytable \
  Region:STRING,Quarter:STRING,Total_sales:INTEGER

API

tables.insert API 메서드를 사용할 때 ExternalDataConfiguration을 만듭니다. schema 속성을 지정하거나 autodetect 속성을 true로 설정하여 지원되는 데이터 소스에 스키마 자동 감지를 사용 설정합니다.

자바

이 샘플을 사용해 보기 전에 BigQuery 빠른 시작: 클라이언트 라이브러리 사용의 자바 설정 안내를 따르세요. 자세한 내용은 BigQuery 자바 API 참조 문서를 확인하세요. 

import com.google.cloud.bigquery.BigQuery;
import com.google.cloud.bigquery.BigQueryException;
import com.google.cloud.bigquery.BigQueryOptions;
import com.google.cloud.bigquery.CsvOptions;
import com.google.cloud.bigquery.ExternalTableDefinition;
import com.google.cloud.bigquery.Field;
import com.google.cloud.bigquery.QueryJobConfiguration;
import com.google.cloud.bigquery.Schema;
import com.google.cloud.bigquery.StandardSQLTypeName;
import com.google.cloud.bigquery.TableId;
import com.google.cloud.bigquery.TableInfo;
import com.google.cloud.bigquery.TableResult;

// Sample to queries an external data source using a permanent table
public class QueryExternalGCSPerm {

  public static void runQueryExternalGCSPerm() {
    // TODO(developer): Replace these variables before running the sample.
    String datasetName = "MY_DATASET_NAME";
    String tableName = "MY_TABLE_NAME";
    String sourceUri = "gs://cloud-samples-data/bigquery/us-states/us-states.csv";
    Schema schema =
        Schema.of(
            Field.of("name", StandardSQLTypeName.STRING),
            Field.of("post_abbr", StandardSQLTypeName.STRING));
    String query =
        String.format("SELECT * FROM %s.%s WHERE name LIKE 'W%%'", datasetName, tableName);
    queryExternalGCSPerm(datasetName, tableName, sourceUri, schema, query);
  }

  public static void queryExternalGCSPerm(
      String datasetName, String tableName, String sourceUri, Schema schema, String query) {
    try {
      // Initialize client that will be used to send requests. This client only needs to be created
      // once, and can be reused for multiple requests.
      BigQuery bigquery = BigQueryOptions.getDefaultInstance().getService();

      // Skip header row in the file.
      CsvOptions csvOptions = CsvOptions.newBuilder().setSkipLeadingRows(1).build();

      TableId tableId = TableId.of(datasetName, tableName);
      // Create a permanent table linked to the GCS file
      ExternalTableDefinition externalTable =
          ExternalTableDefinition.newBuilder(sourceUri, csvOptions).setSchema(schema).build();
      bigquery.create(TableInfo.of(tableId, externalTable));

      // Example query to find states starting with 'W'
      TableResult results = bigquery.query(QueryJobConfiguration.of(query));

      results
          .iterateAll()
          .forEach(row -> row.forEach(val -> System.out.printf("%s,", val.toString())));

      System.out.println("Query on external permanent table performed successfully.");
    } catch (BigQueryException | InterruptedException e) {
      System.out.println("Query not performed \n" + e.toString());
    }
  }
}

Node.js

이 샘플을 사용해 보기 전에 BigQuery 빠른 시작: 클라이언트 라이브러리 사용의 Node.js 설정 안내를 따르세요. 자세한 내용은 BigQuery Node.js API 참조 문서를 확인하세요. 

// Import the Google Cloud client library and create a client
const {BigQuery} = require('@google-cloud/bigquery');
const bigquery = new BigQuery();

async function queryExternalGCSPerm() {
  // Queries an external data source using a permanent table

  /**
   * TODO(developer): Uncomment the following lines before running the sample.
   */
  // const datasetId = "my_dataset";
  // const tableId = "my_table";

  // Configure the external data source
  const dataConfig = {
    sourceFormat: 'CSV',
    sourceUris: ['gs://cloud-samples-data/bigquery/us-states/us-states.csv'],
    // Optionally skip header row
    csvOptions: {skipLeadingRows: 1},
  };

  // For all options, see https://cloud.google.com/bigquery/docs/reference/v2/tables#resource
  const options = {
    schema: schema,
    externalDataConfiguration: dataConfig,
  };

  // Create an external table linked to the GCS file
  const [table] = await bigquery
    .dataset(datasetId)
    .createTable(tableId, options);

  console.log(`Table ${table.id} created.`);

  // Example query to find states starting with 'W'
  const query = `SELECT post_abbr
  FROM \`${datasetId}.${tableId}\`
  WHERE name LIKE 'W%'`;

  // Run the query as a job
  const [job] = await bigquery.createQueryJob(query);
  console.log(`Job ${job.id} started.`);

  // Wait for the query to finish
  const [rows] = await job.getQueryResults();

  // Print the results
  console.log('Rows:');
  console.log(rows);
}

Python

이 샘플을 사용해 보기 전에 BigQuery 빠른 시작: 클라이언트 라이브러리 사용의 Python 설정 안내를 따르세요. 자세한 내용은 BigQuery Python API 참조 문서를 확인하세요. 

# from google.cloud import bigquery
# client = bigquery.Client()
# dataset_id = 'my_dataset'

# Configure the external data source
dataset_ref = bigquery.DatasetReference(project, dataset_id)
table_id = "us_states"
schema = [
    bigquery.SchemaField("name", "STRING"),
    bigquery.SchemaField("post_abbr", "STRING"),
]
table = bigquery.Table(dataset_ref.table(table_id), schema=schema)
external_config = bigquery.ExternalConfig("CSV")
external_config.source_uris = [
    "gs://cloud-samples-data/bigquery/us-states/us-states.csv"
]
external_config.options.skip_leading_rows = 1  # optionally skip header row
table.external_data_configuration = external_config

# Create a permanent table linked to the GCS file
table = client.create_table(table)  # API request

# Example query to find states starting with 'W'
sql = 'SELECT * FROM `{}.{}` WHERE name LIKE "W%"'.format(dataset_id, table_id)

query_job = client.query(sql)  # API request

w_states = list(query_job)  # Waits for query to finish
print("There are {} states with names starting with W.".format(len(w_states)))

임시 테이블을 사용하여 Cloud Storage 데이터 쿼리

영구 테이블을 만들지 않고 외부 데이터 소스를 쿼리하려면 다음을 결합하는 명령어를 실행합니다.

테이블 정의 파일이나 제공된 스키마는 임시 외부 테이블을 만드는 데 사용되며, 임시 외부 테이블을 대상으로 쿼리가 실행됩니다. 임시 테이블을 사용한 외부 데이터 소스 쿼리는 bq 명령줄 도구 및 API에서 지원됩니다.

임시 외부 테이블을 사용하는 경우, BigQuery 데이터 세트 중 하나에 테이블을 만들지 마세요. 테이블이 데이터 세트에 영구적으로 저장되지 않으므로, 다른 사용자와 테이블을 공유할 수 없습니다. 임시 테이블을 사용하여 외부 데이터 소스를 쿼리하면 외부 데이터를 대상으로 하는 일회성 임시 쿼리 또는 ETL(추출, 변환, 로드) 프로세스에 유용합니다.

필수 권한

임시 테이블을 사용하여 Cloud Storage에서 외부 데이터를 쿼리하려면 프로젝트 수준 이상에서 쿼리 작업을 실행할 수 있는 권한과 외부 데이터를 가리키는 테이블이 포함된 데이터 세트에 대한 액세스 권한이 필요합니다. Cloud Storage에서 데이터를 쿼리하려면 데이터가 포함된 버킷에 액세스할 수 있는 권한도 필요합니다.

BigQuery에서 외부 테이블을 쿼리할 권한

임시 테이블을 사용하여 BigQuery에서 외부 테이블을 쿼리하려면 다음 IAM 권한이 필요합니다.

  • bigquery.tables.getData
  • bigquery.jobs.create

다음과 같이 사전 정의된 각 IAM 역할에는 임시 테이블을 사용하여 BigQuery에서 외부 테이블을 쿼리하는 데 필요한 권한이 포함되어 있습니다.

  • roles/bigquery.dataEditor
  • roles/bigquery.dataOwner
  • roles/bigquery.admin(bigquery.jobs.create 권한 포함)
  • roles/bigquery.user(bigquery.jobs.create 권한 포함)
  • roles/bigquery.jobUser(bigquery.jobs.create 권한 포함)

또한 bigquery.datasets.create 권한이 있으면 만들 데이터 세트에서 외부 테이블을 만들고 액세스할 수 있습니다. 그래도 데이터를 쿼리하려면 bigquery.jobs.create 권한이 필요합니다.

BigQuery의 IAM 역할과 권한에 대한 자세한 내용은 사전 정의된 역할 및 권한을 참조하세요.

Cloud Storage 버킷에서 외부 데이터를 쿼리할 수 있는 권한

Cloud Storage 버킷에서 외부 데이터를 쿼리하려면 다음 IAM 권한이 필요합니다.

  • storage.objects.get
  • storage.objects.list(URI 와일드 카드를 사용하는 경우 필수)

사전 정의된 IAM 역할 roles/storage.objectViewer에는 Cloud Storage 버킷에서 외부 데이터를 쿼리하는 데 필요한 모든 권한이 포함됩니다.

임시 테이블 만들기 및 쿼리

bq 명령줄 도구, API, 클라이언트 라이브러리를 사용하여 외부 데이터 소스에 연결된 임시 테이블을 만들고 쿼리할 수 있습니다.

bq

--external_table_definition 플래그와 함께 bq query 명령어를 사용하여 외부 데이터 소스에 연결된 임시 테이블을 쿼리합니다. bq 명령줄 도구를 사용하여 외부 데이터 소스에 연결된 임시 테이블을 쿼리할 때는 다음을 사용하여 테이블의 스키마를 식별할 수 있습니다.

  • 테이블 정의 파일(로컬 머신에 저장됨)
  • 인라인 스키마 정의
  • JSON 스키마 파일(로컬 머신에 저장됨)

(선택사항) --location 플래그를 지정하고 값을 사용자 위치로 설정합니다.

테이블 정의 파일을 사용하여 외부 데이터 소스에 연결된 임시 테이블을 쿼리하려면 다음 명령어를 입력합니다.

bq --location=location query \
--external_table_definition=table::definition_file \
'query'

각 항목의 의미는 다음과 같습니다.

  • location은 사용자 위치의 이름입니다. --location 플래그는 선택사항입니다. 예를 들어 도쿄 리전에서 BigQuery를 사용하는 경우 플래그 값을 asia-northeast1로 설정하면 됩니다. .bigqueryrc 파일을 사용하여 위치 기본값을 설정할 수 있습니다.
  • table은 만들고 있는 임시 테이블의 이름입니다.
  • definition_file은 로컬 머신에 있는 테이블 정의 파일 경로입니다.
  • query는 임시 테이블에 제출하는 쿼리입니다.

예를 들어 다음 명령어는 sales_def라는 테이블 정의 파일을 사용하여 sales라는 임시 파일을 만들고 쿼리합니다.

bq query \
--external_table_definition=sales::sales_def \
'SELECT
  Region,
  Total_sales
FROM
  sales'

인라인 스키마 정의를 사용하여 외부 데이터 소스에 연결된 임시 테이블을 쿼리하려면 다음 명령어를 입력합니다.

bq --location=location query \
--external_table_definition=table::schema@source_format=Cloud Storage URI \
'query'

각 항목의 의미는 다음과 같습니다.

  • location은 사용자 위치의 이름입니다. --location 플래그는 선택사항입니다. 예를 들어 도쿄 리전에서 BigQuery를 사용하는 경우 플래그 값을 asia-northeast1로 설정하면 됩니다. .bigqueryrc 파일을 사용하여 위치 기본값을 설정할 수 있습니다.
  • table은 만들고 있는 임시 테이블의 이름입니다.
  • schema는 field:data_type,field:data_type 형식의 인라인 스키마 정의입니다.
  • source_format은 외부 데이터 소스의 형식입니다(예: CSV).
  • Cloud Storage URI는 Cloud Storage URI입니다.
  • query는 임시 테이블에 제출하는 쿼리입니다.

예를 들어 다음 명령어는 스키마 정의 Region:STRING,Quarter:STRING,Total_sales:INTEGER를 사용하여 Cloud Storage에 저장되는 CSV 파일에 연결된 sales라는 임시 테이블을 만들고 쿼리합니다.

bq query \
--external_table_definition=sales::Region:STRING,Quarter:STRING,Total_sales:INTEGER@CSV=gs://mybucket/sales.csv \
'SELECT
  Region,
  Total_sales
FROM
  sales'

JSON 스키마 파일을 사용하여 외부 데이터 소스에 연결된 임시 테이블을 쿼리하려면 다음 명령어를 입력합니다.

bq --location=location query \
--external_table_definition=schema_file@source_format=Cloud Storage URI \
'query'

각 항목의 의미는 다음과 같습니다.

  • location은 사용자 위치의 이름입니다. --location 플래그는 선택사항입니다. 예를 들어 도쿄 리전에서 BigQuery를 사용하는 경우 플래그 값을 asia-northeast1로 설정하면 됩니다. .bigqueryrc 파일을 사용하여 위치 기본값을 설정할 수 있습니다.
  • schema_file는 로컬 머신에 있는 JSON 스키마 파일의 경로입니다.
  • source_format은 외부 데이터 소스의 형식입니다(예: CSV).
  • Cloud Storage URI는 Cloud Storage URI입니다.
  • query는 임시 테이블에 제출하는 쿼리입니다.

예를 들어 다음 명령어는 /tmp/sales_schema.json 스키마 파일을 사용하여 Cloud Storage에 저장되는 CSV 파일에 연결된 sales라는 임시 테이블을 만들고 쿼리합니다.

  bq query \
  --external_table_definition=sales::/tmp/sales_schema.json@CSV=gs://mybucket/sales.csv \
  'SELECT
      Region,
      Total_sales
    FROM
      sales'

API

자바

이 샘플을 사용해 보기 전에 BigQuery 빠른 시작: 클라이언트 라이브러리 사용의 자바 설정 안내를 따르세요. 자세한 내용은 BigQuery 자바 API 참조 문서를 확인하세요. 

import com.google.cloud.bigquery.BigQuery;
import com.google.cloud.bigquery.BigQueryException;
import com.google.cloud.bigquery.BigQueryOptions;
import com.google.cloud.bigquery.CsvOptions;
import com.google.cloud.bigquery.ExternalTableDefinition;
import com.google.cloud.bigquery.Field;
import com.google.cloud.bigquery.QueryJobConfiguration;
import com.google.cloud.bigquery.Schema;
import com.google.cloud.bigquery.StandardSQLTypeName;
import com.google.cloud.bigquery.TableResult;

// Sample to queries an external data source using a temporary table
public class QueryExternalGCSTemp {

  public static void runQueryExternalGCSTemp() {
    // TODO(developer): Replace these variables before running the sample.
    String tableName = "MY_TABLE_NAME";
    String sourceUri = "gs://cloud-samples-data/bigquery/us-states/us-states.csv";
    Schema schema =
        Schema.of(
            Field.of("name", StandardSQLTypeName.STRING),
            Field.of("post_abbr", StandardSQLTypeName.STRING));
    String query = String.format("SELECT * FROM %s WHERE name LIKE 'W%%'", tableName);
    queryExternalGCSTemp(tableName, sourceUri, schema, query);
  }

  public static void queryExternalGCSTemp(
      String tableName, String sourceUri, Schema schema, String query) {
    try {
      // Initialize client that will be used to send requests. This client only needs to be created
      // once, and can be reused for multiple requests.
      BigQuery bigquery = BigQueryOptions.getDefaultInstance().getService();

      // Skip header row in the file.
      CsvOptions csvOptions = CsvOptions.newBuilder().setSkipLeadingRows(1).build();

      // Configure the external data source and query job.
      ExternalTableDefinition externalTable =
          ExternalTableDefinition.newBuilder(sourceUri, csvOptions).setSchema(schema).build();
      QueryJobConfiguration queryConfig =
          QueryJobConfiguration.newBuilder(query)
              .addTableDefinition(tableName, externalTable)
              .build();

      // Example query to find states starting with 'W'
      TableResult results = bigquery.query(queryConfig);

      results
          .iterateAll()
          .forEach(row -> row.forEach(val -> System.out.printf("%s,", val.toString())));

      System.out.println("Query on external temporary table performed successfully.");
    } catch (BigQueryException | InterruptedException e) {
      System.out.println("Query not performed \n" + e.toString());
    }
  }
}

Node.js

이 샘플을 사용해 보기 전에 BigQuery 빠른 시작: 클라이언트 라이브러리 사용의 Node.js 설정 안내를 따르세요. 자세한 내용은 BigQuery Node.js API 참조 문서를 확인하세요. 

// Import the Google Cloud client library and create a client
const {BigQuery} = require('@google-cloud/bigquery');
const bigquery = new BigQuery();

async function queryExternalGCSTemp() {
  // Queries an external data source using a temporary table.

  const tableId = 'us_states';

  // Configure the external data source
  const externalDataConfig = {
    sourceFormat: 'CSV',
    sourceUris: ['gs://cloud-samples-data/bigquery/us-states/us-states.csv'],
    // Optionally skip header row.
    csvOptions: {skipLeadingRows: 1},
    schema: {fields: schema},
  };

  // Example query to find states starting with 'W'
  const query = `SELECT post_abbr
  FROM \`${tableId}\`
  WHERE name LIKE 'W%'`;

  // For all options, see https://cloud.google.com/bigquery/docs/reference/v2/tables#resource
  const options = {
    query,
    tableDefinitions: {[tableId]: externalDataConfig},
  };

  // Run the query as a job
  const [job] = await bigquery.createQueryJob(options);
  console.log(`Job ${job.id} started.`);

  // Wait for the query to finish
  const [rows] = await job.getQueryResults();

  // Print the results
  console.log('Rows:');
  console.log(rows);
}

Python

이 샘플을 사용해 보기 전에 BigQuery 빠른 시작: 클라이언트 라이브러리 사용의 Python 설정 안내를 따르세요. 자세한 내용은 BigQuery Python API 참조 문서를 확인하세요. 

from google.cloud import bigquery

# Construct a BigQuery client object.
client = bigquery.Client()

# Configure the external data source and query job.
external_config = bigquery.ExternalConfig("CSV")
external_config.source_uris = [
    "gs://cloud-samples-data/bigquery/us-states/us-states.csv"
]
external_config.schema = [
    bigquery.SchemaField("name", "STRING"),
    bigquery.SchemaField("post_abbr", "STRING"),
]
external_config.options.skip_leading_rows = 1
table_id = "us_states"
job_config = bigquery.QueryJobConfig(table_definitions={table_id: external_config})

# Example query to find states starting with 'W'.
sql = 'SELECT * FROM `{}` WHERE name LIKE "W%"'.format(table_id)

query_job = client.query(sql, job_config=job_config)  # Make an API request.

w_states = list(query_job)  # Wait for the job to complete.
print("There are {} states with names starting with W.".format(len(w_states)))

외부 파티션 데이터 쿼리

외부에서 파티션을 나눈 Cloud Storage 데이터 쿼리 관련 안내를 참조하세요.

Cloud Storage URI의 와일드 카드 지원

Cloud Storage 데이터가 공통 기본 이름을 공유하는 다수의 파일로 분할되는 경우에는 테이블 정의 파일의 URI에 와일드 카드를 사용할 수 있습니다. 테이블 정의 파일을 사용하지 않고 외부 테이블을 만들 때도 와일드 카드를 사용할 수 있습니다.

Cloud Storage URI에 와일드 카드를 추가하려면 기본 이름에 별표(*)를 추가합니다.

예:

  • 다음 와일드 카드 URI는 프리픽스 gs://mybucket/fed-samples/fed-sample로 시작하는 모든 폴더의 모든 파일을 선택합니다.

    gs://mybucket/fed-samples/fed-sample*

  • 다음 와일드 카드 URI에서는 fed-samples라는 폴더와 fed-samples의 모든 하위 폴더에 있는 .csv 확장자가 있는 파일만 선택합니다.

    gs://mybucket/fed-samples/fed-sample/*.csv

  • 다음 와일드 카드 URI에서는 fed-samples라는 폴더에 있는 명명 패턴이 fed-sample*.csv인 파일을 선택합니다. 이 예시에서는 fed-samples 하위 폴더에 있는 파일을 선택하지 않습니다.

    gs://mybucket/fed-samples/fed-sample*.csv

bq 명령줄 도구를 사용할 때 일부 플랫폼에서 별표를 이스케이프 처리해야 할 수도 있습니다.

와일드 카드는 버킷 내의 객체(파일 이름)에 하나만 사용할 수 있습니다. 와일드 카드는 객체 이름 중간이나 끝에 입력할 수 있습니다. 버킷 이름에는 와일드 카드를 추가할 수 없습니다. 소스 URI에서 와일드 카드 여러 개가 지원되지 않습니다. 예를 들어 gs://mybucket/fed-*/temp/*.csv 경로는 유효하지 않습니다.

Google Datastore 내보내기의 경우 URI를 하나만 지정할 수 있으며 .backup_info 또는 .export_metadata로 끝나야 합니다.

다음과 같은 경우 별표 와일드 카드 문자가 허용되지 않습니다.

  • Datastore 또는 Firestore 내보내기에 연결된 외부 테이블을 만드는 경우
  • Cloud Storage에서 Datastore 또는 Firestore 내보내기 데이터를 로드하는 경우

_FILE_NAME 유사 열

외부 데이터 소스를 기반으로 하는 테이블은 _FILE_NAME이라는 유사 열을 제공합니다. 이 열에는 행이 속한 파일의 정규화된 경로가 있습니다. Cloud Storage와 Google 드라이브에 저장된 외부 데이터를 참조하는 테이블에만 이 열을 사용할 수 있습니다.

_FILE_NAME 열 이름은 예약되어 있으므로, 어떤 테이블에도 이 이름으로 열을 만들 수 없습니다. _FILE_NAME 값을 선택하려면 별칭을 사용해야 합니다. 다음 예시 쿼리에서는 유사 열에 별칭 fn을 할당하여 _FILE_NAME을 선택하는 방법을 보여줍니다.

bq query \
--project_id=project_id \
--use_legacy_sql=false \
'SELECT
   name,
   _FILE_NAME AS fn
 FROM
   `dataset.table_name`
 WHERE
   name contains "Alex"'

각 항목의 의미는 다음과 같습니다.

  • project_id는 유효한 프로젝트 ID입니다(Cloud Shell을 사용하거나 Google Cloud CLI에서 기본 프로젝트를 설정하는 경우에는 이 플래그가 필요 없음).
  • dataset는 영구 외부 테이블이 저장되는 데이터 세트 이름입니다.
  • table_name은 영구 외부 테이블 이름입니다.

쿼리의 _FILE_NAME 유사 열에 필터 조건자가 있는 경우 BigQuery는 필터를 충족하지 않는 파일 읽기를 건너뛰려고 시도합니다. _FILE_NAME 유사 열로 쿼리 조건자를 구성할 때 유사 열을 사용하여 수집-시간으로 파티션을 나눈 테이블 쿼리와 유사한 권장사항이 적용됩니다.