Utiliser des instructions de langage de définition de données (LDD)

Les instructions de langage de définition de données (Data definition language, DDL) permettent de créer et de modifier des ressources BigQuery à l'aide de la syntaxe de requête SQL standard. Dans BigQuery, à l'heure actuelle, les commandes LDD vous permettent de :

Exécuter des instructions LDD

Vous pouvez exécuter des instructions LDD en utilisant Cloud Console ou l'outil de ligne de commande bq, en appelant l'API REST jobs.query, ou de manière automatisée à l'aide des bibliothèques clientes de l'API BigQuery.

Console

  1. Accédez à la page "BigQuery" de Cloud Console.

    Accéder à BigQuery

  2. Cliquez sur Saisir une nouvelle requête.

    Saisissez une nouvelle requête.

  3. Saisissez l'instruction LDD dans la zone de texte de l'éditeur de requête. Exemple :

     CREATE TABLE mydataset.newtable ( x INT64 )
     

  4. Cliquez sur Exécuter.

bq

Saisissez la commande bq query et indiquez l'instruction LDD comme paramètre de requête. Définissez l'indicateur use_legacy_sql sur false.

bq query --use_legacy_sql=false \
  'CREATE TABLE mydataset.newtable ( x INT64 )'

API

Appelez la méthode jobs.query et indiquez l'instruction LDD dans la propriété query du corps de la requête.

La fonctionnalité LDD complète les informations renvoyées par une ressource de tâches. statistics.query.statementType inclut les valeurs supplémentaires suivantes pour la compatibilité LDD :

  • CREATE_TABLE
  • CREATE_TABLE_AS_SELECT
  • DROP_TABLE
  • CREATE_VIEW
  • DROP_VIEW

statistics.query comporte deux champs supplémentaires :

  • ddlOperationPerformed : opération LDD effectuée, éventuellement dépendante de l'existence de la cible LDD. Les valeurs actuelles incluent :
    • CREATE : la requête a créé la cible LDD.
    • SKIP : aucune opération. Exemples : l'instruction CREATE TABLE IF NOT EXISTS a été envoyée et la table existe. Ou l'instruction DROP TABLE IF EXISTS a été envoyée et la table n'existe pas.
    • REPLACE : la requête a remplacé la cible LDD. Exemple : l'instruction CREATE OR REPLACE TABLE a été envoyée et la table existe déjà.
    • DROP : la requête a supprimé la cible LDD.
  • ddlTargetTable : lorsque vous envoyez une instruction CREATE TABLE/VIEW ou une instruction DROP TABLE/VIEW, la table cible est renvoyée sous la forme d'un objet comportant trois champs :
    • "projectId" : chaîne
    • "datasetId" : chaîne
    • "tableId" : chaîne

Java

Appelez la méthode BigQuery.create() pour démarrer une tâche de requête. Appelez la méthode Job.waitFor() pour attendre la fin de la requête LDD.

import com.google.cloud.bigquery.BigQuery;
import com.google.cloud.bigquery.BigQueryException;
import com.google.cloud.bigquery.BigQueryOptions;
import com.google.cloud.bigquery.Job;
import com.google.cloud.bigquery.JobInfo;
import com.google.cloud.bigquery.QueryJobConfiguration;

// Sample to create a view using DDL
public class DDLCreateView {

  public static void runDDLCreateView() {
    // TODO(developer): Replace these variables before running the sample.
    String projectId = "MY_PROJECT_ID";
    String datasetId = "MY_DATASET_ID";
    String tableId = "MY_VIEW_ID";
    String ddl =
        "CREATE VIEW "
            + "`"
            + projectId
            + "."
            + datasetId
            + "."
            + tableId
            + "`"
            + " OPTIONS("
            + " expiration_timestamp=TIMESTAMP_ADD("
            + " CURRENT_TIMESTAMP(), INTERVAL 48 HOUR),"
            + " friendly_name=\"new_view\","
            + " description=\"a view that expires in 2 days\","
            + " labels=[(\"org_unit\", \"development\")]"
            + " )"
            + " AS SELECT name, state, year, number"
            + " FROM `bigquery-public-data.usa_names.usa_1910_current`"
            + " WHERE state LIKE 'W%'`";
    ddlCreateView(ddl);
  }

  public static void ddlCreateView(String ddl) {
    try {
      // Initialize client that will be used to send requests. This client only needs to be created
      // once, and can be reused for multiple requests.
      BigQuery bigquery = BigQueryOptions.getDefaultInstance().getService();

      QueryJobConfiguration config = QueryJobConfiguration.newBuilder(ddl).build();

      // create a view using query and it will wait to complete job.
      Job job = bigquery.create(JobInfo.of(config));
      job = job.waitFor();
      if (job.isDone()) {
        System.out.println("View created successfully");
      } else {
        System.out.println("View was not created");
      }
    } catch (BigQueryException | InterruptedException e) {
      System.out.println("View was not created. \n" + e.toString());
    }
  }
}

Node.js

// Import the Google Cloud client library and create a client
const {BigQuery} = require('@google-cloud/bigquery');
const bigquery = new BigQuery();

async function ddlCreateView() {
  // Creates a view via a DDL query

  /**
   * TODO(developer): Uncomment the following lines before running the sample.
   */
  // const projectId = "my_project"
  // const datasetId = "my_dataset"
  // const tableId = "my_new_view"

  const query = `
  CREATE VIEW \`${projectId}.${datasetId}.${tableId}\`
  OPTIONS(
      expiration_timestamp=TIMESTAMP_ADD(
          CURRENT_TIMESTAMP(), INTERVAL 48 HOUR),
      friendly_name="new_view",
      description="a view that expires in 2 days",
      labels=[("org_unit", "development")]
  )
  AS SELECT name, state, year, number
      FROM \`bigquery-public-data.usa_names.usa_1910_current\`
      WHERE state LIKE 'W%'`;

  // For all options, see https://cloud.google.com/bigquery/docs/reference/rest/v2/jobs/query
  const options = {
    query: query,
  };

  // Run the query as a job
  const [job] = await bigquery.createQueryJob(options);

  job.on('complete', metadata => {
    console.log(`Created new view ${tableId} via job ${metadata.id}`);
  });
}

Python

Appelez la méthode Client.query() pour démarrer une tâche de requête. Appelez la méthode QueryJob.result() pour attendre la fin de la requête LDD.

# from google.cloud import bigquery
# project = 'my-project'
# dataset_id = 'my_dataset'
# table_id = 'new_view'
# client = bigquery.Client(project=project)

sql = """
CREATE VIEW `{}.{}.{}`
OPTIONS(
    expiration_timestamp=TIMESTAMP_ADD(
        CURRENT_TIMESTAMP(), INTERVAL 48 HOUR),
    friendly_name="new_view",
    description="a view that expires in 2 days",
    labels=[("org_unit", "development")]
)
AS SELECT name, state, year, number
    FROM `bigquery-public-data.usa_names.usa_1910_current`
    WHERE state LIKE 'W%'
""".format(
    project, dataset_id, table_id
)

job = client.query(sql)  # API request.
job.result()  # Waits for the query to finish.

print(
    'Created new view "{}.{}.{}".'.format(
        job.destination.project,
        job.destination.dataset_id,
        job.destination.table_id,
    )
)

Instruction CREATE SCHEMA

Crée un ensemble de données.

Pour exécuter cette instruction, vous devez disposer des autorisations bigquery.datasets.create. L'ensemble de données est créé à l'emplacement que vous spécifiez dans les paramètres de requête. Pour en savoir plus, consultez la page Spécifier votre zone.

Pour en savoir plus sur la création d'un ensemble de données, consultez la page Créer des ensembles de données. Pour plus d'informations sur les quotas, consultez la section Limites des ensembles de données.

CREATE SCHEMA [IF NOT EXISTS]
[project_name.]dataset_name
[OPTIONS(schema_option_list)]

Où :

  • IF NOT EXISTS : si vous incluez cette clause et que l'ensemble de données existe déjà, l'instruction réussit sans action. Si vous omettez cette clause et que l'ensemble de données existe déjà, l'instruction renvoie une erreur.

  • project_name est le nom du projet dans lequel vous créez l'ensemble de données. Sa valeur par défaut correspond au projet qui exécute cette requête LDD.

  • dataset_name est le nom de l'ensemble de données à créer.

  • schema_option_list spécifie une liste d'options pour la création de l'ensemble de données.

schema_option_list

La liste d'options spécifie des options pour l'ensemble de données. Spécifiez les options au format suivant : NAME=VALUE, ...

Les options suivantes sont compatibles :

NAME VALUE Détails
default_kms_key_name STRING Spécifie la clé Cloud KMS par défaut pour le chiffrement des données de table de cet ensemble de données. Vous pouvez remplacer cette valeur lorsque vous créez une table.
default_partition_expiration_days FLOAT64 Spécifie le délai d'expiration par défaut, en jours, pour les partitions de tables de cet ensemble de données. Vous pouvez remplacer cette valeur lorsque vous créez une table.
default_table_expiration_days FLOAT64 Spécifie le délai d'expiration par défaut, en jours, pour les tables de cet ensemble de données. Vous pouvez remplacer cette valeur lorsque vous créez une table.
description STRING Description de l'ensemble de données.
friendly_name STRING Nom descriptif de l'ensemble de données.
labels <ARRAY<STRUCT<STRING, STRING>>> Tableau de libellés de l'ensemble de données, exprimé sous forme de paires clé/valeur.

Exemple

L'exemple suivant crée un ensemble de données avec un délai d'expiration de table par défaut et un ensemble d'étiquettes.

CREATE SCHEMA mydataset
OPTIONS(
  default_table_expiration_days=3.75,
  labels=[("label1","value1"),("label2","value2")]
  )

Instruction CREATE TABLE

Pour créer une table dans BigQuery, utilisez l'instruction LDD CREATE TABLE.

{CREATE TABLE | CREATE TABLE IF NOT EXISTS | CREATE OR REPLACE TABLE}
[[project_name.]dataset_name.]table_name
[(
  column_name column_schema[, ...]
)]
[PARTITION BY partition_expression]
[CLUSTER BY clustering_column_list]
[OPTIONS(table_option_list)]
[AS query_statement]

Où :

{CREATE TABLE | CREATE TABLE IF NOT EXISTS | CREATE OR REPLACE TABLE} correspond à l'une des instructions suivantes :

  • CREATE TABLE : crée une table.
  • CREATE TABLE IF NOT EXISTS : crée une table uniquement si la table n'existe pas dans l'ensemble de données spécifié.
  • CREATE OR REPLACE TABLE : crée une table et remplace une table existante portant le même nom dans l'ensemble de données spécifié.

Les instructions CREATE TABLE doivent respecter les règles suivantes :

  • Chaque requête ne peut contenir qu'une seule instruction CREATE.
  • La liste des colonnes ou la clause as query_statement ou bien les deux doivent être présentes.
  • Lorsque la liste des colonnes et la clause as query_statement sont toutes deux présentes, BigQuery ignore les noms dans la clause as query_statement et met les colonnes en correspondance avec leur position dans la liste des colonnes.
  • Lorsque seule la clause as query_statement est présente, BigQuery détermine le nom et le type des colonnes à l'aide de la clause as query_statement.
  • Les noms des colonnes doivent être spécifiés soit avec la liste des colonnes, soit à l'aide de la clause as query_statement.
  • Les noms de colonnes en double ne sont pas autorisés.

Chemin d'accès à la table

