드라이브 데이터 쿼리

이 페이지에서는 BigQuery를 사용하여 Drive에 저장된 데이터를 쿼리하는 방법을 설명합니다.

BigQuery는 개인의 Drive 파일과 공유 파일 모두를 대상으로 하는 쿼리를 지원합니다. Drive에 대한 자세한 내용은 G Suite 학습 센터를 참조하세요.

Drive에서 다음 형식의 파일을 쿼리할 수 있습니다.

  • 쉼표로 구분된 값(CSV)
  • 줄바꿈으로 구분된 JSON
  • Avro
  • 스프레드시트

Drive 외부 데이터 소스를 쿼리하려면 데이터의 Drive브 URI 경로를 제공하고 데이터 소스를 참조하는 외부 테이블을 만듭니다. Drive 데이터 소스를 참조하는 데 사용되는 테이블은 영구 테이블이거나 임시 테이블일 수 있습니다.

Drive URI 검색

Drive 데이터 소스의 외부 테이블을 만들려면 Drive브 URI를 제공해야 합니다. Drive URI를 검색하려면 다음 안내를 따르세요.

  1. 드라이브로 이동합니다.

  2. 파일을 마우스 오른쪽 버튼으로 클릭하고 링크 복사를 선택합니다. URI는 다음과 같이 표시됩니다.

    • https://docs.google.com/spreadsheets/d/FILE_ID

      또는

    • https://drive.google.com/file/d/FILE_ID

    여기서 FILE_ID는 Drive 파일의 영숫자 ID입니다.

또는 파일을 열어서 URI를 검색할 수 있습니다. 예를 들어 Sheets 파일의 URI를 검색하려면 다음 안내를 따르세요.

  1. Sheets로 이동합니다.

  2. 스프레드시트를 열고 브라우저 주소 표시줄의 URI를 복사합니다.

Drive 액세스 사용 설정하기

쿼리를 실행하거나 제휴 소스를 정의할 때 Drive 내에서 호스팅되는 데이터에 액세스하려면 추가 OAuth 범위가 필요합니다. 기본적으로 사용 설정되지 않지만 다음 메커니즘을 통해 Cloud Console, bq 명령줄 도구 또는 API에서 포함될 수 있습니다.

Console

Cloud Console에서 영구 테이블을 만들 때 웹 기반 인증 단계를 따르세요. 메시지가 표시되면 허용을 클릭하여 BigQuery 클라이언트 도구에 Drive에 대한 액세스 권한을 부여합니다.

gcloud

Drive 액세스를 사용 설정하려면 다음 안내를 따르세요.

  1. 다음 명령어를 입력하여 최신 버전의 gcloud 명령줄 도구를 갖고 있는지 확인합니다.

    gcloud components update
    
  2. 다음 명령어를 입력하여 Drive로 인증합니다.

    gcloud auth login --enable-gdrive-access
    

API

BigQuery API를 사용하는 경우 BigQuery의 범위 외에도 Drive의 OAuth 범위를 요청합니다.

Python

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

from google.cloud import bigquery
import google.auth

# Create credentials with Drive & BigQuery API scopes.
# Both APIs must be enabled for your project before running this code.
credentials, project = google.auth.default(
    scopes=[
        "https://www.googleapis.com/auth/drive",
        "https://www.googleapis.com/auth/bigquery",
    ]
)

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

자바

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

GoogleCredentials credentials =
    ServiceAccountCredentials.getApplicationDefault()
        .createScoped(
            ImmutableSet.of(
                "https://www.googleapis.com/auth/bigquery",
                "https://www.googleapis.com/auth/drive"));

// 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.newBuilder().setCredentials(credentials).build().getService();

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

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

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

영구 외부 테이블을 사용하여 Drive 데이터 쿼리

필수 권한 및 범위

영구 테이블을 사용하여 Drive에서 외부 데이터를 쿼리할 경우 프로젝트 수준 이상에서 쿼리 작업을 실행할 수 있는 권한, 외부 데이터를 가리키는 테이블을 만들 수 있는 권한, 테이블 데이터에 액세스할 수 있는 권한이 필요합니다. 외부 데이터가 Drive에 저장된 경우에는 외부 테이블과 연결된 Drive 파일에 액세스할 수 있는 권한도 필요합니다.

