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

במאמר הזה מוסבר איך לשלוח שאילתות לנתונים שמאוחסנים בטבלה חיצונית של Cloud Storage.

לפני שמתחילים

מוודאים שיש לכם טבלה חיצונית של Cloud Storage.

התפקידים הנדרשים

כדי לשלוח שאילתות לטבלאות חיצוניות ב-Cloud Storage, צריך לוודא שיש לכם את התפקידים הבאים:

• צפייה בנתוני BigQuery ‏ (roles/bigquery.dataViewer)
• משתמש BigQuery‏ (roles/bigquery.user)
• צפייה באובייקטים באחסון (roles/storage.objectViewer)

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

כדי לראות את ההרשאות המדויקות שנדרשות ב-BigQuery כדי לשלוח שאילתות לטבלאות חיצוניות, מרחיבים את הקטע ההרשאות הנדרשות:

ההרשאות הנדרשות

• bigquery.jobs.create
• bigquery.readsessions.create (נדרש רק אם קוראים נתונים באמצעות BigQuery Storage Read API)
• bigquery.tables.get
• bigquery.tables.getData

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

שליחת שאילתות לטבלאות חיצוניות קבועות

אחרי שיוצרים טבלה חיצונית ב-Cloud Storage, אפשר לשלוח אליה שאילתות באמצעות תחביר GoogleSQL, בדיוק כמו בטבלה רגילה ב-BigQuery. לדוגמה, SELECT field1, field2 FROM mydataset.my_cloud_storage_table;.

שאילתות על טבלאות חיצוניות זמניות

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

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

• קובץ הגדרת טבלה
• הגדרת סכימה מוטבעת
• קובץ סכימת JSON

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

כשמשתמשים בטבלה חיצונית זמנית, לא יוצרים טבלה באחד ממערכי הנתונים של BigQuery. אי אפשר לשתף את הטבלה עם אחרים כי היא לא נשמרת לצמיתות במערך נתונים.

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

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 method כדי להריץ את השאילתה באופן אסינכרוני, או ל-jobs.query method כדי להריץ את השאילתה באופן סינכרוני, ומעבירים את האובייקט Job.

Java

לפני שמנסים את הדוגמה הזו, צריך לפעול לפי Javaהוראות ההגדרה שבמדריך למתחילים של BigQuery באמצעות ספריות לקוח. מידע נוסף מופיע במאמרי העזרה של BigQuery Java API.

כדי לבצע אימות ב-BigQuery, צריך להגדיר את Application Default Credentials. מידע נוסף זמין במאמר הגדרת אימות לספריות לקוח.

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

לפני שמנסים את הדוגמה הזו, צריך לפעול לפי Node.jsהוראות ההגדרה שבמדריך למתחילים של BigQuery באמצעות ספריות לקוח. מידע נוסף מופיע במאמרי העזרה של BigQuery Node.js API.

כדי לבצע אימות ב-BigQuery, צריך להגדיר את Application Default Credentials. מידע נוסף זמין במאמר הגדרת אימות לספריות לקוח.

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

לפני שמנסים את הדוגמה הזו, צריך לפעול לפי Pythonהוראות ההגדרה שבמדריך למתחילים של BigQuery באמצעות ספריות לקוח. מידע נוסף מופיע במאמרי העזרה של BigQuery Python API.

כדי לבצע אימות ב-BigQuery, צריך להגדיר את Application Default Credentials. מידע נוסף זמין במאמר הגדרת אימות לספריות לקוח.

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

שאילתה בעמודה הווירטואלית _FILE_NAME

טבלאות שמבוססות על מקורות נתונים חיצוניים מספקות עמודה וירטואלית בשם _FILE_NAME. העמודה הזו מכילה את הנתיב המוגדר במלואו לקובץ שאליו השורה שייכת. העמודה הזו זמינה רק לטבלאות שמפנות לנתונים חיצוניים שמאוחסנים ב-Cloud Storage, ב-Google Drive, ב-Amazon S3 וב-Azure Blob Storage.

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

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

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

• ‫PROJECT_ID הוא מזהה פרויקט תקין (הדגל הזה לא נדרש אם משתמשים ב-Cloud Shell או אם מגדירים פרויקט ברירת מחדל ב-Google Cloud CLI)
• ‫DATASET הוא השם של מערך הנתונים שבו מאוחסנת הטבלה החיצונית הקבועה.
• ‫TABLE_NAME הוא השם של הטבלה החיצונית הקבועה

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

אופטימיזציה של שאילתות בטבלאות חיצוניות

כדאי להפעיל את Anywhere Cache כשמבצעים שאילתות על נתונים ב-Cloud Storage באמצעות טבלאות חיצוניות. ‫Anywhere Cache מספק מטמון קריאה אזורי שמגובה על ידי SSD עבור קטגוריות Cloud Storage, שיכול לשפר את ביצועי השאילתות ולהפחית את עלויות השאילתות כששולחים שאילתות על טבלאות חיצוניות. מידע נוסף זמין במאמר בנושא אופטימיזציה של שאילתות בטבלאות חיצוניות ב-Cloud Storage.

המאמרים הבאים

• מידע נוסף על שימוש ב-SQL ב-BigQuery
• מידע נוסף על טבלאות חיצוניות
• מידע נוסף על מכסות ב-BigQuery