יצירת תצוגות מהותיות

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

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

מקצים תפקידים של ניהול זהויות והרשאות גישה (IAM) שמעניקים למשתמשים את ההרשאות הדרושות לביצוע כל משימה שמופיעה במאמר הזה.

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

כדי ליצור תצוגות מהותיות, אתם צריכים את הרשאת bigquery.tables.create ב-IAM.

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

  • bigquery.dataEditor
  • bigquery.dataOwner
  • bigquery.admin

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

יצירת תצוגות מהותיות

כדי ליצור תצוגה חומרית, בוחרים באחת מהאפשרויות הבאות:

SQL

משתמשים בהצהרה CREATE MATERIALIZED VIEW. בדוגמה הבאה נוצרת תצוגה חומרית של מספר הקליקים לכל מזהה מוצר:

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

    כניסה ל-BigQuery

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

    CREATE MATERIALIZED VIEW PROJECT_ID.DATASET.MATERIALIZED_VIEW_NAME AS (
  QUERY_EXPRESSION
);

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

    • ‫PROJECT_ID: שם הפרויקט שבו רוצים ליצור את התצוגה החומרית – לדוגמה, myproject.
    • ‫DATASET: השם של מערך הנתונים ב-BigQuery שבו רוצים ליצור את התצוגה החומרית – למשל, mydataset. אם יוצרים תצוגה חומרית על טבלת BigLake ב-Amazon Simple Storage Service ‏ (Amazon S3) ‏(בגרסת Preview), צריך לוודא שמערך הנתונים נמצא באזור נתמך.
    • ‫MATERIALIZED_VIEW_NAME: השם של התצוגה החומרית שרוצים ליצור. לדוגמה, my_mv.
    • ‫QUERY_EXPRESSION: ביטוי השאילתה ב-GoogleSQL שמגדיר את התצוגה החומרית – לדוגמה, SELECT product_id, SUM(clicks) AS sum_clicks FROM mydataset.my_source_table.

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

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

דוגמה

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

CREATE MATERIALIZED VIEW myproject.mydataset.my_mv_table AS (
  SELECT
    product_id,
    SUM(clicks) AS sum_clicks
  FROM
    myproject.mydataset.my_base_table
  GROUP BY
    product_id
);

Terraform

משתמשים במשאב google_bigquery_table.

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

בדוגמה הבאה נוצרת תצוגה מפורטת בשם my_materialized_view:

resource "google_bigquery_dataset" "default" {
  dataset_id                      = "mydataset"
  default_partition_expiration_ms = 2592000000  # 30 days
  default_table_expiration_ms     = 31536000000 # 365 days
  description                     = "dataset description"
  location                        = "US"
  max_time_travel_hours           = 96 # 4 days

  labels = {
    billing_group = "accounting",
    pii           = "sensitive"
  }
}

resource "google_bigquery_table" "default" {
  dataset_id = google_bigquery_dataset.default.dataset_id
  table_id   = "my_materialized_view"

  materialized_view {
    query                            = "SELECT ID, description, date_created FROM `myproject.orders.items`"
    enable_refresh                   = "true"
    refresh_interval_ms              = 172800000 # 2 days
    allow_non_incremental_definition = "false"
  }

}

כדי להחיל את ההגדרות של Terraform בפרויקט ב- Google Cloud , מבצעים את השלבים בקטעים הבאים.

הכנת Cloud Shell

  1. מפעילים את Cloud Shell.

  2. מגדירים את פרויקט ברירת המחדל שבו רוצים להחיל את ההגדרות של Terraform. Google Cloud

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

    export GOOGLE_CLOUD_PROJECT=PROJECT_ID

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

הכנת הספרייה

