Materialisierte Ansichten erstellen und verwenden

In diesem Dokument wird beschrieben, wie Sie materialisierte Ansichten mit BigQuery Materialized Views erstellen und verwenden. Bevor Sie dieses Dokument lesen, sollten Sie sich mit der Einführung in materialisierte Ansichten vertraut machen.

Erforderliche Berechtigungen

Bevor Sie mit einer materialisierten Ansicht arbeiten können, benötigen Sie die erforderlichen Berechtigungen für die materialisierte Ansicht. In der folgenden Tabelle sind die Arten von Vorgängen aufgeführt, die Sie für materialisierte Ansichten ausführen können, die Befehle und Methoden, die Sie für diese Vorgänge verwenden können, die erforderlichen Berechtigungen für jeden Vorgang und die Standardrollen, die diese Berechtigungen enthalten.

Weitere Informationen zu BigQuery Identity and Access Management (IAM) finden Sie unter Vordefinierte Rollen und Berechtigungen.

Vorgangstyp Befehl oder Methode Erforderliche Berechtigungen Standardrollen
Erstellen CREATE MATERIALIZED VIEW bq mk --materialized view tables.insert bigquery.tables.create bigquery.dataEditor bigquery.dataOwner bigquery.admin
Query Standard-SQL-Abfrage bq query tables.getData bigquery.dataViewer bigquery.dataEditor bigquery.dataOwner bigquery.admin
Aktualisieren ALTER MATERIALIZED VIEW bq query bq update tables.patch tables.update bigquery.tables.get bigquery.tables.update bigquery.dataEditor bigquery.dataOwner bigquery.admin
Löschen DROP MATERIALIZED VIEW bq query bq rm tables.delete bigquery.tables.get bigquery.tables.delete bigquery.dataEditor bigquery.dataOwner bigquery.admin
Manuelle Aktualisierung CALL BQ.REFRESH_MATERIALIZED_VIEW bigquery.tables.getData bigquery.tables.update bigquery.tables.updateData bigquery.dataEditor bigquery.dataOwner bigquery.admin

Materialisierte Ansichten erstellen

BigQuery-Material-Ansichten können über die Google Cloud Console, das bq-Befehlszeilentool oder die BigQuery API erstellt werden.

Für diese Beispiele nehmen wir an, dass Ihre Basistabelle den Namen my_base_table und dieses Schema hat:

Spaltenname Typ
product_id integer
clicks integer

Nehmen wir außerdem an, dass Sie eine materialisierte Ansicht benötigen, in der die Anzahl der Klicks pro product_id zusammengefasst ist. Mit den folgenden Schritten wird eine materialisierte Ansicht namens my_mv_table mit folgendem Schema erstellt.

Spaltenname Typ
product_id integer
sum_clicks integer

So erstellen Sie eine materialisierte Ansicht:

Console

Anweisungen zur Datendefinitionssprache (Data Definition Language, DDL) ermöglichen das Erstellen und Ändern von Tabellen und Ansichten mit der Standard-SQL-Abfragesyntax.

Weitere Informationen finden Sie unter DDL-Anweisungen verwenden.

So erstellen Sie in der Cloud Console mithilfe einer DDL-Anweisung eine materialisierte Ansicht:

  1. Rufen Sie in der Cloud Console die BigQuery-Seite auf.

    BigQuery aufrufen

  2. Klicken Sie auf Neue Abfrage erstellen.

  3. Geben Sie die CREATE MATERIALIZED VIEW-DDL-Anweisung in den Textbereich des Abfrageeditors ein.

    CREATE MATERIALIZED VIEW  project-id.my_dataset.my_mv_table
    AS SELECT product_id, SUM(clicks) AS sum_clicks
    FROM  project-id.my_dataset.my_base_table
    GROUP BY 1
    

    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.
  4. Klicken Sie auf Ausführen.

Sofern Sie die automatischen Aktualisierungen nicht deaktivieren, startet BigQuery eine asynchrone vollständige Aktualisierung für die materialisierte Ansicht. Die Abfrage gibt möglicherweise schon einen Erfolg zurück, während die erste Aktualisierung noch läuft. Wenn die materialisierte Ansicht erfolgreich erstellt wurde, wird sie im Bereich Datasets angezeigt.

bq

Verwenden Sie den Befehl bq query und geben Sie die DDL-Anweisung als Abfrageparameter an.

