Benutzerdefinierte SQL-Vorgänge hinzufügen

In diesem Dokument erfahren Sie, wie Sie benutzerdefinierte SQL-Vorgänge in einer dedizierten SQLX-Datei definieren.

Dataform kann benutzerdefinierte SQL-Vorgänge ausführen, die nicht in das Dataform-Modell zum Veröffentlichen einer Tabelle oder zum Schreiben einer Assertion passen. Sie können benutzerdefinierte SQL-Befehle für Dataform definieren, die in BigQuery ausgeführt werden sollen.

Hinweise

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

    Zur Seite „Dataform“

  2. Wählen Sie ein Repository aus oder erstellen Sie ein Repository.

  3. Wählen Sie einen Entwicklungsarbeitsbereich aus oder erstellen Sie einen.

Erforderliche Rollen

Bitten Sie Ihren Administrator, Ihnen die IAM-Rolle Dataform Editor (roles/dataform.editor) für Arbeitsbereiche zu gewähren, um die Berechtigungen zu erhalten, die Sie zum Definieren eines benutzerdefinierten SQL-Vorgangs benötigen. Weitere Informationen zum Zuweisen von Rollen finden Sie unter Zugriff verwalten.

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

Datei für die Definition eines benutzerdefinierten Vorgangs erstellen

Speichern Sie benutzerdefinierte SQLX-Dateien mit Vorgangsdefinitionen im Verzeichnis definitions/. So erstellen Sie eine neue SQLX-Datei im Verzeichnis definitions/:

  1. Rufen Sie Ihren Entwicklungsarbeitsbereich auf.
  2. Klicken Sie im Bereich Dateien neben definitions/ auf das -Menü Mehr.
  3. Klicken Sie auf Datei erstellen.

  4. Geben Sie im Feld Dateipfad hinzufügen den Namen der Datei gefolgt von .sqlx nach definitions/ ein. Beispiel: definitions/sample-operation.sqlx

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

  5. Klicken Sie auf Datei erstellen.

Benutzerdefinierten SQL-Vorgang definieren

Sie können einen benutzerdefinierten SQL-Vorgang in der SQLX-Datei von type: operations definieren. Sie können jede BigQuery-SQL-Anweisung in eine operations-Datei schreiben. Dataform führt Ihre benutzerdefinierten SQL-Vorgänge ohne Änderungen in BigQuery aus.

Sie können mehrere benutzerdefinierte SQL-Vorgänge in einer SQLX-Datei definieren. BigQuery führt alle Vorgänge in einer Datei im selben Kontext aus und erstellt die ausgeführte SQL, indem alle Vorgänge mit einem Semikolon ; zusammengeführt werden.

Wenn Sie bei Verwendung des Open-Source-Dataform-Frameworks mit einem anderen Data Warehouse als BigQuery mehrere benutzerdefinierte SQL-Vorgänge definieren möchten, trennen Sie die Vorgänge mit ---.

So definieren Sie einen benutzerdefinierten SQL-Vorgang in einer dedizierten SQLX-Datei:

  1. Wählen Sie in Ihrem Entwicklungsarbeitsbereich die SQLX-Datei für die Definition benutzerdefinierter Vorgänge aus.

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

    config { type: "operations" }

  3. Schreiben Sie den SQL-Vorgang außerhalb des config-Blocks.

  4. Optional: Klicken Sie auf Format.

Das folgende Codebeispiel zeigt mehrere benutzerdefinierte SQL-Vorgänge, die in einer operations-Datei definiert sind:

config { type: "operations" }

DELETE FROM dataset.table WHERE country = 'GB';

DELETE FROM dataset.table WHERE country = 'FR';

Das folgende Codebeispiel zeigt einen benutzerdefinierten SQL-Vorgang, mit dem eine Ansicht manuell erstellt wird:

config { type: "operations" }
CREATE OR REPLACE VIEW dataset.table AS (SELECT 1 AS TEST)

Referenzierbare Ausgabetabelle erstellen

Sie können manuell eine Tabelle in einem benutzerdefinierten SQL-Vorgang erstellen, auf den Sie in anderen Skripts verweisen können. Wenn Sie eine Tabelle erstellen möchten, die anderen Skripts zur Verfügung steht, müssen Sie deklarieren, dass der Vorgang eine Ausgabe hat.

Damit der Name der Ausgabetabelle mit dem Namen der Datei operations übereinstimmt, können Sie die Funktion self im Vorgang CREATE verwenden.

So erstellen Sie eine Tabelle in einem benutzerdefinierten Vorgang und stellen sie anderen Skripts zur Verfügung:

  1. Wählen Sie in Ihrem Entwicklungsarbeitsbereich die SQLX-Datei für die Definition benutzerdefinierter Vorgänge aus.

  2. Geben Sie in der SQLX-Datei das folgende Code-Snippet ein:

    config {
 type: "operations",
 hasOutput: true
 }

  3. Optional: Schreiben Sie einen SQL-CREATE-Vorgang mit der Funktion self im folgenden Format, um den Namen der Ausgabetabelle mit dem Namen der Datei abzugleichen:

    CREATE OR REPLACE TABLE ${self()} AS (CUSTOM_SQL_QUERY)

    Ersetzen Sie CUSTOM_SQL_QUERY durch die SQL-SELECT-Anweisung der Tabellendefinition.

  4. Optional: Klicken Sie auf Format.

Auf eine benutzerdefinierte Ausgabetabelle eines SQL-Vorgangs verweisen

  • Wenn Sie in der SQLX-Definition einer anderen Tabelle auf eine Ausgabetabelle eines benutzerdefinierten SQL-Vorgangs verweisen möchten, geben Sie den Dateinamen der Ausgabetabelle in die Funktion ref ein.

Das folgende Codebeispiel zeigt einen benutzerdefinierten SQL-Vorgang in einer custom_SQL_operation_table.sqlx-Datei, mit der eine referenzierbare Tabelle namens custom_SQL_operation_table erstellt wird:

// filename is custom_SQL_operation_table.sqlx
config {
type: "operations",
hasOutput: true
}
CREATE OR REPLACE VIEW ${self()} AS (SELECT 1 AS TEST)

Das folgende Codebeispiel zeigt, wie in einer SQLX-Tabellendefinitionsdatei auf custom\_SQL\_operation\_table table verwiesen wird:

config { type: "table" }
SELECT * FROM ${ref("custom_SQL_operation_table")}

Leere Tabelle erstellen

Sie können eine leere Tabelle erstellen, damit ein anderer Dienst sie mit Daten füllen kann. Mit der Funktion CREATE TABLE können Sie eine leere Tabelle in einem benutzerdefinierten SQL-Vorgang erstellen. Damit Sie in anderen SQL-Workflow-Objektdefinitionen (z. B. Tabellen und Ansichten) auf die leere Tabelle verweisen können, können Sie das Attribut hasOutput:true in den Block config des Vorgangs für leere Tabellen einfügen.

  • Zum Erstellen einer leeren Tabelle verwenden Sie die Funktion CREATE TABLE in einer type: "operations"-Datei im folgenden Format:
config {
  type: "operations",
  hasOutput: true  // optional, lets you reference the empty table
}

CREATE TABLE ${self()} (

)

Das folgende Codebeispiel zeigt einen benutzerdefinierten SQL-Vorgang, mit dem eine leere Tabelle mit einer Ganzzahl und einer Stringspalte erstellt wird. Auf die erstellte leere Tabelle kann nicht von anderen SQL-Workflow-Objekten verwiesen werden:

config {
  type: "operations"
}

CREATE TABLE ${self()} (
  x INT64,
  y STRING
)

Nächste Schritte