Créer des tables

Dans Dataform, une table est l'un des types d'objets qui composent un workflow. Vous pouvez créer des tables qui référencent des données provenant des sources de données déclarées pour votre workflow ou d'autres tables de votre workflow. Dataform compile vos définitions de table en SQL en temps réel. Lorsque vous déclenchez l'exécution, Dataform exécute le code SQL et crée les tables que vous avez définies dans BigQuery.

Vous pouvez créer les types de tables suivants dans un fichier SQLX type: "table" :

Vous pouvez également définir des partitions et des clusters de table.

Pour conserver une trace de l'objectif d'une table ou de sa relation avec d'autres tables de votre workflow, vous pouvez ajouter de la documentation à la table ou à ses colonnes sélectionnées.

Pour tester les données d'une table par rapport à des conditions spécifiques, vous pouvez créer des requêtes de test de qualité des données appelées assertions. Dataform exécute des assertions chaque fois qu'il met à jour votre workflow et vous alerte en cas d'échec.

Pour remplacer le schéma, la base de données et le nom par défaut d'une table sélectionnée, vous pouvez remplacer les paramètres de la table.

Pour désactiver la création de tables ou exécuter une instruction SQL avant ou après la création de tables, vous pouvez configurer des actions supplémentaires.

Pour organiser vos tables dans BigQuery après les avoir exécutées, vous pouvez ajouter des étiquettes BigQuery. Pour en savoir plus, consultez la Présentation des libellés.

Pour restreindre l'accès aux données au niveau des colonnes d'une table, vous pouvez ajouter des tags avec stratégie BigQuery. Pour en savoir plus, consultez Présentation du contrôle des accès au niveau des colonnes.

Pour conserver les règles de données BigQuery et les tags de gouvernance des données (preview) appliqués par les systèmes externes aux exécutions de tables, vous pouvez conserver les métadonnées de contrôle des accès.

En plus de définir des tables dans un fichier SQLX type: "table", vous pouvez créer des tables vides en définissant une requête SQL personnalisée dans un fichier SQLX type: "operations". Vous pouvez créer une table vide pour qu'un autre service puisse l'alimenter en données.

Avant de commencer

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

    Accéder à Dataform

  2. Créez et initialisez un espace de travail de développement dans votre dépôt.

  3. Facultatif : Déclarez une source de données.

Rôles requis

Pour obtenir les autorisations nécessaires pour effectuer les tâches décrites dans ce document, demandez à votre administrateur de vous accorder les rôles IAM suivants :

Pour en savoir plus sur l'attribution de rôles, consultez Gérer l'accès aux projets, aux dossiers et aux organisations.

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

Pour conserver les métadonnées de contrôle des accès, le compte de service qui exécute vos workflows Dataform doit disposer d'autorisations IAM spécifiques en fonction de la tâche :

  • Extraire les métadonnées : pour lire la vue INFORMATION_SCHEMA.COLUMNS, le compte de service doit disposer des autorisations de lecture sur le schéma de l'ensemble de données ou de la table. Ces autorisations sont incluses dans les rôles IAM suivants :

  • Appliquer des tags de gouvernance des données : pour associer et gérer des tags de gouvernance des données sur des tables et des vues, le compte de service doit disposer des autorisations bigquery.tables.createTagBinding et bigquery.tables.deleteTagBinding. Ces autorisations sont incluses dans les rôles IAM prédéfinis suivants :

  • Appliquer des stratégies de données : pour créer et mettre à jour des stratégies de données (y compris des stratégies sans tag), le compte de service doit disposer des autorisations bigquery.dataPolicies.create et bigquery.dataPolicies.update. Ces autorisations sont incluses dans les rôles IAM prédéfinis suivants :

Créer une table

Cette section explique comment créer des tables avec Dataform Core dans Dataform.

À propos des définitions de tables

Pour définir une table, vous devez définir son type et écrire une instruction SELECT dans un fichier SQLX type: "table". Dataform compile ensuite votre code Dataform Core en SQL, exécute le code SQL et crée les tables que vous avez définies dans BigQuery.

Dans une instruction SELECT Dataform Core, vous définissez la structure du tableau et référencez d'autres objets de votre workflow.

En plus de définir des tables dans un fichier SQLX type: "table", vous pouvez créer des tables vides en définissant une requête SQL personnalisée dans un fichier SQLX type: "operations". Pour en savoir plus, consultez Créer une table vide.

Référencer des dépendances avec ref

Pour référencer une action de workflow dans une instruction SELECT et l'ajouter automatiquement en tant que dépendance, utilisez la fonction ref. Dataform exécute les dépendances avant les tables qui en dépendent pour vérifier l'ordre correct du pipeline.

La fonction ref est une fonction Dataform Core intégrée qui est essentielle à la gestion des dépendances dans Dataform. La fonction ref vous permet de référencer les objets suivants définis dans votre workflow Dataform et d'en dépendre automatiquement, au lieu de coder en dur les noms de schéma et de table :

Dataform utilise la fonction ref pour créer un arbre de dépendances de toutes les tables à créer ou à mettre à jour.

Après la compilation, Dataform ajoute des instructions récurrentes à l'instruction SQL, telles que CREATE, REPLACE, INSERT ou MERGE.