לכל קובץ תצורה של Terraform צריכה להיות ספרייה משלו (שנקראת גם מודול ברמה הבסיסית).

  1. יוצרים ספרייה חדשה ב-Cloud Shell ובה יוצרים קובץ חדש. שם הקובץ חייב לכלול את הסיומת .tf, למשל main.tf. במדריך הזה, הקובץ נקרא main.tf. 
    mkdir DIRECTORY && cd DIRECTORY && touch main.tf

  2. אם אתם עוקבים אחרי המדריך, תוכלו להעתיק את הקוד לדוגמה בכל קטע או שלב.

    מעתיקים את הקוד לדוגמה בקובץ main.tf החדש שיצרתם.

    לחלופין, אפשר גם להעתיק את הקוד מ-GitHub. כדאי לעשות את זה כשקטע הקוד של Terraform הוא חלק מפתרון מקצה לקצה.

  3. בודקים את הפרמטרים לדוגמה ומשנים אותם בהתאם לסביבה שלכם.
  4. שומרים את השינויים.
  5. מפעילים את Terraform. צריך לעשות זאת רק פעם אחת לכל ספרייה. 
    terraform init

    אופציונלי: תוכלו לכלול את האפשרות -upgrade, כדי להשתמש בגרסה העדכנית ביותר של הספק של Google: 

    terraform init -upgrade

החלה של השינויים

  1. בודקים את ההגדרות ומוודאים שהמשאבים שמערכת Terraform תיצור או תעדכן תואמים לציפיות שלכם: 
    terraform plan

    מתקנים את ההגדרות לפי הצורך.

  2. מריצים את הפקודה הבאה ומזינים yes בהודעה שמופיעה, כדי להחיל את הגדרות Terraform: 
    terraform apply

    ממתינים עד שב-Terraform תוצג ההודעה "Apply complete!‎".

  3. פותחים את Google Cloud הפרויקט כדי לראות את התוצאות. במסוף Google Cloud , נכנסים למשאבים בממשק המשתמש כדי לוודא שהם נוצרו או עודכנו ב-Terraform.

API

מבצעים קריאה ל-tables.insert method ומעבירים Tableresource עם שדה materializedView מוגדר:

{
  "kind": "bigquery#table",
  "tableReference": {
    "projectId": "PROJECT_ID",
    "datasetId": "DATASET",
    "tableId": "MATERIALIZED_VIEW_NAME"
  },
  "materializedView": {
    "query": "QUERY_EXPRESSION"
  }
}

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

  • ‫PROJECT_ID: שם הפרויקט שבו רוצים ליצור את התצוגה החומרית – לדוגמה, myproject.
  • ‫DATASET: השם של מערך הנתונים ב-BigQuery שבו רוצים ליצור את התצוגה החומרית – למשל, mydataset. אם יוצרים תצוגה חומרית על טבלת BigLake ב-Amazon Simple Storage Service ‏ (Amazon S3) ‏(בגרסת Preview), צריך לוודא שמערך הנתונים נמצא באזור נתמך.
  • ‫MATERIALIZED_VIEW_NAME: השם של התצוגה החומרית שרוצים ליצור. לדוגמה, my_mv.
  • ‫QUERY_EXPRESSION: ביטוי השאילתה ב-GoogleSQL שמגדיר את התצוגה החומרית – לדוגמה, SELECT product_id, SUM(clicks) AS sum_clicks FROM mydataset.my_source_table.

דוגמה

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

{
  "kind": "bigquery#table",
  "tableReference": {
    "projectId": "myproject",
    "datasetId": "mydataset",
    "tableId": "my_mv"
  },
  "materializedView": {
    "query": "select product_id,sum(clicks) as
                sum_clicks from myproject.mydataset.my_source_table
                group by 1"
  }
}

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

// Sample to create materialized view
public class CreateMaterializedView {

  public static void main(String[] args) {
    // TODO(developer): Replace these variables before running the sample.
    String datasetName = "MY_DATASET_NAME";
    String tableName = "MY_TABLE_NAME";
    String materializedViewName = "MY_MATERIALIZED_VIEW_NAME";
    String query =
        String.format(
            "SELECT MAX(TimestampField) AS TimestampField, StringField, "
                + "MAX(BooleanField) AS BooleanField "
                + "FROM %s.%s GROUP BY StringField",
            datasetName, tableName);
    createMaterializedView(datasetName, materializedViewName, query);
  }

