INFORMATION_SCHEMA
est une série de vues offrant un accès aux métadonnées sur les ensembles de données, les tables et les vues.
Vous pouvez interroger les vues INFORMATION_SCHEMA.TABLES
et INFORMATION_SCHEMA.TABLE_OPTIONS
pour récupérer des métadonnées sur les tables et les vues d'un projet. Vous pouvez également interroger les vues INFORMATION_SCHEMA.COLUMNS
et INFORMATION_SCHEMA.COLUMN_FIELD_PATHS
pour récupérer des métadonnées sur les colonnes (champs) d'une table.
Les vues TABLES
et TABLE_OPTIONS
contiennent également des informations générales sur les vues. Pour obtenir des informations détaillées, interrogez plutôt la vue INFORMATION_SCHEMA.VIEWS
.
Autorisations requises
Pour obtenir des informations sur les tables, vous devez au minimum disposer des autorisations bigquery.tables.get
. Les rôles Cloud IAM prédéfinis suivants incluent les autorisations bigquery.tables.get
:
bigquery.metadataViewer
bigquery.dataViewer
bigquery.dataOwner
bigquery.dataEditor
bigquery.admin
En outre, si un utilisateur dispose des autorisations bigquery.datasets.create
, lorsqu'il crée un ensemble de données, il obtient également le rôle bigquery.dataOwner
qui lui permet d'y accéder.
L'accès bigquery.dataOwner
donne à l'utilisateur la possibilité de récupérer les métadonnées d'une table.
Pour en savoir plus sur les rôles et les autorisations Cloud IAM dans BigQuery, consultez la page Contrôle des accès.
Vue TABLES
Lorsque vous interrogez la vue INFORMATION_SCHEMA.TABLES
, les résultats de la requête contiennent une ligne pour chaque table ou vue d'un ensemble de données.
Les requêtes exécutées sur la vue INFORMATION_SCHEMA.TABLES
doivent avoir un qualificatif d'ensemble de données. L'utilisateur qui soumet la requête doit avoir accès à l'ensemble de données contenant les tables ou les vues.
La vue INFORMATION_SCHEMA.TABLES
présente le schéma suivant :
Nom de la colonne | Type de données | Valeur |
---|---|---|
TABLE_CATALOG |
STRING |
Nom du projet qui contient l'ensemble de données |
TABLE_SCHEMA |
STRING |
Nom de l'ensemble de données contenant la table ou la vue (également appelé datasetId ) |
TABLE_NAME |
STRING |
Nom de la table ou de la vue (également appelé tableId ) |
TABLE_TYPE |
STRING |
Type de table :
|
IS_INSERTABLE_INTO |
STRING |
YES ou NO suivant que la table accepte ou non les instructions LMD INSERT |
IS_TYPED |
STRING |
La valeur est toujours NO |
CREATION_TIME |
TIMESTAMP |
Date/Heure de création de la table |
Pour en savoir plus sur les propriétés des ensembles de données, consultez la page concernant la ressource d'ensemble de données dans la documentation de l'API REST. Pour en savoir plus sur les propriétés des tables et des vues, consultez la page relative à la ressource de table dans la documentation de l'API REST.
Exemples
Exemple 1 :
L'exemple suivant récupère toutes les colonnes de la vue INFORMATION_SCHEMA.TABLES
, à l'exception de is_typed
qui est réservée en vue d'une utilisation ultérieure. Les métadonnées renvoyées concernent toutes les tables de l'ensemble de données mydataset
de votre projet par défaut (myproject
).
mydataset
contient les tables suivantes :
mytable1
: table BigQuery standardmyview1
: vue BigQuery
Les requêtes exécutées sur la vue INFORMATION_SCHEMA.TABLES
doivent avoir un qualificatif d'ensemble de données. L'utilisateur qui soumet la requête doit avoir accès à l'ensemble de données contenant les tables.
Pour exécuter la requête sur un projet autre que celui par défaut, ajoutez l'ID du projet à l'ensemble de données, en respectant le format suivant : `project_id`.dataset.INFORMATION_SCHEMA.view
. Par exemple, `myproject`.mydataset.INFORMATION_SCHEMA.TABLES
.
Pour exécuter la requête :
Console
Ouvrez l'interface utilisateur Web de BigQuery dans la console GCP.
Saisissez la requête SQL standard suivante dans la zone Éditeur de requête.
INFORMATION_SCHEMA
requiert la syntaxe SQL standard. Le langage SQL standard est la syntaxe par défaut dans la console GCP.SELECT * EXCEPT(is_typed) FROM mydataset.INFORMATION_SCHEMA.TABLES
Cliquez sur Exécuter.
CLI
Exécutez la commande query
, puis spécifiez la syntaxe SQL standard à l'aide de l'indicateur --nouse_legacy_sql
ou --use_legacy_sql=false
. La syntaxe SQL standard est requise pour les requêtes INFORMATION_SCHEMA
.
Pour exécuter la requête, saisissez la commande suivante :
bq query --nouse_legacy_sql \ 'SELECT * EXCEPT(is_typed) FROM mydataset.INFORMATION_SCHEMA.TABLES'
Les résultats doivent se présenter sous la forme suivante :
+----------------+---------------+----------------+------------+--------------------+---------------------+ | table_catalog | table_schema | table_name | table_type | is_insertable_into | creation_time | +----------------+---------------+----------------+------------+--------------------+---------------------+ | myproject | mydataset | mytable1 | BASE TABLE | YES | 2018-10-29 20:34:44 | | myproject | mydataset | myview1 | VIEW | NO | 2018-12-29 00:19:20 | +----------------+---------------+----------------+------------+--------------------+---------------------+
Exemple 2 :
L'exemple suivant récupère toutes les tables de type BASE TABLE
de la vue INFORMATION_SCHEMA.TABLES
. La colonne is_typed
est exclue. Les métadonnées renvoyées concernent les tables de l'ensemble de données mydataset
de votre projet par défaut : myproject
.
Les requêtes exécutées sur la vue INFORMATION_SCHEMA.TABLES
doivent avoir un qualificatif d'ensemble de données. L'utilisateur qui soumet la requête doit avoir accès à l'ensemble de données contenant les tables.
Pour exécuter la requête sur un projet autre que celui par défaut, ajoutez l'ID du projet à l'ensemble de données, en respectant le format suivant : `project_id`.dataset.INFORMATION_SCHEMA.view
. Par exemple, `myproject`.mydataset.INFORMATION_SCHEMA.TABLES
.
Pour exécuter la requête :
Console
Ouvrez l'interface utilisateur Web de BigQuery dans la console GCP.
Saisissez la requête SQL standard suivante dans la zone Éditeur de requête.
INFORMATION_SCHEMA
requiert la syntaxe SQL standard. Le langage SQL standard est la syntaxe par défaut dans la console GCP.SELECT * EXCEPT(is_typed) FROM mydataset.INFORMATION_SCHEMA.TABLES WHERE table_type="BASE TABLE"
Cliquez sur Exécuter.
CLI
Exécutez la commande query
, puis spécifiez la syntaxe SQL standard à l'aide de l'indicateur --nouse_legacy_sql
ou --use_legacy_sql=false
. La syntaxe SQL standard est requise pour les requêtes INFORMATION_SCHEMA
.
Pour exécuter la requête, saisissez la commande suivante :
bq query --nouse_legacy_sql \ 'SELECT * EXCEPT(is_typed) FROM mydataset.INFORMATION_SCHEMA.TABLES WHERE table_type="BASE TABLE"'
Les résultats doivent se présenter sous la forme suivante :
+----------------+---------------+----------------+------------+--------------------+---------------------+ | table_catalog | table_schema | table_name | table_type | is_insertable_into | creation_time | +----------------+---------------+----------------+------------+--------------------+---------------------+ | myproject | mydataset | mytable1 | BASE TABLE | NO | 2018-10-31 22:40:05 | +----------------+---------------+----------------+------------+--------------------+---------------------+
Vue TABLE_OPTIONS
Lorsque vous interrogez la vue INFORMATION_SCHEMA.TABLE_OPTIONS
, les résultats de la requête contiennent une ligne pour chaque table ou vue d'un ensemble de données.
Les requêtes exécutées sur la vue INFORMATION_SCHEMA.TABLE_OPTIONS
doivent avoir un qualificatif d'ensemble de données. L'utilisateur qui soumet la requête doit avoir accès à l'ensemble de données contenant les tables ou les vues.
La vue INFORMATION_SCHEMA.TABLE_OPTIONS
présente le schéma suivant :
Nom de la colonne | Type de données | Valeur |
---|---|---|
TABLE_CATALOG |
STRING |
Nom du projet qui contient l'ensemble de données |
TABLE_SCHEMA |
STRING |
Nom de l'ensemble de données contenant la table ou la vue (également appelé datasetId ) |
TABLE_NAME |
STRING |
Nom de la table ou de la vue (également appelé tableId ) |
OPTION_NAME |
STRING |
Une des valeurs de nom figurant dans la table d'options |
OPTION_TYPE |
STRING |
Une des valeurs de type de données figurant dans la table d'options |
OPTION_VALUE |
STRING |
Une des options de valeur figurant dans la table d'options |
Table d'options
OPTION_NAME |
OPTION_TYPE |
OPTION_VALUE |
---|---|---|
partition_expiration_days |
FLOAT64 |
Durée de vie par défaut, en jours, de toutes les partitions d'une table partitionnée |
expiration_timestamp |
FLOAT64 |
Durée de vie par défaut, en jours, de la table |
kms_key_name |
STRING |
Nom de la clé Cloud KMS employée pour chiffrer la table |
friendly_name |
STRING |
Nom descriptif de la table |
description |
STRING |
Description de la table |
labels |
ARRAY<STRUCT<STRING, STRING>> |
Tableau de valeurs STRUCT représentant les étiquettes de la table |
Pour en savoir plus sur les propriétés des ensembles de données, consultez la page concernant la ressource d'ensemble de données dans la documentation de l'API REST. Pour en savoir plus sur les propriétés des tables et des vues, consultez la page relative à la ressource de table dans la documentation de l'API REST.
Exemples
Exemple 1 :
L'exemple suivant récupère les délais d'expiration par défaut pour toutes les tables de l'ensemble de données mydataset
de votre projet par défaut (myproject
) en interrogeant la vue INFORMATION_SCHEMATA.TABLE_OPTIONS
.
Les requêtes exécutées sur la vue INFORMATION_SCHEMA.TABLE_OPTIONS
doivent avoir un qualificatif d'ensemble de données. L'utilisateur qui soumet la requête doit avoir accès à l'ensemble de données contenant les tables.
Pour exécuter la requête sur un projet autre que celui par défaut, ajoutez l'ID du projet à l'ensemble de données, en respectant le format suivant : `project_id`.dataset.INFORMATION_SCHEMA.view
. Par exemple, `myproject`.mydataset.INFORMATION_SCHEMA.TABLE_OPTIONS
.
Pour exécuter la requête :
Console
Ouvrez l'interface utilisateur Web de BigQuery dans la console GCP.
Saisissez la requête SQL standard suivante dans la zone Éditeur de requête.
INFORMATION_SCHEMA
requiert la syntaxe SQL standard. Le langage SQL standard est la syntaxe par défaut dans la console GCP.SELECT * FROM mydataset.INFORMATION_SCHEMA.TABLE_OPTIONS WHERE option_name="expiration_timestamp"
Cliquez sur Exécuter.
CLI
Exécutez la commande query
, puis spécifiez la syntaxe SQL standard à l'aide de l'indicateur --nouse_legacy_sql
ou --use_legacy_sql=false
. La syntaxe SQL standard est requise pour les requêtes INFORMATION_SCHEMA
.
Pour exécuter la requête, saisissez la commande suivante :
bq query --nouse_legacy_sql \ 'SELECT * FROM mydataset.INFORMATION_SCHEMA.TABLE_OPTIONS WHERE option_name="expiration_timestamp"'
Les résultats doivent se présenter sous la forme suivante :
+----------------+---------------+------------+----------------------+-------------+--------------------------------------+ | table_catalog | table_schema | table_name | option_name | option_type | option_value | +----------------+---------------+------------+----------------------+-------------+--------------------------------------+ | myproject | mydataset | mytable1 | expiration_timestamp | TIMESTAMP | TIMESTAMP "2020-01-16T21:12:28.000Z" | | myproject | mydataset | mytable2 | expiration_timestamp | TIMESTAMP | TIMESTAMP "2021-01-01T21:12:28.000Z" | +----------------+---------------+------------+----------------------+-------------+--------------------------------------+
Exemple 2 :
L'exemple suivant récupère les métadonnées sur toutes les tables de l'ensemble de données mydataset
contenant des données de test. La requête utilise les valeurs de l'option description
pour rechercher les tables dont la description contient "test". mydataset
se trouve dans votre projet par défaut (myproject
).
Pour exécuter la requête sur un projet autre que celui par défaut, ajoutez l'ID du projet à l'ensemble de données, en respectant le format suivant : `project_id`.dataset.INFORMATION_SCHEMA.view
. Par exemple, `myproject`.mydataset.INFORMATION_SCHEMA.TABLE_OPTIONS
.
Pour exécuter la requête :
Console
Ouvrez l'interface utilisateur Web de BigQuery dans la console GCP.
Saisissez la requête SQL standard suivante dans la zone Éditeur de requête.
INFORMATION_SCHEMA
requiert la syntaxe SQL standard. Le langage SQL standard est la syntaxe par défaut dans la console GCP.SELECT * FROM mydataset.INFORMATION_SCHEMA.TABLE_OPTIONS WHERE option_name="description" AND option_value LIKE "%test%"
Cliquez sur Exécuter.
CLI
Exécutez la commande query
, puis spécifiez la syntaxe SQL standard à l'aide de l'indicateur --nouse_legacy_sql
ou --use_legacy_sql=false
. La syntaxe SQL standard est requise pour les requêtes INFORMATION_SCHEMA
.
Pour exécuter la requête, saisissez la commande suivante :
bq query --nouse_legacy_sql \ 'SELECT * FROM mydataset.INFORMATION_SCHEMA.TABLE_OPTIONS WHERE option_name="description" AND option_value LIKE "%test%"'
Les résultats doivent se présenter sous la forme suivante :
+----------------+---------------+------------+-------------+-------------+--------------+ | table_catalog | table_schema | table_name | option_name | option_type | option_value | +----------------+---------------+------------+-------------+-------------+--------------+ | myproject | mydataset | mytable1 | description | STRING | "test data" | | myproject | mydataset | mytable2 | description | STRING | "test data" | +----------------+---------------+------------+-------------+-------------+--------------+
Vue COLUMNS
Lorsque vous interrogez la vue INFORMATION_SCHEMA.COLUMNS
, les résultats de la requête contiennent une ligne pour chaque colonne (champ) d'une table.
Les requêtes exécutées sur la vue INFORMATION_SCHEMA.COLUMNS
doivent avoir un qualificatif d'ensemble de données. L'utilisateur qui soumet la requête doit avoir accès à l'ensemble de données contenant les tables.
La vue INFORMATION_SCHEMA.COLUMNS
présente le schéma suivant :
Nom de la colonne | Type de données | Valeur |
---|---|---|
TABLE_CATALOG |
STRING |
Nom du projet qui contient l'ensemble de données |
TABLE_SCHEMA |
STRING |
Nom de l'ensemble de données contenant la table (également appelé datasetId ) |
TABLE_NAME |
STRING |
Nom de la table ou de la vue (également appelé tableId ) |
COLUMN_NAME |
STRING |
Nom de la colonne |
ORDINAL_POSITION |
INT64 |
Décalage avec un indice de 1 de la colonne dans la table. S'il s'agit d'une pseudo-colonne telle que _PARTITIONTIME ou _PARTITIONDATE, la valeur est NULL . |
IS_NULLABLE |
STRING |
YES ou NO selon que le mode de la colonne autorise ou non les valeurs NULL |
DATA_TYPE |
STRING |
Type de données SQL standard de la colonne |
IS_GENERATED |
STRING |
La valeur est toujours NEVER |
GENERATION_EXPRESSION |
STRING |
La valeur est toujours NULL |
IS_STORED |
STRING |
La valeur est toujours NULL |
IS_HIDDEN |
STRING |
YES ou NO selon que la colonne est une pseudo-colonne telle que _PARTITIONTIME ou _PARTITIONDATE |
IS_UPDATABLE |
STRING |
La valeur est toujours NULL |
IS_SYSTEM_DEFINED |
STRING |
YES ou NO selon que la colonne est une pseudo-colonne telle que _PARTITIONTIME ou _PARTITIONDATE |
IS_PARTITIONING_COLUMN |
STRING |
YES ou NO selon que la colonne est une colonne de partitionnement ou non |
CLUSTERING_ORDINAL_POSITION |
STRING |
Décalage avec un indice de 1 de la colonne dans les colonnes de clustering de la table. La valeur est NULL si la table n'est pas une table en cluster. |
Pour en savoir plus sur les propriétés des ensembles de données, consultez la page concernant la ressource d'ensemble de données dans la documentation de l'API REST. Pour en savoir plus sur les propriétés des tables et des vues, consultez la page relative à la ressource de table dans la documentation de l'API REST.
Exemples
L'exemple suivant récupère les métadonnées de la vue INFORMATION_SCHEMA.COLUMNS
pour la table population_by_zip_2010
de l'ensemble de données census_bureau_usa
. Ce dernier fait partie du programme d'ensembles de données publics de BigQuery.
Comme la table que vous interrogez se trouve dans un autre projet (le projet bigquery-public-data
), ajoutez l'ID de ce dernier à l'ensemble de données, en respectant le format suivant : `project_id`.dataset.INFORMATION_SCHEMA.view
. Par exemple, `bigquery-public-data`.census_bureau_usa.INFORMATION_SCHEMA.TABLES
.
Les colonnes suivantes sont exclues des résultats de la requête, car elles sont actuellement réservées en vue d'une utilisation ultérieure :
IS_GENERATED
GENERATION_EXPRESSION
IS_STORED
IS_UPDATABLE
Les requêtes exécutées sur la vue INFORMATION_SCHEMA.COLUMNS
doivent avoir un qualificatif d'ensemble de données. L'utilisateur qui soumet la requête doit avoir accès à l'ensemble de données contenant les tables.
Pour exécuter la requête :
Console
Ouvrez l'interface utilisateur Web de BigQuery dans la console GCP.
Saisissez la requête SQL standard suivante dans la zone Éditeur de requête.
INFORMATION_SCHEMA
requiert la syntaxe SQL standard. Le langage SQL standard est la syntaxe par défaut dans la console GCP.SELECT * EXCEPT(is_generated, generation_expression, is_stored, is_updatable) FROM `bigquery-public-data`.census_bureau_usa.INFORMATION_SCHEMA.COLUMNS WHERE table_name="population_by_zip_2010"
Cliquez sur Exécuter.
CLI
Exécutez la commande query
, puis spécifiez la syntaxe SQL standard à l'aide de l'indicateur --nouse_legacy_sql
ou --use_legacy_sql=false
. La syntaxe SQL standard est requise pour les requêtes INFORMATION_SCHEMA
.
Pour exécuter la requête, saisissez la commande suivante :
bq query --nouse_legacy_sql \ 'SELECT * EXCEPT(is_generated, generation_expression, is_stored, is_updatable) FROM `bigquery-public-data`.census_bureau_usa.INFORMATION_SCHEMA.COLUMNS WHERE table_name="population_by_zip_2010"'
Les résultats doivent se présenter sous la forme suivante. Pour des raisons de lisibilité, les colonnes table_catalog
et table_schema
sont exclues des résultats :
+------------------------+-------------+------------------+-------------+-----------+-----------+-------------------+------------------------+-----------------------------+ | table_name | column_name | ordinal_position | is_nullable | data_type | is_hidden | is_system_defined | is_partitioning_column | clustering_ordinal_position | +------------------------+-------------+------------------+-------------+-----------+-----------+-------------------+------------------------+-----------------------------+ | population_by_zip_2010 | zipcode | 1 | NO | STRING | NO | NO | NO | NULL | | population_by_zip_2010 | geo_id | 2 | YES | STRING | NO | NO | NO | NULL | | population_by_zip_2010 | minimum_age | 3 | YES | INT64 | NO | NO | NO | NULL | | population_by_zip_2010 | maximum_age | 4 | YES | INT64 | NO | NO | NO | NULL | | population_by_zip_2010 | gender | 5 | YES | STRING | NO | NO | NO | NULL | | population_by_zip_2010 | population | 6 | YES | INT64 | NO | NO | NO | NULL | +------------------------+-------------+------------------+-------------+-----------+-----------+-------------------+------------------------+-----------------------------+
Vue COLUMN_FIELD_PATHS
Lorsque vous interrogez la vue INFORMATION_SCHEMA.COLUMN_FIELD_PATHS
, les résultats de la requête contiennent une ligne pour chaque colonne imbriquée dans une colonne RECORD
(ou STRUCT
).
Les requêtes exécutées sur la vue INFORMATION_SCHEMA.COLUMN_FIELD_PATHS
doivent avoir un qualificatif d'ensemble de données. L'utilisateur qui soumet la requête doit avoir accès à l'ensemble de données contenant les tables.
La vue INFORMATION_SCHEMA.COLUMN_FIELD_PATHS
présente le schéma suivant :
Nom de la colonne | Type de données | Valeur |
---|---|---|
TABLE_CATALOG |
> STRING |
Nom du projet qui contient l'ensemble de données |
TABLE_SCHEMA |
STRING |
Nom de l'ensemble de données contenant la table (également appelé datasetId ) |
TABLE_NAME |
STRING |
Nom de la table ou de la vue (également appelé tableId ) |
COLUMN_NAME |
STRING |
Nom de la colonne |
FIELD_PATH |
STRING |
Chemin d'accès à une colonne imbriquée dans une colonne RECORD (ou STRUCT) |
DATA_TYPE |
STRING |
Type de données SQL standard de la colonne |
DESCRIPTION |
STRING |
Description de la colonne |
Pour en savoir plus sur les propriétés des ensembles de données, consultez la page concernant la ressource d'ensemble de données dans la documentation de l'API REST. Pour en savoir plus sur les propriétés des tables et des vues, consultez la page relative à la ressource de table dans la documentation de l'API REST.
Exemples
L'exemple suivant récupère les métadonnées de la vue INFORMATION_SCHEMA.COLUMN_FIELD_PATHS
pour la table commits
de l'ensemble de données github_repos
.
Ce dernier fait partie du programme d'ensembles de données publics de BigQuery.
Comme la table que vous interrogez se trouve dans un autre projet (le projet bigquery-public-data
), ajoutez l'ID de ce dernier à l'ensemble de données, en respectant le format suivant : `project_id`.dataset.INFORMATION_SCHEMA.view
. Par exemple, `bigquery-public-data`.github_repos.INFORMATION_SCHEMA.COLUMN_FIELD_PATHS
.
La table commits
contient les colonnes imbriquées ainsi que les colonnes imbriquées et répétées suivantes :
author
: colonneRECORD
imbriquéecommitter
: colonneRECORD
imbriquéetrailer
: colonneRECORD
imbriquée et répétéedifference
: colonneRECORD
imbriquée et répétée
La requête récupérera des métadonnées sur les colonnes author
et difference
.
Les requêtes exécutées sur la vue INFORMATION_SCHEMA.COLUMN_FIELD_PATHS
doivent avoir un qualificatif d'ensemble de données. L'utilisateur qui soumet la requête doit avoir accès à l'ensemble de données contenant les tables.
Pour exécuter la requête :
Console
Ouvrez l'interface utilisateur Web de BigQuery dans la console GCP.
Saisissez la requête SQL standard suivante dans la zone Éditeur de requête.
INFORMATION_SCHEMA
requiert la syntaxe SQL standard. Le langage SQL standard est la syntaxe par défaut dans la console GCP.SELECT * FROM `bigquery-public-data`.github_repos.INFORMATION_SCHEMA.COLUMN_FIELD_PATHS WHERE table_name="commits" AND column_name="author" OR column_name="difference"
Cliquez sur Exécuter.
CLI
Exécutez la commande query
, puis spécifiez la syntaxe SQL standard à l'aide de l'indicateur --nouse_legacy_sql
ou --use_legacy_sql=false
. La syntaxe SQL standard est requise pour les requêtes INFORMATION_SCHEMA
.
Pour exécuter la requête, saisissez la commande suivante :
bq query --nouse_legacy_sql \ 'SELECT * FROM `bigquery-public-data`.github_repos.INFORMATION_SCHEMA.COLUMN_FIELD_PATHS WHERE table_name="commits" AND column_name="author" OR column_name="difference"'
Les résultats doivent se présenter sous la forme suivante. Pour des raisons de lisibilité, les colonnes table_catalog
et table_schema
sont exclues des résultats.
+------------+-------------+---------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------+-------------+ | table_name | column_name | field_path | data_type | description | +------------+-------------+---------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------+-------------+ | commits | author | author | STRUCT<name STRING, email STRING, time_sec INT64, tz_offset INT64, date TIMESTAMP> | NULL | | commits | author | author.name | STRING | NULL | | commits | author | author.email | STRING | NULL | | commits | author | author.time_sec | INT64 | NULL | | commits | author | author.tz_offset | INT64 | NULL | | commits | author | author.date | TIMESTAMP | NULL | | commits | difference | difference | ARRAY<STRUCT<old_mode INT64, new_mode INT64, old_path STRING, new_path STRING, old_sha1 STRING, new_sha1 STRING, old_repo STRING, new_repo STRING>> | NULL | | commits | difference | difference.old_mode | INT64 | NULL | | commits | difference | difference.new_mode | INT64 | NULL | | commits | difference | difference.old_path | STRING | NULL | | commits | difference | difference.new_path | STRING | NULL | | commits | difference | difference.old_sha1 | STRING | NULL | | commits | difference | difference.new_sha1 | STRING | NULL | | commits | difference | difference.old_repo | STRING | NULL | | commits | difference | difference.new_repo | STRING | NULL | +------------+-------------+---------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------+-------------+
Exemple avancé
L'exemple avancé suivant interroge les vues INFORMATION_SCHEMA.TABLES
, TABLE_OPTIONS
et COLUMNS
pour récupérer des métadonnées sur les tables de l'ensemble de données mydataset
de votre projet par défaut (myproject
). mydataset
contient deux tables :
mytable1
: utilise le même schéma que la tablecommits
de l'ensemble de données publicgithub_repos
mytable2
: utilise le même schéma que la tablepopulation_by_zip_2010
de l'ensemble de données publiccensus_bureau_usa
Les fonctions définies par l'utilisateur se servent de ces résultats pour assembler les instructions LDD nécessaires à la recréation des tables. Vous pouvez ensuite utiliser ces instructions dans les résultats de la requête pour recréer les tables dans mydataset
.
Pour exécuter la requête sur un projet autre que celui par défaut, ajoutez l'ID du projet à l'ensemble de données, en respectant le format suivant : `project_id`.dataset.INFORMATION_SCHEMA.view
. Par exemple, `myproject`.mydataset.INFORMATION_SCHEMA.TABLES
.
Pour exécuter la requête :
Console
Ouvrez l'interface utilisateur Web de BigQuery dans la console GCP.
Saisissez la requête SQL standard suivante dans la zone Éditeur de requête.
INFORMATION_SCHEMA
requiert la syntaxe SQL standard. Le langage SQL standard est la syntaxe par défaut dans la console GCP.CREATE TEMP FUNCTION MakePartitionByExpression( column_name STRING, data_type STRING ) AS ( IF( column_name = '_PARTITIONTIME', 'DATE(_PARTITIONTIME)', IF( data_type = 'TIMESTAMP', CONCAT('DATE(', column_name, ')'), column_name ) ) ); CREATE TEMP FUNCTION MakePartitionByClause( columns ARRAY<STRUCT<column_name STRING, data_type STRING, is_nullable STRING, is_partitioning_column STRING, clustering_ordinal_position INT64>> ) AS ( IFNULL( CONCAT( 'PARTITION BY ', (SELECT MakePartitionByExpression(column_name, data_type) FROM UNNEST(columns) WHERE is_partitioning_column = 'YES'), '\n'), '' ) ); CREATE TEMP FUNCTION MakeClusterByClause( columns ARRAY<STRUCT<column_name STRING, data_type STRING, is_nullable STRING, is_partitioning_column STRING, clustering_ordinal_position INT64>> ) AS ( IFNULL( CONCAT( 'CLUSTER BY ', (SELECT STRING_AGG(column_name, ', ' ORDER BY clustering_ordinal_position) FROM UNNEST(columns) WHERE clustering_ordinal_position IS NOT NULL), '\n' ), '' ) ); CREATE TEMP FUNCTION MakeNullable(data_type STRING, is_nullable STRING) AS ( IF(not STARTS_WITH(data_type, 'ARRAY<') and is_nullable = 'NO', ' NOT NULL', '') ); CREATE TEMP FUNCTION MakeColumnList( columns ARRAY<STRUCT<column_name STRING, data_type STRING, is_nullable STRING, is_partitioning_column STRING, clustering_ordinal_position INT64>> ) AS ( IFNULL( CONCAT( '(\n', (SELECT STRING_AGG(CONCAT(' ', column_name, ' ', data_type, MakeNullable(data_type, is_nullable)), ',\n') FROM UNNEST(columns)), '\n)\n' ), '' ) ); CREATE TEMP FUNCTION MakeOptionList( options ARRAY<STRUCT<option_name STRING, option_value STRING>> ) AS ( IFNULL( CONCAT( 'OPTIONS (\n', (SELECT STRING_AGG(CONCAT(' ', option_name, '=', option_value), ',\n') FROM UNNEST(options)), '\n)\n'), '' ) ); WITH Components AS ( SELECT CONCAT('`', table_catalog, '.', table_schema, '.', table_name, '`') AS table_name, ARRAY_AGG( STRUCT(column_name, data_type, is_nullable, is_partitioning_column, clustering_ordinal_position) ORDER BY ordinal_position ) AS columns, (SELECT ARRAY_AGG(STRUCT(option_name, option_value)) FROM mydataset.INFORMATION_SCHEMA.TABLE_OPTIONS AS t2 WHERE t.table_name = t2.table_name) AS options FROM mydataset.INFORMATION_SCHEMA.TABLES AS t LEFT JOIN mydataset.INFORMATION_SCHEMA.COLUMNS USING (table_catalog, table_schema, table_name) WHERE table_type = 'BASE TABLE' GROUP BY table_catalog, table_schema, t.table_name ) SELECT CONCAT( 'CREATE OR REPLACE TABLE ', table_name, '\n', MakeColumnList(columns), MakePartitionByClause(columns), MakeClusterByClause(columns), MakeOptionList(options)) FROM Components
CLI
Exécutez la commande query
, puis spécifiez la syntaxe SQL standard à l'aide de l'indicateur --nouse_legacy_sql
ou --use_legacy_sql=false
. La syntaxe SQL standard est requise pour les requêtes INFORMATION_SCHEMA
.
Pour exécuter la requête, saisissez :
QUERY_TEXT=$(cat <<ENDQUERY
CREATE TEMP FUNCTION MakePartitionByExpression(
column_name STRING, data_type STRING
) AS (
IF(
column_name = '_PARTITIONTIME',
'DATE(_PARTITIONTIME)',
IF(
data_type = 'TIMESTAMP',
CONCAT('DATE(', column_name, ')'),
column_name
)
)
);
CREATE TEMP FUNCTION MakePartitionByClause(
columns ARRAY<STRUCT<column_name STRING, data_type STRING, is_nullable STRING, is_partitioning_column STRING, clustering_ordinal_position INT64>>
) AS (
IFNULL(
CONCAT(
'PARTITION BY ',
(SELECT MakePartitionByExpression(column_name, data_type)
FROM UNNEST(columns) WHERE is_partitioning_column = 'YES'),
'\n'),
''
)
);
CREATE TEMP FUNCTION MakeClusterByClause(
columns ARRAY<STRUCT<column_name STRING, data_type STRING, is_nullable STRING, is_partitioning_column STRING, clustering_ordinal_position INT64>>
) AS (
IFNULL(
CONCAT(
'CLUSTER BY ',
(SELECT STRING_AGG(column_name, ', ' ORDER BY clustering_ordinal_position)
FROM UNNEST(columns) WHERE clustering_ordinal_position IS NOT NULL),
'\n'
),
''
)
);
CREATE TEMP FUNCTION MakeNullable(data_type STRING, is_nullable STRING)
AS (
IF(not STARTS_WITH(data_type, 'ARRAY<') and is_nullable = 'NO', ' NOT NULL', '')
);
CREATE TEMP FUNCTION MakeColumnList(
columns ARRAY<STRUCT<column_name STRING, data_type STRING, is_nullable STRING, is_partitioning_column STRING, clustering_ordinal_position INT64>>
) AS (
IFNULL(
CONCAT(
'(\n',
(SELECT STRING_AGG(CONCAT(' ', column_name, ' ', data_type, MakeNullable(data_type, is_nullable)), ',\n')
FROM UNNEST(columns)),
'\n)\n'
),
''
)
);
CREATE TEMP FUNCTION MakeOptionList(
options ARRAY<STRUCT<option_name STRING, option_value STRING>>
) AS (
IFNULL(
CONCAT(
'OPTIONS (\n',
(SELECT STRING_AGG(CONCAT(' ', option_name, '=', option_value), ',\n') FROM UNNEST(options)),
'\n)\n'),
''
)
);
WITH Components AS (
SELECT
CONCAT('\`', table_catalog, '.', table_schema, '.', table_name, '\`') AS table_name,
ARRAY_AGG(
STRUCT(column_name, data_type, is_nullable, is_partitioning_column, clustering_ordinal_position)
ORDER BY ordinal_position
) AS columns,
(SELECT ARRAY_AGG(STRUCT(option_name, option_value))
FROM mydataset.INFORMATION_SCHEMA.TABLE_OPTIONS AS t2
WHERE t.table_name = t2.table_name) AS options
FROM mydataset.INFORMATION_SCHEMA.TABLES AS t
LEFT JOIN mydataset.INFORMATION_SCHEMA.COLUMNS
USING (table_catalog, table_schema, table_name)
WHERE table_type = 'BASE TABLE'
GROUP BY table_catalog, table_schema, t.table_name
)
SELECT
CONCAT(
'CREATE OR REPLACE TABLE ',
table_name,
'\n',
MakeColumnList(columns),
MakePartitionByClause(columns),
MakeClusterByClause(columns),
MakeOptionList(options))
FROM Components
ENDQUERY
)
bq query --nouse_legacy_sql "$QUERY_TEXT"
Le résultat doit se présenter sous la forme suivante :
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | f0_ | +-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | CREATE OR REPLACE TABLE `myproject.mydataset.population_by_zip_2010` | | ( | | zipcode STRING NOT NULL, | | geo_id STRING, | | minimum_age INT64, | | maximum_age INT64, | | gender STRING, | | population INT64 | | ) | | OPTIONS ( | | expiration_timestamp=TIMESTAMP "2019-04-17T02:10:32.055Z" | | ) | | CREATE OR REPLACE TABLE `myproject.mydataset.commits` | | ( | | commit STRING, | | tree STRING, | | parent ARRAY<STRING>, | | author STRUCT<name STRING, email STRING, time_sec INT64, tz_offset INT64, date TIMESTAMP>, | | committer STRUCT<name STRING, email STRING, time_sec INT64, tz_offset INT64, date TIMESTAMP>, | | subject STRING, | | message STRING, | | trailer ARRAY<STRUCT<key STRING, value STRING, email STRING>>, | | difference ARRAY<STRUCT<old_mode INT64, new_mode INT64, old_path STRING, new_path STRING, old_sha1 STRING, new_sha1 STRING, old_repo STRING, new_repo STRING>>, | | difference_truncated BOOL, | | repo_name ARRAY<STRING>, | | encoding STRING | | ) | | OPTIONS ( | | expiration_timestamp=TIMESTAMP "2019-04-17T03:12:03.248Z" | | ) | +-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
Étapes suivantes
- Pour obtenir une présentation des vues
INFORMATION_SCHEMA
, consultez la page Présentation de BigQueryINFORMATION_SCHEMA
. - Découvrez comment obtenir des vues
INFORMATION_SCHEMA
à l'aide des métadonnées d'ensemble de données. - Découvrez comment obtenir des vues
INFORMATION_SCHEMA
à l'aide des métadonnées de vue.