Materialisierte Ansichten erstellen

In diesem Dokument erfahren Sie, wie Sie materialisierte Ansichten in BigQuery erstellen. Bevor Sie dieses Dokument lesen, sollten Sie sich mit der Einführung in materialisierte Ansichten vertraut machen.

Hinweis

Weisen Sie IAM-Rollen (Identity and Access Management) zu, die Nutzern die erforderlichen Berechtigungen zum Ausführen der einzelnen Aufgaben in diesem Dokument gewähren.

Erforderliche Berechtigungen

Zum Erstellen von materialisierten Ansichten benötigen Sie die IAM-Berechtigung bigquery.tables.create.

Jede der folgenden vordefinierten IAM-Rollen enthält die Berechtigungen, die Sie zum Erstellen einer materialisierten Ansicht benötigen:

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

Weitere Informationen zu BigQuery Identity and Access Management (IAM) finden Sie unter Zugriffssteuerung mit IAM.

Materialisierte Ansichten erstellen

Wählen Sie eine der folgenden Optionen aus, um eine materialisierte Ansicht zu erstellen:

SQL

Verwenden Sie die Anweisung CREATE MATERIALIZED VIEW. Im folgenden Beispiel wird eine materialisierte Ansicht für die Anzahl der Klicks für jede Produkt-ID erstellt:

  1. Öffnen Sie in der Google Cloud Console die Seite BigQuery.

    BigQuery aufrufen

  2. Geben Sie im Abfrageeditor die folgende Anweisung ein:

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

    Ersetzen Sie Folgendes:

    • PROJECT_ID: der Name Ihres Projekts, in dem Sie die materialisierte Ansicht erstellen möchten, z. B. myproject.
    • DATASET: der Name des BigQuery-Datasets, in dem Sie die materialisierte Ansicht erstellen möchten, z. B. mydataset. Wenn Sie eine materialisierte Ansicht über eine Amazon Simple Storage Service-Tabelle (Amazon S3) (Vorschau) erstellen, achten Sie darauf, dass sich das Dataset in einem unterstützte Region befindet.
    • MATERIALIZED_VIEW_NAME: der Name der materialisierten Ansicht, die Sie erstellen möchten, z. B. my_mv.
    • QUERY_EXPRESSION: der GoogleSQL-Abfrageausdruck, der die materialisierte Ansicht definiert, z. B. SELECT product_id, SUM(clicks) AS sum_clicks FROM mydataset.my_source_table.

  3. Klicken Sie auf Ausführen.

Informationen zum Ausführen von Abfragen finden Sie unter Interaktive Abfrage ausführen.

Beispiel

Im folgenden Beispiel wird eine materialisierte Ansicht für die Anzahl der Klicks für jede Produkt-ID erstellt:

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

Verwenden Sie die Ressource google_bigquery_table:

Richten Sie zur Authentifizierung bei BigQuery die Standardanmeldedaten für Anwendungen ein. Weitere Informationen finden Sie unter Authentifizierung für Clientbibliotheken einrichten.

Im folgenden Beispiel wird eine Ansicht mit dem Namen my_materialized_view erstellt:

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"
  deletion_protection = false # set to "true" in production

  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"
  }

}

Führen Sie die Schritte in den folgenden Abschnitten aus, um Ihre Terraform-Konfiguration auf ein Google Cloud-Projekt anzuwenden.

Cloud Shell vorbereiten

  1. Rufen Sie Cloud Shell auf.

  2. Legen Sie das Google Cloud-Standardprojekt fest, auf das Sie Ihre Terraform-Konfigurationen anwenden möchten.

    Sie müssen diesen Befehl nur einmal pro Projekt und in jedem beliebigen Verzeichnis ausführen.

    export GOOGLE_CLOUD_PROJECT=PROJECT_ID

    Umgebungsvariablen werden überschrieben, wenn Sie in der Terraform-Konfigurationsdatei explizite Werte festlegen.

Verzeichnis vorbereiten

Jede Terraform-Konfigurationsdatei muss ein eigenes Verzeichnis haben (auch als Stammmodul bezeichnet).

  1. Erstellen Sie in Cloud Shell ein Verzeichnis und eine neue Datei in diesem Verzeichnis. Der Dateiname muss die Erweiterung .tf haben, z. B. main.tf. In dieser Anleitung wird die Datei als main.tf bezeichnet. 
    mkdir DIRECTORY && cd DIRECTORY && touch main.tf

  2. Wenn Sie einer Anleitung folgen, können Sie den Beispielcode in jedem Abschnitt oder Schritt kopieren.

    Kopieren Sie den Beispielcode in das neu erstellte main.tf.

    Kopieren Sie optional den Code aus GitHub. Dies wird empfohlen, wenn das Terraform-Snippet Teil einer End-to-End-Lösung ist.

  3. Prüfen und ändern Sie die Beispielparameter, die auf Ihre Umgebung angewendet werden sollen.
  4. Speichern Sie die Änderungen.
  5. Initialisieren Sie Terraform. Dies ist nur einmal für jedes Verzeichnis erforderlich. 
    terraform init

    Fügen Sie optional die Option -upgrade ein, um die neueste Google-Anbieterversion zu verwenden: 

    terraform init -upgrade

