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 corrispondenti al carattere jolly un'espressione di base. 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 nella tabella con funzione carattere jolly contiene una colonna speciale, _TABLE_SUFFIX, che contiene il valore che corrisponde al carattere jolly.

Limitazioni

Le query delle tabelle con caratteri jolly sono soggette alle seguenti limitazioni.

  • La funzionalità tabella con funzione carattere jolly non supporta le viste. Se la tabella con funzione carattere jolly corrisponde a qualsiasi vista nel set di dati, la query restituisce un errore anche se la query contiene una clausola WHERE nella pseudocolonna _TABLE_SUFFIX da filtrare 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 ogni costo query.
  • Le tabelle con caratteri jolly supportano solo l'archiviazione BigQuery integrata. 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 una combinazione di tabelle partizionate e non partizionate. Anche le tabelle per cui è stata eseguita la query devono avere specifiche di clustering identiche.
  • Puoi usare tabelle con caratteri jolly con tabelle partizionate, nonché l'eliminazione delle partizioni l'eliminazione dei cluster. Tuttavia, le tabelle sono in cluster ma non partizionate di eliminare i cluster dall'utilizzo dei caratteri jolly.
  • 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. Per Ad esempio, è possibile usare una tabella con funzione carattere jolly nella clausola FROM di una tabella UPDATE ma non è possibile usare una tabella con funzione carattere jolly come destinazione di UPDATE operativa.
  • Filtri nelle pseudocolonne _TABLE_SUFFIX o _PARTITIONTIME che includono Le funzioni JavaScript definite dall'utente non limitano il numero di tabelle analizzate in un con tabella con funzione carattere jolly.
  • Le query con caratteri jolly non sono supportate per le tabelle protette dalla rete e le chiavi di crittografia CMEK.
  • I controlli di controllo dell'accesso per le query con caratteri jolly non vengono considerati condizionali accesso concesso per le tabelle con tag.

  • Quando utilizzi tabelle con caratteri jolly, tutte le tabelle nel set di dati che iniziano con nome della tabella prima di * vengono scansionati anche se viene utilizzato _TABLE_SUFFIX in combinazione con REGEXP_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 tabella my_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 tabella my_dataset.my_table_03, previsto. 

    SELECT *
FROM my_project.my_dataset.my_table_*
WHERE _TABLE_SUFFIX = '03'

Prima di iniziare

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 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 analizza tutti gli ID tabella dal 1929 al 1940 sarebbe molto lunga se devi assegnare un nome a tutte e 12 le tabelle nella clausola FROM (la maggior parte delle tabelle sono omessi 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 funzione carattere 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
Le tabelle con caratteri jolly supportano BigQuery integrato solo spazio di archiviazione. Non è possibile utilizzare caratteri jolly durante l'esecuzione di query su un tabella esterna o una vista.

Sintassi delle tabelle con caratteri jolly

Sintassi delle tabelle con caratteri jolly:

SELECT
FROM
  `<project-id>.<dataset-id>.<table-prefix>*`
WHERE
  bool_expression
&lt;project-id&gt;
ID progetto Cloud Platform. (Facoltativo) Se utilizzi l'ID progetto predefinito.
&lt;dataset-id&gt;
ID set di dati BigQuery.
&lt;table-prefix&gt;
Una stringa comune a tutte le tabelle corrispondente al carattere jolly . Il prefisso della tabella è facoltativo. Se il prefisso della tabella viene omesso, le corrispondenze sono a tutte le tabelle nel set di dati.
* (carattere jolly)
Il carattere jolly "*" rappresenta uno o più caratteri di una 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 con corrispondenze:

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 tabella con funzione carattere jolly contiene il carattere speciale (*), che indica che è necessario racchiudere il nome della tabella con caratteri jolly tra caratteri dell'accento grave (`). 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 è correttamente racchiusa tra virgolette:

#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

Eseguire query sulle tabelle utilizzando tabelle con caratteri jolly

Le tabelle con caratteri jolly consentono di eseguire query su diverse 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 segnalata durante gli 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

Filtra le tabelle selezionate utilizzando _TABLE_SUFFIX

Per limitare una query in modo che esegua la scansione solo di un insieme specificato di tabelle, utilizza il metodo pseudocolonna _TABLE_SUFFIX in una clausola WHERE con una condizione che è un'espressione costante.

La pseudocolonna _TABLE_SUFFIX contiene i valori corrispondenti alla tabella carattere jolly. 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 I valori _TABLE_SUFFIX possono essere utilizzati in una clausola WHERE per filtrare in base a specifiche tabelle.

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, il che contribuisce a ridurre il costo di esecuzione delle tue query.

Tuttavia, i filtri basati su _TABLE_SUFFIX che includono condizioni senza costante non limitano il numero di tabelle analizzate in una tabella con funzione carattere jolly. Per Ad esempio, la seguente query non limita le tabelle analizzate per il carattere jolly tabella bigquery-public-data.noaa_gsod.gsod19* perché il filtro utilizza la classe 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%')

Come ulteriore esempio, la seguente query limita la scansione in base al primo condizioni di filtro, _TABLE_SUFFIX BETWEEN '40' and '60', perché è un'espressione costante. Tuttavia, la seguente query non limita la scansione in base al secondo filtro condizione, _TABLE_SUFFIX = (SELECT SUBSTR(MAX(table_name), LENGTH('gsod19') + 1) FROM bigquery-public-data.noaa_gsod.INFORMATION_SCHEMA.TABLES WHERE table_name LIKE 'gsod194%'), perché è un'espressione dinamica:

#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'

Questi esempi di query utilizzano la vista INFORMATION_SCHEMA.TABLES. Per ulteriori informazioni informazioni sulla tabella INFORMATION_SCHEMA, consulta Recupero della tabella utilizzando INFORMATION_SCHEMA.

Scansione di un intervallo di tabelle con _TABLE_SUFFIX

Per analizzare un intervallo di tabelle, utilizza la pseudocolonna _TABLE_SUFFIX insieme a la 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 analizza la 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 analizzare tutte le tabelle in un set di dati, puoi utilizzare un prefisso vuoto e la tabella carattere jolly, che indica che la pseudocolonna _TABLE_SUFFIX contiene nomi completi delle tabelle. Ad esempio, la seguente clausola FROM analizza tutte le tabelle in il set di dati GSOD:

FROM
  `bigquery-public-data.noaa_gsod.*`

Con un prefisso vuoto, la pseudocolonna _TABLE_SUFFIX contiene la tabella completa names. 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 in genere hanno un rendimento migliore. Per ulteriori 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 funzione carattere jolly, BigQuery deduce automaticamente lo schema per quella 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 in una tabella corrispondente non esiste una colonna dello schema dedotto: BigQuery restituisce i valori NULL per quella colonna nelle righe per la tabella in cui manca la colonna.

Se lo schema non è coerente tra le tabelle corrispondenti al carattere jolly della query, 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

  • I prefissi più lunghi di solito hanno un rendimento migliore di 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, poiché 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