Creazione di partizioni e cluster in BigQuery

1. Introduzione

BigQuery è un data warehouse aziendale completamente gestito, economico e con scalabilità nell'ordine dei petabyte per l'analisi. BigQuery è serverless, Non è necessario configurare e gestire i cluster.

Un set di dati BigQuery risiede in un progetto Google Cloud e contiene una o più tabelle. Puoi eseguire query su questi set di dati con SQL.

In questo codelab, utilizzerai la UI web di BigQuery nella console di Google Cloud per comprendere il partizionamento e il clustering in BigQuery. Il partizionamento e il clustering delle tabelle di BigQuery consentono di strutturare i dati in modo che corrispondano ai pattern di accesso ai dati comuni. Il partizionamento e il clustering sono fondamentali per massimizzare completamente le prestazioni e i costi di BigQuery quando si eseguono query su un intervallo di dati specifico. Ne deriva l'analisi di meno dati per query e l'eliminazione viene determinata prima dell'ora di inizio della query.

Nota: oltre all'interfaccia utente web di BigQuery, puoi utilizzare lo strumento a riga di comando bq per eseguire operazioni sui set di dati BigQuery. Per ulteriori informazioni sullo strumento a riga di comando bq, vedi strumento a riga di comando bq.

Per saperne di più su BigQuery, consulta la documentazione di BigQuery.

Obiettivi didattici

• Creare ed eseguire query su tabelle partizionate e in cluster
• Confronta le prestazioni delle query con le tabelle partizionate e in cluster

Che cosa ti serve

Per completare il lab, avrai bisogno di:

• L'ultima versione di Google Chrome
• Un account di fatturazione Google Cloud.

2. Preparazione

Per lavorare con BigQuery, devi creare un progetto della piattaforma Google Cloud o selezionarne uno esistente.

Creare un progetto

Per creare un nuovo progetto:

1. Se non hai ancora un Account Google (Gmail o Google Apps), creane uno.
2. Accedi alla console della piattaforma Google Cloud ( console.cloud.google.com) e crea un nuovo progetto.
3. Se non hai progetti, fai clic sul pulsante Crea progetto:

In caso contrario, crea un nuovo progetto dal menu di selezione del progetto:

4. Inserisci un nome per il progetto e seleziona Crea. Tieni presente che l'ID progetto è un nome univoco tra tutti i progetti Google Cloud.

3. Utilizzo di set di dati pubblici

BigQuery ti consente di lavorare con set di dati pubblici tra cui BBC News, repository GitHub, Stack Overflow e i set di dati NOAA (National Oceanic and Atmospheric Administration) degli Stati Uniti. Non è necessario caricare questi set di dati in BigQuery. Devi solo aprire i set di dati per sfogliarli ed eseguirvi query in BigQuery. In questo codelab lavorerai con il set di dati pubblico Stack Overflow.

Sfoglia il set di dati di Stack Overflow

Il set di dati di Stack Overflow contiene informazioni su post, tag, badge, commenti, utenti e altro ancora. Per sfogliare il set di dati di Stack Overflow nella UI web di BigQuery, segui questi passaggi:

1. Apri il set di dati di Stack Overflow. La UI web di BigQuery si apre nella console di Google Cloud e mostra le informazioni sul set di dati Stackoverflow.
2. Nel pannello di navigazione , seleziona bigquery-public-data. Il menu si espande per elencare i set di dati pubblici. Ogni set di dati comprende una o più tabelle.
3. Scorri verso il basso e seleziona stackoverflow. Il menu si espande per elencare le tabelle nel set di dati di Stack Overflow.
4. Seleziona badge per visualizzare lo schema della tabella dei badge. Prendi nota dei nomi dei campi della tabella.
5. Sopra i nomi dei campi, fai clic su Anteprima per visualizzare dati di esempio per la tabella dei badge.

Per saperne di più su tutti i set di dati pubblici disponibili in BigQuery, vedi Set di dati pubblici di Google BigQuery.

Eseguire query sul set di dati Stackoverflow

Sfogliare un set di dati è un buon modo per comprendere i dati con cui stai lavorando, ma l'esecuzione di query sui set di dati è il punto di forza di BigQuery. Questa sezione insegna come eseguire le query di BigQuery. A questo punto non è necessario conoscere SQL. Puoi copiare e incollare le query di seguito.

Per eseguire una query, completa i seguenti passaggi:

1. In alto a destra nella console di Google Cloud, seleziona Crea nuova query.
2. Nell'area di testo Editor query, copia e incolla la seguente query SQL. BigQuery convalida la query e nella UI web viene visualizzato un segno di spunta verde sotto l'area di testo per indicare che la sintassi è valida.

SELECT
  EXTRACT(YEAR FROM creation_date) AS creation_year,
  COUNT(*) AS total_posts
