Membuat kueri data Cloud Storage dalam tabel eksternal

Dokumen ini menjelaskan cara membuat kueri data yang tersimpan di tabel eksternal Cloud Storage.

Sebelum memulai

Pastikan Anda memiliki tabel eksternal Cloud Storage.

Peran yang diperlukan

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

  • BigQuery Data Viewer (roles/bigquery.dataViewer)
  • BigQuery User (roles/bigquery.user)
  • Storage Object Viewer (roles/storage.objectViewer)

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.

Membuat kueri tabel eksternal permanen

Setelah membuat tabel eksternal Cloud Storage, Anda dapat membuat kuerinya menggunakan sintaksis GoogleSQL, sama seperti tabel BigQuery standar. Contoh, SELECT field1, field2 FROM mydataset.my_cloud_storage_table;.

Membuat kueri tabel eksternal 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.

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:

(Opsional) Berikan flag --location dan tetapkan nilainya ke lokasi 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'

Ganti kode berikut:

  • LOCATION: nama lokasi Anda. Flag --location bersifat opsional. Misalnya, jika menggunakan BigQuery di region Tokyo, Anda dapat menetapkan nilai flag ke asia-northeast1. Anda dapat menetapkan nilai default untuk lokasi menggunakan file .bigqueryrc.
  • TABLE: nama tabel sementara yang Anda buat.
  • DEFINITION_FILE: jalur ke file definisi tabel di mesin lokal Anda.
  • QUERY: kueri yang Anda kirimkan ke tabel sementara.

Misalnya, perintah berikut membuat dan mengkueri 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=BUCKET_PATH \
'QUERY'

Ganti kode berikut:

  • LOCATION: nama lokasi Anda. Flag --location bersifat opsional. Misalnya, jika menggunakan BigQuery di region Tokyo, Anda dapat menetapkan nilai flag ke asia-northeast1. Anda dapat menetapkan nilai default untuk lokasi menggunakan file .bigqueryrc.
  • TABLE: nama tabel sementara yang Anda buat.
  • SCHEMA: definisi skema inline dalam format field:data_type,field:data_type.
  • SOURCE_FORMAT: format sumber data eksternal, misalnya, CSV.

  • BUCKET_PATH: jalur ke bucket Cloud Storage yang berisi data untuk tabel, dalam format gs://bucket_name/[folder_name/]file_pattern.

    Anda dapat memilih beberapa file dari bucket dengan menentukan satu karakter pengganti tanda bintang (*) di file_pattern. Contoh: gs://mybucket/file00*.parquet Untuk informasi selengkapnya, lihat Dukungan karakter pengganti untuk URI Cloud Storage.

    Anda dapat menentukan beberapa bucket untuk opsi uris dengan menyediakan beberapa jalur.

    Contoh berikut menunjukkan nilai uris yang valid:

    • gs://bucket/path1/myfile.csv
    • gs://bucket/path1/*.parquet
    • gs://bucket/path1/file1*, gs://bucket1/path1/*

    Saat Anda menentukan nilai uris yang menargetkan beberapa file, semua file tersebut harus berbagi skema yang kompatibel.

    Untuk mengetahui informasi selengkapnya tentang penggunaan Cloud Storage URI di BigQuery, lihat Jalur resource Cloud Storage.

  • QUERY: 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 Cloud Storage dengan definisi skema berikut: Region:STRING,Quarter:STRING,Total_sales:INTEGER.

bq query \
--external_table_definition=sales::Region:STRING,Quarter:STRING,Total_sales:INTEGER@CSV=gs://mybucket/sales.csv \
'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_FORMAT=BUCKET_PATH \
'QUERY'

Ganti kode berikut:

  • LOCATION: nama lokasi Anda. Flag --location bersifat opsional. Misalnya, jika menggunakan BigQuery di region Tokyo, Anda dapat menetapkan nilai flag ke asia-northeast1. Anda dapat menetapkan nilai default untuk lokasi menggunakan file .bigqueryrc.
  • SCHEMA_FILE: jalur ke file skema JSON di komputer lokal Anda.
  • SOURCE_FORMAT: format sumber data eksternal, misalnya, CSV.

  • BUCKET_PATH: jalur ke bucket Cloud Storage yang berisi data untuk tabel, dalam format gs://bucket_name/[folder_name/]file_pattern.

    Anda dapat memilih beberapa file dari bucket dengan menentukan satu karakter pengganti tanda bintang (*) di file_pattern. Contoh: gs://mybucket/file00*.parquet Untuk informasi selengkapnya, lihat Dukungan karakter pengganti untuk URI Cloud Storage.

    Anda dapat menentukan beberapa bucket untuk opsi uris dengan menyediakan beberapa jalur.

    Contoh berikut menunjukkan nilai uris yang valid:

    • gs://bucket/path1/myfile.csv
    • gs://bucket/path1/*.parquet
    • gs://bucket/path1/file1*, gs://bucket1/path1/*

    Saat Anda menentukan nilai uris yang menargetkan beberapa file, semua file tersebut harus berbagi skema yang kompatibel.

    Untuk mengetahui informasi selengkapnya tentang penggunaan Cloud Storage URI di BigQuery, lihat Jalur resource Cloud Storage.

  • QUERY: 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 Cloud Storage menggunakan file skema /tmp/sales_schema.json.

  bq query \
  --external_table_definition=sales::/tmp/sales_schema.json@CSV=gs://mybucket/sales.csv \
  'SELECT
      Region,
      Total_sales
    FROM
      sales'

