Tabellen erstellen

In Dataform ist eine Tabelle einer der Objekttypen, aus denen ein Workflow besteht. Sie können Tabellen erstellen, die auf Daten aus den Datenquellen verweisen, die für Ihren Workflow deklariert wurden, oder aus anderen Tabellen in Ihrem Workflow. Dataform kompiliert Ihre Tabellendefinitionen in Echtzeit in SQL. Wenn Sie die Ausführung auslösen, führt Dataform den SQL-Code aus und erstellt die von Ihnen definierten Tabellen in BigQuery.

Sie können die folgenden Tabellentypen in einer type: "table"-SQLX-Datei erstellen:

Sie können auch Tabellenpartitionen und ‑cluster definieren.

Wenn Sie den Zweck einer Tabelle oder ihre Beziehung zu anderen Tabellen in Ihrem Workflow dokumentieren möchten, können Sie der Tabelle oder den ausgewählten Spalten Dokumentation hinzufügen.

Wenn Sie die Daten in einer Tabelle anhand bestimmter Bedingungen testen möchten, können Sie Datenqualitäts-Testabfragen erstellen, die als Assertions bezeichnet werden. Dataform führt Assertionen jedes Mal aus, wenn der Workflow aktualisiert wird, und benachrichtigt Sie, wenn eine Assertion fehlschlägt.

Wenn Sie das Standardschema, die Standarddatenbank und den Standardnamen einer ausgewählten Tabelle überschreiben möchten, können Sie die Tabelleneinstellungen überschreiben.

Wenn Sie die Tabellenerstellung deaktivieren oder eine SQL-Anweisung vor oder nach der Tabellenerstellung ausführen möchten, können Sie zusätzliche Aktionen konfigurieren.

Nachdem Sie Ihre Tabellen in BigQuery ausgeführt haben, können Sie sie mit BigQuery-Labels organisieren. Weitere Informationen finden Sie unter Einführung in Labels.

Wenn Sie den Datenzugriff auf Tabellenspaltenebene einschränken möchten, können Sie BigQuery-Richtlinien-Tags hinzufügen. Weitere Informationen finden Sie unter Einführung in die Zugriffssteuerung auf Spaltenebene.

Neben der Definition von Tabellen in einer type: "table"-SQLX-Datei können Sie leere Tabellen erstellen, indem Sie eine benutzerdefinierte SQL-Abfrage in einer type: "operations"-SQLX-Datei definieren. Möglicherweise möchten Sie eine leere Tabelle erstellen, damit ein anderer Dienst sie mit Daten füllen kann.

Hinweise

  1. Rufen Sie in der Google Cloud Console die Seite Dataform auf.

    Zu Dataform

  2. Entwicklungsarbeitsbereich in Ihrem Repository erstellen und initialisieren

  3. Optional: Datenquelle deklarieren.

Erforderliche Rollen

Bitten Sie Ihren Administrator, Ihnen die IAM-Rolle Dataform Editor (roles/dataform.editor) für Arbeitsbereiche zuzuweisen, damit Sie die Berechtigungen erhalten, die Sie zum Ausführen der Aufgaben in diesem Dokument benötigen. Weitere Informationen zum Zuweisen von Rollen finden Sie unter Zugriff auf Projekte, Ordner und Organisationen verwalten.

Sie können die erforderlichen Berechtigungen auch über benutzerdefinierte Rollen oder andere vordefinierte Rollen erhalten.

Tabelle erstellen

In diesem Abschnitt erfahren Sie, wie Sie mit Dataform Core Tabellen in Dataform erstellen.

Tabellendefinitionen

Um eine Tabelle zu definieren, definieren Sie den Tabellentyp und schreiben eine SELECT-Anweisung in eine type: "table"-SQLX-Datei. Dataform kompiliert dann Ihren Dataform Core-Code in SQL, führt den SQL-Code aus und erstellt die von Ihnen definierten Tabellen in BigQuery.

In einer Dataform Core-SELECT-Anweisung definieren Sie die Tabellenstruktur und verweisen auf andere Objekte Ihres Workflows.

Neben der Definition von Tabellen in einer type: "table"-SLQX-Datei können Sie auch leere Tabellen erstellen, indem Sie eine benutzerdefinierte SQL-Abfrage in einer type: "operations"-SQLX-Datei definieren. Weitere Informationen finden Sie unter Leere Tabelle erstellen.

Abhängigkeiten mit ref referenzieren

Wenn Sie in einer SELECT-Anweisung auf eine Workflow-Aktion verweisen und sie automatisch als Abhängigkeit hinzufügen möchten, verwenden Sie die Funktion ref. Dataform führt Abhängigkeiten vor Tabellen aus, die von ihnen abhängen, um die richtige Reihenfolge der Pipeline zu überprüfen.

Die Funktion ref ist eine integrierte Dataform Core-Funktion, die für die Abhängigkeitsverwaltung in Dataform unerlässlich ist. Mit der Funktion ref können Sie auf die folgenden Objekte verweisen, die in Ihrem Dataform-Workflow definiert sind, und automatisch von ihnen abhängig sein, anstatt Schema- und Tabellennamen fest zu codieren:

Dataform verwendet die Funktion ref, um einen Abhängigkeitsbaum aller Tabellen zu erstellen, die erstellt oder aktualisiert werden sollen.

Nach dem Kompilieren fügt Dataform der SQL-Anweisung Boilerplate-Anweisungen wie CREATE, REPLACE, INSERT oder MERGE hinzu.

Das folgende Codebeispiel zeigt eine Tabellendefinition mit der Funktion ref:

config { type: "table" }

SELECT
  order_date AS date,
  order_id AS order_id,
  order_status AS order_status,
  SUM(item_count) AS item_count,
  SUM(amount) AS revenue

FROM ${ref("store_clean")}

GROUP BY 1, 2

In der ref-Funktion geben Sie den Namen der Tabellen- oder Datenquellendeklaration an, von der Sie abhängig sein möchten. Dies ist in der Regel der Dateiname der SQLX-Datei, in der die Deklaration der Tabelle oder Datenquelle definiert ist.

Wenn ein Tabellenname überschrieben wird, verwenden Sie den überschriebenen Namen in der Funktion ref. Verweisen Sie beispielsweise auf eine Tabelle mit config { name: "overridden_name" } als ref("overridden_name"). Weitere Informationen finden Sie unter Tabelleneinstellungen überschreiben und Auf eine Tabelle mit einem überschriebenen Tabellennamen verweisen.

Wenn Sie mehrere Tabellen mit demselben Namen in verschiedenen Schemas haben, können Sie auf eine bestimmte Tabelle verweisen, indem Sie der Funktion ref zwei Argumente übergeben: den Schemanamen und den Tabellennamen.

Das folgende Codebeispiel zeigt die Funktion ref mit zwei Argumenten, um eine Tabelle in einem bestimmten Schema anzugeben:

config { type: "table" }
SELECT * FROM ${ref("schema", "store_clean")}

Sie können dem config-Block auch manuell Tabellenabhängigkeiten für Tabellen, Zusicherungen, Datenquellendeklarationen oder benutzerdefinierte SQL-Vorgänge hinzufügen, auf die in einer ref-Funktion in der SELECT-Anweisung nicht verwiesen wird. Dataform führt diese Abhängigkeiten vor abhängigen Tabellen aus.

Das folgende Codebeispiel zeigt eine Tabellenabhängigkeit im config-Block:

config { dependencies: [ "unreferenced_table" ] }
SELECT * FROM ...

Weitere Informationen zur Abhängigkeitsverwaltung in Ihrem Workflow finden Sie unter Abhängigkeiten festlegen.

Mit resolve auf andere Tabellen verweisen

Mit der resolve-Funktion können Sie in einer SELECT-Anweisung wie mit der ref-Funktion auf eine Tabellen- oder Datenquellendeklaration verweisen, ohne die Referenz als Abhängigkeit hinzuzufügen. Das bedeutet, dass sich das mit der Funktion resolve referenzierte Objekt nicht auf die Ausführung der Tabelle auswirkt, in der die Funktion resolve verwendet wird.

Weitere Informationen zu integrierten Dataform Core-Funktionen finden Sie in der Dataform Core-Referenz.

SQLX-Datei für eine Tabellendefinition erstellen

Speichern Sie SQLX-Dateien für Tabellendefinitionen im Verzeichnis definitions/. So erstellen Sie eine neue SQLX-Datei im Verzeichnis definitions/:

  1. Rufen Sie in der Google Cloud Console die Seite Dataform auf.

    Zu Dataform

  2. Klicken Sie auf den Namen eines Repositorys, um es zu öffnen.

  3. Klicken Sie auf den Namen des Arbeitsbereichs, um ihn zu öffnen.

  4. Klicken Sie im Bereich Dateien neben definitions/ auf Mehr.

  5. Klicken Sie auf Datei erstellen.

  6. Geben Sie im Feld Dateipfad hinzufügen den Namen der Datei gefolgt von .sqlx nach definitions/ ein. Beispiel: definitions/my-table.sqlx

    Dateinamen dürfen nur Zahlen, Buchstaben, Bindestriche und Unterstriche enthalten.

  7. Klicken Sie auf Datei erstellen.

Tabellentyp definieren

So erstellen Sie eine neue Tabellentypdefinition:

  1. Maximieren Sie in Ihrem Entwicklungsarbeitsbereich im Bereich Dateien das Verzeichnis definitions/.
  2. Wählen Sie die SQLX-Datei mit der Tabellendefinition aus, die Sie bearbeiten möchten.

  3. Geben Sie in die Datei das folgende Code-Snippet ein:

    config { type: "TABLE_TYPE" }

    Ersetzen Sie TABLE_TYPE durch einen der folgenden Tabellentypen:

    • table
    • incremental
    • view

  4. Optional: Wenn Sie eine materialisierte Ansicht definieren möchten, geben Sie die Eigenschaft materialized unter type: "view" im folgenden Format ein:

    config {
  type: "view",
  materialized: true
}

    Weitere Informationen finden Sie unter ITableConfig.

  5. Optional: Klicken Sie auf Formatieren.

