יצירת טבלאות חיצוניות ב-Cloud Storage

המסוף

1. עוברים לדף BigQuery.

   כניסה ל-BigQuery

2. בחלונית הימנית, לוחצים על explore כלי הניתוחים:

   

   אם החלונית הימנית לא מוצגת, לוחצים על last_page הרחבת החלונית הימנית כדי לפתוח אותה.

3. בחלונית Explorer, מרחיבים את הפרויקט, לוחצים על Datasets ואז בוחרים מערך נתונים.

4. מרחיבים את האפשרות more_vert Actions (פעולות) ולוחצים על Create table (יצירת טבלה).

5. בקטע מקור, מציינים את הפרטים הבאים:

   1. בקטע Create table from (יצירת טבלה מתוך), בוחרים באפשרות Google Cloud Storage.

   2. בקטע Select file from GCS bucket or use a URI pattern (בחירת קובץ ממאגר GCS או שימוש בתבנית URI), מעיינים כדי לבחור מאגר וקובץ לשימוש, או מקלידים את הנתיב בפורמט gs://bucket_name/[folder_name/]file_name.

      אי אפשר לציין כמה כתובות URI במסוף Cloud de Confiance , אבל אפשר לבחור כמה קבצים על ידי ציון כוכבית אחת (*) כתו כללי. לדוגמה, gs://mybucket/file_name*. מידע נוסף זמין במאמר בנושא תמיכה בתווים כלליים בכתובות URI של Cloud Storage.

      קטגוריה של Cloud Storage צריכה להיות באותו מיקום של מערך הנתונים שמכיל את הטבלה שאתם יוצרים.

   3. בקטע פורמט קובץ, בוחרים את הפורמט שמתאים לקובץ.

6. בקטע יעד, מציינים את הפרטים הבאים:

   1. בשדה Project, בוחרים את הפרויקט שבו רוצים ליצור את הטבלה.

   2. בקטע Dataset (מערך נתונים), בוחרים את מערך הנתונים שבו רוצים ליצור את הטבלה.

   3. בשדה Table (טבלה), מזינים את שם הטבלה שיוצרים.

   4. בשדה Table type (סוג הטבלה), בוחרים באפשרות External table (טבלה חיצונית).

7. בקטע סכימה, אתם יכולים להפעיל זיהוי אוטומטי של סכימה או לציין סכימה באופן ידני אם יש לכם קובץ מקור. אם אין לכם קובץ מקור, אתם צריכים לציין סכימה באופן ידני.

   • כדי להפעיל זיהוי אוטומטי של סכימה, בוחרים באפשרות זיהוי אוטומטי.

   • כדי לציין סכימה באופן ידני, משאירים את האפשרות זיהוי אוטומטי לא מסומנת. מפעילים את האפשרות Edit as text ומזינים את סכימת הטבלה כמערך JSON.

8. כדי להתעלם משורות עם ערכי עמודות נוספים שלא תואמים לסכימה, מרחיבים את הקטע אפשרויות מתקדמות ובוחרים באפשרות ערכים לא ידועים.

9. לוחצים על יצירת טבלה.

אחרי שיוצרים את הטבלה הקבועה, אפשר להריץ שאילתה על הטבלה כאילו היא הייתה טבלה ב-BigQuery. אחרי שהשאילתה מסתיימת, אפשר לייצא את התוצאות כקובצי CSV או JSON, לשמור את התוצאות כטבלה או לשמור את התוצאות ב-Google Sheets.

SQL

כדי ליצור טבלה חיצונית קבועה, מריצים את הצהרת ה-DDL‏ CREATE EXTERNAL TABLE. אפשר לציין את הסכימה באופן מפורש, או להשתמש בזיהוי אוטומטי של סכימה כדי להסיק את הסכימה מהנתונים החיצוניים.

1. במסוף Cloud de Confiance , עוברים לדף 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*']. מידע נוסף זמין במאמר תמיכה בתווים כלליים בכתובות URI של Cloud Storage.

     אפשר לציין כמה דליים לאפשרות uris על ידי ציון כמה נתיבים.

     בדוגמאות הבאות מוצגים ערכים חוקיים של uris:

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

     כשמציינים uris ערכים שמטרגטים כמה קבצים, לכל הקבצים האלה צריכה להיות סכימה תואמת.

     מידע נוסף על שימוש בכתובות URI של Cloud Storage ב-BigQuery זמין במאמר בנושא נתיב משאב של Cloud Storage.