L'exemple de code suivant montre une définition de table utilisant la fonction 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

Dans la fonction ref, vous indiquez le nom de la déclaration de table ou de source de données dont vous souhaitez dépendre. Il s'agit généralement du nom du fichier SQLX dans lequel la déclaration de cette table ou de cette source de données est définie.

Si le nom d'une table est remplacé, utilisez le nom remplacé dans la fonction ref. Par exemple, référencez une table avec config { name: "overridden_name" } comme ref("overridden_name"). Pour en savoir plus, consultez Remplacer les paramètres d'une table et Faire référence à une table avec un nom remplacé.

Lorsque vous avez plusieurs tables portant le même nom dans différents schémas, vous pouvez faire référence à une table spécifique en fournissant deux arguments à la fonction ref : le nom du schéma et le nom de la table.

L'exemple de code suivant montre la fonction ref avec deux arguments pour spécifier une table dans un schéma spécifique :

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

Vous pouvez également ajouter manuellement des dépendances de table au bloc config pour les tables, les assertions, les déclarations de sources de données ou les opérations SQL personnalisées qui ne sont pas référencées dans une fonction ref de l'instruction SELECT. Dataform exécute ces dépendances avant les tables dépendantes.

L'exemple de code suivant montre une dépendance de table dans le bloc config :

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

Pour en savoir plus sur la gestion des dépendances dans votre workflow, consultez Définir des dépendances.

Référencer d'autres tables avec resolve

La fonction resolve vous permet de référencer une déclaration de table ou de source de données dans une instruction SELECT comme la fonction ref, mais elle n'ajoute pas la référence en tant que dépendance. Cela signifie que l'objet référencé à l'aide de la fonction resolve n'a aucune incidence sur l'exécution de la table qui utilise la fonction resolve.

Pour en savoir plus sur les fonctions Dataform Core intégrées, consultez la documentation de référence sur Dataform Core.

Créer un fichier SQLX pour définir une table

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

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

    Accéder à Dataform

  2. Pour ouvrir un dépôt, cliquez sur son nom.

  3. Pour ouvrir un espace de travail de développement, cliquez sur son nom.

  4. Dans le volet Fichiers, à côté de definitions/, cliquez sur Plus.

  5. Cliquez sur Créer un fichier.

  6. 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/my-table.sqlx.

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

  7. Cliquez sur Créer un fichier.

Définir le type de tableau

Pour créer une définition de type de table, procédez comme suit :

  1. Dans votre espace de travail de développement, dans le volet Fichiers, développez le répertoire definitions/.
  2. Sélectionnez le fichier SQLX de définition de table que vous souhaitez modifier.

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

    config { type: "TABLE_TYPE" }

    Remplacez TABLE_TYPE par l'un des types de tables suivants :

    • table
    • incremental
    • view

  4. Facultatif : Pour définir une vue matérialisée, saisissez la propriété materialized sous type: "view" au format suivant :

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

    Pour en savoir plus, consultez ITableConfig.

  5. (Facultatif) Cliquez sur Format.

Définir la structure et les dépendances de la table

Pour écrire une instruction SELECT de définition de table, et définir la structure et les dépendances de la table, procédez comme suit :

  1. Dans votre espace de travail de développement, dans le volet Fichiers, développez le répertoire definitions/.
  2. Sélectionnez le fichier SQLX de définition de table que vous souhaitez modifier.
  3. Sous le bloc config, écrivez une instruction SELECT.
  4. Facultatif : Cliquez sur Format.

L'exemple de code suivant montre une définition de table avec une instruction SELECT et la fonction 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

Ajouter des dépendances de tables manuelles

Pour ajouter des dépendances de table qui ne sont pas référencées dans l'instruction SELECT, mais qui doivent être exécutées avant la table actuelle, procédez comme suit :

  1. Dans votre espace de travail de développement, dans le volet Fichiers, développez le répertoire definitions/.
  2. Sélectionnez le fichier SQLX de définition de table que vous souhaitez modifier.

  3. Dans le bloc config du tableau, saisissez l'extrait de code suivant :

    dependencies: [ "DEPENDENCY_TABLE", ]

    Remplacez DEPENDENCY_TABLE par le nom de fichier de la table que vous souhaitez ajouter en tant que dépendance. Vous pouvez saisir plusieurs noms de fichiers.

  4. Facultatif : Cliquez sur Format.

L'exemple de code suivant montre deux tables ajoutées en tant que dépendances de table manuelles au bloc config d'un fichier de définition de table :

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

Remplacer les paramètres du tableau

Vous pouvez remplacer le schéma, la base de données et le nom par défaut d'une table sélectionnée.

Par défaut, une table suit la configuration de schéma et de base de données que vous avez définie dans workflow_settings.yaml. Le nom d'une table est identique à celui du fichier SQLX de définition de la table.

Pour remplacer le schéma et le nom d'une table sélectionnée, procédez comme suit :

  1. Accédez à votre espace de travail de développement.

  2. Dans le volet Fichiers, développez definitions/.

  3. Ouvrez un fichier de définition de table SQLX.

  4. Dans le bloc config, saisissez l'extrait de code suivant :

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

    Remplacez les éléments suivants :

    • OVERRIDDEN_SCHEMA : ensemble de données BigQuery dans lequel vous souhaitez créer la table.

    • OVERRIDDEN_DATABASE : ID du projet BigQuery dans lequel vous souhaitez créer la table.

    • OVERRIDDEN_NAME : nom de la table, qui est différent du nom de fichier de définition de table SQLX.

  5. Facultatif : Cliquez sur Format.