bq query --use_legacy_sql=false '
CREATE MATERIALIZED VIEW project-id.my_dataset.my_mv_table
AS SELECT product_id, SUM(clicks) AS sum_clicks
FROM  project-id.my_dataset.my_base_table
GROUP BY 1'

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.

Sofern Sie die automatischen Aktualisierungen nicht deaktivieren, startet BigQuery eine asynchrone vollständige Aktualisierung für die materialisierte Ansicht. Die Abfrage gibt möglicherweise schon einen Erfolg zurück, während die erste Aktualisierung noch läuft. Wenn die materialisierte Ansicht erfolgreich erstellt wurde, wird sie im Bereich Datasets angezeigt.

Alternativ können Sie den Befehl bq mk mit dem Argument --materialized_view verwenden, um eine materialisierte Ansicht zu erstellen. Die folgenden Argumente funktionieren mit dem Argument --materialized_view:

  • --enable_refresh: Gibt an, ob automatische Aktualisierung aktiviert ist.
  • --refresh_interval_ms: Gibt die Zeit in Millisekunden für das Aktualisierungsintervall an.

API

Rufen Sie die Methode tables.insert mit einer definierten materializedView-Ressource im Rahmen Ihrer API-Anfrage auf. Die Ressource materializedView 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"
  }
}

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.

Sofern Sie die automatischen Aktualisierungen nicht deaktivieren, startet BigQuery eine asynchrone vollständige Aktualisierung für die materialisierte Ansicht. Die Abfrage gibt möglicherweise schon einen Erfolg zurück, während die erste Aktualisierung noch läuft. Wenn die materialisierte Ansicht erfolgreich erstellt wurde, wird sie im Bereich Datasets angezeigt.

Java

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

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

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.

Abfragen von materialisierten Ansichten

Sie können materialisierte Ansichten genauso wie eine normale Tabelle oder Standardansicht direkt abfragen.

Wenn Sie eine Basistabelle mit einer materialisierten Ansicht abfragen, wird sie vom Abfrageoptimierungstool möglicherweise automatisch auf das Ergebnis umgeschrieben, das im Cache der materialisierten Ansicht gespeichert ist. Der Abfrageplan zeigt, dass die Abfrage auf die materialisierte Ansicht umgeschrieben wurde.

Wenn mehrere materialisierte Ansichten zum Umschreiben einer Abfrage verwendet werden können, wird die materialisierte Ansicht mit der kleinsten geschätzten Anzahl von Zeilen verwendet, die gescannt werden soll.

Wenn Sie eine nicht aktuelle materialisierte Ansicht abfragen, werden die Daten aus der materialisierten Ansicht mit Deltaänderungen aus der Basistabelle kombiniert, um aktuelle Ergebnisse zu berechnen. Mögliche Gründe dafür, dass eine materialisierte Ansicht nicht aktuell ist:

  • Die materialisierte Ansicht ist nicht für die automatische Aktualisierung eingerichtet, da der Nutzer die Aktualisierung manuell steuern möchte.

  • Die Basistabelle wird zu häufig aktualisiert, sodass die Häufigkeitsbegrenzung erzwungen wird.

  • Die Basistabelle wurde vor wenigen Sekunden geändert, sodass die materialisierte Ansicht noch nicht mit der Basistabelle Schritt gehalten hat.

Auch wenn eine materialisierte Ansicht aus einem oder mehreren dieser Gründe nicht aktuell ist, sind Abfragen in der materialisierten Ansicht noch aktuell, da Änderungen aus der Basistabelle auch in den Abfrageergebnissen angezeigt werden.

Informationen zum Ausführen von Abfragen finden Sie unter BigQuery-Daten abfragen.

Materialisierte Tabelle ändern

Sie können eine materialisierte Ansicht über die Cloud Console oder das bq-Befehlszeilentool ändern. Verwenden Sie dafür DDL mit ALTER MATERIALIZED VIEW und SET OPTIONS.

Im folgenden Beispiel wird enable_refresh auf true gesetzt. Passen Sie dies nach Bedarf an Ihren Fall an.

Console

So ändern Sie eine materialisierte Ansicht in der Cloud Console mithilfe einer DDL-Anweisung:

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