Änderungen anwenden

  1. Prüfen Sie die Konfiguration und prüfen Sie, ob die Ressourcen, die Terraform erstellen oder aktualisieren wird, Ihren Erwartungen entsprechen: 
    terraform plan

    Korrigieren Sie die Konfiguration nach Bedarf.

  2. Wenden Sie die Terraform-Konfiguration an. Führen Sie dazu den folgenden Befehl aus und geben Sie yes an der Eingabeaufforderung ein: 
    terraform apply

    Warten Sie, bis Terraform die Meldung „Apply complete“ anzeigt.

  3. Öffnen Sie Ihr Google Cloud-Projekt, um die Ergebnisse aufzurufen. Rufen Sie in der Google Cloud Console Ihre Ressourcen in der Benutzeroberfläche auf, um sicherzustellen, dass Terraform sie erstellt oder aktualisiert hat.

API

Rufen Sie die Methode tables.insert auf und übergeben Sie eine Table-Ressource mit einem definierten materializedView-Feld:

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

Ersetzen Sie Folgendes:

  • PROJECT_ID: der Name Ihres Projekts, in dem Sie die materialisierte Ansicht erstellen möchten, z. B. myproject.
  • DATASET: der Name des BigQuery-Datasets, in dem Sie die materialisierte Ansicht erstellen möchten, z. B. mydataset. Wenn Sie eine materialisierte Ansicht über eine Amazon Simple Storage Service-Tabelle (Amazon S3) (Vorschau) erstellen, achten Sie darauf, dass sich das Dataset in einem unterstützte Region befindet.
  • MATERIALIZED_VIEW_NAME: der Name der materialisierten Ansicht, die Sie erstellen möchten, z. B. my_mv.
  • QUERY_EXPRESSION: der GoogleSQL-Abfrageausdruck, der die materialisierte Ansicht definiert, z. B. SELECT product_id, SUM(clicks) AS sum_clicks FROM mydataset.my_source_table.

Beispiel

Im folgenden Beispiel wird eine materialisierte Ansicht für die Anzahl der Klicks für jede Produkt-ID erstellt:

{
  "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

Bevor Sie dieses Beispiel anwenden, folgen Sie den Schritten zur Einrichtung von Java in der BigQuery-Kurzanleitung zur Verwendung von Clientbibliotheken. Weitere Angaben finden Sie in der Referenzdokumentation zur BigQuery Java API.

Richten Sie zur Authentifizierung bei BigQuery die Standardanmeldedaten für Anwendungen ein. Weitere Informationen finden Sie unter Authentifizierung für Clientbibliotheken einrichten. 

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

Wenn die materialisierte Ansicht erfolgreich erstellt wurde, wird sie im Bereich Explorer von BigQuery in der Google Cloud Console angezeigt. Das folgende Beispiel zeigt ein Schema einer materialisierten Ansicht:

Materialized View-Schema in der Google Cloud Console

Sofern Sie die automatischen Aktualisierung nicht deaktivieren, startet BigQuery eine asynchrone vollständige Aktualisierung für die materialisierte Ansicht. Die Abfrage wird schnell abgeschlossen, die erste Aktualisierung wird jedoch möglicherweise weiterhin ausgeführt.

Zugriffssteuerung

Sie können den Zugriff auf eine materialisierte Ansicht auf Dataset-Ebene, Ansichtsebene oder Spaltenebene gewähren. Sie können ihn aber auch auf einer höheren Ebene in der IAM-Ressourcenhierarchie definieren.

Zum Abfragen einer materialisierten Ansicht benötigen Sie Zugriff auf die Ansicht sowie auf ihre Basistabellen. Um eine materialisierte Ansicht freizugeben, können Sie den Basistabellen Berechtigungen erteilen oder eine materialisierte Ansicht als autorisierte Ansicht konfigurieren. Weitere Informationen zu Autorisierte Ansichten.

Informationen zum Steuern des Zugriffs auf Ansichten in BigQuery finden Sie unter Autorisierte Ansichten.

Unterstützung von Abfragen bei materialisierten Ansichten

Materialisierte Ansichten verwenden eine eingeschränkte SQL-Syntax. Abfragen müssen das folgende Muster verwenden:

[ 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

Abfragebeschränkungen

Für materialisierte Ansichten gelten die folgenden Beschränkungen.

Anforderungen von Aggregatabfragen

Aggregierte Werte in der Abfrage der materialisierten Ansicht müssen Ausgaben sein. Das Berechnen, Filtern oder Zusammenführen anhand eines aggregierten Werts wird nicht unterstützt. Beispielsweise wird das Erstellen einer Ansicht aus der folgenden Abfrage nicht unterstützt, da sie einen Wert erzeugt, der aus einer Zusammenfassung (COUNT(*) / 10 as cnt) berechnet wird.

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

Derzeit werden nur die folgenden Aggregatfunktion unterstützt:

  • ANY_VALUE (jedoch nicht über STRUCT)
  • APPROX_COUNT_DISTINCT
  • ARRAY_AGG (jedoch nicht über ARRAY oder STRUCT)
  • AVG
  • BIT_AND
  • BIT_OR
  • BIT_XOR
  • COUNT
  • COUNTIF
  • HLL_COUNT.INIT
  • LOGICAL_AND
  • LOGICAL_OR
  • MAX
  • MIN
  • MAX_BY (jedoch nicht über STRUCT)
  • MIN_BY (jedoch nicht über STRUCT)
  • SUM

Nicht unterstützte SQL-Features

Die folgenden SQL-Features werden in materialisierten Ansichten nicht unterstützt:

Unterstützung für LEFT OUTER JOIN und UNION ALL

Wenn Sie Feedback oder Unterstützung für dieses Feature benötigen, senden Sie eine E-Mail an bq-mv-help @google.com.

Inkrementelle materialisierte Ansichten unterstützen LEFT OUTER JOIN und UNION ALL. Materialisierte Ansichten mit den Anweisungen LEFT OUTER JOIN und UNION ALL haben die gleichen Einschränkungen wie bei anderen inkrementellen materialisierten Ansichten. Darüber hinaus wird die intelligente Abstimmung nicht für materialisierte Ansichten mit "Union All" oder "Left Outer Join" unterstützt.

Beispiele

Im folgenden Beispiel wird eine aggregierte inkrementelle materialisierte Ansicht mit einem LEFT JOIN erstellt. Diese Ansicht wird schrittweise aktualisiert, wenn Daten an die linke Tabelle angefügt werden.

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
);

Im folgenden Beispiel wird eine aggregierte inkrementelle materialisierte Ansicht mit einem UNION ALL erstellt. Diese Ansicht wird schrittweise aktualisiert, wenn Daten an eine oder beide Tabellen angefügt werden. Weitere Informationen zu inkrementellen Aktualisierungen finden Sie unter Inkrementelle Aktualisierungen.

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
);

Einschränkungen der Zugriffssteuerung

  • Wenn die Abfrage eines Nutzers einer materialisierten Ansicht Basistabellenspalten enthält, auf die er aufgrund der Sicherheit auf Spaltenebene nicht zugreifen kann, schlägt die Abfrage mit der Meldung Access Denied fehl.
  • Wenn ein Nutzer eine materialisierte Ansicht abfragt, aber keinen vollständigen Zugriff auf alle Zeilen in den Basistabellen der materialisierten Ansichten hat, führt BigQuery die Abfrage für die Basistabellen aus, anstatt Daten der materialisierten Ansicht zu lesen. Dadurch wird sichergestellt, dass alle Abfrageeinschränkungen der Zugriffssteuerung berücksichtigt werden. Diese Einschränkung gilt auch für das Abfragen von Tabellen mit datenmaskierten Spalten.

WITH-Klausel und allgemeine Tabellenausdrücke (Common Table Expressions, CTEs)

Materialisierte Ansichten unterstützen WITH-Klauseln und allgemeine Tabellenausdrücke. Materialisierte Ansichten mit WITH-Klauseln müssen weiterhin dem Muster und den Einschränkungen von materialisierten Ansichten ohne WITH-Klauseln folgen.

Beispiele

Das folgende Beispiel zeigt eine materialisierte Ansicht mit einer WITH-Klausel.

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;

Das folgende Beispiel zeigt eine materialisierte Ansicht mit einer WITH-Klausel, die nicht unterstützt wird, da sie zwei GROUP BY-Klauseln enthält:

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

Materialisierte Ansichten über BigLake-Tabellen