  public static void createMaterializedView(
      String datasetName, String materializedViewName, 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();

      TableId tableId = TableId.of(datasetName, materializedViewName);

      MaterializedViewDefinition materializedViewDefinition =
          MaterializedViewDefinition.newBuilder(query).build();

      bigquery.create(TableInfo.of(tableId, materializedViewDefinition));
      System.out.println("Materialized view created successfully");
    } catch (BigQueryException e) {
      System.out.println("Materialized view was not created. \n" + e.toString());
    }
  }
}

אחרי שיוצרים את התצוגה החומרית בהצלחה, היא מופיעה בחלונית Explorer של BigQuery במסוף Google Cloud . בדוגמה הבאה מוצגת סכימה של תצוגה חומרית:

סכימה של תצוגה מהותית במסוף Google Cloud

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

בקרת גישה

אפשר לתת גישה לתצוגה חומרית ברמת מערך הנתונים, ברמת התצוגה או ברמת העמודה. אפשר גם להגדיר גישה ברמה גבוהה יותר בהיררכיית המשאבים של IAM.

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

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

תמיכה בשאילתות של תצוגות מהותיות

תצוגות חומריות משתמשות בתחביר SQL מוגבל. השאילתות צריכות להיות בפורמט הבא:

[ WITH cte [, …]]
SELECT  [{ ALL | DISTINCT }]
  expression [ [ AS ] alias ] [, ...]
FROM from_item [, ...]
[ WHERE bool_expression ]
[ GROUP BY expression [, ...] ]

from_item:
    {
      table_name [ as_alias ]
      | { join_operation | ( join_operation ) }
      | field_path
      | unnest_operator
      | cte_name [ as_alias ]
    }

as_alias:
    [ AS ] alias

מגבלות על שאילתות

יש מגבלות על תצוגות חומריות.

דרישות מצטברות

הפונקציות המצטברות בשאילתת התצוגה המהותית צריכות להיות פלטים. אין תמיכה בחישוב, סינון או צירוף על סמך ערך מצטבר. לדוגמה, יצירת תצוגה מהשאילתה הבאה אינה נתמכת כי היא מפיקה ערך שמחושב מתוך צבירה, COUNT(*) / 10 as cnt.

SELECT TIMESTAMP_TRUNC(ts, HOUR) AS ts_hour, COUNT(*) / 10 AS cnt
FROM mydataset.mytable
GROUP BY ts_hour;

יש תמיכה רק בפונקציות הצבירה הבאות:

  • ‫ANY_VALUE (אבל לא מעל STRUCT)
  • APPROX_COUNT_DISTINCT
  • ‫ARRAY_AGG (אבל לא מעל ARRAY או STRUCT)
  • AVG
  • BIT_AND
  • BIT_OR
  • BIT_XOR
  • COUNT
  • COUNTIF
  • HLL_COUNT.INIT
  • LOGICAL_AND
  • LOGICAL_OR
  • MAX
  • MIN
  • ‫MAX_BY (אבל לא מעל STRUCT)
  • ‫MIN_BY (אבל לא מעל STRUCT)
  • SUM

תכונות SQL שלא נתמכות

התכונות הבאות של SQL לא נתמכות בתצוגות חומריות:

LEFT OUTER JOIN ותמיכה ב-UNION ALL

כדי לשלוח משוב או לבקש תמיכה בנוגע לתכונה הזו, אפשר לשלוח אימייל לכתובת bq-mv-help@google.com.

תצוגות מהותיות מצטברות תומכות ב-LEFT OUTER JOIN וב-UNION ALL. תצוגות מהותיות עם הצהרות LEFT OUTER JOIN ו-UNION ALL חולקות את המגבלות של תצוגות מהותיות מצטברות אחרות. בנוסף, smart tuning לא נתמך בתצוגות חומריות עם union all או left outer join.

דוגמאות

בדוגמה הבאה נוצרת תצוגה מהותית מצטברת מצטברת עם LEFT JOIN. התצוגה הזו מתעדכנת בהדרגה כשהנתונים מתווספים לטבלה משמאל.

