建立 Cloud Storage 外部資料表

主控台

1. 前往「BigQuery」頁面

   前往 BigQuery

2. 在「Explorer」窗格中展開專案並選取資料集。

3. 展開 more_vert「動作」選項，然後按一下「建立資料表」。

4. 在「Source」部分中，指定下列詳細資料：

   1. 在「Create table from」(使用下列資料建立資料表) 區段，選取「Google Cloud Storage」

   2. 如要從 GCS 值區選取檔案或使用 URI 模式，請瀏覽並選取要使用的值區和檔案，或輸入 gs://bucket_name/[folder_name/]file_name 格式的路徑。

      您無法在 Google Cloud 控制台中指定多個 URI，但可以指定一個星號 (*) 萬用字元來選取多個檔案。例如 gs://mybucket/file_name*。詳情請參閱「Cloud Storage URI 的萬用字元支援」。

      Cloud Storage 值區的位置必須和要建立的資料表所在的資料集位置相同。

   3. 在「File format」(檔案格式) 部分，選取與檔案相符的格式。

5. 在「Destination」部分中，指定下列詳細資料：

   1. 在「Project」(專案) 部分，選擇要建立資料表的專案。

   2. 在「Dataset」(資料集) 部分，選擇要建立資料表的資料集。

   3. 在「Table」(資料表) 中，輸入要建立的資料表名稱。

   4. 在「Table type」(資料表類型) 中，選取「External table」(外部資料表)。

6. 在「Schema」區段中，您可以啟用結構定義自動偵測功能，或手動指定結構定義 (如果有來源檔案)。如果沒有來源檔案，您必須手動指定結構定義。

   • 如要啟用結構定義自動偵測功能，請選取「Auto-detect」(自動偵測) 選項。

   • 如要手動指定結構定義，請不要勾選「Auto-detect」選項。啟用「Edit as text」，然後以 JSON 陣列的形式輸入資料表結構定義。

7. 如要忽略含有與結構定義不符的額外資料欄值的資料列，請展開「進階選項」部分，然後選取「不明的值」。

8. 點選「建立資料表」。

建立永久資料表後，您就可以把這個資料表當做原生 BigQuery 資料表一樣執行查詢。查詢完成後，可以將結果匯出為 CSV 或 JSON 檔案、將結果儲存為資料表，或將結果儲存至 Google 試算表。

SQL

您可以執行 CREATE EXTERNAL TABLE DDL 陳述式，建立永久外部資料表。您可以明確指定結構定義，也可以使用結構定義自動偵測功能，從外部資料推斷結構定義。

1. 前往 Google Cloud 控制台的「BigQuery」頁面。

   前往 BigQuery

2. 在查詢編輯器中輸入以下陳述式：

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

   請依指示取代下列項目：

   • PROJECT_ID：您要建立資料表的專案名稱，例如 myproject
   • DATASET：您要建立資料表的 BigQuery 資料集名稱，例如 mydataset
   • EXTERNAL_TABLE_NAME：您要建立的資料表名稱，例如 mytable
   • TABLE_FORMAT：您要建立的資料表格式，例如 PARQUET
   • BUCKET_PATH：包含外部資料表資料的 Cloud Storage 值區路徑，格式為 ['gs://bucket_name/[folder_name/]file_name']。

     您可以在路徑中指定一個星號 (*) 萬用字元，從值區中選取多個檔案。例如，['gs://mybucket/file_name*']。詳情請參閱「Cloud Storage URI 的萬用字元支援」。

     您可以提供多個路徑，為 uris 選項指定多個值區。

     以下範例顯示有效的 uris 值：

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

     指定目標為多個檔案的 uris 值時，所有檔案都必須共用相容的結構定義。

     如要進一步瞭解如何在 BigQuery 中使用 Cloud Storage URI，請參閱「Cloud Storage 資源路徑」。

3. 按一下 play_circle「Run」。

如要進一步瞭解如何執行查詢，請參閱「執行互動式查詢」一文。