Tabellenstruktur und ‑abhängigkeiten definieren

So schreiben Sie eine SELECT-Tabellendefinitionsanweisung und definieren die Tabellenstruktur und ‑abhängigkeiten:

  1. Maximieren Sie in Ihrem Entwicklungsarbeitsbereich im Bereich Dateien das Verzeichnis definitions/.
  2. Wählen Sie die SQLX-Datei mit der Tabellendefinition aus, die Sie bearbeiten möchten.
  3. Schreiben Sie unter dem config-Block eine SELECT-Anweisung.
  4. Optional: Klicken Sie auf Formatieren.

Das folgende Codebeispiel zeigt eine Tabellendefinition mit einer SELECT-Anweisung und der Funktion ref:

config { type: "table" }
SELECT
  customers.id AS id,
  customers.first_name AS first_name,
  customers.last_name AS last_name,
  customers.email AS email,
  customers.country AS country,
  COUNT(orders.id) AS order_count,
  SUM(orders.amount) AS total_spent
FROM
  dataform-samples.dataform_sample.crm_customers AS customers
  LEFT JOIN ${ref('order_stats')} orders
    ON customers.id = orders.customer_id

WHERE
  customers.id IS NOT NULL
  AND customers.first_name <> 'Internal account'
  AND country IN ('UK', 'US', 'FR', 'ES', 'NG', 'JP')

GROUP BY 1, 2, 3, 4, 5

Manuelle Tabellenabhängigkeiten hinzufügen

So fügen Sie Tabellenabhängigkeiten hinzu, auf die in der SELECT-Anweisung nicht verwiesen wird, die aber vor der aktuellen Tabelle ausgeführt werden müssen:

  1. Maximieren Sie in Ihrem Entwicklungsarbeitsbereich im Bereich Dateien das Verzeichnis definitions/.
  2. Wählen Sie die SQLX-Datei mit der Tabellendefinition aus, die Sie bearbeiten möchten.

  3. Geben Sie im config-Block der Tabelle das folgende Code-Snippet ein:

    dependencies: [ "DEPENDENCY_TABLE", ]

    Ersetzen Sie DEPENDENCY_TABLE durch den Dateinamen der Tabelle, die Sie als Abhängigkeit hinzufügen möchten. Sie können mehrere Dateinamen eingeben.

  4. Optional: Klicken Sie auf Formatieren.

Im folgenden Codebeispiel werden zwei Tabellen als manuelle Tabellenabhängigkeiten dem config-Block einer Tabellendefinitionsdatei hinzugefügt:

config { dependencies: [ "some_table", "some_other_table" ] }

Tabelleneinstellungen überschreiben

Sie können das Standardschema, die Standarddatenbank und den Standardnamen einer ausgewählten Tabelle überschreiben.

Standardmäßig entspricht eine Tabelle dem Schema und der Datenbankkonfiguration, die Sie in workflow_settings.yaml festgelegt haben. Der Name einer Tabelle entspricht dem Namen der SQLX-Datei mit der Tabellendefinition.

So überschreiben Sie das Schema und den Namen einer ausgewählten Tabelle:

  1. Rufen Sie Ihren Entwicklungsarbeitsbereich auf.

  2. Maximieren Sie im Bereich Dateien die Option definitions/.

  3. Öffnen Sie eine SQLX-Tabellendefinitionsdatei.

  4. Geben Sie im Block config das folgende Code-Snippet ein:

     {
   schema: "OVERRIDDEN_SCHEMA",
   database: "OVERRIDDEN_DATABASE",
   name: "OVERRIDDEN_NAME"
 }

    Ersetzen Sie Folgendes:

    • OVERRIDDEN_SCHEMA: Das BigQuery-Dataset, in dem Sie die Tabelle erstellen möchten.

    • OVERRIDDEN_DATABASE: die ID des BigQuery-Projekts, in dem Sie die Tabelle erstellen möchten.

    • OVERRIDDEN_NAME: Der Name der Tabelle, der sich vom Dateinamen der SQLX-Tabellendefinition unterscheidet.

  5. Optional: Klicken Sie auf Formatieren.

Weitere Informationen finden Sie unter Auf eine Tabelle mit einem überschriebenen Tabellennamen verweisen.

Tabellenpartitionen und ‑cluster erstellen

In diesem Abschnitt erfahren Sie, wie Sie mit Dataform Core Tabellenpartitionen und ‑cluster erstellen. BigQuery unterstützt partitionierte Tabellen und das Clustering von Tabellen. Weitere Informationen finden Sie unter Einführung in partitionierte Tabellen und Geclusterte Tabellen erstellen und verwenden.

Tabellenpartition erstellen

So erstellen Sie eine Tabellenpartition:

  1. Rufen Sie Ihren Entwicklungsarbeitsbereich auf.
  2. Maximieren Sie im Bereich Dateien die Option definitions/.
  3. Öffnen Sie eine SQLX-Datei mit einer Tabellendefinition.

  4. Fügen Sie im Block config den Block bigquery unter der Deklaration des Tabellentyps im folgenden Format hinzu:

    config {
  type: "table",
  bigquery: {
  }
}

  5. Geben Sie im Block bigquery das folgende Code-Snippet ein:

        partitionBy: "PARTITION_EXPRESSION"

    Ersetzen Sie PARTITION_EXPRESSION durch einen Ausdruck zum Partitionieren der Tabelle.

  6. Optional: Klicken Sie auf Formatieren.

