Membuat tabel eksternal Google Drive

Dokumen ini menjelaskan cara membuat tabel eksternal berdasarkan data yang disimpan di Google Drive.

BigQuery mendukung tabel eksternal baik pada file Drive pribadi maupun file bersama. Untuk informasi selengkapnya tentang Drive, lihat Pelatihan dan bantuan Drive.

Anda dapat membuat tabel eksternal di atas file di Drive yang memiliki format berikut:

Nilai yang dipisahkan koma (CSV)
JSON yang dibatasi baris baru
Avro
Google Spreadsheet

Sebelum memulai

Sebelum membuat tabel eksternal, kumpulkan beberapa informasi dan pastikan Anda memiliki izin untuk membuat tabel tersebut.

Mengambil URI Drive

Guna membuat tabel eksternal untuk sumber data Google Drive, Anda harus memberikan URI Drive. Anda dapat mengambil URI Drive langsung dari URL data Drive:

Format URI

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

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

dengan FILE_ID adalah ID alfanumerik untuk file Drive Anda.

Mengaktifkan akses Drive

Perlu cakupan OAuth tambahan untuk mengakses data yang dihosting dalam Drive. Meskipun tidak diaktifkan secara default, alat ini dapat disertakan dalam Google Cloud Console, di alat command line bq, atau melalui API melalui mekanisme berikut:

Konsol

Ikuti langkah-langkah autentikasi berbasis web saat membuat tabel permanen di Konsol Google Cloud. Saat diminta, klik Izinkan untuk memberi BigQuery Client Tools akses ke Drive.

gcloud

Untuk mengaktifkan akses Drive:

Masukkan perintah berikut untuk memastikan Anda memiliki Google Cloud CLI versi terbaru.
```
gcloud components update
```
Masukkan perintah berikut untuk melakukan autentikasi dengan Drive.
```
gcloud auth login --enable-gdrive-access
```

API

Jika Anda menggunakan BigQuery API, minta cakupan OAuth untuk Drive selain cakupan untuk BigQuery.

Python

Sebelum mencoba contoh ini, ikuti petunjuk penyiapan Python di Panduan memulai BigQuery menggunakan library klien. Untuk mengetahui informasi selengkapnya, lihat Dokumentasi referensi BigQuery Python API.

Untuk melakukan autentikasi ke BigQuery, siapkan Kredensial Default Aplikasi. Untuk mengetahui informasi selengkapnya, lihat Menyiapkan autentikasi untuk library klien.

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.
#
# If you are using credentials from gcloud, you must authorize the
# application first with the following command:
#
# gcloud auth application-default login \
#   --scopes=https://www.googleapis.com/auth/drive,https://www.googleapis.com/auth/cloud-platform
credentials, project = google.auth.default(
    scopes=[
        "https://www.googleapis.com/auth/drive",
        "https://www.googleapis.com/auth/cloud-platform",
    ]
)

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

Java

Sebelum mencoba contoh ini, ikuti petunjuk penyiapan Java di Panduan memulai BigQuery menggunakan library klien. Untuk mengetahui informasi selengkapnya, lihat Dokumentasi referensi BigQuery Java API.

Untuk melakukan autentikasi ke BigQuery, siapkan Kredensial Default Aplikasi. Untuk mengetahui informasi selengkapnya, lihat Menyiapkan autentikasi untuk library klien.

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();

Peran yang diperlukan

Untuk membuat tabel eksternal, Anda memerlukan izin bigquery.tables.create Identity and Access Management (IAM) BigQuery.

Setiap peran Identity and Access Management yang telah ditetapkan berikut mencakup izin ini:

BigQuery Data Editor (roles/bigquery.dataEditor)
BigQuery Data Owner (roles/bigquery.dataOwner)
BigQuery Admin (roles/bigquery.admin)

Jika Anda bukan akun utama dI salah satu peran ini, minta administrator untuk memberi Anda akses atau membuat tabel eksternal.

