Modificare manualmente gli schemi della tabella

Questo documento descrive come modificare manualmente la definizione dello schema per le tabelle BigQuery esistenti. Molte modifiche allo schema non sono supportate in modo nativo in BigQuery e richiedono soluzioni alternative manuali. Le modifiche allo schema non supportate includono:

• Modifica del nome di una colonna.
• Modifica di un tipo di dati di una colonna.
• Modifica della modalità di una colonna (oltre al passaggio della colonna REQUIRED a NULLABLE).
• Eliminazione di una colonna.

Per ulteriori informazioni sulle modifiche agli schemi supportate in BigQuery, consulta la sezione Modificare gli schemi delle tabelle.

Attenzione: le opzioni descritte in questa pagina includono la sovrascrittura della tabella o il caricamento dei dati in una nuova tabella. Per le tabelle partizionate in fase di importazione, queste operazioni distruggono le informazioni di partizionamento originali. Al termine dell'operazione, tutti i dati della tabella risiedono in una singola partizione.

Modificare il nome di una colonna

La console, lo strumento a riga di comando bq o l'API non supportano la ridenominazione di una colonna. Se provi ad aggiornare uno schema di tabella utilizzando una colonna rinominata, viene restituito il seguente errore: BigQuery error in update operation: Provided Schema does not match Table project_id:dataset.table.

Esistono due modi per rinominare manualmente una colonna:

• Utilizzando una query SQL: scegli questa opzione se hai maggiore dubbi sulla semplicità e facilità d'uso e meno sui costi.
• Ricrea la tabella: scegli questa opzione se hai più dubbi sui costi e meno sulla semplicità.

Opzione 1: utilizza le query

Per modificare il nome di una colonna utilizzando una query SQL, seleziona tutte le colonne della tabella e alias la colonna che vuoi rinominare. Puoi usare il risultato della query per sovrascrivere la tabella esistente o crearne una nuova. Quando alias la colonna con un nuovo nome, questa deve rispettare le regole di BigQuery per i nomi di colonne.

Vantaggi

• L'utilizzo di una query per scrivere i dati in una nuova tabella di destinazione conserva i dati originali.
• Se utilizzi il job di query per sovrascrivere la tabella originale, ti verranno addebitati i costi di archiviazione di una tabella anziché di due, ma perderai i dati originali.

Svantaggi

• La ridenominazione di una colonna tramite una query richiede la scansione dell'intera tabella. Gli addebiti per le query possono essere significativi se la tabella è molto grande.
• Se scrivi i risultati della query in una nuova tabella di destinazione, ti verranno addebitati i costi di archiviazione sia per la vecchia tabella sia per la nuova, a meno che non elimini la vecchia tabella.
• Le colonne rinominate obbligatorie sono diventate nulli.

Esempio di alias di colonna

L'esempio seguente mostra una query SQL standard che seleziona tutti i dati in mytable ad eccezione di due colonne che richiedono la ridenominazione. Un alias viene utilizzato per generare nuovi nomi per le due colonne. column_one è stato rinominato newcolumn_one e column_two è stato rinominato newcolumn_two. Il risultato della query viene utilizzato per sovrascrivere la tabella esistente.

console

1. Nella console, seleziona Editor query.

2. Nell'Editor query, inserisci la seguente query per selezionare tutti i dati in mydataset.mytable, tranne le due colonne che devono essere rinominate. mydataset.mytable è nel tuo progetto predefinito. La query utilizza un alias per cambiare il nome di column_one in newcolumn_one e il nome di column_two in newcolumn_two.

   SELECT
    * EXCEPT(column_one, column_two),
    column_one AS newcolumn_one, column_two AS newcolumn_two
   FROM
    mydataset.mytable

3. Fai clic su Altro e seleziona Impostazioni query.

4. Nella sezione Destinazione:

   1. Seleziona Imposta una tabella di destinazione per i risultati della query.

   2. Per Set di dati, scegli myproject.mydataset.

   3. In ID tabella, inserisci mytable.

   4. Per Preferenza di scrittura per tabella di destinazione, seleziona Sovrascrivi tabella. Questa operazione sovrascrive mytable utilizzando i risultati della query.