範例

以下範例會使用結構定義自動偵測功能，建立名為 sales 的外部資料表，且此表會連結至儲存在 Cloud Storage 中的 CSV 檔案：

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

以下範例會明確指定結構定義，並略過 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

如要建立外部資料表，請使用 bq mk 指令搭配 --external_table_definition 旗標。這個標記包含資料表定義檔案或內嵌資料表定義的路徑。

選項 1：資料表定義檔

使用 bq mkdef 指令建立資料表定義檔案，然後將檔案路徑傳遞至 bq mk 指令，如下所示：

bq mkdef --source_format=SOURCE_FORMAT \
  BUCKET_PATH > DEFINITION_FILE

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

更改下列內容：

• SOURCE_FORMAT：外部資料來源的格式。例如：CSV。

• BUCKET_PATH：包含資料表資料的 Cloud Storage 值區路徑，格式為 gs://bucket_name/[folder_name/]file_pattern。

  您可以在 file_pattern 中指定一個星號 (*) 萬用字元，從值區中選取多個檔案。例如 gs://mybucket/file00*.parquet。詳情請參閱「Cloud Storage URI 的萬用字元支援」。

  您可以提供多個路徑，為 uris 選項指定多個值區。

  以下範例顯示有效的 uris 值：

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

  指定以多個檔案為目標的 uris 值時，所有檔案都必須共用相容的結構定義。

  如要進一步瞭解如何在 BigQuery 中使用 Cloud Storage URI，請參閱「Cloud Storage 資源路徑」。

• DEFINITION_FILE：本機電腦上資料表定義檔的路徑。

• DATASET_NAME：包含資料表的資料集名稱。

• TABLE_NAME：您要建立的資料表名稱。

• SCHEMA：指定 JSON 結構定義檔的路徑，或以 field:data_type,field:data_type,... 格式指定結構定義。

範例：

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

如要使用結構定義自動偵測功能，請在 mkdef 指令中設定 --autodetect=true 標記，並省略結構定義：

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

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

選項 2：內嵌資料表定義

您可以直接將資料表定義傳遞至 bq mk 指令，而非建立資料表定義檔：

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

更改下列內容：

• SOURCE_FORMAT：外部資料來源的格式

  例如 CSV。

• BUCKET_PATH：包含資料表資料的 Cloud Storage 值區路徑，格式為 gs://bucket_name/[folder_name/]file_pattern。

  您可以在 file_pattern 中指定一個星號 (*) 萬用字元，從值區中選取多個檔案。例如 gs://mybucket/file00*.parquet。詳情請參閱「Cloud Storage URI 的萬用字元支援」。

  您可以提供多個路徑，為 uris 選項指定多個值區。

  以下範例顯示有效的 uris 值：

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

  指定以多個檔案為目標的 uris 值時，所有檔案都必須共用相容的結構定義。

  如要進一步瞭解如何在 BigQuery 中使用 Cloud Storage URI，請參閱「Cloud Storage 資源路徑」。

• DATASET_NAME：含有資料表的資料集名稱。

• TABLE_NAME：您要建立的資料表名稱。

• SCHEMA：指定 JSON 結構定義檔的路徑，或以 field:data_type,field:data_type,... 格式指定結構定義。如要使用結構定義自動偵測功能，請省略這個引數。

範例：

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

呼叫 tables.insert 方法 API 方法，並在您傳入的 Table 資源中建立 ExternalDataConfiguration。

指定 schema 屬性，或將 autodetect 屬性設為 true，為支援的資料來源啟用結構定義自動偵測功能。

Java

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

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

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

主控台

1. 前往 Google Cloud 控制台的「BigQuery」頁面。

   前往 BigQuery

