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

BQ

שולחים שאילתה לטבלה זמנית שמקושרת למקור נתונים חיצוני באמצעות הפקודה bq query עם הדגל --external_table_definition. כשמשתמשים בכלי שורת הפקודה של BigQuery כדי לשלוח שאילתה לטבלה זמנית שמקושרת למקור נתונים חיצוני, אפשר לזהות את הסכימה של הטבלה באמצעות:

• קובץ הגדרת טבלה (מאוחסן במחשב המקומי)
• הגדרת סכימה מוטבעת
• קובץ סכימת JSON (שמאוחסן במחשב המקומי)

(אופציונלי) מציינים את הדגל --location ומגדירים את הערך למיקום.

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

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

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

• ‫LOCATION: השם של המיקום. הדגל --location הוא אופציונלי. לדוגמה, אם אתם משתמשים ב-BigQuery באזור טוקיו, אתם יכולים להגדיר את הערך של הדגל ל-asia-northeast1. אפשר להגדיר ערך ברירת מחדל למיקום באמצעות הקובץ ‎.bigqueryrc.
• ‫TABLE: השם של הטבלה הזמנית שיוצרים.
• ‫DEFINITION_FILE: הנתיב אל קובץ הגדרת הטבלה במחשב המקומי.
• ‫QUERY: השאילתה שאתם שולחים לטבלה הזמנית.

לדוגמה, הפקודה הבאה יוצרת שאילתה על טבלה זמנית בשם sales באמצעות קובץ הגדרת טבלה בשם sales_def.

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

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

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

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

• ‫LOCATION: השם של המיקום. הדגל --location הוא אופציונלי. לדוגמה, אם אתם משתמשים ב-BigQuery באזור טוקיו, אתם יכולים להגדיר את הערך של הדגל ל-asia-northeast1. אפשר להגדיר ערך ברירת מחדל למיקום באמצעות הקובץ ‎.bigqueryrc.
• ‫TABLE: השם של הטבלה הזמנית שיוצרים.
• ‫SCHEMA: הגדרת הסכימה במקום בפורמט field:data_type,field:data_type.
• ‫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.

• ‫QUERY: השאילתה שאתם שולחים לטבלה הזמנית.

לדוגמה, הפקודה הבאה יוצרת טבלה זמנית בשם sales ומבצעת עליה שאילתה. הטבלה מקושרת לקובץ CSV שמאוחסן ב-Cloud Storage עם הגדרת הסכימה הבאה: Region:STRING,Quarter:STRING,Total_sales:INTEGER.

bq query \
--external_table_definition=sales::Region:STRING,Quarter:STRING,Total_sales:INTEGER@CSV=gs://mybucket/sales.csv \
'SELECT
  Region,
  Total_sales
FROM
  sales'

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

bq --location=LOCATION query \
--external_table_definition=SCHEMA_FILE@SOURCE_FORMAT=BUCKET_PATH \
'QUERY'

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

• ‫LOCATION: השם של המיקום. הדגל --location הוא אופציונלי. לדוגמה, אם אתם משתמשים ב-BigQuery באזור טוקיו, אתם יכולים להגדיר את הערך של הדגל ל-asia-northeast1. אפשר להגדיר ערך ברירת מחדל למיקום באמצעות הקובץ ‎.bigqueryrc.
• ‫SCHEMA_FILE: הנתיב לקובץ סכימת ה-JSON במחשב המקומי.
• ‫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.

• ‫QUERY: השאילתה שאתם שולחים לטבלה הזמנית.

לדוגמה, הפקודה הבאה יוצרת שאילתה בטבלה זמנית בשם sales שמקושרת לקובץ CSV שמאוחסן ב-Cloud Storage באמצעות קובץ הסכימה /tmp/sales_schema.json.

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

API

כדי להריץ שאילתה באמצעות ה-API, צריך לבצע את השלבים הבאים:

1. יוצרים אובייקט Job.
2. מאכלסים את הקטע configuration באובייקט Job באמצעות אובייקט JobConfiguration.
3. מאכלסים את הקטע query באובייקט JobConfiguration באמצעות אובייקט JobConfigurationQuery.
4. מאכלסים את הקטע tableDefinitions של אובייקט JobConfigurationQuery באובייקט ExternalDataConfiguration.
5. מבצעים קריאה ל-jobs.insert כדי להריץ את השאילתה באופן אסינכרוני, או ל-jobs.query כדי להריץ את השאילתה באופן סינכרוני, ומעבירים את האובייקט Job.

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.TableResult;

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

  public static void runQueryExternalGCSTemp() {
    // TODO(developer): Replace these variables before running the sample.
    String tableName = "MY_TABLE_NAME";
    String sourceUri = "gs://cloud-samples-data/bigquery/us-states/us-states.csv";
    Schema schema =
        Schema.of(
            Field.of("name", StandardSQLTypeName.STRING),
            Field.of("post_abbr", StandardSQLTypeName.STRING));
    String query = String.format("SELECT * FROM %s WHERE name LIKE 'W%%'", tableName);
    queryExternalGCSTemp(tableName, sourceUri, schema, query);
  }

  public static void queryExternalGCSTemp(
      String tableName, String sourceUri, Schema schema, String query) {
    try {
      // Initialize client that will be used to send requests. This client only needs to be created
      // once, and can be reused for multiple requests.
      BigQuery bigquery = BigQueryOptions.getDefaultInstance().getService();

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

      // Configure the external data source and query job.
      ExternalTableDefinition externalTable =
          ExternalTableDefinition.newBuilder(sourceUri, csvOptions).setSchema(schema).build();
      QueryJobConfiguration queryConfig =
          QueryJobConfiguration.newBuilder(query)
              .addTableDefinition(tableName, externalTable)
              .build();

      // Example query to find states starting with 'W'
      TableResult results = bigquery.query(queryConfig);

      results
          .iterateAll()
          .forEach(row -> row.forEach(val -> System.out.printf("%s,", val.toString())));

      System.out.println("Query on external temporary table performed successfully.");
    } catch (BigQueryException | InterruptedException e) {
      System.out.println("Query not performed \n" + e.toString());
    }
  }
}

Node.js

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

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

  const tableId = 'us_states';

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

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

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

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

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

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

Python

from google.cloud import bigquery

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

# Configure the external data source and query job.
external_config = bigquery.ExternalConfig("CSV")
external_config.source_uris = [
    "gs://cloud-samples-data/bigquery/us-states/us-states.csv"
]
external_config.schema = [
    bigquery.SchemaField("name", "STRING"),
    bigquery.SchemaField("post_abbr", "STRING"),
]
external_config.options.skip_leading_rows = 1
table_id = "us_states"
job_config = bigquery.QueryJobConfig(table_definitions={table_id: external_config})

# Example query to find states starting with 'W'.
sql = 'SELECT * FROM `{}` WHERE name LIKE "W%"'.format(table_id)

query_job = client.query(sql, job_config=job_config)  # Make an API request.

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