Benutzerdefinierte SQL-Vorgänge hinzufügen

In diesem Dokument wird beschrieben, wie Sie benutzerdefinierte SQL-Vorgänge in einer speziellen SQLX-Datei definieren.

In Dataform können benutzerdefinierte SQL-Vorgänge ausgeführt werden, 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.

Hinweis

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

    Zur Seite „Dataform“

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

  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, damit Sie die Berechtigungen erhalten, die Sie zum Definieren eines benutzerdefinierten SQL-Vorgangs 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.

Datei für benutzerdefinierte Vorgangsdefinition erstellen

Speichern Sie SQLX-Dateien mit benutzerdefinierten 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 ein und fügen Sie nach definitions/ die Zahl .sqlx hinzu. Beispiel: definitions/sample-operation.sqlx

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

  5. Klicken Sie auf Datei erstellen.

Benutzerdefinierten SQL-Vorgang definieren

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

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

Wenn Sie mehrere benutzerdefinierte SQL-Vorgänge definieren möchten, wenn Sie das Open-Source-Dataform-Framework mit einem anderen Data Warehouse als BigQuery verwenden, trennen Sie die Vorgänge mit ---.

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

  1. Wählen Sie in Ihrem Entwicklungsarbeitsbereich die SQLX-Datei für die Definition des benutzerdefinierten Vorgangs 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 Formatieren.

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 eine Tabelle manuell in einem benutzerdefinierten SQL-Vorgang erstellen, auf den Sie in anderen Scripts verweisen können. Wenn Sie eine Tabelle erstellen möchten, die für andere Scripts verfügbar ist, müssen Sie angeben, dass der Vorgang eine Ausgabe hat.

Wenn der Name der Ausgabetabelle mit dem Namen der operations-Datei übereinstimmen soll, können Sie die Funktion self im CREATE-Vorgang verwenden.

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

  1. Wählen Sie in Ihrem Entwicklungsbereich die SQLX-Datei für die Definition der benutzerdefinierten Operation aus.

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

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

  3. Optional: Wenn der Name der Ausgabetabelle mit dem Namen der Datei übereinstimmen soll, schreiben Sie einen SQL-CREATE-Vorgang mit der self-Funktion im folgenden Format:

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

    Ersetzen Sie CUSTOM_SQL_QUERY durch die SQL-SELECTAnweisung für die Tabellendefinition.

  4. Optional: Klicken Sie auf Formatieren.

Auf eine Ausgabetabelle eines benutzerdefinierten SQL-Vorgangs verweisen

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

Das folgende Codebeispiel zeigt einen benutzerdefinierten SQL-Vorgang in einer custom_SQL_operation_table.sqlx-Datei, mit dem 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)

Im folgenden Codebeispiel wird auf die custom\_SQL\_operation\_table table in einer SQLX-Datei mit Tabellendefinition verwiesen:

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 in einem benutzerdefinierten SQL-Vorgang eine leere Tabelle erstellen. Wenn Sie in anderen SQL-Workflow-Objektdefinitionen, z. B. Tabellen und Ansichten, auf die leere Tabelle verweisen möchten, können Sie dem config-Block des Vorgangs „Leere Tabelle“ die Eigenschaft hasOutput:true hinzufügen.

  • Verwenden Sie die Funktion CREATE TABLE in einer type: "operations"-Datei im folgenden Format, um eine leere Tabelle zu erstellen:
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. Andere SQL-Workflow-Objekte können nicht auf die erstellte leere Tabelle verweisen:

config {
  type: "operations"
}

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

Nächste Schritte