Esempi di query GoogleSQL per Bigtable
Gli esempi in questa pagina mostrano i pattern di query SQL per le query Bigtable comuni e avanzate. Puoi eseguire query GoogleSQL nell'editor di query di Bigtable Studio. Puoi anche eseguire query utilizzando la libreria client Bigtable per Java.
Prima di leggere questa pagina, consulta la panoramica di GoogleSQL per Bigtable.
Gli esempi in questa pagina utilizzano ID e valori simili a quelli in Dati per gli esempi.
Pattern di query SQL Bigtable comuni
Di seguito sono riportati esempi di query comuni per i dati Bigtable. Per visualizzare esempi di query simili che chiamano l'API Bigtable Data, consulta Esempi di lettura e Utilizzo dei filtri. Per esempi di query sulle chiavi di riga strutturate, consulta Query chiave di riga strutturate.
Recupera l'ultima versione di tutte le colonne per una determinata chiave di riga.
SELECT * FROM myTable WHERE _key = 'r1'
Recupera tutte le versioni di tutte le colonne per una determinata chiave di riga.
SELECT * FROM myTable(with_history => TRUE) WHERE _key = 'r1'
Recupera l'ultima versione di una determinata colonna da una determinata famiglia di colonne per una determinata chiave di riga.
SELECT stats_summary['os_build'] AS os
FROM analytics
WHERE _key = 'phone#4c410523#20190501'
Recupera le chiavi di riga e l'ultima versione di più colonne per un determinato intervallo di chiave di riga.
SELECT
_key,
stats_summary['os_build'] AS os,
stats_summary['user_agent'] AS agent
FROM analytics
WHERE _key >= 'phone#4c410523#20190501' AND _key < 'phone#4c410523#201906201'
Recupera tutte le versioni di tutte le colonne per più intervalli di chiave di riga, fino a 10 righe.
SELECT *
FROM analytics(with_history => TRUE)
WHERE
(_key >= 'phone#4c410523#20190501' AND _key < 'phone#4c410523#201906201')
OR (_key >= 'phone#5c10102#20190501' AND _key < 'phone#5c10102#20190601')
LIMIT 10
Recupera tutte le versioni di tutte le colonne per più chiavi di riga.
SELECT *
FROM analytics(with_history => TRUE)
WHERE _key = 'phone#4c410523#20190501' OR _key = 'phone#4c410523#20190502'
Recupera tutte le versioni di tutte le colonne per più chiavi di riga utilizzando un approccio diverso.
SELECT *
FROM analytics(with_history => TRUE)
WHERE _key IS IN ('phone#4c410523#20190501', 'phone#4c410523#20190502')
Recupera l'ultima versione di tutte le colonne all'interno di una famiglia di colonne per un prefisso della chiave di riga.
SELECT stats_summary
FROM analytics
WHERE _key LIKE 'phone#%'
Recupera le chiavi di riga e le tre versioni più recenti di tutte le colonne all'interno di una famiglia di colonne per tutte le righe della tabella. Questa query richiede una scansione completa della tabella, pertanto non è consigliata per pattern di accesso a bassa latenza e ad alta velocità effettiva.
SELECT _key, cell_plan FROM analytics(with_history => TRUE, latest_n => 3)
Recupera l'ultima versione di tutte le colonne con chiavi di riga corrispondenti a un'espressione regolare specificata. Questa query richiede una scansione completa della tabella, pertanto non è consigliata per pattern di accesso a bassa latenza e ad alta velocità effettiva, a meno che tu non fornisca anche un predicato del prefisso della chiave di riga o dell'intervallo di chiavi di riga nella clausola WHERE
.
SELECT *
FROM myTable(with_history => TRUE)
WHERE REGEXP_CONTAINS(_key, '.*#20190501$')
Recupera l'ultima versione di tutte le colonne con il prefisso della chiave di riga corrispondente e
il valore del contatore superiore a 123
. Non è necessario trasmettere per questo confronto,
perché gli aggregati
Bigtable sono numerici.
SELECT *
FROM myTable
WHERE _key LIKE 'user12%' AND counterFamily['counter'] > 123
Recupera l'ultima versione di tutte le colonne per un prefisso della chiave di riga se il referrer corrisponde a un valore specifico.
SELECT *
FROM analytics
WHERE _key LIKE 'com.mysite%' AND session['referrer'] = './home'
Classifica una determinata riga in base al valore di una determinata colonna. Questa query è simile all'utilizzo di un filtro condizionale di composizione nell'API Bigtable Data.
SELECT
*,
CASE cell_plan['data_plan']
WHEN '10gb' THEN 'passed-filter'
ELSE 'filtered-out'
END
AS label
FROM analytics
Recupera la chiave di riga e i qualificatori di colonna in una famiglia di colonne specifica per un intervallo di chiavi di riga specificato. In SQL, le famiglie di colonne sono rappresentate dal tipo di dati map, in cui ogni qualificatore e valore di colonna viene mappato come coppia chiave-valore. Questa query SQL è simile all'utilizzo di un filtro rimuovi valore nell'API Bigtable Data.
SELECT _key, MAP_KEYS(cell_plan) AS keys
FROM analytics
WHERE _key >= 'phone#4c410523#20190501' AND _key < 'phone#4c410523#201906201'
La funzione UNPACK
consente di trasformare i dati Bigtable in un formato di serie temporale tabellare, utile per eseguire l'analisi delle serie temporali. Considera un esempio in cui hai una colonna clicks
in una famiglia di colonne engagement
. La seguente query utilizza UNPACK
per vedere il rendimento di determinate campagne aggregando i clic in un minuto dell'ultima ora.
SELECT
FORMAT_TIMESTAMP('%M', _timestamp) AS minute,
COUNT(clicks) AS total_clicks
FROM
UNPACK((
SELECT engagement['clicks'] as clicks
FROM metrics(with_history => true, after => TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 1 HOUR))
WHERE _key = @campaign_id
))
GROUP BY
minute;
Pattern di query SQL Bigtable avanzati
Gli esempi seguenti mostrano pattern più avanzati.
Con la seguente query, puoi recuperare la chiave di riga e il valore più recente dell'attributo JSON abc
nella famiglia di colonne session
. Per saperne di più, consulta le funzioni JSON
.
SELECT _key, JSON_VALUE(session['payload'], '$.abc') AS abc FROM analytics
Con la seguente query, puoi recuperare la chiave di riga e calcolare la durata media della sessione utilizzando l'ultimo valore di due celle aggregate Bigtable, che sono numeriche, per ogni riga della tabella.
SELECT
_key AS userid,
session['total_minutes'] / session['count'] AS avg_session_length
FROM analytics
Con la seguente query, puoi recuperare l'ultima versione di tutte le colonne per
un determinato prefisso della chiave di riga se la famiglia di colonne session
contiene referrer
,
origin
o server
come qualificatore di colonna. In alternativa, questa query può anche
essere scritta come una serie di confronti individuali, ad esempio session['referrer']
IS NOT NULL OR session['origin'] IS NOT NULL
. Tuttavia, per le query che comportano
un numero elevato di confronti, è consigliato il seguente approccio.
SELECT *
FROM analytics
WHERE
_key LIKE 'com.abc%'
AND ARRAY_INCLUDES_ANY(MAP_KEYS(session), ['referrer', 'origin', 'server'])
Con la seguente query puoi recuperare l'ultima versione di tutte le colonne per
un determinato prefisso della chiave di riga se la famiglia di colonne session
contienereferrer
,
origin
e server
come qualificatori di colonna. In alternativa, questa query può essere
scritta come una serie di confronti individuali, ad esempio session['referrer'] IS
NOT NULL AND session ['origin'] IS NOT NULL
.
SELECT *
FROM analytics
WHERE
_key LIKE 'com.abc%'
AND ARRAY_INCLUDES_ALL(MAP_KEYS(session), ['referrer', 'origin', 'server'])
Con la seguente query, puoi recuperare l'ultima versione di tutte le colonne per
un determinato prefisso della chiave di riga se la famiglia di colonne session
contiene
com.google.search
, com.google.maps
o com.google.shopping
come valori.
SELECT *
FROM analytics
WHERE
_key LIKE 'com.abc%'
AND ARRAY_INCLUDES_ANY(
MAP_VALUES(session),
['com.google.search', 'com.google.maps', 'com.google.shopping'])
Con la seguente query, puoi recuperare l'ultima versione di tutte le colonne se
le coppie chiave-valore nella famiglia di colonne cell_plan
includono sia
data_plan:unlimited
che roaming:North America
.
SELECT *
FROM analytics
WHERE
ARRAY_INCLUDES_ALL(
CAST(
MAP_ENTRIES(cell_plan)
AS ARRAY<STRUCT<key STRING, value STRING>>),
[('data_plan', 'unlimited'), ('roaming', 'North America')])
Con la seguente query puoi recuperare le letture row key
e temperature
per i sensori meteo nei casi in cui la temperatura ha superato i 70 gradi
durante le ultime sette misurazioni.
SELECT
_key AS sensorid,
ARRAY_FILTER(
CAST(
sensor['temperature']
AS ARRAY<STRUCT<timestamp TIMESTAMP, value STRING>>),
e -> CAST(e.value AS FLOAT32) > 70) AS high_temperature
FROM weather(with_history => TRUE, latest_n => 7)
Nell'ordine di applicazione dei filtri temporali, latest_n
viene applicato per ultimo, quindi una query come after => X,
before => y, latest_n => 3
restituisce gli ultimi tre valori che soddisfano le condizioni
after e before. Se il tuo caso d'uso richiede che latest_n
abbia la precedenza, puoi fornire latest_n
come unico filtro temporale e poi applicare il resto dei filtri temporali utilizzando gli operatori di query nell'istruzione SELECT
, come mostrato nell'esempio. Per ulteriori informazioni, consulta Filtri
temporali.
SELECT
ARRAY_FILTER(
CAST(
address['street']
AS ARRAY<STRUCT<timestamp TIMESTAMP, value STRING>>),
e -> e.timestamp > TIMESTAMP('2021-01-04T23:51:00.000Z'))
AS street_address
FROM locations(with_history => TRUE, latest_n => 3)
Analogamente all'esempio precedente, puoi applicare un filtro temporale diverso a
ogni famiglia di colonne nella query. Ad esempio, la seguente query restituisce le
tre versioni più recenti della colonna street
e le due versioni meno recenti della colonna state
.
SELECT
ARRAY_FILTER(
CAST(
address['street']
AS ARRAY<STRUCT<timestamp TIMESTAMP, value STRING>>),
(e, i) -> i <= 2)
AS street_address,
ARRAY_FILTER(
ARRAY_REVERSE(
CAST(
address['state']
AS ARRAY<STRUCT<timestamp TIMESTAMP, value STRING>>)),
(e, i) -> i <= 1)
AS state
FROM locations(with_history => TRUE)
Con la seguente query, puoi recuperare tutte le versioni di tutte le colonne se
le coppie chiave-valore nella famiglia di colonne dell'indirizzo includono city:Savannah
o
city:Nashville
in qualsiasi momento.
SELECT *
FROM locations(with_history => TRUE)
WHERE
ARRAY_LENGTH(
ARRAY_FILTER(
CAST(
MAP_ENTRIES(address)
AS ARRAY<
STRUCT<
key STRING,
value ARRAY<STRUCT<timestamp TIMESTAMP, value STRING>>>>),
e ->
e.key = 'city'
AND ARRAY_INCLUDES_ANY(
ARRAY_TRANSFORM(e.value, k -> k.value), ['Savannah', 'Nashville'])))
> 0
In questo esempio specifico, il casting non è necessario, quindi può essere scritto anche nella seguente forma più breve.
SELECT *
FROM locations(with_history => TRUE)
WHERE
ARRAY_LENGTH(
ARRAY_FILTER(
MAP_ENTRIES(address),
e ->
e.key = 'city'
AND ARRAY_INCLUDES_ANY(
ARRAY_TRANSFORM(e.value, k -> k.value), ['Savannah', 'Nashville'])))
> 0
Passaggi successivi
- Sfoglia la documentazione di riferimento di GoogleSQL per Bigtable.
- Utilizza il connettore Bigtable Spark.
- Gestisci chiave di riga di riga.