BigQuery 권한

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

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

다음과 같은 사전 정의된 IAM 역할에는 bigquery.tables.create 권한과 bigquery.tables.getData 권한이 모두 포함되어 있습니다.

  • bigquery.dataEditor
  • bigquery.dataOwner
  • bigquery.admin

다음과 같은 사전 정의된 IAM 역할에는 bigquery.jobs.create 권한이 포함되어 있습니다.

  • bigquery.user
  • bigquery.jobUser
  • bigquery.admin

또한 bigquery.datasets.create 권한이 있는 사용자는 데이터 세트를 만들 때 해당 데이터 세트에 대한 bigquery.dataOwner 액세스 권한을 부여받습니다. bigquery.dataOwner 액세스 권한이 있으면 사용자는 데이터 세트에 외부 테이블을 만들 수 있지만 데이터를 쿼리하려면 여전히 bigquery.jobs.create 권한이 필요합니다.

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

Drive 권한

Drive에서 외부 데이터를 쿼리하려면 최소한 외부 테이블과 연결된 Drive 파일에 대한 View 액세스 권한을 부여받아야 합니다.

Compute Engine 인스턴스의 범위

Compute Engine 인스턴스를 만들 때 인스턴스의 범위 목록을 지정할 수 있습니다. 범위는 Drive를 포함한 Google Cloud 제품에 대한 인스턴스의 액세스를 제어합니다. VM에서 실행되는 애플리케이션은 서비스 계정을 사용하여 Google Cloud APIs를 호출합니다.