5. Fai clic su Salva per aggiornare le impostazioni, quindi nell'Editor query fai clic su Esegui. Una volta completato il job di query, le colonne in mytable hanno nuovi nomi.

bq

Immetti il comando bq query seguente per selezionare tutti i dati in mydataset.mytable, tranne le due colonne da rinominare. mydataset.mytable è nel tuo progetto predefinito. La query utilizza un alias per cambiare il nome di column_one in newcolumn_one e il nome di column_two in newcolumn_two.

Scrivi i risultati della query a mydataset.mytable utilizzando il flag --destination_table e specifica il flag --replace per sovrascrivere mytable. Specifica il flag use_legacy_sql=false per utilizzare la sintassi SQL standard.

(Facoltativo) Fornisci il flag --location e imposta il valore sulla tua località.

bq query \
--destination_table mydataset.mytable \
--replace \
--use_legacy_sql=false \
'SELECT
  * EXCEPT(column_one,
    column_two),
  column_one AS newcolumn_one,
  column_two AS newcolumn_two
FROM
  mydataset.mytable'

Server

Per cambiare il nome di column_one in newcolumn_one e il nome di column_two in newcolumn_two, chiama il metodo jobs.insert e configura un job query. (Facoltativo) Specifica la tua località nella proprietà location della sezione jobReference.

La query SQL utilizzata nel job di query sarebbe: SELECT * EXCEPT(column_one, column_two), column_one AS newcolumn_one, column_two AS newcolumn_two FROM mydataset.mytable. Questa query seleziona tutti i dati in mytable ad eccezione di due colonne che richiedono una ridenominazione. Un alias viene utilizzato per generare nuovi nomi per le due colonne.

Per sovrascrivere mytable con i risultati della query, includi mydataset.mytable nella proprietà configuration.query.destinationTable e specifica WRITE_TRUNCATE nella proprietà configuration.query.writeDisposition. Per specificare una nuova tabella di destinazione, inserisci il nome della tabella nella proprietà configuration.query.destinationTable.

Opzione 2: esporta e carica i dati in una nuova tabella

Puoi anche rinominare una colonna esportando i dati della tabella in Cloud Storage, quindi caricarli in una nuova tabella con una definizione di schema contenente il nome della colonna corretto. Puoi anche utilizzare il job di caricamento per sovrascrivere la tabella esistente.

Vantaggi

• Non ti viene addebitato alcun costo per il job di esportazione o il job di caricamento. Attualmente, i job di caricamento ed esportazione di BigQuery sono gratuiti.
• Se utilizzi il job di caricamento per sovrascrivere la tabella originale, ti verranno addebitati i costi di archiviazione di una tabella anziché di due, ma perderai i dati originali.

Svantaggi

• Se carichi i dati in una nuova tabella, ti verranno addebitati i costi di archiviazione per la tabella originale e la nuova tabella, a meno che non elimini quella precedente.
• Ti vengono addebitati i costi per l'archiviazione dei dati esportati in Cloud Storage.

Modificare il tipo di dati di una colonna

La modifica del tipo di dati di una colonna non è supportata dalla console, dallo strumento a riga di comando bq o dall'API. Se provi ad aggiornare una tabella applicando uno schema che specifica un nuovo tipo di dati per una colonna, viene restituito il seguente errore: BigQuery error in update operation: Provided Schema does not match Table project_id:dataset.table.

Esistono due modi per modificare manualmente il tipo di dati di una colonna:

• Utilizzando una query SQL: scegli questa opzione se hai maggiore dubbi sulla semplicità e facilità d'uso e meno sui costi.
• Ricrea la tabella: scegli questa opzione se hai più dubbi sui costi e meno sulla semplicità.

Opzione 1: utilizza le query

Utilizza una query SQL per selezionare tutti i dati della tabella e trasmettere la colonna pertinente come tipo di dati diverso. Puoi utilizzare i risultati della query per sovrascrivere la tabella o creare una nuova tabella di destinazione.

Vantaggi

• L'utilizzo di una query per scrivere i dati in una nuova tabella di destinazione conserva i dati originali.
• Se utilizzi il job di query per sovrascrivere la tabella originale, ti verranno addebitati i costi di archiviazione di una tabella anziché di due, ma perderai i dati originali.

Svantaggi