Pour en savoir plus, consultez Référencer une table avec un nom de table remplacé.

Créer des tables gérées Apache Iceberg

Utilisez Dataform pour créer des tables BigQuery au format Iceberg. Ces tables sont appelées tables gérées Iceberg. Pour en savoir plus sur les rôles requis et les autres tâches de configuration, consultez les étapes Avant de commencer pour les workflows de tables gérées Iceberg.

Créer la définition de table Iceberg

Pour créer les tables, définissez la configuration Iceberg dans le bloc bigquery d'un fichier de définition de table.

Pour définir les tables, procédez comme suit :

  1. Accédez à votre espace de travail de développement.
  2. Dans le volet Fichiers, développez definitions/.
  3. Ouvrez un fichier SQLX de définition de table.

  4. Dans le bloc config, ajoutez un bloc bigquery contenant un bloc iceberg au format suivant :

    config {
  type: "table",
  name: "my_table",
  uniqueKey: ["uniquekey"],
  bigquery: {
    iceberg: {
      bucket_name: "BUCKET_NAME"
    }
  }
}

    Remplacez BUCKET_NAME par le nom du bucket Cloud Storage dans lequel les données de la table sont stockées.

    Les autres propriétés sont facultatives et ont des valeurs par défaut si elles ne sont pas spécifiées. Pour en savoir plus, consultez les propriétés de iceberg.

    Vous pouvez fournir les valeurs pour chaque table ou définir des valeurs par défaut au niveau du workflow dans workflow_settings.yaml. Vous pouvez remplacer les valeurs par défaut au niveau du workflow en définissant explicitement les propriétés dans le bloc iceberg.

Créer des partitions et des clusters de tables

Cette section vous explique comment utiliser Dataform Core pour créer des partitions et des clusters de tables. BigQuery accepte les tables partitionnées et le clustering de tables. Pour en savoir plus, consultez les pages Présentation des tables partitionnées et Créer et utiliser des tables groupées.

Créer une partition de table

Pour créer une partition de table, procédez comme suit :

  1. Accédez à votre espace de travail de développement.
  2. Dans le volet Fichiers, développez definitions/.
  3. Ouvrez un fichier SQLX de définition de table.

  4. Dans le bloc config, ajoutez le bloc bigquery sous la déclaration du type de table au format suivant :

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

  5. Dans le bloc bigquery, saisissez l'extrait de code suivant :

        partitionBy: "PARTITION_EXPRESSION"

    Remplacez PARTITION_EXPRESSION par une expression pour partitionner la table.

  6. Facultatif : Cliquez sur Format.

L'exemple de code suivant montre comment partitionner une table par heure dans un fichier SQLX de définition de table :

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

L'exemple de code suivant montre comment partitionner une table par une valeur entière dans un fichier SQLX de définition de table :

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

Définir un filtre de partitionnement

Pour définir un filtre de partition, procédez comme suit :

  1. Accédez à votre espace de travail de développement.
  2. Dans le volet Fichiers, développez definitions/.
  3. Ouvrez un fichier SQLX de définition de table partitionnée.

  4. Dans le bloc bigquery, saisissez l'extrait de code suivant :

    requirePartitionFilter : true

  5. Facultatif : Cliquez sur Format.

L'exemple de code suivant montre un filtre de partition défini dans le bloc bigquery d'un fichier SQLX de table partitionnée :

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

Pour en savoir plus sur le filtre de partition dans BigQuery, consultez Définir l'attribut "Exiger un filtre de partitionnement" sur une table partitionnée.

Définir une durée de conservation pour les partitions

Pour contrôler la conservation de toutes les partitions d'une table partitionnée, procédez comme suit :

  1. Accédez à votre espace de travail de développement.
  2. Dans le volet Fichiers, développez definitions/.
  3. Ouvrez un fichier SQLX de définition de table partitionnée.

  4. Dans le bloc bigquery, saisissez l'extrait de code suivant :

    partitionExpirationDays: NUMBER_OF_DAYS

    Remplacez NUMBER_OF_DAYS par le nombre de jours pendant lesquels vous souhaitez conserver les partitions.

  5. Facultatif : Cliquez sur Format.

L'exemple de code suivant montre une période de conservation des partitions définie sur 14 jours dans le bloc bigquery d'un fichier SQLX de table partitionnée :

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

Créer un cluster de tables

Pour créer un cluster de tables, procédez comme suit :

  1. Accédez à votre espace de travail de développement.
  2. Dans le volet Fichiers, développez definitions/.
  3. Ouvrez un fichier SQLX de définition de table.

  4. Dans le bloc bigquery, saisissez l'extrait de code suivant :

        clusterBy: ["CLUSTER_COLUMN"]

    Remplacez CLUSTER_COLUMN par le nom de la colonne par laquelle vous souhaitez mettre en cluster la table. Pour en savoir plus, consultez clustering_column_list.

  5. Facultatif : cliquez sur Format.