BigQuery aufrufen

  1. Klicken Sie auf Neue Abfrage erstellen.

  2. Geben Sie die ALTER MATERIALIZED VIEW-DDL-Anweisung in den Textbereich des Abfrageeditors ein.

    ALTER MATERIALIZED VIEW project-id.my_dataset.my_mv_table
    SET OPTIONS (enable_refresh=true)
    

    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 ändern.
  3. Klicken Sie auf Ausführen.

bq

Verwenden Sie den Befehl bq query und geben Sie die DDL-Anweisung als Abfrageparameter an.

ALTER MATERIALIZED VIEW project-id.my_dataset.my_mv_table
SET OPTIONS (enable_refresh=true)

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 ändern.

Alternativ können Sie den Befehl bq update ausführen.

bq update \
--enable_refresh=true \
--refresh_interval_ms= \
project-id.my_dataset.my_mv_table

Java

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

import com.google.cloud.bigquery.BigQuery;
import com.google.cloud.bigquery.BigQueryException;
import com.google.cloud.bigquery.BigQueryOptions;
import com.google.cloud.bigquery.QueryJobConfiguration;
import com.google.cloud.bigquery.TableResult;

public class QueryMaterializedView {

  public static void main(String[] args) throws InterruptedException {
    // TODO(developer): Replace these variables before running the sample.
    String datasetName = "MY_DATASET_NAME";
    String materializedViewName = "MY_MATERIALIZED_VIEW_NAME";
    String query = String.format("SELECT * FROM %s.%s", datasetName, materializedViewName);
    queryMaterializedView(query);
  }

  public static void queryMaterializedView(String query) throws InterruptedException {
    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();

      TableResult results = bigquery.query(QueryJobConfiguration.of(query));
      results
          .iterateAll()
          .forEach(row -> row.forEach(val -> System.out.printf("%s\n", val.toString())));

      System.out.println("Query performed successfully.");
    } catch (BigQueryException e) {
      System.out.println("Query was not performed. \n" + e.toString());
    }
  }
}

Materialisierte Ansichten bearbeiten

Eine materialisierte Ansicht kann nur durch die Anweisungen CREATE, DROP oder ALTER, ihre API-Entsprechungen und eine manuelle Aktualisierung bearbeitet werden.

Die folgenden Vorgänge sind für eine materialisierte Ansicht nicht zulässig.

  • Kopier-, Import- oder Exportjobs, bei denen die Quelle oder das Ziel eine materialisierte Ansicht ist.

  • Abfrageergebnisse in eine materialisierte Ansicht schreiben.

  • Der Aufruf von tabledata.list.

  • Mit der BigQuery Storage Read API.

Im folgenden Beispiel wird eine materialisierte Ansicht geändert:

Java

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

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

// Sample to alter materialized view
public class AlterMaterializedView {

  public static void main(String[] args) {
    // TODO(developer): Replace these variables before running the sample.
    String datasetName = "MY_DATASET_NAME";
    String materializedViewName = "MY_MATERIALIZED_VIEW_NAME";
    alterMaterializedView(datasetName, materializedViewName);
  }

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

      // Get existing materialized view
      Table table = bigquery.getTable(tableId);
      MaterializedViewDefinition materializedViewDefinition = table.getDefinition();
      // Alter materialized view
      materializedViewDefinition
          .toBuilder()
          .setEnableRefresh(true)
          .setRefreshIntervalMs(1000L)
          .build();
      table.toBuilder().setDefinition(materializedViewDefinition).build().update();
      System.out.println("Materialized view altered successfully");
    } catch (BigQueryException e) {
      System.out.println("Materialized view was not altered. \n" + e.toString());
    }
  }
}

Materialisierte Ansichten löschen

Sie können eine materialisierte Ansicht über die Cloud Console, das bq-Befehlszeilentool oder die API löschen.

Console

So löschen Sie eine materialisierte Ansicht in der Cloud Console mithilfe einer DDL-Anweisung:

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

BigQuery aufrufen

  1. Klicken Sie auf Neue Abfrage erstellen.

  2. Geben Sie die DELETE MATERIALIZED VIEW-DDL-Anweisung in den Textbereich des Abfrageeditors ein.

    DROP MATERIALIZED VIEW project-id.my_dataset.my_mv_table
    

    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 löschen.
  3. Klicken Sie auf Ausführen.

bq

Verwenden Sie den Befehl bq query und geben Sie die DDL-Anweisung als Abfrageparameter an.

