שאילתות לגבי נתונים ב-Drive

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

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

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

מגבלות

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

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

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

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

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

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

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

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

הרשאות ב-Drive

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

היקפים למכונות Compute Engine

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

אם מגדירים מכונה של 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.

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

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