Obtenir des métadonnées de table à l'aide de INFORMATION_SCHEMA

Bêta

Cette fonctionnalité est disponible dans sa version préliminaire, et peut changer ou bénéficier d'une assistance limitée. Pour en savoir plus, consultez les étapes de lancement des produits.

INFORMATION_SCHEMA contient les vues suivantes pour les métadonnées de table :

• TABLES et TABLE_OPTIONS pour les métadonnées concernant les tables
• COLUMNS et COLUMN_FIELD_PATHS pour les métadonnées concernant les colonnes et les champs

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 VIEWS.

Autorisations requises

Pour TABLES et TABLE_OPTIONS, vous devez disposer des autorisations suivantes :

• bigquery.tables.get
• bigquery.tables.list
• bigquery.routines.get
• bigquery.routines.list

Pour COLUMNS et COLUMN_FIELD_PATHS, vous devez disposer des autorisations suivantes :

• bigquery.tables.get
• bigquery.tables.list

Syntaxe

Les requêtes exécutées sur ces vues doivent être associées à un qualificatif d'ensemble de données.

-- Returns metadata for tables in a single dataset.
SELECT * FROM myDataset.INFORMATION_SCHEMA.TABLES;

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.

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 : BASE TABLE : une table BigQuery VIEW : une vue BigQuery EXTERNAL : une table qui référence une source de données externe
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

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 mydataset dans votre projet par défaut (myproject).

mydataset contient les tables suivantes :

• mytable1 : une table BigQuery standard
• myview1 : une vue BigQuery

Pour exécuter la requête sur un projet autre que celui par défaut, ajoutez l'ID du projet au nom de 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

1. Ouvrez l'UI Web de BigQuery dans Cloud Console.

   Accéder à Cloud Console

2. 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 Cloud Console.

   SELECT
    * EXCEPT(is_typed)
   FROM
    mydataset.INFORMATION_SCHEMA.TABLES
   

   Remarque : Les noms des vues INFORMATION_SCHEMA sont sensibles à la casse.

3. Cliquez sur Exécuter.

Ligne de commande

Exécutez la commande query, puis spécifiez la syntaxe SQL standard à l'aide de l'option --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 :

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 à partir 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).

Pour exécuter la requête sur un projet autre que celui par défaut, ajoutez l'ID du projet au nom de 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

1. Ouvrez l'UI Web de BigQuery dans Cloud Console.

   Accéder à Cloud Console

2. 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 Cloud Console.

   SELECT
    * EXCEPT(is_typed)
   FROM
    mydataset.INFORMATION_SCHEMA.TABLES
   WHERE
    table_type="BASE TABLE"
   

   Remarque : Les noms des vues INFORMATION_SCHEMA sont sensibles à la casse.

3. Cliquez sur Exécuter.

Ligne de commande

Exécutez la commande query, puis spécifiez la syntaxe SQL standard à l'aide de l'option --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 :

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.

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
require_partition_filter	BOOL	Filtre de partition nécessaire ou non pour les requêtes sur la table

Exemples

Exemple 1 :

L'exemple suivant récupère les délais d'expiration par défaut de toutes les tables de l'ensemble de données mydataset de votre projet par défaut (myproject) en interrogeant la vue INFORMATION_SCHEMA.TABLE_OPTIONS.

Pour exécuter la requête sur un projet autre que celui par défaut, ajoutez l'ID du projet au nom de 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

1. Ouvrez l'UI Web de BigQuery dans Cloud Console.

   Accéder à Cloud Console

2. 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 Cloud Console.

   SELECT
    *
   FROM
    mydataset.INFORMATION_SCHEMA.TABLE_OPTIONS
   WHERE
    option_name="expiration_timestamp"
   

   Remarque : Les noms des vues INFORMATION_SCHEMA sont sensibles à la casse.

3. Cliquez sur Exécuter.

Ligne de commande

Exécutez la commande query, puis spécifiez la syntaxe SQL standard à l'aide de l'option --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 :

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" |
  +----------------+---------------+------------+----------------------+-------------+--------------------------------------+
  

Remarque : Les tables sans délai d'expiration sont exclues des résultats de la requête.