FROM `bigquery-public-data.stackoverflow.posts_questions`
GROUP BY creation_year
ORDER BY total_posts DESC
LIMIT 10

3. Seleziona Esegui. La query restituisce il numero di post o domande di Stack Overflow pubblicati ogni anno.

Nota:questa query utilizza il nome completo della tabella posts_questions, inclusi il nome del progetto, il nome del set di dati e il nome della tabella: bigquery-public-data.stackoverflow.posts_questions.

4. Creazione di una nuova tabella

Nella sezione precedente, hai eseguito una query su set di dati pubblici messi a tua disposizione da BigQuery. In questa sezione creerai una nuova tabella in BigQuery da una tabella esistente. Creerai una nuova tabella con dati campionati dalla tabella del set di dati pubblico di Stack Overflow posts_questions, quindi eseguirai una query sulla tabella.

Crea un nuovo set di dati

Per creare e caricare dati di una tabella in BigQuery, per prima cosa crea un set di dati BigQuery che contenga i dati completando i seguenti passaggi:

1. Nel pannello di navigazione della console Google Cloud, seleziona il nome del progetto creato durante la configurazione.
2. Sul lato destro, nel riquadro dei dettagli, seleziona Crea set di dati.

3. Nella finestra di dialogo Crea set di dati, per ID set di dati, digita stackoverflow. Lascia invariate tutte le altre impostazioni predefinite e fai clic su OK.

Crea una nuova tabella con i post di StackOverflow del 2018

Ora che hai creato un set di dati BigQuery, puoi creare una nuova tabella in BigQuery. Per creare una tabella con i dati di una tabella esistente, eseguirai una query sul set di dati dei post di Stack Overflow del 2018 e scriverai i risultati in una nuova tabella, completando i seguenti passaggi:

1. In alto a destra nella console di Google Cloud, seleziona Crea nuova query.

2. Nell'area di testo Editor query, copia e incolla la seguente query SQL per creare una nuova tabella, che è un'istruzione DDL.

CREATE OR REPLACE TABLE `stackoverflow.questions_2018` AS
SELECT id, title, accepted_answer_id, creation_date, answer_count , comment_count , favorite_count, view_count, tags
FROM `bigquery-public-data.stackoverflow.posts_questions`
WHERE creation_date BETWEEN '2018-01-01' AND '2019-01-01';

3. Seleziona Esegui. La query crea una nuova tabella questions_2018 nel set di dati stackoverflow nel tuo progetto con i dati derivanti dall'esecuzione di una query sul set di dati di BigQuery Stack Overflow bigquery-public-data.stackoverflow.posts_questions.

Nota: questo passaggio richiede circa 2 minuti per creare una nuova tabella. Attendi prima di procedere con il passaggio successivo.

Esegui query sulla nuova tabella con i post di Stack Overflow del 2018

Ora che hai creato una tabella BigQuery, eseguiamo una query per restituire post di Stack Overflow con domande e titoli, oltre ad altre statistiche come il numero di risposte, commenti, visualizzazioni e preferiti. Completa i seguenti passaggi:

1. In alto a destra nella console di Google Cloud, seleziona Crea nuova query.
2. Nell'area di testo Editor di query, copia e incolla la seguente query SQL.

SELECT id, title, accepted_answer_id, creation_date, answer_count , comment_count , favorite_count, view_count 
FROM  `stackoverflow.questions_2018` 
WHERE creation_date BETWEEN '2018-01-01' AND '2018-02-01'
AND tags = 'android';

3. Seleziona Esegui. La query restituisce domande di Stack Overflow create nel mese di gennaio 2018 e contrassegnate come android insieme alla domanda e ad alcune altre statistiche.
4. Per impostazione predefinita, BigQuery memorizza nella cache i risultati delle query. Esegui la stessa query e vedrai che BigQuery ha impiegato molto meno tempo per restituire i risultati perché restituisce i risultati dalla cache.
5. Esegui di nuovo la stessa query, ma questa volta con la memorizzazione nella cache BigQuery disabilitata. Disabiliteremo la cache per garantire un confronto delle prestazioni equo rispetto alle tabelle partizionate e in cluster per il resto del lab, che verrà eseguito nelle sezioni successive. Nell'editor query, fai clic su Altro e seleziona Impostazioni query.
6. In Preferenza cache, deseleziona Utilizza risultati memorizzati nella cache.
7. Nei risultati della query dovresti vedere il tempo necessario per il completamento della query e il volume di dati elaborati per ottenere i risultati.

5. Creazione e query di una tabella partizionata

Nella sezione precedente, hai creato una nuova tabella in BigQuery con i dati della tabella posts_questions utilizzando il set di dati pubblico Stack Overflow. Abbiamo eseguito una query su questo set di dati con la memorizzazione nella cache disabilitata e abbiamo osservato le prestazioni della query. In questa sezione creerai una nuova tabella partizionata dalla tabella posts_questions dello stesso set di dati pubblico di Stack Overflow e osserverai le prestazioni della query.