bq query --use_legacy_sql=false '
DROP MATERIALIZED VIEW
project-id.my_dataset.my_mv_table'

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 löschen.

Alternativ können Sie den Befehl bq rm verwenden, um die materialisierte Ansicht zu löschen.

API

Rufen Sie die Methode tables.delete auf und geben Sie Werte für die Parameter projectId, datasetId und tableId an:

  • Weisen Sie Ihrer Projekt-ID den Parameter projectId zu.
  • Weisen Sie Ihrer Dataset-ID den Parameter datasetId zu.
  • Weisen Sie der Tabellen-ID der materialisierten Ansicht, die Sie löschen möchten, den Parameter tableId zu.

Java

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

import com.google.cloud.bigquery.BigQuery;
import com.google.cloud.bigquery.BigQueryException;
import com.google.cloud.bigquery.BigQueryOptions;
import com.google.cloud.bigquery.TableId;

// Sample to delete materialized view
public class DeleteMaterializedView {

  public static void main(String[] args) {
    // TODO(developer): Replace these variables before running the sample.
    String datasetName = "MY_DATASET_NAME";
    String materializedViewName = "MY_MATERIALIZED_VIEW_NAME";
    deleteMaterializedView(datasetName, materializedViewName);
  }

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

      boolean success = bigquery.delete(tableId);
      if (success) {
        System.out.println("Materialized view deleted successfully");
      } else {
        System.out.println("Materialized view was not found");
      }
    } catch (BigQueryException e) {
      System.out.println("Materialized view was not found. \n" + e.toString());
    }
  }
}

Materialisierte Ansichten überwachen

Mit der BigQuery API können Sie Informationen zu materialisierten Ansichten und Aktualisierungsjobs zu materialisierten Ansichten abrufen.

Monitoring von materialisierten Ansichten

Rufen Sie für die Suche nach materialisierten Ansichten die Methode tables.list auf oder fragen Sie die Tabelle INFORMATION_SCHEMA.TABLES ab.

Rufen Sie zum Abrufen der Attribute einer materialisierten Ansicht die Methode tables.get auf oder fragen Sie die Tabelle INFORMATION_SCHEMA.TABLE_OPTIONS ab.

Materialisierte Ansichten sind in der Tabelle INFORMATION_SCHEMA.VIEWS nicht aufgeführt.

Monitoring von Aktualisierungsjobs für materialisierte Ansichten

Wenn Sie Aktualisierungsjobs suchen, rufen Sie zum Auflisten die Methode jobs.list auf. Rufen Sie die Methode jobs.get auf, um Details zu den Jobs abzurufen. Automatische Aktualisierungsjobs enthalten das Präfix materialized_view_refresh in der Job-ID und werden von einem BigQuery-Administratorkonto gestartet.

Beispiel:

SELECT job_id, total_slot_ms, total_bytes_processed
FROM region-US.INFORMATION_SCHEMA.JOBS_BY_PROJECT
WHERE job_id LIKE "%materialized_view_refresh_%"
LIMIT 10

Unterstützte materialisierte Ansichten

Materialisierte Ansichten müssen eine Zusammenfassung über eine einzelne Tabelle sein. Die Verwendung von GROUP BY ist optional.

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

SELECT
  expression [ [ AS ] alias ] ] [, ...]
FROM from_item [, ...]
[ WHERE bool_expression ]
[ GROUP BY expression [, ...] ]

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
  • SUM

Bestimmte Unterabfragen werden unterstützt, solange sie keine Berechnung oder Filterung zusätzlich zu einer Aggregation hinzufügen. Die folgenden SQL-Abfragen werden beispielsweise unterstützt:

WITH tmp AS (
  SELECT TIMESTAMP_TRUNC(ts, HOUR) as ts_hour, *
  FROM dataset.table
)
SELECT ts_hour, COUNT(*) as cnt
FROM tmp
GROUP BY ts_hour

WITH tmp AS (
  SELECT TIMESTAMP_TRUNC(ts, HOUR) as ts_hour, COUNT(*) as cnt
  FROM dataset.table
  GROUP BY 1
)
SELECT * FROM tmp

SELECT ts_hour, COUNT(*) as cnt
FROM (
  SELECT TIMESTAMP_TRUNC(ts, HOUR) as ts_hour, *
  FROM dataset.table
)
GROUP BY ts_hour