서비스 계정으로 실행되도록 Compute Engine 인스턴스를 설정하고 서비스 계정이 Drive 데이터 소스에 연결된 외부 테이블에 액세스하는 경우 인스턴스에 Drive용 OAuth 범위(https://www.googleapis.com/auth/drive)를 추가해야 합니다.

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

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

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

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

BigQuery에 영구 외부 테이블을 만들 때 스키마 정보를 지정하는 방법에는 세 가지가 있습니다.

  • tables.insert API 메서드를 사용하여 영구 외부 테이블을 만드는 경우 스키마 정의 및 ExternalDataConfiguration이 포함된 테이블 리소스를 만듭니다. autodetect 매개변수를 true로 설정하여 지원되는 데이터 소스에 스키마 자동 감지를 사용 설정합니다.
  • bq 명령줄 도구를 사용하여 영구 외부 테이블을 만드는 경우, 테이블 정의 파일을 사용하거나, 고유 스키마 파일을 만들어서 사용하거나, bq 도구로 스키마 인라인에 들어갈 수 있습니다. 테이블 정의 파일을 만들 때 지원되는 데이터 소스에 대한 스키마 자동 감지를 사용 설정할 수 있습니다.
  • Cloud Console을 사용하여 영구 외부 테이블을 만드는 경우 수동으로 테이블 스키마를 입력하거나 지원되는 데이터 소스에 대해 스키마 자동 감지를 사용할 수 있습니다.

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

Console

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

BigQuery로 이동

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

  2. 세부정보 패널에서 테이블 만들기를 클릭합니다.

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

    • 다음 항목으로 테이블 만들기에서 드라이브를 선택합니다.

    • Drive URI 선택 필드에 Drive브 URI를 입력합니다. Drive URI에는 와일드 카드가 지원되지 않습니다.

    • 파일 형식에 사용 중인 데이터의 형식을 선택합니다. Drive 데이터에 유효한 형식은 다음과 같습니다.

      • 쉼표로 구분된 값(CSV)
      • 줄바꿈으로 구분된 JSON
      • Avro
      • 스프레드시트
  4. (선택사항) Sheets를 선택한 경우 시트 범위(선택사항) 상자에 쿼리할 시트 및 셀 범위를 지정합니다. 시트 이름을 지정하거나 셀 범위로 sheet_name!top_left_cell_id:bottom_right_cell_id를 지정할 수 있습니다(예: 'Sheet1! A1:B20'). 시트 범위를 지정하지 않으면 파일의 첫 번째 시트가 사용됩니다.

  5. 테이블 만들기 페이지의 대상 섹션에서 다음을 수행합니다. * 데이터 세트 이름에서 적절한 데이터 세트를 선택하고 테이블 이름 필드에 BigQuery에서 만들고 있는 테이블의 이름을 입력합니다.

    데이터 세트 선택

    • 테이블 유형외부 테이블로 설정되어 있는지 확인합니다.
  6. 스키마 섹션에 스키마 정의를 입력합니다.

    • JSON 또는 CSV 파일의 경우 자동 감지 옵션을 선택하여 스키마 자동 감지를 사용 설정할 수 있습니다. Datastore 내보내기, Firestore 내보내기, Avro 파일에는 자동 감지를 사용할 수 없습니다. 이러한 파일 유형의 스키마 정보는 자체 설명적 소스 데이터에서 자동으로 검색됩니다.
    • 다음과 같이 스키마 정보를 직접 입력합니다.
      • 텍스트로 편집을 사용 설정하고 테이블 스키마를 JSON 배열로 입력합니다. 참고: bq 명령줄 도구에 bq show --format=prettyjson DATASET.TABLE 명령어를 입력하여 기존 테이블 스키마를 JSON 형식으로 볼 수 있습니다.
      • 필드 추가를 사용하여 스키마를 수동으로 입력합니다.
  7. 테이블 만들기를 클릭합니다.

  8. 필요한 경우 계정을 선택한 후 허용을 클릭하여 BigQuery 클라이언트 도구에 Drive에 대한 액세스 권한을 부여합니다.

그러면 외부 데이터 소스에 대한 제한이 적용되는 기본 BigQuery 테이블처럼 테이블에 대해 쿼리를 실행할 수 있습니다.

쿼리가 완료되면 결과를 CSV 또는 JSON으로 다운로드하거나, 테이블로 저장하거나, Sheets에 저장할 수 있습니다. 자세한 내용은 데이터 다운로드, 저장, 내보내기를 참조하세요.

bq

bq 명령줄 도구에서 bq mk 명령어를 사용하여 테이블을 만듭니다. bq 명령줄 도구를 사용하여 외부 데이터 소스에 연결된 테이블을 만들 때 다음을 사용하여 테이블 스키마를 식별할 수 있습니다.

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

테이블 정의 파일을 사용하여 Drive 데이터 소스에 연결된 영구 테이블을 만들려면 다음 명령어를 입력합니다.

bq mk \
--external_table_definition=DEFINITION_FILE \
DATASET.TABLE

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

  • DEFINITION_FILE은 로컬 머신에 있는 테이블 정의 파일 경로입니다.
  • DATASET는 테이블이 포함된 데이터 세트의 이름입니다.
  • TABLE은 만드는 테이블의 이름입니다.

예를 들어 다음 명령어는 mytable_def라는 테이블 정의 파일을 사용하여 mytable이라는 영구 테이블을 만듭니다.

bq mk --external_table_definition=/tmp/mytable_def mydataset.mytable

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

bq mk \
--external_table_definition=SCHEMA@SOURCE_FORMAT=DRIVE_URI \
DATASET.TABLE

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

  • SCHEMAFIELD:DATA_TYPE,FIELD:DATA_TYPE 형식의 스키마 정의입니다.
  • SOURCE_FORMATCSV, NEWLINE_DELIMITED_JSON, AVRO 또는 GOOGLE_SHEETS입니다.
  • DRIVE_URIDrive URI입니다.
  • DATASET는 테이블이 포함된 데이터세트의 이름입니다.
  • TABLE은 만드는 테이블의 이름입니다.

예를 들어 다음 명령어는 스키마 정의 Region:STRING,Quarter:STRING,Total_sales:INTEGER를 사용하여 Drive에 저장된 Sheets 파일에 연결된 sales라는 영구 테이블을 만듭니다.

bq mk \
--external_table_definition=Region:STRING,Quarter:STRING,Total_sales:INTEGER@GOOGLE_SHEETS=https://drive.google.com/open?id=1234_AbCD12abCd \
mydataset.sales

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

bq mk \
--external_table_definition=SCHEMA_FILE@SOURCE_FORMAT=DRIVE_URI \
DATASET.TABLE

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

  • SCHEMA_FILE은 로컬 머신에 있는 JSON 스키마 파일의 경로입니다.
  • SOURCE_FORMATCSV, NEWLINE_DELIMITED_JSON, AVRO 또는 GOOGLE_SHEETS입니다.
  • DRIVE_URIDrive URI입니다.
  • DATASET는 테이블이 포함된 데이터세트의 이름입니다.
  • TABLE은 만드는 테이블의 이름입니다.

예를 들어 다음 명령어는 /tmp/sales_schema.json 스키마 파일을 사용하여 Drive에 저장된 CSV 파일에 연결된 sales라는 테이블을 만듭니다.

bq mk \
--external_table_definition=/tmp/sales_schema.json@CSV=https://drive.google.com/open?id=1234_AbCD12abCd \
mydataset.sales

영구 테이블을 만든 후에는 외부 데이터 소스에 대한 제한이 적용되는 기본 BigQuery 테이블처럼 테이블에 대해 쿼리를 실행할 수 있습니다.

쿼리가 완료되면 결과를 CSV 또는 JSON으로 다운로드하거나, 테이블로 저장하거나, Sheets에 저장할 수 있습니다. 자세한 내용은 데이터 다운로드, 저장, 내보내기를 참조하세요.

API

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

Python

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

from google.cloud import bigquery
import google.auth

# Create credentials with Drive & BigQuery API scopes.
# Both APIs must be enabled for your project before running this code.
credentials, project = google.auth.default(
    scopes=[
        "https://www.googleapis.com/auth/drive",
        "https://www.googleapis.com/auth/bigquery",
    ]
)

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

# TODO(developer): Set dataset_id to the ID of the dataset to fetch.
# dataset_id = "your-project.your_dataset"

# Configure the external data source.
dataset = client.get_dataset(dataset_id)
table_id = "us_states"
schema = [
    bigquery.SchemaField("name", "STRING"),
    bigquery.SchemaField("post_abbr", "STRING"),
]
table = bigquery.Table(dataset.table(table_id), schema=schema)
external_config = bigquery.ExternalConfig("GOOGLE_SHEETS")
# Use a shareable link or grant viewing access to the email address you
# used to authenticate with BigQuery (this example Sheet is public).
sheet_url = (
    "https://docs.google.com/spreadsheets"
    "/d/1i_QCL-7HcSyUZmIbP9E6lO_T5u3HnpLe7dnpHaijg_E/edit?usp=sharing"
)
external_config.source_uris = [sheet_url]
external_config.options.skip_leading_rows = 1  # Optionally skip header row.
external_config.options.range = (
    "us-states!A20:B49"  # Optionally set range of the sheet to query from.
)
table.external_data_configuration = external_config

# Create a permanent table linked to the Sheets file.
table = client.create_table(table)  # Make an 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)  # Make an API request.

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

자바

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

import com.google.auth.oauth2.GoogleCredentials;
import com.google.auth.oauth2.ServiceAccountCredentials;
import com.google.cloud.bigquery.BigQuery;
import com.google.cloud.bigquery.BigQueryException;
import com.google.cloud.bigquery.BigQueryOptions;
import com.google.cloud.bigquery.ExternalTableDefinition;
import com.google.cloud.bigquery.Field;
import com.google.cloud.bigquery.GoogleSheetsOptions;
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;
import com.google.common.collect.ImmutableSet;
import java.io.IOException;

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

  public static void main(String[] args) {
    // TODO(developer): Replace these variables before running the sample.
    String datasetName = "MY_DATASET_NAME";
    String tableName = "MY_TABLE_NAME";
    String sourceUri =
        "https://docs.google.com/spreadsheets/d/1i_QCL-7HcSyUZmIbP9E6lO_T5u3HnpLe7dnpHaijg_E/edit?usp=sharing";
    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);
    queryExternalSheetsPerm(datasetName, tableName, sourceUri, schema, query);
  }

  public static void queryExternalSheetsPerm(
      String datasetName, String tableName, String sourceUri, Schema schema, String query) {
    try {

      // Create credentials with Drive & BigQuery API scopes.
      // Both APIs must be enabled for your project before running this code.
      GoogleCredentials credentials =
          ServiceAccountCredentials.getApplicationDefault()
              .createScoped(
                  ImmutableSet.of(
                      "https://www.googleapis.com/auth/bigquery",
                      "https://www.googleapis.com/auth/drive"));

      // 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.newBuilder().setCredentials(credentials).build().getService();

      // Skip header row in the file.
      GoogleSheetsOptions sheetsOptions =
          GoogleSheetsOptions.newBuilder()
              .setSkipLeadingRows(1) // Optionally skip header row.
              .setRange("us-states!A20:B49") // Optionally set range of the sheet to query from.
              .build();

      TableId tableId = TableId.of(datasetName, tableName);
      // Create a permanent table linked to the Sheets file.
      ExternalTableDefinition externalTable =
          ExternalTableDefinition.newBuilder(sourceUri, sheetsOptions).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 | IOException e) {
      System.out.println("Query not performed \n" + e.toString());
    }
  }
}