Zum Erstellen von materialisierten Ansichten über BigLake-Tabellen muss für die BigLake-Tabelle das Caching von Metadaten über Cloud Storage-Daten aktiviert sein und die materialisierte Ansicht muss einen Optionswert max_staleness haben, der größer ist als der der Basistabelle. Materialisierte Ansichten über BigLake-Tabellen unterstützen denselben Satz von Abfragen wie andere materialisierte Ansichten.

Beispiel

So erstellen Sie eine einfache Aggregationsansicht mit einer BigLake-Basistabelle:

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;

Weitere Informationen zu den Einschränkungen von materialisierten Ansichten über BigLake-Tabellen finden Sie unter Materialisierte Ansichten über BigLake-Tabellen.

Materialisierte Ansichten über Apache Iceberg-Tabellen

Wenn Sie Feedback oder Unterstützung für dieses Feature benötigen, senden Sie eine E-Mail an bq-mv-help@google.com.

Sie können auf große Iceberg-Tabellen in materialisierten Ansichten verweisen, anstatt diese Daten in den von BigQuery verwalteten Speicher zu migrieren.

Materialisierte Ansicht über eine Iceberg-Tabelle erstellen

So erstellen Sie eine materialisierte Ansicht über einen Iceberg:

  1. Rufen Sie eine Iceberg-Tabelle mit einer der folgenden Methoden ab:

    Beispiel

    CREATE EXTERNAL TABLE mydataset.myicebergtable
  WITH CONNECTION `myproject.us.myconnection`
  OPTIONS (
        format = 'ICEBERG',
        uris = ["gs://mybucket/mydata/mytable/metadata/iceberg.metadata.json"]
  )

  2. Verweisen Sie in der Definition der materialisierten Ansicht auf die Iceberg-Tabelle:

    CREATE MATERIALIZED VIEW mydataset.myicebergmv
AS SELECT * FROM mydataset.myicebergtable;

Beschränkungen

Zusätzlich zu den Einschränkungen von standardmäßigen Iceberg-Tabellen gelten für materialisierte Ansichten über Iceberg-Tabellen die folgenden Beschränkungen:

  • Eine an Partition ausgerichtete materialisierte Ansicht wird nicht unterstützt.
  • Bei jeder Schemaänderung wird die materialisierte Ansicht ungültig.
  • Partitionsentwicklungen werden unterstützt. Wenn Sie jedoch die Partitionierungsspalten einer Basistabelle ändern, ohne die materialisierte Ansicht neu zu erstellen, kann dies zu einer vollständigen Entwertung führen, die durch eine Aktualisierung nicht korrigiert werden kann.
  • Die Basistabelle muss mindestens einen Snapshot enthalten.
  • Die Iceberg-Tabelle muss eine BigLake-Tabelle sein, z. B. eine autorisierte externe Tabelle.
  • Wenn VPC Service Controls aktiviert ist, müssen Dienstkonten der autorisierten externen Tabelle zu den Regeln für eingehenden Traffic hinzugefügt werden. Andernfalls blockiert VPC Service Controls die automatische Hintergrundaktualisierung für die materialisierte Ansicht.

Die Datei metadata.json Ihrer Iceberg-Tabelle muss die folgenden Spezifikationen haben. Ohne diese Spezifikationen scannen Ihre Abfragen die Basistabelle und verwenden das materialisierte Ergebnis nicht.

  • In Tabellenmetadaten:

    • current-snapshot-id
    • current-schema-id
    • snapshots
    • snapshot-log

  • In Snapshots:

    • parent-snapshot-id (falls verfügbar)
    • schema-id
    • operation (im summary-Feld)

  • Partitionierung (für die partitionierte materialisierte Ansicht)

Partitionierte materialisierte Ansichten

Materialisierte Ansichten von partitionierten Tabellen können partitioniert werden. Die Partitionierung einer materialisierten Ansicht ähnelt der Partitionierung einer normalen Tabelle, da sie einen Vorteil bietet, wenn Abfragen häufig auf eine Teilmenge der Partitionen zugreifen. Darüber hinaus kann das Partitionieren einer materialisierten Ansicht das Verhalten der Ansicht verbessern, wenn Daten in der Basistabelle oder den Tabellen geändert oder gelöscht werden. Weitere Informationen zu Partitionsausrichtung.

Wenn die Basistabelle partitioniert ist, können Sie eine materialisierte Ansicht in derselben Partitionierungsspalte partitionieren. Bei zeitbasierten Partitionen muss der Detaillierungsgrad (stündlich, täglich, monatlich oder jährlich) übereinstimmen. Bei Ganzzahlbereichs-Partitionen muss die Bereichsspezifikation genau übereinstimmen. Sie können eine materialisierte Ansicht nicht über eine nicht partitionierte Basistabelle partitionieren.