SELECT * FROM (
  SELECT TIMESTAMP_TRUNC(ts, HOUR) as ts_hour, COUNT(*) as cnt
  FROM dataset.table
  GROUP BY 1
)

Die folgende SQL-Abfrage wird für materialisierte Ansichten nicht unterstützt, da sie auf eine Aggregation angewendet wird:

-- Not supported for a materialized view
WITH tmp AS (
  SELECT TIMESTAMP_TRUNC(ts, HOUR) as ts_hour, COUNT(*) as cnt
  FROM dataset.table
  GROUP BY 1
)
SELECT TIMESTAMP_TRUNC(ts_hour, DAY) as ts_day, cnt+1 as cnt FROM tmp

Die folgende SQL-Abfrage wird für materialisierte Ansichten nicht unterstützt, da sie auf eine Aggregation angewendet wird:

-- Not supported for a materialized view
SELECT ts_hour, cnt
FROM (
  SELECT TIMESTAMP_TRUNC(ts, HOUR) as ts_hour, COUNT(*) as cnt
  FROM dataset.table
  GROUP BY 1
)
WHERE ts_hour != TIMESTAMP(DATE('2020-01-01'))

FROM-Klausel

Die FROM-Klausel muss eine einzelne Tabelle enthalten und kann die Verschachtelung von einem oder mehreren Array-Ausdrücken aufheben.

Beispiele

FROM mytable

FROM mytable AS t, t.struct_column.array_field AS x

FROM mytable AS t LEFT JOIN UNNEST(t.array_column) AS x

FROM mytable AS t, t.array_column AS x, x.array_field AS y

FROM mytable AS t, UNNEST(SPLIT(t.string_column)) AS x

WITH OFFSET wird nicht unterstützt.

Unterstützte Abfrage-Umschreibungsmuster

Die folgenden Muster zur Umschreibung von Abfragen werden in Abfragen von Basistabellen und Definitionen materialisierter Ansichten unterstützt.

Muster 1

Dieses Muster zeigt, dass die Gruppierungsschlüssel und Aggregatoren in der Abfrage eine Untergruppe der Schlüssel in der materialisierten Ansicht sind.

Für diese Abfrage der Basistabelle gilt:

SELECT
  ss_sold_date_sk,
  SUM(ss_net_profit) AS sum_profit
FROM store_sales
GROUP BY 1

Im Folgenden finden Sie eine Abfrage der materialisierten Ansicht, die die Gruppierungsschlüssel und Aggregatoren der Basistabellenabfrage als Teilmenge der Abfrage der materialisierten Ansicht zeigt.

SELECT
  ss_store_sk,
  ss_sold_date_sk,
  SUM(ss_net_paid) AS sum_paid,
  SUM(ss_net_profit) AS sum_profit,
  COUNT(*) AS cnt_sales
FROM store_sales
GROUP BY 1, 2

Muster 2

Dieses Muster zeigt, dass Gruppierungsschlüssel in der Basistabellenabfrage aus Gruppierungsschlüsseln in der materialisierten Ansicht berechnet werden können.

Für diese Abfrage der Basistabelle gilt:

SELECT
  DATE_TRUNC(DATE(l_shipdate) MONTH) as shipmonth,
  SUM(l_extendedprice) as sum_price
FROM lineitem
GROUP BY 1

Im Folgenden finden Sie eine Abfrage der materialisierten Ansicht, die Gruppierungsschlüssel in der Basistabelle berechnet:

SELECT
  DATE(l_shipdate) as shipdate,
  SUM(l_extendedprice) as sum_price
FROM lineitem
GROUP BY 1

Muster 3

Dieses Muster zeigt, dass Filterausdrücke in der Abfrage aus Gruppierungsschlüsseln in der materialisierten Ansicht abgeleitet werden können oder genau mit der materialisierten Ansicht übereinstimmen.

Für diese Abfrage der Basistabelle gilt:

SELECT
  DATE_TRUNC(DATE(l_shipdate) MONTH) as shipmonth,
  SUM(l_extendedprice) as sum_price
FROM lineitem
WHERE DATE(l_shipdate) >= DATE(2016, 01, 01) AND l_shipmode = "AIR"
GROUP BY 1

