Migrazione da tabelle non partizionate a partizioni

A giugno il team di BQ ha annunciato il supporto per le tabelle partizionate per data . Ma alla guida manca come migrare le vecchie tabelle non partizionate nel nuovo stile.

Sto cercando un modo per aggiornare più o meno tutte le tabelle con il nuovo stile.

Anche al di fuori del tipo DAY sono partizionate quali altre opzioni sono disponibili? L’interfaccia utente BQ mostra questo, poiché non ero in grado di creare una nuova tabella partizionata dall’interfaccia utente Web BQ.

    dalla risposta di Pavan: Si noti che questo approccio addebiterà il costo di scansione della tabella di origine per la query tutte le volte che lo si interroga.


    dai commenti di Pentium10: Supponiamo che abbia diversi anni di dati, ho bisogno di preparare diverse query per ogni giorno e di eseguirle tutte, e suppongo di avere 1000 giorni di storia, ho bisogno di pagare 1000 volte il prezzo completo della query dalla fonte tavolo?


    Come possiamo vedere, il problema principale qui è avere una scansione completa per ogni giorno. Il resto è meno problematico e può essere facilmente copiato in qualsiasi client di scelta

    Quindi, di seguito è riportato – Come partizionare la tabella evitando al tempo stesso la scansione completa della tabella per ogni giorno?

    Sotto la procedura dettagliata viene mostrato l’approccio

    È abbastanza generico da estendere / applicare a qualsiasi caso d’uso reale – nel frattempo sto usando bigquery-public-data.noaa_gsod.gsod2017 e sto limitando “esercizio” a soli 10 giorni per tenerlo leggibile

    Passaggio 1 : crea la tabella pivot
    In questo passo noi
    a) comprime il contenuto di ogni riga in record / array
    e
    b) metterli tutti nella rispettiva colonna “giornaliera”

     #standardSQL SELECT ARRAY_CONCAT_AGG(CASE WHEN d = 'day20170101' THEN r END) AS day20170101, ARRAY_CONCAT_AGG(CASE WHEN d = 'day20170102' THEN r END) AS day20170102, ARRAY_CONCAT_AGG(CASE WHEN d = 'day20170103' THEN r END) AS day20170103, ARRAY_CONCAT_AGG(CASE WHEN d = 'day20170104' THEN r END) AS day20170104, ARRAY_CONCAT_AGG(CASE WHEN d = 'day20170105' THEN r END) AS day20170105, ARRAY_CONCAT_AGG(CASE WHEN d = 'day20170106' THEN r END) AS day20170106, ARRAY_CONCAT_AGG(CASE WHEN d = 'day20170107' THEN r END) AS day20170107, ARRAY_CONCAT_AGG(CASE WHEN d = 'day20170108' THEN r END) AS day20170108, ARRAY_CONCAT_AGG(CASE WHEN d = 'day20170109' THEN r END) AS day20170109, ARRAY_CONCAT_AGG(CASE WHEN d = 'day20170110' THEN r END) AS day20170110 FROM ( SELECT d, r, ROW_NUMBER() OVER(PARTITION BY d) AS line FROM ( SELECT stn, CONCAT('day', year, mo, da) AS d, ARRAY_AGG(t) AS r FROM `bigquery-public-data.noaa_gsod.gsod2017` AS t GROUP BY stn, d ) ) GROUP BY line 

    Esegui sopra la query nell’interfaccia utente Web con pivot_table (o qualsiasi altro nome è preferibile) come destinazione

    Come possiamo vedere – qui otterremo una tabella con 10 colonne: una colonna per un giorno e lo schema di ogni colonna è una copia dello schema della tabella originale:

    inserisci la descrizione dell'immagine qui

    Passaggio 2 – Elaborazione delle partizioni SOLO per scansione della rispettiva colonna (nessuna scansione completa della tabella) – inserimento nella rispettiva partizione

     #standardSQL SELECT r.* FROM pivot_table, UNNEST(day20170101) AS r 

    Esegui sopra la query dall’interfaccia utente Web con la tabella di destinazione denominata mytable $ 20160101

    Puoi eseguire lo stesso per il giorno successivo

     #standardSQL SELECT r.* FROM pivot_table, UNNEST(day20170102) AS r 

    Ora dovresti avere una tabella di destinazione come mytable $ 20160102 e così via

    inserisci la descrizione dell'immagine qui

    Dovresti essere in grado di automatizzare / scriptare questo passaggio con qualsiasi client di tua scelta

    Ci sono molte varianti di come puoi usare l’approccio sopra – dipende dalla tua creatività

    Nota: BigQuery consente fino a 10000 colonne nella tabella, quindi 365 colonne per i rispettivi giorni di un anno non sono sicuramente un problema qui: o) A meno che non ci sia una limitazione su quanto indietro si può andare con le nuove partizioni – ho sentito (ma non Non ho ancora la possibilità di controllare) ora non ci sono più di 90 giorni indietro

    Aggiornare

    Nota: la versione precedente ha un po ‘di logica in più per il riempimento di tutte le celle aggregate nel minor numero ansible di righe finali.

    ROW_NUMBER() OVER(PARTITION BY d) AS line
    e poi
    GROUP BY line
    insieme a
    ARRAY_CONCAT_AGG(…)
    fa questo

    Funziona bene quando la dimensione della riga nella tabella originale non è così grande, quindi la dimensione finale della riga combinata sarà comunque entro il limite di dimensioni delle righe di BigQuery (che credo sia di 10 MB ora)

    Se la tabella di origine ha già la dimensione della riga vicina a tale limite, utilizzare la versione corretta sotto riportata

    In questa versione, il raggruppamento viene rimosso in modo tale che ogni riga abbia solo valore per una colonna

     #standardSQL SELECT CASE WHEN d = 'day20170101' THEN r END AS day20170101, CASE WHEN d = 'day20170102' THEN r END AS day20170102, CASE WHEN d = 'day20170103' THEN r END AS day20170103, CASE WHEN d = 'day20170104' THEN r END AS day20170104, CASE WHEN d = 'day20170105' THEN r END AS day20170105, CASE WHEN d = 'day20170106' THEN r END AS day20170106, CASE WHEN d = 'day20170107' THEN r END AS day20170107, CASE WHEN d = 'day20170108' THEN r END AS day20170108, CASE WHEN d = 'day20170109' THEN r END AS day20170109, CASE WHEN d = 'day20170110' THEN r END AS day20170110 FROM ( SELECT stn, CONCAT('day', year, mo, da) AS d, ARRAY_AGG(t) AS r FROM `bigquery-public-data.noaa_gsod.gsod2017` AS t GROUP BY stn, d ) WHERE d BETWEEN 'day20170101' AND 'day20170110' 

    Come puoi vedere ora – la tabella pivot (sparce_pivot_table) è abbastanza sparsa (stesso 21,5 MB ma ora 114.089 righe vs 11.584 righe in pivot_table) quindi ha una dimensione media delle righe di 190B contro 1.9KB nella versione iniziale. Ovviamente è circa 10 volte inferiore rispetto al numero di colonne nell’esempio.
    Quindi, prima di utilizzare questo approccio, è necessario fare alcuni calcoli per progettare / stimare cosa e come può essere fatto!

    inserisci la descrizione dell'immagine qui

    Ancora: ogni cella nella tabella pivot è una sorta di rappresentazione JSON dell’intera riga nella tabella originale. È tale che contiene non solo i valori come era per le righe nella tabella originale ma ha anche uno schema in esso

    inserisci la descrizione dell'immagine qui

    Come tale è abbastanza dettagliato – quindi la dimensione della cella può essere più volte più grande della dimensione originale [che limita l’utilizzo di questo approccio … a meno che non diventi ancora più creativo: o) … che è ancora pieno di aree qui applicare: o)]

    Fino a quando la nuova funzionalità non viene implementata in BigQuery, c’è un altro (molto più economico) modo di partizionare le tabelle usando Cloud Dataflow . Abbiamo usato questo approccio invece di eseguire centinaia di SELECT * , che ci sarebbero costate migliaia di dollari.

    1. Crea la tabella partizionata in BigQuery usando il normale comando di partition
    2. Creare una pipeline Dataflow e utilizzare un sink BigQuery.IO.Read per leggere la tabella
    3. Usa una trasformazione Partition per partizionare ogni riga
    4. Usando un massimo di 200 frammenti / sinks alla volta (più di quello e raggiungi i limiti API), crea un sink BigQuery.IO.Write per ogni giorno / frammento che scriverà sulla partizione corrispondente usando la syntax decoratore della partizione – "$YYYYMMDD"
    5. Ripeti N volte finché tutti i dati non vengono elaborati.

    Ecco un esempio su Github per iniziare.

    Devi ancora pagare la / e pipeline Dataflow, ma è una frazione del costo dell’utilizzo di più SELECT * in BigQuery.

    A partire da oggi, è ora ansible creare una tabella partizionata da una tabella non partizionata, interrogandoli e specificando la colonna della partizione. Pagherai per una scansione completa della tabella sulla tabella originale (non partizionata). Nota : questo è attualmente in beta.

    https://cloud.google.com/bigquery/docs/creating-column-partitions#creating_a_partitioned_table_from_a_query_result

    Per creare una tabella partizionata da un risultato di una query, scrivere i risultati in una nuova tabella di destinazione. È ansible creare una tabella partizionata interrogando una tabella partizionata o una tabella non partizionata. Non è ansible modificare una tabella standard esistente in una tabella partizionata utilizzando i risultati della query.

    Se oggi disponi di tabelle con data e ora, puoi utilizzare questo approccio:

    https://cloud.google.com/bigquery/docs/creating-partitioned-tables#converting_dated_tables_into_a_partitioned_table

    Se si ha una singola tabella non partizionata da convertire in una tabella partizionata, è ansible provare l’approccio dell’esecuzione di una query SELECT * con risultati grandi e l’utilizzo della partizione della tabella come destinazione (analogamente a come si ripristinano i dati per una partizione):

    https://cloud.google.com/bigquery/docs/creating-partitioned-tables#restating_data_in_a_partition

    Tieni presente che questo approccio addebiterà il costo di scansione della tabella di origine per la query tutte le volte che lo richiedi.

    Stiamo lavorando a qualcosa per rendere questo scenario significativamente migliore nei prossimi mesi.