2. 在「Explorer」窗格中展開專案並選取資料集。
3. 按一下 more_vert「查看動作」，然後點選「建立資料表」。系統會開啟「Create table」窗格。
4. 在「Source」部分中，指定下列詳細資料：
1. 在「Create table from」(使用下列資料建立資料表) 部分，選取「Google Cloud Storage」。
2. 針對「Select file from Cloud Storage bucket」(從 Cloud Storage 值區選取檔案)，請使用萬用字元輸入 Cloud Storage 資料夾的路徑。例如 my_bucket/my_files*。Cloud Storage 值區的位置必須與要建立、附加或覆寫的表格所在的資料集位置相同。
3. 從「File format」清單中選取檔案類型。
4. 勾選「Source data partitioning」(來源資料分割)核取方塊，然後在「Select Source URI Prefix」(選取來源 URI 前置字串) 中輸入 Cloud Storage URI 前置字串。例如：gs://my_bucket/my_files。
5. 在「Partition inference mode」部分中，選取下列其中一個選項：
   • 自動推論類型：將分區結構定義偵測模式設為 AUTO。
   • 所有資料欄皆為字串：將分區結構定義偵測模式設為 STRINGS。
   • 提供自己的：將分區結構定義偵測模式設為 CUSTOM，然後手動輸入分區鍵的結構定義資訊。詳情請參閱「提供自訂分區索引鍵結構定義」。
6. 選用：如要要求在對這個資料表進行的所有查詢中使用分區篩選器，請選取「Require partition filter」(需要分區篩選器) 核取方塊。使用分區篩選器可以降低成本並提升效能。詳情請參閱「在查詢中要求分區鍵的子句篩選器」。
5. 在「Destination」(目的地) 部分中，指定下列詳細資料：
   1. 在「Project」(專案) 部分，選取要建立資料表的專案。
   2. 在「Dataset」(資料集) 部分，選取要建立資料表的資料集。
   3. 在「Table」中，輸入要建立的資料表名稱。
   4. 在「Table type」(資料表類型) 中，選取「External table」(外部資料表)。
6. 在「Schema」(結構定義) 部分輸入結構定義。
7. 如要啟用結構定義的自動偵測功能，請選取「自動偵測」。
8. 如要忽略含有與結構定義不符之額外資料欄值的資料列，請展開「進階選項」部分，然後選取「不明的值」。
9. 點選「建立資料表」。

SQL

使用 CREATE EXTERNAL TABLE DDL 陳述式。

以下範例會自動偵測 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);

更改下列內容：

• SOURCE_FORMAT：外部資料來源的格式，例如 PARQUET
• GCS_URIS：Cloud Storage 資料夾的路徑，使用萬用字元格式
• GCS_URI_SHARED_PREFIX：不含萬用字元的來源 URI 前置字串
• BOOLEAN：是否要在查詢時要求述詞篩選器。這個標記是選用的，預設值為 false。

以下範例會在 WITH PARTITION COLUMNS 子句中列出自訂 Hive 分區索引鍵和類型，以便使用自訂分區索引鍵和類型：

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

更改下列內容：

• PARTITION_COLUMN_LIST：列出 Cloud Storage 資料夾路徑中相同順序的資料欄，格式如下：

KEY1 TYPE1, KEY2 TYPE2

以下範例會建立外部分區資料表。這個指令會使用結構定義自動偵測功能，同時偵測檔案結構定義和巢狀分割區版面配置。如果外部路徑為 gs://bucket/path/field_1=first/field_2=1/data.parquet，系統會將分區欄位偵測為 field_1 (STRING) 和 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);

以下範例會明確指定分區欄，藉此建立外部分區資料表。此範例假設外部檔案路徑具有 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

首先，請使用 bq mkdef 指令建立資料表定義檔：

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

更改下列內容：

• SOURCE_FORMAT：外部資料來源的格式。例如：CSV。
• PARTITIONING_MODE：Hive 分區模式。請使用下列其中一個值：
  • AUTO：自動偵測索引鍵名稱和類型。
  • STRINGS：自動將索引鍵名稱轉換為字串。
  • CUSTOM：在來源 URI 前置字串中編碼索引鍵結構定義。