CREATE MATERIALIZED VIEW dataset.mv
AS (
  SELECT
    s_store_sk,
    s_country,
    s_zip,
    SUM(ss_net_paid) AS sum_sales,
  FROM dataset.store_sales
  LEFT JOIN dataset.store
    ON ss_store_sk = s_store_sk
  GROUP BY 1, 2, 3
);

בדוגמה הבאה נוצרת תצוגה מהותית מצטברת מצטברת עם UNION ALL. התצוגה הזו מתעדכנת בהדרגה כשהנתונים מצורפים לאחת מהטבלאות או לשתי הטבלאות. מידע נוסף על עדכונים מצטברים זמין במאמר בנושא עדכונים מצטברים.

CREATE MATERIALIZED VIEW dataset.mv PARTITION BY DATE(ts_hour)
AS (
  SELECT
    SELECT TIMESTAMP_TRUNC(ts, HOUR) AS ts_hour, SUM(sales) sum_sales
  FROM
    (SELECT ts, sales from dataset.table1 UNION ALL
     SELECT ts, sales from dataset.table2)
  GROUP BY 1
);

הגבלות על בקרת גישה

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

‫WITH clause וביטויי טבלה נפוצים (CTEs)

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

דוגמאות

בדוגמה הבאה מוצגת תצוגה חומרית באמצעות פסקה WITH:

WITH tmp AS (
  SELECT TIMESTAMP_TRUNC(ts, HOUR) AS ts_hour, *
  FROM mydataset.mytable
)
SELECT ts_hour, COUNT(*) AS cnt
FROM tmp
GROUP BY ts_hour;

בדוגמה הבאה מוצגת תצוגה מהותית שמשתמשת בסעיף WITH שלא נתמך כי הוא מכיל שני סעיפים GROUP BY:

WITH tmp AS (
  SELECT city, COUNT(*) AS population
  FROM mydataset.mytable
  GROUP BY city
)
SELECT population, COUNT(*) AS cnt
GROUP BY population;

תצוגות מהותיות מעל טבלאות BigLake

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

דוגמה

יצירת תצוגה פשוטה של נתונים מצטברים באמצעות טבלת בסיס של BigLake:

CREATE MATERIALIZED VIEW sample_dataset.sample_mv
    OPTIONS (max_staleness=INTERVAL "0:30:0" HOUR TO SECOND)
AS SELECT COUNT(*) cnt
FROM dataset.biglake_base_table;

פרטים על המגבלות של תצוגות חומריות בטבלאות BigLake מופיעים במאמר תצוגות חומריות בטבלאות BigLake.

תצוגות מהותיות מעל טבלאות חיצוניות של Apache Iceberg

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

יצירת תצוגה מהותית מעל טבלת Iceberg

בדוגמה הבאה נוצרת תצוגה חומרית שתואמת למחיצה על פני טבלת Iceberg בסיסית עם מחיצות:

CREATE MATERIALIZED VIEW mydataset.myicebergmv
  PARTITION BY DATE_TRUNC(birth_month, MONTH)
AS
  SELECT * FROM mydataset.myicebergtable;

טבלת הבסיס של Iceberg‏ myicebergtable צריכה לכלול מפרט של מחיצה כמו זה שבהמשך:

  "partition-specs" : [ {
    "spec-id" : 0,
    "fields" : [ {
    "name" : "birth_month",
    "transform" : "month",
    "source-id" : 3,
    "field-id" : 1000
    } ]
  } ]

מגבלות