3. לוחצים על play_circle הפעלה.

מידע נוסף על הרצת שאילתות זמין במאמר הרצת שאילתה אינטראקטיבית.

דוגמאות

בדוגמה הבאה נעשה שימוש בזיהוי אוטומטי של סכימה כדי ליצור טבלה חיצונית בשם sales שמקושרת לקובץ CSV שמאוחסן ב-Cloud Storage:

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. מידע נוסף זמין במאמר בנושא תמיכה בתווים כלליים בכתובות URI של Cloud Storage.

  אפשר לציין כמה דליים לאפשרות uris על ידי ציון כמה נתיבים.

  בדוגמאות הבאות מוצגים ערכים חוקיים של uris:

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

  כשמציינים ערכים של uris שמטרגטים כמה קבצים, לכל הקבצים האלה צריכה להיות סכימה תואמת.

  מידע נוסף על שימוש בכתובות URI של Cloud Storage ב-BigQuery זמין במאמר בנושא נתיב משאב ב-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

כדי להשתמש בזיהוי אוטומטי של סכימה, מגדירים את הדגל --autodetect=true בפקודה mkdef ומשמיטים את הסכימה:

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. מידע נוסף זמין במאמר בנושא תמיכה בתווים כלליים בכתובות URI של Cloud Storage.

  אפשר לציין כמה דליים לאפשרות uris על ידי ציון כמה נתיבים.

  בדוגמאות הבאות מוצגים ערכים חוקיים של uris:

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

  כשמציינים ערכים של uris שמטרגטים כמה קבצים, לכל הקבצים האלה צריכה להיות סכימה תואמת.

  מידע נוסף על שימוש בכתובות URI של Cloud Storage ב-BigQuery זמין במאמר בנושא נתיב משאב ב-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

מבצעים קריאה ל-method של API‏ tables.insert ויוצרים ExternalDataConfiguration במשאב Table שמעבירים.

מציינים את המאפיין 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. במסוף Cloud de Confiance , עוברים אל BigQuery.

   כניסה לדף BigQuery

2. בחלונית הימנית, לוחצים על explore כלי הניתוחים.
3. בחלונית Explorer, מרחיבים את הפרויקט, לוחצים על Datasets ובוחרים מערך נתונים.
4. לוחצים על more_vert פעולות ואז על יצירת טבלה. תיפתח החלונית Create table.
5. בקטע מקור, מציינים את הפרטים הבאים:
1. בקטע Create table from (יצירת טבלה מתוך), בוחרים באפשרות Google Cloud Storage.
2. בקטע Select file from Cloud Storage bucket (בחירת קובץ מקטגוריה של Cloud Storage), מזינים את הנתיב לתיקייה ב-Cloud Storage באמצעות wildcards. לדוגמה, my_bucket/my_files*. הקטגוריה של Cloud Storage צריכה להיות באותו מיקום כמו מערך הנתונים שמכיל את הטבלה שרוצים ליצור, להוסיף לה נתונים או להחליף אותה.
3. מהרשימה פורמט קובץ בוחרים את סוג הקובץ.
4. מסמנים את תיבת הסימון חלוקת נתוני המקור למחיצות, ובשדה בחירת קידומת URI של המקור מזינים את קידומת ה-URI של Cloud Storage. לדוגמה, gs://my_bucket/my_files.
5. בקטע Partition inference mode (מצב הסקה של מחיצות), בוחרים באחת מהאפשרויות הבאות:
   • הסקת סוגים באופן אוטומטי: הגדרת מצב הזיהוי של סכימת החלוקה למחיצות לערך AUTO.
   • כל העמודות הן מחרוזות: מגדירים את מצב זיהוי הסכימה של המחיצה ל-STRINGS.
   • אני רוצה לספק משלי: מגדירים את מצב הזיהוי של סכימת המחיצה ל-CUSTOM ומזינים באופן ידני את פרטי הסכימה של מפתחות המחיצה. מידע נוסף מופיע במאמר בנושא הגדרת סכימת מפתח מחיצה בהתאמה אישית.