Una tabella partizionata è una tabella speciale divisa in segmenti, denominati partizioni, che semplificano la gestione e l'interrogazione dei dati. In genere puoi suddividere tabelle di grandi dimensioni in più partizioni più piccole utilizzando la data di importazione dati o la colonna TIMESTAMP/DATE o una colonna INTEGER. Verrà creata una tabella partizionata DATE.

Scopri di più sulle tabelle partizionate qui.

Crea una nuova tabella partizionata con i post di StackOverflow del 2018

Per creare una tabella partizionata con i dati di una tabella o query esistente, eseguirai una query sul set di dati dei post di Stackoverflow del 2018 e scriverai i risultati in una nuova tabella, completa i seguenti passaggi:

1. In alto a destra nella console di Google Cloud, seleziona Crea nuova query.

2. Nell'area di testo Editor query, copia e incolla la seguente query SQL per creare una nuova tabella, che è un'istruzione DDL.

CREATE OR REPLACE TABLE `stackoverflow.questions_2018_partitioned` 
PARTITION BY DATE(creation_date) AS
SELECT id, title, accepted_answer_id, creation_date, answer_count , comment_count , favorite_count, view_count, tags
FROM `bigquery-public-data.stackoverflow.posts_questions`
WHERE creation_date BETWEEN '2018-01-01' AND '2019-01-01';

3. Seleziona Esegui. La query crea una nuova tabella questions_2018_partitioned nel set di dati stackoverflow del tuo progetto con i dati derivanti dall'esecuzione di una query sul set di dati di BigQuery Stack Overflow bigquery-public-data.stackoverflow.posts_questions

Nota: questo passaggio richiede circa 2 minuti per creare una nuova tabella. Attendi prima di procedere con il passaggio successivo.

Esegui query sulla tabella partizionata con i post di Stack Overflow del 2018

Ora che hai creato una tabella BigQuery partizionata, esegui la stessa query, questa volta sulla tabella partizionata, per restituire post di Stack Overflow con domande e titoli insieme ad altre statistiche come il numero di risposte, commenti, visualizzazioni e preferiti. Completa i seguenti passaggi:

1. In alto a destra nella console di Google Cloud, seleziona Crea nuova query.
2. Nell'area di testo Editor di query, copia e incolla la seguente query SQL.

SELECT id, title, accepted_answer_id, creation_date, answer_count , comment_count , favorite_count, view_count 
FROM  `stackoverflow.questions_2018_partitioned` 
WHERE creation_date BETWEEN '2018-01-01' AND '2018-02-01'
AND tags = 'android';

3. Seleziona Esegui con la memorizzazione nella cache di BigQuery disabilitata (consulta la sezione precedente per disabilitare la cache di BigQuery). La query restituisce domande di Stack Overflow create nel mese di gennaio 2018 e contrassegnate come android insieme alla domanda e ad alcune altre statistiche.
4. Nei risultati della query dovresti vedere il tempo necessario per il completamento della query e il volume di dati elaborati per ottenere i risultati.

Dovresti vedere che le prestazioni della query con una tabella partizionata sono migliori di quelle della tabella non partizionata, poiché BigQuery elimina le partizioni, ovvero analizza solo le partizioni richieste, elaborando meno dati e con l'esecuzione più rapida. Ciò consente di ottimizzare i costi delle query e le relative prestazioni.

6. Creazione e query in una tabella in cluster

Nella sezione precedente, hai creato una tabella partizionata in BigQuery con i dati della tabella posts_questions nel set di dati pubblico di Stack Overflow. Abbiamo eseguito una query su questa tabella con la memorizzazione nella cache disabilitata e abbiamo osservato le prestazioni delle query con le tabelle non partizionate e partizionate. In questa sezione creerai una nuova tabella in cluster dalla tabella posts_questions dello stesso set di dati pubblico di Stack Overflow e osserverai le prestazioni della query.

Quando una tabella è in cluster in BigQuery, i dati vengono organizzati automaticamente in base ai contenuti di una o più colonne nello schema della tabella. Le colonne specificate vengono utilizzate per collocare i dati correlati. Quando i dati vengono scritti in una tabella in cluster, BigQuery li ordina utilizzando i valori nelle colonne di clustering. Questi valori aiutano a organizzare i dati in più blocchi nello spazio di archiviazione BigQuery. L'ordine delle colonne in cluster determina l'ordinamento dei dati. Quando vengono aggiunti nuovi dati a una tabella o a una partizione specifica, BigQuery esegue il riclustering automatico in background per ripristinare la proprietà di ordinamento della tabella o della partizione.

Scopri di più sull'utilizzo delle tabelle in cluster qui.

