הרצת שאילתות על טבלאות חיצוניות ב-Drive

במאמר הזה מוסבר איך לשלוח שאילתות לנתונים שמאוחסנים בטבלה חיצונית ב-Google Drive.

ב-BigQuery אפשר להריץ שאילתות על קבצים אישיים ועל קבצים משותפים ב-Drive. מידע נוסף על Drive זמין במאמר הדרכה ועזרה בנושא Google Drive.

אתם יכולים לשלוח שאילתות לנתונים ב-Drive מטבלה חיצונית קבועה או מטבלה חיצונית זמנית שאתם יוצרים כשאתם מריצים את השאילתה.

מגבלות

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

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

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

  • צפייה בנתוני BigQuery‏ (roles/bigquery.dataViewer)
  • משתמש BigQuery‏ (roles/bigquery.user)

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

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

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

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

הרשאות ב-Drive

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

היקפי הרשאות למכונות של Compute Engine

כשיוצרים מכונה של Compute Engine, אפשר לציין רשימה של היקפי הרשאות למכונה. ההיקפים קובעים את הגישה של המכונה למוצרי Google Cloud, כולל Drive. אפליקציות שפועלות במכונה הווירטואלית משתמשות בחשבון השירות כדי לשלוח קריאות לממשקי ה-API של Google Cloud .