בנוסף למגבלות של טבלאות Iceberg רגילות, יש מגבלות נוספות לתצוגות חומריות של טבלאות Iceberg:

  • אפשר ליצור תצוגה חומרית שמתאימה לחלוקה למחיצות עם טבלת הבסיס. עם זאת, התצוגה המהותית תומכת רק בטרנספורמציה של מחיצות לפי זמן, למשל YEAR,‏ MONTH,‏ DAY ו-HOUR.
  • רמת הפירוט של המחיצה בתצוגה החומרית לא יכולה להיות גבוהה יותר מרמת הפירוט של המחיצה בטבלת הבסיס. לדוגמה, אם מחלקים את טבלת הבסיס למחיצות לפי שנה באמצעות העמודה birth_date, אי אפשר ליצור תצוגה חומרית עם PARTITION BY DATE_TRUNC(birth_date, MONTH).
  • אם יש שינויים בטבלאות הבסיסיות של Iceberg ביותר מ-4,000 מחיצות, התצוגה החומרית נפסלת לחלוטין ברענון, גם אם היא מחולקת למחיצות.
  • יש תמיכה בשינויים במחיצות. עם זאת, שינוי עמודות החלוקה למחיצות (partitioning) של טבלת בסיס בלי ליצור מחדש את התצוגה המהותית עלול לגרום לביטול תוקף מלא שלא ניתן לתקן על ידי רענון התצוגה המהותית.
  • בטבלת הבסיס חייבת להיות לפחות תמונת מצב אחת.
  • טבלת Iceberg צריכה להיות טבלת BigLake, למשל טבלה חיצונית עם הרשאה.
  • השאילתה על התצוגה החומרית עלולה להיכשל אם הקובץ metadata.json של טבלת Iceberg פגום.
  • אם VPC Service Controls מופעל, צריך להוסיף את חשבונות השירות של הטבלה החיצונית המורשית לכללי הכניסה (ingress), אחרת, VPC Service Controls יחסום את הרענון האוטומטי ברקע של התצוגה החומרית.

קובץ ה-metadata.json של טבלת Iceberg צריך לעמוד בדרישות הבאות. בלי המפרטים האלה, השאילתות שלכם סורקות את טבלת הבסיס ולא משתמשות בתוצאה המגובה.

תצוגות מהותיות עם חלוקה למחיצות

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

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

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

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

תוקף המחיצות

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

דוגמה 1

בדוגמה הזו, הטבלה הבסיסית מחולקת למחיצות (partitioning) לפי העמודה transaction_time עם מחיצות יומיות. התצוגה החומרית מחולקת למחיצות באותה עמודה ומקובצת בעמודה employee_id.

CREATE TABLE my_project.my_dataset.my_base_table(
  employee_id INT64,
  transaction_time TIMESTAMP)
  PARTITION BY DATE(transaction_time)
  OPTIONS (partition_expiration_days = 2);

CREATE MATERIALIZED VIEW my_project.my_dataset.my_mv_table
  PARTITION BY DATE(transaction_time)
  CLUSTER BY employee_id
AS (
  SELECT
    employee_id,
    transaction_time,
    COUNT(employee_id) AS cnt
  FROM
    my_dataset.my_base_table
  GROUP BY
    employee_id, transaction_time
);

דוגמה 2

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

CREATE MATERIALIZED VIEW my_project.my_dataset.my_mv_table
  PARTITION BY date
  CLUSTER BY employee_id
AS (
  SELECT
    employee_id,
    _PARTITIONDATE AS date,
    COUNT(1) AS count
  FROM
    my_dataset.my_base_table
  GROUP BY
    employee_id,
    date
);

דוגמה 3

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

שימו לב לנקודות הבאות:

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

  • במפרט המחיצות של התצוגה המגובה בחומר, רמת הפירוט צריכה להיות זהה לזו של טבלת הבסיס.

CREATE TABLE my_project.my_dataset.my_base_table (
  employee_id INT64,
  transaction_time TIMESTAMP)
  PARTITION BY DATE(transaction_time);

CREATE MATERIALIZED VIEW my_project.my_dataset.my_mv_table
  PARTITION BY DATE(transaction_hour)
AS (
  SELECT
    employee_id,
    TIMESTAMP_TRUNC(transaction_time, HOUR) AS transaction_hour,
    COUNT(employee_id) AS cnt
  FROM
    my_dataset.my_base_table
  GROUP BY
    employee_id,
    transaction_hour
);

תצוגות מהותיות של אשכולות

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

הפניה לתצוגות לוגיות

כדי לשלוח משוב או לבקש תמיכה בנוגע לתכונה הזו, אפשר לשלוח אימייל לכתובת bq-mv-help@google.com.

