שאילתות על טבלאות מסודרות באשכולות

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

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

‫BigQuery ממיין את הנתונים בטבלה מסודרת באשכולות על סמך הערכים בעמודות המסודרות באשכולות, ומארגן אותם בבלוקים.

כששולחים שאילתה שמכילה מסנן בעמודה מסודרת באשכולות, BigQuery משתמש במידע על הסידור באשכולות כדי לקבוע ביעילות אם בלוק מכיל נתונים שרלוונטיים לשאילתה. כך BigQuery יכול לסרוק רק את הבלוקים הרלוונטיים – תהליך שנקרא הסרת בלוקים.

אפשר לשלוח שאילתות לטבלאות מסודרות באשכולות לפי:

• שימוש במסוף Cloud de Confiance
• שימוש בפקודה bq query של כלי שורת הפקודה של BigQuery
• ביצוע קריאה לשיטה jobs.insert והגדרה של משימת שאילתה
• שימוש בספריות הלקוח

אפשר להשתמש ב-GoogleSQL רק עם טבלאות מקובצות.

import (
	"context"
	"fmt"
	"io"

	"cloud.google.com/go/bigquery"
	"google.golang.org/api/iterator"
)

// queryClusteredTable demonstrates querying a table that has a clustering specification.
func queryClusteredTable(w io.Writer, projectID, datasetID, tableID string) error {
	// projectID := "my-project-id"
	// datasetID := "mydataset"
	// tableID := "mytable"
	ctx := context.Background()
	client, err := bigquery.NewClient(ctx, projectID)
	if err != nil {
		return fmt.Errorf("bigquery.NewClient: %v", err)
	}
	defer client.Close()

	q := client.Query(fmt.Sprintf(`
	SELECT
	  COUNT(1) as transactions,
	  SUM(amount) as total_paid,
	  COUNT(DISTINCT destination) as distinct_recipients
    FROM
	  `+"`%s.%s`"+`
	 WHERE
	    timestamp > TIMESTAMP('2015-01-01')
		AND origin = @wallet`, datasetID, tableID))
	q.Parameters = []bigquery.QueryParameter{
		{
			Name:  "wallet",
			Value: "wallet00001866cb7e0f09a890",
		},
	}
	// Run the query and print results when the query job is completed.
	job, err := q.Run(ctx)
	if err != nil {
		return err
	}
	status, err := job.Wait(ctx)
	if err != nil {
		return err
	}
	if err := status.Err(); err != nil {
		return err
	}
	it, err := job.Read(ctx)
	for {
		var row []bigquery.Value
		err := it.Next(&row)
		if err == iterator.Done {
			break
		}
		if err != nil {
			return err
		}
		fmt.Fprintln(w, row)
	}
	return nil
}

import com.google.cloud.bigquery.BigQuery;
import com.google.cloud.bigquery.BigQueryException;
import com.google.cloud.bigquery.BigQueryOptions;
import com.google.cloud.bigquery.QueryJobConfiguration;
import com.google.cloud.bigquery.TableResult;

public class QueryClusteredTable {

  public static void runQueryClusteredTable() throws Exception {
    // TODO(developer): Replace these variables before running the sample.
    String projectId = "MY_PROJECT_ID";
    String datasetName = "MY_DATASET_NAME";
    String tableName = "MY_TABLE_NAME";
    queryClusteredTable(projectId, datasetName, tableName);
  }

  public static void queryClusteredTable(String projectId, String datasetName, String tableName) {
    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();

      String sourceTable = "`" + projectId + "." + datasetName + "." + tableName + "`";
      String query =
          "SELECT word, word_count\n"
              + "FROM "
              + sourceTable
              + "\n"
              // Optimize query performance by filtering the clustered columns in sort order
              + "WHERE corpus = 'romeoandjuliet'\n"
              + "AND word_count >= 1";

      QueryJobConfiguration queryConfig = QueryJobConfiguration.newBuilder(query).build();

      TableResult results = bigquery.query(queryConfig);

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

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

from google.cloud import bigquery

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

# TODO(developer): Set table_id to the ID of the destination table.
# table_id = "your-project.your_dataset.your_table_name"

sql = "SELECT * FROM `bigquery-public-data.samples.shakespeare`"
cluster_fields = ["corpus"]

job_config = bigquery.QueryJobConfig(
    clustering_fields=cluster_fields, destination=table_id
)

# Start the query, passing in the extra configuration.
client.query_and_wait(
    sql, job_config=job_config
)  # Make an API request and wait for job to complete.

table = client.get_table(table_id)  # Make an API request.
if table.clustering_fields == cluster_fields:
    print(
        "The destination table is written using the cluster_fields configuration."
    )

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

כדי להריץ עבודת שאילתה, צריך הרשאת bigquery.jobs.create בממשק לניהול הזהויות והרשאות הגישה (IAM) בפרויקט שבו מורצת עבודת השאילתה.

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

• roles/bigquery.admin
• roles/bigquery.jobUser
• roles/bigquery.user

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

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

• roles/bigquery.admin
• roles/bigquery.dataOwner
• roles/bigquery.dataEditor
• roles/bigquery.dataViewer

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

שיטות מומלצות

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

כדי לספק הקשר, הטבלה לדוגמה שבה נעשה שימוש בדוגמאות של השיטות המומלצות היא טבלה מסודרת באשכולות שנוצרת באמצעות הצהרת DDL. הצהרת ה-DDL יוצרת טבלה בשם ClusteredSalesData. הטבלה מסודרת באשכולות לפי העמודות הבאות: customer_id, product_id, order_id, בסדר המיון הזה.

CREATE TABLE
  `mydataset.ClusteredSalesData`
PARTITION BY
  DATE(timestamp)
CLUSTER BY
  customer_id,
  product_id,
  order_id AS
SELECT
  *
FROM
  `mydataset.SalesData`

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

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

בדוגמה הבאה מבוצעת שאילתה בטבלת ClusteredSalesData עם אשכולות שנוצרה בדוגמה הקודמת. השאילתה כוללת ביטוי מסנן שמסנן לפי customer_id ואז לפי product_id. השאילתה הזו מבצעת אופטימיזציה של הביצועים על ידי סינון העמודות המקובצות בסדר המיון – סדר העמודות שצוין בסעיף CLUSTER BY.

SELECT
  SUM(totalSale)
FROM
  `mydataset.ClusteredSalesData`
WHERE
  customer_id = 10000
  AND product_id LIKE 'gcp_analytics%'

השאילתה הבאה לא מסננת את העמודות המקובצות בסדר המיון. לכן, ביצועי השאילתה לא אופטימליים. השאילתה הזו מסננת לפי product_id ואז לפי order_id (מדלגת על customer_id).

SELECT
  SUM(totalSale)
FROM
  `mydataset.ClusteredSalesData`
WHERE
  product_id LIKE 'gcp_analytics%'
  AND order_id = 20000

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

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

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

SELECT
  SUM(totalSale)
FROM
  `mydataset.ClusteredSalesData`
WHERE
  CAST(customer_id AS STRING) = "10000"

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

SELECT
  SUM(totalSale)
FROM
  `mydataset.ClusteredSalesData`
WHERE
  customer_id = 10000

לא להשוות עמודות מסודרות באשכולות לעמודות אחרות

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

השאילתה הבאה לא מצמצמת בלוקים כי ביטוי המסנן משווה עמודה מסודרת באשכולות – customer_id לעמודה אחרת – order_id.

SELECT
  SUM(totalSale)
FROM
  `mydataset.ClusteredSalesData`
WHERE
  customer_id = order_id

אבטחת טבלאות

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

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

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