• Per cambiare il tipo di dati di una colonna utilizzando una query è necessario analizzare l'intera tabella. Gli addebiti per le query possono essere significativi se la tabella è molto grande.
• Se scrivi i risultati della query in una nuova tabella di destinazione, ti verranno addebitati i costi di archiviazione sia per la vecchia tabella sia per la nuova, a meno che non elimini la vecchia tabella.

Esempio di CAST

L'esempio seguente mostra una query SQL standard che seleziona tutti i dati da column_two e column_three in mydataset.mytable e trasmette column_one da DATE a STRING. Il risultato della query viene utilizzato per sovrascrivere la tabella esistente. La tabella sovrascritta archivia column_one come tipo di dati STRING.

Se utilizzi CAST, una query può non riuscire se BigQuery non è in grado di eseguire la trasmissione. Per informazioni dettagliate sulla trasmissione di regole in SQL standard, consulta la sezione Trasmissione nella documentazione di riferimento per funzioni e operatori.

console

1. Nella console, fai clic su Crea nuova query.

2. Nell'Editor query, inserisci la seguente query per selezionare tutti i dati da column_two e column_three in mydataset.mytable e trasmettere column_one da DATE a STRING. La query utilizza un alias per trasmettere column_one con lo stesso nome. mydataset.mytable è nel tuo progetto predefinito.

   SELECT
    column_two, column_three, CAST(column_one AS STRING) AS column_one
   FROM
    mydataset.mytable

3. Fai clic su Altro e seleziona Impostazioni query.

4. Nella sezione Destinazione, seleziona Imposta una tabella di destinazione per i risultati della query.

5. Nei seguenti campi:

   1. Per Nome progetto, lascia il valore impostato sul progetto predefinito. Questo è il progetto che contiene mydataset.mytable.

   2. In Nome set di dati, scegli mydataset.

   3. Nel campo Nome tabella, inserisci mytable.

   4. Fai clic su OK.

6. Nella sezione Preferenza di scrittura per tabella di destinazione, per Preferenza di scrittura, scegli Sovrascrivi tabella. Questa operazione sovrascrive mytable utilizzando i risultati della query.

7. (Facoltativo) In Località di elaborazione, fai clic su Selezione automatica e scegli la località dei tuoi dati.

8. Fai clic su Salva per aggiornare le impostazioni, quindi nell'Editor query fai clic su Esegui. Quando il job di query viene completato, il tipo di dati column_one è STRING.

bq

Inserisci il comando bq query seguente per selezionare tutti i dati da column_two e column_three in mydataset.mytable e trasmettere column_one da DATE a STRING. La query utilizza un alias per trasmettere column_one con lo stesso nome. mydataset.mytable è nel tuo progetto predefinito.

I risultati della query vengono scritti in mydataset.mytable utilizzando il flag --destination_table e il flag --replace viene utilizzato per sovrascrivere mytable. Specifica il flag use_legacy_sql=false per utilizzare la sintassi SQL standard.

(Facoltativo) Fornisci il flag --location e imposta il valore sulla tua località.

bq query \
--destination_table mydataset.mytable \
--replace \
--use_legacy_sql=false \
'SELECT
  column_two,
  column_three,
  CAST(column_one AS STRING) AS column_one
FROM
  mydataset.mytable'

Server

Per selezionare tutti i dati da column_two e column_three in mydataset.mytable e trasmettere column_one da DATE a STRING, chiama il metodo jobs.insert e configura un job query. (Facoltativo) Specifica la tua località nella proprietà location nella sezione jobReference.

La query SQL utilizzata nel job di query sarebbe: SELECT column_two, column_three, CAST(column_one AS STRING) AS column_one FROM mydataset.mytable. La query utilizza un alias per trasmettere column_one con lo stesso nome.

Per sovrascrivere mytable con i risultati della query, includi mydataset.mytable nella proprietà configuration.query.destinationTable e specifica WRITE_TRUNCATE nella proprietà configuration.query.writeDisposition.

Opzione 2: esporta e carica i dati in una nuova tabella

Puoi anche modificare il tipo di dati di una colonna esportando i dati della tabella in Cloud Storage e poi caricandoli in una nuova tabella con una definizione di schema che specifica il tipo di dati corretto per questa colonna. Puoi anche utilizzare il job di caricamento per sovrascrivere la tabella esistente.