6. אופציונלי: כדי לדרוש מסנן מחיצה בכל השאילתות של הטבלה הזו, מסמנים את תיבת הסימון דרישת מסנן מחיצה. הוספת מסנן מחיצות יכולה להפחית את העלות ולשפר את הביצועים. מידע נוסף מופיע במאמר בנושא הוספת מסנני פרדיקט למפתחות של מחיצות בשאילתות.
6. בקטע יעד, מציינים את הפרטים הבאים:
   1. בקטע Project (פרויקט), בוחרים את הפרויקט שבו רוצים ליצור את הטבלה.
   2. בקטע Dataset (מערך נתונים), בוחרים את מערך הנתונים שבו רוצים ליצור את הטבלה.
   3. בשדה Table (טבלה), מזינים את שם הטבלה שרוצים ליצור.
   4. בשדה Table type (סוג הטבלה), בוחרים באפשרות External table (טבלה חיצונית).
7. בקטע Schema (סכימה), מזינים את הגדרת הסכימה.
8. כדי להפעיל את הזיהוי האוטומטי של סכימה, בוחרים באפשרות זיהוי אוטומטי.
9. כדי להתעלם משורות עם ערכי עמודות נוספים שלא תואמים לסכימה, מרחיבים את הקטע אפשרויות מתקדמות ובוחרים באפשרות ערכים לא ידועים.
10. לוחצים על יצירת טבלה.

SQL

משתמשים בהצהרת DDL‏ CREATE EXTERNAL TABLE.

בדוגמה הבאה נעשה שימוש בזיהוי אוטומטי של מפתחות חלוקה של 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, בפורמט של wildcard
• ‫GCS_URI_SHARED_PREFIX: הקידומת של ה-URI של המקור בלי התו הכללי לחיפוש
• ‫BOOLEAN: האם נדרש מסנן של תנאי בזמן השאילתה. הדגל הזה הוא אופציונלי. ערך ברירת המחדל הוא false.

בדוגמה הבאה נעשה שימוש בסוגים ובמפתחות מותאמים אישית של מחיצות Hive על ידי רישום שלהם בסעיף 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);

מחליפים את מה שכתוב בשדות הבאים:

• ‫PARTITION_COLUMN_LIST: רשימת עמודות לפי אותו סדר בנתיב של תיקיית Cloud Storage, בפורמט:

KEY1 TYPE1, KEY2 TYPE2

בדוגמה הבאה נוצרת טבלה מחולקת למחיצות (Partitions) חיצונית. הוא משתמש בזיהוי אוטומטי של סכימה כדי לזהות את סכימת הקובץ ואת פריסת החלוקה למחיצות של Hive. אם הנתיב החיצוני הוא 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, בפורמט של wildcard.
• ‫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,.... כדי להשתמש בזיהוי אוטומטי של סכימה, משמיטים את הארגומנט הזה.

דוגמאות

בדוגמה הבאה נעשה שימוש במצב החלוקה למחיצות של 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

בדוגמה הבאה נעשה שימוש במצב החלוקה למחיצות של 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

בדוגמה הבאה נעשה שימוש במצב החלוקה למחיצות של 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

כדי להגדיר חלוקה למחיצות ב-Hive באמצעות BigQuery API, צריך לכלול אובייקט hivePartitioningOptions באובייקט ExternalDataConfiguration כשיוצרים את קובץ הגדרת הטבלה.