Crea una nuova tabella in cluster con i post di Stack Overflow del 2018

In questa sezione creerai una nuova tabella partizionata in base a creation_date e raggruppata in cluster nella colonna tags in base al pattern di accesso alle query. Per creare una tabella in cluster con i dati di una tabella o query esistente, eseguirai una query sulla tabella dei post di Stack Overflow 2018 e scriverai i risultati in una nuova tabella, completando i seguenti passaggi:

1. In alto a destra nella console di Google Cloud, seleziona Crea nuova query.

2. Nell'area di testo Editor query, copia e incolla la seguente query SQL per creare una nuova tabella, che è un'istruzione DDL.

#standardSQL
CREATE OR REPLACE TABLE `stackoverflow.questions_2018_clustered`
PARTITION BY
  DATE(creation_date)
CLUSTER BY
  tags AS
SELECT
  id, title, accepted_answer_id, creation_date, answer_count , comment_count , favorite_count, view_count, tags
FROM
  `bigquery-public-data.stackoverflow.posts_questions`
WHERE
  creation_date BETWEEN '2018-01-01' AND '2019-01-01';

3. Seleziona Esegui. La query crea una nuova tabella questions_2018_clustered nel set di dati stackoverflow del tuo progetto con i dati derivanti dall'esecuzione di una query nella tabella BigQuery Stack Overflowbigquery-public-data.stackoverflow.posts_questions. La nuova tabella è partizionata in data Creation_date e raggruppata nella colonna dei tag.

Nota: questo passaggio richiede circa 2 minuti per creare una nuova tabella. Attendi prima di procedere con il passaggio successivo.

Esegui query sulla tabella in cluster con i post di Stack Overflow del 2018

Ora che hai creato una tabella in cluster BigQuery, esegui di nuovo la stessa query, questa volta sulla tabella partizionata e in cluster, per restituire i post di Stack Overflow con domande e titoli insieme ad altre statistiche come il numero di risposte, commenti, visualizzazioni e preferiti. Completa i seguenti passaggi:

1. In alto a destra nella console di Google Cloud, seleziona Crea nuova query.
2. Nell'area di testo Editor di query, copia e incolla la seguente query SQL.

SELECT id, title, accepted_answer_id, creation_date, answer_count , comment_count , favorite_count, view_count 
FROM  `stackoverflow.questions_2018_clustered` 
WHERE creation_date BETWEEN '2018-01-01' AND '2018-02-01'
AND tags = 'android';

3. Seleziona Esegui con la memorizzazione nella cache di BigQuery disabilitata (consulta la sezione precedente per disabilitare la cache di BigQuery). La query restituisce domande di Stack Overflow create nel mese di gennaio 2018 e contrassegnate come android insieme alla domanda e ad alcune altre statistiche.
4. Nei risultati della query dovresti vedere il tempo necessario per il completamento della query e il volume di dati elaborati per ottenere i risultati.

Con una tabella partizionata e in cluster, la query ha analizzato meno dati rispetto a una tabella partizionata o non partizionata. Il modo in cui i dati sono organizzati tramite partizionamento e clustering riduce al minimo la quantità di dati analizzati dai worker di slot, migliorando così le prestazioni delle query e ottimizzando i costi.

7. esegui la pulizia

A meno che tu non abbia intenzione di continuare a lavorare con il set di dati stackoverflow, devi eliminarlo ed eliminare il progetto che hai creato per questo codelab.

Eliminare il set di dati BigQuery

Per eliminare il set di dati di BigQuery, procedi come segue:

1. Seleziona il set di dati stackoverflow dal pannello di navigazione a sinistra in BigQuery .
2. Nel riquadro dei dettagli, seleziona Elimina set di dati.
3. Nella finestra di dialogo Elimina set di dati, inserisci stackoverflow e seleziona Elimina per confermare che vuoi eliminare il set di dati.

Eliminare il progetto

Per eliminare il progetto Google Cloud che hai creato per questo codelab, segui questi passaggi:

1. Nel menu di navigazione di Google Cloud, seleziona IAM e Console di amministrazione.
2. Nel pannello di navigazione, seleziona Impostazioni.
3. Nel riquadro dei dettagli, verifica che il progetto attuale sia quello che hai creato per questo codelab e seleziona Chiudi.
4. Nella finestra di dialogo Chiudi progetto, inserisci l'ID progetto (non il nome) del progetto e seleziona Chiudi per confermare.

Complimenti! Hai appreso

• Utilizzare la UI web di BigQuery per creare una nuova tabella da tabelle esistenti
• Creare ed eseguire query su tabelle partizionate e in cluster
• In che modo il partizionamento e il clustering ottimizzano le prestazioni e i costi delle query

Tieni presente che non hai dovuto configurare o gestire i cluster per lavorare con i set di dati.