Best Practices für materialisierte Ansichten

Dieses Dokument enthält Best Practices für die Verwendung von BigQuery Materialized Views. Bevor Sie dieses Dokument lesen, sollten Sie sich mit der Einführung in materialisierte Ansichten und Materialisierte Ansichten erstellen und verwenden vertraut machen.

Überlegungen zum Erstellen von materialisierten Ansichten

Achten Sie darauf, dass die Definition der materialisierten Ansicht die Abfragemuster der Basistabelle 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 (where order_date = current_date()) oder einem Datumsbereich (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

Wenn die Basistabelle partitioniert ist und ihre materialisierte Ansicht eine bedeutende Größe aufweist, sollte die materialisierte Ansicht ebenfalls partitioniert werden. Als allgemeine Richtlinie gilt, dass eine materialisierte Ansicht dann eine bedeutende Größe erreicht hat, wenn sie ungefähr dieselbe Größe wie eine Partition der Basistabelle hat oder größer als diese ist.

Überlegungen zu ARRAY_AGG

Wenn Sie Spalten mit ARRAY_AGG aus einer materialisierten Ansicht auswählen, gelten dafür die gleichen Einschränkungen wie bei Verwendung einer ARRAY_AGG-Klausel zur Auswahl aus der Basistabelle. Im Fall von RESPECT NULLS, das standardmäßig festgelegt ist, dürfen Ausgabe-Arrays keine NULL-Elemente enthalten. Wenn ein Ausgabe-Array für ein Element NULL enthält, wird ein Fehler ausgegeben.

Für diese materialisierte Ansicht:

CREATE MATERIALIZED VIEW project-id.my_dataset.my_mv_table AS
SELECT
  column_1, ARRAY_AGG(column_2 ORDER BY column_3 DESC LIMIT 4) column_arr
FROM `project.dataset.base_table`
GROUP BY column_1

Die folgende Abfrage schlägt fehl:

SELECT * FROM project-id.my_dataset.my_mv_table

Die Fehlerdetails sehen so aus:

query: Array cannot have a null element; error in writing field

Fügen Sie IGNORE NULLS zur ARRAY_AGG-Klausel hinzu, um diesen Fehler zu vermeiden:

ARRAY_AGG(col_2 ORDER BY col_3 DESC LIMIT 4 IGNORE NULLS) col

Eine weitere Möglichkeit ist, die Ausgabe-Arrayspalte mit dem Spezifizierer '%t' oder '%T' zu formatieren.

SELECT column_1, FORMAT('%T', column_arr) FROM project-id.my_dataset.my_mv_table

Überlegungen zu JOIN

Obwohl Joins derzeit nicht unterstützt werden, können Sie mithilfe von materialisierten Ansichten möglicherweise die Kosten und die Latenz einer Abfrage reduzieren, die zusätzlich zum Join eine Aggregation durchführt. Angenommen, Sie verknüpfen eine große Faktentabelle mit einigen kleinen Dimensionstabellen und führen dann zusätzlich zum Join eine Aggregation durch. Sie sollten eventuell die Abfrage neu schreiben, um zuerst die Aggregation über der Faktentabelle mit Fremdschlüsseln als Gruppierungsschlüssel durchzuführen, das Ergebnis mit der Dimensionstabelle zusammenzuführen und schließlich eine nachträgliche Aggregation durchzuführen.

Zur Veranschaulichung dieses Ansatzes verwenden wir die Abfrage Nr. 52 aus der TPC-DS-Benchmark.

Dies ist die ursprüngliche Abfrage:

SELECT  dt.d_year,
   item.i_brand_id brand_id,
   item.i_brand brand,
 SUM(ss_ext_sales_price) ext_price
 FROM project.tpcds_10T.date_dim dt,
   project.tpcds_10T.store_sales,
   project.tpcds_10T.item
 WHERE dt.d_date_sk = store_sales.ss_sold_date_sk
    and store_sales.ss_item_sk = item.i_item_sk
    and item.i_manager_id = 1
    and dt.d_moy=12
    and dt.d_year=1998
 GROUP BY dt.d_year,
   item.i_brand,
   item.i_brand_id
 ORDER BY dt.d_year,
   ext_price DESC,
   brand_id
LIMIT 100

Dies ist die alternative Abfrage:

WITH sales_summary AS (
  SELECT ss_sold_date_sk,
    ss_item_sk,
    SUM(ss_ext_sales_price) AS ext_price
  FROM project.tpcds_10T.store_sales
  GROUP BY ss_sold_date_sk, ss_item_sk
)
SELECT dt.d_year,
  item.i_brand_id brand_id,
  item.i_brand brand,
  SUM(ext_price) AS ext_price
FROM sales_summary,
  project.tpcds_10T.date_dim dt,
  project.tpcds_10T.item
WHERE sales_summary.ss_sold_date_sk = dt.d_date_sk
  AND sales_summary.ss_item_sk = item.i_item_sk
    AND item.i_manager_id = 1
    AND dt.d_moy=12
    AND dt.d_year=1998
GROUP BY dt.d_year,
  item.i_brand,
  item.i_brand_id
ORDER BY dt.d_year,
  ext_price desc,
  brand_id
LIMIT 100

So erstellen Sie die Ansicht:

CREATE OR REPLACE MATERIALIZED VIEW project-id.tpcds_10T.sales_summary_mv AS
  SELECT
    ss_sold_date_sk,
    ss_item_sk,
    SUM(ss_ext_sales_price) AS ext_price
  FROM project-id.tpcds_10T.store_sales
  GROUP BY ss_sold_date_sk, ss_item_sk

Als Nächstes können Sie die materialisierte Ansicht so mit den Dimensionstabellen verknüpfen:

SELECT dt.d_year,
  item.i_brand_id brand_id,
  item.i_brand brand,
  SUM(ext_price) as ext_price
FROM sales_summary_mv,
  project.tpcds_10T.date_dim dt,
  project.tpcds_10T.item
WHERE sales_summary_mv.ss_sold_date_sk = dt.d_date_sk
  AND sales_summary_mv.ss_item_sk = item.i_item_sk
    AND item.i_manager_id = 1
    AND dt.d_moy=12
    AND dt.d_year=1998
GROUP BY dt.d_year,
  item.i_brand,
  item.i_brand_id
ORDER BY dt.d_year,
  ext_price desc,
  brand_id
LIMIT 100

Überlegungen zur Wartung von materialisierten Ansichten

Überwachen Sie die Kosten des Aktualisierungsjobs und passen Sie bei Bedarf das automatische Aktualisierungsintervall an. Überwachen Sie insbesondere total_bytes_processed und total_slot_ms.

So ist es beispielsweise sinnvoll, die Ansicht seltener zu aktualisieren, wenn die Aufnahmerate in der Basistabelle relativ klein ist. Wenn sich die zugrunde liegenden Daten dagegen schnell ändern, ist eine häufigere Aktualisierung sinnvoll.

Wenn die Basistabelle Daten zu vordefinierten Zeitpunkten aufnimmt, z. B. durch eine nächtliche Extrahierungs-, Transformations- und Lade-Pipeline (ETL), sollten Sie den Wartungsplan für die materialisierte Ansicht vollständig manuell ausführen. Gehen Sie dazu so vor:

  1. Automatische Aktualisierung deaktivieren.

  2. Führen Sie eine manuelle Aktualisierung durch, entweder als Teil der ETL-Pipeline oder durch Festlegen einer geplanten Abfrage zu bestimmten Tageszeiten.