Membuat kueri data Drive

Dokumen ini menjelaskan cara membuat kueri data yang tersimpan dalam tabel eksternal Google Drive.

BigQuery mendukung kueri terhadap file Drive pribadi dan file bersama. Untuk mengetahui informasi selengkapnya tentang Drive, lihat Pelatihan dan bantuan Google Drive.

Anda dapat membuat kueri data Drive dari tabel eksternal permanen atau dari tabel eksternal sementara yang Anda buat saat menjalankan kueri.

Peran yang diperlukan

Untuk membuat kueri tabel eksternal Drive, pastikan Anda memiliki peran berikut:

  • BigQuery Data Viewer (roles/bigquery.dataViewer)
  • BigQuery User (roles/bigquery.user)

Bergantung pada izin yang Anda miliki, Anda dapat memberikan peran ini pada diri sendiri atau meminta administrator untuk memberikannya. Untuk mengetahui informasi selengkapnya tentang cara memberikan peran, lihat Melihat peran yang dapat diberikan pada resource.

Guna melihat izin spesifik BigQuery yang diperlukan untuk membuat kueri tabel eksternal, luaskan bagian Izin yang diperlukan:

Izin yang diperlukan

Anda mungkin juga bisa mendapatkan izin ini dengan peran khusus atau peran yang telah ditetapkan sebelumnya.

Izin Drive

Setidaknya, untuk membuat kueri data eksternal di Drive, Anda harus diberi akses View ke file Drive yang ditautkan ke tabel eksternal.

Cakupan untuk instance Compute Engine

Saat membuat instance Compute Engine, Anda dapat menentukan daftar cakupan untuk instance tersebut. Cakupan mengontrol akses instance ke produk Google Cloud, termasuk Drive. Aplikasi yang berjalan di VM menggunakan akun layanan untuk memanggil Google Cloud API.

Jika Anda menyiapkan instance Compute Engine untuk dijalankan sebagai akun layanan, dan akun layanan tersebut mengakses tabel eksternal yang ditautkan ke sumber data Drive, Anda harus menambahkan Cakupan OAuth untuk Drive (https://www.googleapis.com/auth/drive.readonly) ke instance.

Untuk mengetahui informasi cara menerapkan cakupan ke instance Compute Engine, lihat Mengubah akun layanan dan cakupan akses untuk instance. Untuk mengetahui informasi selengkapnya tentang akun layanan Compute Engine, lihat Akun layanan.

Membuat kueri data Drive menggunakan tabel eksternal permanen

Setelah membuat tabel eksternal Drive, Anda dapat membuat kuerinya menggunakan sintaksis GoogleSQL, sama seperti saat membuat kueri tabel BigQuery standar. Contoh, SELECT field1, field2 FROM mydataset.my_drive_table;.

Membuat kueri data Drive menggunakan tabel sementara

Membuat kueri sumber data eksternal menggunakan tabel sementara berguna untuk kueri ad-hoc satu kali atas data eksternal, atau untuk proses ekstrak, transformasi, dan pemuatan (ETL).

Untuk membuat kueri sumber data eksternal tanpa membuat tabel permanen, Anda perlu memberikan definisi tabel untuk tabel sementara, lalu menggunakan definisi tabel tersebut dalam perintah atau panggilan untuk membuat kueri tabel sementara. Anda dapat memberikan definisi tabel dengan salah satu cara berikut:

File definisi tabel atau skema yang disediakan digunakan untuk membuat tabel eksternal sementara, dan kueri dijalankan terhadap tabel eksternal sementara.

Saat menggunakan tabel eksternal sementara, Anda tidak membuat tabel di salah satu set data BigQuery Anda. Karena tidak disimpan secara permanen dalam set data, tabel itu tidak dapat dibagikan kepada orang lain.

Membuat dan melakukan kueri tabel sementara

Anda dapat membuat dan membuat kueri tabel sementara yang ditautkan ke sumber data eksternal menggunakan alat command line bq, API, atau library klien.

bq

Anda membuat kueri tabel sementara yang ditautkan ke sumber data eksternal menggunakan perintah bq query dengan flag --external_table_definition. Saat menggunakan alat command line bq untuk membuat kueri tabel sementara 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 kueri tabel sementara yang ditautkan ke sumber data eksternal menggunakan file definisi tabel, masukkan perintah berikut.

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

Dengan keterangan:

  • LOCATION adalah lokasi Anda. Flag --location bersifat opsional.
  • TABLE adalah nama tabel sementara yang Anda buat.
  • DEFINITION_FILE adalah jalur ke file definisi tabel di mesin lokal Anda.
  • QUERY adalah kueri yang Anda kirimkan ke tabel sementara.

Misalnya, perintah berikut membuat dan membuat kueri tabel sementara bernama sales menggunakan file definisi tabel bernama sales_def.

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

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

bq --location=LOCATION query \
--external_table_definition=TABLE::SCHEMA@SOURCE_FORMAT=DRIVE_URI \
'QUERY'

Dengan keterangan:

  • LOCATION adalah lokasi Anda. Flag --location bersifat opsional.
  • TABLE adalah nama tabel sementara yang Anda buat.
  • SCHEMA adalah definisi skema inline 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.
  • QUERY adalah kueri yang Anda kirimkan ke tabel sementara.

Misalnya, perintah berikut membuat dan membuat kueri tabel sementara bernama sales yang ditautkan ke file CSV yang disimpan di Drive dengan definisi skema berikut: Region:STRING,Quarter:STRING,Total_sales:INTEGER.

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'

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

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

Dengan keterangan:

  • LOCATION adalah lokasi Anda. Flag --location bersifat opsional.
  • SCHEMA_FILE adalah jalur ke file skema JSON di komputer lokal Anda.
  • SOURCE_FILE adalah CSV, NEWLINE_DELIMITED_JSON, AVRO, atau GOOGLE_SHEETS.
  • DRIVE_URI adalah URI Drive Anda.
  • QUERY adalah kueri yang Anda kirimkan ke tabel sementara.

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

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

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

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. 

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

Batasan

Kueri BigQuery dapat membebani Google Spreadsheet, sehingga menghasilkan error seperti Resources exceeded during query execution: Google Sheets service overloaded. Pertimbangkan untuk menyederhanakan spreadsheet Anda, misalnya, dengan meminimalkan penggunaan formula. Untuk mengetahui informasi selengkapnya, lihat batasan tabel eksternal.

Langkah selanjutnya