Im Folgenden finden Sie eine Abfrage der materialisierten Ansicht, die einen Filterausdruck aus der Basistabelle ableitet:

SELECT
  DATE(l_shipdate) as shipdate,
  SUM(l_extendedprice) as sum_price
FROM lineitem
WHERE l_shipmode = "AIR"
GROUP BY 1

Muster 4

Dieses Muster zeigt, wie Ausdrücke in der Basistabellenabfrage eine Teilmenge der Daten in der materialisierten Ansicht auswählen.

Für diese Abfrage der Basistabelle gilt:

SELECT
  l_discount,
  COUNT(*) as cnt
FROM lineitem
WHERE l_discount > 20.0
GROUP BY 1

Im Folgenden finden Sie eine Abfrage der materialisierten Ansicht, deren Ergebnisse eine Obermenge der Basistabellenabfrage sind:

SELECT
  l_discount,
  COUNT(*) as cnt
FROM lineitem
WHERE l_discount IS NOT NULL
GROUP BY 1

Inkrementelle Aktualisierungen

Wenn sich die Basistabelle immer wieder nur mit Anfügungen ändert, scannt die Abfrage, die die materialisierte Ansicht verwendet, alle Daten der materialisierten Ansicht sowie ein Delta in der Basistabelle seit dem letzten Aktualisierungsvorgang. Dies gilt unabhängig davon, ob die materialisierte Ansicht explizit referenziert oder vom Abfrageoptimierungstool ausgewählt wird, und führt in beiden Fällen zu schnelleren und kostengünstigeren Abfragen.

Wenn jedoch seit der letzten Aktualisierung der materialisierten Ansicht in der Basistabelle Aktualisierungen oder Löschungen stattgefunden haben, kann die materialisierte Ansicht möglicherweise nicht gescannt werden.

Im Folgenden sind Beispiele für Aktualisierungs- oder Löschvorgänge aufgeführt:

  • DML UPDATE
  • DML MERGE
  • DML DELETE
  • Abgeschnittener Text
  • Ablauf der Partition
  • Console, bq-Befehlszeile und API-Entsprechungen der vorhergehenden Elemente in dieser Liste

Wenn diese Vorgänge stattfinden, erzielt die Abfrage der materialisierten Ansicht möglicherweise bis zur nächsten Aktualisierung der Ansicht keine Einsparungen. Tatsächlich macht jeder Aktualisierungs- oder Löschvorgang in der Basistabelle einen Teil des Zustands der materialisierten Ansichts ungültig. Bei nicht partitionierten Ansichten wird die gesamte Ansicht ungültig. Bei partitionierten Ansichten werden in den meisten Fällen nur die betroffenen Partitionen ungültig.

Beachten Sie, dass Daten aus dem BigQuery-Streamingzwischenspeicher der Basistabelle nicht in einer materialisierten Ansicht gespeichert werden. Ein Streamingzwischenspeicher wird immer noch vollständig gescannt, unabhängig davon, ob eine materialisierte Ansicht verwendet wird.

Mit partitionierten und geclusterten Tabellen arbeiten

Materialisierte Ansichten können durch dieselbe Partitionsspalte partitioniert werden, die für die Basistabelle konfiguriert ist.

Materialisierte Ansichten können von beliebigen Spalten geclustert werden, die den Einschränkungen bei geclusterten Tabellen von BigQuery unterliegen.

Im Folgenden wird gezeigt, wie Sie eine Basistabelle mit einer DATE-Partition in der Spalte transaction_time erstellen:

CREATE TABLE project-id.my_dataset.my_base_table(
  employee_id INT64,
  transaction_time TIMESTAMP)
PARTITION BY DATE(transaction_time)
OPTIONS ( partition_expiration_days = 2)

Im Folgenden wird gezeigt, wie Sie eine durch dieselbe Spalte transaction_time partitionierte Ansicht erstellen.

CREATE MATERIALIZED VIEW project-id.my_dataset.my_mv_table
PARTITION BY DATE(transaction_time)
CLUSTER BY employee_id
AS SELECT
  employee_id,
  transaction_time,
  COUNT(1) AS cnt
FROM my_dataset.my_base_table
GROUP BY 1, 2;

Wenn die Basistabelle nach Aufnahmezeit partitioniert ist, kann eine materialisierte Ansicht nach der Spalte _PARTITIONDATE der Basistabelle gruppiert und auch partitioniert werden.