Wenn die Basistabelle nach Aufnahmezeit partitioniert ist, kann eine materialisierte Ansicht nach der Spalte _PARTITIONDATE der Basistabelle gruppiert und auch partitioniert werden. Wenn Sie die Partitionierung beim Erstellen der materialisierten Ansicht nicht explizit angeben, wird die materialisierte Ansicht nicht partitioniert.

Wenn die Basistabelle partitioniert ist, sollten Sie die Partitionierung Ihrer materialisierten Ansicht in Betracht ziehen, um die Kosten für Aktualisierungsjobs und Abfragekosten zu reduzieren.

Ablauf der Partition

Der Partitionsablauf kann nicht für materialisierte Ansichten festgelegt werden. Eine materialisierte Ansicht übernimmt implizit die Ablaufzeit der Partition aus der Basistabelle. Partitionen mit materialisierten Ansichten werden an den Basistabellenpartitionen ausgerichtet, sodass sie synchron ablaufen.

Beispiel 1

In diesem Beispiel wird die Basistabelle mit täglichen Partitionen in der Spalte transaction_time partitioniert. Die materialisierte Ansicht wird nach derselben Spalte partitioniert und in der Spalte employee_id geclustert.

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
);

Beispiel 2

In diesem Beispiel wird die Basistabelle nach Aufnahmezeit und täglichen Partitionen partitioniert. Die materialisierte Ansicht wählt die Aufnahmezeit als Spalte mit dem Namen date aus. Die materialisierte Ansicht ist nach der Spalte date gruppiert und nach derselben Spalte partitioniert.

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
);

Beispiel 3

In diesem Beispiel ist die Basistabelle nach der Spalte TIMESTAMP transaction_time mit täglichen Partitionen partitioniert. Die materialisierte Ansicht definiert eine Spalte namens transaction_hour und verwendet die Funktion TIMESTAMP_TRUNC, um den Wert auf die nächste Stunde zu kürzen. Die materialisierte Ansicht ist nach transaction_hour gruppiert und auch partitioniert.

Wichtige Hinweise:

  • Die Kürzungsfunktion, die auf die Partitionierungsspalte angewendet wird, muss mindestens so detailliert wie die Partitionierung der Basistabelle sein. Wenn die Basistabelle beispielsweise tägliche Partitionen verwendet, kann die Kürzungsfunktion nicht den Detaillierungsgrad MONTH oder YEAR verwenden.

  • In der Partitionsspezifikation der materialisierten Ansicht muss der Detaillierungsgrad mit der Basistabelle übereinstimmen.

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
);

Materialisierte Ansichten im Cluster

Sie können materialisierte Ansichten nach ihren Ausgabespalten gruppieren. Dabei gelten die Einschränkungen für geclusterte Tabellen von BigQuery. Aggregierte Ausgabespalten können nicht als Clustering-Spalten verwendet werden. Durch das Hinzufügen von Clustering-Spalten zu materialisierten Ansichten kann die Leistung von Abfragen verbessert werden, die Filter für diese Spalten enthalten.

Logische Ansichten referenzieren

Wenn Sie Feedback oder Unterstützung für dieses Feature benötigen, senden Sie eine E-Mail an bq-mv-help@google.com.

Abfragen von materialisierten Ansichten können auf logische Ansichten verweisen, unterliegen jedoch den folgenden Einschränkungen:

Überlegungen zum Erstellen von materialisierten Ansichten

Welche materialisierten Ansichten erstellt werden sollen

Achten Sie beim Erstellen einer materialisierten Ansicht darauf, dass die Definition der materialisierten Ansicht die Abfragemuster der Basistabellen widerspiegelt. Da die Zahl der materialisierten Ansichten pro Tabelle auf maximal 20 begrenzt ist, sollten Sie nicht für jede Variante einer Abfrage eine Ansicht erstellen. Erstellen Sie stattdessen materialisierte Ansichten, die sich für einen umfassenderen Satz von Abfragen eignen.

Denken Sie beispielsweise an eine Abfrage für eine Tabelle, bei der Nutzer häufig nach den Spalten user_id oder department filtern. Sie können nach diesen Spalten gruppieren und optional in Clustern nach ihnen gruppieren, anstatt Filter wie user_id = 123 in die materialisierte Ansicht einzufügen.