임시 테이블을 사용하여 Drive 데이터 쿼리하기

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

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

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

필수 권한

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

BigQuery 권한

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

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

다음과 같은 사전 정의된 IAM 역할에는 bigquery.tables.getData 권한이 포함되어 있습니다.

  • bigquery.dataEditor
  • bigquery.dataOwner
  • bigquery.admin

다음과 같은 사전 정의된 IAM 역할에는 bigquery.jobs.create 권한이 포함되어 있습니다.

  • bigquery.user
  • bigquery.jobUser
  • bigquery.admin

또한 bigquery.datasets.create 권한이 있는 사용자는 데이터 세트를 만들 때 해당 데이터 세트에 대한 bigquery.dataOwner 액세스 권한을 부여받습니다. bigquery.dataOwner 액세스 권한이 있으면 사용자는 데이터 세트에 외부 테이블을 만들고 액세스할 수 있지만 데이터를 쿼리하려면 여전히 bigquery.jobs.create 권한이 필요합니다.

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

Drive 권한

Drive에서 외부 데이터를 쿼리하려면 최소한 외부 테이블과 연결된 Drive 파일에 대한 View 액세스 권한을 부여받아야 합니다.

임시 테이블 만들기 및 쿼리

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

bq

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

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

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

bq --location=LOCATION query \
--external_table_definition=TABLE::DEFINITION_FILE \
'QUERY'

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

  • LOCATION은 사용자의 위치입니다. --location 플래그는 선택사항입니다.
  • 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=DRIVE_URI \
