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 :
- créer des tables, des vues et des fonctions définies par l'utilisateur (User-defined functions, UDF) ;
- modifier des tables ;
- supprimer des tables et des vues.
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
Accédez à la page "BigQuery" de Cloud Console.
Cliquez sur Saisir une nouvelle requête.
Saisissez l'instruction LDD dans la zone de texte de l'éditeur de requête. Exemple :
CREATE TABLE mydataset.newtable ( x INT64 )
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_TABLECREATE_TABLE_AS_SELECTDROP_TABLECREATE_VIEWDROP_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'instructionCREATE TABLE IF NOT EXISTSa été envoyée et la table existe. Ou l'instructionDROP TABLE IF EXISTSa été envoyée et la table n'existe pas.REPLACE: la requête a remplacé la cible LDD. Exemple : l'instructionCREATE OR REPLACE TABLEa été envoyée et la table existe déjà.DROP: la requête a supprimé la cible LDD.
ddlTargetTable: lorsque vous envoyez une instructionCREATE TABLE/VIEWou une instructionDROP 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.
Node.js
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.
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_nameest 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_nameest le nom de l'ensemble de données à créer.schema_option_listspé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_statementou bien les deux doivent être présentes. - Lorsque la liste des colonnes et la clause
as query_statementsont toutes deux présentes, BigQuery ignore les noms dans la clauseas query_statementet met les colonnes en correspondance avec leur position dans la liste des colonnes. - Lorsque seule la clause
as query_statementest présente, BigQuery détermine le nom et le type des colonnes à l'aide de la clauseas 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_namecorrespond 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_schemaest semblable à un type de données, mais il accepte une contrainteNOT NULLfacultative pour les types autres queARRAY.column_schemaest également compatible avec des options sur les colonnes de premier niveau et sur les champsSTRUCT.
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 clauseAS query_statement.DATE(_PARTITIONTIME). Équivaut à_PARTITIONDATE. Cette syntaxe ne peut pas être utilisée avec la clauseAS query_statement.<date_column>. Partitionner en fonction d'une colonneDATEavec des partitions quotidiennes.DATE({ <timestamp_column> | <datetime_column> }). Partitionner en fonction d'une colonneTIMESTAMPouDATETIMEavec des partitions quotidiennes.DATETIME_TRUNC(<datetime_column>, { DAY | HOUR | MONTH | YEAR }). Partitionner en fonction d'une colonneDATETIMEavec le type de partitionnement spécifié.TIMESTAMP_TRUNC(<timestamp_column>, { DAY | HOUR | MONTH | YEAR }). Partitionner une colonneTIMESTAMPavec 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 clauseAS query_statement.DATE_TRUNC(<date_column>, { MONTH | YEAR }). Partitionner en fonction d'une colonneDATEavec 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ù :startest la valeur de début inclusive du partitionnement par plages.endest la valeur de fin exclusive du partitionnement par plages.intervalest 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 : Cette propriété est équivalente à la propriété de ressource de table expirationTime. |
partition_expiration_days |
|
Exemple : 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 |
|
Exemple : 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 |
|
Exemple : 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 |
|
Exemple : Cette propriété est équivalente à la propriété de ressource de table friendlyName. |
description |
|
Exemple : Cette propriété est équivalente à la propriété de ressource de table description. |
labels |
|
Exemple : 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,CREATEetUPDATE - Des fonctions définies par l'utilisateur, des fonctions d'agrégation ou des fonctions d'analyse
- Les fonctions scalaires suivantes :
ARRAY_TO_STRINGREPLACEREGEXP_REPLACERANDFORMATLPADRPADREPEATSESSION_USERGENERATE_ARRAYGENERATE_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 |
|
Exemple : 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 LMDINSERT. - 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 instructionCREATE 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îneSTRING) etword_count(un type de donnéesINT64repré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
REQUIREDcontenant a (un tableau de chaînes), b (une valeur booléenneREQUIRED) et c (un nombre à virgule flottanteNULLABLE) 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
DATEde 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 : Cette propriété est équivalente à la propriété de ressource de table expirationTime. |
friendly_name |
|
Exemple : Cette propriété est équivalente à la propriété de ressource de table friendlyName. |
description |
|
Exemple : Cette propriété est équivalente à la propriété de ressource de table description. |
labels |
|
Exemple : 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,CREATEetUPDATE - Des fonctions définies par l'utilisateur, des fonctions d'agrégation ou des fonctions d'analyse
- Les fonctions scalaires suivantes :
ARRAY_TO_STRINGREPLACEREGEXP_REPLACERANDFORMATLPADRPADREPEATSESSION_USERGENERATE_ARRAYGENERATE_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 : |
refresh_interval_minutes |
FLOAT64 |
Exemple : |
expiration_timestamp |
TIMESTAMP |
Exemple : Cette propriété est équivalente à la propriété de ressource de table expirationTime. |
friendly_name |
|
Exemple : Cette propriété est équivalente à la propriété de ressource de table friendlyName. |
description |
|
Exemple : Cette propriété est équivalente à la propriété de ressource de table description. |
labels |
|
Exemple : 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_nameest 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_nameest le nom de l'ensemble de données dans lequel vous créez la table.table_nameest le nom de la table externe.column_nameest le nom d'une colonne de la table.column_schemaspécifie le schéma de la colonne. Cette clause utilise la même syntaxe que la définitioncolumn_schemade l'instructionCREATE TABLE. Si vous ne l'incluez pas, BigQuery détecte automatiquement le schéma.partition_column_nameest 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_typeest de type de colonne de partition.external_table_option_listspé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 |
Si la valeur est S'applique aux données CSV. |
allow_quoted_newlines |
Si la valeur est S'applique aux données CSV. |
compression |
Type de compression de la source de données. Valeur autorisée : S'applique aux données CSV et JSON. |
description |
Description de cette table. |
enable_logical_types |
Si la valeur est S'applique aux données Avro. |
encoding |
Encodage des caractères des données. Valeurs autorisées : S'applique aux données CSV. |
expiration_timestamp |
Date et heure d'expiration de cette table. Si cette option n'est pas spécifiée, la table n'expire pas. Exemple : |
field_delimiter |
Séparateur des champs dans un fichier CSV. S'applique aux données CSV. |
format |
Format des données externes.
Valeurs autorisées : La valeur |
decimal_target_types |
Détermine comment convertir un type Exemple : |
hive_partition_uri_prefix |
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 : |
ignore_unknown_values |
Si la valeur est S'applique aux données CSV et JSON. |
max_bad_records |
Nombre maximal d'enregistrements incorrects à ignorer lors de la lecture des données. S'applique aux données CSV, JSON et Sheets. |
null_marker |
Chaîne représentant les valeurs S'applique aux données CSV. |
projection_fields |
Liste des propriétés d'entité à charger. S'applique aux données Datastore. |
quote |
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é S'applique aux données CSV. |
require_hive_partition_filter |
Si la valeur est S'applique aux données Avro, CSV, JSON, Parquet et ORC. |
sheet_range |
Plage d'une feuille de calcul Sheets à interroger. S'applique aux données Sheets. Exemple : |
skip_leading_rows |
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 |
Tableau d'URI complets pour les emplacements de données externes. Exemple : |
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 clauseIF 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_typeséparée par une virgule. La valeur deparam_typeest un type de données BigQuery. Pour une fonction SQL UDF, la valeur deparam_typepeut également êtreANY 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 fonctionadd_one(i)renvoie toujoursi + 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, siadd_random(i)renvoiei + 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
RETURNSest facultative. Si la clauseRETURNSest 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
RETURNSest obligatoire. Pour en savoir plus sur les valeurs autorisées pourdata_type, consultez la section Types de données compatibles avec les fonctions JavaScript définies par l'utilisateur.
- Si la fonction est définie en SQL, la clause
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_codecorrespond à 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.
- Chaîne entre guillemets
- 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
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 desql_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
Accédez à la page "BigQuery" de Cloud Console.
Cliquez sur Saisir une nouvelle requête.
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; """;Cliquez sur Run (Exécuter).
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;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 |
|
Exemple : Si Si Si La valeur par défaut est |
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_nameest 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_nameest le nom de l'ensemble de données.schema_set_options_listspé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 : Cette propriété est équivalente à la propriété de ressource de table expirationTime. |
partition_expiration_days |
|
Exemple : 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 |
|
Exemple : 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 |
|
Exemple : 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 |
|
Exemple : Cette propriété est équivalente à la propriété de ressource de table friendlyName. |
description |
|
Exemple : Cette propriété est équivalente à la propriété de ressource de table description. |
labels |
|
Exemple : 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,CREATEetUPDATE - Des fonctions définies par l'utilisateur, des fonctions d'agrégation ou des fonctions d'analyse
- Les fonctions scalaires suivantes :
ARRAY_TO_STRINGREPLACEREGEXP_REPLACERANDFORMATLPADRPADREPEATSESSION_USERGENERATE_ARRAYGENERATE_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_namecorrespond au nom du projet contenant la table. Sa valeur par défaut correspond au projet qui exécute cette requête LDD.dataset_nameest le nom de l'ensemble de données contenant la table.table_nameest le nom de la table à modifier. La table doit déjà exister et posséder un schéma.column_nameest le nom de la colonne à ajouter.column_schemaest le schéma de la colonne. Ce schéma utilise la même syntaxe que le schéma de colonne de l'instructionCREATE 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
RECORDexistants
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 :
NULLsi la nouvelle colonne a été ajoutée avec le modeNULLABLE. Il s'agit du mode par défaut.- Une valeur
ARRAYvide si la nouvelle colonne a été ajoutée avec le modeREPEATED.
Exemples
Ajouter des colonnes
L'exemple suivant ajoute les colonnes suivantes à une table existante nommée mytable :
- Colonne
Ade typeSTRING - Colonne
Bde typeGEOGRAPHY - Colonne
Cde typeNUMERICavec le modeREPEATED - Colonne
Dde typeDATEavec 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
Bde typeGEOGRAPHY - Colonne
Cde typeINT64avec le modeREPEATED - Colonne
Dde typeINT64avec le modeREQUIRED - Colonne
Ede typeTIMESTAMPavec 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_namecorrespond au nom du projet contenant la table. Sa valeur par défaut correspond au projet qui exécute cette requête LDD.dataset_nameest le nom de l'ensemble de données contenant la table.table_nameest le nom de la table à modifier. La table doit déjà exister et posséder un schéma.column_nameest 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
RECORDexistants
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 : Cette propriété est équivalente à la propriété de ressource de table expirationTime. |
friendly_name |
|
Exemple : Cette propriété est équivalente à la propriété de ressource de table friendlyName. |
description |
|
Exemple : Cette propriété est équivalente à la propriété de ressource de table description. |
labels |
|
Exemple : 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,CREATEetUPDATE - Des fonctions définies par l'utilisateur, des fonctions d'agrégation ou des fonctions d'analyse
- Les fonctions scalaires suivantes :
ARRAY_TO_STRINGREPLACEREGEXP_REPLACERANDFORMATLPADRPADREPEATSESSION_USERGENERATE_ARRAYGENERATE_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 : |
refresh_interval_minutes |
FLOAT64 |
Exemple : |
expiration_timestamp |
TIMESTAMP |
Exemple : Cette propriété est équivalente à la propriété de ressource de table expirationTime. |
friendly_name |
|
Exemple : Cette propriété est équivalente à la propriété de ressource de table friendlyName. |
description |
|
Exemple : Cette propriété est équivalente à la propriété de ressource de table description. |
labels |
|
Exemple : 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_nameest 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_nameest 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_namecorrespond au nom du projet contenant la table. Sa valeur par défaut correspond au projet qui exécute cette requête LDD.dataset_nameest le nom de l'ensemble de données contenant la table.table_nameest 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;