Ein weiteres Beispiel: Häufig verwenden Nutzer Datumsfilter, um entweder nach einem bestimmten Datum, z. B. WHERE order_date = CURRENT_DATE(), oder einem Datumsbereich, z. B. WHERE order_date BETWEEN '2019-10-01' AND '2019-10-31', zu filtern. Fügen Sie in der materialisierten Ansicht einen Zeitraumfilter hinzu, der die erwarteten Zeiträume in der Abfrage abdeckt:

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

Joins

Die folgenden Empfehlungen gelten für materialisierte Ansichten mit JOINs.

Tabelle mit den häufigsten Änderungen an erster Stelle setzen

Achten Sie darauf, dass die größte oder sich am häufigsten ändernde Tabelle die erste Tabelle ganz links ist, auf die in der Ansichtsabfrage verwiesen wird. Materialisierte Ansichten mit Joins unterstützen inkrementelle Abfragen und werden aktualisiert, wenn die erste Tabelle ganz links in der Abfrage angehängt wird, Änderungen an anderen Tabellen den Ansichtscache jedoch vollständig entwerten. In Stern- oder Schneeflockenschemas sollte die erste Tabelle ganz links in der Regel die Faktentabelle sein.

Joining von Clustering-Schlüsseln vermeiden

Materialisierte Ansichten mit Joins funktionieren am besten, wenn die Daten stark aggregiert werden oder die ursprüngliche Join-Abfrage teuer ist. Für selektive Abfragen ist BigQuery oft bereits in der Lage, Joins effizient durchzuführen, und es ist keine materialisierte Ansicht erforderlich. Betrachten Sie beispielsweise die folgenden Definitionen der materialisierten Ansicht.

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
);

Angenommen, store_sales wird nach ss_store_sk geclustert und Sie führen häufig Abfragen wie die folgenden aus:

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

Die materialisierte Ansicht ist möglicherweise nicht so effizient wie die ursprüngliche Abfrage. Die besten Ergebnisse erzielen Sie, wenn Sie mit einer repräsentativen Anzahl von Abfragen mit und ohne materialisierter Ansicht experimentieren.

Materialisierte Ansichten mit der Option max_staleness verwenden

Die Option max_staleness materialisierter Ansichten hilft Ihnen, bei der Verarbeitung großer, sich häufig ändernder Datasets eine dauerhaft hohe Leistung mit kontrollierten Kosten zu erreichen. Mit dem Parameter max_staleness können Sie die Aktualität der Ergebnisse anpassen, um die Abfrageleistung zu optimieren. Dieses Verhalten kann für Dashboards und Berichte nützlich sein, für die Datenaktualität nicht wichtig ist.

Veraltete Daten

Wenn Sie materialisierte Ansichten mit max_staleness abfragen, gibt BigQuery Daten zurück, die mit dem Ergebnis einer Abfrage der materialisierten Ansicht übereinstimmen, die im Intervall max_staleness ausgeführt wurde.

Die Abfrage wird unter folgenden Bedingungen ausgeführt:

  • Wenn die letzte Aktualisierung innerhalb des Intervalls max_staleness erfolgt, gibt BigQuery Daten direkt aus der materialisierten Ansicht zurück, ohne die Basistabellen zu lesen.

    Letzte Aktualisierung im Veralterungsintervall.

  • Wenn die letzte Aktualisierung außerhalb des Intervalls max_staleness liegt, liest die Abfrage Daten aus den Basistabellen, um Ergebnisse innerhalb des Veralterungsintervalls zurückzugeben.

    Letzte Aktualisierung außerhalb des Veralterungsintervalls.

Mit der Option max_staleness erstellen

Wählen Sie eine der folgenden Optionen aus:

SQL

Um eine materialisierte Ansicht mit der Option max_staleness zu erstellen, fügen Sie beim Erstellen der materialisierten Ansicht eine OPTIONS-Klausel zur DDL-Anweisung hinzu:

  1. Öffnen Sie in der Google Cloud Console die Seite BigQuery.

    BigQuery aufrufen

  2. Geben Sie im Abfrageeditor die folgende Anweisung ein:

    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;

    Dabei gilt:

    • project-id ist die Projekt-ID.
    • my_dataset ist die ID eines Datasets in Ihrem Projekt.
    • my_mv_table ist die ID der materialisierten Ansicht, die Sie erstellen.
    • my_base_table ist die ID einer Tabelle in Ihrem Dataset, die als Basistabelle für Ihre materialisierte Ansicht dient.

    • Klicken Sie auf Ausführen.

Informationen zum Ausführen von Abfragen finden Sie unter Interaktive Abfrage ausführen.

API

Rufen Sie die Methode tables.insert mit einer definierten materializedView-Ressource im Rahmen Ihrer API-Anfrage auf. Die materializedView-Ressource enthält das Feld query. Beispiel:

{
  "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"
}

Dabei gilt:

  • project-id ist die Projekt-ID.
  • my_dataset ist die ID eines Datasets in Ihrem Projekt.
  • my_mv_table ist die ID der materialisierten Ansicht, die Sie erstellen.
  • my_base_table ist die ID einer Tabelle in Ihrem Dataset, die als Basistabelle für Ihre materialisierte Ansicht dient.
  • product_id ist eine Spalte aus der Basistabelle.
  • clicks ist eine Spalte aus der Basistabelle.
  • sum_clicks ist eine Spalte in der materialisierten Ansicht, die Sie erstellen.

Option max_staleness anwenden

Sie können diesen Parameter mithilfe der Anweisung ALTER MATERIALIZED VIEW auf vorhandene materialisierte Ansichten anwenden. Beispiel:

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);

Mit max_staleness abfragen

Sie können materialisierte Ansichten mit der Option max_staleness wie jede andere materialisierte Ansicht, logische Ansicht oder Tabelle abfragen.

Beispiel:

SELECT * FROM  project-id.my_dataset.my_mv_table

Diese Abfrage gibt Daten aus der letzten Aktualisierung zurück, wenn die Daten nicht älter als der Parameter max_staleness sind. Wenn die materialisierte Ansicht nicht innerhalb des Intervalls max_staleness aktualisiert wurde, führt BigQuery die Ergebnisse der letzten verfügbaren Aktualisierung mit den Änderungen der Basistabelle zusammen, um Ergebnisse im Intervall max_staleness zurückzugeben.

Datenstreaming und max_staleness-Ergebnisse

Wenn Sie mit der Option max_staleness Daten in die Basistabellen einer materialisierten Ansicht streamen, schließt die Abfrage der materialisierten Ansicht möglicherweise Datensätze aus, die vor dem Beginn des Veralterungsintervalls in ihre Tabellen gestreamt wurden. Daher stellt eine materialisierte Ansicht, die Daten aus mehreren Tabellen und die Option max_staleness enthält, möglicherweise keinen Snapshot dieser Tabellen zu einem bestimmten Zeitpunkt dar.

Intelligente Feinabstimmung und die Option max_staleness

Bei der intelligenten Feinabstimmung werden Abfragen automatisch neu geschrieben, um materialisierte Ansichten zu verwenden, wann immer dies möglich ist unabhängig von der Option max_staleness, auch wenn die Abfrage nicht auf eine materialisierte Ansicht verweist. Die Option max_staleness für eine materialisierte Ansicht wirkt sich nicht auf die Ergebnisse der umgeschriebenen Abfrage aus. Die Option max_staleness wirkt sich nur auf Abfragen aus, die die materialisierte Ansicht direkt abfragen.

Veralterung und Aktualisierungshäufigkeit verwalten

Sie sollten max_staleness entsprechend Ihren Anforderungen festlegen. Konfigurieren Sie das Aktualisierungsintervall so, dass die Aktualisierung innerhalb des Veralterungsintervalls erfolgt, um keine Daten aus Basistabellen zu lesen. Sie können die durchschnittliche Aktualisierungslaufzeit plus eine Marge für das Wachstum berücksichtigen.

Wenn beispielsweise eine Stunde erforderlich ist, um die materialisierte Ansicht zu aktualisieren, und ein 1-stündiger Puffer für das Wachstum vorhanden sein soll, sollten Sie das Aktualisierungsintervall auf zwei Stunden festlegen. Mit dieser Konfiguration wird sichergestellt, dass die Aktualisierung innerhalb der maximalen vier Stunden des Berichts für die Veralterung erfolgt.

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;

Nicht inkrementelle materialisierte Ansichten

Nicht inkrementelle materialisierte Ansichten unterstützen die meisten SQL-Abfragen, einschließlich OUTER JOIN-, UNION- und HAVING-Klauseln, sowie Analysefunktionen. Um festzustellen, ob eine materialisierte Ansicht in der Abfrage verwendet wurde, prüfen Sie die Kostenschätzungen mithilfe eines Probelaufs. In Szenarien, in denen veraltete Daten akzeptabel sind, z. B. für die Batchdatenverarbeitung oder Berichterstellung, können nicht inkrementelle materialisierte Ansichten die Abfrageleistung verbessern und die Kosten senken. Mit der Option max_staleness können Sie beliebige, komplexe materialisierte Ansichten erstellen, die automatisch beibehalten werden und integrierte Veralterungsgarantien haben.

Nicht inkrementelle materialisierte Ansichten verwenden