L'exemple de code suivant montre une table partitionnée mise en cluster par les colonnes name et revenue :

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

Configurer une table incrémentielle

Cette section vous explique comment utiliser Dataform Core pour configurer une table incrémentielle.

À propos des tables incrémentales

Dataform met à jour les tables différemment selon leur type. Lors de chaque exécution d'une table ou d'une vue, Dataform recrée l'intégralité de la table ou de la vue.

Lorsque vous définissez une table incrémentielle, Dataform ne la crée qu'une seule fois à partir de zéro. Lors des exécutions suivantes, Dataform n'insère ou ne fusionne que les nouvelles lignes dans la table incrémentielle en fonction des conditions que vous configurez.

Dataform n'insère de nouvelles lignes que dans les colonnes qui existent déjà dans la table incrémentielle. Si vous modifiez la requête de définition de table incrémentielle (par exemple, en ajoutant une colonne), vous devez décider si vous devez reconstruire la table à partir de zéro. Pour recréer la table, la prochaine fois que vous déclencherez une exécution de table, sélectionnez l'option Exécuter avec actualisation complète. Pour d'autres options, consultez Modifier le schéma d'une table incrémentale sans actualisation complète.

Voici quelques cas d'utilisation courants des tables incrémentielles :

Optimisation des performances
Pour certains types de données, comme les journaux Web ou les données analytiques, vous pouvez choisir de ne traiter que les nouveaux enregistrements au lieu de retraiter l'intégralité du tableau.
Réduction de la latence
Vous pouvez utiliser des tables incrémentielles pour exécuter des workflows rapidement, mais fréquemment, ce qui réduit la latence en aval des tables de sortie.
Instantanés quotidiens
Vous pouvez configurer une table incrémentielle pour créer des instantanés quotidiens des données de la table, par exemple pour l'analyse longitudinale des paramètres utilisateur stockés dans une base de données de production.

Traiter un sous-ensemble de lignes dans une table incrémentielle

Pour déterminer un sous-ensemble de lignes à traiter par Dataform lors de chaque exécution, ajoutez une clause WHERE conditionnelle au fichier de définition SQLX de la table incrémentielle. Dans la clause WHERE, vous pouvez spécifier une condition incrémentielle et une condition non incrémentielle. Dataform applique la condition incrémentielle lors de l'exécution de la table sans actualisation complète, et la condition non incrémentielle lors de l'exécution avec actualisation complète.

Pour configurer une table incrémentielle, procédez comme suit :

  1. Accédez à votre espace de travail de développement.
  2. Dans le volet Fichiers, développez definitions/.
  3. Ouvrez un fichier SQLX de définition de table incrémentielle.

  4. Saisissez une clause WHERE au format suivant :

    config { type: "incremental" }

SELECT_STATEMENT

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

    Remplacez les éléments suivants :

    • SELECT_STATEMENT : instruction SELECT qui définit votre table.
    • INCREMENTAL_CONDITION : condition que vous spécifiez dans la clause WHERE pour sélectionner les lignes que Dataform doit traiter lors de l'exécution de la table sans actualisation complète.
    • NON_INCREMENTAL_CONDITION : condition que vous spécifiez dans la clause WHERE pour sélectionner les lignes que Dataform doit traiter lors de l'exécution de la table avec une actualisation complète.

  5. Facultatif : Cliquez sur Format.

L'exemple de code suivant montre une table incrémentielle qui traite de manière incrémentielle les lignes de la table productiondb.logs :

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"`)}

L'exemple de code suivant montre une table incrémentielle qui crée un instantané de la table productiondb.customers :

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()})`) }

Fusionner des lignes dans une table incrémentielle

Pour vérifier qu'une table incrémentielle ne contient qu'une seule ligne correspondant à une combinaison de colonnes sélectionnée, définissez les colonnes sélectionnées comme uniqueKey pour fusionner les lignes ayant la même valeur uniqueKey. Lorsque vous mettez à jour la table, Dataform fusionne les lignes ayant la même valeur uniqueKey au lieu de les ajouter.

Pour configurer la fusion dans une table incrémentielle, procédez comme suit :

  1. Accédez à votre espace de travail de développement.
  2. Dans le volet Fichiers, développez definitions/.
  3. Sélectionner un fichier SQLX de définition de table incrémentielle

  4. Dans le bloc config, définissez les colonnes sélectionnées sur uniqueKey au format suivant :

    uniqueKey: ["COLUMN_NAME"]

    Remplacez COLUMN_NAME par le nom d'une colonne sélectionnée.

  5. Facultatif : Cliquez sur Format.

L'exemple de code suivant montre une table incrémentielle dont la colonne transaction_id est définie sur uniqueKey pour vérifier qu'elle contient toujours une ligne :

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

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

Filtrer les lignes d'une table incrémentielle

Dans une table partitionnée incrémentielle, pour éviter que Dataform n'analyse l'intégralité de la table pour trouver les lignes correspondantes, définissez updatePartitionFilter de sorte qu'il ne prenne en compte qu'un sous-ensemble d'enregistrements.

L'exemple de code suivant montre une table partitionnée incrémentielle avec fusion configurée par les propriétés updatePartitionFilter et uniqueKey, à l'aide de deux types de filtrage distincts :

  • L'élagage des cibles à l'aide de la propriété updatePartitionFilter limite les partitions analysées dans la table cible lors d'une opération MERGE pour trouver les lignes correspondant à uniqueKey.
  • Élagage de la source à l'aide d'une clause WHERE : limite les lignes extraites de la table source lors d'une exécution incrémentielle. Pour ce faire, utilisez la fonction ${when(incremental(), ...)}.
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()})`) }

