Google Drive 외부 테이블 만들기

이 문서에서는 Google Drive에 저장된 데이터에서 외부 테이블을 만드는 방법을 설명합니다.

BigQuery는 개인의 Drive 파일과 공유 파일 모두를 대상으로 외부 테이블을 지원합니다. Drive에 대한 자세한 내용은 Drive 교육 및 도움말을 참조하세요.

다음 형식의 Drive 파일에 외부 테이블을 만들 수 있습니다.

• 쉼표로 구분된 값(CSV)
• 줄바꿈으로 구분된 JSON
• Avro
• Google Sheets

시작하기 전에

외부 테이블을 만들기 전에 몇 가지 정보를 수집하고 테이블을 만들 권한이 있는지 확인합니다.

Drive URI 검색

Google Drive 데이터 소스의 외부 테이블을 만들려면 Drive URI를 제공해야 합니다. Drive 데이터의 URL에서 직접 Drive URI를 검색할 수 있습니다.

URI 형식

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

  또는

• https://drive.google.com/open?id=FILE_ID

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

Drive 액세스 인증 및 사용 설정

Drive 내에서 호스팅되는 데이터에 액세스하려면 추가 OAuth 범위가 필요합니다. BigQuery에 인증하고 드라이브 액세스를 사용 설정하려면 다음을 수행합니다.

콘솔

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

gcloud

1. In the Google Cloud console, activate Cloud Shell.

   Activate Cloud Shell

   At the bottom of the Google Cloud console, a Cloud Shell session starts and displays a command-line prompt. Cloud Shell is a shell environment with the Google Cloud CLI already installed and with values already set for your current project. It can take a few seconds for the session to initialize.

2. 다음 명령어를 입력해서 Google Cloud CLI가 최신 버전인지 확인합니다.

   gcloud components update
   

3. 다음 명령어를 입력하여 Drive로 인증합니다.

   gcloud auth login --enable-gdrive-access
   

참고: Cloud Shell을 사용해서 Drive 데이터에 액세스할 경우에는 Google Cloud CLI 업데이트 또는 Drive에 인증을 수행할 필요가 없습니다.

API

BigQuery의 범위 외에도 적절한 Drive의 OAuth 범위를 요청합니다.

1. gcloud auth login --enable-gdrive-access 명령어를 실행하여 로그인합니다.
2. gcloud auth print-access-token 명령어를 실행하여 API에 사용되는 Drive 범위의 OAuth 액세스 토큰을 가져옵니다.

Python

1. OAuth 클라이언트 ID를 만듭니다.

2. 다음을 수행하여 로컬 환경에서 필요한 범위로 애플리케이션 기본 사용자 인증 정보(ADC)를 설정합니다.

   1. Google Cloud CLI를 설치한 후 다음 명령어를 실행하여 초기화합니다.

      gcloud init

   2. Google 계정의 로컬 인증 사용자 인증 정보를 만듭니다.

      gcloud auth application-default login \
          --client-id-file=CLIENT_ID_FILE \
          --scopes=https://www.googleapis.com/auth/drive,https://www.googleapis.com/auth/cloud-platform

      CLIENT_ID_FILE을 OAuth 클라이언트 ID가 포함된 파일로 바꿉니다.

      자세한 내용은 gcloud CLI를 사용하여 제공된 사용자 인증 정보를 참고하세요.

자바

1. OAuth 클라이언트 ID를 만듭니다.

2. 다음을 수행하여 로컬 환경에서 필요한 범위로 애플리케이션 기본 사용자 인증 정보(ADC)를 설정합니다.

   1. Google Cloud CLI를 설치한 후 다음 명령어를 실행하여 초기화합니다.

      gcloud init

   2. Google 계정의 로컬 인증 사용자 인증 정보를 만듭니다.

      gcloud auth application-default login \
          --client-id-file=CLIENT_ID_FILE \
          --scopes=https://www.googleapis.com/auth/drive,https://www.googleapis.com/auth/cloud-platform

      CLIENT_ID_FILE을 OAuth 클라이언트 ID가 포함된 파일로 바꿉니다.

      자세한 내용은 gcloud CLI를 사용하여 제공된 사용자 인증 정보를 참고하세요.