Im Folgenden wird gezeigt, wie Sie eine Basistabelle erstellen, die nach Aufnahmezeit partitioniert ist:

CREATE TABLE project-id.my_dataset.my_base_table(
  employee_id INT64)
PARTITION BY _PARTITIONDATE
OPTIONS ( partition_expiration_days = 2)

Im Folgenden wird gezeigt, wie eine materialisierte Ansicht erstellt wird, die nach der Aufnahmezeit aus der Basistabelle gruppiert und auch partitioniert ist:

CREATE MATERIALIZED VIEW project-id.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 1, 2;

Wenn die Basistabelle nach TIMESTAMP partitioniert ist, kann die materialisierte Ansicht diesen Zeitstempel auf die Genauigkeit eines DAY kürzen und dann den abgeschnittenen Zeitstempel als Partitionierungsspalte verwenden.

Im Folgenden wird gezeigt, wie Sie eine nach der Spalte transaction_hour partitionierte Ansicht erstellen, die eine Verkürzung der Spalte transaction_time der Basistabelle ist. Verwenden Sie die Funktion TIMESTAMP_TRUNC, um den Zeitstempel zu kürzen.

CREATE MATERIALIZED VIEW project-id.my_dataset.my_mv_table
PARTITION BY DATE(transaction_hour)
AS SELECT
  employee_id,
  TIMESTAMP_TRUNC(transaction_time, HOUR) as transaction_hour,
  COUNT(1) AS cnt
FROM my_dataset.my_base_table
GROUP BY 1, 2;

Im Folgenden sehen Sie die Genauigkeit, die Sie für die Kürzung des Zeitstempels verwenden können:

  • MICROSECOND
  • MILLISECOND
  • SECOND
  • MINUTE
  • HOUR
  • DAY

Allgemeine Informationen zum Kürzen von Zeitstempeln finden Sie unter TIMESTAMP_TRUNC.

Partitionsausrichtung

Bei einer partitionierten Ansicht stellt BigQuery sicher, dass seine Partitionen an den Partitionen der Basistabelle ausgerichtet sind. Beispielsweise wird eine Zeile aus der Partition 2020-01-01 der Basistabelle mit einer Zeile aus der Partition 2020-01-01 der materialisierten Ansicht zusammengefasst.

Durch die Partitionsausrichtung wird gewährleistet, dass eine materialisierte Ansicht effizient verwaltet wird. Beim Aktualisieren werden nur die Partitionen der materialisierten Ansicht aktualisiert, die in den entsprechenden Partitionen der Basistabelle Anhänge, Aktualisierungen oder Löschungen haben. In seltenen Fällen kann das Löschen oder Aktualisieren in der Basistabelle dazu führen, dass die gesamte Ansicht neu berechnet wird. Zum Zeitpunkt der Abfrage wird durch die Partitionierungsausrichtung sichergestellt, dass ein Partitionsfilter sowohl auf den Scan der materialisierten Ansicht als auch den Scan der Basistabelle angewendet wird.

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.

Nachdem eine materialisierte Ansicht auf der Grundlage einer partitionierten Basistabelle erstellt wurde, kann der Partitionsablauf der Tabelle nicht mehr geändert werden. Wenn Sie den Partitionsablauf der Basistabelle ändern möchten, müssen Sie zuerst alle materialisierten Ansichten löschen, die Sie über diese Tabelle erstellt haben.

Materialisierte Ansichten aktualisieren

Sie können angeben, ob BigQuery automatische oder manuelle Aktualisierungen verwenden soll, um vorausberechnete Ergebnisse aus einer Basistabelle zu aktualisieren. Wenn Sie die Standardwerte nicht verwenden möchten, können Sie die Aktualisierungseinstellungen beim Erstellen einer materialisierten Ansicht konfigurieren. Sie können die Aktualisierungseinstellungen auch ändern, nachdem die materialisierte Ansicht bereits erstellt wurde.

Sie können eine materialisierte Ansicht jederzeit manuell aktualisieren.

Automatische Aktualisierung

Standardmäßig werden materialisierte Ansichten automatisch innerhalb von fünf Minuten nach einer Änderung an der Basistabelle aktualisiert, jedoch nicht häufiger als alle 30 Minuten. Beispiele für Änderungen sind das Einfügen oder Löschen von Zeilen.

