שאילתת נתונים ב-Amazon S3

במאמר הזה מוסבר איך לשלוח שאילתות לנתונים שמאוחסנים בטבלת BigLake של Amazon Simple Storage Service ‏ (Amazon S3).

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

מוודאים שיש לכם טבלת Amazon S3 BigLake.

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

כדי להריץ שאילתות על טבלאות BigLake ב-Amazon S3, צריך לוודא שלמתקשר עם BigQuery API יש את התפקידים הבאים:

  • משתמש עם הרשאת גישה ל-BigQuery Connection ‏ (roles/bigquery.connectionUser)
  • צפייה בנתוני BigQuery ‏ (roles/bigquery.dataViewer)
  • משתמש BigQuery‏ (roles/bigquery.user)

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

כדי לראות בדיוק אילו הרשאות נדרשות לשאילתות בטבלאות BigLake ב-Amazon S3, אפשר להרחיב את הקטע ההרשאות הנדרשות:

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

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

הרצת שאילתות על טבלאות BigLake ב-Amazon S3

אחרי שיוצרים טבלת BigLake ב-Amazon S3, אפשר להריץ עליה שאילתות באמצעות תחביר GoogleSQL, בדיוק כמו בטבלה ב-BigQuery רגילה.

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

כשיוצרים הזמנה באזור BigQuery Omni, צריך להשתמש במהדורת Enterprise. במאמר יצירת הזמנות מוסבר איך ליצור הזמנה עם מהדורה.

מריצים שאילתה על טבלת BigLake Amazon S3:

SQL

כדי להריץ שאילתה על הטבלה:

  1. במסוף Google Cloud , עוברים לדף BigQuery.

    כניסה ל-BigQuery

  2. מזינים את ההצהרה הבאה בעורך השאילתות:

    SELECT * FROM DATASET_NAME.TABLE_NAME;

    מחליפים את מה שכתוב בשדות הבאים:

    • ‫DATASET_NAME: השם של מערך הנתונים שיצרתם
    • ‫TABLE_NAME: השם של הטבלה שיצרתם

    • לוחצים על הפעלה.

מידע נוסף על הרצת שאילתות זמין במאמר הרצת שאילתה אינטראקטיבית.

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.DatasetId;
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.TableId;
import com.google.cloud.bigquery.TableInfo;
import com.google.cloud.bigquery.TableResult;

// Sample to queries an external data source aws s3 using a permanent table
public class QueryExternalTableAws {

  public static void main(String[] args) throws InterruptedException {
    // TODO(developer): Replace these variables before running the sample.
    String projectId = "MY_PROJECT_ID";
    String datasetName = "MY_DATASET_NAME";
    String externalTableName = "MY_EXTERNAL_TABLE_NAME";
    // Query to find states starting with 'W'
    String query =
        String.format(
            "SELECT * FROM s%.%s.%s WHERE name LIKE 'W%%'",
            projectId, datasetName, externalTableName);
    queryExternalTableAws(query);
  }

  public static void queryExternalTableAws(String query) throws InterruptedException {
    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();

      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 aws external permanent table performed successfully.");
    } catch (BigQueryException e) {
      System.out.println("Query not performed \n" + e.toString());
    }
  }
}

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

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

בוחרים באחת מהאפשרויות הבאות:

המסוף

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

API

כדי לשלוח שאילתה לטבלת BigLake באמצעות ה-API, צריך לבצע את השלבים הבאים:

  1. יוצרים אובייקט Job.
  2. מבצעים קריאה לשיטה jobs.insert כדי להריץ את השאילתה באופן אסינכרוני, או לשיטה jobs.query כדי להריץ את השאילתה באופן סינכרוני, ומעבירים את האובייקט Job.
  3. קוראים שורות באמצעות jobs.getQueryResults על ידי העברת הפניה למשימה שצוינה, ושיטות tabledata.list על ידי העברת הפניה לטבלה שצוינה של תוצאת השאילתה.

שאילתה בעמודה הווירטואלית _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 מנסה לדלג על קריאת קבצים שלא עומדים בדרישות המסנן. ההמלצות שדומות ל המלצות לגבי שאילתות של טבלאות עם חלוקה למחיצות בזמן ההטמעה באמצעות עמודות פסאודו רלוונטיות גם כשיוצרים פסוקיות WHERE של שאילתות עם עמודת הפסאודו _FILE_NAME.

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