Esempi di query GoogleSQL per Bigtable

Gli esempi in questa pagina mostrano pattern di query SQL per query Bigtable comuni e avanzate. Puoi eseguire query GoogleSQL in Editor 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 fa riferimento a un valore con timestamp per una determinata colonna e riga. Ogni versione è archiviata in una cella. Per ulteriori informazioni, consulta Modello di archiviazione di 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 di Bigtable. per vedere esempi di query simili che chiamano i dati Bigtable sull'API, consulta Leggi gli esempi e Utilizzare i filtri.

Recupera la versione più recente 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'

Recuperare l'ultima versione di una determinata colonna da una determinata colonna 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 la versione più recente di più colonne per un determinato intervallo di chiavi 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 di una famiglia di colonne per una 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 alto rendimento.

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

Recupera la versione più recente di tutte le colonne con chiavi di riga corrispondenti a un'espressione regolare specificata. Questa query richiede una scansione completa della tabella, quindi consigliata per i pattern di accesso a bassa latenza e velocità effettiva elevata, a meno che non fornisci un prefisso della chiave di riga o un predicato di chiavi di riga nella clausola WHERE.

  SELECT *
  FROM myTable(with_history => TRUE)
  WHERE REGEXP_CONTAINS(_key, '.*#20190501$')

Recupera la versione più recente di tutte le colonne con il prefisso della chiave di riga corrispondente e un valore del contatore superiore a 123. Non è necessario eseguire il casting per questo confronto, poiché gli aggregate di 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 di 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 composito 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 mappa, in cui ogni qualificatore e valore della colonna viene mappato come coppia chiave-valore. Questa query SQL è simile all'utilizzo di un filtro per il valore della riga nell' API Bigtable Data.

  SELECT _key, MAP_KEYS(cell_plan) AS keys
  FROM analytics
  WHERE _key >= 'phone#4c410523#20190501' AND _key < 'phone#4c410523#201906201'

Pattern di query SQL Bigtable avanzati

Gli esempi riportati di seguito dimostrano pattern più avanzati.

Con la seguente query, puoi recuperare la chiave di riga e il valore più recente di Attributo JSON abc nella famiglia di colonne session. Per ulteriori informazioni, 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 media durata della sessione utilizzando l'ultimo valore di due aggregati Bigtable celle , che sono numerici, 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 la versione più recente 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 scritte sotto forma di 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, si consiglia 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 singoli confronti, 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 la versione più recente 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 la versione più recente di tutte le colonne se Le coppie chiave-valore nella famiglia di colonne cell_plan includono entrambe data_plan:unlimited e 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 abbia 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)

In ordine di filtro temporale, latest_n è l'ultimo, quindi una query come after => X, before => y, latest_n => 3 restituisce i tre valori più recenti che soddisfano le condizioni "dopo" e "prima". Se il tuo caso d'uso richiede che latest_n puoi specificare latest_n come unico filtro temporale, e poi applicare gli altri filtri temporali utilizzando gli operatori di query 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-04 23:51:00+00'))
      AS street_address
  FROM locations(with_history => TRUE, latest_n => 3)

Come nell'esempio precedente, puoi applicare un filtro temporale diverso ogni famiglia di colonne nella query. Ad esempio, la seguente query restituisce le tre versioni più recenti della colonna street e le due meno recenti.

  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 indirizzo includono sia city:Savannah sia 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 trasferimento non è necessario, quindi può essere scritto anche nella forma più breve riportata di seguito.

  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.