Éviter les analyses complètes des tables lors de l'ingestion à partir d'une table partitionnée

Lorsque vous créez une table incrémentielle qui fait référence à une table partitionnée, nous vous recommandons de créer votre requête de table de manière à éviter les analyses complètes de la table partitionnée lors de chaque mise à jour incrémentielle.

Vous pouvez limiter le nombre de partitions analysées par BigQuery pour mettre à jour la table incrémentielle en utilisant une expression constante dans votre requête de table. Pour transformer une valeur de la table partitionnée en expression constante, utilisez le scripting BigQuery pour déclarer la valeur en tant que variable dans le bloc pre_operations. Ensuite, utilisez la variable comme expression constante dans une clause WHERE dans la requête SELECT.

Avec cette configuration, Dataform met à jour la table incrémentielle en fonction des partitions les plus récentes de la table partitionnée référencée, sans analyser l'intégralité de la table.

Pour configurer une table incrémentielle qui référence une table partitionnée et évite les analyses de table complètes, procédez comme suit :

  1. Accédez à votre espace de travail de développement.
  2. Dans le volet Fichiers, développez definitions/.
  3. Sélectionnez un fichier SQLX de définition de table incrémentielle.
  4. Dans le bloc pre_operations, déclarez une variable avec des scripts BigQuery.
  5. Filtrez l'instruction SELECT qui définit la table avec une clause WHERE qui fait référence à la variable déclarée.
  6. (Facultatif) Cliquez sur Format.

L'exemple de code suivant montre une table incrémentielle dans laquelle la table raw_events référencée est partitionnée par event_timestamp :

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

Dans l'exemple de code précédent, la variable event_timestamp_checkpoint est définie dans le bloc pre_operations. La variable event_timestamp_checkpoint est ensuite utilisée comme expression constante dans la clause WHERE.

Recompiler une table incrémentale à partir de zéro avec une actualisation complète

Vous pouvez forcer la recompilation d'une table incrémentielle à partir de zéro à l'aide de l'interface de ligne de commande avec l'option --full-refresh ou de l'option Exécuter avec une actualisation complète lors du déclenchement d'une exécution de workflow.

Lorsque vous sélectionnez l'option d'actualisation complète, dans votre espace de travail de développement ou à l'aide de la CLI Dataform, Dataform ignore le paramètre ${when(incremental(), ... } lors de l'exécution et recrée la table avec une instruction CREATE OR REPLACE.

Protéger une table incrémentale contre une actualisation complète

Pour éviter qu'une table incrémentielle ne soit reconstruite à partir de zéro et qu'il y ait une perte de données potentielle, vous pouvez la définir comme protected. Vous pouvez empêcher la recompilation d'une table incrémentielle si votre source de données est temporaire.

Pour marquer une table incrémentielle comme protected :

  1. Accédez à votre espace de travail de développement.
  2. Dans le volet Fichiers, développez definitions/.
  3. Sélectionnez un fichier SQLX de définition de table incrémentielle.
  4. Dans le bloc config, saisissez protected: true.
  5. (Facultatif) Cliquez sur Format.

L'exemple de code suivant montre un tableau incrémentiel marqué comme protected :

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

Modifier le schéma d'une table incrémentale sans actualisation complète

Vous pouvez modifier le schéma d'une table incrémentale dans la requête SELECT sans actualisation complète de la table à l'aide de la propriété de configuration onSchemaChange. Cette propriété vous permet d'ajouter des colonnes à une table ou d'en supprimer, tout en conservant les données historiques. Cette approche permet d'éviter la perte de données et les mises à jour manuelles dans BigQuery.

Pour utiliser cette fonctionnalité, vous devez installer Dataform Core 3.0.11 ou version ultérieure.

Une fois que vous avez défini la propriété onSchemaChange, Dataform vérifie la requête SELECT pour déterminer si des colonnes ont été ajoutées ou supprimées. Dataform effectue ensuite l'action spécifiée par la valeur de la propriété onSchemaChange.

Pour définir la propriété onSchemaChange, procédez comme suit :

  1. Accédez à votre espace de travail de développement.
  2. Dans le volet Fichiers, développez definitions/.
  3. Sélectionnez un fichier SQLX de définition de table incrémentielle.

  4. Dans le bloc config, saisissez l'extrait de code suivant :

    onSchemaChange: "ON_CHANGE_ACTION"

    Remplacez ON_CHANGE_ACTION par l'une des actions suivantes :

    • IGNORE (par défaut) : ignore les colonnes ajoutées et affiche une erreur pour les colonnes manquantes. Si onSchemaChange n'est pas défini, il s'agit du comportement par défaut lorsque le schéma change.
    • FAIL : arrête l'action si Dataform détecte une modification du schéma, ce qui permet de maintenir la cohérence du schéma.
    • EXTEND : ajoute de nouvelles colonnes de la requête à la table incrémentielle et ajoute des valeurs NULL pour les enregistrements précédents. Affiche une erreur si une colonne est supprimée ou manquante dans le schéma d'origine de la requête. Vous pouvez utiliser ce paramètre pour ajouter des colonnes à vos tables incrémentielles lors de l'exécution.

    • SYNCHRONIZE : ajoute de nouvelles colonnes de la requête à la table incrémentielle et ajoute des valeurs NULL pour les enregistrements précédents. Supprime les colonnes qui figuraient dans le schéma d'origine, mais qui sont désormais absentes de la requête actuelle.

  5. (Facultatif) Cliquez sur Format.