Exemple 2 :

L'exemple suivant récupère les métadonnées de toutes les tables de 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 votre projet par défaut, ajoutez l'ID du projet à l'ensemble de données au format suivant : `project_id`.dataset.INFORMATION_SCHEMA.view. Par exemple, `myproject`.mydataset.INFORMATION_SCHEMA.TABLE_OPTIONS.

Pour exécuter la requête :

Console

1. Ouvrez l'UI Web de BigQuery dans Cloud Console.

   Accéder à Cloud Console

2. 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 Cloud Console.

   SELECT
    *
   FROM
    mydataset.INFORMATION_SCHEMA.TABLE_OPTIONS
   WHERE
    option_name="description" AND option_value LIKE "%test%"
   

   Remarque : Les noms des vues INFORMATION_SCHEMA sont sensibles à la casse.

3. Cliquez sur Exécuter.

Ligne de commande

Exécutez la commande query, puis spécifiez la syntaxe SQL standard à l'aide de l'option --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 :

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 lancez une requête sur la vue INFORMATION_SCHEMA.COLUMNS, les résultats de la requête contiennent une ligne pour chaque colonne (champ) d'une table.

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 qu'il s'agit ou non d'une colonne de partitionnement
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.

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. Celui-ci fait partie du programme d'ensembles de données publics de BigQuery.

Comme la table que vous interrogez se trouve dans un autre projet (bigquery-public-data), vous ajoutez l'ID de projet à l'ensemble de données, en respectant le format `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

Pour exécuter la requête :

Console

1. Ouvrez l'UI Web de BigQuery dans Cloud Console.

   Accéder à Cloud Console

2. 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 Cloud Console.

   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"
   

   Remarque : Les noms des vues INFORMATION_SCHEMA sont sensibles à la casse.

3. Cliquez sur Exécuter.

Ligne de commande

Exécutez la commande query, puis spécifiez la syntaxe SQL standard à l'aide de l'option --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 :

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é, table_catalog et table_schema sont exclues des résultats comme suit :

+------------------------+-------------+------------------+-------------+-----------+-----------+-------------------+------------------------+-----------------------------+
|       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

Les résultats de la requête contiennent une ligne pour chaque colonne imbriquée dans une colonne RECORD (ou STRUCT).

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).

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

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. Celui-ci fait partie du programme d'ensembles de données publics de BigQuery.

Comme la table que vous interrogez se trouve dans un autre projet (bigquery-public-data), vous ajoutez l'ID de projet à l'ensemble de données, en respectant le format `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 : colonne imbriquée RECORD
• committer : colonne imbriquée RECORD
• trailer : colonne imbriquée et répétée RECORD
• difference : colonne imbriquée et répétée RECORD

Votre requête récupérera les métadonnées relatives aux colonnes author et difference.

Pour exécuter la requête :

Console

1. Ouvrez l'UI Web de BigQuery dans Cloud Console.

   Accéder à Cloud Console

2. 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 Cloud Console.

   SELECT
    *
   FROM
    `bigquery-public-data`.github_repos.INFORMATION_SCHEMA.COLUMN_FIELD_PATHS
   WHERE
    table_name="commits"
    AND column_name="author"
    OR column_name="difference"
   

   Remarque : Les noms des vues INFORMATION_SCHEMA sont sensibles à la casse.

3. Cliquez sur Exécuter.

Ligne de commande

Exécutez la commande query, puis spécifiez la syntaxe SQL standard à l'aide de l'option --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 :

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é, table_catalog et table_schema sont exclus 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 extraire les métadonnées relatives aux tables dans mydataset dans votre projet par défaut (myproject). mydataset contient deux tables :

• mytable1 : utilise le même schéma que la table commits de l'ensemble de données public github_repos.
• mytable2 : utilise le même schéma que la table population_by_zip_2010 de l'ensemble de données public census_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 LDD 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 votre projet 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

1. Ouvrez l'UI Web de BigQuery dans Cloud Console.

   Accéder à Cloud Console

2. 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 Cloud Console.

   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'option --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"                                                                                                                   |
| )                                                                                                                                                                             |
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+