Esempi di query GoogleSQL per Bigtable

Nota: questa pagina include funzionalità disponibili solo nella versione Enterprise Plus, come Data Boost per SQL. Per saperne di più, consulta Panoramica delle versioni.

Gli esempi in questa pagina mostrano pattern di query SQL per 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.

Nota: una versione si riferisce a un valore con timestamp per una determinata colonna e riga. Ogni versione viene archiviata in una cella. Per saperne di più, consulta Modello di archiviazione 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 alcuni esempi di query comuni per i dati Bigtable. Per vedere esempi di query simili che chiamano l'API Bigtable Data, consulta Esempi di lettura e Utilizzare i 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 con throughput elevato.

  SELECT _key, cell_plan FROM analytics(with_history => TRUE, latest_n => 3)

Recupera l'ultima versione di tutte le colonne con chiavi di riga che corrispondono a un'espressione regolare specificata. Questa query richiede una scansione completa della tabella, pertanto non è consigliata per pattern di accesso a bassa latenza e con throughput elevato, a meno che non fornisci anche un prefisso della chiave di riga o un predicato 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 maggiore di 123. Non è necessario eseguire il cast 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 in the 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 della mappa, in cui ogni qualificatore di colonna e valore viene mappato come coppia chiave-valore. Questa query SQL è simile all'utilizzo di un filtro di valori di rimozione 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 temporali tabulare, utile per eseguire l'analisi delle serie temporali. Prendi in considerazione 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 JSON funzioni.

  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ò essere scritta anche come una serie di confronti individuali, ad esempio session['referrer'] IS NOT NULL OR session['origin'] IS NOT NULL. Tuttavia, per le query che coinvolgono 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 contiene referrer, 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 sia 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 la row key e le letture della temperature per i sensori meteorologici 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 filtraggio temporale, latest_n viene 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 saperne di più, 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 address 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 cast non è obbligatorio, 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

Analisi SQL con throughput elevato con Data Boost

Nella versione Enterprise Plus, per i workload analitici con throughput elevato, puoi utilizzare Data Boost per eseguire query che analizzano grandi quantità di dati senza influire sul rendimento dei cluster di produzione. Queste query sono ottimali per l'analisi in tempo reale e per la generazione di insight da dati storici o di serie temporali. Per eseguire queste query, devi utilizzare un profilo dell'applicazione configurato per Data Boost.

Gli esempi seguenti mostrano pattern SQL per l'analisi con throughput elevato utilizzando il serverless computing.

Analizzare i dati storici delle serie temporali

Con Data Boost, puoi recuperare le chiavi di riga e tutte le versioni delle metriche per tutte le righe di una tabella delle ultime 24 ore. La seguente query esegue una scansione completa della tabella, gestita da un serverless computing isolato.

SELECT _key, metrics
FROM myTable(with_history => TRUE, after => TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 24 HOUR))

Aggregare set di dati di grandi dimensioni

La seguente query mostra come calcolare il numero totale di eventi per ogni dispositivo in un intervallo di tempo specifico nell'intera tabella.

SELECT
  _key AS device_id,
  COUNT(events['event_type']) AS total_events
FROM
  UNPACK((
    SELECT events['event_type']
    FROM sensor_data(with_history => TRUE, after => TIMESTAMP('2026-01-01T00:00:00Z'))
  ))
GROUP BY
  Device_id;

Scansioni di intervalli di grandi dimensioni

La seguente query mostra come recuperare tutte le colonne per un intervallo di chiavi di riga di grandi dimensioni. L'utilizzo di Data Boost garantisce che questa richiesta con throughput elevato non consumi risorse CPU sui nodi del cluster.

SELECT *
FROM analytics
WHERE _key >= 'user#000000' AND _key < 'user#999999'

Passaggi successivi

• Sfoglia la documentazione di riferimento di GoogleSQL per Bigtable.
• Utilizza il connettore Bigtable Spark.
• Gestisci chiave di riga di riga.