Vantaggi

• Non ti viene addebitato alcun costo per il job di esportazione o il job di caricamento. Attualmente, i job di caricamento ed esportazione di BigQuery sono gratuiti.
• Se utilizzi il job di caricamento per sovrascrivere la tabella originale, ti verranno addebitati i costi di archiviazione di una tabella anziché di due, ma perderai i dati originali.

Svantaggi

• Se carichi i dati in una nuova tabella, ti verranno addebitati i costi di archiviazione per la tabella originale e la nuova tabella, a meno che non elimini quella precedente.
• Ti vengono addebitati i costi per l'archiviazione dei dati esportati in Cloud Storage.

Modificare la modalità colonna

Attualmente, l'unica modifica supportata che puoi apportare alla modalità colonna è modificarla da REQUIRED a NULLABLE. Il passaggio dalla modalità di REQUIRED di una colonna a NULLABLE viene chiamato anche allentamento delle colonne. Per informazioni sul aggiornamento di REQUIRED colonne come NULLABLE, consulta la sezione Rilassare la modalità di una colonna.

Se cerchi di applicare una modifica non supportata a una modalità colonna, viene restituito un errore simile al seguente. In questo esempio, è stato effettuato un tentativo di modificare una modalità colonna da NULLABLE a REPEATED: BigQuery error in update operation: Provided Schema does not match Table project_id:dataset.table. Field field has changed mode from NULLABLE to REPEATED.

Esporta e carica i dati in una nuova tabella

Puoi modificare manualmente la modalità di una colonna esportando i dati della tabella in Cloud Storage e poi caricandoli in una nuova tabella con una definizione di schema che specifica la modalità corretta per la colonna. Puoi anche utilizzare il job di caricamento per sovrascrivere la tabella esistente.

Vantaggi

• Non ti viene addebitato alcun costo per il job di esportazione o il job di caricamento. Attualmente, i job di caricamento ed esportazione di BigQuery sono gratuiti.
• Se utilizzi il job di caricamento per sovrascrivere la tabella originale, ti verranno addebitati i costi di archiviazione di una tabella anziché di due, ma perderai i dati originali.

Svantaggi

• Se carichi i dati in una nuova tabella, ti verranno addebitati i costi di archiviazione per la tabella originale e la nuova tabella, a meno che non elimini quella precedente.
• Ti vengono addebitati i costi per l'archiviazione dei dati esportati in Cloud Storage.

Elimina colonne dallo schema della tabella

L'eliminazione di una colonna da uno schema di tabella esistente non è supportato dalla console, dallo strumento a riga di comando bq o dall'API. Se provi ad aggiornare una tabella applicando uno schema che rimuove una colonna, viene restituito il seguente errore: BigQuery error in update operation: Provided Schema does not match Table project_id:dataset.table.

Puoi eliminare manualmente una colonna in due modi:

• Utilizzando una query SQL: scegli questa opzione se hai maggiore dubbi sulla semplicità e facilità d'uso e meno sui costi.
• Ricrea la tabella: scegli questa opzione se hai più dubbi sui costi e meno sulla semplicità.

Opzione 1: utilizza le query

Per eliminare una colonna, puoi utilizzare due istruzioni SQL:

• SELECT * EXCEPT
• ALTER TABLE DROP COLUMN

La sezione seguente mostra come utilizzare una query SELECT * EXCEPT che esclude la colonna (o le colonne) da rimuovere e utilizzare il risultato della query per sovrascrivere la tabella o crearne una nuova.

Per utilizzare l'istruzione ALTER TABLE DROP COLUMN, consulta gli esempi nella pagina relativa alla lingua di definizione dei dati.

Vantaggi

• L'utilizzo di una query per scrivere i dati in una nuova tabella di destinazione conserva i dati originali.
• Se utilizzi il job di query per sovrascrivere la tabella originale, ti verranno addebitati i costi di archiviazione di una tabella anziché di due, ma perderai i dati originali.

Svantaggi

