שליחת שאילתות לטבלאות חיצוניות ב-Bigtable

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

‫Bigtable הוא מסד נתונים NoSQL של Google, שמאוכלס באופן חסכוני (sparse) ויכול להתרחב למיליארדי שורות, אלפי עמודות ופטה-בייטים של נתונים. למידע על מודל הנתונים של Bigtable, קראו את המאמר מודל האחסון.

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

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

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

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

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

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

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

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

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

הרצת שאילתה על הטבלה

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

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

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

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

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

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

לשימוש בטבלה חיצונית זמנית במקום בטבלה חיצונית קבועה יש כמה מגבלות, כולל:

  • אתם צריכים את התפקיד אדמין ב-Bigtable‏ (roles/bigtable.admin).
  • בגישה הזו אי אפשר להשתמש במסוף Google Cloud כדי להסיק את הסכימה של הטבלה ב-Bigtable וליצור באופן אוטומטי את הגדרת הטבלה. אתם צריכים ליצור את הגדרת הטבלה בעצמכם.

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

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

  • צפייה בנתוני BigQuery‏ (roles/bigquery.dataViewer)
  • משתמש BigQuery‏ (roles/bigquery.user)
  • אדמין ב-Bigtable‏ (roles/bigtable.admin)

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

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

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

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

יצירת הטבלה והרצת שאילתות

כדי לשלוח שאילתה לנתוני Bigtable באמצעות טבלה חיצונית זמנית:

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

BQ

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

(אופציונלי) מציינים את הדגל --location ומגדירים את הערך למיקום שלכם.

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

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

  • ‫LOCATION: שם המיקום. הדגל --location הוא אופציונלי.
  • ‫TABLE: השם של הטבלה הזמנית שאתם יוצרים.
  • ‫DEFINITION_FILE: הנתיב לקובץ הגדרת הטבלה במחשב המקומי.
  • ‫QUERY: השאילתה ששולחים לטבלה הזמנית.

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

bq query \
--use_legacy_sql=false \
--external_table_definition=follows::/tmp/follows_def \
'SELECT
  COUNT(rowkey)
 FROM
   follows'

API

  • יצירת שאילתה. במאמר שליחת שאילתות על נתונים מוסבר איך ליצור משימת שאילתה.

  • (אופציונלי) מציינים את המיקום במאפיין location בקטע jobReference של משאב המשימה.

  • מציינים את המאפיינים של מקור הנתונים החיצוני על ידי הגדרת ExternalDataConfiguration למשאב הטבלה.

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.BigtableColumn;
import com.google.cloud.bigquery.BigtableColumnFamily;
import com.google.cloud.bigquery.BigtableOptions;
import com.google.cloud.bigquery.ExternalTableDefinition;
import com.google.cloud.bigquery.QueryJobConfiguration;
import com.google.cloud.bigquery.TableResult;
import com.google.common.collect.ImmutableList;
import org.apache.commons.codec.binary.Base64;

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

  public static void main(String[] args) {
    // TODO(developer): Replace these variables before running the sample.
    String projectId = "MY_PROJECT_ID";
    String bigtableInstanceId = "MY_INSTANCE_ID";
    String bigtableTableName = "MY_BIGTABLE_NAME";
    String bigqueryTableName = "MY_TABLE_NAME";
    String sourceUri =
        String.format(
            "https://googleapis.com/bigtable/projects/%s/instances/%s/tables/%s",
            projectId, bigtableInstanceId, bigtableTableName);
    String query = String.format("SELECT * FROM %s ", bigqueryTableName);
    queryExternalBigtableTemp(bigqueryTableName, sourceUri, query);
  }

  public static void queryExternalBigtableTemp(String tableName, String sourceUri, String query) {
    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();

      BigtableColumnFamily.Builder statsSummary = BigtableColumnFamily.newBuilder();

      // Configuring Columns
      BigtableColumn connectedCell =
          BigtableColumn.newBuilder()
              .setQualifierEncoded(Base64.encodeBase64String("connected_cell".getBytes()))
              .setFieldName("connected_cell")
              .setType("STRING")
              .setEncoding("TEXT")
              .build();
      BigtableColumn connectedWifi =
          BigtableColumn.newBuilder()
              .setQualifierEncoded(Base64.encodeBase64String("connected_wifi".getBytes()))
              .setFieldName("connected_wifi")
              .setType("STRING")
              .setEncoding("TEXT")
              .build();
      BigtableColumn osBuild =
          BigtableColumn.newBuilder()
              .setQualifierEncoded(Base64.encodeBase64String("os_build".getBytes()))
              .setFieldName("os_build")
              .setType("STRING")
              .setEncoding("TEXT")
              .build();

      // Configuring column family and columns
      statsSummary
          .setColumns(ImmutableList.of(connectedCell, connectedWifi, osBuild))
          .setFamilyID("stats_summary")
          .setOnlyReadLatest(true)
          .setEncoding("TEXT")
          .setType("STRING")
          .build();

      // Configuring BigtableOptions is optional.
      BigtableOptions options =
          BigtableOptions.newBuilder()
              .setIgnoreUnspecifiedColumnFamilies(true)
              .setReadRowkeyAsString(true)
              .setColumnFamilies(ImmutableList.of(statsSummary.build()))
              .build();

      // Configure the external data source and query job.
      ExternalTableDefinition externalTable =
          ExternalTableDefinition.newBuilder(sourceUri, options).build();
      QueryJobConfiguration queryConfig =
          QueryJobConfiguration.newBuilder(query)
              .addTableDefinition(tableName, externalTable)
              .build();

      // Example query
      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 e) {
      System.out.println("Query not performed \n" + e.toString());
    }
  }
}

