Eseguire query su più tabelle utilizzando una tabella con funzione carattere jolly
Le tabelle con caratteri jolly consentono di eseguire query su più tabelle utilizzando SQL conciso istruzioni. Le tabelle con caratteri jolly sono disponibili solo in GoogleSQL. Per equivalente di SQL precedente, consulta Funzioni con caratteri jolly nella tabella.
Una tabella con funzione carattere jolly rappresenta un'unione di tutte le tabelle che corrispondono all'espressione con funzione carattere jolly. Ad esempio, la seguente clausola FROM
utilizza il carattere jolly
dell'espressione gsod*
per trovare corrispondenze di tutte le tabelle nel set di dati noaa_gsod
che iniziano
con la stringa gsod
.
FROM
`bigquery-public-data.noaa_gsod.gsod*`
Ogni riga della tabella dei caratteri jolly contiene una colonna speciale, _TABLE_SUFFIX
, che contiene il valore corrispondente al carattere jolly.
Limitazioni
Le query sulle tabelle con caratteri jolly sono soggette alle seguenti limitazioni.
- La funzionalità della tabella con caratteri jolly non supporta le visualizzazioni. Se la tabella con caratteri jolly corrisponde a una vista nel set di dati, la query restituisce un errore anche se contiene una clausola
WHERE
nella pseudocolonna_TABLE_SUFFIX
per escludere la vista. - I risultati memorizzati nella cache non sono supportati per le query su più utilizzando un carattere jolly, anche se è selezionata l'opzione Utilizza risultati memorizzati nella cache. Se esegui la stessa query con caratteri jolly più volte, ti viene addebitato un importo per ogni query.
- Le tabelle con caratteri jolly supportano solo lo spazio di archiviazione BigQuery integrato. Tu Impossibile utilizzare caratteri jolly per eseguire query su una tabella esterna o una vista.
- Non puoi utilizzare query con caratteri jolly su tabelle con partizionamento incompatibile o un mix di tabelle partizionate e non partizionate. Anche le tabelle per cui è stata eseguita la query devono avere specifiche di clustering identiche.
- Puoi utilizzare tabelle con caratteri jolly con tabelle partizionate e sono supportate sia l'eliminazione delle partizioni sia l'eliminazione dei cluster. Tuttavia, le tabelle raggruppate in cluster, ma non partizionate, non traggono alcun vantaggio dall'utilizzo di caratteri jolly per il travaglio dei cluster.
- Query che contengono
Data Manipulation Language (Manipolazione dei dati)
Le istruzioni (DML) non possono utilizzare una tabella con funzione carattere jolly come destinazione della query. Ad esempio, una tabella con caratteri jolly può essere utilizzata nella clausola
FROM
di una queryUPDATE
, ma non può essere utilizzata come destinazione dell'operazioneUPDATE
. - I filtri sulle pseudocolonne
_TABLE_SUFFIX
o_PARTITIONTIME
che includono funzioni definite dall'utente JavaScript non limitano il numero di tabelle sottoposte a scansione in una tabella con caratteri jolly. - Le query con caratteri jolly non sono supportate per le tabelle protette dalla rete e le chiavi di crittografia CMEK.
- Tutte le tabelle a cui viene fatto riferimento in una query con caratteri jolly devono avere esattamente lo stesso insieme di chiavi e valori tag.
Quando utilizzi tabelle con caratteri jolly, tutte le tabelle nel set di dati che iniziano con nome della tabella prima che
*
venga scansionato anche se viene utilizzato_TABLE_SUFFIX
in combinazione conREGEXP_CONTAINS
e viene fornita un'espressione regolare, come^[0-9]{2}$
. Ad esempio:SELECT * FROM `my_project.my_dataset.my_table_*` WHERE REGEXP_CONTAINS(_TABLE_SUFFIX, '^[0-9]{2}$');
Se una singola tabella analizzata presenta una mancata corrispondenza dello schema (ovvero una colonna con lo stesso nome è di un tipo diverso), la query non riesce e restituisce l'errore Impossibile campo letto di tipo X come Y Campo: column_name. Tutte le tabelle hanno una corrispondenza uniforme se utilizzi l'operatore di uguaglianza
=
. Ad esempio, nel seguente viene analizzata anche la tabellamy_dataset.my_table_03_backup
. Di conseguenza, la query potrebbe non riuscire a causa di una mancata corrispondenza dello schema. Tuttavia, in assenza di uno schema mancata corrispondenza, i risultati provengono solo dalla tabellamy_dataset.my_table_03
, previsto.SELECT * FROM
my_project.my_dataset.my_table_*
WHERE _TABLE_SUFFIX = '03'
Prima di iniziare
- Assicurati di utilizzare GoogleSQL. Per ulteriori informazioni, consulta la sezione Cambiare i dialetti SQL.
- Se utilizzi SQL precedente, consulta la sezione Funzioni con caratteri jolly per le tabelle.
- Molti degli esempi in questa pagina utilizzano un set di dati pubblico della National Oceanic and Atmospheric Administration (NOAA). Per ulteriori informazioni sui dati, consulta NOAA Global Surface Summary of the Day Weather Data.
Quando utilizzare le tabelle con caratteri jolly
Le tabelle con caratteri jolly sono utili quando un set di dati contiene più dati con nomi simili che hanno schemi compatibili. In genere, questi set di dati contengono tabelle che rappresentano ciascuna i dati di un singolo giorno, mese o anno. Ad esempio, un per il set di dati pubblico ospitato da BigQuery, Riepilogo della superficie globale NOAA dei dati meteo giornalieri, contiene una tabella per ogni anno dal 1929 a oggi.
Una query che esegue la scansione di tutti gli ID tabella dal 1929 al 1940 sarebbe molto lunga se dovessi assegnare un nome a tutte e 12 le tabelle nella clausola FROM
(la maggior parte delle tabelle è omessa in questo esempio):
#standardSQL SELECT max, ROUND((max-32)*5/9,1) celsius, mo, da, year FROM ( SELECT * FROM `bigquery-public-data.noaa_gsod.gsod1929` UNION ALL SELECT * FROM `bigquery-public-data.noaa_gsod.gsod1930` UNION ALL SELECT * FROM `bigquery-public-data.noaa_gsod.gsod1931` UNION ALL # ... Tables omitted for brevity SELECT * FROM `bigquery-public-data.noaa_gsod.gsod1940` ) WHERE max != 9999.9 # code for missing data ORDER BY max DESC
La stessa query che utilizza una tabella con caratteri jolly è molto più concisa:
#standardSQL SELECT max, ROUND((max-32)*5/9,1) celsius, mo, da, year FROM `bigquery-public-data.noaa_gsod.gsod19*` WHERE max != 9999.9 # code for missing data AND _TABLE_SUFFIX BETWEEN '29' AND '40' ORDER BY max DESC
Sintassi delle tabelle con caratteri jolly
Sintassi delle tabelle con caratteri jolly:
SELECT FROM `<project-id>.<dataset-id>.<table-prefix>*` WHERE bool_expression
- <project-id>
- ID progetto Cloud Platform. (Facoltativo) Se utilizzi l'ID progetto predefinito.
- <dataset-id>
- ID set di dati BigQuery .
- <table-prefix>
- Una stringa comune a tutte le tabelle corrispondente al carattere jolly . Il prefisso della tabella è facoltativo. L'omissione del prefisso della tabella corrisponde a tutte le tabelle del set di dati.
- * (carattere jolly)
- Il carattere jolly "*" rappresenta uno o più caratteri di un nome tabella. Il carattere jolly può apparire solo come carattere finale di un il nome tabella con funzione carattere jolly.
Le query con tabelle con caratteri jolly supportano la pseudocolonna _TABLE_SUFFIX
in
Clausola WHERE
. Questa colonna contiene i valori corrispondenti al carattere jolly
di modo che le query possano filtrare le tabelle a cui si accede. Ad esempio,
le seguenti clausole WHERE
utilizzano
operatori di confronto
per filtrare le tabelle corrispondenti:
WHERE
_TABLE_SUFFIX BETWEEN '29' AND '40'
WHERE
_TABLE_SUFFIX = '1929'
WHERE
_TABLE_SUFFIX < '1941'
Per saperne di più sulla pseudocolonna _TABLE_SUFFIX
, consulta
Filtro delle tabelle selezionate utilizzando _TABLE_SUFFIX.
Racchiudi i nomi delle tabelle con caratteri jolly tra apici inversi
Il nome della tabella jolly contiene il carattere speciale (*), il che significa che devi racchiudere il nome della tabella jolly tra i caratteri backtick (`). Per Ad esempio, la seguente query è valida perché utilizza apici inversi:
#standardSQL /* Valid SQL query */ SELECT max FROM `bigquery-public-data.noaa_gsod.gsod*` WHERE max != 9999.9 # code for missing data AND _TABLE_SUFFIX = '1929' ORDER BY max DESC
La seguente query NON è valida perché non è racchiusa tra virgolette con i simboli ".
#standardSQL /* Syntax error: Expected end of statement but got "-" at [4:11] */ SELECT max FROM # missing backticks bigquery-public-data.noaa_gsod.gsod* WHERE max != 9999.9 # code for missing data AND _TABLE_SUFFIX = '1929' ORDER BY max DESC
Le virgolette non funzionano:
#standardSQL /* Syntax error: Unexpected string literal: 'bigquery-public-data.noaa_gsod.gsod*' at [4:3] */ SELECT max FROM # quotes are not backticks 'bigquery-public-data.noaa_gsod.gsod*' WHERE max != 9999.9 # code for missing data AND _TABLE_SUFFIX = '1929' ORDER BY max DESC
Esegui query sulle tabelle utilizzando le tabelle con caratteri jolly
Le tabelle con caratteri jolly ti consentono di eseguire query su più tabelle in modo conciso.
Ad esempio, un set di dati pubblico ospitato da BigQuery,
il NOAA Global Surface Summary of the Day Weather Data,
contiene una tabella per ogni anno dal 1929 a oggi in cui tutti condividono
prefisso comune gsod
seguito dalle quattro cifre dell'anno. Le tabelle hanno un nome
gsod1929
, gsod1930
, gsod1931
e così via.
Per eseguire una query su un gruppo di tabelle che condividono un prefisso comune, utilizza il carattere jolly della tabella
simbolo (*) dopo il prefisso della tabella nell'istruzione FROM
. Ad esempio,
la seguente query trova la temperatura massima registrata negli anni '40:
#standardSQL
SELECT
max,
ROUND((max-32)*5/9,1) celsius,
mo,
da,
year
FROM
`bigquery-public-data.noaa_gsod.gsod194*`
WHERE
max != 9999.9 # code for missing data
ORDER BY
max DESC
Filtrare le tabelle selezionate utilizzando _TABLE_SUFFIX
Per limitare una query in modo che esamini solo un insieme specificato di tabelle, utilizza la pseudocolonna _TABLE_SUFFIX
in una clausola WHERE
con una condizione che sia un'espressione costante.
La pseudocolonna _TABLE_SUFFIX
contiene i valori corrispondenti al carattere jolly della tabella. Ad esempio, la query precedente, che analizza tutte le tabelle
anni '40, utilizza un carattere jolly nella tabella per rappresentare l'ultima cifra dell'anno:
FROM
`bigquery-public-data.noaa_gsod.gsod194*`
La pseudocolonna _TABLE_SUFFIX
corrispondente contiene valori nell'intervallo
Da 0
a 9
, che rappresentano le tabelle da gsod1940
a gsod1949
. Questi valori _TABLE_SUFFIX
possono essere utilizzati in una clausola WHERE
per filtrare in base a tabelle specifiche.
Ad esempio, per filtrare in base alla temperatura massima negli anni 1940 e 1944,
utilizza i valori 0
e 4
per _TABLE_SUFFIX
:
#standardSQL
SELECT
max,
ROUND((max-32)*5/9,1) celsius,
mo,
da,
year
FROM
`bigquery-public-data.noaa_gsod.gsod194*`
WHERE
max != 9999.9 # code for missing data
AND ( _TABLE_SUFFIX = '0'
OR _TABLE_SUFFIX = '4' )
ORDER BY
max DESC
L'utilizzo di _TABLE_SUFFIX
può ridurre notevolmente il numero di byte analizzati, contribuendo a ridurre il costo di esecuzione delle query.
Tuttavia, i filtri su _TABLE_SUFFIX
che includono condizioni senza espressioni costanti non limitano il numero di tabelle analizzate in una tabella con caratteri jolly. Ad esempio, la seguente query non limita le tabelle sottoposte a scansione per la tabella con caratteri jolly bigquery-public-data.noaa_gsod.gsod19*
perché il filtro utilizza il valore dinamico della colonna table_id
:
#standardSQL
# Scans all tables that match the prefix `gsod19`
SELECT
ROUND((max-32)*5/9,1) celsius
FROM
`bigquery-public-data.noaa_gsod.gsod19*`
WHERE
_TABLE_SUFFIX = (SELECT SUBSTR(MAX(table_name), LENGTH('gsod19') + 1)
FROM `bigquery-public-data.noaa_gsod.INFORMATION_SCHEMA.TABLES`
WHERE table_name LIKE 'gsod194%')
Un altro esempio è la seguente query, che limita la ricerca in base alla prima condizione di filtro, _TABLE_SUFFIX BETWEEN '40' and '60'
, perché è un'espressione costante.
Tuttavia, la seguente query non limita la ricerca in base alla seconda condizione di filtro, _TABLE_SUFFIX = (SELECT SUBSTR(MAX(table_name), LENGTH('gsod19')
+ 1) FROM
, perché è un'espressione dinamica:bigquery-public-data.noaa_gsod.INFORMATION_SCHEMA.TABLES
WHERE table_name LIKE
'gsod194%')
#standardSQL
# Scans all tables with names that fall between `gsod1940` and `gsod1960`
SELECT
ROUND((max-32)*5/9,1) celsius
FROM
`bigquery-public-data.noaa_gsod.gsod19*`
WHERE
_TABLE_SUFFIX BETWEEN '40' AND '60'
AND _TABLE_SUFFIX = (SELECT SUBSTR(MAX(table_name), LENGTH('gsod19') + 1)
FROM `bigquery-public-data.noaa_gsod.INFORMATION_SCHEMA.TABLES`
WHERE table_name LIKE 'gsod194%')
Come soluzione alternativa, puoi eseguire due query separate: Ad esempio:
Prima query:
#standardSQL
# Get the list of tables that match the required table name prefixes
SELECT SUBSTR(MAX(table_name), LENGTH('gsod19') + 1)
FROM `bigquery-public-data.noaa_gsod.INFORMATION_SCHEMA.TABLES`
WHERE table_name LIKE 'gsod194%'
Seconda query:
#standardSQL
# Construct the second query based on the values from the first query
SELECT
ROUND((max-32)*5/9,1) celsius
FROM
`bigquery-public-data.noaa_gsod.gsod19*`
WHERE _TABLE_SUFFIX = '49'
Queste query di esempio utilizzano la visualizzazione INFORMATION_SCHEMA.TABLES
. Per maggiori informazioni
informazioni sulla tabella INFORMATION_SCHEMA
, consulta Recupero della tabella
utilizzando INFORMATION_SCHEMA.
Scansione di un intervallo di tabelle con _TABLE_SUFFIX
Per eseguire la scansione di un intervallo di tabelle, utilizza la pseudocolonna _TABLE_SUFFIX
insieme alla clausola BETWEEN
. Ad esempio, per trovare la temperatura massima riportata in
gli anni tra il 1929 e il 1935 inclusi, utilizza il carattere jolly della tabella per rappresentare
le ultime due cifre dell'anno:
#standardSQL
SELECT
max,
ROUND((max-32)*5/9,1) celsius,
mo,
da,
year
FROM
`bigquery-public-data.noaa_gsod.gsod19*`
WHERE
max != 9999.9 # code for missing data
AND _TABLE_SUFFIX BETWEEN '29' and '35'
ORDER BY
max DESC
Scansione di un intervallo di tabelle partizionate in base alla data di importazione utilizzando _PARTITIONTIME
Per analizzare un intervallo di tabelle partizionate per data di importazione, utilizza _PARTITIONTIME
pseudocolonna con la pseudocolonna _TABLE_SUFFIX
. Ad esempio, la seguente query esegue la scansione della partizione del 1° gennaio 2017 nella tabella my_dataset.mytable_id1
.
#standardSQL
SELECT
field1,
field2,
field3
FROM
`my_dataset.mytable_*`
WHERE
_TABLE_SUFFIX = 'id1'
AND _PARTITIONTIME = TIMESTAMP('2017-01-01')
Esecuzione di query su tutte le tabelle in un set di dati
Per eseguire la scansione di tutte le tabelle di un set di dati, puoi utilizzare un prefisso vuoto e il carattere jolly per le tabelle, il che significa che la pseudocolonna _TABLE_SUFFIX
contiene i nomi completi delle tabelle. Ad esempio, la seguente clausola FROM
esegue la scansione di tutte le tabelle nel set di dati GSOD:
FROM
`bigquery-public-data.noaa_gsod.*`
Con un prefisso vuoto, la pseudocolonna _TABLE_SUFFIX
contiene la tabella completa
i nomi degli utenti. Ad esempio, la seguente query equivale all'esempio precedente
che trova la temperatura massima tra gli anni 1929 e 1935, ma utilizza
nomi completi delle tabelle nella clausola WHERE
:
#standardSQL
SELECT
max,
ROUND((max-32)*5/9,1) celsius,
mo,
da,
year
FROM
`bigquery-public-data.noaa_gsod.*`
WHERE
max != 9999.9 # code for missing data
AND _TABLE_SUFFIX BETWEEN 'gsod1929' and 'gsod1935'
ORDER BY
max DESC
Tieni presente, tuttavia, che i prefissi più lunghi hanno in genere un rendimento migliore. Per maggiori informazioni informazioni, consulta le best practice.
Dettagli sull'esecuzione della query
Schema utilizzato per la valutazione delle query
Per eseguire una query GoogleSQL che utilizza una tabella con caratteri jolly,
BigQuery deduce automaticamente lo schema della tabella.
BigQuery utilizza lo schema per la tabella creata più di recente
che corrisponde al carattere jolly come schema per la tabella con funzione carattere jolly. Anche se
limita il numero di tabelle da utilizzare dalla tabella con funzione carattere jolly usando
la pseudocolonna _TABLE_SUFFIX
in una clausola WHERE
, BigQuery
utilizza lo schema dell'ultima tabella creata che corrisponde al carattere jolly.
Se una colonna dello schema dedotto non esiste in una tabella con corrispondenza,
BigQuery restituisce valori NULL
per quella colonna nelle righe della tabella in cui manca la colonna.
Se lo schema non è coerente nelle tabelle corrispondenti alla query con caratteri jolly, BigQuery restituisce un errore. Questo è il caso le colonne delle tabelle corrispondenti hanno tipi di dati diversi o quando le colonne che non sono presenti in tutte le tabelle corrispondenti non si può presumere che abbiano un valore nullo.
Best practice
In genere, i prefissi più lunghi hanno un rendimento migliore rispetto a quelli più brevi. Ad esempio, la seguente query utilizza un prefisso lungo (
gsod200
):#standardSQL SELECT max FROM `bigquery-public-data.noaa_gsod.gsod200*` WHERE max != 9999.9 # code for missing data AND _TABLE_SUFFIX BETWEEN '0' AND '1' ORDER BY max DESC
La seguente query in genere ha un rendimento peggiore perché utilizza un campo vuoto prefisso:
#standardSQL SELECT max FROM `bigquery-public-data.noaa_gsod.*` WHERE max != 9999.9 # code for missing data AND _TABLE_SUFFIX BETWEEN 'gsod2000' AND 'gsod2001' ORDER BY max DESC
Si consiglia il partizionamento rispetto allo sharding, in quanto le tabelle partizionate meglio. Lo sharding riduce le prestazioni e crea più tabelle da gestire. Per Per ulteriori informazioni, consulta la sezione Partizionamento e sharding.
Per le best practice per il controllo dei costi in BigQuery, consulta Controllo dei costi in BigQuery
Passaggi successivi
- Per saperne di più su GoogleSQL, consulta Riferimento per le query GoogleSQL.