Untuk mengetahui informasi lebih lanjut tentang peran dan izin Identity and Access Management di BigQuery, lihat Peran dan izin yang telah ditetapkan sebelumnya.

Membuat tabel eksternal

Anda dapat membuat tabel permanen yang ditautkan ke sumber data eksternal dengan:

Menggunakan konsol Google Cloud
Menggunakan perintah mk alat command line bq
Membuat ExternalDataConfiguration saat Anda menggunakan metode API tables.insert
Menggunakan library klien

Untuk membuat tabel eksternal:

Konsol

Di konsol Google Cloud, buka halaman BigQuery.

Buka BigQuery

Di panel Explorer, luaskan project Anda dan pilih set data.
Luaskan opsi Actions dan klik Open.
Di panel detail, klik Create table .
Di halaman Buat Tabel, di bagian Sumber:
- Untuk Buat tabel dari, pilih Drive.
- Di kolom Pilih URI Drive, masukkan URI Drive. Perhatikan bahwa karakter pengganti tidak didukung untuk URI Drive.
- Untuk File format, pilih format data. Format yang valid untuk data Drive meliputi:
  - Nilai yang dipisahkan koma (CSV)
  - JSON yang dibatasi baris baru
  - Avro
  - Spreadsheet
  Catatan: Di Google Cloud Console, memilih cadangan Avro atau Datastore akan menyembunyikan opsi Deteksi otomatis karena opsi ini jenis file adalah deskripsi mandiri.
(Opsional) Jika Anda memilih Spreadsheet, di kotak Rentang sheet (Opsional), tentukan sheet dan rentang sel yang akan dikueri. Anda dapat menentukan nama sheet, atau menentukan sheet_name!top_left_cell_id:bottom_right_cell_id untuk rentang sel; misalnya, "Sheet1!A1:B20". Jika Sheet range tidak ditentukan, sheet pertama dalam file akan digunakan.
Di halaman Buat Tabel, di bagian Sumber:
- Untuk Nama set data, pilih set data yang sesuai, lalu di kolom Nama tabel, masukkan nama tabel yang Anda buat di BigQuery.
- Pastikan Jenis tabel ditetapkan ke Tabel eksternal.
Di bagian Schema, masukkan definisi schema.
- Untuk file JSON atau CSV, Anda dapat mencentang opsi Deteksi otomatis untuk mengaktifkan deteksi otomatis skema. Auto-detect tidak tersedia untuk ekspor Datastore, ekspor Firestore, dan file Avro. Informasi skema untuk jenis file ini otomatis diambil dari data sumber yang mendeskripsikan sendiri.
- Masukkan informasi skema secara manual dengan:
  - Mengaktifkan Edit sebagai text dan memasukkan skema tabel sebagai array JSON. Catatan: Anda dapat melihat skema tabel yang ada dalam format JSON dengan memasukkan perintah berikut di alat command line bq: bq show --format=prettyjson DATASET.TABLE.
  - Menggunakan Tambahkan kolom untuk memasukkan skema secara manual.
Klik Buat tabel.
Jika perlu, pilih akun Anda, lalu klik Izinkan untuk memberi alat klien BigQuery akses ke Drive.

Kemudian, Anda dapat menjalankan kueri terhadap tabel seolah-olah tabel tersebut adalah tabel BigQuery standar, dengan tunduk pada batasan pada sumber data eksternal.

Setelah kueri selesai, Anda dapat mendownload hasilnya sebagai CSV atau JSON, menyimpan hasilnya sebagai tabel, atau menyimpan hasilnya ke Spreadsheet. Lihat Mendownload, menyimpan, dan mengekspor data untuk informasi selengkapnya.

bq

Anda dapat membuat tabel di alat command line bq menggunakan perintah bq mk. Saat menggunakan alat command line bq untuk membuat tabel yang ditautkan ke sumber data eksternal, Anda dapat mengidentifikasi skema tabel menggunakan:

File definisi tabel (disimpan di komputer lokal Anda)
Definisi skema inline
File skema JSON (disimpan di komputer lokal Anda)

