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

במאמר הזה נסביר איך ליצור תצוגות חומריות ב-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 ומעבירים Table resource עם שדה 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.

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

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

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

  • אם משתמש מריץ שאילתה על תצוגה חומרית אבל אין לו גישה מלאה לכל השורות בטבלאות הבסיס של התצוגה החומרית, מערכת 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
);

‫clause WITH וביטויי טבלה נפוצים (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 מחיצות, התצוגה החומרית תבוטל לחלוטין ברענון, גם אם היא מחולקת למחיצות.
  • יש תמיכה בשינויים במחיצות. עם זאת, שינוי עמודות החלוקה של טבלת בסיס בלי ליצור מחדש את התצוגה החומרית עלול לגרום לביטול תוקף מלא שלא ניתן לתקן על ידי רענון התצוגה החומרית.
  • בטבלת הבסיס חייבת להיות לפחות תמונה אחת.
  • טבלת Iceberg צריכה להיות טבלת BigLake, למשל טבלה חיצונית עם הרשאה.
  • השאילתה על התצוגה החומרית עלולה להיכשל אם הקובץ metadata.json של טבלת Iceberg פגום.
  • אם VPC Service Controls מופעל, צריך להוסיף את חשבונות השירות של הטבלה החיצונית המורשית לכללי הכניסה (ingress), אחרת, VPC Service Controls יחסום את הרענון האוטומטי ברקע של התצוגה החומרית.

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

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

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

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

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

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

תוקף המחיצות

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

דוגמה 1

בדוגמה הזו, טבלת הבסיס מחולקת למחיצות בעמודה 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 לא תייצג תמונת מצב של הטבלאות האלה בנקודת זמן מסוימת.

הגבלות על נתונים בסטרימינג ועל שימוש בתכונה 'חזרה בזמן'

למערכי נתונים ב-BigQuery יש חלון זמן של 7 ימים לשימוש בתכונה Time Travel, אבל מאגר הנתונים הזמני של הסטרימינג שומר נתונים רק למשך 3 ימים. כתוצאה מכך, יש מגבלה על תצוגות חומריות:

  • שאילתה נכשלת: אם תצוגה חומרית משתמשת באפשרות max_staleness ולא בוצע בה רענון במשך יותר מ-3 ימים, שאילתות שמופעלות על התצוגה נכשלות ומוצגת הודעת השגיאה Streaming data from <materialized_view_name> is temporarily unavailable.

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

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

התאמה חכמה והאפשרות 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, כולל פסקי ה-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.

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