Im folgenden Codebeispiel wird gezeigt, wie eine Tabelle in einer SQLX-Datei mit Tabellendefinition nach Stunde partitioniert wird:

config {
  type: "table",
  bigquery: {
    partitionBy: "DATETIME_TRUNC(<timestamp_column>, HOUR)"
  }
}

Im folgenden Codebeispiel wird gezeigt, wie eine Tabelle in einer SQLX-Datei für die Tabellendefinition nach einem Ganzzahlwert partitioniert wird:

config {
  type: "table",
  bigquery: {
    partitionBy: "RANGE_BUCKET(<integer_column>, GENERATE_ARRAY(0, 1000000, 1000))"
  }
}

Partitionsfilter festlegen

So legen Sie einen Partitionsfilter fest:

  1. Rufen Sie Ihren Entwicklungsarbeitsbereich auf.
  2. Maximieren Sie im Bereich Dateien die Option definitions/.
  3. Öffnen Sie eine SQLX-Datei mit der Definition einer partitionierten Tabelle.

  4. Geben Sie im Block bigquery das folgende Code-Snippet ein:

    requirePartitionFilter : true

  5. Optional: Klicken Sie auf Formatieren.

Das folgende Codebeispiel zeigt einen Partitionsfilter, der im bigquery-Block einer SQLX-Datei für eine partitionierte Tabelle festgelegt ist:

config {
  type: "table",
  bigquery: {
    partitionBy: "DATE(ts)",
    requirePartitionFilter : true
  }
}
SELECT CURRENT_TIMESTAMP() AS ts

Weitere Informationen zum Partitionsfilter in BigQuery finden Sie unter Erforderliches Partitionsfilterattribut für partitionierte Tabelle festlegen.

Aufbewahrungsdauer für Partitionen festlegen

So legen Sie die Aufbewahrungsdauer für alle Partitionen in einer partitionierten Tabelle fest:

  1. Rufen Sie Ihren Entwicklungsarbeitsbereich auf.
  2. Maximieren Sie im Bereich Dateien die Option definitions/.
  3. Öffnen Sie eine SQLX-Datei mit der Definition einer partitionierten Tabelle.

  4. Geben Sie im Block bigquery das folgende Code-Snippet ein:

    partitionExpirationDays: NUMBER_OF_DAYS

    Ersetzen Sie NUMBER_OF_DAYS durch die Anzahl der Tage, für die Sie die Partitionen beibehalten möchten.

  5. Optional: Klicken Sie auf Formatieren.

Im folgenden Codebeispiel wird ein Aufbewahrungszeitraum für Partitionen gezeigt, der im bigquery-Block einer SQLX-Datei für eine partitionierte Tabelle auf 14 Tage festgelegt ist:

config {
  type: "table",
  bigquery: {
    partitionBy: "DATE(ts)",
    partitionExpirationDays: 14,
  }
}
SELECT CURRENT_TIMESTAMP() AS ts

Tabellencluster erstellen

So erstellen Sie einen Tabellencluster:

  1. Rufen Sie Ihren Entwicklungsarbeitsbereich auf.
  2. Maximieren Sie im Bereich Dateien die Option definitions/.
  3. Öffnen Sie eine SQLX-Datei mit einer Tabellendefinition.

  4. Geben Sie im Block bigquery das folgende Code-Snippet ein:

        clusterBy: ["CLUSTER_COLUMN"]

    Ersetzen Sie CLUSTER_COLUMN durch den Namen der Spalte, nach der Sie die Tabelle gruppieren möchten. Weitere Informationen finden Sie unter clustering_column_list.

  5. Optional: Klicken Sie auf Formatieren.

Das folgende Codebeispiel zeigt eine partitionierte Tabelle, die nach den Spalten name und revenue geclustert ist:

config {
  type: "table",
  bigquery: {
    partitionBy: "DATE(ts)",
    clusterBy: ["name", "revenue"]
  }
}
SELECT CURRENT_TIMESTAMP() as ts, name, revenue

Inkrementelle Tabelle konfigurieren

In diesem Abschnitt erfahren Sie, wie Sie mit Dataform Core eine inkrementelle Tabelle konfigurieren.

Inkrementelle Tabellen

Dataform aktualisiert Tabellen je nach Tabellentyp unterschiedlich. Bei jeder Ausführung einer Tabelle oder Ansicht wird die gesamte Tabelle oder Ansicht von Dataform neu erstellt.

Wenn Sie eine inkrementelle Tabelle definieren, wird sie von Dataform nur beim ersten Mal von Grund auf neu erstellt. Bei nachfolgenden Ausführungen fügt Dataform nur neue Zeilen in die inkrementelle Tabelle ein oder führt sie zusammen, je nach den von Ihnen konfigurierten Bedingungen.

