יצירת טבלאות חיצוניות ב-Google Drive

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

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

אתם יכולים ליצור טבלאות חיצוניות על קבצים ב-Drive בפורמטים הבאים:

ערכים מופרדים בפסיקים (‎CSV)
פורמט JSON שמופרד בתו שורה חדשה
Avro
Google Sheets

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

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

אחזור מזהי URI של Drive

כדי ליצור טבלה חיצונית למקור נתונים ב-Google Drive, צריך לספק את ה-URI של Drive. אפשר לאחזר את ה-URI של Drive ישירות מכתובת ה-URL של נתוני Drive:

פורמט URI

https://docs.google.com/spreadsheets/d/FILE_ID

או
https://drive.google.com/open?id=FILE_ID

‫FILE_ID הוא המזהה האלפאנומרי של הקובץ ב-Drive.

אימות והפעלת הגישה ל-Drive

כדי לגשת לנתונים שמתארחים ב-Drive, צריך להשתמש בהיקף OAuth נוסף. כדי לבצע אימות ל-BigQuery ולהפעיל גישה ל-Drive:

המסוף

כשיוצרים טבלה חיצונית במסוף Google Cloud , צריך לפעול לפי השלבים לאימות מבוסס-אינטרנט. כשמוצגת בקשה, לוחצים על אישור כדי לתת ל-BigQuery Client Tools גישה ל-Drive.

gcloud

In the Google Cloud console, activate Cloud Shell.

Activate Cloud Shell

At the bottom of the Google Cloud console, a Cloud Shell session starts and displays a command-line prompt. Cloud Shell is a shell environment with the Google Cloud CLI already installed and with values already set for your current project. It can take a few seconds for the session to initialize.
מזינים את הפקודה הבאה כדי לוודא שמותקנת אצלכם הגרסה האחרונה של Google Cloud CLI.
```
gcloud components update
```
מזינים את הפקודה הבאה כדי לבצע אימות ב-Drive.
```
gcloud auth login --enable-gdrive-access
```

API

צריך לבקש את היקף ההרשאות המתאים של OAuth ל-Drive בנוסף להיקף ההרשאות של BigQuery:

מריצים את הפקודה gcloud auth login --enable-gdrive-access כדי להיכנס.
מריצים את הפקודה gcloud auth print-access-token כדי לקבל את אסימון הגישה מסוג OAuth עם היקף הגישה ל-Drive שמשמש את ה-API.

Python

יצירת מזהה לקוח OAuth
מגדירים את Application Default Credentials (ADC) בסביבה המקומית עם ההיקפים הנדרשים באופן הבא:
1. מתקינים את ה-CLI של Google Cloud ומאתחלים אותו באמצעות הפקודה הבאה:
```
gcloud init
```
2. יצירת פרטי כניסה לאימות מקומי בחשבון Google:
```
gcloud auth application-default login \
    --client-id-file=CLIENT_ID_FILE \
    --scopes=https://www.googleapis.com/auth/drive,https://www.googleapis.com/auth/cloud-platform
```
  מחליפים את CLIENT_ID_FILE בקובץ שמכיל את מזהה הלקוח ב-OAuth.
  
  למידע נוסף, ראו פרטי הכניסה של משתמשים, שסופקו באמצעות ה-CLI של gcloud.

Java

יצירת מזהה לקוח OAuth
מגדירים את Application Default Credentials (ADC) בסביבה המקומית עם ההיקפים הנדרשים באופן הבא:
1. מתקינים את ה-CLI של Google Cloud ומאתחלים אותו באמצעות הפקודה הבאה:
```
gcloud init
```
2. יצירת פרטי כניסה לאימות מקומי בחשבון Google:
```
gcloud auth application-default login \
    --client-id-file=CLIENT_ID_FILE \
    --scopes=https://www.googleapis.com/auth/drive,https://www.googleapis.com/auth/cloud-platform
```
  מחליפים את CLIENT_ID_FILE בקובץ שמכיל את מזהה הלקוח ב-OAuth.
  
  למידע נוסף, ראו פרטי הכניסה של משתמשים, שסופקו באמצעות ה-CLI של gcloud.

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

כדי ליצור טבלה חיצונית, צריך הרשאה לניהול זהויות והרשאות גישה (IAM) ב-BigQuery‏ bigquery.tables.create.

