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

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

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

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

כדי להריץ משימת שאילתה, צריך את ההרשאה 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 זמין במאמר תפקידים והרשאות מוגדרים מראש.

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

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

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

המשך

לפני שמנסים את הדוגמה הזו, צריך לפעול לפי הוראות ההגדרה של 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."
    )

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

כדי להשיג את הביצועים הטובים ביותר משאילתות בטבלאות מסודרות באשכולות, כדאי להשתמש בשיטות המומלצות הבאות.

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