'QUERY'

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

  • LOCATION은 사용자의 위치입니다. --location 플래그는 선택사항입니다.
  • TABLE은 만들고 있는 임시 테이블의 이름입니다.
  • SCHEMAFIELD:DATA_TYPE,FIELD:DATA_TYPE 형식의 인라인 스키마 정의입니다.
  • SOURCE_FORMATCSV, NEWLINE_DELIMITED_JSON, AVRO 또는 GOOGLE_SHEETS입니다.
  • DRIVE_URIDrive URI입니다.
  • QUERY는 임시 테이블에 제출하는 쿼리입니다.

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

bq --location=US query \
--external_table_definition=sales::Region:STRING,Quarter:STRING,Total_sales:INTEGER@CSV=https://drive.google.com/open?id=1234_AbCD12abCd \
'SELECT
   Region,Total_sales
 FROM
   sales'

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

bq --location=LOCATION query \
--external_table_definition=SCHEMA_FILE@SOURCE_FORMT=DRIVE_URI \
'QUERY'

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

  • LOCATION은 사용자의 위치입니다. --location 플래그는 선택사항입니다.
  • SCHEMA_FILE은 로컬 머신에 있는 JSON 스키마 파일의 경로입니다.
  • SOURCE_FILECSV, NEWLINE_DELIMITED_JSON, AVRO 또는 GOOGLE_SHEETS입니다.
  • DRIVE_URIDrive URI입니다.
  • QUERY는 임시 테이블에 제출하는 쿼리입니다.

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