שאילתות של תצוגות חומריות יכולות להפנות לתצוגות לוגיות, אבל הן כפופות למגבלות הבאות:

שיקולים כשיוצרים תצוגות מהותיות

אילו תצוגות מהותיות ליצור

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

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

דוגמה נוספת: אפשר להשתמש במסנני תאריכים דטרמיניסטיים, לפי תאריך ספציפי, כמו WHERE order_date = '2019-10-01', או לפי טווח תאריכים, כמו WHERE order_date BETWEEN '2019-10-01' AND '2019-10-31'. מוסיפים מסנן של טווח תאריכים בתצוגה החומרית שכולל את טווחי התאריכים הצפויים בשאילתה:

CREATE MATERIALIZED VIEW ...
  ...
  WHERE date > '2019-01-01'
  GROUP BY date

הצטרפויות

ההמלצות הבאות רלוונטיות לתצוגות חומריות עם JOIN.

הצבת הטבלה עם השינויים הכי תכופים ראשונה

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

הימנעות מהצטרפות למפתחות אשכול

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

CREATE MATERIALIZED VIEW dataset.mv
  CLUSTER BY s_market_id
AS (
  SELECT
    s_market_id,
    s_country,
    SUM(ss_net_paid) AS sum_sales,
    COUNT(*) AS cnt_sales
  FROM dataset.store_sales
  INNER JOIN dataset.store
    ON ss_store_sk = s_store_sk
  GROUP BY s_market_id, s_country
);

נניח ש-store_sales מקובץ באשכול ב-ss_store_sk ואתם מריצים לעיתים קרובות שאילתות כמו הבאות:

SELECT
  SUM(ss_net_paid)
FROM dataset.store_sales
INNER JOIN dataset.store
ON ss_store_sk = s_store_sk
WHERE s_country = 'Germany';

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

שימוש בתצוגות מהותיות עם האפשרות max_staleness

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

נתונים לא עדכניים

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

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

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

האפשרות 'יצירה באמצעות max_staleness'

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

SQL

כדי ליצור תצוגה מהותית עם האפשרות max_staleness, מוסיפים פסקה OPTIONS להצהרת ה-DDL כשיוצרים את התצוגה המהותית:

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

    כניסה ל-BigQuery

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

    CREATE MATERIALIZED VIEW  project-id.my_dataset.my_mv_table
  OPTIONS (enable_refresh = true, refresh_interval_minutes = 60,
    max_staleness = INTERVAL "4:0:0" HOUR TO SECOND)
AS SELECT
  employee_id,
  DATE(transaction_time),
  COUNT(1) AS count
FROM my_dataset.my_base_table
GROUP BY 1, 2;

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

    • ‫project-id הוא מזהה הפרויקט.
    • ‫my_dataset הוא מזהה של מערך נתונים בפרויקט.
    • ‫my_mv_table הוא המזהה של התצוגה המהותית שאתם יוצרים.
    • ‫my_base_table הוא המזהה של טבלה במערך הנתונים שמשמשת כטבלת הבסיס לתצוגה החומרית.

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

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

API

מבצעים קריאה לשיטה tables.insert עם משאב materializedView מוגדר כחלק מבקשת ה-API. המשאב materializedView מכיל את השדה query. לדוגמה:

{
  "kind": "bigquery#table",
  "tableReference": {
    "projectId": "project-id",
    "datasetId": "my_dataset",
    "tableId": "my_mv_table"
  },
  "materializedView": {
    "query": "select product_id,sum(clicks) as
                sum_clicks from project-id.my_dataset.my_base_table
                group by 1"
  }
  "maxStaleness": "4:0:0"
}

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

  • ‫project-id הוא מזהה הפרויקט.
  • ‫my_dataset הוא מזהה של מערך נתונים בפרויקט.
  • ‫my_mv_table הוא המזהה של התצוגה המהותית שאתם יוצרים.
  • ‫my_base_table הוא המזהה של טבלה במערך הנתונים שמשמשת כטבלת הבסיס לתצוגה החומרית.
  • ‫product_id היא עמודה מטבלת הבסיס.
  • ‫clicks היא עמודה מטבלת הבסיס.
  • ‫sum_clicks היא עמודה בתצוגה המהותית שאתם יוצרים.