Die automatische Aktualisierung kann jederzeit aktiviert oder deaktiviert werden.

Wenn Sie die automatische Aktualisierung beim Erstellen einer Tabelle deaktivieren möchten, setzen Sie enable_refresh auf false.

CREATE MATERIALIZED VIEW project-id.my_dataset.my_mv_table
PARTITION BY RANGE_BUCKET(column, buckets)
OPTIONS (enable_refresh = false)
AS SELECT ...

Bei einer vorhandenen materialisierten Ansicht können Sie den Wert enable_refresh mit ALTER MATERIALIZED VIEW ändern.

ALTER MATERIALIZED VIEW project-id.my_dataset.my_mv_table
SET OPTIONS (enable_refresh = true)

Auch wenn die automatische Aktualisierung deaktiviert ist, kann die materialisierte Ansicht jederzeit manuell aktualisiert werden.

Häufigkeitsbegrenzung festlegen

Sie können festlegen, wie häufig die automatische Aktualisierung durchgeführt wird. Standardmäßig werden materialisierte Ansichten nicht häufiger als alle 30 Minuten aktualisiert.

Die Aktualisierungshäufigkeit kann jederzeit geändert werden.

Wenn Sie beim Erstellen einer materialisierten Ansicht eine Häufigkeitsbegrenzung festlegen möchten, setzen Sie refresh_interval_minutes in DDL (oder refresh_interval_ms in der API und im bq-Befehlszeilentool) auf den von Ihnen erstellten Wert.

CREATE MATERIALIZED VIEW project-id.my_dataset.my_mv_table
OPTIONS (enable_refresh = true, refresh_interval_minutes = 60)
AS SELECT ...

Ebenso können Sie die Häufigkeitsbegrenzung beim Ändern einer Tabelle festlegen. In diesem Beispiel wird davon ausgegangen, dass Sie die automatische Aktualisierung bereits aktiviert haben und nur die Häufigkeitsbegrenzung ändern möchten:

ALTER MATERIALIZED VIEW project-id.my_dataset.my_mv_table
SET OPTIONS (refresh_interval_minutes = 60)

Die Mindesthäufigkeit der Aktualisierungen beträgt 1 Minute. Die maximale Häufigkeit für Aktualisierungen beträgt sieben Tage.

Sie können eine materialisierte Ansicht jederzeit manuell aktualisieren. Dies unterliegt nicht der Häufigkeitsbegrenzung.

Best-Effort-Ansatz

Die automatische Aktualisierung erfolgt auf Best-Effort-Basis. BigQuery versucht, innerhalb von fünf Minuten nach einer Änderung an der Basistabelle eine Aktualisierung zu starten (vorausgesetzt, die vorherige Aktualisierung wurde vor über 30 Minuten durchgeführt). Es wird jedoch nicht garantiert, dass die Aktualisierung zu diesem Zeitpunkt gestartet wird oder dass der Vorgang abgeschlossen wird. Das Abfragen von materialisierten Ansichten stellt den letzten Status der Basistabelle dar. Wenn die Ansicht jedoch nicht kürzlich aktualisiert wurde, können die Kosten/Latenz der Abfrage höher als erwartet sein.

Die automatische Aktualisierung wird ähnlich behandelt wie eine Abfrage mit der Batch-Priorität. Wenn das Projekt der materialisierten Ansicht derzeit nicht über die Kapazität verfügt, erfolgt die Aktualisierung verzögert. Wenn das Projekt viele Ansichten enthält, deren Aktualisierung teuer ist, kann jede einzelne Ansicht im Verhältnis zur Basistabelle erheblich verzögert sein.

Manuelle Aktualisierung

Um die Daten in der materialisierten Ansicht zu aktualisieren, rufen Sie nach Bedarf den Systemvorgang BQ.REFRESH_MATERIALIZED_VIEW auf. Beim Aufruf dieses Systemvorgangs identifiziert BigQuery die Änderungen, die in der Basistabelle stattgefunden haben, und wendet diese Änderungen auf die materialisierte Ansicht an. Die Abfrage zum Ausführen von BQ.REFRESH_MATERIALIZED_VIEW wird abgeschlossen, wenn die Aktualisierung abgeschlossen ist.

CALL BQ.REFRESH_MATERIALIZED_VIEW('project-id.my_dataset.my_mv_table')

Tipp