Membuat tabel eksternal Cloud Storage

BigQuery mendukung kueri data Cloud Storage dalam format berikut:

  • Nilai yang dipisahkan koma (CSV)
  • JSON (dibatasi newline)
  • Avro
  • ORC
  • Parquet
  • Ekspor Datastore
  • Ekspor Firestore

BigQuery mendukung pembuatan kueri data Cloud Storage dari kelas penyimpanan berikut:

  • Standar
  • Nearline
  • Coldline
  • Archive

Untuk membuat kueri tabel eksternal Cloud Storage, Anda harus memiliki izin di tabel eksternal dan file Cloud Storage. Sebaiknya gunakan tabel BigLake jika memungkinkan. Tabel BigLake memberikan delegasi akses sehingga Anda hanya memerlukan izin pada tabel BigLake untuk membuat kueri data Cloud Storage.

Pastikan Anda mempertimbangkan lokasi set data dan bucket Cloud Storage saat Anda membuat kueri data yang disimpan di Cloud Storage.

Sebelum memulai

Berikan peran Identity and Access Management (IAM) yang memberi pengguna izin yang diperlukan untuk melakukan setiap tugas dalam dokumen ini. Izin yang diperlukan untuk melakukan tugas (jika ada) tercantum di bagian "Izin yang diperlukan" pada tugas tersebut.

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)

Anda juga memerlukan izin berikut untuk mengakses bucket Cloud Storage yang berisi data Anda:

  • storage.buckets.get
  • storage.objects.get
  • storage.objects.list (wajib jika Anda menggunakan karakter pengganti URI)

Peran Identity and Access Management yang telah ditetapkan sebelumnya di Cloud Storage Storage Admin (roles/storage.admin) mencakup izin ini.

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.

Cakupan akses untuk instance Compute Engine