L'exemple de code suivant montre un tableau incrémentiel avec l'action EXTEND définie pour la propriété onSchemaChange :

config {
    type: "incremental",
    onSchemaChange: "EXTEND",
}
SELECT ...

Ajouter une documentation de table

Cette section vous explique comment ajouter des descriptions d'une table, de ses colonnes et de ses enregistrements à un fichier SQLX Dataform Core.

Vous pouvez ajouter des descriptions de tables, de colonnes et d'enregistrements à tous les types de tables dans Dataform : tables, tables incrémentielles et vues.

Vous pouvez documenter les éléments suivants :

  • Objectif de la table.
  • Contenu ou rôle des colonnes ou des enregistrements dans le tableau.
  • Relation entre la table et les autres actions de votre workflow, par exemple les tables ou les vues qui dépendent de la table actuelle.
  • Assertions appliquées à la table.
  • Opérations pré ou post-traitement appliquées à la table.
  • Le propriétaire de la table, c'est-à-dire l'utilisateur qui l'a créée. Ces informations peuvent être utiles si plusieurs membres de l'équipe travaillent sur un workflow.

Ajouter des métadonnées pour Knowledge Catalog

Les métadonnées standards de BigQuery (ensembles de données, tables et vues, par exemple) sont automatiquement disponibles dans Knowledge Catalog.

Vous pouvez également définir des métadonnées personnalisées pour vos tables et vues directement dans le bloc de configuration de vos fichiers .sqlx. Une fois l'action terminée, Dataform lance automatiquement une synchronisation des métadonnées avec Knowledge Catalog. Ce processus d'enrichissement met à jour Knowledge Catalog avec les métadonnées sémantiques définies dans votre configuration SQLX.

Utilisez la clé de métadonnées pour spécifier des informations pour le Knowledge Catalog. Le processus d'enrichissement est compatible avec les constructions de métadonnées suivantes :

  • Présentation : documentation et texte récapitulatif pour l'entrée. Nécessite Dataform Core version 3.0.37 ou ultérieure.
  • Aspects génériques : détails sémantiques tels que les informations sur le système et le type de table. Nécessite la version 3.0.52 ou ultérieure de Dataform Core.

L'exemple de configuration suivant montre comment ajouter des aspects de métadonnées génériques et de présentation à une configuration de table pour Knowledge Catalog :

config {
  type: "table",
  metadata: {
    overview: "This table provides standardized trip data.",
    extraProperties: {
        generic: {
              system: "BigQuery",
              type: "table"
        }
      }
  }
}

Pour vérifier l'état d'une mise à jour des métadonnées, consultez Inspecter les journaux d'exécution de l'espace de travail pour les workflows Dataform ou Afficher les exécutions manuelles passées pour les pipelines BigQuery.

Pour vérifier les métadonnées synchronisées, vous pouvez rechercher l'élément dans Knowledge Catalog. Pour en savoir plus, consultez Rechercher des ressources.

Ajouter une description de la table

Pour ajouter une description à une table dans un fichier SQLX, procédez comme suit :

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

    Accéder à Dataform

  2. Sélectionnez un dépôt.

  3. Sélectionnez un espace de travail de développement.

  4. Dans le volet Fichiers, cliquez sur le fichier SQLX de définition de table que vous souhaitez modifier.

  5. Dans le bloc config du fichier, saisissez la description de la table au format suivant :

    description: "Description of the table",

  6. Facultatif : Cliquez sur Format.

L'exemple de code suivant montre une description de table ajoutée au bloc config d'un fichier de définition de table SQLX :

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

Ajouter des descriptions de colonnes et d'enregistrements

Pour ajouter des descriptions de colonnes et d'enregistrements individuels à un fichier SQLX, procédez comme suit :

  1. Dans le bloc config de votre fichier de définition de table, saisissez columns: {}.

  2. Dans columns: {}, saisissez les descriptions des colonnes au format suivant :

    column_name: "Description of the column",

  3. Dans columns: {}, saisissez les descriptions des enregistrements au format suivant :

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

  4. (Facultatif) Cliquez sur Format.

L'exemple de code suivant montre les descriptions de table, de colonne et d'enregistrement dans le bloc config d'un fichier de définition de table SQLX :

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

Réutiliser la documentation des colonnes avec des inclusions

Vous pouvez réutiliser les descriptions des colonnes dans Dataform dans votre workflow SQL avec des inclusions JavaScript. Vous pouvez réutiliser la documentation des colonnes si vous avez plusieurs colonnes portant le même nom et la même description dans votre workflow SQL.

