Caricamento di dati JSON da Cloud Storage
Puoi caricare dati JSON delimitati da nuova riga da Cloud Storage in una nuova tabella o partizione oppure aggiungere o sovrascrivere una tabella o partizione esistente. Quando i dati vengono caricati in BigQuery, vengono convertiti in formato a colonne per Condensatore (il formato di archiviazione di BigQuery).
Quando carichi dati da Cloud Storage in una tabella BigQuery, il set di dati che contiene la tabella deve trovarsi nella stessa località a livello di una o più regioni del bucket Cloud Storage.
Il formato JSON delimitato da nuova riga è lo stesso del formato JSON Lines.
Limitazioni
Quando carichi dati in BigQuery da un bucket Cloud Storage, devi rispettare le seguenti limitazioni:
- Se la località del set di dati è impostata su un valore diverso da
US
, il bucket Cloud Storage deve trovarsi nella stessa regione o all'interno della stessa multiregione del set di dati. - BigQuery non garantisce la coerenza dei dati per le origini dati esterne. Le modifiche ai dati sottostanti durante l'esecuzione di una query possono causare comportamenti imprevisti.
- BigQuery non supporta il controllo delle versioni degli oggetti di Cloud Storage. Se includi un numero di generazione nell'URI Cloud Storage, il job di caricamento non va a buon fine.
Quando carichi file JSON in BigQuery, tieni presente quanto segue:
- I dati JSON devono essere delimitati da una nuova riga. Ogni oggetto JSON deve essere su una riga separata nel file.
- Se utilizzi la compressione gzip, BigQuery non è in grado di leggere i dati in parallelo. Il caricamento di dati JSON compressi in BigQuery è più lento rispetto al caricamento dei dati non compressi.
- Non puoi includere file compressi e non compressi nello stesso job di caricamento.
- Le dimensioni massime di un file gzip sono 4 GB.
BigQuery supporta il tipo
JSON
anche se le informazioni sullo schema non sono note al momento dell'importazione. Un campo dichiarato come di tipoJSON
viene caricato con i valori JSON non elaborati.Se utilizzi l'API BigQuery per caricare un numero intero al di fuori dell'intervallo [-253+1, 253-1] (di solito significa maggiore di 9.007.199.254.740.991), in una colonna numero intero (INT64), passalo come stringa per evitare il danneggiamento dei dati. Questo problema è causato da una limitazione sulle dimensioni dei numeri interi in JSON/ECMAScript. Per ulteriori informazioni, consulta la sezione sui numeri di RFC 7159.
- Quando carichi dati CSV o JSON, i valori nelle colonne
DATE
devono utilizzare il separatore del trattino (-
) e la data deve essere nel seguente formato:YYYY-MM-DD
(anno-mese-giorno). - Quando carichi dati JSON o CSV, i valori nelle colonne
TIMESTAMP
devono utilizzare un separatore trattino (-
) o barra (/
) per la parte relativa alla data del timestamp e la data deve essere in uno dei seguenti formati:YYYY-MM-DD
(anno-mese-giorno) oYYYY/MM/DD
(anno/mese/giorno). La partehh:mm:ss
(ora-minuti-secondi) del timestamp deve utilizzare un separatore di due punti (:
). I file devono rispettare i limiti di dimensione dei file JSON descritti nei limiti dei job di caricamento.
Prima di iniziare
Concedi i ruoli IAM (Identity and Access Management) che concedono agli utenti le autorizzazioni necessarie per eseguire ogni attività nel documento e crea un set di dati per archiviare i tuoi dati.
Autorizzazioni obbligatorie
Per caricare dati in BigQuery, devi disporre delle autorizzazioni IAM per eseguire un job di caricamento e caricare i dati in tabelle e partizioni BigQuery. Se stai caricando i dati da Cloud Storage, devi avere anche le autorizzazioni IAM per accedere al bucket che contiene i tuoi dati.
Autorizzazioni per caricare i dati in BigQuery
Per caricare i dati in una nuova tabella o partizione BigQuery oppure per aggiungere o sovrascrivere una tabella o una partizione esistente, devi disporre delle seguenti autorizzazioni IAM:
bigquery.tables.create
bigquery.tables.updateData
bigquery.tables.update
bigquery.jobs.create
Ciascuno dei seguenti ruoli IAM predefiniti include le autorizzazioni necessarie per caricare i dati in una tabella o partizione BigQuery:
roles/bigquery.dataEditor
roles/bigquery.dataOwner
roles/bigquery.admin
(include l'autorizzazionebigquery.jobs.create
)bigquery.user
(include l'autorizzazionebigquery.jobs.create
)bigquery.jobUser
(include l'autorizzazionebigquery.jobs.create
)
Inoltre, se disponi dell'autorizzazione bigquery.datasets.create
, puoi creare e aggiornare le tabelle utilizzando un job di caricamento nei set di dati che crei.
Per ulteriori informazioni su ruoli e autorizzazioni IAM in BigQuery, consulta Autorizzazioni e ruoli predefiniti.
Autorizzazioni per caricare i dati da Cloud Storage
Per ottenere le autorizzazioni necessarie per caricare i dati da un bucket Cloud Storage,
chiedi all'amministratore di concederti il ruolo IAM
Amministratore Storage (roles/storage.admin
) per il bucket.
Per saperne di più sulla concessione dei ruoli, consulta Gestire l'accesso.
Questo ruolo predefinito contiene le autorizzazioni necessarie per caricare i dati da un bucket Cloud Storage. Per visualizzare le autorizzazioni esatte necessarie, espandi la sezione Autorizzazioni richieste:
Autorizzazioni obbligatorie
Per caricare i dati da un bucket Cloud Storage sono necessarie le seguenti autorizzazioni:
-
storage.buckets.get
-
storage.objects.get
-
storage.objects.list (required if you are using a URI wildcard)
Potresti anche essere in grado di ottenere queste autorizzazioni con i ruoli personalizzati o altri ruoli predefiniti.
crea un set di dati
Crea un set di dati BigQuery per archiviare i tuoi dati.
Compressione JSON
Puoi utilizzare l'utilità gzip
per comprimere i file JSON. Tieni presente che gzip
esegue la compressione completa, a differenza della compressione del contenuto dei file eseguita dai codec di compressione per altri formati file, ad esempio Avro. L'utilizzo di gzip
per
comprimere i file JSON potrebbe avere un impatto sulle prestazioni; per ulteriori informazioni
sui compromessi, consulta
Caricare dati compressi e non compressi.
Caricamento di dati JSON in una nuova tabella
Per caricare i dati JSON da Cloud Storage in una nuova tabella BigQuery:
Console
Nella console Google Cloud, vai alla pagina BigQuery.
- Nel riquadro Explorer, espandi il progetto, quindi seleziona un set di dati.
- Nella sezione Informazioni sul set di dati, fai clic su Crea tabella.
- Nel riquadro Crea tabella, specifica i seguenti dettagli:
- Nella sezione Origine, seleziona Google Cloud Storage nell'elenco Crea tabella da.
Poi esegui le seguenti operazioni:
- Seleziona un file dal bucket Cloud Storage o inserisci l'URI Cloud Storage. Non puoi includere più URI nella console Google Cloud, ma sono supportati i caratteri jolly. Il bucket Cloud Storage deve trovarsi nella stessa località del set di dati contenente la tabella da creare, aggiungere o sovrascrivere.
- In Formato file, seleziona JSONL (JSON delimitato da nuova riga).
- Nella sezione Destinazione, specifica i seguenti dettagli:
- In Set di dati, seleziona il set di dati in cui vuoi creare la tabella.
- Nel campo Tabella, inserisci il nome della tabella che vuoi creare.
- Verifica che il campo Tipo di tabella sia impostato su Tabella nativa.
- Nella sezione Schema, inserisci la definizione di schema.
Per attivare il rilevamento automatico di uno schema,
seleziona Rilevamento automatico.
Puoi inserire manualmente le informazioni dello schema utilizzando uno dei seguenti metodi:
- Opzione 1: fai clic su Modifica come testo e incolla lo schema sotto forma di array JSON. Quando utilizzi un array JSON, per generare lo schema segui la stessa procedura utilizzata per la creazione di un file di schema JSON.
Puoi visualizzare lo schema di una tabella esistente in formato JSON inserendo il seguente comando:
bq show --format=prettyjson dataset.table
- Opzione 2: fai clic su Tipo e Modalità per ciascun campo. Aggiungi campo e inserisci lo schema della tabella. Specifica Nome,
- Opzione 1: fai clic su Modifica come testo e incolla lo schema sotto forma di array JSON. Quando utilizzi un array JSON, per generare lo schema segui la stessa procedura utilizzata per la creazione di un file di schema JSON.
Puoi visualizzare lo schema di una tabella esistente in formato JSON inserendo il seguente comando:
- (Facoltativo) Specifica le impostazioni di partizione e cluster. Per saperne di più, consulta Creazione di tabelle partizionate e Creazione e utilizzo delle tabelle in cluster.
- Fai clic su Opzioni avanzate ed esegui le seguenti operazioni:
- In Preferenza di scrittura, lascia selezionata l'opzione Scrivi se vuoto. Questa opzione consente di creare una nuova tabella in cui caricare i dati.
- In Numero di errori consentiti, accetta il valore predefinito di
0
o inserisci il numero massimo di righe contenenti errori che possono essere ignorati. Se il numero di righe con errori supera questo valore, il job restituirà un messaggioinvalid
e avrà esito negativo. Questa opzione si applica solo ai file CSV e JSON. - Se in una riga vuoi ignorare i valori che non sono presenti nello schema della tabella, seleziona Valori sconosciuti.
- In Crittografia, fai clic su Chiave gestita dal cliente per utilizzare una chiave Cloud Key Management Service. Se lasci l'impostazione Chiave gestita da Google, BigQuery cripta i dati at-rest.
- Fai clic su Crea tabella.
SQL
Utilizza l'istruzione DDL LOAD DATA
.
L'esempio seguente carica un file JSON nella nuova tabella mytable
:
Nella console Google Cloud, vai alla pagina BigQuery.
Nell'Editor query, inserisci la seguente istruzione:
LOAD DATA OVERWRITE mydataset.mytable (x INT64,y STRING) FROM FILES ( format = 'JSON', uris = ['gs://bucket/path/file.json']);
Fai clic su
Esegui.
Per ulteriori informazioni su come eseguire le query, consulta Eseguire una query interattiva.
bq
Utilizza il comando bq load
, specifica NEWLINE_DELIMITED_JSON
con il flag --source_format
e includi un URI Cloud Storage.
Puoi includere un singolo URI, un elenco di URI separati da virgole o un URI contenente un caratteri jolly.
Fornisci lo schema incorporato, in un file di definizione dello schema oppure utilizza il rilevamento automatico dello schema.
(Facoltativo) Fornisci il flag --location
e imposta il valore sulla tua
località.
Altri flag facoltativi includono:
--max_bad_records
: un numero intero che specifica il numero massimo di record errati consentiti prima che l'intero job abbia esito negativo. Il valore predefinito è0
. Vengono restituiti al massimo cinque errori di qualsiasi tipo, indipendentemente dal valore--max_bad_records
.--ignore_unknown_values
: se specificato, consente e ignora i valori aggiuntivi non riconosciuti nei dati CSV o JSON.--autodetect
: se specificato, attiva il rilevamento automatico dello schema per i dati CSV e JSON.--time_partitioning_type
: abilita il partizionamento basato sul tempo su una tabella e imposta il tipo di partizione. I valori possibili sonoHOUR
,DAY
,MONTH
eYEAR
. Questo flag è facoltativo quando crei una tabella partizionata in una colonnaDATE
,DATETIME
oTIMESTAMP
. Il tipo di partizione predefinito per il partizionamento basato sul tempo èDAY
. Non puoi modificare la specifica di partizionamento in una tabella esistente.--time_partitioning_expiration
: un numero intero che specifica (in secondi) quando deve essere eliminata una partizione basata sul tempo. La data di scadenza restituisce la data UTC della partizione più il valore intero.--time_partitioning_field
: la colonnaDATE
oTIMESTAMP
utilizzata per creare una tabella partizionata. Se il partizionamento basato sul tempo è abilitato senza questo valore, viene creata una tabella partizionata per data di importazione.--require_partition_filter
: se abilitata, questa opzione richiede agli utenti di includere una clausolaWHERE
che specifichi le partizioni su cui eseguire la query. La richiesta di un filtro di partizionamento può ridurre i costi e migliorare le prestazioni. Per ulteriori informazioni, consulta Esecuzione di query sulle tabelle partizionate.--clustering_fields
: un elenco separato da virgole di massimo quattro nomi di colonna utilizzato per creare una tabella in cluster.--destination_kms_key
: la chiave Cloud KMS per la crittografia dei dati della tabella.Per ulteriori informazioni sulle tabelle partizionate, consulta:
Per ulteriori informazioni sulle tabelle in cluster, consulta:
Per saperne di più sulla crittografia delle tabelle, vedi:
Per caricare dati JSON in BigQuery, inserisci il seguente comando:
bq --location=LOCATION load \ --source_format=FORMAT \ DATASET.TABLE \ PATH_TO_SOURCE \ SCHEMA
Sostituisci quanto segue:
LOCATION
: la tua posizione. Il flag--location
è facoltativo. Ad esempio, se utilizzi BigQuery nella regione di Tokyo, puoi impostare il valore del flag suasia-northeast1
. Puoi impostare un valore predefinito per la località utilizzando il file.bigqueryrc.FORMAT
:NEWLINE_DELIMITED_JSON
.DATASET
: un set di dati esistente.TABLE
: il nome della tabella in cui stai caricando i dati.PATH_TO_SOURCE
: un URI Cloud Storage completo o un elenco di URI separati da virgole. Sono supportati anche i caratteri jolly.SCHEMA
: uno schema valido. Lo schema può essere un file JSON locale o essere digitato in linea come parte del comando. Se utilizzi un file di schema, non assegnargli un'estensione. Puoi anche utilizzare il flag--autodetect
anziché fornire una definizione dello schema.
Esempi:
Il seguente comando carica i dati da gs://mybucket/mydata.json
in una
tabella denominata mytable
in mydataset
. Lo schema viene definito in un file
di schema locale denominato myschema
.
bq load \
--source_format=NEWLINE_DELIMITED_JSON \
mydataset.mytable \
gs://mybucket/mydata.json \
./myschema
Il seguente comando carica i dati da gs://mybucket/mydata.json
in una nuova tabella partizionata per data di importazione denominata mytable
in mydataset
. Lo schema viene definito in un file di schema locale denominato myschema
.
bq load \
--source_format=NEWLINE_DELIMITED_JSON \
--time_partitioning_type=DAY \
mydataset.mytable \
gs://mybucket/mydata.json \
./myschema
Il seguente comando carica i dati da gs://mybucket/mydata.json
in una
tabella partizionata denominata mytable
in mydataset
. La tabella è partizionata
nella colonna mytimestamp
. Lo schema è definito in un file di schema locale denominato myschema
.
bq load \
--source_format=NEWLINE_DELIMITED_JSON \
--time_partitioning_field mytimestamp \
mydataset.mytable \
gs://mybucket/mydata.json \
./myschema
Il seguente comando carica i dati da gs://mybucket/mydata.json
in una
tabella denominata mytable
in mydataset
. Lo schema viene rilevato automaticamente.
bq load \
--autodetect \
--source_format=NEWLINE_DELIMITED_JSON \
mydataset.mytable \
gs://mybucket/mydata.json
Il seguente comando carica i dati da gs://mybucket/mydata.json
in una
tabella denominata mytable
in mydataset
. Lo schema è definito in linea nel formato FIELD:DATA_TYPE, FIELD:DATA_TYPE
.
bq load \
--source_format=NEWLINE_DELIMITED_JSON \
mydataset.mytable \
gs://mybucket/mydata.json \
qtr:STRING,sales:FLOAT,year:STRING
Il seguente comando carica i dati da più file in gs://mybucket/
in una tabella denominata mytable
in mydataset
. L'URI Cloud Storage utilizza
un carattere jolly. Lo schema viene rilevato automaticamente.
bq load \
--autodetect \
--source_format=NEWLINE_DELIMITED_JSON \
mydataset.mytable \
gs://mybucket/mydata*.json
Il seguente comando carica i dati da più file in gs://mybucket/
in una tabella denominata mytable
in mydataset
. Il comando include un elenco separato da virgole di URI Cloud Storage con caratteri jolly. Lo schema è definito in un file di schema locale denominato myschema
.
bq load \
--source_format=NEWLINE_DELIMITED_JSON \
mydataset.mytable \
"gs://mybucket/00/*.json","gs://mybucket/01/*.json" \
./myschema
API
Crea un job
load
che punta ai dati di origine in Cloud Storage.(Facoltativo) Specifica la tua località nella proprietà
location
nella sezionejobReference
della risorsa job.La proprietà
source URIs
deve essere completa nel formatogs://BUCKET/OBJECT
. Ogni URI può contenere un carattere jolly "*"Specifica il formato dei dati
JSON
impostando la proprietàsourceFormat
suNEWLINE_DELIMITED_JSON
.Per controllare lo stato del job, chiama
jobs.get(JOB_ID*)
, sostituendoJOB_ID
con l'ID del job restituito dalla richiesta iniziale.- Se
status.state = DONE
, il job è stato completato correttamente. - Se è presente la proprietà
status.errorResult
, la richiesta non è andata a buon fine e l'oggetto include informazioni che descrivono l'errore. Quando una richiesta non va a buon fine, non viene creata alcuna tabella e non vengono caricati dati. - Se
status.errorResult
è assente, il job è stato completato correttamente; anche se potrebbero essersi verificati alcuni errori non irreversibili, come problemi di importazione di alcune righe. Gli errori non irreversibili sono elencati nella proprietàstatus.errors
dell'oggetto job restituito.
- Se
Note dell'API:
I job di caricamento sono atomici e coerenti. Se un job di caricamento ha esito negativo, nessuno dei dati è disponibile e, se un job di caricamento ha esito positivo, sono disponibili tutti i dati.
Come best practice, genera un ID univoco e passalo come
jobReference.jobId
quando chiamijobs.insert
per creare un job di caricamento. Questo approccio è più solido in caso di errore di rete perché il client può eseguire il polling o riprovare sull'ID job noto.La chiamata a
jobs.insert
per un determinato ID job è idempotente. Puoi riprovare tutte le volte che vuoi sullo stesso ID job e al massimo una di queste operazioni ha esito positivo.
C#
Prima di provare questo esempio, segui le istruzioni di configurazione di C# disponibili nella guida rapida di BigQuery sull'utilizzo delle librerie client. Per saperne di più, consulta la documentazione di riferimento dell'API BigQuery C#.
Per eseguire l'autenticazione in BigQuery, configura Credenziali predefinite dell'applicazione. Per maggiori informazioni, consulta Configurare l'autenticazione per le librerie client.
Utilizza il metodoBigQueryClient.CreateLoadJob()
per avviare un job di caricamento
da Cloud Storage. Per utilizzare JSON delimitato da nuova riga, crea un oggetto CreateLoadJobOptions
e imposta la relativa proprietà SourceFormat
su FileFormat.NewlineDelimitedJson
.
Go
Prima di provare questo esempio, segui le istruzioni di configurazione di Go disponibili nella guida rapida di BigQuery sull'utilizzo delle librerie client. Per saperne di più, consulta la documentazione di riferimento dell'API BigQuery Go.
Per eseguire l'autenticazione in BigQuery, configura Credenziali predefinite dell'applicazione. Per maggiori informazioni, consulta Configurare l'autenticazione per le librerie client.
Java
Prima di provare questo esempio, segui le istruzioni di configurazione di Java disponibili nella guida rapida di BigQuery sull'utilizzo delle librerie client. Per saperne di più, consulta la documentazione di riferimento dell'API BigQuery Java.
Per eseguire l'autenticazione in BigQuery, configura Credenziali predefinite dell'applicazione. Per maggiori informazioni, consulta Configurare l'autenticazione per le librerie client.
Utilizza il metodo LoadJobConfiguration.builder(tableId, sourceUri) per avviare un job di caricamento da Cloud Storage. Per usare un JSON delimitato da nuova riga, utilizza LoadJobConfiguration.setFormatOptions(FormatOptions.json()).
Node.js
Prima di provare questo esempio, segui le istruzioni di configurazione di Node.js disponibili nella guida rapida di BigQuery sull'utilizzo delle librerie client. Per saperne di più, consulta la documentazione di riferimento dell'API BigQuery Node.js.
Per eseguire l'autenticazione in BigQuery, configura Credenziali predefinite dell'applicazione. Per maggiori informazioni, consulta Configurare l'autenticazione per le librerie client.
PHP
Prima di provare questo esempio, segui le istruzioni di configurazione di PHP disponibili nella guida rapida di BigQuery sull'utilizzo delle librerie client. Per saperne di più, consulta la documentazione di riferimento dell'API BigQuery PHP.
Per eseguire l'autenticazione in BigQuery, configura Credenziali predefinite dell'applicazione. Per maggiori informazioni, consulta Configurare l'autenticazione per le librerie client.
Python
Prima di provare questo esempio, segui le istruzioni di configurazione di Python disponibili nella guida rapida di BigQuery sull'utilizzo delle librerie client. Per saperne di più, consulta la documentazione di riferimento dell'API BigQuery Python.
Per eseguire l'autenticazione in BigQuery, configura Credenziali predefinite dell'applicazione. Per maggiori informazioni, consulta Configurare l'autenticazione per le librerie client.
Usa il metodo Client.load_table_from_uri() per avviare un job di caricamento da Cloud Storage. Per utilizzare JSON delimitato da nuova riga, imposta la proprietà LoadJobConfig.source_format sulla stringaNEWLINE_DELIMITED_JSON
e passa la configurazione del job come
argomento job_config
al metodo load_table_from_uri()
.
Ruby
Prima di provare questo esempio, segui le istruzioni di configurazione di Ruby disponibili nella guida rapida di BigQuery sull'utilizzo delle librerie client. Per saperne di più, consulta la documentazione di riferimento dell'API BigQuery Ruby.
Per eseguire l'autenticazione in BigQuery, configura Credenziali predefinite dell'applicazione. Per maggiori informazioni, consulta Configurare l'autenticazione per le librerie client.
Usa il metodo Dataset.load_job() per avviare un job di caricamento da Cloud Storage. Per utilizzare JSON delimitato da nuova riga, imposta il parametroformat
su "json"
.
Caricamento di dati JSON nidificati e ripetuti in corso...
BigQuery supporta il caricamento di dati nidificati e ripetuti da formati di origine che supportano schemi basati su oggetti, come JSON, Avro, ORC, Parquet, Firestore e Datastore.
Un oggetto JSON, inclusi i campi nidificati/ripetuti, deve essere visualizzato su ogni riga.
L'esempio seguente mostra dati nidificati/ripetuti di esempio. Questa tabella contiene informazioni sulle persone. Comprende i seguenti campi:
id
first_name
last_name
dob
(data di nascita)addresses
(un campo nidificato e ripetuto)addresses.status
(attuale o precedente)addresses.address
addresses.city
addresses.state
addresses.zip
addresses.numberOfYears
(anni all'indirizzo)
Il file di dati JSON sarà simile al seguente. Tieni presente che il campo dell'indirizzo contiene un array di valori (indicati da [ ]
).
{"id":"1","first_name":"John","last_name":"Doe","dob":"1968-01-22","addresses":[{"status":"current","address":"123 First Avenue","city":"Seattle","state":"WA","zip":"11111","numberOfYears":"1"},{"status":"previous","address":"456 Main Street","city":"Portland","state":"OR","zip":"22222","numberOfYears":"5"}]} {"id":"2","first_name":"Jane","last_name":"Doe","dob":"1980-10-16","addresses":[{"status":"current","address":"789 Any Avenue","city":"New York","state":"NY","zip":"33333","numberOfYears":"2"},{"status":"previous","address":"321 Main Street","city":"Hoboken","state":"NJ","zip":"44444","numberOfYears":"3"}]}
Lo schema per questa tabella sarebbe il seguente:
[ { "name": "id", "type": "STRING", "mode": "NULLABLE" }, { "name": "first_name", "type": "STRING", "mode": "NULLABLE" }, { "name": "last_name", "type": "STRING", "mode": "NULLABLE" }, { "name": "dob", "type": "DATE", "mode": "NULLABLE" }, { "name": "addresses", "type": "RECORD", "mode": "REPEATED", "fields": [ { "name": "status", "type": "STRING", "mode": "NULLABLE" }, { "name": "address", "type": "STRING", "mode": "NULLABLE" }, { "name": "city", "type": "STRING", "mode": "NULLABLE" }, { "name": "state", "type": "STRING", "mode": "NULLABLE" }, { "name": "zip", "type": "STRING", "mode": "NULLABLE" }, { "name": "numberOfYears", "type": "STRING", "mode": "NULLABLE" } ] } ]
Per informazioni su come specificare uno schema nidificato e ripetuto, consulta Specifica dei campi nidificati e ripetuti.
Caricamento di dati JSON semistrutturati
BigQuery supporta il caricamento di dati semistrutturati, in cui un campo può assumere
valori di diversi tipi. L'esempio seguente mostra dati simili al precedente esempio di dati JSON nidificati e ripetuti, ad eccezione del fatto che il campo address
può essere STRING
, STRUCT
o ARRAY
:
{"id":"1","first_name":"John","last_name":"Doe","dob":"1968-01-22","address":"123 First Avenue, Seattle WA 11111"} {"id":"2","first_name":"Jane","last_name":"Doe","dob":"1980-10-16","address":{"status":"current","address":"789 Any Avenue","city":"New York","state":"NY","zip":"33333","numberOfYears":"2"}} {"id":"3","first_name":"Bob","last_name":"Doe","dob":"1982-01-10","address":[{"status":"current","address":"789 Any Avenue","city":"New York","state":"NY","zip":"33333","numberOfYears":"2"}, "321 Main Street Hoboken NJ 44444"]}
Puoi caricare questi dati in BigQuery utilizzando lo schema seguente:
[ { "name": "id", "type": "STRING", "mode": "NULLABLE" }, { "name": "first_name", "type": "STRING", "mode": "NULLABLE" }, { "name": "last_name", "type": "STRING", "mode": "NULLABLE" }, { "name": "dob", "type": "DATE", "mode": "NULLABLE" }, { "name": "address", "type": "JSON", "mode": "NULLABLE" } ]
Il campo address
viene caricato in una colonna di tipo JSON
che consente di contenere i tipi misti dell'esempio. Puoi importare i dati come JSON
, indipendentemente dal fatto che
contengano tipi misti o meno. Ad esempio, puoi specificare JSON
anziché
STRING
come tipo per il campo first_name
. Per maggiori informazioni, consulta
Utilizzare i dati JSON in GoogleSQL.
Aggiunta o sovrascrittura di una tabella con dati JSON
Puoi caricare dati aggiuntivi in una tabella dai file di origine o aggiungendo i risultati della query.
Nella console Google Cloud, utilizza l'opzione Preferenza di scrittura per specificare l'azione da eseguire quando carichi i dati da un file di origine o da un risultato di query.
Quando carichi dati aggiuntivi in una tabella, hai a disposizione le seguenti opzioni:
Opzione della console | flag dello strumento bq | Proprietà API BigQuery | Descrizione |
---|---|---|---|
Scrivi se vuota | Non supportata | WRITE_EMPTY |
Scrive i dati solo se la tabella è vuota. |
Aggiungi a tabella | --noreplace o --replace=false ; se --[no]replace non è specificato, il valore predefinito è Aggiungi |
WRITE_APPEND |
(Predefinito) Accoda i dati alla fine della tabella. |
Sovrascrivi tabella | --replace o --replace=true |
WRITE_TRUNCATE |
Cancella tutti i dati esistenti in una tabella prima di scrivere nuovi dati. Questa azione elimina anche lo schema della tabella, la sicurezza a livello di riga e rimuove qualsiasi chiave Cloud KMS. |
Se carichi i dati in una tabella esistente, il job di caricamento può aggiungere i dati o sovrascrivere la tabella.
Puoi aggiungere o sovrascrivere una tabella utilizzando uno dei seguenti metodi:
- Nella console Google Cloud
- Il comando
bq load
dello strumento a riga di comando bq - Metodo API
jobs.insert
e configurazione di un jobload
- Le librerie client
Console
Nella console Google Cloud, vai alla pagina BigQuery.
- Nel riquadro Explorer, espandi il progetto, quindi seleziona un set di dati.
- Nella sezione Informazioni sul set di dati, fai clic su Crea tabella.
- Nel riquadro Crea tabella, specifica i seguenti dettagli:
- Nella sezione Origine, seleziona Google Cloud Storage nell'elenco Crea tabella da.
Poi esegui le seguenti operazioni:
- Seleziona un file dal bucket Cloud Storage o inserisci l'URI Cloud Storage. Non puoi includere più URI nella console Google Cloud, ma sono supportati i caratteri jolly. Il bucket Cloud Storage deve trovarsi nella stessa località del set di dati contenente la tabella da creare, aggiungere o sovrascrivere.
- In Formato file, seleziona JSONL (JSON delimitato da nuova riga).
- Nella sezione Destinazione, specifica i seguenti dettagli:
- In Set di dati, seleziona il set di dati in cui vuoi creare la tabella.
- Nel campo Tabella, inserisci il nome della tabella che vuoi creare.
- Verifica che il campo Tipo di tabella sia impostato su Tabella nativa.
- Nella sezione Schema, inserisci la definizione di schema.
Per attivare il rilevamento automatico di uno schema,
seleziona Rilevamento automatico.
Puoi inserire manualmente le informazioni dello schema utilizzando uno dei seguenti metodi:
- Opzione 1: fai clic su Modifica come testo e incolla lo schema sotto forma di array JSON. Quando utilizzi un array JSON, per generare lo schema segui la stessa procedura utilizzata per la creazione di un file di schema JSON.
Puoi visualizzare lo schema di una tabella esistente in formato JSON inserendo il seguente comando:
bq show --format=prettyjson dataset.table
- Opzione 2: fai clic su Tipo e Modalità per ciascun campo. Aggiungi campo e inserisci lo schema della tabella. Specifica Nome,
- Opzione 1: fai clic su Modifica come testo e incolla lo schema sotto forma di array JSON. Quando utilizzi un array JSON, per generare lo schema segui la stessa procedura utilizzata per la creazione di un file di schema JSON.
Puoi visualizzare lo schema di una tabella esistente in formato JSON inserendo il seguente comando:
- (Facoltativo) Specifica le impostazioni di partizione e cluster. Per saperne di più, consulta Creazione di tabelle partizionate e Creazione e utilizzo delle tabelle in cluster. Non puoi convertire una tabella in una tabella partizionata o in cluster aggiungendola o sovrascrivendola. La console Google Cloud non supporta l'aggiunta o la sovrascrittura di tabelle partizionate o in cluster in un job di caricamento.
- Fai clic su Opzioni avanzate ed esegui le seguenti operazioni:
- In Preferenza di scrittura, scegli Aggiungi alla tabella o Sovrascrivi tabella.
- In Numero di errori consentiti, accetta il valore predefinito di
0
o inserisci il numero massimo di righe contenenti errori che possono essere ignorati. Se il numero di righe con errori supera questo valore, il job restituirà un messaggioinvalid
e avrà esito negativo. Questa opzione si applica solo ai file CSV e JSON. - Se in una riga vuoi ignorare i valori che non sono presenti nello schema della tabella, seleziona Valori sconosciuti.
- In Crittografia, fai clic su Chiave gestita dal cliente per utilizzare una chiave Cloud Key Management Service. Se lasci l'impostazione Chiave gestita da Google, BigQuery cripta i dati at-rest.
- Fai clic su Crea tabella.
SQL
Utilizza l'istruzione DDL LOAD DATA
.
Nell'esempio seguente viene aggiunto un file JSON alla tabella mytable
:
Nella console Google Cloud, vai alla pagina BigQuery.
Nell'Editor query, inserisci la seguente istruzione:
LOAD DATA INTO mydataset.mytable FROM FILES ( format = 'JSON', uris = ['gs://bucket/path/file.json']);
Fai clic su
Esegui.
Per ulteriori informazioni su come eseguire le query, consulta Eseguire una query interattiva.
bq
Utilizza il comando bq load
, specifica NEWLINE_DELIMITED_JSON
con il flag --source_format
e includi un URI Cloud Storage.
Puoi includere un singolo URI, un elenco di URI separati da virgole o un URI contenente un caratteri jolly.
Fornisci lo schema incorporato, in un file di definizione dello schema oppure utilizza il rilevamento automatico dello schema.
Specifica il flag --replace
per sovrascrivere la tabella. Utilizza il flag --noreplace
per aggiungere dati alla tabella. Se non viene specificato alcun flag, l'impostazione predefinita prevede l'aggiunta di dati.
È possibile modificare lo schema della tabella quando la aggiungi o la sovrascrivi. Per ulteriori informazioni sulle modifiche allo schema supportate durante un'operazione di caricamento, consulta Modifica degli schemi delle tabelle.
(Facoltativo) Fornisci il flag --location
e imposta il valore sulla tua
località.
Altri flag facoltativi includono:
--max_bad_records
: un numero intero che specifica il numero massimo di record errati consentiti prima che l'intero job abbia esito negativo. Il valore predefinito è0
. Vengono restituiti al massimo cinque errori di qualsiasi tipo, indipendentemente dal valore--max_bad_records
.--ignore_unknown_values
: se specificato, consente e ignora i valori aggiuntivi non riconosciuti nei dati CSV o JSON.--autodetect
: se specificato, attiva il rilevamento automatico dello schema per i dati CSV e JSON.--destination_kms_key
: la chiave Cloud KMS per la crittografia dei dati della tabella.
bq --location=LOCATION load \ --[no]replace \ --source_format=FORMAT \ DATASET.TABLE \ PATH_TO_SOURCE \ SCHEMA
Sostituisci quanto segue:
LOCATION
: la tua località. Il flag--location
è facoltativo. Puoi impostare un valore predefinito per la località utilizzando il file.bigqueryrc.FORMAT
:NEWLINE_DELIMITED_JSON
.DATASET
: un set di dati esistente.TABLE
: il nome della tabella in cui stai caricando i dati.PATH_TO_SOURCE
: un URI Cloud Storage completo o un elenco di URI separati da virgole. Sono supportati anche i caratteri jolly.SCHEMA
: uno schema valido. Lo schema può essere un file JSON locale o essere digitato in linea come parte del comando. Puoi anche utilizzare il flag--autodetect
anziché fornire una definizione dello schema.
Esempi:
Il seguente comando carica i dati da gs://mybucket/mydata.json
e
sovrascrive una tabella denominata mytable
in mydataset
. Lo schema viene definito utilizzando il rilevamento automatico dello schema.
bq load \
--autodetect \
--replace \
--source_format=NEWLINE_DELIMITED_JSON \
mydataset.mytable \
gs://mybucket/mydata.json
Il seguente comando carica i dati da gs://mybucket/mydata.json
e
aggiunge i dati a una tabella denominata mytable
in mydataset
. Lo schema viene definito utilizzando un file di schema JSON: myschema
.
bq load \
--noreplace \
--source_format=NEWLINE_DELIMITED_JSON \
mydataset.mytable \
gs://mybucket/mydata.json \
./myschema
API
Crea un job
load
che punta ai dati di origine in Cloud Storage.(Facoltativo) Specifica la tua località nella proprietà
location
nella sezionejobReference
della risorsa job.La proprietà
source URIs
deve essere completa, nel formatogs://BUCKET/OBJECT
. Puoi includere più URI sotto forma di elenco separato da virgole. Sono supportati anche i caratteri jolly.Specifica il formato dei dati impostando la proprietà
configuration.load.sourceFormat
suNEWLINE_DELIMITED_JSON
.Specifica la preferenza di scrittura impostando la proprietà
configuration.load.writeDisposition
suWRITE_TRUNCATE
oWRITE_APPEND
.
Go
Prima di provare questo esempio, segui le istruzioni di configurazione di Go disponibili nella guida rapida di BigQuery sull'utilizzo delle librerie client. Per saperne di più, consulta la documentazione di riferimento dell'API BigQuery Go.
Per eseguire l'autenticazione in BigQuery, configura Credenziali predefinite dell'applicazione. Per maggiori informazioni, consulta Configurare l'autenticazione per le librerie client.
Java
Node.js
Prima di provare questo esempio, segui le istruzioni di configurazione di Node.js disponibili nella guida rapida di BigQuery sull'utilizzo delle librerie client. Per saperne di più, consulta la documentazione di riferimento dell'API BigQuery Node.js.
Per eseguire l'autenticazione in BigQuery, configura Credenziali predefinite dell'applicazione. Per maggiori informazioni, consulta Configurare l'autenticazione per le librerie client.
PHP
Prima di provare questo esempio, segui le istruzioni di configurazione di PHP disponibili nella guida rapida di BigQuery sull'utilizzo delle librerie client. Per saperne di più, consulta la documentazione di riferimento dell'API BigQuery PHP.
Per eseguire l'autenticazione in BigQuery, configura Credenziali predefinite dell'applicazione. Per maggiori informazioni, consulta Configurare l'autenticazione per le librerie client.
Python
Per sostituire le righe in una tabella esistente, imposta la proprietà LoadJobConfig.write_disposition sulla stringa WRITE_TRUNCATE
.
Prima di provare questo esempio, segui le istruzioni di configurazione di Python disponibili nella guida rapida di BigQuery sull'utilizzo delle librerie client. Per saperne di più, consulta la documentazione di riferimento dell'API BigQuery Python.
Per eseguire l'autenticazione in BigQuery, configura Credenziali predefinite dell'applicazione. Per maggiori informazioni, consulta Configurare l'autenticazione per le librerie client.
Ruby
Per sostituire le righe in una tabella esistente, imposta il parametro write
di Table.load_job() su "WRITE_TRUNCATE"
.
Prima di provare questo esempio, segui le istruzioni di configurazione di Ruby disponibili nella guida rapida di BigQuery sull'utilizzo delle librerie client. Per saperne di più, consulta la documentazione di riferimento dell'API BigQuery Ruby.
Per eseguire l'autenticazione in BigQuery, configura Credenziali predefinite dell'applicazione. Per maggiori informazioni, consulta Configurare l'autenticazione per le librerie client.
Caricamento di dati JSON partizionati hive
BigQuery supporta il caricamento dei dati JSON partizionati hive archiviati in Cloud Storage e compila le colonne di partizionamento hive come colonne nella tabella gestita BigQuery di destinazione. Per maggiori informazioni, consulta Caricamento di dati partizionati esternamente.
Dettagli del caricamento dei dati JSON
Questa sezione descrive in che modo BigQuery analizza i vari tipi di dati durante il caricamento dei dati JSON.
Tipi di dati
Booleano. BigQuery può analizzare una qualsiasi delle seguenti coppie per i dati booleani: 1 o 0, vero o falso, t o f, sì o no, y o n (tutti senza distinzione tra maiuscole e minuscole). Il rilevamento automatico dello schema rileva automaticamente uno qualsiasi di questi valori, ad eccezione di 0 e 1.Byte. Le colonne con tipi BYTES devono essere codificate come Base64.
Data. Le colonne con tipi di DATA devono essere nel formato YYYY-MM-DD
.
Data/ora. Le colonne con tipi DATETIME devono essere nel formato YYYY-MM-DD
HH:MM:SS[.SSSSSS]
.
Area geografica. Le colonne con tipi GEOGRAPHY devono contenere stringhe in uno dei seguenti formati:
- Testo noto (WKT)
- Programma binario noto (WKB)
- GeoJSON
Se utilizzi WKB, il valore deve essere con codifica esadecimale.
Il seguente elenco mostra esempi di dati validi:
- Tempo di visualizzazione (ore):
POINT(1 2)
- GeoJSON:
{ "type": "Point", "coordinates": [1, 2] }
- WKB con codifica esadecimale:
0101000000feffffffffffef3f0000000000000040
Prima di caricare i dati GEOGRAPHY, leggi anche Caricamento dei dati geospaziali.
Intervallo. Le colonne con tipi INTERVAL devono essere nel formato ISO 8601 PYMDTHMS
, dove:
- P = indicatore che indica che il valore rappresenta una durata. Devi sempre includerlo.
- Y = anno
- M = mese
- D = giorno
- T = Designatore che indica la parte temporale della durata. Devi sempre includerlo.
- H = Ora
- M = minuto
- S = secondo. I secondi possono essere indicati come valore intero o frazionario di massimo sei cifre con una precisione in microsecondi.
Puoi indicare un valore negativo anteponendo un trattino (-).
Il seguente elenco mostra esempi di dati validi:
P-10000Y0M-3660000DT-87840000H0M0S
P0Y0M0DT0H0M0.000001S
P10000Y0M3660000DT87840000H0M0S
Per caricare i dati INTERVAL, devi utilizzare il comando bq load
e il flag --schema
per specificare uno schema. Non puoi caricare i dati INTERVAL utilizzando la console.
Ora. Le colonne con i tipi TIME devono essere nel formato HH:MM:SS[.SSSSSS]
.
Timestamp. BigQuery accetta vari formati di timestamp. Il timestamp deve includere una parte relativa alla data e una parte relativa all'ora.
La parte della data può essere formattata come
YYYY-MM-DD
oYYYY/MM/DD
.La parte del timestamp deve essere formattata come
HH:MM[:SS[.SSSSSS]]
(secondi e frazioni di secondi sono facoltative).Data e ora devono essere separate da uno spazio o da una "T".
Facoltativamente, la data e l'ora possono essere seguite da una differenza rispetto al fuso orario UTC o all'indicazione del fuso orario UTC (
Z
). Per saperne di più, consulta Fusi orari.
Ad esempio, quelli riportati di seguito sono valori di timestamp validi:
- 19-08-2018 12:11
- 19-08-2018 12:11:35
- 19-08-2018 12:11:35.22
- 19/08/2018 12:11
- 05-07-2018 12:54:00 UTC
- 19-08-2018 07:11:35.220 -05:00
- 2018-08-19T12:11:35.220Z
Se fornisci uno schema, BigQuery accetta anche l'ora epoch Unix per i valori del timestamp. Tuttavia, il rilevamento automatico dello schema non rileva questo caso e considera il valore come numerico o di tipo stringa.
Esempi di valori di timestamp Unix epoch:
- 1534680695
- 1,534680695e11
Array (campo ripetuto). Il valore deve essere un array JSON o null
. JSON
null
viene convertito in SQL NULL
. L'array non può contenere valori null
.
Rilevamento automatico dello schema
Questa sezione descrive il comportamento del rilevamento automatico dello schema durante il caricamento dei file JSON.
Campi JSON nidificati e ripetuti
BigQuery deduce i campi nidificati e ripetuti nei file JSON. Se un
valore di campo è un oggetto JSON, BigQuery carica la colonna come
tipo RECORD
. Se il valore di un campo è un array, BigQuery carica la colonna come colonna ripetuta. Per un esempio di dati JSON con dati nidificati e ripetuti, consulta Caricamento di dati JSON nidificati e ripetuti.
Conversione di stringhe
Se attivi il rilevamento automatico degli schemi, BigQuery converte le stringhe in tipi booleani, numerici o data/ora, se possibile. Ad esempio,
utilizzando i seguenti dati JSON, il rilevamento automatico dello schema converte il campo id
in una colonna INTEGER
:
{ "name":"Alice","id":"12"}
{ "name":"Bob","id":"34"}
{ "name":"Charles","id":"45"}
Tipi di codifica
BigQuery prevede che i dati JSON abbiano codifica UTF-8. Se hai file JSON con altri tipi di codifica supportati, devi specificare esplicitamente la codifica utilizzando il flag --encoding
in modo che BigQuery converta i dati in UTF-8.
BigQuery supporta i seguenti tipi di codifica per i file JSON:
- UTF-8
- ISO-8859-1
- UTF-16BE (big Endian UTF-16)
- UTF-16LE (Little Endian UTF-16)
- UTF-32BE (Big Endian UTF-32)
- UTF-32LE (Little Endian UTF-32)
Opzioni JSON
Per cambiare il modo in cui BigQuery analizza i dati JSON, specifica opzioni aggiuntive nella console Google Cloud, nello strumento a riga di comando bq, nell'API o nelle librerie client.
Opzione JSON | Opzione della console | flag dello strumento bq | Proprietà API BigQuery | Descrizione |
---|---|---|---|---|
Numero di record non validi consentiti | Numero di errori consentiti | --max_bad_records |
maxBadRecords
(Java,
Python)
|
(Facoltativo) Numero massimo di record non validi che BigQuery può ignorare durante l'esecuzione del job. Se il numero di record errati supera questo valore, viene restituito un errore non valido nel risultato del job. Il valore predefinito è "0", che richiede che tutti i record siano validi. |
Valori sconosciuti | Ignora valori sconosciuti | --ignore_unknown_values |
ignoreUnknownValues
(Java,
Python)
|
(Facoltativo) Indica se BigQuery deve consentire valori aggiuntivi non rappresentati nello schema della tabella. Se il valore è true, i valori aggiuntivi vengono ignorati. Se il valore è falso, i record con colonne aggiuntive vengono trattati come record non validi e, se sono presenti troppi record errati, viene restituito un errore non valido nel risultato del job. Il valore predefinito è false. La proprietà "sourceFormat" determina cosa viene considerato da BigQuery come valore aggiuntivo: CSV: colonne finali, JSON: valori denominati che non corrispondono a nessun nome di colonna. |
Codifica | Nessuna | -E o --encoding |
encoding
(Python) |
(Facoltativo) La codifica dei caratteri dei dati. I valori supportati sono UTF-8, ISO-8859-1, UTF-16BE, UTF-16LE, UTF-32BE o UTF-32LE. Il valore predefinito è UTF-8. |
Passaggi successivi
- Per informazioni sul caricamento dei dati JSON da un file locale, consulta Caricamento di dati da file locali.
- Per maggiori informazioni sulla creazione, sull'importazione e sull'esecuzione di query sui dati JSON, consulta Utilizzare i dati JSON in GoogleSQL.