project_name est le nom du projet dans lequel vous créez la table. Sa valeur par défaut correspond au projet qui exécute cette requête LDD. Si le nom du projet contient des caractères spéciaux tels que des signes deux-points, il doit être placé entre accents graves ` (exemple : `google.com:my_project`).

dataset_name est le nom de l'ensemble de données dans lequel vous créez la table. Sa valeur par défaut correspond à l'ensemble de données par défaut (defaultDataset) dans la requête.

table_name est le nom de la table que vous créez.

Lorsque vous créez une table dans BigQuery, le nom de la table doit être unique pour chaque ensemble de données. Le nom de la table peut :

  • contenir jusqu'à 1 024 caractères ;
  • contenir des caractères Unicode des catégories L (lettre), M (marque), N (nombre), Pc (ponctuation de type connecteur, y compris trait de soulignement), Pd (ponctuation de type tiret), Zs (espace). Pour en savoir plus, consultez la section décrivant les catégories générales.

Par exemple, tous les noms de table suivants sont valides : table-01, ग्राहक, 00_お客様, étudiant.

Certains noms de tables et préfixes de noms de tables sont réservés. Si vous recevez une erreur indiquant que le nom ou le préfixe de votre table est réservé, sélectionnez-en un autre et réessayez.

column_name et column_schema

(column_name column_schema[, ...]) contient les informations de schéma de la table dans une liste d'éléments séparés par des virgules :

  • column_name correspond au nom de la colonne. Un nom de colonne :
    • ne doit contenir que des lettres (a-z, A-Z), des chiffres (0-9) ou des traits de soulignement (_) ;
    • doit commencer par une lettre ou un trait de soulignement ;
    • Peut contenir jusqu'à 300 caractères.
  • column_schema est semblable à un type de données, mais il accepte une contrainte NOT NULL facultative pour les types autres que ARRAY. column_schema est également compatible avec des options sur les colonnes de premier niveau et sur les champs STRUCT.
column_schema :=
   {simple_type [NOT NULL] |
    STRUCT<field_list> [NOT NULL] |
    ARRAY<array_element_schema>}
   [OPTIONS(column_option_list)]

field_list := field_name column_schema [, ...]

array_element_schema := {simple_type | STRUCT<field_list>} [NOT NULL]

simple_type correspond à tout type de données compatible en dehors de STRUCT et ARRAY.

field_name correspond au nom du champ "struct". Les noms de champs "struct" sont soumis aux mêmes restrictions que les noms de colonnes.

Lorsque la contrainte NOT NULL est présente pour une colonne ou un champ, la colonne ou le champ sont créés avec le mode REQUIRED. Inversement, lorsque la contrainte NOT NULL est absente, la colonne ou le champ sont créés avec le mode NULLABLE.

Les colonnes et les champs de type ARRAY ne sont pas compatibles avec le modificateur NOT NULL. Par exemple, un column_schema correspondant à ARRAY<INT64> NOT NULL n'est pas valide, car les colonnes ARRAY présentent le mode REPEATED et peuvent être vides, mais ne peuvent pas être NULL. Un élément de tableau dans une table ne peut jamais être NULL, que la contrainte NOT NULL soit spécifiée ou non. Par exemple, ARRAY<INT64> correspond à ARRAY<INT64 NOT NULL>.

L'attribut NOT NULL du champ column_schema d'une table ne se propage pas dans le reste de la table via les requêtes. Par exemple, si la table T contient une colonne déclarée comme x INT64 NOT NULL, CREATE TABLE dataset.newtable AS SELECT x FROM T crée une table nommée dataset.newtable dans laquelle x est NULLABLE.

column_schema ne peut être utilisé que dans la liste des définitions de colonnes des instructions CREATE TABLE. Il ne peut pas être utilisé comme type à l'intérieur des expressions. Par exemple, CAST(1 AS INT64 NOT NULL) n'est pas valide.

partition_expression

PARTITION BY est une clause facultative qui contrôle le partitionnement de table. partition_expression est une expression qui détermine comment partitionner la table. L'expression de partition peut contenir les valeurs suivantes :

  • _PARTITIONDATE. Partitionner par date d'ingestion avec des partitions quotidiennes. Cette syntaxe ne peut pas être utilisée avec la clause AS query_statement.
  • DATE(_PARTITIONTIME). Équivaut à _PARTITIONDATE. Cette syntaxe ne peut pas être utilisée avec la clause AS query_statement.
  • <date_column>. Partitionner en fonction d'une colonne DATE avec des partitions quotidiennes.
  • DATE({ <timestamp_column> | <datetime_column> }). Partitionner en fonction d'une colonne TIMESTAMP ou DATETIME avec des partitions quotidiennes.
  • DATETIME_TRUNC(<datetime_column>, { DAY | HOUR | MONTH | YEAR }). Partitionner en fonction d'une colonne DATETIME avec le type de partitionnement spécifié.
  • TIMESTAMP_TRUNC(<timestamp_column>, { DAY | HOUR | MONTH | YEAR }). Partitionner une colonne TIMESTAMP avec le type de partitionnement spécifié.
  • TIMESTAMP_TRUNC(_PARTITIONTIME, { DAY | HOUR | MONTH | YEAR }). Partitionner par date d'ingestion avec le type de partitionnement spécifié. Cette syntaxe ne peut pas être utilisée avec la clause AS query_statement.
  • DATE_TRUNC(<date_column>, { MONTH | YEAR }). Partitionner en fonction d'une colonne DATE avec le type de partitionnement spécifié.
  • RANGE_BUCKET(<int64_column>, GENERATE_ARRAY(<start>, <end>[, <interval>])). Partitionner en fonction d'une colonne d'entiers avec la plage spécifiée, où :

    • start est la valeur de début inclusive du partitionnement par plages.
    • end est la valeur de fin exclusive du partitionnement par plages.
    • interval est la largeur de chaque plage au sein de la partition. La valeur par défaut est 1.

clustering_column_list

CLUSTER BY est une clause facultative qui contrôle le clustering des tables. clustering_column_list est une liste d'éléments séparés par des virgules qui détermine la façon de procéder au clustering de la table. Cette liste peut contenir jusqu'à quatre noms de colonnes à mettre en cluster.

table_option_list

La liste d'options vous permet de définir des options de table, telles qu'un libellé et une date/heure d'expiration. Vous pouvez inclure plusieurs options dans une liste d'éléments séparés par des virgules.

Spécifiez les listes d'options de table au format suivant :

NAME=VALUE, ...

NAME et VALUE doivent être utilisées selon l'une des combinaisons suivantes :

NAME VALUE Détails
expiration_timestamp TIMESTAMP

Exemple : expiration_timestamp=TIMESTAMP "2025-01-01 00:00:00 UTC"

Cette propriété est équivalente à la propriété de ressource de table expirationTime.

partition_expiration_days

FLOAT64

Exemple : partition_expiration_days=7

Cette propriété est équivalente à la propriété de ressource de table timePartitioning.expirationMs mais utilise des jours au lieu de millisecondes. Un jour équivaut à 86 400 000 millisecondes, soit 24 heures.

Cette propriété ne peut être définie que si la table est partitionnée.

require_partition_filter

BOOL

Exemple : require_partition_filter=true

Cette propriété est équivalente à la propriété de ressource de table timePartitioning.requirePartitionFilter.

Cette propriété ne peut être définie que si la table est partitionnée.

kms_key_name

STRING

Exemple : kms_key_name="projects/project_id/locations/location/keyRings/keyring/cryptoKeys/key"

Cette propriété est équivalente à la propriété de ressource de table encryptionConfiguration.kmsKeyName.

En savoir plus sur la protection des données avec des clés Cloud KMS

friendly_name

STRING

Exemple : friendly_name="my_table"

Cette propriété est équivalente à la propriété de ressource de table friendlyName.

description

STRING

Exemple : description="a table that expires in 2025"

Cette propriété est équivalente à la propriété de ressource de table description.

labels

ARRAY<STRUCT<STRING, STRING>>

Exemple : labels=[("org_unit", "development")]

Cette propriété est équivalente à la propriété de ressource de table labels.

VALUE est une expression constante ne contenant que des littéraux, des paramètres de requête et des fonctions scalaires. Si l'expression constante renvoie la valeur null, l'option NAME correspondante est ignorée.

L'expression constante ne peut pas contenir les éléments suivants :

  • Une référence à une table
  • Des sous-requêtes ou des instructions SQL telles que SELECT, CREATE et UPDATE
  • Des fonctions définies par l'utilisateur, des fonctions d'agrégation ou des fonctions d'analyse
  • Les fonctions scalaires suivantes :
    • ARRAY_TO_STRING
    • REPLACE
    • REGEXP_REPLACE
    • RAND
    • FORMAT
    • LPAD
    • RPAD
    • REPEAT
    • SESSION_USER
    • GENERATE_ARRAY
    • GENERATE_DATE_ARRAY

column_option_list

Le paramètre column_option_list dans column_schema permet de spécifier des options de colonne ou de champ facultatives. Les options de colonnes ont la même syntaxe et les mêmes exigences que les options de tables, mais une liste différente de noms (NAME) et de valeurs (VALUE) :

NAME VALUE Détails
description

STRING

Exemple : description="a unique id"

Cette propriété est équivalente à la propriété de ressource de table schema.fields[].description.

query_statement

La clause AS query_statement spécifie la requête à partir de laquelle la table doit être créée. Consultez la documentation de référence sur la syntaxe SQL pour connaître le format compatible pour query_statement.

Limites connues :

  • Il n'est pas possible de créer une table partitionnée par date d'ingestion à partir du résultat d'une requête. Au lieu de cela, créez la table avec une instruction LDD CREATE TABLE, puis insérez-y des données à l'aide d'une instruction LMD INSERT.
  • Il n'est pas possible de remplacer une table par un type de partitionnement différent à l'aide du modificateur OR REPLACE. Au lieu de cela, supprimez (DROP) la table, puis utilisez une instruction CREATE TABLE ... AS SELECT ... pour la recréer.

Tables temporaires

Pour créer une table temporaire, utilisez le mot clé TEMP ou TEMPORARY lorsque vous utilisez l'instruction CREATE TABLE.

Syntaxe

{ CREATE {TEMP|TEMPORARY} TABLE |
  CREATE {TEMP|TEMPORARY} TABLE IF NOT EXISTS |
  CREATE OR REPLACE {TEMP|TEMPORARY} TABLE } ...

À l'exception de l'utilisation de TEMP ou de TEMPORARY, la syntaxe est identique à la syntaxe CREATE TABLE.

Les noms des tables temporaires ne doivent pas être qualifiés. Autrement dit, n'utilisez pas de qualificatif de projet ou d'ensemble de données. Les tables temporaires sont automatiquement créées dans un ensemble de données spécial.

Vous pouvez référencer une table temporaire par son nom pour la durée du script actuel. Pour en savoir plus, consultez la section Créer des scripts en langage SQL standard. Cela inclut les tables créées par une procédure dans le script. Vous ne pouvez pas interroger une table lorsque l'exécution du script dans lequel elle est créée est terminée.

Une fois qu'un script est terminé, la table temporaire peut exister jusqu'à 24 heures. Elle n'est pas enregistrée avec le nom que vous lui avez donné, mais sous un nom aléatoire. Pour afficher la structure et les données de la table, accédez à la console BigQuery, cliquez sur Historique des requêtes, puis choisissez la requête qui a créé la table temporaire. Ensuite, dans la ligne Table de destination, cliquez sur Table temporaire.

Vous ne pouvez pas partager des tables temporaires et elles ne sont pas visibles à l'aide des méthodes "list" standards ou des autres méthodes de manipulation des tables. Le stockage de tables temporaires n'est pas facturé.

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

CREATE TEMP TABLE Example
(
  x INT64,
  y STRING
);

INSERT INTO Example
VALUES (5, 'foo');

INSERT INTO Example
VALUES (6, 'bar');

SELECT *
FROM Example;

Ce script renvoie le résultat suivant :

+-----+---+-----+
| Row | x | y   |
+-----+---|-----+
| 1   | 5 | foo |
| 2   | 6 | bar |
+-----+---|-----+

Vous pouvez supprimer explicitement une table temporaire avant la fin du script en exécutant une instruction DROP TABLE :

CREATE TEMP TABLE foo(x INT64);
SELECT * FROM foo;  -- Succeeds
DROP TABLE foo;
SELECT * FROM foo;  -- Results in an error

Lorsque des tables temporaires sont utilisées avec un ensemble de données par défaut, les noms de table non qualifiés font référence aux éléments suivants :

  • une table temporaire, le cas échéant,
  • ou bien une table dans l'ensemble de données par défaut.

L'exception concerne les instructions CREATE TABLE, où la table cible est considérée comme une table temporaire si et seulement si le mot clé TEMP ou TEMPORARY est présent.

Prenons l'exemple du script suivant :

-- Create table t1 in the default dataset
CREATE TABLE t1 (x INT64);

-- Create temporary table t1
CREATE TEMP TABLE t1 (x INT64);

-- This statement will select from the temporary table
SELECT * FROM t1;

-- Drop the temporary table
DROP TABLE t1;

-- Now that the temporary table is dropped, this statement will select from
-- the table in the default dataset
SELECT * FROM t1;

Vous pouvez indiquer explicitement que vous faites référence à une table temporaire en qualifiant le nom de la table avec _SESSION :

-- Create a temp table
CREATE TEMP TABLE t1 (x INT64);

-- Create a temp table using the `_SESSION` qualifier
CREATE TEMP TABLE _SESSION.t2 (x INT64);

-- Select from a temporary table using the `_SESSION` qualifier
SELECT * FROM _SESSION.t1;

Si vous utilisez le qualificateur _SESSION pour une requête d'une table temporaire qui n'existe pas, vous recevrez un message d'erreur indiquant que la table n'existe pas. Par exemple, s'il n'existe aucune table temporaire appelée t3, vous recevrez ce message d'erreur même si une table nommée t3 existe dans l'ensemble de données par défaut.

Vous ne pouvez pas utiliser _SESSION pour créer une table non-temporaire :

CREATE TABLE _SESSION.t4 (x INT64);  -- Fails

Exemples

Créer une table

L'exemple suivant crée une table partitionnée nommée newtable dans mydataset :

CREATE TABLE mydataset.newtable
(
  x INT64 OPTIONS(description="An optional INTEGER field"),
  y STRUCT<
    a ARRAY<STRING> OPTIONS(description="A repeated STRING field"),
    b BOOL
  >
)
PARTITION BY _PARTITIONDATE
OPTIONS(
  expiration_timestamp=TIMESTAMP "2025-01-01 00:00:00 UTC",
  partition_expiration_days=1,
  description="a table that expires in 2025, with each partition living for 24 hours",
  labels=[("org_unit", "development")]
)

Si vous n'avez pas configuré de projet par défaut, ajoutez un ID de projet au nom de l'ensemble de données dans l'exemple SQL, et placez le nom entre accents graves si project_id contient des caractères spéciaux : `project_id.dataset.table`. Ainsi, au lieu de mydataset.newtable, le qualificatif de table peut être `myproject.mydataset.newtable`.

Si le nom de la table existe dans l'ensemble de données, l'erreur suivante est renvoyée :

Already Exists: project_id:dataset.table

La table est partitionnée à l'aide de la partition_expression suivante : PARTITION BY _PARTITIONDATE. Cette expression partitionne la table en utilisant la date figurant dans la pseudo-colonne _PARTITIONDATE.

Le schéma de la table contient deux colonnes :

  • x : un entier, avec la description "Un champ INTEGER facultatif"
  • y : un type de données STRUCT contenant deux colonnes :

    • a : un tableau de chaînes, avec la description "Un champ STRING répété"
    • b : une valeur booléenne

La liste d'options de table spécifie :

  • les date et heure d'expiration de la table : 1er janvier 2025 à 00:00:00 UTC ;
  • le délai d'expiration de la partition : un jour ;
  • la description : une table qui expire en 2025 ;
  • le libellé : org_unit = development.

Créer une table à partir d'une table existante

L'exemple suivant crée une table nommée top_words dans mydataset à partir d'une requête :

CREATE TABLE mydataset.top_words
OPTIONS(
  description="Top ten words per Shakespeare corpus"
) AS
SELECT
  corpus,
  ARRAY_AGG(STRUCT(word, word_count) ORDER BY word_count DESC LIMIT 10) AS top_words
FROM bigquery-public-data.samples.shakespeare
GROUP BY corpus;

Si vous n'avez pas configuré de projet par défaut, ajoutez un ID de projet au nom de l'ensemble de données dans l'exemple SQL, et placez le nom entre accents graves si project_id contient des caractères spéciaux : `project_id.dataset.table`. Ainsi, au lieu de mydataset.top_words, le qualificatif de table peut être `myproject.mydataset.top_words`.

Si le nom de la table existe dans l'ensemble de données, l'erreur suivante est renvoyée :

Already Exists: project_id:dataset.table

Le schéma de la table contient deux colonnes :

  • corpus : nom d'un corpus de Shakespeare ;
  • top_words : un tableau (ARRAY) de structures (STRUCT) contenant deux champs : word (une chaîne STRING) et word_count (un type de données INT64 représentant le nombre de mots).

La liste d'options de table spécifie :

  • la description : dix mots principaux par corpus de Shakespeare.

Créer une table seulement si la table n'existe pas

L'exemple suivant crée une table nommée newtable dans mydataset uniquement si aucune table nommée newtable n'existe dans mydataset. Si ce nom de table existe dans l'ensemble de données, aucune erreur n'est renvoyée et aucune mesure n'est prise.

CREATE TABLE IF NOT EXISTS mydataset.newtable (x INT64, y STRUCT<a ARRAY<STRING>, b BOOL>)
OPTIONS(
  expiration_timestamp=TIMESTAMP "2025-01-01 00:00:00 UTC",
  description="a table that expires in 2025",
  labels=[("org_unit", "development")]
)

Si vous n'avez pas configuré de projet par défaut, ajoutez un ID de projet au nom de l'ensemble de données dans l'exemple SQL, et placez le nom entre accents graves si project_id contient des caractères spéciaux : `project_id.dataset.table`. Ainsi, au lieu de mydataset.newtable, le qualificatif de table peut être `myproject.mydataset.newtable`.

Le schéma de la table contient deux colonnes :

  • x : un entier
  • y : un type de données STRUCT contenant a (un tableau de chaînes) et b (une valeur booléenne)

La liste d'options de table spécifie :

  • les date et heure d'expiration : 1er janvier 2025 à 00:00:00 UTC ;
  • la description : une table qui expire en 2025 ;
  • le libellé : org_unit = development.

Créer ou remplacer une table

L'exemple suivant crée une table nommée newtable dans mydataset. Si newtable existe déjà dans mydataset, elle est remplacée par une table vide.

CREATE OR REPLACE TABLE mydataset.newtable (x INT64, y STRUCT<a ARRAY<STRING>, b BOOL>)
OPTIONS(
  expiration_timestamp=TIMESTAMP "2025-01-01 00:00:00 UTC",
  description="a table that expires in 2025",
  labels=[("org_unit", "development")]
)

Si vous n'avez pas configuré de projet par défaut, ajoutez un ID de projet au nom de l'ensemble de données dans l'exemple SQL, et placez le nom entre accents graves si project_id contient des caractères spéciaux : `project_id.dataset.table`. Ainsi, au lieu de mydataset.newtable, le qualificatif de table peut être `myproject.mydataset.newtable`.

Le schéma de la table contient deux colonnes :

  • x : un entier
  • y : un type de données STRUCT contenant a (un tableau de chaînes) et b (une valeur booléenne)

La liste d'options de table spécifie :

  • les date et heure d'expiration : 1er janvier 2025 à 00:00:00 UTC ;
  • la description : une table qui expire en 2025 ;
  • le libellé : org_unit = development.

Créer une table avec des colonnes REQUIRED

L'exemple suivant crée une table nommée newtable dans mydataset. Le modificateur NOT NULL dans la liste de définitions de colonnes d'une instruction CREATE TABLE indique qu'une colonne ou un champ est créé en mode REQUIRED.

CREATE TABLE my_dataset.new_table (
  x INT64 NOT NULL,
  y STRUCT<
    a ARRAY<STRING>,
    b BOOL NOT NULL,
    c FLOAT64
  > NOT NULL,
  z STRING
)

Si vous n'avez pas configuré de projet par défaut, ajoutez un ID de projet au nom de l'ensemble de données dans l'exemple SQL, et placez le nom entre accents graves si project_id contient des caractères spéciaux : `project_id.dataset.table`. Ainsi, au lieu de my_dataset.new_table, le qualificatif de table peut être `myproject.my_dataset.new_table`.

Si le nom de la table existe dans l'ensemble de données, l'erreur suivante est renvoyée :

Already Exists: project_id:dataset.table

Le schéma de la table contient trois colonnes :

  • x : un entier REQUIRED
  • y : un type de données STRUCT REQUIRED contenant a (un tableau de chaînes), b (une valeur booléenne REQUIRED) et c (un nombre à virgule flottante NULLABLE)
  • z : une chaîne NULLABLE

Créer une table partitionnée

L'exemple suivant crée une table partitionnée nommée newtable dans mydataset à l'aide d'une colonne DATE :

CREATE TABLE mydataset.newtable (transaction_id INT64, transaction_date DATE)
PARTITION BY transaction_date
OPTIONS(
  partition_expiration_days=3,
  description="a table partitioned by transaction_date"
)

Si vous n'avez pas configuré de projet par défaut, ajoutez un ID de projet au nom de l'ensemble de données dans l'exemple SQL, et placez le nom entre accents graves si project_id contient des caractères spéciaux : `project_id.dataset.table`. Ainsi, au lieu de mydataset.newtable, le qualificatif de table peut être `myproject.mydataset.newtable`.

Le schéma de la table contient deux colonnes :

  • transaction_id : un entier
  • date_transaction : une date

La liste d'options de table spécifie :

  • le délai d'expiration de la partition : trois jours ;
  • la description : une table partitionnée par transaction_date.

Créer une table partitionnée à partir d'un résultat de requête

L'exemple suivant crée une table partitionnée nommée days_with_rain dans mydataset à l'aide d'une colonne DATE :

CREATE TABLE mydataset.days_with_rain
PARTITION BY date
OPTIONS (
  partition_expiration_days=365,
  description="weather stations with precipitation, partitioned by day"
) AS
SELECT
  DATE(CAST(year AS INT64), CAST(mo AS INT64), CAST(da AS INT64)) AS date,
  (SELECT ANY_VALUE(name) FROM `bigquery-public-data.noaa_gsod.stations` AS stations
   WHERE stations.usaf = stn) AS station_name,  -- Stations can have multiple names
  prcp
FROM `bigquery-public-data.noaa_gsod.gsod2017` AS weather
WHERE prcp != 99.9  -- Filter unknown values
  AND prcp > 0      -- Filter stations/days with no precipitation

Si vous n'avez pas configuré de projet par défaut, ajoutez un ID de projet au nom de l'ensemble de données dans l'exemple SQL, et placez le nom entre accents graves si project_id contient des caractères spéciaux : `project_id.dataset.table`. Ainsi, au lieu de mydataset.days_with_rain, le qualificatif de table peut être `myproject.mydataset.days_with_rain`.

Le schéma de la table contient deux colonnes :

  • date : la DATE de collecte des données
  • station_name : le nom de la station météo sous forme de chaîne (STRING)
  • prcp : la quantité de précipitations en pouces, au format FLOAT64

La liste d'options de table spécifie :

  • le délai d'expiration de la partition : un an ;
  • la description : stations météorologiques avec précipitations, partitionnées par jour.

Créer une table en cluster

Exemple 1

L'exemple suivant crée une table en cluster nommée myclusteredtable dans mydataset. La table est une table partitionnée suivant une colonne TIMESTAMP et mise en cluster par une colonne STRING nommée customer_id.

CREATE TABLE mydataset.myclusteredtable
(
  timestamp TIMESTAMP,
  customer_id STRING,
  transaction_amount NUMERIC
)
PARTITION BY DATE(timestamp)
CLUSTER BY customer_id
OPTIONS (
  partition_expiration_days=3,
  description="a table clustered by customer_id"
)

Si vous n'avez pas configuré de projet par défaut, ajoutez un ID de projet au nom de l'ensemble de données dans l'exemple SQL, et placez le nom entre accents graves si project_id contient des caractères spéciaux : `project_id.dataset.table`. Ainsi, au lieu de mydataset.myclusteredtable, le qualificatif de table peut être `myproject.mydataset.myclusteredtable`.

Le schéma de la table contient trois colonnes :

  • timestamp : heure de la collecte des données sous forme d'horodatage (TIMESTAMP)
  • customer_id : ID client sous forme de chaîne (STRING)
  • transaction_amount : montant de la transaction au format numérique (NUMERIC)

La liste d'options de table spécifie :

  • le délai d'expiration de la partition : trois jours ;
  • la description : une table mise en cluster par customer_id.
Exemple 2

L'exemple suivant crée une table en cluster nommée myclusteredtable dans mydataset. La table est une table partitionnée par date d'ingestion.

CREATE TABLE mydataset.myclusteredtable
(
  customer_id STRING,
  transaction_amount NUMERIC
)
PARTITION BY DATE(_PARTITIONTIME)
CLUSTER BY
  customer_id
OPTIONS (
  partition_expiration_days=3,
  description="a table clustered by customer_id"
)

Si vous n'avez pas configuré de projet par défaut, ajoutez un ID de projet au nom de l'ensemble de données dans l'exemple SQL, et placez le nom entre accents graves si project_id contient des caractères spéciaux : `project_id.dataset.table`. Ainsi, au lieu de mydataset.myclusteredtable, le qualificatif de table peut être `myproject.mydataset.myclusteredtable`.

Le schéma de la table contient deux colonnes :

  • customer_id : ID client sous forme de chaîne (STRING)
  • transaction_amount : montant de la transaction au format numérique (NUMERIC)

La liste d'options de table spécifie :

  • le délai d'expiration de la partition : trois jours ;
  • la description : une table mise en cluster par customer_id.
Exemple 3

L'exemple suivant crée une table en cluster nommée myclusteredtable dans mydataset. La table n'est pas partitionnée.

CREATE TABLE mydataset.myclusteredtable
(
  customer_id STRING,
  transaction_amount NUMERIC
)
CLUSTER BY
  customer_id
OPTIONS (
  description="a table clustered by customer_id"
)

Si vous n'avez pas configuré de projet par défaut, ajoutez un ID de projet au nom de l'ensemble de données dans l'exemple SQL, et placez le nom entre accents graves si project_id contient des caractères spéciaux : `project_id.dataset.table`. Ainsi, au lieu de mydataset.myclusteredtable, le qualificatif de table peut être `myproject.mydataset.myclusteredtable`.

Le schéma de la table contient deux colonnes :

  • customer_id : ID client sous forme de chaîne (STRING)
  • transaction_amount : montant de la transaction au format numérique (NUMERIC)

La liste d'options de table spécifie :

  • la description : une table mise en cluster par customer_id.

Créer une table en cluster à partir d'un résultat de requête

Exemple 1

L'exemple suivant crée une table en cluster nommée myclusteredtable dans mydataset en utilisant le résultat d'une requête. La table est une table partitionnée suivant une colonne TIMESTAMP.

CREATE TABLE mydataset.myclusteredtable
(
  timestamp TIMESTAMP,
  customer_id STRING,
  transaction_amount NUMERIC
)
PARTITION BY DATE(timestamp)
CLUSTER BY
  customer_id
OPTIONS (
  partition_expiration_days=3,
  description="a table clustered by customer_id"
)
AS SELECT * FROM mydataset.myothertable

Si vous n'avez pas configuré de projet par défaut, ajoutez un ID de projet au nom de l'ensemble de données dans l'exemple SQL, et placez le nom entre accents graves si project_id contient des caractères spéciaux : `project_id.dataset.table`. Ainsi, au lieu de mydataset.myclusteredtable, le qualificatif de table peut être `myproject.mydataset.myclusteredtable`.

Le schéma de la table contient trois colonnes :

  • timestamp : heure de la collecte des données sous forme d'horodatage (TIMESTAMP)
  • customer_id : ID client sous forme de chaîne (STRING)
  • transaction_amount : montant de la transaction au format numérique (NUMERIC)

La liste d'options de table spécifie :

  • le délai d'expiration de la partition : trois jours ;
  • la description : une table mise en cluster par customer_id.
Exemple 2

L'exemple suivant crée une table en cluster nommée myclusteredtable dans mydataset en utilisant le résultat d'une requête. La table n'est pas partitionnée.

CREATE TABLE mydataset.myclusteredtable
(
  customer_id STRING,
  transaction_amount NUMERIC
)
CLUSTER BY
  customer_id
OPTIONS (
  description="a table clustered by customer_id"
)
AS SELECT * FROM mydataset.myothertable

Si vous n'avez pas configuré de projet par défaut, ajoutez un ID de projet au nom de l'ensemble de données dans l'exemple SQL, et placez le nom entre accents graves si project_id contient des caractères spéciaux : `project_id.dataset.table`. Ainsi, au lieu de mydataset.myclusteredtable, le qualificatif de table peut être `myproject.mydataset.myclusteredtable`.

Le schéma de la table contient deux colonnes :

  • customer_id : ID client sous forme de chaîne (STRING)
  • transaction_amount : montant de la transaction au format numérique (NUMERIC)

La liste d'options de table spécifie :

  • la description : une table mise en cluster par customer_id.

Instruction CREATE VIEW

Pour créer une vue dans BigQuery, utilisez l'instruction LDD CREATE VIEW.

{CREATE VIEW | CREATE VIEW IF NOT EXISTS | CREATE OR REPLACE VIEW}
[[project_name.]dataset_name.]view_name
[OPTIONS(view_option_list)]
AS query_expression

Où :

{CREATE VIEW | CREATE VIEW IF NOT EXISTS | CREATE OR REPLACE VIEW} correspond à l'une des instructions suivantes :

  • CREATE VIEW : crée une vue.
  • CREATE VIEW IF NOT EXISTS : crée une vue uniquement si la vue n'existe pas dans l'ensemble de données spécifié.
  • CREATE OR REPLACE VIEW : crée une vue et remplace une vue existante portant le même nom dans l'ensemble de données spécifié.

project_name est le nom du projet dans lequel vous créez la vue. Sa valeur par défaut correspond au projet qui exécute cette requête LDD. Si le nom du projet contient des caractères spéciaux tels que des signes deux-points, il doit être placé entre accents graves ` (exemple : `google.com:my_project`).