אם אתם מגדירים מכונה של Compute Engine כך שתפעל כחשבון שירות, וחשבון השירות הזה ניגש לטבלה חיצונית שמקושרת למקור נתונים ב-Drive, אתם צריכים להוסיף למכונה את היקף ההרשאות של OAuth ל-Drive (https://www.googleapis.com/auth/drive.readonly).

במאמר שינוי חשבון השירות והיקפי הגישה של מכונה מוסבר איך להחיל היקפי הרשאות על מכונה של Compute Engine. מידע נוסף על חשבונות שירות של Compute Engine זמין במאמר חשבונות שירות.

שליחת שאילתות לנתונים ב-Drive באמצעות טבלאות חיצוניות קבועות

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

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

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

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

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

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

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

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

BQ

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

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

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

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

כאשר:

  • LOCATION הוא המיקום שלך. הדגל --location הוא אופציונלי.
  • ‫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=DRIVE_URI \
'QUERY'

כאשר:

  • LOCATION הוא המיקום שלך. הדגל --location הוא אופציונלי.
  • ‫TABLE הוא השם של הטבלה הזמנית שאתם יוצרים.
  • ‫SCHEMA היא הגדרת הסכימה בתוך השורה בפורמט FIELD:DATA_TYPE,FIELD:DATA_TYPE.
  • ‫SOURCE_FORMAT הוא CSV,‏ NEWLINE_DELIMITED_JSON,‏ AVRO או GOOGLE_SHEETS.
  • ‫DRIVE_URI הוא ה-URI של Drive.
  • ‫QUERY היא השאילתה שאתם שולחים לטבלה הזמנית.

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

bq --location=US query \
--external_table_definition=sales::Region:STRING,Quarter:STRING,Total_sales:INTEGER@CSV=https://drive.google.com/open?id=1234_AbCD12abCd \
'SELECT
   Region,Total_sales
 FROM
   sales'

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

bq --location=LOCATION query \
--external_table_definition=SCHEMA_FILE@SOURCE_FORMT=DRIVE_URI \
'QUERY'

כאשר:

  • LOCATION הוא המיקום שלך. הדגל --location הוא אופציונלי.
  • ‫SCHEMA_FILE הוא הנתיב לקובץ הסכימה של JSON במחשב המקומי.
  • ‫SOURCE_FILE הוא CSV,‏ NEWLINE_DELIMITED_JSON,‏ AVRO או GOOGLE_SHEETS.
  • ‫DRIVE_URI הוא ה-URI של Drive.
  • ‫QUERY היא השאילתה שאתם שולחים לטבלה הזמנית.

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

bq query \
--external_table_definition=sales::/tmp/sales_schema.json@CSV=https://drive.google.com/open?id=1234_AbCD12abCd \
'SELECT
   Total_sales
 FROM
   sales'

API

Python

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

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

from google.cloud import bigquery
import google.auth

# Create credentials with Drive & BigQuery API scopes.
# Both APIs must be enabled for your project before running this code.
credentials, project = google.auth.default(
    scopes=[
        "https://www.googleapis.com/auth/drive",
        "https://www.googleapis.com/auth/bigquery",
    ]
)

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

# Configure the external data source and query job.
external_config = bigquery.ExternalConfig("GOOGLE_SHEETS")

# Use a shareable link or grant viewing access to the email address you
# used to authenticate with BigQuery (this example Sheet is public).
sheet_url = (
    "https://docs.google.com/spreadsheets"
    "/d/1i_QCL-7HcSyUZmIbP9E6lO_T5u3HnpLe7dnpHaijg_E/edit?usp=sharing"
)
external_config.source_uris = [sheet_url]
external_config.schema = [
    bigquery.SchemaField("name", "STRING"),
    bigquery.SchemaField("post_abbr", "STRING"),
]
external_config.options.skip_leading_rows = 1  # Optionally skip header row.
external_config.options.range = (
    "us-states!A20:B49"  # Optionally set range of the sheet to query from.
)
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.

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

Java

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

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

import com.google.auth.oauth2.GoogleCredentials;
import com.google.auth.oauth2.ServiceAccountCredentials;
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.Field;
import com.google.cloud.bigquery.GoogleSheetsOptions;
import com.google.cloud.bigquery.QueryJobConfiguration;
import com.google.cloud.bigquery.Schema;
import com.google.cloud.bigquery.StandardSQLTypeName;
import com.google.cloud.bigquery.TableResult;
import com.google.common.collect.ImmutableSet;
import java.io.IOException;

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

  public static void main(String[] args) {
    // TODO(developer): Replace these variables before running the sample.
    String tableName = "MY_TABLE_NAME";
    String sourceUri =
        "https://docs.google.com/spreadsheets/d/1i_QCL-7HcSyUZmIbP9E6lO_T5u3HnpLe7dnpHaijg_E/edit?usp=sharing";
    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);
    queryExternalSheetsTemp(tableName, sourceUri, schema, query);
  }

  public static void queryExternalSheetsTemp(
      String tableName, String sourceUri, Schema schema, String query) {
    try {

      // Create credentials with Drive & BigQuery API scopes.
      // Both APIs must be enabled for your project before running this code.
      GoogleCredentials credentials =
          ServiceAccountCredentials.getApplicationDefault()
              .createScoped(
                  ImmutableSet.of(
                      "https://www.googleapis.com/auth/bigquery",
                      "https://www.googleapis.com/auth/drive"));

      // 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.newBuilder().setCredentials(credentials).build().getService();

      // Skip header row in the file.
      GoogleSheetsOptions sheetsOptions =
          GoogleSheetsOptions.newBuilder()
              .setSkipLeadingRows(1) // Optionally skip header row.
              .setRange("us-states!A20:B49") // Optionally set range of the sheet to query from.
              .build();

      // Configure the external data source and query job.
      ExternalTableDefinition externalTable =
          ExternalTableDefinition.newBuilder(sourceUri, sheetsOptions).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 | IOException e) {
      System.out.println("Query not performed \n" + e.toString());
    }
  }
}

פתרון בעיות

מחרוזת השגיאה: Resources exceeded during query execution: Google Sheets service overloaded.

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

מחרוזת השגיאה: Access Denied: BigQuery BigQuery: Permission denied while getting Drive credentials

כדי לפתור את השגיאה, מבצעים את השלבים הבאים:

  • מוודאים שיש לכם גישת צפייה לקובץ ב-Drive שמקושר לטבלה החיצונית.
  • אם הגרסה של כלי שורת הפקודה של BigQuery היא 2.1.12 או גרסה ישנה יותר, משתמשים בדגל --enable-gdrive-access.
  • מוודאים שאתם או חשבון השירות שמריץ את השאילתה קיבלתם את התפקידים הנדרשים כדי לשלוח שאילתות לטבלאות חיצוניות ב-Google Drive.

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