• GCS_URI_SHARED_PREFIX：來源 URI 前置字串。
• BOOLEAN：指定是否要在查詢時要求述詞篩選器。這個標記是選用的，預設值為 false。
• GCS_URIS：Cloud Storage 資料夾的路徑，使用萬用字元格式。
• DEFINITION_FILE：本機電腦上資料表定義檔的路徑。

如果 PARTITIONING_MODE 是 CUSTOM，請在來源 URI 前置字串中加入分區索引鍵結構定義，並使用以下格式：

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

建立資料表定義檔後，請使用 bq mk 指令建立外部資料表：

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

更改下列內容：

• DEFINITION_FILE：資料表定義檔案的路徑。
• DATASET_NAME：包含資料表的資料集名稱。
• TABLE_NAME：您要建立的資料表名稱。
• SCHEMA：指定 JSON 結構定義檔的路徑，或以 field:data_type,field:data_type,... 格式指定結構定義。如要使用結構定義自動偵測功能，請省略這個引數。

範例

以下範例使用 AUTO Hive 分割模式：

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

以下範例使用 STRING Hive 分割模式：

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

以下範例使用 CUSTOM Hive 分割模式：

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

如要使用 BigQuery API 設定 Hive 分區，請在建立資料表定義檔時，在 ExternalDataConfiguration 物件中加入 hivePartitioningOptions 物件。

如果您將 hivePartitioningOptions.mode 欄位設為 CUSTOM，就必須在 hivePartitioningOptions.sourceUriPrefix 欄位中對分區索引鍵結構定義進行編碼，如下所示：gs://BUCKET/PATH_TO_TABLE/{KEY1:TYPE1}/{KEY2:TYPE2}/...

如要強制在查詢時使用述詞篩選器，請將 hivePartitioningOptions.requirePartitionFilter 欄位設為 true。

Java

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

SQL

使用 CREATE OR REPLACE EXTERNAL TABLE DDL 陳述式更新資料表：

1. 前往 Google Cloud 控制台的「BigQuery」頁面。

   前往 BigQuery

2. 在查詢編輯器中輸入以下陳述式：

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

   請依指示取代下列項目：

   • PROJECT_ID：包含資料表的專案名稱
   • DATASET：含有資料表的資料集名稱
   • EXTERNAL_TABLE_NAME：資料表名稱
   • REGION：包含連線的區域
   • CONNECTION_ID：要使用的連線名稱

     如要使用 預設連線，請指定 DEFAULT，而非包含 REGION.CONNECTION_ID 的連線字串。

   • TABLE_FORMAT：資料表使用的格式

     更新資料表時無法變更這項設定。

   • BUCKET_PATH：包含外部資料表資料的 Cloud Storage 值區路徑，格式為 ['gs://bucket_name/[folder_name/]file_name']。

     您可以在路徑中指定一個星號 (*) 萬用字元，從值區中選取多個檔案。例如，['gs://mybucket/file_name*']。詳情請參閱「Cloud Storage URI 的萬用字元支援」。

     您可以提供多個路徑，為 uris 選項指定多個值區。

     以下範例顯示有效的 uris 值：

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

     指定目標為多個檔案的 uris 值時，所有檔案都必須共用相容的結構定義。

     如要進一步瞭解如何在 BigQuery 中使用 Cloud Storage URI，請參閱「Cloud Storage 資源路徑」。

   • STALENESS_INTERVAL：指定是否允許作業使用針對資料表的快取中繼資料，以及作業必須使用多新鮮的快取中繼資料

     如要進一步瞭解中繼資料快取的考量事項，請參閱「為提升效能而快取中繼資料」。

     如要停用中繼資料快取功能，請指定 0。這是目前的預設做法。

     如要啟用中繼資料快取功能，請指定介於 30 分鐘至 7 天之間的 間隔文字常值。例如，指定 INTERVAL 4 HOUR 的過時間隔為 4 小時。使用這個值時，如果快取的中繼資料在過去 4 小時內已重新整理，對資料表執行的作業就會使用快取的中繼資料。如果快取的中繼資料較舊，則操作會改為從 Cloud Storage 擷取中繼資料。

   • CACHE_MODE：指定中繼資料快取是否自動或手動重新整理

     如要進一步瞭解中繼資料快取的考量事項，請參閱「為提升效能而快取中繼資料」。

     將其設為 AUTOMATIC，即可按照系統定義的間隔 (通常為 30 到 60 分鐘) 重新整理中繼資料快取。

     如果您想依照指定的時間表重新整理中繼資料快取，請將其設為 MANUAL。在這種情況下，您可以呼叫 BQ.REFRESH_EXTERNAL_METADATA_CACHE 系統程序來重新整理快取。

     如果 STALENESS_INTERVAL 設為大於 0 的值，您必須設定 CACHE_MODE。