Dataform fügt neue Zeilen nur in Spalten ein, die bereits in der inkrementellen Tabelle vorhanden sind. Wenn Sie Änderungen am Query für die Definition der inkrementellen Tabelle vornehmen, z. B. eine neue Spalte hinzufügen, müssen Sie die Tabelle von Grund auf neu erstellen. Wählen Sie dazu beim nächsten Auslösen der Tabellenausführung die Option Mit vollständiger Aktualisierung ausführen aus.

Hier einige gängige Anwendungsfälle für inkrementelle Tabellen:

Leistungsoptimierung
Bei bestimmten Datentypen wie Webprotokollen oder Analysedaten möchten Sie möglicherweise nur neue Datensätze verarbeiten, anstatt die gesamte Tabelle neu zu verarbeiten.
Reduzierung der Latenz
Mit inkrementellen Tabellen können Sie Workflows schnell, aber häufig ausführen und so die Downstream-Latenz der Ausgabetabellen verringern.
Tägliche Snapshots
Sie können eine inkrementelle Tabelle so konfigurieren, dass täglich Snapshots der Tabellendaten erstellt werden, z. B. für die Längsschnittanalyse von Nutzereinstellungen, die in einer Produktionsdatenbank gespeichert sind.

Teilmengen von Zeilen in einer inkrementellen Tabelle verarbeiten

Wenn Sie eine Teilmenge von Zeilen festlegen möchten, die bei jeder Ausführung von Dataform verarbeitet werden sollen, fügen Sie der SQLX-Datei mit der Definition der inkrementellen Tabelle eine bedingte WHERE-Klausel hinzu. In der WHERE-Klausel können Sie eine inkrementelle und eine nicht inkrementelle Bedingung angeben. Dataform wendet die inkrementelle Bedingung während der Tabellenausführung ohne vollständige Aktualisierung und die nicht inkrementelle Bedingung während der Ausführung mit vollständiger Aktualisierung an.

So konfigurieren Sie eine inkrementelle Tabelle:

  1. Rufen Sie Ihren Entwicklungsarbeitsbereich auf.
  2. Maximieren Sie im Bereich Dateien die Option definitions/.
  3. Öffnen Sie eine SQLX-Datei mit der Definition einer inkrementellen Tabelle.

  4. Geben Sie eine WHERE-Klausel im folgenden Format ein:

    config { type: "incremental" }

SELECT_STATEMENT

${when(incremental(), `WHERE INCREMENTAL_CONDITION`, `WHERE NON_INCREMENTAL_CONDITION`) }

    Ersetzen Sie Folgendes:

    • SELECT_STATEMENT: die SELECT-Anweisung, mit der Ihre Tabelle definiert wird.
    • INCREMENTAL_CONDITION: Die Bedingung, die Sie in der WHERE-Klausel angeben, um Zeilen für die Verarbeitung durch Dataform während der Tabellenausführung ohne vollständige Aktualisierung auszuwählen.
    • NON_INCREMENTAL_CONDITION: Die Bedingung, die Sie in der WHERE-Klausel angeben, um Zeilen für Dataform auszuwählen, die während der Tabellenausführung mit einer vollständigen Aktualisierung verarbeitet werden sollen.

  5. Optional: Klicken Sie auf Formatieren.

Das folgende Codebeispiel zeigt eine inkrementelle Tabelle, in der Zeilen der Tabelle productiondb.logs inkrementell verarbeitet werden:

config { type: "incremental" }

SELECT timestamp, message FROM ${ref("productiondb", "logs")}

${when(incremental(),
   `WHERE date > (SELECT MAX(date) FROM ${self()}) AND country = "UK"`,
   `WHERE country = "UK"`)}

Im folgenden Codebeispiel wird eine inkrementelle Tabelle gezeigt, mit der ein Snapshot der Tabelle productiondb.customers erstellt wird:

config { type: "incremental" }

SELECT CURRENT_DATE() AS snapshot_date, customer_id, name, account_settings FROM ${ref("productiondb", "customers")}

${when(incremental(), `WHERE snapshot_date > (SELECT MAX(snapshot_date) FROM ${self()})`) }

Zeilen in einer inkrementellen Tabelle zusammenführen

Wenn Sie prüfen möchten, ob eine inkrementelle Tabelle nur eine Zeile für eine ausgewählte Kombination von Spalten enthält, legen Sie die ausgewählten Spalten als uniqueKey fest, um Zeilen mit demselben uniqueKey-Wert zusammenzuführen. Beim Aktualisieren der Tabelle werden Zeilen mit demselben uniqueKey-Wert zusammengeführt, anstatt angehängt.

So konfigurieren Sie das Zusammenführen in einer inkrementellen Tabelle:

  1. Rufen Sie Ihren Entwicklungsarbeitsbereich auf.
  2. Maximieren Sie im Bereich Dateien die Option definitions/.
  3. SQLX-Datei für die Definition einer inkrementellen Tabelle auswählen

  4. Legen Sie im Block config die ausgewählten Spalten im folgenden Format als uniqueKey fest:

    uniqueKey: ["COLUMN_NAME"]

    Ersetzen Sie COLUMN_NAME durch den Namen einer ausgewählten Spalte.

  5. Optional: Klicken Sie auf Formatieren.

