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 publication d'une table ou d'écriture d'une assertion. Vous pouvez définir des commandes SQL personnalisées que Dataform exécutera dans BigQuery.

Avant de commencer

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

    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

Pour obtenir les autorisations nécessaires pour définir une opération SQL personnalisée, demandez à votre administrateur de vous accorder 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 page Gérer l'accès aux projets, aux dossiers et aux organisations.

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 la définition d'une opération personnalisée

Stockez les fichiers SQLX de définition d'opérations personnalisées 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/. Par 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 même fichier SQLX. BigQuery exécute toutes les opérations d'un fichier dans le même contexte et crée le code SQL exécuté en joignant toutes les opérations avec un point-virgul ;.

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 la définition de l'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 référençable

Vous pouvez créer manuellement une table dans une opération SQL personnalisée que vous pouvez référencer 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 mettre à la disposition d'autres scripts, procédez comme suit:

  1. Dans votre espace de travail de développement, sélectionnez le fichier SQLX pour la définition de l'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 au nom du fichier, écrivez une opération CREATE SQL avec la fonction self au format suivant:

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

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

  4. Facultatif: cliquez sur Format.

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

  • Pour faire référence à une table de sortie d'opération SQL personnalisée dans une 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érençable 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 faire référence à 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 un tableau vide afin qu'un autre service puisse le renseigner 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 faire référence à la table vide dans d'autres définitions d'objets de workflow SQL, par exemple des tables et des 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'entier et une colonne de chaîne. 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
)

Étape suivante