Untuk membuat tabel permanen yang ditautkan ke sumber data Drive Anda menggunakan file definisi tabel, masukkan perintah berikut.

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

Dengan keterangan:

DEFINITION_FILE adalah jalur ke file definisi tabel di mesin lokal Anda.
DATASET adalah nama set data yang berisi tabel.
TABLE adalah nama tabel yang Anda buat.

Misalnya, perintah berikut akan membuat tabel permanen bernama mytable menggunakan file definisi tabel bernama mytable_def.

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

Untuk membuat tabel permanen yang ditautkan ke sumber data eksternal menggunakan definisi skema inline, masukkan perintah berikut.

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

Dengan keterangan:

SCHEMA adalah definisi skema dalam format FIELD:DATA_TYPE,FIELD:DATA_TYPE.
SOURCE_FORMAT adalah CSV, NEWLINE_DELIMITED_JSON, AVRO, atau GOOGLE_SHEETS.
DRIVE_URI adalah URI Drive Anda.
DATASET adalah nama set data yang berisi tabel.
TABLE adalah nama tabel yang Anda buat.

Misalnya, perintah berikut akan membuat tabel permanen bernama sales yang ditautkan ke file Spreadsheet yang disimpan di Drive dengan definisi skema berikut: Region:STRING,Quarter:STRING,Total_sales:INTEGER.

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

Untuk membuat tabel permanen yang ditautkan ke sumber data eksternal Anda menggunakan file skema JSON, masukkan perintah berikut.

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

Dengan keterangan:

SCHEMA_FILE adalah jalur ke file skema JSON di komputer lokal Anda.
SOURCE_FORMAT adalah CSV, NEWLINE_DELIMITED_JSON, AVRO, atau GOOGLE_SHEETS.
DRIVE_URI adalah URI Drive Anda.
DATASET adalah nama set data yang berisi tabel.
TABLE adalah nama tabel yang Anda buat.

Misalnya, perintah berikut akan membuat tabel bernama sales yang ditautkan ke file CSV yang disimpan di Drive menggunakan file skema /tmp/sales_schema.json.

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

Setelah tabel permanen dibuat, Anda dapat menjalankan kueri terhadap tabel seolah-olah tabel tersebut adalah tabel BigQuery standar, dengan tunduk pada batasan sumber data eksternal.

API

Buat ExternalDataConfiguration saat Anda menggunakan metode API tables.insert. Tentukan properti schema atau tetapkan properti autodetect ke true untuk mengaktifkan deteksi otomatis skema untuk sumber data yang didukung.

Python

Untuk melakukan autentikasi ke BigQuery, siapkan Kredensial Default Aplikasi. Untuk mengetahui informasi selengkapnya, lihat Menyiapkan autentikasi untuk library klien.

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.
#
# If you are using credentials from gcloud, you must authorize the
# application first with the following command:
#
# gcloud auth application-default login \
#   --scopes=https://www.googleapis.com/auth/drive,https://www.googleapis.com/auth/cloud-platform
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]
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)
    )
)

Java

Untuk melakukan autentikasi ke BigQuery, siapkan Kredensial Default Aplikasi. Untuk mengetahui informasi selengkapnya, lihat Menyiapkan autentikasi untuk library klien.

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());
    }
  }
}

Membuat kueri tabel eksternal

Untuk mengetahui informasi selengkapnya, lihat Membuat kueri data Drive.

Kolom semu _FILE_NAME

Tabel yang didasarkan pada sumber data eksternal menyediakan kolom semu bernama _FILE_NAME. Kolom ini berisi jalur yang sepenuhnya memenuhi syarat ke file yang memiliki baris tersebut. Kolom ini hanya tersedia untuk tabel yang mereferensikan data eksternal yang disimpan di Cloud Storage dan Google Drive.

Nama kolom _FILE_NAME dicadangkan, yang berarti Anda tidak dapat membuat kolom dengan nama tersebut di tabel Anda.