API

Untuk menjalankan kueri menggunakan API, ikuti langkah-langkah berikut:

  1. Buat Objek Job.
  2. Isi bagian configuration dari objek Job dengan objek JobConfiguration.
  3. Isi bagian query dari objek JobConfiguration dengan objek JobConfigurationQuery.
  4. Isi bagian tableDefinitions dari objek JobConfigurationQuery dengan objek ExternalDataConfiguration.
  5. Panggil metode jobs.insert untuk menjalankan kueri secara asinkron, atau metode jobs.query untuk menjalankan kueri secara sinkron, dengan meneruskan objek Job.

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 informasi selengkapnya, lihat Menyiapkan autentikasi untuk library klien. 

import com.google.cloud.bigquery.BigQuery;
import com.google.cloud.bigquery.BigQueryException;
import com.google.cloud.bigquery.BigQueryOptions;
import com.google.cloud.bigquery.CsvOptions;
import com.google.cloud.bigquery.ExternalTableDefinition;
import com.google.cloud.bigquery.Field;
import com.google.cloud.bigquery.QueryJobConfiguration;
import com.google.cloud.bigquery.Schema;
import com.google.cloud.bigquery.StandardSQLTypeName;
import com.google.cloud.bigquery.TableResult;

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

  public static void runQueryExternalGCSTemp() {
    // TODO(developer): Replace these variables before running the sample.
    String tableName = "MY_TABLE_NAME";
    String sourceUri = "gs://cloud-samples-data/bigquery/us-states/us-states.csv";
    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);
    queryExternalGCSTemp(tableName, sourceUri, schema, query);
  }

  public static void queryExternalGCSTemp(
      String tableName, String sourceUri, Schema schema, String query) {
    try {
      // 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.getDefaultInstance().getService();

      // Skip header row in the file.
      CsvOptions csvOptions = CsvOptions.newBuilder().setSkipLeadingRows(1).build();

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

Node.js

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

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

// Import the Google Cloud client library and create a client
const {BigQuery} = require('@google-cloud/bigquery');
const bigquery = new BigQuery();

async function queryExternalGCSTemp() {
  // Queries an external data source using a temporary table.

  const tableId = 'us_states';

  // Configure the external data source
  const externalDataConfig = {
    sourceFormat: 'CSV',
    sourceUris: ['gs://cloud-samples-data/bigquery/us-states/us-states.csv'],
    // Optionally skip header row.
    csvOptions: {skipLeadingRows: 1},
    schema: {fields: schema},
  };

  // Example query to find states starting with 'W'
  const query = `SELECT post_abbr
  FROM \`${tableId}\`
  WHERE name LIKE 'W%'`;

  // For all options, see https://cloud.google.com/bigquery/docs/reference/v2/tables#resource
  const options = {
    query,
    tableDefinitions: {[tableId]: externalDataConfig},
  };

  // Run the query as a job
  const [job] = await bigquery.createQueryJob(options);
  console.log(`Job ${job.id} started.`);

  // Wait for the query to finish
  const [rows] = await job.getQueryResults();

  // Print the results
  console.log('Rows:');
  console.log(rows);
}

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 informasi selengkapnya, lihat Menyiapkan autentikasi untuk library klien. 

from google.cloud import bigquery

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

# Configure the external data source and query job.
external_config = bigquery.ExternalConfig("CSV")
external_config.source_uris = [
    "gs://cloud-samples-data/bigquery/us-states/us-states.csv"
]
external_config.schema = [
    bigquery.SchemaField("name", "STRING"),
    bigquery.SchemaField("post_abbr", "STRING"),
]
external_config.options.skip_leading_rows = 1
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.

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

Membuat kueri kolom semu _FILE_NAME

Tabel yang didasarkan pada sumber data eksternal menyediakan kolom pseudo 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, Google Drive, Amazon S3, dan Azure Blob Storage.

Nama kolom _FILE_NAME sudah dicadangkan, yang berarti Anda tidak dapat membuat kolom dengan nama tersebut di tabel Anda. Untuk memilih nilai _FILE_NAME, Anda harus menggunakan alias. Contoh kueri berikut menunjukkan pemilihan _FILE_NAME dengan menetapkan alias fn ke kolom semu. 

  bq query \
  --project_id=PROJECT_ID \
  --use_legacy_sql=false \
  'SELECT
     name,
     _FILE_NAME AS fn
   FROM
     `DATASET.TABLE_NAME`
   WHERE
     name contains "Alex"'

Ganti kode berikut:

  • PROJECT_ID adalah project ID yang valid (tanda ini tidak diperlukan jika Anda menggunakan Cloud Shell atau jika Anda menetapkan project default di Google Cloud CLI)
  • DATASET adalah nama set data yang menyimpan tabel eksternal permanen
  • TABLE_NAME adalah nama tabel eksternal permanen

Jika kueri memiliki predikat filter di kolom semu _FILE_NAME, BigQuery akan mencoba melewati file pembacaan yang tidak memenuhi filter. Rekomendasi yang serupa dengan membuat kueri tabel berpartisi waktu penyerapan menggunakan kolom semu berlaku saat membuat predikat kueri dengan kolom semu _FILE_NAME.

Langkah selanjutnya