Jika, dari instance Compute Engine, Anda perlu membuat kueri tabel eksternal yang ditautkan ke sumber Cloud Storage, instance setidaknya harus memiliki cakupan akses hanya baca Cloud Storage (https://www.googleapis.com/auth/devstorage.read_only ).

Cakupan mengontrol akses instance Compute Engine ke produk Google Cloud, termasuk Cloud Storage. Aplikasi yang berjalan di instance menggunakan akun layanan yang terpasangdi instance untuk memanggil Google Cloud API.

Jika Anda menyiapkan instance Compute Engine untuk dijalankan sebagai akun layanan Compute Engine default, instance tersebut secara default diberi sejumlah cakupan default, termasuk cakupan https://www.googleapis.com/auth/devstorage.read_only.

Jika Anda menyiapkan instance dengan akun layanan kustom, pastikan untuk secara eksplisit memberikan cakupan https://www.googleapis.com/auth/devstorage.read_only ke instance tersebut.

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 tabel eksternal pada data yang tidak dipartisi

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

Pilih salah satu opsi berikut:

Konsol

  1. Buka halaman BigQuery.

    Buka BigQuery

  2. Di panel Penjelajah, luaskan project Anda lalu pilih set data.

  3. Luaskan opsi Actions dan klik Create table.

  4. Di bagian Sumber, tentukan detail berikut:

    1. Untuk Create table from, pilih Google Cloud Storage

    2. Untuk Select file from GCS bucket or use a URI pattern, cari untuk memilih bucket dan file yang akan digunakan, atau ketik jalur dalam format gs://bucket_name/[folder_name/]file_name.

      Anda tidak dapat menentukan beberapa URI di Konsol Google Cloud, tetapi Anda dapat memilih beberapa file dengan menentukan satu karakter pengganti tanda bintang (*). Contoh, gs://mybucket/file_name*. Untuk informasi selengkapnya, lihat Dukungan karakter pengganti untuk URI Cloud Storage.

      Bucket Cloud Storage harus berada di lokasi yang sama dengan set data yang berisi tabel yang Anda buat.

    3. Untuk File format, pilih format yang cocok dengan file Anda.

  5. Di bagian Tujuan, tentukan detail berikut:

    1. Untuk Project, pilih project tempat tabel akan dibuat.

    2. Untuk Dataset, pilih set data tempat tabel akan dibuat.

    3. Untuk Table, masukkan nama tabel yang Anda buat.

    4. Untuk Jenis tabel, pilih Tabel eksternal.

  6. Di bagian Skema, Anda dapat mengaktifkan deteksi otomatis skema atau menentukan skema secara manual jika Anda memiliki file sumber. Jika tidak memiliki file sumber, Anda harus menentukan skema secara manual.

    • Untuk mengaktifkan deteksi otomatis skema, pilih opsi Auto-detect.

    • Untuk menentukan skema secara manual, biarkan opsi Deteksi otomatis tidak dicentang. Aktifkan Edit as text dan masukkan skema tabel sebagai array JSON.

  7. Untuk mengabaikan baris dengan nilai kolom tambahan yang tidak cocok dengan skema, luaskan bagian Advanced options dan pilih Unknown values.

  8. Klik Create table.

Setelah tabel permanen dibuat, Anda dapat menjalankan kueri terhadap tabel seolah-olah tabel tersebut merupakan tabel BigQuery native. Setelah kueri selesai, Anda dapat mengekspor hasilnya sebagai file CSV atau JSON, menyimpan hasilnya sebagai tabel, atau menyimpan hasilnya ke Google Spreadsheet.

SQL

Anda dapat membuat tabel eksternal permanen dengan menjalankan pernyataan DDL CREATE EXTERNAL TABLE. Anda dapat menentukan skema secara eksplisit, atau menggunakan deteksi otomatis skema untuk menyimpulkan skema dari data eksternal.

  1. Di Konsol Google Cloud, buka halaman BigQuery.

    Buka BigQuery

  2. Di editor kueri, masukkan pernyataan berikut:

    CREATE EXTERNAL TABLE `PROJECT_ID.DATASET.EXTERNAL_TABLE_NAME`
  OPTIONS (
    format ="TABLE_FORMAT",
    uris = ['BUCKET_PATH'[,...]]
    );

    Ganti yang berikut ini:

    • PROJECT_ID: nama project tempat Anda ingin membuat tabel—misalnya, myproject
    • DATASET: nama set data BigQuery tempat Anda ingin membuat tabel—misalnya, mydataset
    • EXTERNAL_TABLE_NAME: nama tabel yang ingin Anda buat—misalnya, mytable
    • TABLE_FORMAT: format tabel yang ingin Anda buat—misalnya, PARQUET
    • BUCKET_PATH: jalur ke bucket Cloud Storage yang berisi data untuk tabel eksternal, dalam format ['gs://bucket_name/[folder_name/]file_name'].

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

      Anda dapat menyertakan beberapa bucket untuk opsi uris dengan memberikan beberapa jalur.

      Contoh berikut menunjukkan nilai uris yang valid:

      • ['gs://bucket/path1/myfile.csv']
      • ['gs://bucket/path1/*.csv']
      • ['gs://bucket/path1/*', 'gs://bucket/path2/file00*']

      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.

  3. Klik Run.

Untuk informasi selengkapnya tentang cara menjalankan kueri, lihat Menjalankan kueri interaktif.

Contoh

Contoh berikut menggunakan deteksi otomatis skema untuk membuat tabel eksternal bernama sales yang ditautkan ke file CSV yang disimpan di Cloud Storage:

CREATE OR REPLACE EXTERNAL TABLE mydataset.sales
  OPTIONS (
  format = 'CSV',
  uris = ['gs://mybucket/sales.csv']);

Contoh berikutnya menetapkan skema secara eksplisit dan melewati baris pertama file CSV:

CREATE OR REPLACE EXTERNAL TABLE mydataset.sales (
  Region STRING,
  Quarter STRING,
  Total_Sales INT64
) OPTIONS (
    format = 'CSV',
    uris = ['gs://mybucket/sales.csv'],
    skip_leading_rows = 1);

bq

Untuk membuat tabel eksternal, gunakan perintah bq mk dengan flag --external_table_definition. Flag ini berisi jalur ke file definisi tabel atau definisi tabel inline.

Opsi 1: File definisi tabel

Gunakan perintah bq mkdef untuk membuat file definisi tabel, lalu teruskan jalur file ke perintah bq mk seperti berikut:

bq mkdef --source_format=SOURCE_FORMAT \
  BUCKET_PATH > DEFINITION_FILE

bq mk --table \
  --external_table_definition=DEFINITION_FILE \
  DATASET_NAME.TABLE_NAME \
  SCHEMA

Ganti kode berikut:

  • SOURCE_FORMAT: format sumber data eksternal. Contoh, 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 informasi selengkapnya tentang penggunaan URI Cloud Storage di BigQuery, lihat Jalur resource Cloud Storage.

  • DEFINITION_FILE: jalur ke file definisi tabel di mesin lokal Anda.

  • DATASET_NAME: nama set data yang berisi tabel.

  • TABLE_NAME: nama tabel yang Anda buat.

  • SCHEMA: menentukan jalur ke file skema JSON, atau menentukan skema dalam formulir field:data_type,field:data_type,....

Contoh:

bq mkdef --source_format=CSV gs://mybucket/sales.csv > mytable_def

bq mk --table --external_table_definition=mytable_def \
  mydataset.mytable \
  Region:STRING,Quarter:STRING,Total_sales:INTEGER

Untuk menggunakan deteksi otomatis skema, tetapkan flag --autodetect=true dalam perintah mkdef dan hapus skemanya:

bq mkdef --source_format=CSV --autodetect=true \
  gs://mybucket/sales.csv > mytable_def

bq mk --table --external_table_definition=mytable_def \
  mydataset.mytable

Opsi 2: Definisi tabel inline

Daripada membuat file definisi tabel, Anda dapat meneruskan definisi tabel langsung ke perintah bq mk:

bq mk --table \
  --external_table_definition=@SOURCE_FORMAT=BUCKET_PATH \
  DATASET_NAME.TABLE_NAME \
  SCHEMA

Ganti kode berikut:

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

  • DATASET_NAME: nama set data yang berisi tabel.

  • TABLE_NAME: nama tabel yang Anda buat.

  • SCHEMA: menentukan jalur ke file skema JSON, atau menentukan skema dalam formulir field:data_type,field:data_type,.... Untuk menggunakan deteksi otomatis skema, hilangkan argumen ini.

Contoh:

bq mkdef --source_format=CSV gs://mybucket/sales.csv > mytable_def
bq mk --table --external_table_definition=mytable_def \
  mydataset.mytable \
  Region:STRING,Quarter:STRING,Total_sales:INTEGER

API

Panggil metode API metode tables.insert, lalu buat ExternalDataConfiguration di resource Table yang Anda teruskan.

Tentukan properti schema atau tetapkan properti autodetect ke true agar dapat mengaktifkan deteksi otomatis skema untuk sumber data yang didukung.

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.TableId;
import com.google.cloud.bigquery.TableInfo;
import com.google.cloud.bigquery.TableResult;

// Sample to queries an external data source using a permanent table
public class QueryExternalGCSPerm {

  public static void runQueryExternalGCSPerm() {
    // TODO(developer): Replace these variables before running the sample.
    String datasetName = "MY_DATASET_NAME";
    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.%s WHERE name LIKE 'W%%'", datasetName, tableName);
    queryExternalGCSPerm(datasetName, tableName, sourceUri, schema, query);
  }

  public static void queryExternalGCSPerm(
      String datasetName, 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();

      TableId tableId = TableId.of(datasetName, tableName);
      // Create a permanent table linked to the GCS file
      ExternalTableDefinition externalTable =
          ExternalTableDefinition.newBuilder(sourceUri, csvOptions).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 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 queryExternalGCSPerm() {
  // Queries an external data source using a permanent table

  /**
   * TODO(developer): Uncomment the following lines before running the sample.
   */
  // const datasetId = "my_dataset";
  // const tableId = "my_table";

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

  // For all options, see https://cloud.google.com/bigquery/docs/reference/v2/tables#resource
  const options = {
    schema: schema,
    externalDataConfiguration: dataConfig,
  };

  // Create an external table linked to the GCS file
  const [table] = await bigquery
    .dataset(datasetId)
    .createTable(tableId, options);

  console.log(`Table ${table.id} created.`);

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

  // Run the query as a job
  const [job] = await bigquery.createQueryJob(query);
  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()

# TODO(developer): Set table_id to the ID of the table to create.
table_id = "your-project.your_dataset.your_table_name"

# TODO(developer): Set the external source format of your table.
# Note that the set of allowed values for external data sources is
# different than the set used for loading data (see :class:`~google.cloud.bigquery.job.SourceFormat`).
external_source_format = "AVRO"

# TODO(developer): Set the source_uris to point to your data in Google Cloud
source_uris = [
    "gs://cloud-samples-data/bigquery/federated-formats-reference-file-schema/a-twitter.avro",
    "gs://cloud-samples-data/bigquery/federated-formats-reference-file-schema/b-twitter.avro",
    "gs://cloud-samples-data/bigquery/federated-formats-reference-file-schema/c-twitter.avro",
]

# Create ExternalConfig object with external source format
external_config = bigquery.ExternalConfig(external_source_format)
# Set source_uris that point to your data in Google Cloud
external_config.source_uris = source_uris

# TODO(developer) You have the option to set a reference_file_schema_uri, which points to
# a reference file for the table schema
reference_file_schema_uri = "gs://cloud-samples-data/bigquery/federated-formats-reference-file-schema/b-twitter.avro"

external_config.reference_file_schema_uri = reference_file_schema_uri

table = bigquery.Table(table_id)
# Set the external data configuration of the table
table.external_data_configuration = external_config
table = client.create_table(table)  # Make an API request.

print(
    f"Created table with external source format {table.external_data_configuration.source_format}"
)

Membuat tabel eksternal pada data yang dipartisi

Anda dapat membuat tabel eksternal untuk data yang dipartisi Hive yang berada di Cloud Storage. Anda tidak dapat mengubah kunci partisi setelah membuat tabel yang dipartisi secara eksternal. Anda harus membuat ulang tabel untuk mengubah kunci partisi.

Agar dapat membuat tabel eksternal untuk data yang dipartisi Hive, pilih salah satu opsi berikut:

Konsol

  1. Di konsol Google Cloud, buka BigQuery.

    Buka BigQuery

  2. Di panel Explorer, luaskan project Anda dan pilih set data.
  3. Klik View actions, lalu klik Create table. Tindakan ini akan membuka panel Create table.
  4. Di bagian Source, tentukan detail berikut:
    1. Untuk Create table from, pilih Google Cloud Storage.
    2. Untuk Select file from Cloud Storage bucket, masukkan jalur ke folder Cloud Storage, menggunakan karakter pengganti. Misalnya, my_bucket/my_files*. Bucket Cloud Storage harus berada di lokasi yang sama dengan set data yang berisi tabel yang ingin Anda buat, tambahkan, atau timpa.
    3. Dari daftar File format, pilih jenis file.
    4. Centang kotak Source data partitioning, lalu untuk Select Source URI Prefix, masukkan awalan Cloud Storage URI. Misalnya, gs://my_bucket/my_files.
    5. Di bagian Partition interference mode, pilih salah satu opsi berikut:
      • Automatically infer types: menetapkan mode deteksi skema partisi ke AUTO.
      • All columns are strings: menetapkan mode deteksi skema partisi ke STRINGS.
      • Provide my own: menetapkan mode deteksi skema partisi ke CUSTOM dan memasukkan informasi skema secara manual untuk kunci partisi. Untuk mengetahui informasi selengkapnya, lihat Menyediakan skema kunci partisi kustom.
    6. Opsional: Untuk mewajibkan filter partisi pada semua kueri untuk tabel ini, centang kotak Require partition filter. Mewajibkan filter partisi dapat mengurangi biaya dan meningkatkan performa. Untuk mengetahui informasi selengkapnya, lihat Mewajibkan filter predikat pada kunci partisi dalam kueri.
  5. Di bagian Destination, tentukan detail berikut:
    1. Untuk Project, pilih project tempat Anda ingin membuat tabel.
    2. Untuk Dataset, pilih set data tempat Anda ingin membuat tabel.
    3. Untuk Tabel, masukkan nama tabel yang ingin Anda buat.
    4. Untuk Jenis tabel, pilih Tabel eksternal.
  6. Di bagian Skema, masukkan definisi schema.
  7. Untuk mengaktifkan deteksi otomatis skema, pilih Auto detect.
  8. Untuk mengabaikan baris dengan nilai kolom ekstra yang tidak cocok dengan skema, luaskan bagian Advanced options dan pilih Unknown values.
  9. Klik Buat tabel.

SQL

Gunakan pernyataan DDL CREATE EXTERNAL TABLE.

Contoh berikut menggunakan deteksi otomatis kunci partisi Hive:

CREATE EXTERNAL TABLE `PROJECT_ID.DATASET.EXTERNAL_TABLE_NAME`
WITH PARTITION COLUMNS
OPTIONS (
format = 'SOURCE_FORMAT',
uris = ['GCS_URIS'],
hive_partition_uri_prefix = 'GCS_URI_SHARED_PREFIX',
require_hive_partition_filter = BOOLEAN);

Ganti kode berikut:

  • SOURCE_FORMAT: format sumber data eksternal, seperti PARQUET
  • GCS_URIS: jalur ke folder Cloud Storage, menggunakan format karakter pengganti
  • GCS_URI_SHARED_PREFIX: awalan URI sumber tanpa karakter pengganti
  • BOOLEAN: apakah memerlukan filter predikat pada waktu kueri atau tidak. Flag ini bersifat opsional. Nilai defaultnya adalah false.

Contoh berikut menggunakan kunci dan jenis partisi Hive kustom dengan mencantumkannya dalam klausa WITH PARTITION COLUMNS:

CREATE EXTERNAL TABLE `PROJECT_ID.DATASET.EXTERNAL_TABLE_NAME`
WITH PARTITION COLUMNS (PARTITION_COLUMN_LIST)
OPTIONS (
format = 'SOURCE_FORMAT',
uris = ['GCS_URIS'],
hive_partition_uri_prefix = 'GCS_URI_SHARED_PREFIX',
require_hive_partition_filter = BOOLEAN);

Ganti kode berikut:

  • PARTITION_COLUMN_LIST: daftar kolom dengan urutan yang sama di jalur folder Cloud Storage, dalam format:
KEY1 TYPE1, KEY2 TYPE2

Contoh berikut membuat tabel berpartisi eksternal. Fitur ini menggunakan deteksi otomatis skema untuk mendeteksi skema file dan tata letak partisi hive. Jika jalur eksternal adalah gs://bucket/path/field_1=first/field_2=1/data.parquet, kolom partisi akan terdeteksi sebagai field_1 (STRING) dan field_2 (INT64).

CREATE EXTERNAL TABLE dataset.AutoHivePartitionedTable
WITH PARTITION COLUMNS
OPTIONS (
uris = ['gs://bucket/path/*'],
format = 'PARQUET',
hive_partition_uri_prefix = 'gs://bucket/path',
require_hive_partition_filter = false);

Contoh berikut membuat tabel berpartisi eksternal dengan menentukan kolom partisi secara eksplisit. Contoh ini mengasumsikan bahwa jalur file eksternal memiliki pola gs://bucket/path/field_1=first/field_2=1/data.parquet.

CREATE EXTERNAL TABLE dataset.CustomHivePartitionedTable
WITH PARTITION COLUMNS (
field_1 STRING, -- column order must match the external path
field_2 INT64)
OPTIONS (
uris = ['gs://bucket/path/*'],
format = 'PARQUET',
hive_partition_uri_prefix = 'gs://bucket/path',
require_hive_partition_filter = false);

bq

Pertama, gunakan perintah bq mkdef untuk membuat file definisi tabel:

bq mkdef \
--source_format=SOURCE_FORMAT \
--hive_partitioning_mode=PARTITIONING_MODE \
--hive_partitioning_source_uri_prefix=GCS_URI_SHARED_PREFIX \
--require_hive_partition_filter=BOOLEAN \
 GCS_URIS > DEFINITION_FILE

Ganti kode berikut:

  • SOURCE_FORMAT: format sumber data eksternal. Contoh, CSV.
  • PARTITIONING_MODE: mode partisi Hive. Gunakan salah satu nilai berikut:
    • AUTO: Mendeteksi nama dan jenis kunci secara otomatis.
    • STRINGS: Mengonversi nama kunci menjadi string secara otomatis.
    • CUSTOM: Mengenkode skema kunci di awalan URI sumber.
  • GCS_URI_SHARED_PREFIX: awalan URI sumber.
  • BOOLEAN: Menentukan apakah akan memerlukan filter predikat pada waktu kueri. Flag ini bersifat opsional. Nilai defaultnya adalah false.
  • GCS_URIS: jalur ke folder Cloud Storage, menggunakan format karakter pengganti.
  • DEFINITION_FILE: jalur ke file definisi tabel di komputer lokal Anda.

Jika PARTITIONING_MODE adalah CUSTOM, sertakan skema kunci partisi di awalan URI sumber, menggunakan format berikut:

--hive_partitioning_source_uri_prefix=GCS_URI_SHARED_PREFIX/{KEY1:TYPE1}/{KEY2:TYPE2}/...

Setelah membuat file definisi tabel, gunakan perintah bq mk untuk membuat tabel eksternal:

bq mk --external_table_definition=DEFINITION_FILE \
DATASET_NAME.TABLE_NAME \
SCHEMA

Ganti kode berikut:

  • DEFINITION_FILE: jalur ke file definisi tabel.
  • DATASET_NAME: nama set data yang berisi tabel.
  • TABLE_NAME: nama tabel yang Anda buat.
  • SCHEMA: menentukan jalur ke file skema JSON, atau menentukan skema dalam formulir field:data_type,field:data_type,.... Untuk menggunakan deteksi otomatis skema, hilangkan argumen ini.

Contoh

Contoh berikut menggunakan mode partisi Hive AUTO:

bq mkdef --source_format=CSV \
  --hive_partitioning_mode=AUTO \
  --hive_partitioning_source_uri_prefix=gs://myBucket/myTable \
  gs://myBucket/myTable/* > mytable_def

bq mk --external_table_definition=mytable_def \
  mydataset.mytable \
  Region:STRING,Quarter:STRING,Total_sales:INTEGER

Contoh berikut menggunakan mode partisi Hive STRING:

bq mkdef --source_format=CSV \
  --hive_partitioning_mode=STRING \
  --hive_partitioning_source_uri_prefix=gs://myBucket/myTable \
  gs://myBucket/myTable/* > mytable_def

bq mk --external_table_definition=mytable_def \
  mydataset.mytable \
  Region:STRING,Quarter:STRING,Total_sales:INTEGER

Contoh berikut menggunakan mode partisi Hive CUSTOM:

bq mkdef --source_format=CSV \
  --hive_partitioning_mode=CUSTOM \
  --hive_partitioning_source_uri_prefix=gs://myBucket/myTable/{dt:DATE}/{val:STRING} \
  gs://myBucket/myTable/* > mytable_def

bq mk --external_table_definition=mytable_def \
  mydataset.mytable \
  Region:STRING,Quarter:STRING,Total_sales:INTEGER

API

Untuk menetapkan partisi Hive menggunakan BigQuery API, sertakan objek hivePartitioningOptions dalam objek ExternalDataConfiguration saat Anda membuat file definisi tabel

Jika menetapkan kolom hivePartitioningOptions.mode ke CUSTOM, Anda harus mengenkode skema kunci partisi di kolom hivePartitioningOptions.sourceUriPrefix sebagai berikut: gs://BUCKET/PATH_TO_TABLE/{KEY1:TYPE1}/{KEY2:TYPE2}/...

Untuk menerapkan penggunaan filter predikat pada waktu kueri, tetapkan kolom hivePartitioningOptions.requirePartitionFilter ke true.

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.ExternalTableDefinition;
import com.google.cloud.bigquery.FormatOptions;
import com.google.cloud.bigquery.HivePartitioningOptions;
import com.google.cloud.bigquery.TableId;
import com.google.cloud.bigquery.TableInfo;

// Sample to create external table using hive partitioning
public class SetHivePartitioningOptions {

  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 = "gs://cloud-samples-data/bigquery/hive-partitioning-samples/customlayout/*";
    String sourceUriPrefix =
        "gs://cloud-samples-data/bigquery/hive-partitioning-samples/customlayout/{pkey:STRING}/";
    setHivePartitioningOptions(datasetName, tableName, sourceUriPrefix, sourceUri);
  }

  public static void setHivePartitioningOptions(
      String datasetName, String tableName, String sourceUriPrefix, String sourceUri) {
    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();

      // Configuring partitioning options
      HivePartitioningOptions hivePartitioningOptions =
          HivePartitioningOptions.newBuilder()
              .setMode("CUSTOM")
              .setRequirePartitionFilter(true)
              .setSourceUriPrefix(sourceUriPrefix)
              .build();

      TableId tableId = TableId.of(datasetName, tableName);
      ExternalTableDefinition customTable =
          ExternalTableDefinition.newBuilder(sourceUri, FormatOptions.parquet())
              .setAutodetect(true)
              .setHivePartitioningOptions(hivePartitioningOptions)
              .build();
      bigquery.create(TableInfo.of(tableId, customTable));
      System.out.println("External table created using hivepartitioningoptions");
    } catch (BigQueryException e) {
      System.out.println("External table was not created" + e.toString());
    }
  }
}

Membuat kueri tabel eksternal

Untuk mengetahui informasi selengkapnya, lihat Membuat kueri data Cloud Storage dalam tabel eksternal.

Mengupgrade tabel eksternal ke BigLake

Anda dapat mengupgrade tabel berdasarkan Cloud Storage ke tabel BigLake dengan mengaitkan tabel eksternal ke koneksi. Jika ingin menggunakan penyimpanan metadata ke dalam cache dengan tabel BigLake, Anda dapat menentukan setelannya secara bersamaan. Untuk mendapatkan detail tabel, seperti format sumber dan URI sumber, lihat Mendapatkan informasi tabel.

Untuk memperbarui tabel eksternal ke tabel BigLake, pilih salah satu opsi berikut:

SQL

Gunakan pernyataan DDL CREATE OR REPLACE EXTERNAL TABLE untuk mengupdate tabel:

  1. Di Konsol Google Cloud, buka halaman BigQuery.

    Buka BigQuery

  2. Di editor kueri, masukkan pernyataan berikut:

    CREATE OR REPLACE EXTERNAL TABLE
  `PROJECT_ID.DATASET.EXTERNAL_TABLE_NAME`
  WITH CONNECTION `REGION.CONNECTION_ID`
  OPTIONS(
    format ="TABLE_FORMAT",
    uris = ['BUCKET_PATH'],
    max_staleness = STALENESS_INTERVAL,
    metadata_cache_mode = 'CACHE_MODE'
    );

    Ganti yang berikut ini:

    • PROJECT_ID: nama project yang berisi tabel
    • DATASET: nama set data yang berisi tabel
    • EXTERNAL_TABLE_NAME: nama tabel
    • REGION: region yang berisi koneksi
    • CONNECTION_ID: nama koneksi yang akan digunakan
    • TABLE_FORMAT: format yang digunakan oleh tabel

      Anda tidak dapat mengubahnya saat mengupdate tabel.

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

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

      Anda dapat menyertakan beberapa bucket untuk opsi uris dengan memberikan beberapa jalur.

      Contoh berikut menunjukkan nilai uris yang valid:

      • ['gs://bucket/path1/myfile.csv']
      • ['gs://bucket/path1/*.csv']
      • ['gs://bucket/path1/*', 'gs://bucket/path2/file00*']

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

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

    • STALENESS_INTERVAL: menentukan apakah metadata yang di-cache digunakan oleh operasi terhadap tabel, dan seberapa baru metadata yang di-cache agar operasi dapat menggunakannya

      Untuk informasi selengkapnya tentang pertimbangan caching metadata, lihat Caching metadata untuk performa.

      Untuk menonaktifkan caching metadata, tentukan 0. Ini adalah setelan defaultnya.

      Untuk mengaktifkan caching metadata, tentukan nilai literal interval antara 30 menit dan 7 hari. Misalnya, tentukan INTERVAL 4 HOUR untuk interval periode tidak berlaku 4 jam. Dengan nilai ini, operasi terhadap tabel akan menggunakan metadata yang di-cache jika telah di-refresh dalam 4 jam terakhir. Jika metadata yang di-cache lebih tua, operasi akan mengambil metadata dari Cloud Storage.

    • CACHE_MODE: menentukan apakah cache metadata di-refresh secara otomatis atau manual

      Untuk informasi selengkapnya terkait pertimbangan caching metadata, lihat Caching metadata untuk performa.

      Ubah ke AUTOMATIC agar cache metadata diperbarui sesuai dengan interval yang ditentukan sistem, biasanya antara 30 hingga 60 menit.

      Ubah ke MANUAL jika Anda ingin memperbarui cache metadata sesuai dengan jadwal yang Anda tentukan. Dalam hal ini, Anda dapat memanggil prosedur sistem BQ.REFRESH_EXTERNAL_METADATA_CACHE untuk me-refresh cache.

      Anda harus menetapkan CACHE_MODE jika STALENESS_INTERVAL ditetapkan ke nilai yang lebih besar dari 0.

  3. Klik Run.

Untuk informasi selengkapnya tentang cara menjalankan kueri, lihat Menjalankan kueri interaktif.

bq

Gunakan perintah bq mkdef dan bq update untuk mengupdate tabel:

  1. Buat definisi tabel eksternal, yang menjelaskan aspek tabel yang akan diubah:

    bq mkdef --connection_id=PROJECT_ID.REGION.CONNECTION_ID \
--source_format=TABLE_FORMAT \
--metadata_cache_mode=CACHE_MODE \
"BUCKET_PATH" > /tmp/DEFINITION_FILE

    Ganti yang berikut ini:

    • PROJECT_ID: nama project yang berisi koneksi
    • REGION: region yang berisi koneksi
    • CONNECTION_ID: nama koneksi yang akan digunakan
    • TABLE_FORMAT: format yang digunakan oleh tabel. Anda tidak dapat mengubahnya saat mengupdate tabel.

    • CACHE_MODE: menentukan apakah cache metadata di-refresh secara otomatis atau manual. Untuk informasi selengkapnya seputar pertimbangan caching metadata, lihat Caching metadata untuk performa.

      Tetapkan ke AUTOMATIC agar cache metadata di-refresh pada interval yang ditentukan sistem, biasanya antara 30 dan 60 menit.

      Tetapkan ke MANUAL jika Anda ingin me-refresh cache metadata sesuai jadwal yang Anda tentukan. Dalam hal ini, Anda dapat memanggil prosedur sistem BQ.REFRESH_EXTERNAL_METADATA_CACHE untuk me-refresh cache.

      Anda harus menetapkan CACHE_MODE jika STALENESS_INTERVAL ditetapkan ke nilai yang lebih besar dari 0.

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

      Anda dapat membatasi file yang dipilih dari bucket dengan menentukan satu karakter pengganti tanda bintang (*) di jalur. Contoh, gs://mybucket/file_name*. 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/*.csv
      • gs://bucket/path1/*,gs://bucket/path2/file00*

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

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

    • DEFINITION_FILE: nama file definisi tabel yang Anda buat.

  2. Update tabel menggunakan definisi tabel eksternal baru:

    bq update --max_staleness=STALENESS_INTERVAL \
--external_table_definition=/tmp/DEFINITION_FILE \
PROJECT_ID:DATASET.EXTERNAL_TABLE_NAME

    Ganti kode berikut:

    • STALENESS_INTERVAL: menentukan apakah metadata yang di-cache digunakan oleh operasi terhadap tabel, dan seberapa baru metadata yang di-cache agar operasi dapat menggunakannya. Untuk informasi selengkapnya tentang pertimbangan caching metadata, lihat Caching metadata untuk performa.

      Untuk menonaktifkan caching metadata, tentukan 0. Ini adalah setelan defaultnya.

      Untuk mengaktifkan caching metadata, tentukan nilai interval antara 30 menit hingga 7 hari, menggunakan Y-M D H:M:S yang dijelaskan dalam dokumentasi jenis data INTERVAL. Misalnya, tentukan 0-0 0 4:0:0 untuk interval periode tidak berlaku 4 jam. Dengan nilai ini, operasi terhadap tabel akan menggunakan metadata yang di-cache jika telah di-refresh dalam 4 jam terakhir. Jika metadata yang di-cache lebih tua, operasi akan mengambil metadata dari Cloud Storage.

    • DEFINITION_FILE: nama file definisi tabel yang Anda buat atau update.

    • PROJECT_ID: nama project yang berisi tabel

    • DATASET: nama set data yang berisi tabel

    • EXTERNAL_TABLE_NAME: nama tabel

Jalur resource Cloud Storage

Saat membuat tabel eksternal berdasarkan sumber data Cloud Storage, Anda harus menyediakan jalur ke data tersebut.

Jalur resource Cloud Storage berisi nama bucket dan objek (nama file) Anda. Misalnya, jika bucket Cloud Storage bernama mybucket dan file datanya bernama myfile.csv, jalur resource-nya adalah gs://mybucket/myfile.csv.

BigQuery tidak mendukung jalur resource Cloud Storage yang mencakup beberapa garis miring berturut-turut setelah garis miring ganda di awal. Nama objek Cloud Storage dapat berisi beberapa karakter garis miring ("/") berturut-turut. Namun, BigQuery mengonversi beberapa garis miring berturut-turut menjadi satu garis miring. Misalnya, jalur resource berikut, meskipun valid di Cloud Storage, tidak berfungsi di BigQuery: gs://bucket/my//object//name.

Untuk mengambil jalur resource Cloud Storage:

  1. Buka Konsol Cloud Storage.

    Konsol Cloud Storage

  2. Jelajahi lokasi objek (file) yang berisi data sumber.

  3. Klik nama objek.

    Halaman Detail objek akan terbuka.

  4. Salin nilai yang diberikan di kolom gsutil URI, yang dimulai dengan gs://.

Dukungan karakter pengganti untuk Cloud Storage URI

Jika data dipisahkan menjadi beberapa file, Anda dapat menggunakan karakter pengganti tanda bintang (*) untuk memilih beberapa file. Penggunaan karakter pengganti tanda bintang harus mengikuti aturan berikut:

  • Tanda bintang dapat muncul di dalam nama objek atau di akhir nama objek.
  • Menggunakan beberapa tanda bintang tidak didukung. Misalnya, jalur gs://mybucket/fed-*/temp/*.csv tidak valid.
  • Penggunaan tanda bintang dengan nama bucket tidak didukung.

Contoh:

  • Contoh berikut menunjukkan cara memilih semua file di semua folder yang dimulai dengan awalan gs://mybucket/fed-samples/fed-sample:

    gs://mybucket/fed-samples/fed-sample*

  • Contoh berikut menunjukkan cara memilih file dengan ekstensi .csv saja di folder bernama fed-samples dan subfolder fed-samples apa pun:

    gs://mybucket/fed-samples/*.csv

  • Contoh berikut menunjukkan cara memilih file dengan pola penamaan fed-sample*.csv dalam folder bernama fed-samples. Contoh ini tidak memilih file di subfolder fed-samples.

    gs://mybucket/fed-samples/fed-sample*.csv

Saat menggunakan alat command line bq, Anda mungkin perlu meng-escape tanda bintang di beberapa platform.

Anda tidak dapat menggunakan karakter pengganti tanda bintang saat membuat tabel eksternal yang ditautkan ke ekspor Datastore atau Firestore.

Batasan

Untuk informasi tentang batasan yang berlaku untuk tabel eksternal, lihat Batasan tabel eksternal.

Langkah berikutnya