dataset_name est le nom de l'ensemble de données dans lequel vous créez la vue. Sa valeur par défaut correspond à l'ensemble de données par défaut (defaultDataset) dans la requête.

view_name est le nom de la vue que vous créez. Le nom de la vue doit être unique pour chaque ensemble de données. Le nom de la vue peut :

  • contenir jusqu'à 1 024 caractères ;
  • contenir des lettres (majuscules ou minuscules), des chiffres et des traits de soulignement.

view_option_list permet de spécifier d'autres options de création de vues, telles qu'un libellé et une date d'expiration.

Les instructions CREATE VIEW doivent respecter les règles suivantes :

  • Chaque requête ne peut contenir qu'une seule instruction CREATE.

query_expression est l'expression de requête SQL standard utilisée pour définir la vue.

view_option_list

La liste d'options vous permet de définir des options de vue, telles qu'un libellé et une date/heure d'expiration. Vous pouvez inclure plusieurs options dans une liste d'éléments séparés par des virgules.

Spécifiez les listes d'options de vue au format suivant :

NAME=VALUE, ...

NAME et VALUE doivent être utilisées selon l'une des combinaisons suivantes :

NAME VALUE Détails
expiration_timestamp TIMESTAMP

Exemple : expiration_timestamp=TIMESTAMP "2025-01-01 00:00:00 UTC"

Cette propriété est équivalente à la propriété de ressource de table expirationTime.

friendly_name

STRING

Exemple : friendly_name="my_view"

Cette propriété est équivalente à la propriété de ressource de table friendlyName.

description

STRING

Exemple : description="a view that expires in 2025"

Cette propriété est équivalente à la propriété de ressource de table description.

labels

ARRAY<STRUCT<STRING, STRING>>

Exemple : labels=[("org_unit", "development")]

Cette propriété est équivalente à la propriété de ressource de table labels.

VALUE est une expression constante ne contenant que des littéraux, des paramètres de requête et des fonctions scalaires. Si l'expression constante renvoie la valeur null, l'option NAME correspondante est ignorée.

L'expression constante ne peut pas contenir les éléments suivants :

  • Une référence à une table
  • Des sous-requêtes ou des instructions SQL telles que SELECT, CREATE et UPDATE
  • Des fonctions définies par l'utilisateur, des fonctions d'agrégation ou des fonctions d'analyse
  • Les fonctions scalaires suivantes :
    • ARRAY_TO_STRING
    • REPLACE
    • REGEXP_REPLACE
    • RAND
    • FORMAT
    • LPAD
    • RPAD
    • REPEAT
    • SESSION_USER
    • GENERATE_ARRAY
    • GENERATE_DATE_ARRAY

Projet par défaut dans le corps des vues

Si la vue est créée dans le même projet que celui utilisé pour exécuter l'instruction CREATE VIEW, l'expression de requête (query_expression) du corps de la vue peut référencer des entités sans spécifier le projet. Le projet par défaut est le projet propriétaire de la vue. Prenons l'exemple de requête ci-dessous.

CREATE VIEW myProject.myDataset.myView AS SELECT * FROM anotherDataset.myTable;

Après avoir exécuté la requête CREATE VIEW ci-dessus dans le projet myProject, vous pouvez exécuter la requête SELECT * FROM myProject.myDataset.myView. Quel que soit le projet avec lequel vous souhaitez exécuter cette requête SELECT, la table référencée anotherDataset.myTable est toujours résolue avec le projet myProject.

Si la vue n'est pas créée dans le même projet que celui utilisé pour exécuter l'instruction CREATE VIEW, toutes les références présentes dans l'expression de requête (query_expression) du corps de la vue doivent inclure les ID de projet. Ainsi, l'exemple de requête CREATE VIEW précédent n'est pas valide s'il s'exécute dans un projet différent de myProject.

Exemples

Créer une vue

L'exemple suivant crée une vue nommée newview dans mydataset :

CREATE VIEW `myproject.mydataset.newview`
OPTIONS(
  expiration_timestamp=TIMESTAMP_ADD(CURRENT_TIMESTAMP(), INTERVAL 48 HOUR),
  friendly_name="newview",
  description="a view that expires in 2 days",
  labels=[("org_unit", "development")]
)
AS SELECT column_1, column_2, column_3 FROM `myproject.mydataset.mytable`

Si le nom de la vue existe déjà dans l'ensemble de données, l'erreur suivante est renvoyée :

Already Exists: project_id:dataset.table