Das folgende Codebeispiel zeigt eine inkrementelle Tabelle, in der die Spalte transaction_id auf uniqueKey festgelegt ist, um zu prüfen, ob sie immer eine Zeile enthält:

config {
  type: "incremental",
  uniqueKey: ["transaction_id"]
}

SELECT timestamp, action FROM weblogs.user_actions
${ when(incremental(), `WHERE timestamp > (SELECT MAX(timestamp) FROM ${self()})`) }

Zeilen in einer inkrementellen Tabelle filtern

Um zu vermeiden, dass Dataform in einer inkrementell partitionierten Tabelle die gesamte Tabelle nach übereinstimmenden Zeilen durchsucht, legen Sie updatePartitionFilter so fest, dass nur eine Teilmenge der Datensätze berücksichtigt wird.

Im folgenden Codebeispiel wird eine inkrementell partitionierte Tabelle mit Zusammenführung konfiguriert, indem die Attribute uniqueKey und updatePartitionFilter festgelegt werden:

config {
  type: "incremental",
  uniqueKey: ["transaction_id"],
  bigquery: {
    partitionBy: "DATE(timestamp)",
    updatePartitionFilter:
        "timestamp >= timestamp_sub(current_timestamp(), interval 24 hour)"
  }
}

SELECT timestamp, action FROM weblogs.user_actions
${ when(incremental(), `WHERE timestamp > (SELECT MAX(timestamp) FROM ${self()})`) }

Vollständige Tabellenscans beim Importieren aus einer partitionierten Tabelle vermeiden

Wenn Sie eine inkrementelle Tabelle erstellen, die auf eine partitionierte Tabelle verweist, empfehlen wir, die Tabellenabfrage so zu erstellen, dass bei jeder inkrementellen Aktualisierung keine vollständigen Tabellenscans der partitionierten Tabelle durchgeführt werden.

Sie können die Anzahl der Partitionen begrenzen, die von BigQuery gescannt werden, um die inkrementelle Tabelle zu aktualisieren. Verwenden Sie dazu einen konstanten Ausdruck in Ihrer Tabellenabfrage. Wenn Sie einen Wert aus der partitionierten Tabelle in einen konstanten Ausdruck umwandeln möchten, deklarieren Sie den Wert mit BigQuery-Scripting als Variable im pre_operations-Block. Verwenden Sie die Variable dann als konstanten Ausdruck in einer WHERE-Klausel in der SELECT-Abfrage.

Bei dieser Konfiguration aktualisiert Dataform die inkrementelle Tabelle basierend auf den neuesten Partitionen der referenzierten partitionierten Tabelle, ohne die gesamte Tabelle zu scannen.

So konfigurieren Sie eine inkrementelle Tabelle, die auf eine partitionierte Tabelle verweist und vollständige Tabellenscans vermeidet:

  1. Rufen Sie Ihren Entwicklungsarbeitsbereich auf.
  2. Maximieren Sie im Bereich Dateien die Option definitions/.
  3. SQLX-Datei für die Definition einer inkrementellen Tabelle auswählen
  4. Deklarieren Sie im pre_operations-Block eine Variable mit BigQuery-Scripting.
  5. Filtern Sie die SELECT-Anweisung, mit der die Tabelle definiert wird, mit einer WHERE-Klausel, die auf die deklarierte Variable verweist.
  6. Optional: Klicken Sie auf Formatieren.

Das folgende Codebeispiel zeigt eine inkrementelle Tabelle, in der die referenzierte Tabelle raw_events nach event_timestamp partitioniert ist:

config {
  type: "incremental",
}

pre_operations {
  DECLARE event_timestamp_checkpoint DEFAULT (
    ${when(incremental(),
    `SELECT max(event_timestamp) FROM ${self()}`,
    `SELECT timestamp("2000-01-01")`)}
  )
}

SELECT
  *
FROM
  ${ref("raw_events")}
WHERE event_timestamp > event_timestamp_checkpoint

Im vorherigen Codebeispiel wird die Variable event_timestamp_checkpoint im Block pre_operations definiert. Die Variable event_timestamp_checkpoint wird dann als konstanter Ausdruck in der WHERE-Klausel verwendet.

Inkrementelle Tabelle mit vollständiger Aktualisierung neu erstellen

Sie können erzwingen, dass eine inkrementelle Tabelle von Grund auf neu erstellt wird. Verwenden Sie dazu entweder die Befehlszeile mit der Option --full-refresh oder die Option Mit vollständiger Aktualisierung ausführen, wenn Sie die Ausführung eines Workflows auslösen.

