[PYTHON] J'ai regardé les méta-informations de BigQuery et essayé de les utiliser

introduction

BigQuery est très pratique, n'est-ce pas? Pour le moment, vous pouvez insérer des tables, les agréger à grande vitesse, et même l'apprentissage automatique à l'aide de BigQuery ML, etc., ne peut être effectué qu'avec BigQuery.

Il est pratique à utiliser et lorsque je le remarque, divers ensembles de données et tables ont été créés. Il semble que les méta-informations puissent être vues en utilisant INFORMATION_SCHEMA.

Cette fois, nous allons organiser comment utiliser INFORMATION_SCHEMA et d'autres méta-informations.

Comment lire les méta-informations

Obtenir une liste d'ensembles de données pour un projet particulier

La première consiste à spécifier un projet et à obtenir une liste des ensembles de données à l'intérieur.

SELECT * FROM `myproject.INFORMATION_SCHEMA.SCHEMATA`

Les éléments suivants sont affichés.

--catalog_name: nom du projet --schema_name: nom de l'ensemble de données --schema_owner: nom du propriétaire? (Tous étaient nuls) --creation_time: date et heure de création --last_modified_time: date et heure modifiées --location: emplacement de stockage

C'était dommage que le nom du propriétaire soit Null. Ce serait très facile à gérer si vous saviez qui a créé l'ensemble de données. .. .. Y a-t-il un moyen de le mettre?

Obtention d'une liste de tables pour un projet / ensemble de données spécifique 1

Ensuite, projet: comment spécifier le jeu de données et obtenir la liste des tables à l'intérieur.

SELECT * FROM `myproject.mydataset.INFORMATION_SCHEMA.TABLES`

Les éléments suivants sont affichés.

--table_catalog: nom du projet --table_schema: nom de l'ensemble de données --table_name: nom de la table --table_type: table ou VIEW ou table xref --is_insertable_into: Pouvez-vous INSÉRER? --is_typed: Inconnu (y a-t-il autre chose que Non?) --creation_time: date et heure de création

Je ne comprends pas la signification de is_insertable_into. Il ne semble y avoir aucun élément autre que Oui pour le tableau et Non pour VIEW. Dans ce cas, table_type suffit. .. ..

Obtention d'une liste de tables pour un projet / ensemble de données spécifique 2

De même, Project: Comment obtenir une liste de tables à l'intérieur en spécifiant un ensemble de données. Les éléments qui sont produits sont légèrement différents

SELECT * FROM `myproject.mydataset.__TABLES__`

--project_id: nom du projet --dataset_id: nom de l'ensemble de données --table_id: nom de la table --creation_time: date et heure de création --last_modified_time: date et heure modifiées --row_count: nombre de lignes --size_bytes: taille des données --Type: 1 pour la table, 2 pour la vue

Il est désagréable que le nom soit légèrement différent en fonction des méta-informations que vous voyez. Il peut s'agir de catalog_name, table_catalog ou project_id pour faire référence au même nom de projet. Est-il utilisé correctement dans BigQuery?

Obtention d'une liste de tables pour un projet / ensemble de données spécifique 3

Vous pouvez également consulter des informations supplémentaires sur la table dans INFORMATION_SCHEMA.

SELECT * FROM `myproject.mydataset.INFORMATION_SCHEMA.TABLE_OPTIONS`

Les éléments suivants sont affichés.

--table_catalog: nom du projet --table_schema: nom de l'ensemble de données --table_name: nom de la table --table_type: table ou VIEW ou table xref

• option_name
• option_type
• option_value

Il est difficile de comprendre ce que dit l'option pendant un moment, mais option_name contient "expiration_timestamp", option_type contient "TIMESTAMP" et option_value contient la date et l'heure, et ces trois éléments sont un ensemble.

Il y avait aussi des descriptions et des étiquettes.

Obtenir la liste des tableaux + la liste des colonnes de l'ensemble de données de projet

Plus vous avez de tables, plus vous avez de résultats.

SELECT * FROM `myproject.mydataset.INFORMATION_SCHEMA.COLUMNS`

--table_catalog: nom du projet --table_schema: nom de l'ensemble de données --table_name: nom de la table --column_name: nom de la colonne --ordinal_position: numéro de colonne --is_nullable: Null est-il correct? --data_type: type de données

• is_generated
• generation_expression
• is_stored
• is_hidden
• is_updatable
• is_system_defined --is_partitioning_column: est-il utilisé pour le partitionnement de table? --clustering_ordinal_position: est-il utilisé pour le clustering de tables?

Celles qui n'ont pas de commentaire sont les colonnes qui ne contiennent que JAMAIS ou Null. Je n'ai pas compris le sens des colonnes.

Essayez d'utiliser une combinaison de méta-informations

Je souhaite calculer le montant de facturation pour chaque ensemble de données d'un projet en combinant des méta-informations.

import pandas as pd

query="SELECT schema_name FROM `myproject.INFORMATION_SCHEMA.SCHEMATA`"
df = pd.read_gbq(query, project_id='myproject', dialect="standard")

df_output = pd.DataFrame()

query="""
SELECT 
 "{dataset}" AS dataset, 
 SUM(size_bytes) / 1000000000 AS DataSize, 
 0.020 * SUM(size_bytes) / 1000000000 AS Cost 
FROM `myproject.{dataset}.__TABLES__`
GROUP BY dataset"""

for i, dataset in df.iterrows():
  d = pd.read_gbq(query.format(dataset=dataset[0]), project_id='myproject',dialect="standard")
  
  df_output=df_output.append(d)

Les frais de stockage BQ sont de 0,02 USD par Go pour "Frais BigQuery". Pour le stockage à long terme, cela coûte 0,01 USD, donc la facture réelle peut être un peu moins chère que le résultat.

en conclusion

J'ai résumé les méta-informations de BigQuery. Puisqu'il y a diverses méta-informations, il semble y en avoir d'autres, mais pour le moment, je vais publier celles que j'utilise souvent. Cependant, il y a des objets mystérieux, je dois donc étudier un peu plus.