BigQuery : DDL
Créer et modifier datasets, tables, vues, fonctions et politiques en SQL. Le choix du partitionnement, du clustering et du type de table est expliqué dans BigQuery : organiser les tables.
Datasets
| Objectif | Syntaxe | Résultat |
|---|---|---|
| Créer un dataset dans une région | CREATE SCHEMA IF NOT EXISTS PROJECT_ID.DATASET OPTIONS (location = 'REGION', description = '...') |
SCHEMA désigne un dataset ; sans location, il est créé là où s'exécute la requête |
| Poser des expirations par défaut | CREATE SCHEMA DATASET OPTIONS (default_table_expiration_days = 30, default_partition_expiration_days = 400) |
Valeurs héritées par les nouvelles tables, qui peuvent les surcharger |
| Libeller un dataset | ALTER SCHEMA DATASET SET OPTIONS (labels = [('equipe', 'data'), ('env', 'prod')]) |
Libellés repris dans l'export de facturation |
| Réduire la fenêtre de time travel | ALTER SCHEMA DATASET SET OPTIONS (max_time_travel_hours = 48) |
Multiple de 24, de 48 à 168 h (défaut 168) |
| Facturer le stockage en octets physiques | ALTER SCHEMA DATASET SET OPTIONS (storage_billing_model = 'PHYSICAL') |
Effet sous 24 h ; plus de changement possible pendant 14 jours |
| Supprimer un dataset et son contenu | DROP SCHEMA DATASET CASCADE |
Sans CASCADE, RESTRICT par défaut : échec si le dataset n'est pas vide |
Créer une table
| Objectif | Syntaxe | Résultat |
|---|---|---|
| Table avec schéma explicite | CREATE TABLE DATASET.TABLE (id INT64 NOT NULL, nom STRING OPTIONS (description = '...'), maj TIMESTAMP DEFAULT CURRENT_TIMESTAMP()) |
NOT NULL = mode REQUIRED ; DEFAULT s'applique aux colonnes omises à l'insertion |
| Recréer sans erreur | CREATE OR REPLACE TABLE ... ou CREATE TABLE IF NOT EXISTS ... |
Les deux formes s'excluent ; OR REPLACE ne peut pas changer le type de partitionnement |
| Partition quotidienne sur un horodatage | PARTITION BY DATE(ts) ou PARTITION BY TIMESTAMP_TRUNC(ts, DAY) |
Granularités HOUR, DAY, MONTH, YEAR avec TIMESTAMP_TRUNC ou DATETIME_TRUNC |
| Partition mensuelle sur une date | PARTITION BY DATE_TRUNC(jour, MONTH) |
MONTH ou YEAR ; une colonne DATE seule donne des partitions quotidiennes |
| Partition par heure d'ingestion | PARTITION BY _PARTITIONDATE |
Pseudo-colonnes _PARTITIONTIME et _PARTITIONDATE ; incompatible avec AS SELECT |
| Partition par plage d'entiers | PARTITION BY RANGE_BUCKET(client_id, GENERATE_ARRAY(0, 1000000, 10000)) |
Début inclus, fin exclue, pas de 10 000 |
| Clustering | CLUSTER BY client_id, statut |
Jusqu'à quatre colonnes de premier niveau, l'ordre compte |
| Options de gestion | OPTIONS (partition_expiration_days = 400, require_partition_filter = TRUE, description = '...', labels = [('env', 'prod')]) |
Expiration par partition et filtre de partition obligatoire, pour une table partitionnée seulement |
| Table qui expire | OPTIONS (expiration_timestamp = TIMESTAMP_ADD(CURRENT_TIMESTAMP(), INTERVAL 7 DAY)) |
Table supprimée à cette date |
| Colonne d'identité | id INT64 GENERATED ALWAYS AS IDENTITY (START WITH 1 INCREMENT BY 1) |
Valeurs générées ; BY DEFAULT accepte aussi une valeur fournie ; des valeurs peuvent être sautées |
| Clés primaire et étrangère | PRIMARY KEY (id) NOT ENFORCED, FOREIGN KEY (client_id) REFERENCES DATASET.CLIENTS(id) NOT ENFORCED |
Non vérifiées par BigQuery, qui s'en sert pour optimiser les requêtes : l'unicité reste à garantir |
| Créer depuis une requête (CTAS) | CREATE TABLE DATASET.TABLE PARTITION BY jour CLUSTER BY client_id AS SELECT ... |
Schéma déduit de la requête ; NOT NULL n'est pas propagé |
| Même schéma, sans données | CREATE TABLE DATASET.T2 LIKE DATASET.T1 |
Schéma et métadonnées copiés |
| Copie complète | CREATE TABLE DATASET.T2 COPY DATASET.T1 |
Schéma, métadonnées et données |
| Clone modifiable à faible coût | CREATE TABLE DATASET.T_CLONE CLONE DATASET.T1 |
Seules les données qui divergent de la source sont facturées ; utile pour les tests |
| Instantané en lecture seule | CREATE SNAPSHOT TABLE DATASET.T_SNAP CLONE DATASET.T1 FOR SYSTEM_TIME AS OF TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 1 HOUR) OPTIONS (expiration_timestamp = ...) |
Photo figée de la table, dans la fenêtre de time travel |
| Restaurer depuis un instantané | CREATE TABLE DATASET.T_RESTAUREE CLONE DATASET.T_SNAP |
Nouvelle table modifiable |
| Table temporaire de script | CREATE TEMP TABLE staging (id INT64) |
Nom sans dataset ; voir DML et transactions |
Modifier une table
| Objectif | Syntaxe | Résultat |
|---|---|---|
| Ajouter une colonne | ALTER TABLE DATASET.TABLE ADD COLUMN IF NOT EXISTS pays STRING |
NULL pour les lignes existantes ; pas de colonne REQUIRED, ni de colonne de partition ou de clustering |
| Supprimer une colonne | ALTER TABLE DATASET.TABLE DROP COLUMN IF EXISTS ancien |
Métadonnées seulement : le stockage est libéré à la prochaine réécriture ; récupérable par time travel |
| Renommer une colonne | ALTER TABLE DATASET.TABLE RENAME COLUMN nom TO nom_client |
Supprime les aspects Knowledge Catalog attachés à la colonne |
| Renommer une table | ALTER TABLE DATASET.TABLE RENAME TO TABLE_V2 |
Nouveau nom seul, sans dataset ; impossible sur une table Iceberg gérée |
| Élargir un type | ALTER TABLE DATASET.TABLE ALTER COLUMN montant SET DATA TYPE NUMERIC |
Seulement vers un type moins restrictif : INT64 → NUMERIC, BIGNUMERIC, FLOAT64 ; NUMERIC → BIGNUMERIC ; STRING(5) → STRING(7) |
| Rendre une colonne facultative | ALTER TABLE DATASET.TABLE ALTER COLUMN code DROP NOT NULL |
REQUIRED → NULLABLE ; l'inverse est impossible |
| Poser une valeur par défaut | ALTER TABLE DATASET.TABLE ALTER COLUMN statut SET DEFAULT 'actif' |
Vaut pour les insertions suivantes |
| Décrire une colonne | ALTER TABLE DATASET.TABLE ALTER COLUMN id SET OPTIONS (description = 'Identifiant client') |
Colonne de premier niveau seulement |
| Changer les options de la table | ALTER TABLE DATASET.TABLE SET OPTIONS (partition_expiration_days = 180, require_partition_filter = TRUE) |
Réduire l'expiration supprime aussitôt les partitions trop anciennes |
| Retirer une option | ALTER TABLE DATASET.TABLE SET OPTIONS (expiration_timestamp = NULL) |
La valeur NULL efface l'option |
| Changer le partitionnement | CREATE TABLE DATASET.T2 PARTITION BY ... AS SELECT * FROM DATASET.T1, puis bascule |
Pas de ALTER possible ; le clustering, lui, se change par bq update --clustering_fields |
| Supprimer une table | DROP TABLE IF EXISTS DATASET.TABLE |
Aussi DROP VIEW, DROP MATERIALIZED VIEW, DROP EXTERNAL TABLE, DROP SNAPSHOT TABLE, DROP FUNCTION, DROP PROCEDURE |
Vues
| Objectif | Syntaxe | Résultat |
|---|---|---|
| Vue logique | CREATE OR REPLACE VIEW DATASET.VUE OPTIONS (description = '...') AS SELECT ... |
Requête réexécutée à chaque lecture, facturée sur les tables sous-jacentes |
| Vue matérialisée | CREATE MATERIALIZED VIEW DATASET.VM PARTITION BY jour CLUSTER BY pays OPTIONS (enable_refresh = TRUE, refresh_interval_minutes = 60) AS SELECT jour, pays, SUM(montant) AS ca FROM DATASET.VENTES GROUP BY jour, pays |
Rafraîchissement automatique (défaut : activé, toutes les 30 min) ; partitionnement identique à celui de la table de base |
| Accepter des résultats un peu anciens | OPTIONS (max_staleness = INTERVAL '4:0:0' HOUR TO SECOND) |
Vue servie sans relire les tables de base tant que le dernier rafraîchissement a moins de 4 h |
| Vue matérialisée non incrémentale | OPTIONS (allow_non_incremental_definition = TRUE, max_staleness = ...) |
SQL plus large (jointures externes, UNION, fenêtres) ; max_staleness obligatoire ; chaque rafraîchissement recalcule tout ; option fixée à la création |
| Changer l'intervalle de rafraîchissement | ALTER MATERIALIZED VIEW DATASET.VM SET OPTIONS (refresh_interval_minutes = 120) |
— |
| Donner un rôle en SQL | GRANT `roles/bigquery.dataViewer` ON VIEW DATASET.VUE TO "group:analystes@example.com" |
Ressources SCHEMA, TABLE, VIEW, EXTERNAL TABLE, PROJECT ; retrait par REVOKE |
Une vue autorisée lit un dataset source auquel ses lecteurs n'ont pas accès. L'autorisation ne se fait pas en DDL : on ajoute la vue à la liste d'accès du dataset source (console,
bq update --sourcesur la définition JSON du dataset, ou API). Les utilisateurs reçoivent BigQuery Data Viewer sur le dataset de la vue et BigQuery Job User sur le projet qui exécute leurs requêtes.
Tables externes, BigLake et Iceberg
| Objectif | Syntaxe | Résultat |
|---|---|---|
| Table externe sur des CSV | CREATE EXTERNAL TABLE DATASET.EXT OPTIONS (format = 'CSV', uris = ['gs://BUCKET/chemin/*.csv'], skip_leading_rows = 1) |
Schéma détecté si la liste des colonnes est omise ; lecture seule |
| Partitionnement Hive détecté | CREATE EXTERNAL TABLE DATASET.EXT WITH PARTITION COLUMNS OPTIONS (format = 'PARQUET', uris = ['gs://BUCKET/ventes/*'], hive_partition_uri_prefix = 'gs://BUCKET/ventes', require_hive_partition_filter = TRUE) |
Colonnes déduites de chemins .../jour=2026-06-01/pays=FR/... ; les lister dans WITH PARTITION COLUMNS (jour DATE, pays STRING) pour les typer |
| Table BigLake (accès délégué) | CREATE EXTERNAL TABLE DATASET.EXT WITH CONNECTION `PROJECT_ID.REGION.CONNECTION_ID` OPTIONS (format = 'PARQUET', uris = [...], max_staleness = INTERVAL 4 HOUR, metadata_cache_mode = 'AUTOMATIC') |
Les utilisateurs n'ont plus besoin de droits sur le bucket ; cache de métadonnées. WITH CONNECTION DEFAULT utilise la connexion par défaut |
| Table d'objets (fichiers non structurés) | CREATE EXTERNAL TABLE DATASET.DOCS WITH CONNECTION DEFAULT OPTIONS (object_metadata = 'SIMPLE', uris = ['gs://BUCKET/pdf/*']) |
Une ligne de métadonnées par objet, entrée des fonctions AI.* |
| Table Iceberg gérée | CREATE TABLE DATASET.ICE (id INT64, jour DATE) CLUSTER BY id WITH CONNECTION DEFAULT OPTIONS (file_format = 'PARQUET', table_format = 'ICEBERG', storage_uri = 'gs://BUCKET/ice/') |
Données Parquet dans le bucket du client, DML et streaming complets ; ni COPY, ni CLONE, ni SNAPSHOT, ni renommage |
| Publier l'instantané Iceberg pour d'autres moteurs | EXPORT TABLE METADATA FROM DATASET.ICE |
Métadonnées Iceberg V2 écrites sous storage_uri, lisibles par Spark ; facturé en slots Enterprise |
Fonctions, procédures, sécurité par ligne
| Objectif | Syntaxe | Résultat |
|---|---|---|
| UDF SQL persistante | CREATE OR REPLACE FUNCTION DATASET.ttc(ht NUMERIC, taux NUMERIC) RETURNS NUMERIC AS (ht * (1 + taux)) |
Appel DATASET.ttc(prix, 0.2) depuis toute requête |
| UDF temporaire | CREATE TEMP FUNCTION norm(s STRING) AS (LOWER(TRIM(s))); |
Vit le temps de la requête ; nom sans dataset |
| UDF JavaScript | CREATE TEMP FUNCTION f(x FLOAT64) RETURNS FLOAT64 LANGUAGE js AS r"""return x * 2;"""; |
RETURNS obligatoire ; plus lente qu'une UDF SQL |
| UDF Python | CREATE FUNCTION DATASET.f(x FLOAT64) RETURNS FLOAT64 LANGUAGE python OPTIONS (entry_point = 'f', runtime_version = 'python-3.11', packages = ['pandas==2.2']) AS r"""...""" |
Exécutée dans un conteneur géré |
| Fonction distante | CREATE FUNCTION DATASET.f(x STRING) RETURNS STRING REMOTE WITH CONNECTION `PROJECT_ID.REGION.CONNECTION_ID` OPTIONS (endpoint = 'https://...') |
Appelle un service Cloud Run par lots de lignes |
| Fonction de table (TVF) | CREATE OR REPLACE TABLE FUNCTION DATASET.ventes_de(j DATE) AS SELECT * FROM DATASET.VENTES WHERE jour = j |
SELECT * FROM DATASET.ventes_de('2026-06-01') : une vue paramétrée |
| Procédure stockée | CREATE OR REPLACE PROCEDURE DATASET.purge(IN jours INT64, OUT nb INT64) BEGIN ... END |
Script SQL réutilisable, appelé par CALL |
| Politique d'accès aux lignes | CREATE ROW ACCESS POLICY fr ON DATASET.TABLE GRANT TO ("group:equipe-fr@example.com") FILTER USING (pays = 'FR') |
Chaque groupe ne voit que ses lignes ; qui n'est couvert par aucune politique ne voit rien |
| Politique par utilisateur | FILTER USING (email = SESSION_USER()) |
Chacun voit ses propres lignes |
| Retirer les politiques | DROP ROW ACCESS POLICY fr ON DATASET.TABLE ; DROP ALL ROW ACCESS POLICIES ON DATASET.TABLE |
La dernière politique d'une table ne part qu'avec DROP ALL |
Contexte : BigQuery : organiser les tables, GoogleSQL : DML et transactions, Lakehouse : tables ouvertes et gouvernance, BigQuery.