bq query \
--external_table_definition=sales::/tmp/sales_schema.json@CSV=https://drive.google.com/open?id=1234_AbCD12abCd \
'SELECT
   Total_sales
 FROM
   sales'

API

Python

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

from google.cloud import bigquery
import google.auth

# Create credentials with Drive & BigQuery API scopes.
# Both APIs must be enabled for your project before running this code.
credentials, project = google.auth.default(
    scopes=[
        "https://www.googleapis.com/auth/drive",
        "https://www.googleapis.com/auth/bigquery",
    ]
)

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

# Configure the external data source and query job.
external_config = bigquery.ExternalConfig("GOOGLE_SHEETS")

# Use a shareable link or grant viewing access to the email address you
# used to authenticate with BigQuery (this example Sheet is public).
sheet_url = (
    "https://docs.google.com/spreadsheets"
    "/d/1i_QCL-7HcSyUZmIbP9E6lO_T5u3HnpLe7dnpHaijg_E/edit?usp=sharing"
)
external_config.source_uris = [sheet_url]
external_config.schema = [
    bigquery.SchemaField("name", "STRING"),
    bigquery.SchemaField("post_abbr", "STRING"),
]
external_config.options.skip_leading_rows = 1  # Optionally skip header row.
external_config.options.range = (
    "us-states!A20:B49"  # Optionally set range of the sheet to query from.
)
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.

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

자바

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

import com.google.auth.oauth2.GoogleCredentials;
import com.google.auth.oauth2.ServiceAccountCredentials;
import com.google.cloud.bigquery.BigQuery;
import com.google.cloud.bigquery.BigQueryException;
import com.google.cloud.bigquery.BigQueryOptions;
import com.google.cloud.bigquery.ExternalTableDefinition;
import com.google.cloud.bigquery.Field;
import com.google.cloud.bigquery.GoogleSheetsOptions;
import com.google.cloud.bigquery.QueryJobConfiguration;
import com.google.cloud.bigquery.Schema;
import com.google.cloud.bigquery.StandardSQLTypeName;
import com.google.cloud.bigquery.TableResult;
import com.google.common.collect.ImmutableSet;
import java.io.IOException;

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

  public static void main(String[] args) {
    // TODO(developer): Replace these variables before running the sample.
    String tableName = "MY_TABLE_NAME";
    String sourceUri =
        "https://docs.google.com/spreadsheets/d/1i_QCL-7HcSyUZmIbP9E6lO_T5u3HnpLe7dnpHaijg_E/edit?usp=sharing";
    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);
    queryExternalSheetsTemp(tableName, sourceUri, schema, query);
  }

  public static void queryExternalSheetsTemp(
      String tableName, String sourceUri, Schema schema, String query) {
    try {

      // Create credentials with Drive & BigQuery API scopes.
      // Both APIs must be enabled for your project before running this code.
      GoogleCredentials credentials =
          ServiceAccountCredentials.getApplicationDefault()
              .createScoped(
                  ImmutableSet.of(
                      "https://www.googleapis.com/auth/bigquery",
                      "https://www.googleapis.com/auth/drive"));

      // 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.newBuilder().setCredentials(credentials).build().getService();

      // Skip header row in the file.
      GoogleSheetsOptions sheetsOptions =
          GoogleSheetsOptions.newBuilder()
              .setSkipLeadingRows(1) // Optionally skip header row.
              .setRange("us-states!A20:B49") // Optionally set range of the sheet to query from.
              .build();

      // Configure the external data source and query job.
      ExternalTableDefinition externalTable =
          ExternalTableDefinition.newBuilder(sourceUri, sheetsOptions).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 | IOException e) {
      System.out.println("Query not performed \n" + e.toString());
    }
  }
}

_FILE_NAME 유사 열

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

_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을 사용하거나 Cloud SDK에서 기본 프로젝트를 설정하는 경우에는 이 플래그가 필요 없음).
  • dataset는 영구 외부 테이블이 저장되는 데이터 세트 이름입니다.
  • table_name은 영구 외부 테이블 이름입니다.

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