La vue est définie à l'aide de la requête SQL standard suivante :

SELECT column_1, column_2, column_3 FROM `myproject.mydataset.mytable`

La liste d'options de vue spécifie :

  • les date/heure d'expiration : 48 heures à partir de la création de la vue ;
  • le nom descriptif : newview ;
  • la description : une vue qui expire dans deux jours ;
  • le libellé : org_unit = development.

Créer une vue seulement si la vue n'existe pas déjà

L'exemple suivant crée une vue nommée newview dans mydataset uniquement si aucune vue nommée newview n'existe dans mydataset. Si le nom de la vue existe déjà dans l'ensemble de données, aucune erreur n'est renvoyée et aucune action n'est effectuée.

CREATE VIEW IF NOT EXISTS `myproject.mydataset.newview`
OPTIONS(
  expiration_timestamp=TIMESTAMP_ADD(CURRENT_TIMESTAMP(), INTERVAL 48 HOUR),
  friendly_name="newview",
  description="a view that expires in 2 days",
  labels=[("org_unit", "development")]
)
AS SELECT column_1, column_2, column_3 FROM `myproject.mydataset.mytable`

La vue est définie à l'aide de la requête SQL standard suivante :

SELECT column_1, column_2, column_3 FROM `myproject.mydataset.mytable`

La liste d'options de vue spécifie :

  • les date/heure d'expiration : 48 heures à partir de la création de la vue ;
  • le nom descriptif : newview ;
  • la description : une vue qui expire dans deux jours ;
  • le libellé : org_unit = development.

Créer ou remplacer une vue

L'exemple suivant crée une vue nommée newview dans mydataset. Si newview existe déjà dans mydataset, elle est remplacée à l'aide de l'expression de requête spécifiée.

CREATE OR REPLACE VIEW `myproject.mydataset.newview`
OPTIONS(
  expiration_timestamp=TIMESTAMP_ADD(CURRENT_TIMESTAMP(), INTERVAL 48 HOUR),
  friendly_name="newview",
  description="a view that expires in 2 days",
  labels=[("org_unit", "development")]
)
AS SELECT column_1, column_2, column_3 FROM `myproject.mydataset.mytable`

La vue est définie à l'aide de la requête SQL standard suivante :

SELECT column_1, column_2, column_3 FROM myproject.mydataset.mytable

La liste d'options de vue spécifie :

  • les date/heure d'expiration : 48 heures à partir de la création de la vue ;
  • le nom descriptif : newview ;
  • la description : une vue qui expire dans deux jours ;
  • le libellé : org_unit = development.

Instruction CREATE MATERIALIZED VIEW

Pour créer une vue matérialisée dans BigQuery, utilisez l'instruction LDD CREATE MATERIALIZED VIEW.

{CREATE MATERIALIZED VIEW | CREATE MATERIALIZED VIEW IF NOT EXISTS }
[[project_name.]dataset_name.]materialized_view_name
[PARTITION BY partition_expression]
[CLUSTER BY clustering_column_list]
[OPTIONS(materialized_view_option_list)]
AS query_expression

Où :

{CREATE MATERIALIZED VIEW | CREATE MATERIALIZED VIEW IF NOT EXISTS } correspond à l'une des instructions suivantes :

  • CREATE MATERIALIZED VIEW : crée une vue matérialisée.

  • CREATE MATERIALIZED VIEW IF NOT EXISTS : crée une vue matérialisée uniquement si elle n'existe pas déjà dans l'ensemble de données spécifié.

project_name est le nom du projet dans lequel vous créez la vue matérialisée. Sa valeur par défaut correspond au projet qui exécute cette requête LDD. Si le nom du projet contient des caractères spéciaux tels que des signes deux-points, il doit être placé entre accents graves ` (exemple : `google.com:my_project`).

Si project_name est omis ou s'il est identique au projet qui exécute cette requête LDD, ce dernier est également utilisé comme projet par défaut des références aux tables, fonctions, etc., dans l'expression de requête (query_expression) (à noter que le projet par défaut des références est fixe et ne dépend pas des futures requêtes qui appellent la nouvelle vue matérialisée). Dans le cas contraire, toutes les références dans l'expression de requête (query_expression) doivent spécifier des projets.

dataset_name est le nom de l'ensemble de données dans lequel vous créez la vue matérialisée. Sa valeur par défaut correspond à l'ensemble de données par défaut (defaultDataset) dans la requête.

materialized_view_name est le nom de la vue matérialisée que vous créez. Le nom de la vue matérialisée doit être unique pour chaque ensemble de données. Le nom de la vue matérialisée peut :

  • contenir jusqu'à 1 024 caractères ;
  • contenir des lettres (majuscules ou minuscules), des chiffres et des traits de soulignement.

Les clauses PARTITION BY et CLUSTER BY sont utilisées comme vous le feriez dans une instruction CREATE TABLE. Une vue matérialisée ne peut être partitionnée que de la même manière que la table dans query expression (la table de base) est partitionnée.

materialized_view_option_list vous permet de spécifier des options de vue matérialisée supplémentaires, telles que l'état d'activation de l'actualisation, l'intervalle d'actualisation, un libellé ou encore un délai d'expiration.

Les instructions CREATE MATERIALIZED VIEW doivent respecter les règles suivantes :

  • Chaque requête ne peut contenir qu'une seule instruction CREATE.

query_expression est l'expression de requête SQL standard utilisée pour définir la vue matérialisée.

materialized_view_option_list

La liste d'options vous permet de définir des options de vue matérialisée, telles que l'état d'activation de l'actualisation, l'intervalle d'actualisation, un libellé ou encore un délai d'expiration. Vous pouvez inclure plusieurs options dans une liste d'éléments séparés par des virgules.

Spécifiez les listes d'options de vue matérialisée au format suivant :

NAME=VALUE, ...

NAME et VALUE doivent être utilisées selon l'une des combinaisons suivantes :

NAME VALUE Détails
enable_refresh BOOLEAN

Exemple : enable_refresh=false

refresh_interval_minutes FLOAT64

Exemple : refresh_interval_minutes=20

expiration_timestamp TIMESTAMP

Exemple : expiration_timestamp=TIMESTAMP "2025-01-01 00:00:00 UTC"

Cette propriété est équivalente à la propriété de ressource de table expirationTime.

friendly_name

STRING

Exemple : friendly_name="my_mv"

Cette propriété est équivalente à la propriété de ressource de table friendlyName.

description

STRING

Exemple : description="a materialized view that expires in 2025"

Cette propriété est équivalente à la propriété de ressource de table description.

labels

ARRAY<STRUCT<STRING, STRING>>

Exemple : labels=[("org_unit", "development")]

Cette propriété est équivalente à la propriété de ressource de table labels.

Projet par défaut dans le corps des vues matérialisées

Si la vue matérialisée est créée dans le même projet que celui utilisé pour exécuter l'instruction CREATE MATERIALIZED VIEW, l'expression de requête (query_expression) du corps de la vue peut référencer des entités sans spécifier le projet. Le projet par défaut est le projet propriétaire de la vue matérialisée. Prenons l'exemple de requête ci-dessous.

CREATE MATERIALIZED VIEW myProject.myDataset.myView AS SELECT * FROM anotherDataset.myTable;

Après avoir exécuté la requête CREATE MATERIALIZED VIEW ci-dessus dans le projet myProject, vous pouvez exécuter la requête SELECT * FROM myProject.myDataset.myView. Quel que soit le projet avec lequel vous souhaitez exécuter cette requête SELECT, la table référencée anotherDataset.myTable est toujours résolue avec le projet myProject.

Si la vue matérialisée n'est pas créée dans le même projet que celui utilisé pour exécuter l'instruction CREATE VIEW, toutes les références présentes dans l'expression de requête (query_expression) du corps de la vue matérialisée doivent inclure les ID de projet. Ainsi, l'exemple de requête CREATE MATERIALIZED VIEW précédent n'est pas valide s'il s'exécute dans un projet différent de myProject.

Exemples

Créer une vue matérialisée

L'exemple suivant crée une vue matérialisée nommée new_mv dans mydataset :

CREATE MATERIALIZED VIEW `myproject.mydataset.new_mv`
OPTIONS(
  expiration_timestamp=TIMESTAMP_ADD(CURRENT_TIMESTAMP(), INTERVAL 48 HOUR),
  friendly_name="new_mv",
  description="a materialized view that expires in 2 days",
  labels=[("org_unit", "development")],
  enable_refresh=true,
  refresh_interval_minutes=20
)
AS SELECT column_1, SUM(column_2) AS sum_2, AVG(column_3) AS avg_3
FROM `myproject.mydataset.mytable`
GROUP BY column_1

Si le nom de la vue matérialisée existe déjà dans l'ensemble de données, l'erreur suivante est renvoyée :

Already Exists: project_id:dataset.materialized_view

Lorsque vous utilisez une instruction LDD pour créer une vue matérialisée, vous devez spécifier le projet, l'ensemble de données et la vue matérialisée au format suivant : `project_id.dataset.materialized_view` (en intégrant les accents graves si project_id contient des caractères spéciaux), par exemple, `myproject.mydataset.new_mv`.

La vue matérialisée est définie à l'aide de la requête SQL standard suivante :

SELECT column_1, column_2, column_3 FROM `myproject.mydataset.mytable`

La liste d'options de vue matérialisée spécifie :

  • les date/heure d'expiration : 48 heures à partir de la création de la vue matérialisée ;
  • le nom descriptif : new_mv ;
  • la description : une vue matérialisée qui expire dans deux jours ;
  • le libellé : org_unit = development ;
  • l'état d'activation de l'actualisation : true ;
  • l'intervalle d'actualisation : 20 minutes.

Créer une vue matérialisée uniquement si la vue matérialisée n'existe pas

L'exemple suivant crée une vue matérialisée nommée new_mv dans mydataset uniquement si aucune vue matérialisée nommée new_mv n'existe dans mydataset. Si le nom de la vue matérialisée existe déjà dans l'ensemble de données, aucune erreur n'est renvoyée et aucune action n'est effectuée.

CREATE MATERIALIZED VIEW IF NOT EXISTS `myproject.mydataset.new_mv`
OPTIONS(
  expiration_timestamp=TIMESTAMP_ADD(CURRENT_TIMESTAMP(), INTERVAL 48 HOUR),
  friendly_name="new_mv",
  description="a view that expires in 2 days",
  labels=[("org_unit", "development")],
  enable_refresh=false
)
AS SELECT column_1, column_2, column_3 FROM `myproject.mydataset.mytable`

La vue matérialisée est définie à l'aide de la requête SQL standard suivante :

SELECT column_1, column_2, column_3 FROM `myproject.mydataset.mytable`

La liste d'options de vue matérialisée spécifie :

  • les date/heure d'expiration : 48 heures à partir de la création de la vue ;
  • le nom descriptif : new_mv ;
  • la description : une vue qui expire dans deux jours ;
  • le libellé : org_unit = development ;
  • l'état d'activation de l'actualisation : false.

Créer une vue matérialisée avec partitionnement et clustering

L'exemple suivant crée une vue matérialisée nommée new_mv dans mydataset, partitionnée par la colonne col_datetime et mise en cluster par la colonne col_int :

CREATE MATERIALIZED VIEW `myproject.mydataset.new_mv`
PARTITION BY DATE(col_datetime)
CLUSTER BY col_int
AS SELECT col_int, col_datetime, COUNT(1) as cnt
   FROM `myproject.mydataset.mv_base_table`
   GROUP BY col_int, col_datetime

La table de base (mv_base_table) doit également être partitionnée par la colonne col_datetime. Pour en savoir plus, consultez la page Utiliser des tables partitionnées et en cluster.

Instruction CREATE EXTERNAL TABLE

L'instruction CREATE EXTERNAL TABLE crée une table externe. Les tables externes permettent à BigQuery d'interroger les données stockées en dehors de l'espace de stockage BigQuery. Pour plus d'informations sur les tables externes, consultez la page Présentation des sources de données externes.

CREATE [OR REPLACE] EXTERNAL TABLE [IF NOT EXISTS] [[project_name.]dataset_name.]table_name
[(
  column_name column_schema,
  ...
)]
[WITH PARTITION COLUMNS
  [(
      partition_column_name partition_column_type,
      ...
  )]
]
OPTIONS (
  external_table_option_list,
  ...
);

Où :

  • project_name est le nom du projet dans lequel vous créez la table. Sa valeur par défaut correspond au projet qui exécute cette requête LDD.

  • dataset_name est le nom de l'ensemble de données dans lequel vous créez la table.

  • table_name est le nom de la table externe.

  • column_name est le nom d'une colonne de la table.

  • column_schema spécifie le schéma de la colonne. Cette clause utilise la même syntaxe que la définition column_schema de l'instruction CREATE TABLE. Si vous ne l'incluez pas, BigQuery détecte automatiquement le schéma.

  • partition_column_name est le nom d'une colonne de partition. Incluez ce champ si vos données externes utilisent une configuration de partitionnement Hive. Pour en savoir plus, consultez la section Configurations de données compatibles.

  • partition_column_type est de type de colonne de partition.

  • external_table_option_list spécifie une liste d'options pour la création de la table externe.

external_table_option_list

La liste d'options spécifie des options de création de la table externe. Les options format et uris sont obligatoires. Spécifiez la liste d'options au format suivant : NAME=VALUE, ....

Options
allow_jagged_rows

BOOL

Si la valeur est true, autorise les lignes auxquelles il manque des colonnes finales facultatives.

S'applique aux données CSV.

allow_quoted_newlines

BOOL

Si la valeur est true, autorise les sections de données entre guillemets contenant des caractères de retour à la ligne dans le fichier.

S'applique aux données CSV.

compression

STRING

Type de compression de la source de données. Valeur autorisée : GZIP. Si cette option n'est pas spécifiée, la source de données n'est pas compressée.

S'applique aux données CSV et JSON.

description

STRING

Description de cette table.

enable_logical_types

BOOL

Si la valeur est true, convertit les types logiques Avro en types SQL correspondants. Pour en savoir plus, consultez la section Types logiques.

S'applique aux données Avro.

encoding

STRING

Encodage des caractères des données. Valeurs autorisées : UTF8 (ou UTF-8), ISO_8859_1 (ou ISO-8859-1).

S'applique aux données CSV.

expiration_timestamp

TIMESTAMP

Date et heure d'expiration de cette table. Si cette option n'est pas spécifiée, la table n'expire pas.

Exemple : "2025-01-01 00:00:00 UTC".

field_delimiter

STRING

Séparateur des champs dans un fichier CSV.

S'applique aux données CSV.

format

STRING

Format des données externes. Valeurs autorisées : AVRO, CSV, DATASTORE_BACKUP, GOOGLE_SHEETS, NEWLINE_DELIMITED_JSON (ou JSON), ORC, PARQUET.

La valeur JSON est équivalente à NEWLINE_DELIMITED_JSON.

decimal_target_types

ARRAY<STRING>

Détermine comment convertir un type Decimal. Équivaut à ExternalDataConfiguration.decimal_target_types.

Exemple : ["NUMERIC", "BIGNUMERIC"].

hive_partition_uri_prefix

STRING

Préfixe commun à tous les URI sources avant le début de l'encodage de la clé de partition. Ne s'applique qu'aux tables externes partitionnées avec Hive.

S'applique aux données Avro, CSV, JSON, Parquet et ORC.

Exemple : "gs://bucket/path".

ignore_unknown_values

BOOL

Si la valeur est true, ignore les valeurs supplémentaires qui ne sont pas représentées dans le schéma de la table, sans renvoyer d'erreur.

S'applique aux données CSV et JSON.

max_bad_records

INT64

Nombre maximal d'enregistrements incorrects à ignorer lors de la lecture des données.

S'applique aux données CSV, JSON et Sheets.

null_marker

STRING

Chaîne représentant les valeurs NULL dans un fichier CSV.

S'applique aux données CSV.

projection_fields

STRING

Liste des propriétés d'entité à charger.

S'applique aux données Datastore.

quote

STRING

Chaîne utilisée pour citer des sections de données dans un fichier CSV. Si vos données contiennent des caractères de retour à la ligne entre guillemets, définit également la propriété allow_quoted_newlines sur true.

S'applique aux données CSV.

require_hive_partition_filter

BOOL

Si la valeur est true, toutes les requêtes sur cette table nécessitent un filtre de partition pouvant être utilisé pour éliminer les partitions lors de la lecture des données. Ne s'applique qu'aux tables externes partitionnées avec Hive.

S'applique aux données Avro, CSV, JSON, Parquet et ORC.

sheet_range

STRING

Plage d'une feuille de calcul Sheets à interroger.

S'applique aux données Sheets.

Exemple : “sheet1!A1:B20”.

skip_leading_rows

INT64

Nombre de lignes en haut d'un fichier à ignorer lors de la lecture des données.

S'applique aux données CSV et Sheets.

uris

ARRAY<STRING>

Tableau d'URI complets pour les emplacements de données externes.

Exemple : ["gs://bucket/path/*"].

L'instruction CREATE EXTERNAL TABLE ne permet pas de créer des tables externes temporaires.

Pour créer une table partitionnée en externe, utilisez la clause WITH PARTITION COLUMNS pour spécifier les détails du schéma de partition. BigQuery valide les définitions des colonnes en fonction de l'emplacement des données externes. La déclaration du schéma doit strictement respecter l'ordre des champs dans le chemin externe. Pour en savoir plus sur le partitionnement externe, consultez la page Interroger des données partitionnées en externe.

Exemples

L'exemple suivant crée une table externe à partir de plusieurs URI. Les données sont au format CSV. Cet exemple utilise la détection automatique de schéma.

CREATE EXTERNAL TABLE dataset.CsvTable OPTIONS (
  format = 'CSV',
  uris = ['gs://bucket/path1.csv', 'gs://bucket/path2.csv']
);

L'exemple suivant crée une table externe à partir d'un fichier CSV et spécifie explicitement le schéma. Il spécifie également le délimiteur de champ ('|') et définit le nombre maximal d'enregistrements incorrects autorisés.

CREATE OR REPLACE EXTERNAL TABLE dataset.CsvTable
(
  x INT64,
  y STRING
)
OPTIONS (
  format = 'CSV',
  uris = ['gs://bucket/path1.csv'],
  field_delimiter = '|',
  max_bad_records = 5
);

L'exemple suivant crée une table partitionnée en externe. Il utilise la détection automatique de schéma pour détecter à la fois le schéma du fichier et la configuration de partitionnement Hive.

Par exemple, si le chemin externe est gs://bucket/path/field_1=first/field_2=1/data.csv, les colonnes de partition sont field_1 (STRING) et field_2 (INT64).

CREATE EXTERNAL TABLE dataset.AutoHivePartitionedTable
WITH PARTITION COLUMNS
OPTIONS (
  uris=['gs://bucket/path/*'],
  format=csv,
  hive_partition_uri_prefix='gs://bucket/path'
);

L'exemple suivant crée une table partitionnée en externe en spécifiant explicitement les colonnes de partition. Cet exemple suppose que le chemin d'accès au fichier externe est au format gs://bucket/path/field_1=first/field_2=1/data.csv.

CREATE EXTERNAL TABLE dataset.CustomHivePartitionedTable
WITH PARTITION COLUMNS (
  field_1 STRING, -- column order must match the external path
  field_2 INT64
)
OPTIONS (
  uris=['gs://bucket/path/*'],
  format=csv,
  hive_partition_uri_prefix='gs://bucket/path'
);

Instruction CREATE FUNCTION

BigQuery est compatible avec les fonctions définies par l'utilisateur. Une fonction définie par l'utilisateur vous permet de créer une fonction à l'aide d'une expression SQL ou du langage JavaScript. Ces fonctions acceptent des colonnes d'entrée et effectuent des actions, puis renvoient le résultat de ces dernières sous la forme d'une valeur.

Les fonctions définies par l'utilisateur peuvent être persistantes ou temporaires. Vous pouvez réutiliser une fonction persistante définie par l'utilisateur dans plusieurs requêtes, alors qu'une fonction temporaire définie par l'utilisateur ne peut être utilisée que dans une seule requête. Pour en savoir plus sur les fonctions définies par l'utilisateur, consultez la section sur les fonctions définies par l'utilisateur.

Syntaxe des fonctions définies par l'utilisateur (UDF)

Pour créer une fonction persistante définie par l'utilisateur, utilisez la syntaxe suivante :

CREATE [OR REPLACE] FUNCTION [IF NOT EXISTS]
    [[project_name.]dataset_name.]function_name
    ([named_parameter[, ...]])
  [RETURNS data_type]
  { sql_function_definition | javascript_function_definition }

Pour créer une fonction temporaire définie par l'utilisateur, utilisez la syntaxe suivante :

CREATE [OR REPLACE] {TEMPORARY | TEMP} FUNCTION [IF NOT EXISTS]
    function_name
    ([named_parameter[, ...]])
  [RETURNS data_type]
  { sql_function_definition | javascript_function_definition }

named_parameter:
  param_name param_type

sql_function_definition:
  AS (sql_expression)

javascript_function_definition:
  [determinism_specifier]
  LANGUAGE js
  [OPTIONS (library = library_array)]
  AS javascript_code

determinism_specifier:
  { DETERMINISTIC | NOT DETERMINISTIC }

Cette syntaxe comprend les composants suivants :

  • CREATE { FUNCTION | OR REPLACE FUNCTION | FUNCTION IF NOT EXISTS } : crée ou met à jour une fonction. Pour remplacer une fonction existante portant le même nom, utilisez le mot clé OR REPLACE. Pour traiter la requête comme réussie et n'effectuer aucune action si une fonction du même nom existe déjà, utilisez la clause IF NOT EXISTS.

  • project_name est le nom du projet dans lequel vous créez la fonction. Sa valeur par défaut correspond au projet qui exécute cette requête LDD. Si le nom du projet contient des caractères spéciaux tels que des signes deux-points, il doit être placé entre accents graves ` (exemple : `google.com:my_project`).

  • dataset_name est le nom de l'ensemble de données dans lequel vous créez la fonction. Sa valeur par défaut correspond à l'ensemble de données par défaut (defaultDataset) dans la requête.

  • named_parameter : consiste en une paire param_name/param_type séparée par une virgule. La valeur de param_type est un type de données BigQuery. Pour une fonction SQL UDF, la valeur de param_type peut également être ANY TYPE.

  • determinism_specifier : s'applique uniquement aux fonctions JavaScript définies par l'utilisateur. La valeur indique à BigQuery si le résultat de la requête peut être mis en cache. Les valeurs possibles sont les suivantes :

    • DETERMINISTIC : la fonction renvoie toujours le même résultat lorsqu'elle est exécutée avec les mêmes arguments. Le résultat de la requête peut être mis en cache. Par exemple, si la fonction add_one(i) renvoie toujours i + 1, la fonction est déterministe.

    • NOT DETERMINISTIC : la fonction ne renvoie pas toujours le même résultat lorsqu'elle est exécutée avec les mêmes arguments. Elle n'est donc pas mise en cache. Par exemple, si add_random(i) renvoie i + rand(), la fonction n'est pas déterministe et BigQuery n'utilise pas les résultats mis en cache.

      Si toutes les fonctions appelées sont déterministes, BigQuery essaie de mettre en cache les résultats. Il arrive toutefois que les résultats ne puissent pas être mis en cache pour d'autres raisons. Pour en savoir plus, consultez la page Utiliser les résultats de requête mis en cache.

  • [RETURNS data_type] : spécifie le type de données renvoyé par la fonction.

    • Si la fonction est définie en SQL, la clause RETURNS est facultative. Si la clause RETURNS est omise, BigQuery déduit le type renvoyé par votre fonction du corps de la fonction SQL lors des appels par une requête.
    • Si la fonction est définie en JavaScript, la clause RETURNS est obligatoire. Pour en savoir plus sur les valeurs autorisées pour data_type, consultez la section Types de données compatibles avec les fonctions JavaScript définies par l'utilisateur.
  • AS (sql_expression) : spécifie l'expression SQL qui définit la fonction.

  • [OPTIONS (library = library_array)] : pour une fonction définie par l'utilisateur écrite en JavaScript, spécifie un tableau de bibliothèques JavaScript à inclure dans la définition de la fonction.

  • AS javascript_code : spécifie la définition d'une fonction JavaScript. javascript_code correspond à une valeur littérale de chaîne.

    • Si le code inclut des guillemets et des barres obliques inverses, il doit faire l'objet d'un échappement ou être représenté sous forme de chaîne brute. Par exemple, le code return "\n"; peut être représenté comme suit :
      • Chaîne entre guillemets"return \"\\n\";". Les guillemets et les barres obliques inverses doivent faire l'objet d'un échappement.
      • Chaîne entre guillemets triples : """return "\\n";""". Les barres obliques inverses doivent faire l'objet d'un échappement, contrairement aux guillemets.
      • Chaîne brute : r"""return "\n";""". Aucun échappement n'est nécessaire.

