‫Google משתמשת בטכנולוגיית AI כדי לתרגם תוכן לשפה המועדפת עליך. בתרגומים כאלו עשויות להיות שגיאות.

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

טבלאות חיצוניות ב-Google Drive מאפשרות לשלוח שאילתות לגבי נתונים בקובצי Drive (כמו קובצי CSV, קובצי JSON שמופרדים בשורה חדשה, קובצי Avro וקובצי Google Sheets) ישירות מ-BigQuery, בלי לטעון את הנתונים לאחסון ב-BigQuery. אפשר ליצור טבלאות חיצוניות גם בקובצי Drive אישיים וגם בקובצי Drive משותפים.

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

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

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

אחזור מזהי 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 , פועלים לפי השלבים לאימות מבוסס-אינטרנט. כשתתבקשו, לחצו על Allow כדי לתת ל-BigQuery Client Tools גישה ל-Drive.

gcloud

מפעילים את Cloud Shell במסוף Google Cloud .

הפעלת Cloud Shell

בחלק התחתון של מסוף Google Cloud יתחיל סשן של Cloud Shell ותופיע הודעה של שורת הפקודה. Cloud Shell היא סביבת מעטפת שבה ה-CLI של Google Cloud מותקן ומוגדרים ערכים לפרויקט הקיים. הסשן יופעל תוך כמה שניות.
מזינים את הפקודה הבאה כדי לוודא שמשתמשים בגרסה העדכנית של 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.

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

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

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

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

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

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

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

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

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

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

המסוף

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

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

אם החלונית הימנית לא מוצגת, לוחצים על הרחבת החלונית הימנית כדי לפתוח אותה.
בחלונית Explorer, מרחיבים את הפרויקט, לוחצים על Datasets ובוחרים קבוצת נתונים.
בחלונית הפרטים, לוחצים על Create table .
בדף Create table, בקטע Source:
- בקטע Create table from, בוחרים באפשרות Drive.
- בשדה בחירת URI ב-Drive, מזינים את ה-URI ב-Drive. שימו לב שאין תמיכה בתווים כלליים לחיפוש במזהי URI של Drive.
- בקטע File format, בוחרים את פורמט הקובץ של הנתונים. פורמטים תקינים של נתונים ב-Drive כוללים:
  - ערכים מופרדים בפסיקים (‎CSV)
  - ‫JSON מופרד בשורות חדשות
  - Avro
  - Sheets
  הערה: כשבוחרים במסוף Google Cloud בגיבוי של Avro או Datastore, האפשרות Automatically detect מוסתרת כי סוגי הקבצים האלה מתארים את עצמם.
(אופציונלי) אם בוחרים ב-Sheets, בתיבה Sheet range (Optional) מציינים את הגיליון ואת טווח התאים שרוצים לשלוח אליהם שאילתה. אפשר לציין שם של גיליון, או לציין sheet_name!top_left_cell_id:bottom_right_cell_id לטווח תאים, למשל: "Sheet1!A1:B20". אם לא מציינים טווח גיליון, המערכת משתמשת בגיליון הראשון בקובץ.
בדף Create table, בקטע Destination:
- בשדה Dataset name בוחרים את מערך הנתונים המתאים, ובשדה Table name מזינים את שם הטבלה שיוצרים ב-BigQuery.
- מוודאים שהשדה Table type מוגדר ל-External table.
בקטע Schema, מזינים את הגדרת הסכימה.
- בקובצי JSON או CSV, אפשר לסמן את האפשרות Auto-detect כדי להפעיל זיהוי אוטומטי של הסכימה. האפשרות Auto-detect לא זמינה לייצוא של Datastore, לייצוא של Firestore ולקובצי Avro. פרטי הסכימה של סוגי הקבצים האלה מאוחזרים באופן אוטומטי מנתוני המקור שמתארים את עצמם.
- מזינים את פרטי הסכימה באופן ידני:
  - להפעיל את האפשרות Edit as text ולהזין את סכימת הטבלה כמערך JSON. הערה: כדי לראות את הסכימה של טבלה קיימת בפורמט JSON, מזינים את הפקודה הבאה בכלי שורת הפקודה של BigQuery: ‏bq show --format=prettyjson DATASET.TABLE.
  - להשתמש באפשרות Add field כדי להזין את הסכימה באופן ידני.
לוחצים על Create table.
אם צריך, בוחרים את החשבון ולוחצים על Allow כדי לתת לכלי הלקוח של 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 שמקושרת לקובץ 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`