[PYTHON] Ich habe mir die Metainformationen von BigQuery angesehen und versucht, sie zu verwenden

Einführung

BigQuery ist sehr praktisch, nicht wahr? Derzeit können Sie Tabellen einfügen, die Put-Tabellen mit hoher Geschwindigkeit aggregieren und sogar maschinelles Lernen mit BigQuery ML usw. kann nur mit BigQuery durchgeführt werden.

Es ist bequem zu verwenden, und wenn ich es bemerke, wurden verschiedene Datensätze und Tabellen erstellt. Es scheint, dass die Metainformationen mithilfe von INFORMATION_SCHEMA angezeigt werden können.

Dieses Mal werden wir organisieren, wie INFORMATION_SCHEMA und andere Metainformationen verwendet werden.

Wie man Metainformationen liest

Rufen Sie eine Liste der Datensätze für ein bestimmtes Projekt ab

Die erste besteht darin, das Projekt anzugeben und die Liste der darin enthaltenen Datensätze abzurufen.

SELECT * FROM `myproject.INFORMATION_SCHEMA.SCHEMATA`

Die folgenden Elemente werden ausgegeben.

--catalog_name: Projektname --schema_name: Datensatzname --schema_owner: Name des Besitzers? (Alle waren Null) --creation_time: Erstellungsdatum und -zeit --last_modified_time: Geändertes Datum und Uhrzeit --location: Speicherort

Schade, dass der Name des Besitzers Null war. Dies wäre sehr einfach zu verwalten, wenn Sie wüssten, wer den Datensatz erstellt hat. .. .. Gibt es eine Möglichkeit, es einzutragen?

Abrufen einer Tabellenliste für ein bestimmtes Projekt / einen bestimmten Datensatz 1

Weiter Projekt: So geben Sie ein Dataset an und erhalten eine Liste der darin enthaltenen Tabellen.

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

Die folgenden Elemente werden ausgegeben.

--table_catalog: Projektname --table_schema: Datensatzname --table_name: Tabellenname --table_type: Tabelle oder VIEW oder XRef-Tabelle --is_insertable_into: Können Sie INSERT? --is_typed: Unbekannt (gibt es etwas anderes als Nein?) --creation_time: Erstellungsdatum und -zeit

Ich verstehe die Bedeutung von is_insertable_into nicht. Es scheint keine anderen Elemente als Ja für die Tabelle und Nein für VIEW zu geben. In diesem Fall reicht table_type aus. .. ..

Abrufen einer Liste von Tabellen für ein bestimmtes Projekt / einen bestimmten Datensatz 2

Ebenso Projekt: So erhalten Sie eine Liste der darin enthaltenen Tabellen, indem Sie ein Dataset angeben. Die ausgegebenen Elemente unterscheiden sich geringfügig

SELECT * FROM `myproject.mydataset.__TABLES__`

--project_id: Projektname --dataset_id: Datasetname --table_id: Tabellenname --creation_time: Erstellungsdatum und -zeit --last_modified_time: Geändertes Datum und Uhrzeit --row_count: Anzahl der Zeilen --size_bytes: Datengröße

• Typ: 1 für Tabelle, 2 für VIEW

Es ist unangenehm, dass der Name je nach den angezeigten Metainformationen leicht unterschiedlich ist. Es kann sich um Katalogname, Tabellenkatalog oder Projekt-ID handeln, um auf denselben Projektnamen zu verweisen. Wird es in BigQuery richtig verwendet?

Abrufen einer Liste von Tabellen für ein bestimmtes Projekt / einen bestimmten Datensatz 3

Weitere Informationen zur Tabelle finden Sie auch in INFORMATION_SCHEMA.

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

Die folgenden Elemente werden ausgegeben.

--table_catalog: Projektname --table_schema: Datensatzname --table_name: Tabellenname --table_type: Tabelle oder VIEW oder XRef-Tabelle

• option_name
• option_type
• option_value

Es ist schwer zu verstehen, was die Option für einen Moment sagt, aber option_name ist "expiration_timestamp", option_type ist "TIMESTAMP" und option_value ist das Datum und die Uhrzeit, und diese drei sind eine Menge.

Es gab auch Beschreibungen und Etiketten.

Tabellenliste + Spaltenliste eines bestimmten Projektdatensatzes abrufen

Je mehr Tabellen Sie haben, desto mehr Ausgabe haben Sie.

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

--table_catalog: Projektname --table_schema: Datensatzname --table_name: Tabellenname --column_name: Spaltenname --ordinal_position: Spaltennummer --is_nullable: Ist Null in Ordnung? --data_type: Datentyp

• is_generated
• generation_expression
• is_stored
• is_hidden
• is_updatable
• is_system_defined --is_partitioning_column: Wird es für die Tabellenpartitionierung verwendet? --clustering_ordinal_position: Wird es für das Clustering von Tabellen verwendet?

Diejenigen, die keinen Kommentar haben, sind die Spalten, die nur NIE oder Null enthalten. Ich habe die Bedeutung der Spalten nicht verstanden.

Versuchen Sie es mit einer Kombination von Metainformationen

Ich möchte den Rechnungsbetrag für jeden Datensatz in einem Projekt berechnen, indem ich Metainformationen kombiniere.

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)

Die BQ-Speicherkosten betragen 0,02 USD pro GB aus "BigQuery-Gebühren". Für die Langzeitlagerung kostet es 0,01 USD, sodass die tatsächliche Rechnung möglicherweise etwas billiger ist als das Ergebnis.

abschließend

Ich habe die Metainformationen von BigQuery zusammengefasst. Da es verschiedene Metainformationen gibt, scheint es andere zu geben, aber vorerst werde ich diejenigen veröffentlichen, die ich oft benutze. Es gibt jedoch einige mysteriöse Gegenstände, daher muss ich etwas mehr lernen.