Structure des fonctions SQL définies par l'utilisateur

Vous pouvez créer des fonctions SQL définies par l'utilisateur à l'aide de la structure suivante :

CREATE [OR REPLACE] [TEMPORARY | TEMP] FUNCTION [IF NOT EXISTS]
    [[`project_name`.]dataset_name.]function_name
    ([named_parameter[, ...]])
  [RETURNS data_type]
  AS (sql_expression)

named_parameter:
  param_name param_type

Paramètres modélisés des fonctions SQL définies par l'utilisateur

Un paramètre basé sur un modèle avec param_type = ANY TYPE peut correspondre à plusieurs types d'argument lorsque la fonction est appelée.

  • Si plusieurs paramètres ont le type ANY TYPE, BigQuery n'applique aucune relation entre ces arguments.
  • Le type renvoyé par la fonction ne peut pas être ANY TYPE. Elle doit être d'un type explicite ou bien omise, ce qui signifie qu'elle doit être déterminée automatiquement en fonction de sql_expression.
  • La transmission des arguments de fonction possédant des types incompatibles avec la définition de la fonction génère une erreur au moment de l'appel.

Projet par défaut dans le corps des fonctions SQL définies par l'utilisateur

Si l'UDF SQL est créée dans le même projet que celui utilisé pour exécuter l'instruction CREATE FUNCTION, l'expression SQL (sql_expression) du corps de l'UDF peut référencer des entités sans spécifier le projet. Le projet par défaut est le projet propriétaire de l'UDF. Prenons l'exemple de requête ci-dessous.

CREATE FUNCTION myProject.myDataset.myFunction() AS (anotherDataset.anotherFunction());

Après avoir exécuté la requête CREATE FUNCTION ci-dessus dans le projet myProject, vous pouvez exécuter la requête SELECT myProject.myDataset.myFunction(). Quel que soit le projet avec lequel vous souhaitez exécuter cette requête SELECT, la fonction référencée anotherDataset.anotherFunction est toujours résolue avec le projet myProject.

Si l'UDF n'est pas créée dans le même projet que celui utilisé pour exécuter l'instruction CREATE FUNCTION, toutes les références présentes dans l'expression SQL (sql_expression) du corps de l'UDF doivent inclure les ID de projet. Ainsi, l'exemple de requête CREATE FUNCTION précédent n'est pas valide s'il s'exécute dans un projet différent de myProject.

Exemples de fonctions SQL définies par l'utilisateur

L'exemple suivant crée une fonction SQL persistante définie par l'utilisateur. Nous considérons ici qu'un ensemble de données nommé mydataset existe dans le projet actif. Si aucun ensemble de données portant ce nom n'existe, reportez-vous à la documentation sur la création des ensembles de données.

CREATE FUNCTION mydataset.multiplyInputs(x FLOAT64, y FLOAT64)
RETURNS FLOAT64
AS (x * y);

Après avoir exécuté l'instruction CREATE FUNCTION, vous pouvez utiliser la nouvelle fonction persistante définie par l'utilisateur dans une requête distincte. Remplacez le contenu de l'éditeur de requête par le contenu suivant, puis exécutez la requête :

WITH numbers AS
  (SELECT 1 AS x, 5 as y
  UNION ALL
  SELECT 2 AS x, 10 as y
  UNION ALL
  SELECT 3 as x, 15 as y)
SELECT x, y, mydataset.multiplyInputs(x, y) as product
FROM numbers;

L'exemple ci-dessus produit le résultat suivant :

+-----+-----+--------------+
| x   | y   | product      |
+-----+-----+--------------+
| 1   | 5   | 5            |
| 2   | 10  | 20           |
| 3   | 15  | 45           |
+-----+-----+--------------+

L'exemple suivant décrit une fonction SQL définie par l'utilisateur qui exploite un paramètre modélisé. La fonction obtenue accepte des arguments de divers types.

CREATE FUNCTION mydataset.addFourAndDivideAny(x ANY TYPE, y ANY TYPE) AS (
  (x + 4) / y
);

Après avoir exécuté l'instruction CREATE FUNCTION, vous pouvez utiliser la nouvelle fonction persistante définie par l'utilisateur dans une requête distincte :

SELECT addFourAndDivideAny(3, 4) AS integer_output,
       addFourAndDivideAny(1.59, 3.14) AS floating_point_output;

Cette requête renvoie le résultat suivant :

+----------------+-----------------------+
| integer_output | floating_point_output |
+----------------+-----------------------+
| 1.75           | 1.7802547770700636    |
+----------------+-----------------------+

L'exemple suivant montre une fonction SQL définie par l'utilisateur qui emploie un paramètre modélisé pour renvoyer le dernier élément d'un tableau de n'importe quel type.

CREATE FUNCTION mydataset.lastArrayElement(arr ANY TYPE) AS (
  arr[ORDINAL(ARRAY_LENGTH(arr))]
);

Après avoir exécuté l'instruction CREATE FUNCTION, vous pouvez utiliser la nouvelle fonction persistante définie par l'utilisateur dans une requête distincte :

SELECT
  names[OFFSET(0)] AS first_name,
  lastArrayElement(names) AS last_name
FROM (
  SELECT ['Fred', 'McFeely', 'Rogers'] AS names UNION ALL
  SELECT ['Marie', 'Skłodowska', 'Curie']
);

La requête ci-dessus renvoie le résultat suivant :

+------------+-----------+
| first_name | last_name |
+------------+-----------+
| Fred       | Rogers    |
| Marie      | Curie     |
+------------+-----------+

Structure des fonctions JavaScript définies par l'utilisateur

Vous pouvez créer des fonctions JavaScript persistantes définies par l'utilisateur à l'aide de la syntaxe suivante :

CREATE [OR REPLACE] [TEMPORARY | TEMP] FUNCTION [IF NOT EXISTS]
    [[`project_name`.]dataset_name.]function_name
    ([named_parameter[, ...]])
  RETURNS data_type
  [DETERMINISTIC | NOT DETERMINISTIC]
  LANGUAGE js
  [OPTIONS (library = library_array)]
  AS javascript_code