Vous pouvez définir une constante avec la description d'une seule colonne ou une constante avec un ensemble de descriptions de colonnes pour réutiliser les descriptions de toutes les colonnes d'une table. Pour en savoir plus sur la création et l'utilisation des inclusions dans Dataform, consultez Réutiliser du code dans un même dépôt avec les inclusions.

L'exemple de code suivant montre plusieurs constantes avec des descriptions de colonnes individuelles définies dans le fichier JavaScript includes/docs.js :


// 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,
};

L'exemple de code suivant montre les constantes user_id et age, définies dans includes/docs.js, utilisées dans le fichier de définition de table SQLX definitions/my_table.sqlx pour générer la documentation des colonnes sélectionnées dans la table :

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 ...

L'exemple de code suivant montre une constante avec un ensemble de descriptions de colonnes définies dans le fichier JavaScript includes/docs.js :


// 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
};

L'exemple de code suivant montre la constante columns, qui est définie dans includes/table_docs.js et utilisée dans le fichier de définition de table SQLX definitions/my_table.sqlx pour générer la documentation de toutes les colonnes de la table :

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

SELECT 1 AS one

Ajouter des étiquettes BigQuery

Cette section vous explique comment ajouter des étiquettes aux tables dans Dataform.

BigQuery permet d'ajouter des libellés aux ressources. Pour en savoir plus sur les étiquettes dans BigQuery, consultez Présentation des étiquettes.

Pour ajouter une étiquette BigQuery à une table dans Dataform, ajoutez l'étiquette au bloc bigquery dans le bloc config du fichier SQLX de définition de la table.

Pour ajouter une étiquette BigQuery à un fichier de définition de table, procédez comme suit :

  1. Accédez à votre espace de travail de développement.
  2. Dans le volet Fichiers, développez definitions/.
  3. Sélectionnez un fichier de définition de table SQLX.

  4. Dans le bloc config, ajoutez un libellé au format suivant :

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

    Remplacez les éléments suivants :

    • LABEL1 : nom de votre libellé
    • VALUE_OF_LABEL1 : valeur de votre étiquette

  5. Facultatif : Pour ajouter un libellé dont le nom contient des caractères spéciaux, saisissez le nom du libellé entre guillemets ("").

  6. (Facultatif) Cliquez sur Format.

L'exemple de code suivant montre les libellés department:shipping et cost-center:logistics ajoutés au bloc bigquery dans un fichier SQLX de définition de table partitionnée :

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

SELECT CURRENT_TIMESTAMP() AS ts

Conserver les métadonnées de contrôle des accès