כל אחד מהתפקידים המוגדרים מראש הבאים של ניהול זהויות והרשאות גישה (IAM) כולל את ההרשאה הזו:

עריכה של נתוני BigQuery‏ (roles/bigquery.dataEditor)
בעלים של נתונים ב-BigQuery‏ (roles/bigquery.dataOwner)
אדמין של BigQuery‏ (roles/bigquery.admin)

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

במאמר תפקידים והרשאות מוגדרים מראש יש מידע נוסף על תפקידים והרשאות ב-IAM ב-BigQuery.

יצירת טבלאות חיצוניות

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

שימוש במסוף Google Cloud
שימוש בפקודה mk של כלי שורת הפקודה של bq
יצירת ExternalDataConfiguration כשמשתמשים ב-method ‏tables.insert של API
שימוש בספריות הלקוח

כדי ליצור טבלה חיצונית:

המסוף

נכנסים לדף BigQuery במסוף Google Cloud .

כניסה ל-BigQuery
בחלונית הימנית, לוחצים על כלי הניתוחים:

אם החלונית הימנית לא מוצגת, לוחצים על הרחבת החלונית הימנית כדי לפתוח אותה.
בחלונית Explorer, מרחיבים את הפרויקט, לוחצים על Datasets ואז בוחרים מערך נתונים.
בחלונית הפרטים, לוחצים על יצירת טבלה .
בדף Create table, בקטע Source:
- בקטע Create table from (יצירת טבלה מתוך), בוחרים באפשרות Drive (דרייב).
- בשדה Select Drive URI, מזינים את Drive URI. שימו לב: אין תמיכה בתווים כלליים לחיפוש בכתובות URI של Drive.
- בקטע פורמט קובץ, בוחרים את פורמט הנתונים. פורמטים תקינים של נתוני Drive כוללים:
  - ערכים מופרדים בפסיקים (‎CSV)
  - פורמט JSON שמופרד בתו שורה חדשה
  - Avro
  - Sheets
  הערה: במסוף Google Cloud , אם בוחרים בגיבוי של Avro או Datastore, האפשרות זיהוי אוטומטי מוסתרת כי סוגי הקבצים האלה הם בעלי תיאור עצמי.
(אופציונלי) אם בוחרים באפשרות Sheets, בתיבה Sheet range (Optional) (טווח גיליון (אופציונלי)), מציינים את הגיליון ואת טווח התאים שרוצים לשלוח להם שאילתה. אפשר לציין שם של גיליון, או לציין sheet_name!top_left_cell_id:bottom_right_cell_id לטווח תאים, למשל Sheet1!A1:B20. אם לא מציינים טווח גיליון, המערכת משתמשת בגיליון הראשון בקובץ.
בדף יצירת טבלה, בקטע יעד:
- בשדה שם מערך הנתונים, בוחרים את מערך הנתונים המתאים, ובשדה שם הטבלה מזינים את שם הטבלה שיוצרים ב-BigQuery.
- מוודאים שהאפשרות Table type מוגדרת ל-External table.
בקטע Schema (סכימה), מזינים את הגדרת הסכימה.
- אם אתם מעלים קובץ JSON או CSV, אתם יכולים לסמן את האפשרות זיהוי אוטומטי כדי להפעיל זיהוי אוטומטי של הסכימה. האפשרות זיהוי אוטומטי לא זמינה לייצוא של Datastore, לייצוא של Firestore ולקובצי Avro. פרטי הסכמה של סוגי הקבצים האלה מאוחזרים באופן אוטומטי מנתוני המקור שמתארים את עצמם.
- מזינים את פרטי הסכימה באופן ידני באמצעות:
  - הפעלת האפשרות Edit as text והזנת סכימת הטבלה כמערך JSON. הערה: כדי לראות את הסכימה של טבלה קיימת בפורמט JSON, מזינים את הפקודה הבאה בכלי שורת הפקודה של BigQuery: bq show --format=prettyjson DATASET.TABLE.
  - משתמשים באפשרות הוספת שדה כדי להזין את הסכימה באופן ידני.
לוחצים על יצירת טבלה.
במקרה הצורך, בוחרים את החשבון ולוחצים על אישור כדי לתת לכלים של לקוח BigQuery גישה ל-Drive.

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

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

BQ

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

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

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

bq mk \
--external_table_definition=DEFINITION_FILE \
DATASET.TABLE

כאשר:

