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

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

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

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

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

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

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

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

Go

לפני שמנסים את הדוגמה הזו, צריך לפעול לפי Goהוראות ההגדרה שבמדריך למתחילים של BigQuery באמצעות ספריות לקוח. מידע נוסף מופיע במאמרי העזרה של BigQuery Go API.

כדי לבצע אימות ב-BigQuery, צריך להגדיר את Application Default Credentials. מידע נוסף זמין במאמר הגדרת אימות לספריות לקוח.

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
}

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.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());
    }
  }
}

Python

לפני שמנסים את הדוגמה הזו, צריך לפעול לפי Pythonהוראות ההגדרה שבמדריך למתחילים של BigQuery באמצעות ספריות לקוח. מידע נוסף מופיע במאמרי העזרה של BigQuery Python API.

כדי לבצע אימות ב-BigQuery, צריך להגדיר את Application Default Credentials. מידע נוסף זמין במאמר הגדרת אימות לספריות לקוח.

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 זמינה במאמר מבוא לטבלאות מחולקות למחיצות.
• במאמר יצירת טבלאות עם חלוקה למחיצות מוסבר איך ליצור טבלאות כאלה.