Écrire des résultats de requête
Ce document explique comment écrire des résultats de requête dans des tables temporaires ou permanentes.
Tables temporaires et permanentes
BigQuery enregistre tous les résultats de requête dans une table, qui peut être permanente ou temporaire :
BigQuery utilise des tables temporaires pour mettre en cache les résultats des requêtes qui ne sont pas écrits dans une table permanente. Les tables sont créées dans un ensemble de données spécial et nommées de manière aléatoire. Vous pouvez également créer des tables temporaires pour les utiliser dans des requêtes à plusieurs instructions et des sessions.
Une fois la requête terminée, la table temporaire peut exister jusqu'à 24 heures. Pour afficher la structure et les données de la table, accédez à la console BigQuery, cliquez sur Historique personnel, puis choisissez la requête qui a créé la table temporaire. Ensuite, dans la ligne Table de destination, cliquez sur Table temporaire.
L'accès aux données d'une table temporaire est limité à l'utilisateur ou au compte de service qui a créé la tâche de requête.
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. Les tables temporaires sont créées dans la même région que la ou les tables interrogées.
Une table permanente peut être une table nouvelle ou existante dans un ensemble de données auquel vous avez accès. Si vous écrivez des résultats de requête dans une nouvelle table, le stockage des données vous est facturé. Lorsque vous écrivez des résultats de requête dans une table permanente, les tables que vous interrogez doivent se trouver dans le même emplacement que l'ensemble de données qui contient la table de destination.
Vous ne pouvez pas enregistrer les résultats de la requête dans une table temporaire lorsque la règle d'administration de l'organisation limitée au domaine est activée. Pour contourner ce problème, désactivez temporairement la règle d'administration limitée au domaine, exécutez la requête, puis réactivez la règle. Vous pouvez également enregistrer les résultats de la requête dans une table de destination.
Autorisations requises
Pour écrire les résultats d'une requête dans une table, vous devez au minimum disposer des autorisations suivantes :
bigquery.tables.create
pour créer une tablebigquery.tables.updateData
pour écrire des données dans une nouvelle table, écraser une table ou ajouter des données à une tablebigquery.jobs.create
pour exécuter une tâche de requête
Des autorisations supplémentaires, par exemple bigquery.tables.getData
, peuvent être nécessaires pour accéder aux données que vous interrogez.
Les rôles IAM prédéfinis suivants incluent les autorisations bigquery.tables.create
et bigquery.tables.updateData
:
bigquery.dataEditor
bigquery.dataOwner
bigquery.admin
Les rôles IAM prédéfinis suivants incluent les autorisations bigquery.jobs.create
:
bigquery.user
bigquery.jobUser
bigquery.admin
En outre, si un utilisateur possède les autorisations bigquery.datasets.create
, il obtient également un accès bigquery.dataOwner
à l'ensemble de données qu'il crée.
L'accès correspondant au rôle bigquery.dataOwner
donne à l'utilisateur la possibilité de créer et de mettre à jour des tables dans l'ensemble de données.
Pour en savoir plus sur les rôles et les autorisations IAM dans BigQuery, consultez la page Rôles prédéfinis et autorisations.
Écrire des résultats de requête dans une table permanente
Lorsque vous écrivez des résultats de requête dans une table permanente, vous pouvez créer une table, ajouter les résultats à une table existante ou remplacer une table existante.
Écrire des résultats de requête
Procédez comme suit pour écrire les résultats de votre requête dans une table permanente. Pour maîtriser vos coûts, vous pouvez prévisualiser les données avant d'exécuter la requête.
Console
Ouvrez la page BigQuery dans la console Google Cloud.
Dans le panneau Explorateur, développez votre projet et sélectionnez un ensemble de données.
Saisissez une requête SQL valide.
Cliquez sur More (Plus), puis sélectionnez Query settings (Paramètres de requête).
Sélectionnez l'option Définir une table de destination pour les résultats de la requête.
Dans la section Destination, sélectionnez l'ensemble de données dans lequel vous souhaitez créer la table, puis choisissez un ID de table.
Dans la section Préférence d'écriture pour la table de destination, choisissez l'une des options suivantes :
- Écrire si la table est vide : n'écrit les résultats de requête dans la table que si celle-ci est vide.
- Append to table (Ajouter à la table) : ajoute les résultats de requête à une table existante.
- Écraser la table : écrase une table existante portant le même nom à l'aide des résultats de requête.
Facultatif : Dans le champ Emplacement des données, sélectionnez votre emplacement.
Pour mettre à jour les paramètres de la requête, cliquez sur Enregistrer.
Cliquez sur Exécuter. Cette action crée une tâche de requête qui écrit les résultats dans la table spécifiée.
Si vous oubliez de spécifier une table de destination avant d'exécuter la requête, vous pouvez également copier la table de résultats mise en cache dans une table permanente en cliquant sur le bouton Enregistrer les résultats situé au-dessus de l'éditeur.
SQL
L'exemple suivant utilise l'instruction CREATE TABLE
pour créer la table trips
à partir de données figurant dans la table bikeshare_trips
publique :
Dans la console Google Cloud, accédez à la page BigQuery.
Dans l'éditeur de requête, saisissez l'instruction suivante :
CREATE TABLE mydataset.trips AS ( SELECT bike_id, start_time, duration_minutes FROM bigquery-public-data.austin_bikeshare.bikeshare_trips );
Cliquez sur
Exécuter.
Pour en savoir plus sur l'exécution des requêtes, consultez Exécuter une requête interactive.
Pour en savoir plus, consultez la section Créer une table à partir d'une table existante.
bq
-
In the Google Cloud console, activate Cloud Shell.
At the bottom of the Google Cloud console, a Cloud Shell session starts and displays a command-line prompt. Cloud Shell is a shell environment with the Google Cloud CLI already installed and with values already set for your current project. It can take a few seconds for the session to initialize.
Saisissez la commande
bq query
en spécifiant l'option--destination_table
pour créer une table permanente basée sur les résultats de la requête. Spécifiez l'optionuse_legacy_sql=false
pour utiliser la syntaxe GoogleSQL. Pour écrire les résultats des requêtes dans une table qui n'est pas dans votre projet par défaut, ajoutez l'ID du projet au nom de l'ensemble de données en respectant le format suivant :project_id:dataset
.(Facultatif) Spécifiez l'option
--location
et définissez la valeur correspondant à votre localisation.Pour contrôler la disposition en écriture d'une table de destination existante, fournissez l'une des options facultatives suivantes :
--append_table
: si la table de destination existe, les résultats de requête y sont ajoutés.--replace
: si la table de destination existe, elle est remplacée par les résultats de requête.bq --location=location query \ --destination_table project_id:dataset.table \ --use_legacy_sql=false 'query'
Remplacez les éléments suivants :
location
représente le nom de l'emplacement utilisé pour traiter la requête. L'option--location
est facultative. Par exemple, si vous utilisez BigQuery dans la région de Tokyo, vous pouvez définir la valeur de l'option surasia-northeast1
. Vous pouvez spécifier une valeur par défaut pour l'emplacement à l'aide du fichier.bigqueryrc
.project_id
est l'ID de votre projet.dataset
représente le nom de l'ensemble de données contenant la table dans laquelle vous écrivez les résultats de requête.table
représente le nom de la table dans laquelle vous écrivez les résultats de requête.query
est une requête dans la syntaxe GoogleSQL.Si aucune option de disposition en écriture n'est spécifiée, le comportement par défaut consiste à écrire les résultats dans la table uniquement si celle-ci est vide. Si la table existe et qu'elle n'est pas vide, l'erreur suivante est renvoyée :
BigQuery error in query operation: Error processing job project_id:bqjob_123abc456789_00000e1234f_1: Already Exists: Table project_id:dataset.table
.Exemples :
Saisissez la commande suivante pour écrire les résultats de la requête dans une table de destination nommée
mytable
au sein de l'ensemble de donnéesmydataset
. L'ensemble de données se trouve dans votre projet par défaut. Comme aucune option de disposition en écriture n'est spécifiée dans la commande, la table doit être nouvelle ou vide. Dans le cas contraire, une erreurAlready exists
est renvoyée. La requête récupère les données de l'ensemble de données public USA Name Data.bq query \ --destination_table mydataset.mytable \ --use_legacy_sql=false \ 'SELECT name, number FROM `bigquery-public-data`.usa_names.usa_1910_current WHERE gender = "M" ORDER BY number DESC'
Saisissez la commande suivante pour que les résultats de requête écrasent une table de destination nommée
mytable
se trouvant dans l'ensemble de donnéesmydataset
. L'ensemble de données se trouve dans votre projet par défaut. La commande utilise l'option--replace
pour écraser la table de destination.bq query \ --destination_table mydataset.mytable \ --replace \ --use_legacy_sql=false \ 'SELECT name, number FROM `bigquery-public-data`.usa_names.usa_1910_current WHERE gender = "M" ORDER BY number DESC'
Saisissez la commande suivante pour ajouter les résultats de requête à une table de destination nommée
mytable
au sein de l'ensemble de donnéesmydataset
. L'ensemble de données se trouve dans le projetmy-other-project
, et non dans votre projet par défaut. La commande utilise l'option--append_table
pour ajouter les résultats de requête à la table de destination.bq query \ --append_table \ --use_legacy_sql=false \ --destination_table my-other-project:mydataset.mytable \ 'SELECT name, number FROM `bigquery-public-data`.usa_names.usa_1910_current WHERE gender = "M" ORDER BY number DESC'
Le résultat de chacun de ces exemples ressemble à ce qui suit. Pour des raisons de lisibilité, certains résultats sont tronqués.
Waiting on bqjob_r123abc456_000001234567_1 ... (2s) Current status: DONE +---------+--------+ | name | number | +---------+--------+ | Robert | 10021 | | John | 9636 | | Robert | 9297 | | ... | +---------+--------+
API
Pour enregistrer les résultats de requête dans une table permanente, appelez la méthode jobs.insert
, configurez une tâche query
et ajoutez une valeur pour la propriété destinationTable
. Pour contrôler la disposition en écriture d'une table de destination existante, configurez la propriété writeDisposition
.
Pour contrôler la zone de traitement de la tâche de requête, spécifiez la propriété location
dans la section jobReference
de la ressource de tâche.
Go
Avant d'essayer cet exemple, suivez les instructions de configuration pour Go du guide de démarrage rapide de BigQuery : Utiliser les bibliothèques clientes. Pour en savoir plus, consultez la documentation de référence de l'API BigQuery pour Go.
Pour vous authentifier auprès de BigQuery, configurez le service Identifiants par défaut de l'application. Pour en savoir plus, consultez la page Configurer l'authentification pour les bibliothèques clientes.
Java
Avant d'essayer cet exemple, suivez les instructions de configuration pour Java du guide de démarrage rapide de BigQuery : Utiliser les bibliothèques clientes. Pour en savoir plus, consultez la documentation de référence de l'API BigQuery pour Java.
Pour vous authentifier auprès de BigQuery, configurez le service Identifiants par défaut de l'application. Pour en savoir plus, consultez la page Configurer l'authentification pour les bibliothèques clientes.
Pour enregistrer des résultats de requête dans une table permanente, définissez la table de destination sur l'identifiant TableId souhaité dans une configuration QueryJobConfiguration.
Node.js
Avant d'essayer cet exemple, suivez les instructions de configuration pour Node.js du guide de démarrage rapide de BigQuery : Utiliser les bibliothèques clientes. Pour en savoir plus, consultez la documentation de référence de l'API BigQuery pour Node.js.
Pour vous authentifier auprès de BigQuery, configurez le service Identifiants par défaut de l'application. Pour en savoir plus, consultez la page Configurer l'authentification pour les bibliothèques clientes.
Python
Avant d'essayer cet exemple, suivez les instructions de configuration pour Python du guide de démarrage rapide de BigQuery : Utiliser les bibliothèques clientes. Pour en savoir plus, consultez la documentation de référence de l'API BigQuery pour Python.
Pour vous authentifier auprès de BigQuery, configurez le service Identifiants par défaut de l'application. Pour en savoir plus, consultez la page Configurer l'authentification pour les bibliothèques clientes.
Pour enregistrer des résultats de requête dans une table permanente, créez une configuration QueryJobConfig et définissez la destination sur la valeur TableReference souhaitée. Transmettez la configuration du job à la méthode de requête.Écrire des résultats de requête volumineux
Normalement, les requêtes ont une taille de réponse maximale. Si vous envisagez d'exécuter une requête pouvant renvoyer des résultats plus importants, vous pouvez effectuer l'une des opérations suivantes :
- Dans GoogleSQL, spécifiez une table de destination pour les résultats de requête.
- en ancien SQL, spécifier une table de destination et définir l'option
allowLargeResults
.
Lorsque vous spécifiez une table de destination pour des résultats de requête volumineux, le stockage des données vous est facturé.
Limites
En ancien SQL, l'écriture de résultats volumineux est soumise aux limites suivantes :
- Vous devez spécifier une table de destination.
- Vous ne pouvez pas spécifier de clause
ORDER BY
,TOP
ouLIMIT
de niveau supérieur. Cela annule l'avantage d'utiliserallowLargeResults
, car le résultat de la requête ne peut plus être calculé en parallèle. - Les fonctions de fenêtrage ne renvoient des résultats de requête volumineux que si elles sont utilisées avec une clause
PARTITION BY
.
Écrire des résultats volumineux en ancien SQL
Pour écrire des ensembles de résultats volumineux en ancien SQL :
Console
Dans la console Google Cloud, ouvrez la page "BigQuery".
Cliquez sur Saisir une nouvelle requête.
Saisissez une requête SQL valide dans la zone de texte de l'éditeur de requête. Utilisez le préfixe
#legacySQL
ou assurez-vous que la case Utiliser l'ancien SQL est cochée dans les paramètres de requête.Cliquez sur More (Plus), puis sélectionnez Query settings (Paramètres de requête).
Pour Destination, cochez la case Set a destination table for query results (Définir une table de destination pour les résultats de la requête).
Pour Ensemble de données, choisissez l'ensemble de données où sera stockée la table.
Dans le champ ID de la table, saisissez un nom de table.
Si vous écrivez un ensemble de résultats volumineux dans une table existante, vous pouvez utiliser les options Destination table write preference (Préférence d'écriture pour la table de destination) pour contrôler la disposition en écriture de la table de destination :
- Écrire si la table est vide : n'écrit les résultats de requête dans la table que si celle-ci est vide.
- Append to table (Ajouter à la table) : ajoute les résultats de requête à une table existante.
- Écraser la table : écrase une table existante portant le même nom à l'aide des résultats de requête.
Pour Taille des résultats, cochez la case Autoriser un nombre élevé de résultats (aucune limite).
Facultatif : Dans le champ Emplacement des données, sélectionnez l'emplacement de vos données.
Cliquez sur Enregistrer pour mettre à jour les paramètres de la requête.
Cliquez sur Exécuter. Cette action crée une tâche de requête qui écrit les résultats de requête volumineux dans la table spécifiée.
bq
Utilisez conjointement les options --allow_large_results
et --destination_table
pour créer la table de destination qui doit contenir l'ensemble de résultats volumineux. Comme l'option --allow_large_results
ne s'applique qu'à l'ancien SQL, vous devez également spécifier l'option --use_legacy_sql=true
. Pour écrire les résultats des requêtes dans une table qui n'est pas dans votre projet par défaut, ajoutez l'ID du projet au nom de l'ensemble de données au format suivant : PROJECT_ID:DATASET
.
Spécifiez l'option --location
et définissez la valeur correspondant à votre emplacement.
Pour contrôler la disposition en écriture d'une table de destination existante, fournissez l'une des options facultatives suivantes :
--append_table
: si la table de destination existe, les résultats de requête y sont ajoutés.--replace
: si la table de destination existe, elle est remplacée par les résultats de requête.
bq --location=location query \ --destination_table PROJECT_ID:DATASET.TABLE \ --use_legacy_sql=true \ --allow_large_results "QUERY"
Remplacez les éléments suivants :
LOCATION
représente le nom de l'emplacement utilisé pour traiter la requête. L'option--location
est facultative. Par exemple, si vous utilisez BigQuery dans la région de Tokyo, vous pouvez définir la valeur de l'option surasia-northeast1
. Vous pouvez définir une valeur par défaut correspondant à l'emplacement à l'aide du fichier.bigqueryrc
.PROJECT_ID
est l'ID de votre projet.DATASET
représente le nom de l'ensemble de données contenant la table dans laquelle vous écrivez les résultats de requête.TABLE
représente le nom de la table dans laquelle vous écrivez les résultats de requête.QUERY
est une requête dans une syntaxe en ancien SQL.
Exemples :
Saisissez la commande suivante pour écrire les résultats de requête volumineux dans une table de destination nommée mytable
dans l'ensemble de données mydataset
. L'ensemble de données se trouve dans votre projet par défaut. Aucune option de disposition en écriture n'étant spécifiée dans la commande, la table doit être nouvelle ou vide. Dans le cas contraire, une erreur Already exists
est renvoyée. La requête récupère les données de l'ensemble de données public USA Name Data.
Cette requête est utilisée à titre d'exemple uniquement. L'ensemble de résultats renvoyé ne dépasse pas la taille de réponse maximale.
bq query \
--destination_table mydataset.mytable \
--use_legacy_sql=true \
--allow_large_results \
"SELECT
name,
number
FROM
[bigquery-public-data:usa_names.usa_1910_current]
WHERE
gender = 'M'
ORDER BY
number DESC"
Saisissez la commande suivante pour que les résultats de requête volumineux écrasent une table de destination nommée mytable
se trouvant dans l'ensemble de données mydataset
. Cet ensemble de données se trouve dans le projet myotherproject
, et non dans votre projet par défaut. La commande utilise l'option --replace
pour écraser la table de destination.
bq query \
--destination_table mydataset.mytable \
--replace \
--use_legacy_sql=true \
--allow_large_results \
"SELECT
name,
number
FROM
[bigquery-public-data:usa_names.usa_1910_current]
WHERE
gender = 'M'
ORDER BY
number DESC"
Saisissez la commande suivante pour ajouter les résultats de requête volumineux à une table de destination nommée mytable
dans l'ensemble de données mydataset
. Cet ensemble de données se trouve dans le projet myotherproject
, et non dans votre projet par défaut. La commande utilise l'option --append_table
pour ajouter les résultats de requête à la table de destination.
bq query \
--destination_table myotherproject:mydataset.mytable \
--append_table \
--use_legacy_sql=true \
--allow_large_results \
"SELECT
name,
number
FROM
[bigquery-public-data:usa_names.usa_1910_current]
WHERE
gender = 'M'
ORDER BY
number DESC"
API
Pour écrire des résultats volumineux dans une table de destination, appelez la méthode jobs.insert
, configurez une tâche de type query
et définissez la propriété allowLargeResults
sur true
.
Spécifiez la table de destination à l'aide de la propriété destinationTable
. Pour contrôler la disposition en écriture d'une table de destination existante, configurez la propriété writeDisposition
.
Spécifiez votre emplacement dans la propriété location
de la section jobReference
de la ressource de tâche.
Go
Avant d'essayer cet exemple, suivez les instructions de configuration pour Go du guide de démarrage rapide de BigQuery : Utiliser les bibliothèques clientes. Pour en savoir plus, consultez la documentation de référence de l'API BigQuery pour Go.
Pour vous authentifier auprès de BigQuery, configurez le service Identifiants par défaut de l'application. Pour en savoir plus, consultez la page Configurer l'authentification pour les bibliothèques clientes.
Java
Pour que des résultats volumineux puissent être renvoyés, définissez l'option Autoriser un nombre élevé de résultats sur true
et la table de destination sur l'identifiant TableId souhaité dans une configuration QueryJobConfiguration.
Avant d'essayer cet exemple, suivez les instructions de configuration pour Java du guide de démarrage rapide de BigQuery : Utiliser les bibliothèques clientes. Pour en savoir plus, consultez la documentation de référence de l'API BigQuery pour Java.
Pour vous authentifier auprès de BigQuery, configurez le service Identifiants par défaut de l'application. Pour en savoir plus, consultez la page Configurer l'authentification pour les bibliothèques clientes.
Node.js
Avant d'essayer cet exemple, suivez les instructions de configuration pour Node.js du guide de démarrage rapide de BigQuery : Utiliser les bibliothèques clientes. Pour en savoir plus, consultez la documentation de référence de l'API BigQuery pour Node.js.
Pour vous authentifier auprès de BigQuery, configurez le service Identifiants par défaut de l'application. Pour en savoir plus, consultez la page Configurer l'authentification pour les bibliothèques clientes.
Python
Avant d'essayer cet exemple, suivez les instructions de configuration pour Python du guide de démarrage rapide de BigQuery : Utiliser les bibliothèques clientes. Pour en savoir plus, consultez la documentation de référence de l'API BigQuery pour Python.
Pour vous authentifier auprès de BigQuery, configurez le service Identifiants par défaut de l'application. Pour en savoir plus, consultez la page Configurer l'authentification pour les bibliothèques clientes.
Télécharger et enregistrer des résultats de requête depuis la console Google Cloud
Après avoir exécuté une requête SQL à l'aide de la console Google Cloud, vous pouvez enregistrer les résultats dans un autre emplacement. Vous pouvez utiliser la console Google Cloud pour télécharger les résultats de requête dans un fichier local, Google Sheets ou Google Drive. Si vous triez d'abord les résultats de la requête par colonne, l'ordre est conservé dans les données téléchargées. L'outil de ligne de commande bq et l'API ne permettent pas d'enregistrer les résultats dans un fichier local, Google Sheets ou Google Drive.
Limites
Le téléchargement et l'enregistrement des résultats de requête sont soumis aux restrictions suivantes :
- Vous ne pouvez télécharger les résultats de requête qu'au format CSV ou JSON délimité par un retour à la ligne.
- Vous ne pouvez pas enregistrer des résultats de requête contenant des données imbriquées et répétées dans Google Sheets.
- Pour enregistrer des résultats de requête dans Google Drive à l'aide de la console Google Cloud, l'ensemble de résultats ne doit pas dépasser 1 Go. Si les résultats sont plus volumineux, vous pouvez les enregistrer dans une table.
- Lorsque vous enregistrez des résultats de requête dans un fichier CSV local, la taille de téléchargement maximale est de 10 Mo.
La taille de téléchargement maximale est basée sur la taille de chaque ligne renvoyée dans la réponse de la méthode
tabledata.list
et peut varier en fonction du schéma des résultats de la requête. Par conséquent, la taille du fichier CSV téléchargé peut varier et être inférieure à la limite de taille de téléchargement maximale. - Vous ne pouvez enregistrer des résultats de requête sur Google Drive qu'au format CSV ou JSON délimité par un retour à la ligne.
Étapes suivantes
- Apprenez à exporter une table vers un fichier JSON de manière automatisée.
- Découvrez les quotas pour les jobs de requête.
- Découvrez les tarifs de stockage de BigQuery.