Ajouter des opérations SQL personnalisées

Ce document explique comment définir des opérations SQL personnalisées dans un fichier SQLX dédié.

Dataform peut exécuter des opérations SQL personnalisées qui ne correspondent pas au modèle Dataform de la publication d'une table ou de l'écriture d'une assertion. Vous pouvez définir des commandes SQL personnalisées que Dataform doit exécuter dans BigQuery.

Avant de commencer

  1. Dans la console Google Cloud, accédez à la page Dataform.

    Accéder à la page Dataform

  2. Sélectionnez ou créez un dépôt.

  3. Sélectionnez ou créez un espace de travail de développement.

Rôles requis

Afin d'obtenir les autorisations nécessaires pour définir une opération SQL personnalisée, demandez à votre administrateur de vous attribuer le rôle IAM Éditeur Dataform (roles/dataform.editor) sur les espaces de travail. Pour en savoir plus sur l'attribution de rôles, consultez la section Gérer les accès.

Vous pouvez également obtenir les autorisations requises via des rôles personnalisés ou d'autres rôles prédéfinis.

Créer un fichier pour définir une opération personnalisée

Stockez les fichiers SQLX de définition d'opération personnalisée dans le répertoire definitions/. Pour créer un fichier SQLX dans le répertoire definitions/, procédez comme suit:

  1. Accédez à votre espace de travail de développement.
  2. Dans le volet Fichiers, à côté de definitions/, cliquez sur le menu Plus.
  3. Cliquez sur Créer un fichier.

  4. Dans le champ Ajouter un chemin d'accès au fichier, saisissez le nom du fichier suivi de .sqlx après definitions/. Exemple : definitions/sample-operation.sqlx.

    Les noms de fichiers ne peuvent contenir que des chiffres, des lettres, des traits d'union et des traits de soulignement.

  5. Cliquez sur Créer un fichier.

Définir une opération SQL personnalisée

Vous pouvez définir une opération SQL personnalisée dans un fichier SQLX de type: operations. Vous pouvez écrire n'importe quelle instruction SQL BigQuery dans un fichier operations. Dataform exécute vos opérations SQL personnalisées dans BigQuery sans modification.

Vous pouvez définir plusieurs opérations SQL personnalisées dans un seul fichier SQLX. BigQuery exécute toutes les opérations dans un fichier dans le même contexte et crée le code SQL exécuté en joignant toutes les opérations par un point-virgule ;.

Pour définir plusieurs opérations SQL personnalisées lorsque vous utilisez le framework Dataform Open Source avec un entrepôt de données autre que BigQuery, séparez les opérations avec ---.

Pour définir une opération SQL personnalisée dans un fichier SQLX dédié, procédez comme suit:

  1. Dans votre espace de travail de développement, sélectionnez le fichier SQLX pour définir une opération personnalisée.

  2. Dans le fichier, saisissez l'extrait de code suivant :

    config { type: "operations" }

  3. En dehors du bloc config, écrivez votre opération SQL.

  4. (Facultatif) Cliquez sur Format.

L'exemple de code suivant montre plusieurs opérations SQL personnalisées définies dans un fichier operations:

config { type: "operations" }

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

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

L'exemple de code suivant montre une opération SQL personnalisée qui crée manuellement une vue:

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

Créer une table de sortie pouvant être référencée

Vous pouvez créer manuellement une table dans une opération SQL personnalisée à laquelle vous pourrez faire référence dans d'autres scripts. Pour créer une table disponible pour d'autres scripts, vous devez déclarer que l'opération a une sortie.

Pour que le nom de la table de sortie corresponde au nom du fichier operations, vous pouvez utiliser la fonction self dans l'opération CREATE.

Pour créer une table dans une opération personnalisée et la rendre disponible pour d'autres scripts, procédez comme suit:

  1. Dans votre espace de travail de développement, sélectionnez le fichier SQLX pour définir une opération personnalisée.

  2. Dans le fichier SQLX, saisissez l'extrait de code suivant:

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

  3. Facultatif: Pour faire correspondre le nom de la table de sortie avec le nom du fichier, écrivez une opération SQL CREATE avec la fonction self au format suivant:

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

    Remplacez CUSTOM_SQL_QUERY par votre instruction SQL SELECT de définition de table.

  4. (Facultatif) Cliquez sur Format.

Référencer une table de sortie d'opération SQL personnalisée

  • Pour référencer une table de sortie d'opération SQL personnalisée dans la définition SQLX d'une autre table, saisissez le nom de fichier de la table de sortie dans la fonction ref.

L'exemple de code suivant montre une opération SQL personnalisée dans un fichier custom_SQL_operation_table.sqlx qui crée une table référencée appelée custom_SQL_operation_table:

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

L'exemple de code suivant montre comment référencer custom\_SQL\_operation\_table table dans un fichier SQLX de définition de table:

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

Créer une table vide

Vous pouvez créer une table vide afin qu'un autre service puisse la remplir avec des données. Vous pouvez créer une table vide dans une opération SQL personnalisée à l'aide de la fonction CREATE TABLE. Pour pouvoir référencer la table vide dans d'autres définitions d'objets de workflow SQL, telles que les tables et les vues, vous pouvez ajouter la propriété hasOutput:true au bloc config de l'opération de table vide.

  • Pour créer une table vide, utilisez la fonction CREATE TABLE dans un fichier type: "operations" au format suivant:
config {
  type: "operations",
  hasOutput: true  // optional, lets you reference the empty table
}

CREATE TABLE ${self()} (

)

L'exemple de code suivant montre une opération SQL personnalisée qui crée une table vide avec une colonne d'entiers et de chaînes. La table vide créée ne peut pas être référencée par d'autres objets de workflow SQL:

config {
  type: "operations"
}

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

Étapes suivantes