3. 按一下 play_circle「Run」。

如要進一步瞭解如何執行查詢，請參閱「執行互動式查詢」一文。

bq

使用 bq mkdef 和 bq update 指令更新資料表：

1. 產生外部資料表定義，說明要變更的資料表層面：

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

   更改下列內容：

   • PROJECT_ID：包含連線的專案名稱
   • REGION：包含連線的區域
   • CONNECTION_ID：要使用的連線名稱
   • TABLE_FORMAT：資料表使用的格式。更新資料表時無法變更這項設定。

   • CACHE_MODE：指定中繼資料快取是自動或手動重新整理。如要進一步瞭解中繼資料快取考量事項，請參閱「中繼資料快取可提升效能」。

     將其設為 AUTOMATIC，即可按照系統定義的間隔 (通常為 30 到 60 分鐘) 重新整理中繼資料快取。

     如果您想依據自訂的時間表重新整理中繼資料快取，請將其設為 MANUAL。在這種情況下，您可以呼叫 BQ.REFRESH_EXTERNAL_METADATA_CACHE 系統程序來重新整理快取。

     如果 STALENESS_INTERVAL 設為大於 0 的值，您必須設定 CACHE_MODE。

   • BUCKET_PATH：包含外部資料表資料的 Cloud Storage 值區路徑，格式為 gs://bucket_name/[folder_name/]file_name。

     您可以在路徑中指定一個星號 (*) 萬用字元，藉此限制從值區中選取的檔案。例如，gs://mybucket/file_name*。詳情請參閱「Cloud Storage URI 的萬用字元支援」。

     您可以提供多個路徑，為 uris 選項指定多個值區。

     以下範例顯示有效的 uris 值：

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

     指定以多個檔案為目標的 uris 值時，所有檔案都必須共用相容的結構定義。

     如要進一步瞭解如何在 BigQuery 中使用 Cloud Storage URI，請參閱「Cloud Storage 資源路徑」。

   • DEFINITION_FILE：您要建立的資料表定義檔案名稱。

2. 使用新的外部資料表定義更新資料表：

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

   更改下列內容：

   • STALENESS_INTERVAL：指定作業是否會使用針對資料表的快取中繼資料，以及作業必須使用多新鮮的快取中繼資料。如要進一步瞭解中繼資料快取的考量事項，請參閱「為提升效能而快取中繼資料」。

     如要停用中繼資料快取功能，請指定 0。這是目前的預設做法。

     如要啟用中繼資料快取功能，請使用 INTERVAL 資料類型說明文件中所述的 Y-M D H:M:S 格式，指定 30 分鐘至 7 天之間的間隔值。例如，指定 0-0 0 4:0:0 為 4 小時的過時間隔。使用這個值時，如果資料表在過去 4 小時內已重新整理，針對資料表執行的作業會使用快取的中繼資料。如果快取的中繼資料較舊，作業會改為從 Cloud Storage 擷取中繼資料。

   • DEFINITION_FILE：您建立或更新的資料表定義檔案名稱。

   • PROJECT_ID：包含資料表的專案名稱

   • DATASET：含有資料表的資料集名稱

   • EXTERNAL_TABLE_NAME：資料表名稱