אם מגדירים את השדה 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. במסוף Cloud de Confiance , עוברים לדף 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*']. מידע נוסף זמין במאמר תמיכה בתווים כלליים בכתובות URI של Cloud Storage.

     אפשר לציין כמה דליים לאפשרות uris על ידי ציון כמה נתיבים.

     בדוגמאות הבאות מוצגים ערכים חוקיים של uris:

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

     כשמציינים uris ערכים שמטרגטים כמה קבצים, לכל הקבצים האלה צריכה להיות סכימה תואמת.

     מידע נוסף על שימוש בכתובות URI של Cloud Storage ב-BigQuery זמין במאמר בנושא נתיב משאב של Cloud Storage.

   • ‫STALENESS_INTERVAL: מציין אם פעולות שמתבצעות בטבלה משתמשות במטא-נתונים ששמורים במטמון, ומה רמת העדכניות של המטא-נתונים ששמורים במטמון שנדרשת כדי שהפעולה תוכל להשתמש בהם.

     מידע נוסף על שיקולים לגבי שמירת מטא-נתונים במטמון זמין במאמר שמירת מטא-נתונים במטמון לשיפור הביצועים.

     כדי להשבית את השמירה במטמון של המטא-נתונים, מציינים את הערך 0. (זוהי ברירת המחדל)

     כדי להפעיל שמירת מטא-נתונים במטמון, צריך לציין ערך של מילול של מרווח בין 30 דקות ל-7 ימים. לדוגמה, כדי לציין מרווח של 4 שעות, צריך להזין INTERVAL 4 HOUR. אם הערך הזה מוגדר, פעולות שמתבצעות בטבלה משתמשות במטא נתונים ששמורים במטמון אם הם רעננו ב-4 השעות האחרונות. אם המטא-נתונים שבמטמון ישנים יותר, הפעולה מאחזרת מטא-נתונים מ-Cloud Storage במקום זאת.

   • ‫CACHE_MODE: מציין אם המטמון של המטא-נתונים מתרענן באופן אוטומטי או ידני

     מידע נוסף על שיקולים בנוגע לשמירה במטמון של מטא-נתונים זמין במאמר שמירה במטמון של מטא-נתונים לשיפור הביצועים.

     מגדירים את הערך AUTOMATIC כדי שהמטמון של המטא-נתונים יתעדכן במרווח זמן שמוגדר על ידי המערכת, בדרך כלל בין 30 ל-60 דקות.

     מגדירים את הערך MANUAL אם רוצים לרענן את מטמון המטא-נתונים לפי לוח זמנים שקובעים. במקרה כזה, אפשר להתקשר אל BQ.REFRESH_EXTERNAL_METADATA_CACHE הליך המערכת כדי לרענן את המטמון.

     אם מגדירים את STALENESS_INTERVAL לערך גדול מ-0, צריך להגדיר גם את CACHE_MODE.

3. לוחצים על play_circle הפעלה.

מידע נוסף על הרצת שאילתות זמין במאמר הרצת שאילתה אינטראקטיבית.

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 ההליך של המערכת כדי לרענן את המטמון.

     אם הערך של CACHE_MODE גדול מ-0, צריך להגדיר את STALENESS_INTERVAL.

   • ‫BUCKET_PATH: הנתיב לדלי Cloud Storage שמכיל את הנתונים של הטבלה החיצונית, בפורמט gs://bucket_name/[folder_name/]file_name.

     כדי להגביל את הקבצים שנבחרו מהמאגר, מציינים כוכבית אחת (*) בנתיב. לדוגמה, gs://mybucket/file_name*. מידע נוסף זמין במאמר בנושא תמיכה בתווים כלליים בכתובות URI של Cloud Storage.

     אפשר לציין כמה דליים לאפשרות uris על ידי ציון כמה נתיבים.

     בדוגמאות הבאות מוצגים ערכים חוקיים של uris:

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

     כשמציינים ערכים של uris שמטרגטים כמה קבצים, לכל הקבצים האלה צריכה להיות סכימה תואמת.

     מידע נוסף על שימוש בכתובות URI של Cloud Storage ב-BigQuery זמין במאמר בנושא נתיב משאב ב-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. (זוהי ברירת המחדל)

     כדי להפעיל שמירת מטא-נתונים במטמון, צריך לציין ערך של מרווח זמן בין 30 דקות ל-7 ימים, באמצעות הפורמט Y-M D H:M:S שמתואר במסמכי התיעוד של סוג הנתונים INTERVAL. לדוגמה, כדי להגדיר את מרווח הרענון ל-4 שעות, מציינים 0-0 0 4:0:0. אם הערך הזה מוגדר, פעולות שמתבצעות בטבלה משתמשות במטא נתונים שנשמרו במטמון אם הם רעננו ב-4 השעות האחרונות. אם המטא-נתונים שבמטמון ישנים יותר, הפעולה מאחזרת מטא-נתונים מ-Cloud Storage במקום זאת.

   • ‫DEFINITION_FILE: השם של קובץ הגדרת הטבלה שיצרתם או עדכנתם.

   • ‫PROJECT_ID: שם הפרויקט שמכיל את הטבלה

   • ‫DATASET: השם של מערך הנתונים שמכיל את הטבלה

   • ‫EXTERNAL_TABLE_NAME: שם הטבלה