החלת אפשרות max_staleness

אפשר להחיל את הפרמטר הזה על תצוגות חומריות קיימות באמצעות ההצהרה ALTER MATERIALIZED VIEW. לדוגמה:

ALTER MATERIALIZED VIEW project-id.my_dataset.my_mv_table
SET OPTIONS (enable_refresh = true, refresh_interval_minutes = 120,
  max_staleness = INTERVAL "8:0:0" HOUR TO SECOND);

שאילתה באמצעות max_staleness

אפשר להריץ שאילתות על תצוגות חומריות באמצעות האפשרות max_staleness, כמו שאילתות על כל תצוגה חומרית, תצוגה לוגית או טבלה אחרת.

לדוגמה:

SELECT * FROM  project-id.my_dataset.my_mv_table

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

סטרימינג של נתונים ותוצאות של max_staleness

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

אופטימיזציה חכמה והאפשרות max_staleness

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

ניהול של נתונים לא עדכניים ותדירות הרענון

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

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

CREATE MATERIALIZED VIEW project-id.my_dataset.my_mv_table
OPTIONS (enable_refresh = true, refresh_interval_minutes = 120, max_staleness =
INTERVAL "4:0:0" HOUR TO SECOND)
AS SELECT
  employee_id,
  DATE(transaction_time),
  COUNT(1) AS cnt
FROM my_dataset.my_base_table
GROUP BY 1, 2;

תצוגות מהותיות לא מצטברות

תצוגות חומריות לא מצטברות תומכות ברוב שאילתות ה-SQL, כולל תנאי OUTER JOIN,‏ UNION ו-HAVING, ופונקציות ניתוח. כדי לבדוק אם נעשה שימוש בתצוגה חומרית בשאילתה, אפשר להשתמש בהרצה יבשה כדי לראות את הערכות העלויות. בתרחישים שבהם נתונים לא עדכניים הם סבירים, למשל בעיבוד נתונים או בדוחות, תצוגות חומריות לא מצטברות יכולות לשפר את הביצועים של השאילתות ולהפחית את העלויות. באמצעות האפשרות max_staleness, אתם יכולים ליצור תצוגות חומריות שרירותיות ומורכבות, שמתעדכנות באופן אוטומטי וכוללות ערבויות מובנות לגבי מידת העדכניות שלהן.

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

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

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

יצירה באמצעות allow_non_incremental_definition

כדי ליצור תצוגה חומרית באמצעות האפשרות allow_non_incremental_definition פועלים לפי השלבים הבאים. אחרי שיוצרים את התצוגה החומרית, אי אפשר לשנות את האפשרות allow_non_incremental_definition. לדוגמה, אי אפשר לשנות את הערך true ל-false או להסיר את האפשרות allow_non_incremental_definition מהתצוגה החומרית.

SQL

מוסיפים פסקה OPTIONS להצהרת ה-DDL כשיוצרים את התצוגה החומרית:

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

    כניסה ל-BigQuery

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

    CREATE MATERIALIZED VIEW my_project.my_dataset.my_mv_table
OPTIONS (
  enable_refresh = true, refresh_interval_minutes = 60,
  max_staleness = INTERVAL "4" HOUR,
    allow_non_incremental_definition = true)
AS SELECT
  s_store_sk,
  SUM(ss_net_paid) AS sum_sales,
  APPROX_QUANTILES(ss_net_paid, 2)[safe_offset(1)] median
FROM my_project.my_dataset.store
LEFT OUTER JOIN my_project.my_dataset.store_sales
  ON ss_store_sk = s_store_sk
GROUP BY s_store_sk
HAVING median < 40 OR median is NULL ;

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

    • ‫my_project הוא מזהה הפרויקט.
    • ‫my_dataset הוא מזהה של מערך נתונים בפרויקט.
    • ‫my_mv_table הוא המזהה של התצוגה החומרית שאתם יוצרים.
    • ‫my_dataset.store ו-my_dataset.store_sales הם מזהי הטבלאות במערך הנתונים שמשמשות כטבלאות בסיס לתצוגה החומרית.

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

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