• Per eliminare una colonna utilizzando una query, devi eseguire la scansione dei dati in tutte le colonne ad eccezione di quella che stai rimuovendo. Gli addebiti per le query possono essere significativi se la tabella è molto grande.
• Se scrivi i risultati della query in una nuova tabella di destinazione, ti verranno addebitati i costi di archiviazione sia per la vecchia tabella sia per la nuova, a meno che non elimini la vecchia tabella.

Esempio di SELECT * EXCEPT

L'esempio seguente mostra una query SQL standard che seleziona tutti i dati da mydataset.mytable ad eccezione di column_two. Il risultato della query viene utilizzato per sovrascrivere la tabella esistente.

console

1. Nella console, fai clic su Crea nuova query.

2. Nell'Editor query, inserisci la seguente query per selezionare tutti i dati da mydataset.mytable tranne column_two. mydataset.mytable è nel tuo progetto predefinito.

   SELECT
    * EXCEPT(column_two)
   FROM
    mydataset.mytable

3. Fai clic su Altro e seleziona Impostazioni query.

4. Nella sezione Destinazione, seleziona Imposta una tabella di destinazione per i risultati della query.

5. Nei seguenti campi:

   1. Per Nome progetto, lascia il valore impostato sul progetto predefinito. Questo è il progetto che contiene mydataset.mytable.

   2. In Nome set di dati, scegli mydataset.

   3. Nel campo Nome tabella, inserisci mytable.

   4. Fai clic su OK.

6. Nella sezione Preferenza di scrittura per tabella di destinazione, per Preferenza di scrittura, scegli Sovrascrivi tabella. Questa operazione sovrascrive mytable utilizzando i risultati della query.

7. (Facoltativo) In Località di elaborazione, fai clic su Selezione automatica e scegli la località dei tuoi dati.

8. Fai clic su Salva per aggiornare le impostazioni, quindi nell'Editor query fai clic su Esegui. Quando il job di query viene completato, la tabella ora include tutte le colonne tranne column_two.

bq

Inserisci il seguente comando bq query per selezionare tutti i dati da mydataset.mytable ad eccezione di column_two. mydataset.mytable è nel tuo progetto predefinito. I risultati della query vengono scritti in mydataset.mytable utilizzando il flag --destination_table e il flag --replace viene utilizzato per sovrascrivere mytable. Specifica il flag use_legacy_sql=false per utilizzare la sintassi SQL standard.

(Facoltativo) Fornisci il flag --location e imposta il valore sulla tua località.

bq query \
--destination_table mydataset.mytable \
--replace \
--use_legacy_sql=false \
'SELECT
  * EXCEPT(column_two)
FROM
  mydataset.mytable'

Server

Per selezionare tutti i dati da mydataset.mytable tranne column_two, chiama il metodo jobs.insert e configura un job query. (Facoltativo) Specifica la tua località nella proprietà location della sezione jobReference.

La query SQL utilizzata nel job di query sarebbe: SELECT * EXCEPT(column_two) FROM mydataset.mytable.

Per sovrascrivere mytable con i risultati della query, includi mydataset.mytable nella proprietà configuration.query.destinationTable e specifica WRITE_TRUNCATE nella proprietà configuration.query.writeDisposition.

Opzione 2: esporta e carica i dati in una nuova tabella

Puoi anche rimuovere una colonna esportando i dati della tabella in Cloud Storage, eliminando i dati corrispondenti alla colonna o alle colonne che vuoi rimuovere e poi caricando i dati in una nuova tabella con una definizione di schema che non include le colonne rimosse. Puoi anche utilizzare il job di caricamento per sovrascrivere la tabella esistente.

Vantaggi

• Non ti viene addebitato alcun costo per il job di esportazione o il job di caricamento. Attualmente, i job di caricamento ed esportazione di BigQuery sono gratuiti.
• Se utilizzi il job di caricamento per sovrascrivere la tabella originale, ti verranno addebitati i costi di archiviazione di una tabella anziché di due, ma perderai i dati originali.

Svantaggi

• Se carichi i dati in una nuova tabella, ti verranno addebitati i costi di archiviazione per la tabella originale e la nuova tabella, a meno che non elimini quella precedente.
• Ti vengono addebitati i costi per l'archiviazione dei dati esportati in Cloud Storage.

Sicurezza tavoli

Per controllare l'accesso alle tabelle in BigQuery, consulta Introduzione ai controlli di accesso alla tabella.