Pour en savoir plus sur les valeurs acceptées pour les data_type et sur les types de paramètres valides, consultez la section Types de données compatibles avec les fonctions JavaScript définies par l'utilisateur.

Exemples de fonctions JavaScript définies par l'utilisateur

CREATE TEMP FUNCTION mydataset.multiplyInputs(x FLOAT64, y FLOAT64)
RETURNS FLOAT64
LANGUAGE js
AS r"""
  return x*y;
""";

Après avoir exécuté l'instruction CREATE FUNCTION, vous pouvez utiliser la nouvelle fonction persistante définie par l'utilisateur dans une requête distincte :

WITH numbers AS
  (SELECT 1 AS x, 5 as y
  UNION ALL
  SELECT 2 AS x, 10 as y
  UNION ALL
  SELECT 3 as x, 15 as y)
SELECT x, y, multiplyInputs(x, y) as product
FROM numbers;

L'exemple ci-dessus renvoie le résultat suivant :

+-----+-----+--------------+
| x   | y   | product      |
+-----+-----+--------------+
| 1   | 5   | 5            |
| 2   | 10  | 20           |
| 3   | 15  | 45           |
+-----+-----+--------------+

Vous pouvez transmettre le résultat d'une fonction définie par l'utilisateur en tant qu'entrée d'une autre fonction définie par l'utilisateur. Par exemple, vous pouvez créer une fonction persistante définie par l'utilisateur à l'aide de la requête suivante  :

CREATE FUNCTION mydataset.multiplyInputs(x FLOAT64, y FLOAT64)
RETURNS FLOAT64
LANGUAGE js
AS r"""
  return x*y;
""";

Exécutez ensuite une autre requête pour créer une deuxième fonction persistante définie par l'utilisateur :

CREATE FUNCTION mydataset.divideByTwo(x FLOAT64)
RETURNS FLOAT64
LANGUAGE js
AS r"""
  return x/2;
""";

Exécutez maintenant la requête suivante pour utiliser les deux fonctions persistantes définies par l'utilisateur dans la même requête :

WITH numbers AS
  (SELECT 1 AS x, 5 as y
  UNION ALL
  SELECT 2 AS x, 10 as y
  UNION ALL
  SELECT 3 as x, 15 as y)
SELECT x,
  y,
  mydataset.multiplyInputs(
    mydataset.divideByTwo(x), mydataset.divideByTwo(y)) as half_product
FROM numbers;

L'exemple ci-dessus renvoie le résultat suivant :

+-----+-----+--------------+
| x   | y   | half_product |
+-----+-----+--------------+
| 1   | 5   | 1.25         |
| 2   | 10  | 5            |
| 3   | 15  | 11.25        |
+-----+-----+--------------+

L'exemple suivant additionne les valeurs de tous les champs nommés "foo" dans la chaîne JSON fournie.

CREATE FUNCTION mydataset.SumFieldsNamedFoo(json_row STRING)
  RETURNS FLOAT64
  LANGUAGE js
  AS r"""
  function SumFoo(obj) {
    var sum = 0;
    for (var field in obj) {
      if (obj.hasOwnProperty(field) && obj[field] != null) {
        if (typeof obj[field] == "object") {
          sum += SumFoo(obj[field]);
        } else if (field == "foo") {
          sum += obj[field];
        }
      }
    }
    return sum;
  }
  var row = JSON.parse(json_row);
  return SumFoo(row);
  """;

Après avoir exécuté l'instruction CREATE FUNCTION, vous pouvez utiliser la nouvelle fonction persistante définie par l'utilisateur dans une requête distincte :

WITH Input AS (
  SELECT STRUCT(1 AS foo, 2 AS bar, STRUCT('foo' AS x, 3.14 AS foo) AS baz) AS s, 10 AS foo UNION ALL
  SELECT NULL, 4 AS foo UNION ALL
  SELECT STRUCT(NULL, 2 AS bar, STRUCT('fizz' AS x, 1.59 AS foo) AS baz) AS s, NULL AS foo
)
SELECT
  TO_JSON_STRING(t) AS json_row,
  mydataset.SumFieldsNamedFoo(TO_JSON_STRING(t)) AS foo_sum
FROM Input AS t;

L'exemple ci-dessus renvoie le résultat suivant :

+---------------------------------------------------------------------+---------+
| json_row                                                            | foo_sum |
+---------------------------------------------------------------------+---------+
| {"s":{"foo":1,"bar":2,"baz":{"x":"foo","foo":3.14}},"foo":10}       | 14.14   |
| {"s":null,"foo":4}                                                  | 4       |
| {"s":{"foo":null,"bar":2,"baz":{"x":"fizz","foo":1.59}},"foo":null} | 1.59    |
+---------------------------------------------------------------------+---------+

Règles relatives aux guillemets

Vous devez placer le code JavaScript entre guillemets. Pour les extraits de code simples ne comprenant qu'une ligne, vous pouvez utiliser une chaîne standard entre guillemets :

CREATE FUNCTION mydataset.plusOne(x FLOAT64)
RETURNS FLOAT64
LANGUAGE js
AS "return x+1;";

Dans le cas où l'extrait contient des guillemets ou se compose de plusieurs lignes, utilisez des blocs entre guillemets triples :

CREATE FUNCTION mydataset.customGreeting(a STRING)
RETURNS STRING
LANGUAGE js AS r"""
  var d = new Date();
  if (d.getHours() < 12) {
    return 'Good Morning, ' + a + '!';
  } else {
    return 'Good Evening, ' + a + '!';
  }
  """;

Inclure des bibliothèques JavaScript

Vous pouvez étendre vos fonctions JavaScript définies par l'utilisateur à l'aide de la section OPTIONS. Cette section vous permet de spécifier des bibliothèques de code JavaScript pour l'UDF.

CREATE FUNCTION mydataset.myFunc(a FLOAT64, b STRING)
  RETURNS STRING
  LANGUAGE js
  OPTIONS (
    library=["gs://my-bucket/path/to/lib1.js", "gs://my-bucket/path/to/lib2.js"]
  )
  AS
  r"""
      // Assumes 'doInterestingStuff' is defined in one of the library files.
      return doInterestingStuff(a, b);
  """;

SELECT mydataset.myFunc(3.14, 'foo');

Dans l'exemple précédent, le code des bibliothèques lib1.js et lib2.js est disponible pour tout code présent dans la section javascript_code de la fonction définie par l'utilisateur. Notez que vous pouvez spécifier des fichiers de bibliothèque à l'aide d'une syntaxe à élément unique ou à tableau.

UDF et Cloud Console

Vous pouvez utiliser Cloud Console pour créer des fonctions persistantes définies par l'utilisateur.

Exécuter une requête pour créer une fonction persistante définie par l'utilisateur

  1. Accédez à la page "BigQuery" de Cloud Console.

    Accéder à BigQuery

  2. Cliquez sur Saisir une nouvelle requête.

    Saisissez une nouvelle requête.

  3. Saisissez l'instruction de fonction définie par l'utilisateur dans la zone de texte de l'éditeur de requête. Exemple :

      CREATE FUNCTION mydataset.timesTwo(x FLOAT64)
      RETURNS FLOAT64
        LANGUAGE js AS r"""
        return x*2;
      """;
    
  4. Cliquez sur Run (Exécuter).

  5. Après avoir créé la fonction persistante définie par l'utilisateur, remplacez le contenu de l'éditeur par une nouvelle requête qui l'utilise :

      SELECT mydataset.timesTwo(numbers) AS doubles
      FROM UNNEST([1, 2, 3, 4, 5]) AS numbers;
    
  6. Cliquez sur Run (Exécuter).

Fonctions définies par l'utilisateur et outil de ligne de commande bq

Vous pouvez utiliser l'outil de ligne de commande bq du SDK Cloud pour créer des fonctions persistantes définies par l'utilisateur.

Utilisez la syntaxe suivante pour exécuter une requête permettant de créer une fonction définie par l'utilisateur :

bq query --use_legacy_sql=false '
  CREATE FUNCTION mydataset.AddTwo(x INT64) AS (x + 2);
'

Instruction CREATE PROCEDURE

Crée une procédure qui consiste en un bloc d'instructions pouvant être appelées à partir d'autres requêtes.

CREATE [OR REPLACE] PROCEDURE [IF NOT EXISTS]
[[project_name.]dataset_name.]procedure_name (procedure_argument[, ...] )
[OPTIONS(procedure_option_list)]
BEGIN
statement_list
END;

procedure_argument: [procedure_argument_mode] argument_name argument_type

procedure_argument_mode: IN | OUT | INOUT

Description

project_name est le nom du projet dans lequel vous créez la procédure. Sa valeur par défaut correspond au projet qui exécute cette requête LDD. Si le nom du projet contient des caractères spéciaux tels que des signes deux-points, il doit être placé entre accents graves ` (exemple : `google.com:my_project`).

dataset_name est le nom de l'ensemble de données dans lequel vous créez la procédure. Sa valeur par défaut correspond à l'ensemble de données par défaut (defaultDataset) dans la requête.

statement_list est une liste d'instructions BigQuery. Une liste d'instructions est une série d'instructions se terminant toutes par un point-virgule.

argument_type peut être tout type BigQuery valide.

procedure_argument_mode spécifie si un argument est une entrée, une sortie ou les deux.

Les procédures peuvent s'appeler elles-mêmes de manière récursive.

procedure_option_list

Le paramètre procedure_option_list permet de spécifier des options de procédure. Les options de procédure ont la même syntaxe et les mêmes exigences que les options de table, mais une liste différente de noms (NAME) et de valeurs (VALUE) :

NAME VALUE Détails
strict_mode

BOOL

Exemple : strict_mode=FALSE

Si strict_mode correspond à TRUE, le corps de la procédure est soumis à des vérifications supplémentaires en cas d'erreurs, telles que des tables ou des colonnes inexistantes. L'instruction CREATE PROCEDURE échoue si l'une de ces vérifications du corps échoue.

Si strict_mode est utile pour intercepter de nombreux types d'erreurs courants, il n'est pas exhaustif. Une procédure créée avec strict_mode ne garantit pas son exécution correcte.

Si strict_mode correspond à FALSE, seule la syntaxe du corps de la procédure est vérifiée. Les procédures qui s'invoquent de manière récursive doivent être créées avec strict_mode=FALSE pour éviter les erreurs provoquées par une procédure encore inexistante lors de sa validation.

La valeur par défaut est TRUE.

Mode de l'argument

IN indique que l'argument n'est qu'une entrée de la procédure. Vous pouvez spécifier une expression de valeur ou une variable pour les arguments IN.

OUT indique que l'argument est une sortie de la procédure. Un argument OUT est initialisé sur NULL lorsque la procédure démarre. Vous devez spécifier une variable pour les arguments OUT.

INOUT indique que l'argument est à la fois une entrée et un résultat de la procédure. Vous devez spécifier une variable pour les arguments INOUT. Un argument INOUT peut être référencé dans le corps d'une procédure en tant que variable et se voir attribuer de nouvelles valeurs.

Si vous ne spécifiez pas IN, OUT ni INOUT, l'argument est traité comme un argument IN.

Champ d'application de la variable

Si une variable est déclarée en dehors d'une procédure, qu'elle est transmise en tant qu'argument INOUT ou OUT à une procédure et que cette procédure attribue une nouvelle valeur à cette variable, la nouvelle valeur est visible en dehors de la procédure.

Les variables déclarées dans une procédure ne sont pas visibles en dehors de la procédure, et inversement.

Une valeur peut être attribuée à un argument OUT ou INOUT à l'aide de SET. Dans ce cas, la valeur modifiée est visible en dehors de la procédure. Si la procédure se termine correctement, la valeur de l'argument OUT ou INOUT est la dernière valeur attribuée à cette variable INOUT.

Les tables temporaires existent pendant toute la durée d'exécution du script. Ainsi, si une procédure crée une table temporaire, l'appelant de la procédure pourra également référencer la table temporaire.

Projet par défaut dans le corps des procédures

Les corps de procédure peuvent référencer des entités sans spécifier le projet. Le projet par défaut est celui auquel appartient la procédure, qui n'est pas forcément celui utilisé pour exécuter l'instruction CREATE PROCEDURE. Prenons l'exemple de requête ci-dessous.

CREATE PROCEDURE myProject.myDataset.QueryTable()
BEGIN
  SELECT * FROM anotherDataset.myTable;
END;

Après avoir créé la procédure ci-dessus, vous pouvez exécuter la requête CALL myProject.myDataset.QueryTable(). Quel que soit le projet avec lequel vous souhaitez exécuter cette requête CALL, la table référencée anotherDataset.myTable est toujours résolue avec le projet myProject.

Exemples

L'exemple suivant crée une procédure qui prend x comme argument d'entrée et renvoie x comme résultat. Comme aucun mode d'argument n'est défini pour l'argument delta, il est considéré comme un argument d'entrée. La procédure consiste en un bloc contenant une instruction unique, qui attribue la somme des deux arguments d'entrée à x.

CREATE PROCEDURE mydataset.AddDelta(INOUT x INT64, delta INT64)
BEGIN
  SET x = x + delta;
END;

L'exemple suivant appelle la procédure AddDelta de l'exemple ci-dessus, en lui transmettant la variable accumulator deux fois. Comme les modifications apportées à x dans AddDelta sont visibles en dehors de AddDelta, ces appels de procédure incrémentent accumulator par un total de huit.

DECLARE accumulator INT64 DEFAULT 0;
CALL mydataset.AddDelta(accumulator, 5);
CALL mydataset.AddDelta(accumulator, 3);
SELECT accumulator;

Cela renvoie le résultat suivant :

+-------------+
| accumulator |
+-------------+
|           8 |
+-------------+

L'exemple suivant crée la procédure SelectFromTablesAndAppend, qui prend target_date comme argument d'entrée et renvoie rows_added comme résultat. La procédure crée une table temporaire DataForTargetDate à partir d'une requête, puis calcule le nombre de lignes dans DataForTargetDate et affecte le résultat à rows_added. Ensuite, elle insère une nouvelle ligne dans TargetTable, en transmettant la valeur de target_date comme l'un des noms de colonnes. Enfin, elle supprime la table DataForTargetDate et renvoie rows_added.

CREATE PROCEDURE mydataset.SelectFromTablesAndAppend(
  target_date DATE, OUT rows_added INT64)
BEGIN
  CREATE TEMP TABLE DataForTargetDate AS
  SELECT t1.id, t1.x, t2.y
  FROM dataset.partitioned_table1 AS t1
  JOIN dataset.partitioned_table2 AS t2
  ON t1.id = t2.id
  WHERE t1.date = target_date
    AND t2.date = target_date;

  SET rows_added = (SELECT COUNT(*) FROM DataForTargetDate);

  SELECT id, x, y, target_date  -- note that target_date is a parameter
  FROM DataForTargetDate;

  DROP TABLE DataForTargetDate;
END;

L'exemple suivant déclare une variable rows_added, puis la transmet en tant qu'argument à la procédure SelectFromTablesAndAppend de l'exemple précédent, avec la valeur de CURRENT_DATE. Un message indiquant le nombre de lignes ajoutées est ensuite renvoyé.

DECLARE rows_added INT64;
CALL mydataset.SelectFromTablesAndAppend(CURRENT_DATE(), rows_added);
SELECT FORMAT('Added %d rows', rows_added);

Instruction ALTER SCHEMA SET OPTIONS

Définit les options sur un ensemble de données.

Pour exécuter cette instruction, vous devez disposer des autorisations bigquery.datasets.update. L'instruction s'exécute à l'emplacement de l'ensemble de données si celui-ci existe, sauf si vous spécifiez un emplacement dans les paramètres de la requête. Pour en savoir plus, consultez la page Spécifier votre emplacement.

ALTER SCHEMA [IF EXISTS]
[project_name.]dataset_name
SET OPTIONS(schema_set_options_list)

Où :

  • IF EXISTS : si vous incluez cette clause et que l'ensemble de données spécifié n'existe pas, l'instruction aboutit sans action. Si vous omettez cette clause et que l'ensemble de données n'existe pas, l'instruction renvoie une erreur.

  • project_name est le nom du projet qui contient l'ensemble de données. Sa valeur par défaut correspond au projet qui exécute cette instruction LDD.

  • dataset_name est le nom de l'ensemble de données.

  • schema_set_options_list spécifie la liste des options à définir.

schema_set_options_list

La liste d'options spécifie des options pour l'ensemble de données. Spécifiez les options au format suivant : NAME=VALUE, ...

Les options suivantes sont compatibles :

NAME VALUE Détails
default_kms_key_name STRING Spécifie la clé Cloud KMS par défaut pour le chiffrement des données de table de cet ensemble de données. Vous pouvez remplacer cette valeur lorsque vous créez une table.
default_partition_expiration_days FLOAT64 Spécifie le délai d'expiration par défaut, en jours, pour les partitions de tables de cet ensemble de données. Vous pouvez remplacer cette valeur lorsque vous créez une table.
default_table_expiration_days FLOAT64 Spécifie le délai d'expiration par défaut, en jours, pour les tables de cet ensemble de données. Vous pouvez remplacer cette valeur lorsque vous créez une table.
description STRING Description de l'ensemble de données.
friendly_name STRING Nom descriptif de l'ensemble de données.
labels <ARRAY<STRUCT<STRING, STRING>>> Tableau de libellés de l'ensemble de données, exprimé sous forme de paires clé/valeur.

Exemple

L'exemple suivant définit le délai d'expiration par défaut des tables.

ALTER SCHEMA mydataset
SET OPTIONS(
  default_table_expiration_days=3.75
  )

Instruction ALTER TABLE SET OPTIONS

Pour définir les options d'une table dans BigQuery, utilisez l'instruction LDD ALTER TABLE SET OPTIONS.

ALTER TABLE [IF EXISTS] [[project_name.]dataset_name.]table_name
SET OPTIONS(table_set_options_list)

Où :

IF EXISTS : si cet élément est présent, la requête aboutit lorsque la table spécifiée n'existe pas. S'il est absent, la requête échoue lorsque la table spécifiée n'existe pas.

project_name est le nom du projet contenant la table que vous modifiez. Sa valeur par défaut correspond au projet qui exécute cette requête LDD. Si le nom du projet contient des caractères spéciaux tels que des signes deux-points, il doit être placé entre accents graves ` (exemple : `google.com:my_project`).