Mit der Option allow_non_incremental_definition können Sie nicht inkrementelle materialisierte Ansichten erstellen. Zu dieser Option wird zusätzlich die Option max_staleness benötigt. Um eine regelmäßige Aktualisierung der materialisierten Ansicht zu gewährleisten, sollten Sie auch eine Aktualisierungsrichtlinie konfigurieren. Ohne Aktualisierungsrichtlinie müssen Sie die materialisierte Ansicht manuell aktualisieren.

Die materialisierte Ansicht stellt immer den Status der Basistabellen innerhalb des Intervalls max_staleness dar. Wenn die letzte Aktualisierung zu veraltet ist und nicht die Basistabellen innerhalb des Intervalls max_staleness darstellt, liest die Abfrage die Basistabellen. Weitere Informationen zu möglichen Auswirkungen auf die Leistung finden Sie unter Datenveralterung.

Mit allow_non_incremental_definition erstellen

So erstellen Sie eine materialisierte Ansicht mit der Option allow_non_incremental_definition: Nachdem Sie die materialisierte Ansicht erstellt haben, können Sie die Option allow_non_incremental_definition nicht mehr ändern. Sie können beispielsweise den Wert true nicht in false ändern oder die Option allow_non_incremental_definition aus der materialisierten Ansicht entfernen.

SQL

Fügen Sie der DDL-Anweisung beim Erstellen der materialisierten Ansicht eine OPTIONS-Klausel hinzu:

  1. Öffnen Sie in der Google Cloud Console die Seite BigQuery.

    BigQuery aufrufen

  2. Geben Sie im Abfrageeditor die folgende Anweisung ein:

    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 ;

    Dabei gilt:

    • my_project ist die Projekt-ID.
    • my_dataset ist die ID eines Datasets in Ihrem Projekt.
    • my_mv_table ist die ID der materialisierten Ansicht, die Sie erstellen.
    • my_dataset.store und my_dataset.store_sales sind die IDs der Tabellen in Ihrem Dataset, die als Basistabellen für Ihre materialisierte Ansicht dienen.

  3. Klicken Sie auf Ausführen.

Informationen zum Ausführen von Abfragen finden Sie unter Interaktive Abfrage ausführen.

API

Rufen Sie die Methode tables.insert mit einer definierten materializedView-Ressource im Rahmen Ihrer API-Anfrage auf. Die materializedView-Ressource enthält das Feld query. Beispiel:

{
  "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"
}

Dabei gilt:

  • my_project ist die Projekt-ID.
  • my_dataset ist die ID eines Datasets in Ihrem Projekt.
  • my_mv_table ist die ID der materialisierten Ansicht, die Sie erstellen.
  • my_dataset.store und my_dataset.store_sales sind die IDs der Tabellen in Ihrem Dataset, die als Basistabellen für Ihre materialisierte Ansicht dienen.

Mit allow_non_incremental_definition abfragen

Sie können nicht inkrementelle materialisierte Ansichten wie jede andere materialisierte Ansicht, logische Ansicht oder Tabelle abfragen.

Beispiel:

SELECT * FROM  my_project.my_dataset.my_mv_table

Wenn die Daten nicht älter als der Parameter max_staleness sind, gibt diese Abfrage Daten aus der letzten Aktualisierung zurück. Weitere Informationen zur Veralterung und Aktualität von Daten finden Sie unter Datenveralterung.

Einschränkungen speziell für nicht inkrementelle materialisierte Ansichten

Die folgenden Einschränkungen gelten nur für materialisierte Ansichten mit der Option allow_non_incremental_definition. Mit Ausnahme von Einschränkungen in der unterstützten Abfragesyntax gelten weiterhin alle Einschränkungen für materialisierte Ansichten.

  • Die intelligente Feinabstimmung wird nicht auf die materialisierten Ansichten angewendet, die die Option allow_non_incremental_definition enthalten. Die einzige Möglichkeit, von materialisierten Ansichten mit der Option allow_non_incremental_definition zu profitieren, besteht darin, sie direkt abzufragen.
  • Materialisierte Ansichten ohne die Option allow_non_incremental_definition können eine Teilmenge ihrer Daten schrittweise aktualisieren. Materialisierte Ansichten mit der Option allow_non_incremental_definition müssen vollständig aktualisiert werden.
  • Materialisierte Ansichten mit der Option "max_staleness" prüfen das Vorhandensein von Sicherheitseinschränkungen auf Spaltenebene während der Abfrageausführung. Weitere Informationen hierzu finden Sie unter Zugriffssteuerung auf Spaltenebene.

Nächste Schritte