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.

Mengautentikasi dan mengaktifkan akses Drive

Perlu cakupan OAuth tambahan untuk mengakses data yang dihosting dalam Drive. Untuk melakukan autentikasi ke BigQuery dan mengaktifkan akses drive, lakukan hal berikut:

Konsol

Ikuti langkah-langkah autentikasi berbasis web saat Anda membuat tabel eksternal di Konsol Google Cloud. Saat diminta, klik Izinkan untuk memberi BigQuery Client Tools akses ke 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. Masukkan perintah berikut untuk memastikan Anda memiliki Google Cloud CLI versi terbaru.

   gcloud components update
   

3. Masukkan perintah berikut untuk melakukan autentikasi dengan Drive.

   gcloud auth login --enable-gdrive-access
   

Catatan: Jika menggunakan Cloud Shell untuk mengakses data Drive, Anda tidak perlu mengupdate Google Cloud CLI atau melakukan autentikasi dengan Drive.

API

Minta cakupan OAuth untuk Drive yang sesuai, selain cakupan untuk BigQuery:

1. Login dengan menjalankan perintah gcloud auth login --enable-gdrive-access.
2. Dapatkan token akses OAuth dengan cakupan Drive yang digunakan untuk API Anda dengan menjalankan perintah gcloud auth print-access-token.

Python

1. Buat Client ID OAuth.

2. Siapkan Kredensial Default Aplikasi (ADC) di lingkungan lokal Anda dengan cakupan yang diperlukan dengan melakukan hal berikut:

   1. Instal Google Cloud CLI, lalu lakukan inisialisasi dengan menjalankan perintah berikut:

      gcloud init

   2. Buat kredensial autentikasi lokal untuk Akun Google Anda:

      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

      Ganti CLIENT_ID_FILE dengan file yang berisi Client ID OAuth Anda.

      Untuk mengetahui informasi selengkapnya, lihat Kredensial pengguna yang diberikan menggunakan gcloud CLI.

Java

1. Buat Client ID OAuth.

2. Siapkan Kredensial Default Aplikasi (ADC) di lingkungan lokal Anda dengan cakupan yang diperlukan dengan melakukan hal berikut:

   1. Instal Google Cloud CLI, lalu lakukan inisialisasi dengan menjalankan perintah berikut:

      gcloud init

   2. Buat kredensial autentikasi lokal untuk Akun Google Anda:

      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

      Ganti CLIENT_ID_FILE dengan file yang berisi Client ID OAuth Anda.

      Untuk mengetahui informasi selengkapnya, lihat Kredensial pengguna yang diberikan menggunakan gcloud CLI.

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

1. Di konsol Google Cloud, buka halaman BigQuery.

Buka BigQuery

1. Di panel Explorer, luaskan project Anda dan pilih set data.

2. Luaskan opsi Actions dan klik Open.

3. Di panel detail, klik Create table .

4. 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.

5. (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.

6. 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.

7. 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.

8. Klik Buat tabel.

9. 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.

Jika file definisi tabel berisi konfigurasi khusus Spreadsheet, Anda dapat melewati baris awal dan menentukan rentang sheet yang ditentukan.

Contoh berikut 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.

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.

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

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

Java

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

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.