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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

BQ

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

(אופציונלי) מציינים את הדגל --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. מפעילים את ה-method‏ jobs.insert כדי להריץ את השאילתה באופן אסינכרוני, או את ה-method‏ jobs.query כדי להריץ את השאילתה באופן סינכרוני, ומעבירים את האובייקט 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 מנסה לדלג על קריאת קבצים שלא עומדים בתנאי הסינון. המלצות דומות ל שאילתות בטבלאות שמחולקות למחיצות לפי זמני הטמעת הנתונים באמצעות עמודות פסאודו חלות כשיוצרים פרדיקטים של שאילתות באמצעות עמודת הפסאודו _FILE_NAME.

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

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

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