dataset_name est le nom de l'ensemble de données contenant la table que vous modifiez. Sa valeur par défaut correspond à l'ensemble de données par défaut (defaultDataset) dans la requête.

table_name correspond au nom de la table que vous modifiez.

table_set_options_list

La liste d'options vous permet de définir des options de table, telles qu'un libellé et une date/heure d'expiration. Vous pouvez inclure plusieurs options dans une liste d'éléments séparés par des virgules.

Spécifiez les listes d'options de table au format suivant :

NAME=VALUE, ...

NAME et VALUE doivent être utilisées selon l'une des combinaisons suivantes :

NAME VALUE Détails
expiration_timestamp TIMESTAMP

Exemple : expiration_timestamp=TIMESTAMP "2025-01-01 00:00:00 UTC"

Cette propriété est équivalente à la propriété de ressource de table expirationTime.

partition_expiration_days

FLOAT64

Exemple : partition_expiration_days=7

Cette propriété est équivalente à la propriété de ressource de table timePartitioning.expirationMs mais utilise des jours au lieu de millisecondes. Un jour équivaut à 86 400 000 millisecondes, soit 24 heures.

Cette propriété ne peut être définie que si la table est partitionnée.

require_partition_filter

BOOL

Exemple : require_partition_filter=true

Cette propriété est équivalente à la propriété de ressource de table timePartitioning.requirePartitionFilter.

Cette propriété ne peut être définie que si la table est partitionnée.

kms_key_name

STRING

Exemple : kms_key_name="projects/project_id/locations/location/keyRings/keyring/cryptoKeys/key"

Cette propriété est équivalente à la propriété de ressource de table encryptionConfiguration.kmsKeyName.

En savoir plus sur la protection des données avec des clés Cloud KMS

friendly_name

STRING

Exemple : friendly_name="my_table"

Cette propriété est équivalente à la propriété de ressource de table friendlyName.

description

STRING

Exemple : description="a table that expires in 2025"

Cette propriété est équivalente à la propriété de ressource de table description.

labels

ARRAY<STRUCT<STRING, STRING>>

Exemple : labels=[("org_unit", "development")]

Cette propriété est équivalente à la propriété de ressource de table labels.

VALUE est une expression constante ne contenant que des littéraux, des paramètres de requête et des fonctions scalaires. Si l'expression constante renvoie la valeur null, l'option NAME correspondante est ignorée.

L'expression constante ne peut pas contenir les éléments suivants :

  • Une référence à une table
  • Des sous-requêtes ou des instructions SQL telles que SELECT, CREATE et UPDATE
  • Des fonctions définies par l'utilisateur, des fonctions d'agrégation ou des fonctions d'analyse
  • Les fonctions scalaires suivantes :
    • ARRAY_TO_STRING
    • REPLACE
    • REGEXP_REPLACE
    • RAND
    • FORMAT
    • LPAD
    • RPAD
    • REPEAT
    • SESSION_USER
    • GENERATE_ARRAY
    • GENERATE_DATE_ARRAY

Lorsque l'option VALUE est définie, la valeur existante de cette option de table est remplacée, le cas échéant. Si l'option VALUE est définie sur NULL, la valeur de la table qui correspondait à cette option est effacée.

Exemples

Définir l'horodatage d'expiration et la description d'une table

L'exemple suivant définit l'horodatage d'expiration d'une table à sept jours à compter de l'heure d'exécution de l'instruction ALTER TABLE, et définit également la description de la table :

ALTER TABLE mydataset.mytable
SET OPTIONS (
  expiration_timestamp=TIMESTAMP_ADD(CURRENT_TIMESTAMP(), INTERVAL 7 DAY),
  description="Table that expires seven days from now"
)

Définir l'attribut de filtrage de partition requis sur une table partitionnée

L'exemple suivant définit l'attribut timePartitioning.requirePartitionFilter sur une table partitionnée :

ALTER TABLE mydataset.mypartitionedtable
SET OPTIONS (require_partition_filter=true)

Les requêtes faisant référence à cette table doivent utiliser un filtre sur la colonne de partitionnement, faute de quoi BigQuery renvoie une erreur. En définissant cette option sur true, vous pouvez éviter d'interroger par erreur davantage de données que vous ne l'aviez prévu :

Effacer l'horodatage d'expiration sur une table

L'exemple suivant efface l'horodatage d'expiration sur une table afin qu'elle n'expire jamais :

ALTER TABLE mydataset.mytable
SET OPTIONS (expiration_timestamp=NULL)

Instruction ALTER TABLE ADD COLUMN

L'instruction ALTER TABLE ADD COLUMN ajoute une ou plusieurs nouvelles colonnes à un schéma de table existant. Pour en savoir plus sur les modifications de schéma dans BigQuery, consultez la page Modifier des schémas de table.

ALTER TABLE [[project_name.]dataset_name.]table_name
ADD COLUMN [IF NOT EXISTS] column_name column_schema [, ...]

Où :

  • project_name correspond au nom du projet contenant la table. Sa valeur par défaut correspond au projet qui exécute cette requête LDD.

  • dataset_name est le nom de l'ensemble de données contenant la table.

  • table_name est le nom de la table à modifier. La table doit déjà exister et posséder un schéma.

  • column_name est le nom de la colonne à ajouter.

  • column_schema est le schéma de la colonne. Ce schéma utilise la même syntaxe que le schéma de colonne de l'instruction CREATE TABLE.

Vous ne pouvez pas utiliser cette instruction pour créer les colonnes suivantes :

  • Colonnes partitionnées
  • Colonnes en cluster
  • Colonnes imbriquées dans les champs RECORD existants

Vous ne pouvez pas ajouter une colonne REQUIRED à un schéma de table existant. Toutefois, vous pouvez créer une colonne REQUIRED imbriquée dans un nouveau champ RECORD.

Sans la clause IF NOT EXISTS, si la table contient déjà une colonne portant ce nom, l'instruction renvoie une erreur. Si la clause IF NOT EXISTS est incluse et que le nom de colonne existe déjà, aucune erreur n'est renvoyée et aucune mesure n'est prise.

La valeur de la nouvelle colonne pour les lignes existantes est définie sur l'une des valeurs suivantes :

  • NULL si la nouvelle colonne a été ajoutée avec le mode NULLABLE. Il s'agit du mode par défaut.
  • Une valeur ARRAY vide si la nouvelle colonne a été ajoutée avec le mode REPEATED.

Exemples

Ajouter des colonnes

L'exemple suivant ajoute les colonnes suivantes à une table existante nommée mytable :

  • Colonne A de type STRING
  • Colonne B de type GEOGRAPHY
  • Colonne C de type NUMERIC avec le mode REPEATED
  • Colonne D de type DATE avec une description
ALTER TABLE mydataset.mytable
  ADD COLUMN A STRING,
  ADD COLUMN IF NOT EXISTS B GEOGRAPHY,
  ADD COLUMN C ARRAY<NUMERIC>,
  ADD COLUMN D DATE OPTIONS(description="my description")

Si l'une des colonnes nommées A, C ou D existe déjà, l'instruction échoue. Si la colonne B existe déjà, l'instruction réussit en raison de la clause IF NOT EXISTS.

Ajouter une colonne RECORD

L'exemple suivant ajoute une colonne nommée A de type STRUCT qui contient les colonnes imbriquées suivantes :

  • Colonne B de type GEOGRAPHY
  • Colonne C de type INT64 avec le mode REPEATED
  • Colonne D de type INT64 avec le mode REQUIRED
  • Colonne E de type TIMESTAMP avec une description
ALTER TABLE mydataset.mytable
   ADD COLUMN A STRUCT<
       B GEOGRAPHY,
       C ARRAY<INT64>,
       D INT64 NOT NULL,
       E TIMESTAMP OPTIONS(description="creation time")
       >

La requête échoue si la table contient déjà une colonne nommée A, même si cette colonne ne contient aucune des colonnes imbriquées spécifiées.

La nouvelle colonne STRUCT nommée A peut être vide, mais la colonne imbriquée D dans A est requise pour toute valeur STRUCT de A.

Instruction ALTER TABLE DROP COLUMN

L'instruction ALTER TABLE DROP COLUMN supprime une ou plusieurs colonnes d'un schéma de table existant. Elle ne libère pas immédiatement l'espace de stockage associé à la colonne supprimée. La déclaration en arrière-plan de l'espace de stockage reste effective pendant sept jours à compter de la date de suppression d'une colonne.

Pour en savoir plus sur la récupération immédiate d'un espace de stockage, consultez la section Supprimer une colonne d'un schéma de table.

Pour en savoir plus sur les modifications de schéma dans BigQuery, consultez la page Modifier des schémas de table.

ALTER TABLE [[project_name.]dataset_name.]table_name
DROP COLUMN [IF EXISTS] column_name [, ...]

Où :

  • project_name correspond au nom du projet contenant la table. Sa valeur par défaut correspond au projet qui exécute cette requête LDD.

  • dataset_name est le nom de l'ensemble de données contenant la table.

  • table_name est le nom de la table à modifier. La table doit déjà exister et posséder un schéma.

  • column_name est le nom de la colonne à supprimer.

Vous ne pouvez pas utiliser cette instruction pour supprimer les éléments suivants :

  • Colonnes partitionnées
  • Colonnes en cluster
  • Colonnes imbriquées dans des champs RECORD existants

Sans la clause IF EXISTS, si la table ne contient pas de colonne portant ce nom, l'instruction renvoie une erreur. Si la clause IF EXISTS est incluse et que le nom de colonne n'existe pas, aucune erreur n'est renvoyée et aucune mesure n'est prise.

Cette instruction ne fait que supprimer la colonne du tableau. Tous les objets faisant référence à la colonne, tels que les vues ou les vues matérialisées, doivent être mis à jour ou recréés séparément.

Exemples

Supprimer des colonnes

L'exemple suivant supprime les colonnes ci-après d'une table existante nommée mytable :

  • Colonne A
  • Colonne B
ALTER TABLE mydataset.mytable
  DROP COLUMN A,
  DROP COLUMN IF EXISTS B

Si la colonne A n'existe pas, l'instruction échoue. Si la colonne B n'existe pas, l'instruction aboutit tout de même, du fait de la clause IF EXISTS.

Instruction ALTER VIEW SET OPTIONS

Pour définir les options d'une vue dans BigQuery, utilisez l'instruction LDD ALTER VIEW SET OPTIONS.

ALTER VIEW [IF EXISTS] [[project_name.]dataset_name.]view_name
SET OPTIONS(view_set_options_list)

Où :

IF EXISTS : si cet élément est présent, la requête aboutit lorsque la vue spécifiée n'existe pas. S'il est absent, la requête échoue lorsque la vue spécifiée n'existe pas.