Lorsque Dataform exécute des actions de workflow qui recréent des objets (par exemple, des tables standards, des vues ou des tables incrémentielles lors d'une actualisation complète), il génère généralement des instructions CREATE OR REPLACE dans BigQuery pour mettre à jour le schéma et les données de l'objet. Par défaut, dans BigQuery, les opérations de remplacement de table suppriment automatiquement les métadonnées de contrôle d'accès au niveau des colonnes existantes, telles que les stratégies de données et les tags de gouvernance des données (bêta) appliqués par les systèmes de gouvernance des données externes.

Vous pouvez configurer Dataform pour qu'il conserve automatiquement les règles de données BigQuery existantes (y compris les règles de données sans tag) et les tags de gouvernance des données lors des exécutions de workflow en activant l'option preserveGovernanceControls. Lorsque cette option est activée, Dataform effectue la séquence suivante lors de l'exécution :

  1. Sauvegarde les métadonnées de colonne existantes à partir de la vue INFORMATION_SCHEMA.COLUMNS.
  2. Crée une table de préproduction vide intermédiaire.
  3. Réapplique les stratégies de sécurité à la table intermédiaire.
  4. Remplissez le tableau avec des données.
  5. Effectue un échange atomique de tables pour remplacer la table d'origine.

Activer la préservation automatisée de la gouvernance pour une table

Pour activer la conservation automatisée des règles de données et des tags de gouvernance des données pour une table individuelle, une table incrémentielle ou une vue, définissez le paramètre preserveGovernanceControls sur true dans le bloc config du fichier de définition SQLX de l'objet.

Pour configurer la conservation de la gouvernance pour une table individuelle, procédez comme suit :

  1. Accédez à votre espace de travail de développement.
  2. Dans le volet Fichiers, développez definitions/.
  3. Sélectionnez un fichier de définition de table SQLX.
  4. Dans le bloc config, saisissez preserveGovernanceControls: true.
  5. (Facultatif) Cliquez sur Format.

L'exemple de code suivant montre une définition de table avec la conservation de gouvernance activée :

config {
  type: "table",
  preserveGovernanceControls: true
}

SELECT
  customer_id,
  email,
  phone_number
FROM
  ${ref("raw_customers")}

Activer la préservation automatisée de la gouvernance pour toutes les tables

Vous pouvez activer la conservation automatisée de la gouvernance comme comportement par défaut à l'échelle du projet pour toutes les tables, tables incrémentielles et vues de votre dépôt en définissant le paramètre preserveGovernanceControls sur true dans le fichier workflow_settings.yaml de votre dépôt.

Pour définir la valeur par défaut à l'échelle du projet, ajoutez le paramètre preserveGovernanceControls à votre fichier workflow_settings.yaml, comme illustré dans l'exemple suivant :

defaultProject: my-gcp-project-id
defaultDataset: my_dataform_dataset
defaultLocation: US
defaultAssertionDataset: my_dataform_assertions
dataformCoreVersion: 3.0.62
preserveGovernanceControls: true

Si vous activez la conservation de la gouvernance de manière globale dans le fichier workflow_settings.yaml, vous pouvez ignorer ce comportement et désactiver la conservation pour une table spécifique en définissant le paramètre preserveGovernanceControls sur false dans le bloc config de cette table individuelle.

Réappliquer les règles d'accès aux lignes à l'aide de post-opérations

L'activation de la préservation de la gouvernance protège automatiquement les métadonnées au niveau des colonnes, telles que les règles de données BigQuery et les tags de gouvernance des données, mais elle ne restaure pas automatiquement les règles d'accès aux lignes. Étant donné que le remplacement d'objets supprime les règles de sécurité au niveau des lignes existantes, vous pouvez réappliquer les règles d'accès aux lignes après la création de la table en définissant des instructions LDD SQL dans le bloc post_operations de votre fichier de définition de table SQLX.

L'exemple de code suivant montre comment réappliquer une règle d'accès au niveau des lignes dans le bloc post_operations d'une table :

config {
  type: "table",
}

SELECT
  bike_id,
  start_time,
  duration
FROM
  ${ref("raw_trips")}

post_operations {
  CREATE ROW ACCESS POLICY my_policy
  ON ${self()}
  GRANT TO ('serviceAccount:sa-name@example-gcp-project.iam.gserviceaccount.com')
  FILTER USING (bike_id LIKE 'US-%');
}

Limites

Lorsque vous utilisez la fonctionnalité de préservation de la gouvernance, tenez compte des limites suivantes :

  • Suppression de colonnes de schéma. Si une colonne est supprimée de la définition de votre table SQLX, Dataform ignore toutes les règles de données et tous les tags sauvegardés associés à la colonne supprimée lors de la phase de restauration.
  • Renommages de colonnes de schéma. Si vous renommez une colonne existante dans votre définition de table SQLX, Dataform ne peut pas mapper les anciennes associations de tags ou de règles au nouveau nom de colonne. Par conséquent, les tags et les règles existants sur ces colonnes sont perdus ou permutés, et vous devez réappliquer manuellement les tags et les règles aux colonnes renommées.
  • Modifications du partitionnement et du clustering. Il n'est pas possible de modifier le partitionnement ou le clustering d'une table existante qui utilise preserveGovernanceControls en place. BigQuery refuse le remplacement. Pour résoudre ce problème, supprimez la table et exécutez à nouveau le workflow. Vous devez réappliquer manuellement les règles de données au niveau des colonnes et les tags de gouvernance des données à la table recréée.
  • Suppression des expirations de partitions : La suppression de la propriété partitionExpirationDays de la configuration ne supprime pas l'expiration de la partition d'une table existante. Pour supprimer l'expiration de la partition d'une table existante, vous devez définir explicitement l'option sur la valeur de votre choix ou modifier la table manuellement.
  • Types de tables Une fois l'exécution réussie, la table est signalée comme un clone de table (table_type = 'CLONE' dans INFORMATION_SCHEMA.TABLES). Elle se comporte comme une table standard, et la table de base affichée dans ses métadonnées de clone n'existe plus.
  • Métadonnées au niveau de la table et règles d'accès aux lignes. Les tags au niveau des tables et les règles d'accès au niveau des lignes ne sont pas conservés d'une exécution à l'autre. Vous pouvez réappliquer des règles d'accès aux lignes après la création de la table en définissant des instructions LDD SQL dans le bloc post_operations de votre fichier de définition de table SQLX.
  • Échanges de tables non transactionnelles Les opérations LDD BigQuery ne sont pas transactionnelles. Si l'exécution d'un workflow échoue au milieu de la séquence de renommage atomique, une intervention manuelle peut être nécessaire si la table cible est laissée dans un état intermédiaire (par exemple, si la table est renommée définitivement en TABLE_NAME_old ou laissée en tant que df_tmp_intermediate).
  • Tags avec stratégie. La conservation automatisée de la gouvernance n'est pas compatible avec les tags avec stratégie BigQuery. Ajoutez plutôt des tags de gouvernance des données BigQuery (preview) et des règles de données à vos colonnes pour utiliser l'intégration SQL intégrée.

Considérations liées aux coûts de stockage

Lorsque preserveGovernanceControls est activé, Dataform recrée la table via une table de préproduction temporaire à chaque exécution (ou à chaque actualisation complète pour les tables incrémentielles). Comme pour toute recréation de table dans BigQuery, la version précédente des données de la table est conservée dans le voyage temporel pour la fenêtre configurée de l'ensemble de données (sept jours par défaut), suivie d'une période de sécurité de sept jours.

La facturation du stockage logique est le modèle de facturation par défaut dans BigQuery, tandis que la facturation du stockage physique est un paramètre à activer et à configurer pour chaque ensemble de données. Si votre ensemble de données utilise la facturation du stockage physique, ces données temporelles et de sécurité conservées sont facturées en plus de la table active. Par conséquent, les tables volumineuses qui sont fréquemment recréées peuvent augmenter considérablement vos coûts de stockage.

Pour réduire ce coût, procédez comme suit :

Étapes suivantes