필요한 역할

외부 테이블을 만들려면 bigquery.tables.create BigQuery Identity and Access Management(IAM) 권한이 필요합니다.

다음과 같이 사전 정의된 각 Identity and Access Management 역할에 이 권한이 포함되어 있습니다.

• BigQuery 데이터 편집자(roles/bigquery.dataEditor)
• BigQuery 데이터 소유자(roles/bigquery.dataOwner)
• BigQuery 관리자(roles/bigquery.admin)

이러한 역할의 주 구성원이 아닌 경우 관리자에게 액세스 권한을 부여하거나 외부 테이블을 만들도록 요청하세요.

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

외부 테이블 만들기

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

• Google Cloud 콘솔 사용
• bq 명령줄 도구의 mk 명령어 사용
• tables.insert API 메서드를 사용할 때 ExternalDataConfiguration 생성
• 클라이언트 라이브러리 사용

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

콘솔

1. Google Cloud 콘솔에서 BigQuery 페이지를 엽니다.

BigQuery로 이동

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

2. 작업 옵션을 펼치고 열기를 클릭합니다.

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

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

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

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

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

     • 쉼표로 구분된 값(CSV)
     • 줄바꿈으로 구분된 JSON
     • Avro
     • 스프레드시트
     참고: Google Cloud 콘솔에서 Avro 또는 Datastore 백업 파일 형식은 자동으로 감지되므로, 이러한 파일을 선택하면 자동 감지 옵션이 사라집니다.

5. (선택사항) Sheets를 선택한 경우 시트 범위(선택사항) 상자에 쿼리할 시트 및 셀 범위를 지정합니다. 시트 이름을 지정하거나 셀 범위로 sheet_name!top_left_cell_id:bottom_right_cell_id를 지정할 수 있습니다(예: 'Sheet1! A1:B20'). 시트 범위를 지정하지 않으면 파일의 첫 번째 시트가 사용됩니다.

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

   • 데이터 세트 이름에서 적절한 데이터 세트를 선택하고, 테이블 이름 필드에 BigQuery에서 만들려는 테이블의 이름을 입력합니다.

     

   • 테이블 유형이 외부 테이블로 설정되어 있는지 확인합니다.

7. 스키마 섹션에 스키마 정의를 입력합니다.

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

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

9. 필요한 경우 계정을 선택한 후 허용을 클릭하여 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

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

• SCHEMA는 FIELD:DATA_TYPE,FIELD:DATA_TYPE 형식의 스키마 정의입니다.
• SOURCE_FORMAT은 CSV, NEWLINE_DELIMITED_JSON, AVRO 또는 GOOGLE_SHEETS입니다.
• DRIVE_URI는 Drive 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_FORMAT은 CSV, NEWLINE_DELIMITED_JSON, AVRO 또는 GOOGLE_SHEETS입니다.
• DRIVE_URI는 Drive URI입니다.
• DATASET는 테이블이 포함된 데이터 세트의 이름입니다.
• TABLE은 만드는 테이블의 이름입니다.

테이블 정의 파일에 Sheets별 구성이 포함된 경우 선행 행을 건너뛰고 정의된 시트 범위를 지정할 수 있습니다.

다음 예에서는 /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

from google.cloud import bigquery
import google.auth

credentials, project = google.auth.default()

# 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]
options = external_config.google_sheets_options
assert options is not None
options.skip_leading_rows = 1  # Optionally skip header row.
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)

results = client.query_and_wait(sql)  # Make an API request.

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

자바

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 {

      GoogleCredentials credentials =
          ServiceAccountCredentials.getApplicationDefault();

      // 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 데이터 쿼리를 참조하세요.

_FILE_NAME 유사 열

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

_FILE_NAME 열 이름은 예약되어 있으므로, 어떤 테이블에도 이 이름으로 열을 만들 수 없습니다.