שיקולי ביצועים

הביצועים של שאילתות במקורות נתונים חיצוניים של Bigtable תלויים בשלושה גורמים:

  • מספר השורות
  • כמות הנתונים שנקראו
  • היקף ההקבלה

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

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

Compute

כששולחים שאילתה לנתונים ב-Bigtable מ-BigQuery, אפשר לבחור בין האפשרויות הבאות לעיבוד:

  • צבירי צמתים, שהיא ברירת המחדל.
  • ‫Data Boost (גרסת טרום-השקה (Preview)), אפשרות למחשוב בלי שרת (serverless computing) שמאפשרת לבודד את תנועת הניתוחים בלי להשפיע על התנועה של הצגת האפליקציות שהצמתים של האשכולות מטפלים בה.

כדי להשתמש ב-Data Boost, אתם או האדמין שלכם צריכים ליצור קובץ הגדרה שמציין פרופיל אפליקציה של Data Boost ב-URI של Bigtable. למידע נוסף, קראו את המאמר יצירת טבלה חיצונית של Bigtable.

אם אתם לא משתמשים ב-Data Boost, חשוב לדעת ששליחת שאילתות ל-Bigtable מ-BigQuery צורכת מחזורי מעבד (CPU) של Bigtable. צריכת המעבד (CPU) על ידי BigQuery, כשמשתמשים בצמתים שהוקצו למשאבי מחשוב, עשויה להשפיע על זמן האחזור ועל התפוקה של בקשות מקבילות אחרות, כמו הצגת תעבורת משתמשים בזמן אמת. לדוגמה, שימוש גבוה במעבד (CPU) ב-Bigtable משפיע על שאילתות "זנב ארוך" ומגדיל את זמן האחזור באחוזון ה-99.

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

מסנני שאילתות

כדי לצמצם את השימוש במשאבים של BigQuery, אתם יכולים להוסיף מסנני שאילתות כשאתם שולחים שאילתות לטבלה חיצונית.

מסנן מפתחות שורה

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

SELECT
  COUNT(follows.column.name)
FROM
  `dataset.table`
WHERE
  rowkey = "alice";

יש גם תמיכה במסנני טווח כמו rowkey > '1' ו-rowkey < '8', אבל רק כש-rowkey נקרא כמחרוזת עם האפשרות readRowkeyAsString.

סינון לפי קבוצת עמודות ומזהה

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

    SELECT
      rowkey AS user_id,
      user_info
    FROM
      project.dataset.table;
 כדי לסנן לפי מגדיר ספציפי, קודם צריך להצהיר עליו ב-"columns" בהגדרה של הטבלה החיצונית: 
CREATE OR REPLACE EXTERNAL TABLE project.dataset.table
  OPTIONS (
    format = 'CLOUD_BIGTABLE',
    uris = ['https://googleapis.com/bigtable/projects/…/instances/…/tables/…'],
    bigtable_options = '''{
  "columnFamilies": [
    {
      "familyId": "user_info",
      "columns": [
        {
          "qualifierString": "name"
        },
        {
          "qualifierString": "email"
        },
        {
          "qualifierString": "registered_at"
        }
      ]
    },
    {
      "familyId": "session_data"
    }
  ],
  "readRowkeyAsString": true,
  "timestampSuffix": "_ts"
}'''
  );

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

    SELECT
      rowkey AS user_id,
      user_info.email.cell[SAFE_OFFSET(0)].value as email
    FROM
      project.dataset.table;