‫DEFINITION_FILE הוא הנתיב אל קובץ הגדרת הטבלה במחשב המקומי.
‫DATASET הוא שם מערך הנתונים שמכיל את הטבלה.
‫TABLE הוא שם הטבלה שאתם יוצרים.

לדוגמה, הפקודה הבאה יוצרת טבלה קבועה בשם mytable באמצעות קובץ הגדרת טבלה בשם mytable_def.

bq mk --external_table_definition=/tmp/mytable_def mydataset.mytable

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

bq mk \
--external_table_definition=SCHEMA@SOURCE_FORMAT=DRIVE_URI \
DATASET.TABLE

כאשר:

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

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

bq mk \
--external_table_definition=Region:STRING,Quarter:STRING,Total_sales:INTEGER@GOOGLE_SHEETS=https://drive.google.com/open?id=1234_AbCD12abCd \
mydataset.sales

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

bq mk \
--external_table_definition=SCHEMA_FILE@SOURCE_FORMAT=DRIVE_URI \
DATASET.TABLE

כאשר:

‫SCHEMA_FILE הוא הנתיב לקובץ סכימת JSON במחשב המקומי.
‫SOURCE_FORMAT הוא CSV,‏ NEWLINE_DELIMITED_JSON,‏ AVRO או GOOGLE_SHEETS.
‫DRIVE_URI הוא ה-URI של Drive.
‫DATASET הוא שם מערך הנתונים שמכיל את הטבלה.
‫TABLE הוא שם הטבלה שאתם יוצרים.

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

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

bq mk \
--external_table_definition=/tmp/sales_schema.json@CSV=https://drive.google.com/open?id=1234_AbCD12abCd \
mydataset.sales

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

API

יוצרים ExternalDataConfiguration כשמשתמשים ב-method ‏tables.insert של API. כדי להפעיל זיהוי אוטומטי של סכימה במקורות נתונים נתמכים, מציינים את המאפיין schema או מגדירים את המאפיין autodetect לערך true.

Python

from google.cloud import bigquery
import google.auth

credentials, project = google.auth.default()

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

# TODO(developer): Set dataset_id to the ID of the dataset to fetch.
# dataset_id = "your-project.your_dataset"

# Configure the external data source.
dataset = client.get_dataset(dataset_id)
table_id = "us_states"
schema = [
    bigquery.SchemaField("name", "STRING"),
    bigquery.SchemaField("post_abbr", "STRING"),
]
table = bigquery.Table(dataset.table(table_id), schema=schema)
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]
options = external_config.google_sheets_options
assert options is not None
options.skip_leading_rows = 1  # Optionally skip header row.
options.range = (
    "us-states!A20:B49"  # Optionally set range of the sheet to query from.
)
table.external_data_configuration = external_config

# Create a permanent table linked to the Sheets file.
table = client.create_table(table)  # Make an API request.

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

results = client.query_and_wait(sql)  # Make an API request.

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

Java

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.TableId;
import com.google.cloud.bigquery.TableInfo;
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 permanent table
public class QueryExternalSheetsPerm {

  public static void main(String[] args) {
    // TODO(developer): Replace these variables before running the sample.
    String datasetName = "MY_DATASET_NAME";
    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.%s WHERE name LIKE 'W%%'", datasetName, tableName);
    queryExternalSheetsPerm(datasetName, tableName, sourceUri, schema, query);
  }

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

      GoogleCredentials credentials =
          ServiceAccountCredentials.getApplicationDefault();

      // 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();

      TableId tableId = TableId.of(datasetName, tableName);
      // Create a permanent table linked to the Sheets file.
      ExternalTableDefinition externalTable =
          ExternalTableDefinition.newBuilder(sourceUri, sheetsOptions).setSchema(schema).build();
      bigquery.create(TableInfo.of(tableId, externalTable));

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

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

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

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

מידע נוסף זמין במאמר בנושא שאילתות על נתונים ב-Drive.

עמודת הפסאודו `_FILE_NAME`

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

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

יצירת טבלאות חיצוניות ב-Google Drive

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

אחזור מזהי URI של Drive

אימות והפעלת הגישה ל-Drive

המסוף

gcloud

API

Python

Java

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

יצירת טבלאות חיצוניות

המסוף

BQ

API

Python

Java

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

עמודת הפסאודו _FILE_NAME

עמודת הפסאודו `_FILE_NAME`