API

מבצעים קריאה לשיטה tables.insert עם משאב materializedView מוגדר כחלק מבקשת ה-API. המשאב materializedView מכיל את השדה query. לדוגמה:

{
  "kind": "bigquery#table",
  "tableReference": {
    "projectId": "my_project",
    "datasetId": "my_dataset",
    "tableId": "my_mv_table"
  },
  "materializedView": {
    "query": "`SELECT`
        s_store_sk,
        SUM(ss_net_paid) AS sum_sales,
        APPROX_QUANTILES(ss_net_paid, 2)[safe_offset(1)] median
      FROM my_project.my_dataset.store
      LEFT OUTER JOIN my_project.my_dataset.store_sales
        ON ss_store_sk = s_store_sk
      GROUP BY s_store_sk
      HAVING median < 40 OR median is NULL`",
    "allowNonIncrementalDefinition": true
  }
  "maxStaleness": "4:0:0"
}

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

  • ‫my_project הוא מזהה הפרויקט.
  • ‫my_dataset הוא מזהה של מערך נתונים בפרויקט.
  • ‫my_mv_table הוא המזהה של התצוגה המהותית שאתם יוצרים.
  • ‫my_dataset.store ו-my_dataset.store_sales הם מזהי הטבלאות במערך הנתונים שמשמשות כטבלאות בסיס לתצוגה החומרית.

יצירת תצוגות מהותיות על מערכי נתונים חיצוניים של Spanner

לפני שממשיכים, צריך ליצור את מערך הנתונים החיצוני הבסיסי של Spanner באמצעות CLOUD_RESOURCE.

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

/*
  You must create the spanner_external_dataset with a CLOUD_RESOURCE connection.
*/
CREATE MATERIALIZED VIEW sample_dataset.sample_spanner_mv
  OPTIONS (
      enable_refresh = true, refresh_interval_minutes = 60,
      max_staleness = INTERVAL "24" HOUR,
        allow_non_incremental_definition = true)
AS
  SELECT COUNT(*) cnt FROM spanner_external_dataset.spanner_table;

שאילתה באמצעות allow_non_incremental_definition

אפשר להריץ שאילתות על תצוגות חומריות לא מצטברות כמו על כל תצוגה חומרית, תצוגה לוגית או טבלה אחרת.

לדוגמה:

SELECT * FROM  my_project.my_dataset.my_mv_table

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

מגבלות שספציפיות לתצוגות חומריות לא מצטברות

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

  • התכונה 'התאמה חכמה' לא מופעלת בתצוגות חומריות שכוללות את האפשרות allow_non_incremental_definition. הדרך היחידה ליהנות מהיתרונות של תצוגות חומריות באמצעות האפשרות allow_non_incremental_definition היא לשלוח אליהן שאילתות ישירות.
  • תצוגות חומריות בלי האפשרות allow_non_incremental_definition יכולות לרענן באופן מצטבר קבוצת משנה של הנתונים שלהן. תצוגות חומריות עם האפשרות allow_non_incremental_definition חייבות לעבור רענון מלא.
  • תצוגות חומריות עם האפשרות max_staleness מאמתות את קיומן של אילוצי אבטחה ברמת העמודה במהלך ביצוע השאילתה. פרטים נוספים על הנושא הזה זמינים במאמר בנושא בקרת גישה ברמת העמודה
  • בתצוגות חומריות מעל טבלאות של מערכי נתונים חיצוניים ב-Spanner, אם הרענון האחרון של תצוגה חומרית לא מצטברת התרחש מחוץ למרווח max_staleness, השאילתות קוראות את טבלאות הבסיס של מערכי הנתונים החיצוניים ב-Spanner, גם אם טבלת הבסיס לא השתנתה. לדוגמה, אם המרווח max_staleness הוא 4 שעות והרענון האחרון בוצע לפני 7 שעות, השאילתה תקרא את טבלאות מערך הנתונים החיצוני של Spanner.

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