project_name est le nom du projet contenant la vue que vous modifiez. Sa valeur par défaut correspond au projet qui exécute cette requête LDD. Si le nom du projet contient des caractères spéciaux tels que des signes deux-points, il doit être placé entre accents graves ` (exemple : `google.com:my_project`).

dataset_name est le nom de l'ensemble de données contenant la vue que vous modifiez. Sa valeur par défaut correspond à l'ensemble de données par défaut (defaultDataset) dans la requête.

view_name correspond au nom de la vue que vous modifiez.

view_set_options_list

La liste d'options vous permet de définir des options de vue, telles qu'un libellé et une date/heure d'expiration. Vous pouvez inclure plusieurs options dans une liste d'éléments séparés par des virgules.

Spécifiez les listes d'options de vue au format suivant :

NAME=VALUE, ...

NAME et VALUE doivent être utilisées selon l'une des combinaisons suivantes :

NAME VALUE Détails
expiration_timestamp TIMESTAMP

Exemple : expiration_timestamp=TIMESTAMP "2025-01-01 00:00:00 UTC"

Cette propriété est équivalente à la propriété de ressource de table expirationTime.

friendly_name

STRING

Exemple : friendly_name="my_view"

Cette propriété est équivalente à la propriété de ressource de table friendlyName.

description

STRING

Exemple : description="a view that expires in 2025"

Cette propriété est équivalente à la propriété de ressource de table description.

labels

ARRAY<STRUCT<STRING, STRING>>

Exemple : labels=[("org_unit", "development")]

Cette propriété est équivalente à la propriété de ressource de table labels.

VALUE est une expression constante ne contenant que des littéraux, des paramètres de requête et des fonctions scalaires. Si l'expression constante renvoie la valeur null, l'option NAME correspondante est ignorée.

L'expression constante ne peut pas contenir les éléments suivants :

  • Une référence à une table
  • Des sous-requêtes ou des instructions SQL telles que SELECT, CREATE et UPDATE
  • Des fonctions définies par l'utilisateur, des fonctions d'agrégation ou des fonctions d'analyse
  • Les fonctions scalaires suivantes :
    • ARRAY_TO_STRING
    • REPLACE
    • REGEXP_REPLACE
    • RAND
    • FORMAT
    • LPAD
    • RPAD
    • REPEAT
    • SESSION_USER
    • GENERATE_ARRAY
    • GENERATE_DATE_ARRAY

Lorsque l'option VALUE est définie, la valeur existante de cette option de vue est remplacée, le cas échéant. Si l'option VALUE est définie sur NULL, la valeur de la vue qui correspondait à cette option est effacée.

Exemples

Définir l'horodatage d'expiration et la description d'une vue

L'exemple suivant définit l'horodatage d'expiration d'une vue à sept jours à compter de l'heure d'exécution de l'instruction ALTER VIEW, et définit également la description de la table :

ALTER VIEW mydataset.myview
SET OPTIONS (
  expiration_timestamp=TIMESTAMP_ADD(CURRENT_TIMESTAMP(), INTERVAL 7 DAY),
  description="View that expires seven days from now"
)

Instruction ALTER MATERIALIZED VIEW SET OPTIONS

Pour définir les options d'une vue matérialisée dans BigQuery, utilisez l'instruction LDD ALTER MATERIALIZED VIEW SET OPTIONS.

ALTER MATERIALIZED VIEW [IF EXISTS] [[project_name.]dataset_name.]materialized_view_name
SET OPTIONS(materialized_view_set_options_list)

Où :

IF EXISTS : si cet élément est présent, la requête aboutit lorsque la vue spécifiée n'existe pas. S'il est absent, la requête échoue lorsque la vue spécifiée n'existe pas.

project_name est le nom du projet contenant la vue matérialisée que vous modifiez. Sa valeur par défaut correspond au projet qui exécute cette requête LDD. Si le nom du projet contient des caractères spéciaux tels que des signes deux-points, il doit être placé entre accents graves ` (exemple : `google.com:my_project`).

dataset_name est le nom de l'ensemble de données contenant la vue matérialisée que vous modifiez. Sa valeur par défaut correspond à l'ensemble de données par défaut (defaultDataset) dans la requête.

materialized_view_name correspond au nom de la vue matérialisée que vous modifiez.

materialized_view_set_options_list

La liste d'options vous permet de définir des options de vue matérialisée, telles que l'état d'activation de l'actualisation, l'intervalle d'actualisation, un libellé ou encore un délai d'expiration. Vous pouvez inclure plusieurs options dans une liste d'éléments séparés par des virgules.

Spécifiez les listes d'options de vue matérialisée au format suivant :

NAME=VALUE, ...

NAME et VALUE doivent être utilisées selon l'une des combinaisons suivantes :

NAME VALUE Détails
enable_refresh BOOLEAN

Exemple : enable_refresh=false

refresh_interval_minutes FLOAT64

Exemple : refresh_interval_minutes=20

expiration_timestamp TIMESTAMP

Exemple : expiration_timestamp=TIMESTAMP "2025-01-01 00:00:00 UTC"

Cette propriété est équivalente à la propriété de ressource de table expirationTime.

friendly_name

STRING

Exemple : friendly_name="my_mv"

Cette propriété est équivalente à la propriété de ressource de table friendlyName.

description

STRING

Exemple : description="a materialized view that expires in 2025"

Cette propriété est équivalente à la propriété de ressource de table description.

labels

ARRAY<STRUCT<STRING, STRING>>

Exemple : labels=[("org_unit", "development")]

Cette propriété est équivalente à la propriété de ressource de table labels.

Lorsque l'option VALUE est définie, la valeur existante de cette option de vue matérialisée est remplacée, le cas échéant. Si l'option VALUE est définie sur NULL, la valeur de la vue qui correspondait à cette option est effacée.

Exemples

Définir l'état et l'intervalle d'actualisation sur une vue matérialisée

L'exemple suivant active l'actualisation avec un intervalle de 20 minutes sur une vue matérialisée :

ALTER MATERIALIZED VIEW mydataset.my_mv
SET OPTIONS (
  enable_refresh=true,
  refresh_interval_minutes=20
)

Instruction DROP SCHEMA

Supprime un ensemble de données.

Pour exécuter cette instruction, vous devez disposer des autorisations bigquery.datasets.delete. L'instruction s'exécute à l'emplacement de l'ensemble de données si celui-ci existe, sauf si vous spécifiez un emplacement dans les paramètres de la requête. Pour en savoir plus, consultez la page Spécifier votre emplacement.

DROP SCHEMA [IF EXISTS]
[project_name.]dataset_name
[ CASCADE | RESTRICT ]

Où :

  • IF EXISTS : si vous incluez cette clause et que l'ensemble de données spécifié n'existe pas, l'instruction aboutit sans action. Si vous omettez cette clause et que l'ensemble de données n'existe pas, l'instruction renvoie une erreur.

  • project_name est le nom du projet qui contient l'ensemble de données. Sa valeur par défaut correspond au projet qui exécute cette instruction LDD.

  • dataset_name est le nom de l'ensemble de données à supprimer.

  • CASCADE : supprime l'ensemble de données et toutes les ressources qu'il contient, telles que les tables, les vues et les fonctions. Vous devez avoir l'autorisation de supprimer les ressources, sinon l'instruction renvoie une erreur. Pour obtenir la liste des autorisations BigQuery, consultez la page Rôles et autorisations prédéfinis.

  • RESTRICT: ne supprime l'ensemble de données que s'il est vide. Sinon, elle renvoie une erreur.

Si vous ne spécifiez pas CASCADE ni RESTRICT, le comportement par défaut est RESTRICT.

Exemples

L'exemple suivant permet de supprimer l'ensemble de données nommé mydataset. Si l'ensemble de données n'existe pas ou n'est pas vide, l'instruction renvoie une erreur.

DROP SCHEMA mydataset

L'exemple suivant supprime l'ensemble de données nommé mydataset et toutes les ressources qu'il contient. Si l'ensemble de données n'existe pas, aucune erreur n'est renvoyée.

DROP SCHEMA IF EXISTS mydataset CASCADE

Instruction DROP TABLE

Pour supprimer une table dans BigQuery, utilisez l'instruction LDD DROP TABLE.

DROP TABLE [IF EXISTS] [[project_name.]dataset_name.]table_name

Où :

IF EXISTS : si cet élément est présent, la requête aboutit lorsque la table spécifiée n'existe pas. S'il est absent, la requête échoue lorsque la table spécifiée n'existe pas.

project_name est le nom du projet contenant la table à supprimer. Sa valeur par défaut correspond au projet qui exécute cette requête LDD. Si le nom du projet contient des caractères spéciaux tels que des signes deux-points, il doit être placé entre accents graves ` (exemple : `google.com:my_project`).

dataset_name est le nom de l'ensemble de données contenant la table à supprimer. Sa valeur par défaut correspond à l'ensemble de données par défaut (defaultDataset) dans la requête.

table_name est le nom de la table à supprimer.

Exemples

Supprimer une table

L'exemple suivant supprime une table nommée mytable dans mydataset :

DROP TABLE mydataset.mytable

Si ce nom de table n'existe pas déjà dans l'ensemble de données, l'erreur suivante est renvoyée :

Error: Not found: Table myproject:mydataset.mytable

Supprimer une table seulement si cette table existe déjà

L'exemple suivant supprime une table nommée mytable dans mydataset uniquement si la table existe déjà. Si ce nom de table n'existe pas déjà dans l'ensemble de données, aucune erreur n'est renvoyée et aucune action n'est effectuée.

DROP TABLE IF EXISTS mydataset.mytable

Instruction DROP EXTERNAL TABLE

L'instruction DROP EXTERNAL TABLE supprime une table externe.

DROP EXTERNAL TABLE [IF EXISTS] [[project_name.]dataset_name.]table_name

Où :

  • project_name correspond au nom du projet contenant la table. Sa valeur par défaut correspond au projet qui exécute cette requête LDD.

  • dataset_name est le nom de l'ensemble de données contenant la table.

  • table_name est le nom de la table à supprimer.

Sans la clause IF EXISTS, si la table externe n'existe pas, l'instruction renvoie une erreur. Si la clause IF EXISTS est incluse et que la table n'existe pas, aucune erreur n'est renvoyée et aucune mesure n'est prise.

Si table_name existe, mais n'est pas une table externe, l'instruction renvoie l'erreur suivante :

Cannot drop table_name which has type TYPE. An external table was expected.

L'instruction DROP EXTERNAL ne supprime que la définition de la table externe de BigQuery. Les données stockées dans l'emplacement externe ne sont pas concernées.

Exemples

L'exemple suivant supprime la table externe nommée external_table de l'ensemble de données mydataset. Il renvoie une erreur si la table externe n'existe pas.

DROP EXTERNAL TABLE mydataset.external_table

L'exemple suivant supprime la table externe nommée external_table de l'ensemble de données mydataset. Si la table externe n'existe pas, aucune erreur n'est renvoyée.

DROP EXTERNAL TABLE IF EXISTS mydataset.external_table

Instruction DROP VIEW

Pour supprimer une vue dans BigQuery, utilisez l'instruction LDD DROP VIEW.

DROP VIEW [IF EXISTS] [[project_name.]dataset_name.]view_name

Où :

IF EXISTS : si cet élément est présent, la requête aboutit lorsque la vue spécifiée n'existe pas. S'il est absent, la requête échoue lorsque la vue spécifiée n'existe pas.

project_name est le nom du projet contenant la vue à supprimer. Sa valeur par défaut correspond au projet qui exécute cette requête LDD. Si le nom du projet contient des caractères spéciaux tels que des signes deux-points, il doit être placé entre accents graves ` (exemple : `google.com:my_project`).

dataset_name est le nom de l'ensemble de données contenant la vue à supprimer. Sa valeur par défaut correspond à l'ensemble de données par défaut (defaultDataset) dans la requête.

view_name est le nom de la vue que vous supprimez.

Exemples

Supprimer une vue

L'exemple suivant crée une vue nommée myview dans mydataset :

DROP VIEW mydataset.myview

Si ce nom de vue n'existe pas déjà dans l'ensemble de données, l'erreur suivante est renvoyée :

Error: Not found: Table myproject:mydataset.myview

Supprimer une vue seulement si cette vue existe déjà

L'exemple suivant supprime une vue nommée myview dans mydataset uniquement si la vue existe déjà. Si ce nom de vue n'existe pas déjà dans l'ensemble de données, aucune erreur n'est renvoyée et aucune action n'est effectuée.

DROP VIEW IF EXISTS mydataset.myview

Instruction DROP MATERIALIZED VIEW

Pour supprimer une vue matérialisée dans BigQuery, utilisez l'instruction LDD DROP MATERIALIZED VIEW.

DROP MATERIALIZED VIEW [IF EXISTS] [[project_name.]dataset_name.]mv_name

Où :

IF EXISTS : si cet élément est présent, la requête aboutit lorsque la vue matérialisée spécifiée n'existe pas. S'il est absent, la requête échoue lorsque la vue matérialisée spécifiée n'existe pas.

project_name est le nom du projet contenant la vue matérialisée à supprimer. Sa valeur par défaut correspond au projet qui exécute cette requête LDD. Si le nom du projet contient des caractères spéciaux tels que des signes deux-points, il doit être placé entre accents graves ` (exemple : `google.com:my_project`).

dataset_name est le nom de l'ensemble de données contenant la vue matérialisée à supprimer. Sa valeur par défaut correspond à l'ensemble de données par défaut (defaultDataset) dans la requête.

mv_name est le nom de la vue matérialisée que vous supprimez.

Exemples

Supprimer une vue matérialisée

L'exemple suivant supprime une vue matérialisée nommée my_mv dans mydataset :

DROP MATERIALIZED VIEW mydataset.my_mv

Si ce nom de vue matérialisée n'existe pas dans l'ensemble de données, l'erreur suivante est renvoyée :

Error: Not found: Table myproject:mydataset.my_mv

Si vous supprimez une vue matérialisée dans un autre projet, vous devez spécifier le projet, l'ensemble de données et la vue matérialisée au format suivant : `project_id.dataset.materialized_view` (en intégrant les accents graves si project_id contient des caractères spéciaux), par exemple, `myproject.mydataset.my_mv`.

Supprimer une vue matérialisée uniquement si elle existe

L'exemple suivant supprime une vue matérialisée nommée my_mv dans mydataset uniquement si la vue matérialisée existe déjà. Si ce nom de vue matérialisée n'existe pas dans l'ensemble de données, aucune erreur n'est renvoyée et aucune action n'est effectuée.

DROP MATERIALIZED VIEW IF EXISTS mydataset.my_mv

Si vous supprimez une vue matérialisée dans un autre projet, vous devez spécifier le projet, l'ensemble de données et la vue matérialisée au format suivant : `project_id.dataset.materialized_view`, (en intégrant les accents graves si project_id contient des caractères spéciaux), par exemple, `myproject.mydataset.my_mv`.

Instruction DROP FUNCTION

DROP FUNCTION [IF EXISTS] [[project_name.]dataset_name.]function_name

Où :

IF EXISTS : si cet élément est présent, la requête aboutit lorsque la fonction spécifiée n'existe pas. S'il est absent, la requête échoue lorsque la fonction spécifiée n'existe pas.

project_name est le nom du projet contenant la fonction à supprimer. Sa valeur par défaut correspond au projet qui exécute cette requête LDD. Si le nom du projet contient des caractères spéciaux tels que des signes deux-points, il doit être placé entre accents graves ` (exemple : `google.com:my_project`).

dataset_name est le nom de l'ensemble de données contenant la fonction à supprimer. Sa valeur par défaut correspond à l'ensemble de données par défaut (defaultDataset) dans la requête.

function_name est le nom de la fonction que vous supprimez.

Exemples

L'exemple d'instruction suivant supprime la fonction parseJsonAsStruct contenue dans l'ensemble de données mydataset.

DROP FUNCTION mydataset.parseJsonAsStruct;

L'exemple d'instruction suivant supprime la fonction parseJsonAsStruct de l'ensemble de données sample_dataset dans le projet other_project.

DROP FUNCTION `other_project`.sample_dataset.parseJsonAsStruct;

Instruction DROP PROCEDURE

DROP PROCEDURE [IF EXISTS] [[project_name.]dataset_name.]procedure_name

Où :

IF EXISTS : si cet élément est présent, la requête aboutit lorsque la procédure spécifiée n'existe pas. S'il est absent, la requête échoue lorsque la procédure spécifiée n'existe pas.

project_name est le nom du projet contenant la procédure à supprimer. Sa valeur par défaut correspond au projet qui exécute cette requête LDD. Si le nom du projet contient des caractères spéciaux tels que des signes deux-points, il doit être placé entre accents graves ` (exemple : `google.com:my_project`).

dataset_name est le nom de l'ensemble de données contenant la procédure à supprimer. Sa valeur par défaut correspond à l'ensemble de données par défaut (defaultDataset) dans la requête.

procedure_name est le nom de la procédure que vous supprimez.

Exemples

L'exemple d'instruction suivant supprime la procédure myprocedure contenue dans l'ensemble de données mydataset.

DROP PROCEDURE mydataset.myProcedure;

L'exemple d'instruction suivant supprime la procédure myProcedure contenue dans l'ensemble de données sample_dataset dans le projet other_project.

DROP PROCEDURE `other-project`.sample_dataset.myprocedure;