Wenn Sie die Option für die vollständige Aktualisierung in Ihrem Entwicklungsarbeitsbereich oder mit der Dataform CLI auswählen, ignoriert Dataform den Parameter ${when(incremental(), ... } während der Ausführung und erstellt die Tabelle mit einer CREATE OR REPLACE-Anweisung neu.

Inkrementelle Tabelle vor vollständiger Aktualisierung schützen

Um zu verhindern, dass eine inkrementelle Tabelle von Grund auf neu erstellt wird und es zu einem möglichen Datenverlust kommt, können Sie die inkrementelle Tabelle als protected festlegen. Sie können verhindern, dass eine inkrementelle Tabelle neu erstellt wird, wenn Ihre Datenquelle temporär ist.

So markieren Sie eine inkrementelle Tabelle als protected:

  1. Rufen Sie Ihren Entwicklungsarbeitsbereich auf.
  2. Maximieren Sie im Bereich Dateien die Option definitions/.
  3. Wählen Sie eine SQLX-Datei für die Definition einer inkrementellen Tabelle aus.
  4. Geben Sie im Block config den Wert protected: true ein.
  5. Optional: Klicken Sie auf Formatieren.

Das folgende Codebeispiel zeigt eine inkrementelle Tabelle, die als protected gekennzeichnet ist:

config {
  type: "incremental",
  protected: true
}
SELECT ...

Tabellendokumentation hinzufügen

In diesem Abschnitt erfahren Sie, wie Sie einer Dataform Core-SQLX-Datei Beschreibungen für eine Tabelle, ihre Spalten und Datensätze hinzufügen.

Sie können allen Tabellentypen in Dataform Tabellen-, Spalten- und Datensatzbeschreibungen hinzufügen: Tabellen, inkrementellen Tabellen und Ansichten.

Dokumentieren Sie Folgendes:

  • Der Zweck der Tabelle.
  • Der Inhalt oder die Rolle von Spalten oder Datensätzen in der Tabelle.
  • Die Beziehung der Tabelle zu anderen Aktionen in Ihrem Workflow, z. B. die Tabellen oder Ansichten, die von der aktuellen Tabelle abhängen.
  • Die auf die Tabelle angewendeten Behauptungen.
  • Die Vor- oder Nachbearbeitungen, die auf die Tabelle angewendet werden.
  • Der Inhaber der Tabelle, also der Nutzer, der sie erstellt hat. Diese Informationen können hilfreich sein, wenn mehrere Teammitglieder an einem Workflow arbeiten.

Tabellenbeschreibung hinzufügen

So fügen Sie einer Tabelle in einer SQLX-Datei eine Beschreibung hinzu:

  1. Rufen Sie in der Google Cloud Console die Seite Dataform auf.

    Zu Dataform

  2. Wählen Sie ein Repository aus.

  3. Wählen Sie einen Entwicklungsarbeitsbereich aus.

  4. Klicken Sie im Bereich Dateien auf die SQLX-Datei mit der Tabellendefinition, die Sie bearbeiten möchten.

  5. Geben Sie im config-Block der Datei die Tabellenbeschreibung im folgenden Format ein:

    description: "Description of the table",

  6. Optional: Klicken Sie auf Formatieren.

Im folgenden Codebeispiel wird eine Tabellenbeschreibung dem config-Block einer SQLX-Tabellendefinitionsdatei hinzugefügt:

config {
  type: "table",
  description: "Description of the table",
 }

Spalten- und Datensatzbeschreibungen hinzufügen

So fügen Sie einer SQLX-Datei Beschreibungen für einzelne Spalten und Datensätze hinzu:

  1. Geben Sie im Block config der Tabellendefinitionsdatei columns: {} ein.

  2. Geben Sie innerhalb von columns: {} Spaltenbeschreibungen im folgenden Format ein:

    column_name: "Description of the column",

  3. Geben Sie in columns: {} Datensatzbeschreibungen im folgenden Format ein:

      record_name: {
      description: "Description of the record",
      columns: {
        record_column_name: "Description of the record column"
      }
}

  4. Optional: Klicken Sie auf Formatieren.

Im folgenden Codebeispiel sehen Sie Tabellen-, Spalten- und Datensatzbeschreibungen im config-Block einer SQLX-Tabellendefinitionsdatei:

config {
  type: "table",
  description: "Description of the table.",
  columns: {
    column1_name: "Description of the first column",
    column2_name: "Description of the second column",
    column3_name: "Description of the third column",
    record_name: {
      description: "Description of the record.",
      columns: {
       record_column1_name: "Description of the first record column",
       record_column2_name: "Description of the second record column",
      }
    }
  }
}
SELECT
  "first_column_value" AS column_1_name,
  "second_column_value" AS column_2_name,
  "third_column_value" AS column_3_name,
  STRUCT("first" AS record_column1_name,
    "second" AS record_column2_name) AS record_name

Spaltendokumentation mit Includes wiederverwenden

Sie können Beschreibungen von Spalten in Dataform mit JavaScript-Includes in Ihrem SQL-Workflow wiederverwenden. Möglicherweise möchten Sie die Spaltendokumentation wiederverwenden, wenn Sie in Ihrem SQL-Workflow mehrere Spalten mit demselben Namen und derselben Beschreibung haben.

Sie können eine Konstante mit einer Beschreibung einer einzelnen Spalte oder eine Konstante mit einer Set- oder Spaltenbeschreibung definieren, um die Beschreibungen aller Spalten in einer Tabelle wiederzuverwenden. Weitere Informationen zum Erstellen und Verwenden von Includes in Dataform finden Sie unter Code in einem einzelnen Repository mit Includes wiederverwenden.

Das folgende Codebeispiel zeigt mehrere Konstanten mit Beschreibungen der einzelnen Spalten, die in der JavaScript-Datei includes/docs.js definiert sind:


// filename is includes/docs.js

const user_id = `A unique identifier for a user`;
const age = `The age of a user`;
const creation_date = `The date this user signed up`;
const user_tenure = `The number of years since the user's creation date`;
const badge_count = `The all-time number of badges the user has received`;
const questions_and_answer_count = `The all-time number of questions and answers the user has created`;
const question_count = `The all-time number of questions the user has created`;
const answer_count = `The all-time number of answers the user has created`;
const last_badge_received_at = `The time the user received their most recent badge`;
const last_posted_at = `The time the user last posted a question or answer`;
const last_question_posted_at = `The time the user last posted an answer`;
const last_answer_posted_at = `The time the user last posted a question`;

module.exports = {
   user_id,
   age,
   creation_date,
   user_tenure,
   badge_count,
   questions_and_answer_count,
   question_count,
   answer_count,
   last_badge_received_at,
   last_posted_at,
   last_question_posted_at,
   last_answer_posted_at,
};

Das folgende Codebeispiel zeigt die Konstanten user_id und age, die in includes/docs.js definiert sind und in der SQLX-Tabellendefinitionsdatei definitions/my_table.sqlx verwendet werden, um die Dokumentation für ausgewählte Spalten in der Tabelle zu generieren:

config {
  type: "table",
  description: "Table description.",
  columns: {
    user_id: docs.user_id,
    column2_name: "Description of the second column",
    column3_name: "Description of the third column",
    age: docs.age,
  }
}

SELECT ...

Das folgende Codebeispiel zeigt eine Konstante mit einer Reihe von Spaltenbeschreibungen, die in der JavaScript-Datei includes/docs.js definiert sind:


// filename is includes/docs.js

const columns = {
    user_id = `A unique identifier for a user`,
    age = `The age of a user`,
    creation_date = `The date this user signed up`,
    user_tenure = `The number of years since the user's creation date`,
    badge_count = `The all-time number of badges the user has received`,
    questions_and_answer_count = `The all-time number of questions and answers the user has created`,
    question_count = `The all-time number of questions the user has created`,
    answer_count = `The all-time number of answers the user has created`,
    last_badge_received_at = `The time the user received their most recent badge`,
    last_posted_at = `The time the user last posted a question or answer`,
    last_question_posted_at = `The time the user last posted an answer`,
    last_answer_posted_at = `The time the user last posted a question`,
}


module.exports = {
  columns
};

Das folgende Codebeispiel zeigt die Konstante columns, die in includes/table_docs.js definiert und in der SQLX-Tabellendefinitionsdatei definitions/my_table.sqlx verwendet wird, um die Dokumentation für alle Spalten in der Tabelle zu generieren:

config { type: "table",
description: "My table description",
columns: docs.columns
}

SELECT 1 AS one

BigQuery-Labels hinzufügen

In diesem Abschnitt erfahren Sie, wie Sie Tabellen in Dataform Labels hinzufügen.

In BigQuery können Ressourcen Labels hinzugefügt werden. Weitere Informationen zu Labels in BigQuery finden Sie unter Einführung in Labels.

Wenn Sie einer Tabelle in Dataform ein BigQuery-Label hinzufügen möchten, fügen Sie das Label dem bigquery-Block im config-Block der SQLX-Datei mit der Tabellendefinition hinzu.

So fügen Sie einer Tabellendefinitionsdatei ein BigQuery-Label hinzu:

  1. Rufen Sie Ihren Entwicklungsarbeitsbereich auf.
  2. Maximieren Sie im Bereich Dateien die Option definitions/.
  3. Wählen Sie eine SQLX-Tabellendefinitionsdatei aus.

  4. Fügen Sie im Block config ein Label im folgenden Format hinzu:

    bigquery: {
    labels: {
      LABEL1: "VALUE_OF_LABEL1"
    }
  }

    Ersetzen Sie Folgendes:

    • LABEL1: der Name Ihres Labels
    • VALUE_OF_LABEL1: der Wert Ihres Labels

  5. Optional: Wenn Sie ein Label mit einem Namen hinzufügen möchten, der Sonderzeichen enthält, geben Sie den Labelnamen in Anführungszeichen ("") ein.

  6. Optional: Klicken Sie auf Formatieren.

Im folgenden Codebeispiel sehen Sie die Labels department:shipping und cost-center:logistics, die dem Block bigquery in einer SQLX-Datei mit der Definition einer partitionierten Tabelle hinzugefügt wurden:

config {
  type: "table",
  bigquery: {
    partitionBy: "DATE(ts)",
    labels: {
      department: "shipping",
      "cost-center": "logistics"
    }
  }
}

SELECT CURRENT_TIMESTAMP() AS ts

Nächste Schritte