Panoramica di GoogleSQL per Bigtable

Puoi utilizzare le istruzioni GoogleSQL per eseguire query sui dati Bigtable. GoogleSQL è un linguaggio di query strutturato (SQL) conforme ad ANSI, implementato anche per altri servizi come BigQuery e Spanner. Google Cloud

Questo documento fornisce una panoramica di GoogleSQL per Bigtable. Fornisce esempi di query SQL che puoi utilizzare con Bigtable e descrive la loro relazione con uno schema di tabella Bigtable. Prima di leggere questo documento, devi avere familiarità con il modello di archiviazione Bigtable e con i concetti di progettazione dello schema.

Puoi creare ed eseguire query in Bigtable Studio nella console Google Cloud oppure puoi eseguirle a livello di programmazione utilizzando la libreria client Bigtable per Java, Python o Go. Per saperne di più, consulta Utilizzare SQL con una libreria client Bigtable.

Le query SQL vengono gestite dai nodi del cluster allo stesso modo delle richieste di dati NoSQL. Pertanto, si applicano le stesse best practice quando crei query SQL da eseguire sui dati Bigtable, ad esempio evitando scansioni complete delle tabelle o filtri complessi. Per ulteriori informazioni, vedi Letture e rendimento.

Non puoi utilizzare Data Boost con GoogleSQL per Bigtable.

Casi d'uso

GoogleSQL per Bigtable è ottimale per lo sviluppo di applicazioni a bassa latenza. Inoltre, l'esecuzione di query SQL nella consoleGoogle Cloud può essere utile per ottenere rapidamente una rappresentazione visiva dello schema di una tabella, verificare che determinati dati siano stati scritti o eseguire il debug di possibili problemi relativi ai dati.

La versione attuale di GoogleSQL per Bigtable non supporta alcuni costrutti SQL comuni, inclusi, a titolo esemplificativo, i seguenti:

• Istruzioni DML (Data Manipulation Language) diverse da SELECT, ad esempio INSERT, UPDATE o DELETE
• Istruzioni DDL (Data Definition Language) come CREATE, ALTER o DROP
• Istruzioni per il controllo dell'accesso ai dati
• Sintassi delle query per le sottoquery, JOIN, UNION, UNNEST e CTEs

Per ulteriori informazioni, tra cui funzioni, operatori, tipi di dati e sintassi delle query supportati, consulta la documentazione di riferimento di GoogleSQL per Bigtable.

Visualizzazioni

Puoi utilizzare GoogleSQL per Bigtable per creare le seguenti risorse:

• Vista materializzata continua: un risultato precalcolato di una query SQL in esecuzione continua, inclusi dati aggregati, che si sincronizza con la tabella di origine con aggiornamenti incrementali. Questa funzionalità è in anteprima.
• Vista logica: una query salvata e denominata su cui è possibile eseguire query come su una tabella.

Per confrontare questi tipi di visualizzazioni, nonché le visualizzazioni autorizzate, consulta Tabelle e visualizzazioni.

Concetti fondamentali

Questa sezione illustra i concetti chiave da tenere presente quando utilizzi GoogleSQL per eseguire query sui dati Bigtable.

Famiglie di colonne nelle risposte SQL

In Bigtable, una tabella contiene una o più famiglie di colonne, che vengono utilizzate per raggruppare le colonne. Quando esegui una query su una tabella Bigtable con GoogleSQL, lo schema della tabella è costituito da quanto segue:

• Una colonna speciale denominata _key che corrisponde alle chiavi di riga nella tabella interrogata
• Una singola colonna per ogni famiglia di colonne Bigtable nella tabella, che contiene i dati della famiglia di colonne in quella riga

Tipo di dati mappa

GoogleSQL per Bigtable include il tipo di dati MAP<key, value>, progettato specificamente per ospitare le famiglie di colonne.

Per impostazione predefinita, ogni riga di una colonna della mappa contiene coppie chiave-valore, dove una chiave è il qualificatore di colonna Bigtable nella tabella sottoposta a query e il valore è il valore più recente per quella colonna.

Di seguito è riportato un esempio di query SQL che restituisce una tabella con il valore della chiave di riga e il valore più recente del qualificatore da una mappa denominata columnFamily.

  SELECT _key, columnFamily['qualifier'] FROM myTable

Se lo schema Bigtable prevede l'archiviazione di più celle o versioni dei dati nelle colonne, puoi aggiungere un filtro temporale, ad esempio with_history, all'istruzione SQL.

In questo caso, le mappe che rappresentano le famiglie di colonne sono nidificate e restituite come array. Nell'array, ogni valore è a sua volta una mappa costituita da un timestamp come chiave e dai dati della cella come valore. Il formato è MAP<key, ARRAY<STRUCT<timestamp, value>>>.

L'esempio seguente restituisce tutte le celle della famiglia di colonne "info" per una singola riga.

  SELECT _key, info FROM users(with_history => TRUE) WHERE _key = 'user_123';

La mappa restituita è simile alla seguente. Nella tabella sottoposta a query, info è la famiglia di colonne, user_123 è la chiave di riga e city e state sono i qualificatori di colonna. Ogni coppia timestamp-valore (STRUCT) in un array rappresenta le celle di quelle colonne nella riga e sono ordinate in ordine decrescente in base al timestamp.

/*----------+------------------------------------------------------------------+
 |   _key   |                              info                                |
 +----------+------------------------------------------------------------------+
 | user_123 | {"city":{<t5>:"Brooklyn", <t0>:"New York"}, "state":{<t0>:"NY"}} |
 +----------+------------------------------------------------------------------*/

Tabelle sparse

Una funzionalità chiave di Bigtable è il suo modello dei dati flessibile. In una tabella Bigtable, se una colonna non viene utilizzata in una riga, non vengono memorizzati dati per la colonna. Una riga potrebbe avere una colonna e la riga successiva potrebbe averne 100. Al contrario, in una tabella di database relazionale, tutte le righe contengono tutte le colonne e un valore NULL viene in genere memorizzato nella colonna di una riga che non contiene dati per quella colonna.

Quando esegui una query su una tabella Bigtable con GoogleSQL, tuttavia, una colonna inutilizzata viene rappresentata con una mappa vuota e viene restituita come valore NULL. Questi valori NULL possono essere utilizzati come predicati di query. Ad esempio, un predicato come WHERE family['column1'] IS NOT NULL può essere utilizzato per restituire una riga solo se column1 viene utilizzato nella riga.

Byte

Quando fornisci una stringa, GoogleSQL esegue il cast implicito dai valori STRING ai valori BYTES per impostazione predefinita. Ciò significa, ad esempio, che puoi fornire la stringa 'qualifier' anziché la sequenza di byte b'qualifier'.

Poiché Bigtable tratta tutti i dati come byte per impostazione predefinita, la maggior parte delle colonne Bigtable non contiene informazioni sul tipo. Tuttavia, con GoogleSQL puoi definire uno schema al momento della lettura con la funzione CAST. Per ulteriori informazioni sul casting, consulta Funzioni di conversione.

Filtri temporali

La tabella seguente elenca gli argomenti che puoi utilizzare per accedere agli elementi temporali di una tabella. Gli argomenti sono elencati nell'ordine in cui vengono filtrati. Ad esempio, with_history viene applicato prima di latest_n. Devi fornire un timestamp valido.

Argomento	Descrizione
as_of	Timestamp. Restituisce gli ultimi valori con timestamp minori o uguali al timestamp fornito.
with_history	Boolean. Controlla se restituire l'ultimo valore come valore scalare o con timestamp come STRUCT.
after_or_equal	Timestamp. Valori con timestamp successivi all'input, inclusi. Richiede with_history => TRUE
before	Timestamp. Valori con timestamp precedenti all'input, esclusi. Richiede with_history => TRUE
latest_n	Numero intero. Il numero di valori con timestamp da restituire per qualificatore di colonna (chiave della mappa). Deve essere maggiore o uguale a 1. Richiede with_history => TRUE.

Per altri esempi, vedi Pattern di query avanzate.

Query di base

Questa sezione descrive e mostra esempi di query SQL Bigtable di base e il loro funzionamento. Per altre query di esempio, vedi Esempi di pattern di query GoogleSQL per Bigtable.

Recuperare l'ultima versione

Anche se Bigtable consente di archiviare più versioni dei dati in ogni colonna, GoogleSQL per Bigtable restituisce per impostazione predefinita l'ultima versione, ovvero la cella più recente, dei dati per ogni riga.

Considera il seguente set di dati di esempio, che mostra che user1 si è trasferito due volte nello stato di New York e una volta nella città di Brooklyn. In questo esempio, address è la famiglia di colonne e i qualificatori di colonna sono street, city e state. Le celle di una colonna sono separate da righe vuote.

		address
_key	strada	city	state
utente1	2023/01/10-14:10:01.000: '113 Xyz Street' 2021/12/20-09:44:31.010: '76 Xyz Street' 2005/03/01-11:12:15.112: '123 Abc Street'	2021/12/20-09:44:31.010: 'Brooklyn' 2005/03/01-11:12:15.112: 'Queens'	2005/03/01-11:12:15.112: 'NY'

Per recuperare la versione più recente di ogni colonna per user1, puoi utilizzare un'istruzione SELECT come la seguente.

   SELECT street, city FROM myTable WHERE _key = 'user1'

La risposta contiene l'indirizzo attuale, che è una combinazione dei valori più recenti di via, città e stato (scritti in momenti diversi) stampati in formato JSON. I timestamp non sono inclusi nella risposta.

_key	address
utente1	{street:'113 Xyz Street', city:'Brooklyn', state: :'NY'}

Recupera tutte le versioni

Per recuperare le versioni precedenti (celle) dei dati, utilizza il flag with_history. Puoi anche assegnare alias a colonne ed espressioni, come illustrato nell'esempio seguente.

  SELECT _key, columnFamily['qualifier'] AS col1
  FROM myTable(with_history => TRUE)

Per comprendere meglio gli eventi che hanno portato allo stato attuale di una riga, puoi recuperare i timestamp per ogni valore recuperando la cronologia completa. Ad esempio, per capire quando user1 si è trasferito al suo indirizzo attuale e da dove si è trasferito, puoi eseguire la seguente query:

  SELECT
    address['street'][0].value AS moved_to,
    address['street'][1].value AS moved_from,
    FORMAT_TIMESTAMP('%Y-%m-%d', address['street'][0].timestamp) AS moved_on,
  FROM myTable(with_history => TRUE)
  WHERE _key = 'user1'

Quando utilizzi il flag with_history nella query SQL, la risposta viene restituita come MAP<key, ARRAY<STRUCT<timestamp, value>>>. Ogni elemento dell'array è un valore con timestamp per la riga, la famiglia di colonne e la colonna specificate. I timestamp sono ordinati in ordine cronologico inverso, quindi i dati più recenti sono sempre il primo elemento restituito.

Nota: i criteri di Garbage Collection controllano il tempo di scadenza o il numero di versioni conservate per una famiglia di colonne. Per maggiori informazioni, consulta la panoramica della garbage collection.

La risposta alla query è la seguente.

moved_to			moved_from	moved_on
Via Xyz 113			76 Xyz Street	10/01/2023

Puoi anche recuperare il numero di versioni in ogni riga utilizzando le funzioni di array, come mostrato nella seguente query:

  SELECT _key, ARRAY_LENGTH(MAP_ENTRIES(address)) AS version_count
  FROM myTable(with_history => TRUE)

Recuperare i dati da un momento specifico

L'utilizzo di un filtro as_of consente di recuperare lo stato di una riga in un determinato momento. Ad esempio, se vuoi conoscere l'indirizzo di user al 10 gennaio 2022 alle 13:14, puoi eseguire la seguente query.

  SELECT address
  FROM myTable(as_of => TIMESTAMP('2022-01-10T13:14:00.234Z'))
  WHERE _key = 'user1'

Il risultato mostra quello che sarebbe stato l'ultimo indirizzo noto il 10 gennaio 2022 alle 13:14, ovvero la combinazione di via e città dell'aggiornamento del 20/12/2021 alle 09:44:31.010 e lo stato del 01/03/2005 alle 11:12:15.112.

address
{street:'76 Xyz Street', city:'Brooklyn', state: :'NY'}

Lo stesso risultato può essere ottenuto anche utilizzando i timestamp Unix.

  SELECT address
  FROM myTable(as_of => TIMESTAMP_FROM_UNIX_MILLIS(1641820440000))
  WHERE _key = 'user1'

Considera il seguente set di dati, che mostra lo stato di attivazione o disattivazione dei rilevatori di fumo e monossido di carbonio. La famiglia di colonne è alarmType e i qualificatori di colonna sono smoke e carbonMonoxide. Le celle di ogni colonna sono separate da righe vuote.

	alarmType
_key	fumo	carbonMonoxide
building1#section1	2023/04/01-09:10:15.000: 'off' 2023/04/01-08:41:40.000: 'on' 2020/07/03-06:25:31.000: 'off' 2020/07/03-06:02:04.000: 'on'	2023/04/01-09:22:08.000: 'off' 2023/04/01-08:53:12.000: 'on'
building1#section2	2021/03/11-07:15:04.000: 'off' 2021/03/11-07:00:25.000: 'on'

Puoi trovare le sezioni di building1 in cui un rilevatore di fumo era attivo alle 9:00 del 1° aprile 2023 e lo stato del rilevatore di monossido di carbonio in quel momento utilizzando la seguente query.

  SELECT _key AS location, alarmType['carbonMonoxide'] AS CO_sensor
  FROM alarms(as_of => TIMESTAMP('2023-04-01T09:00:00.000Z'))
  WHERE _key LIKE 'building1%' and alarmType['smoke'] = 'on'

Il risultato è il seguente:

posizione	CO_sensor
building1#section1	'on'

Esegui query su dati di serie temporali

Un caso d'uso comune di Bigtable è l'archiviazione di dati delle serie temporali. Considera il seguente set di dati di esempio, che mostra le letture di temperatura e umidità per i sensori meteo. L'ID famiglia di colonne è metrics e i qualificatori di colonna sono temperature e humidity. Le celle di una colonna sono separate da righe vuote e ogni cella rappresenta una lettura del sensore con timestamp.

	metriche
_key	temperatura	umidità
sensorA#20230105	2023/01/05-02:00:00.000: 54 2023/01/05-01:00:00.000: 56 2023/01/05-00:00:00.000: 55	2023/01/05-02:00:00.000: 0.89 2023/01/05-01:00:00.000: 0.9 2023/01/05-00:00:00.000: 0.91
sensorA#20230104	2023/01/04-23:00:00.000: 56 2023/01/04-22:00:00.000: 57	2023/01/04-23:00:00.000: 0.9 2023/01/04-22:00:00.000: 0.91

Puoi recuperare un intervallo specifico di valori di timestamp utilizzando i filtri temporali after, before o after_or_equal. L'esempio riportato di seguito utilizza after:

   SELECT metrics['temperature'] AS temp_versioned
   FROM
   sensorReadings(with_history => true, after => TIMESTAMP('2023-01-04T23:00:00.000Z'),
         before => TIMESTAMP('2023-01-05T01:00:00.000Z'))
   WHERE _key LIKE 'sensorA%'

La query restituisce i dati in questo formato:

temp_versioned
[{timestamp: '2023/01/05-01:00:00.000', value:56} {timestamp: '2023/01/05-00:00:00.000', value: 55}]
[{timestamp: '2023/01/04-23:00:00.000', value:56}]

Dati di serie temporali UNPACK

Quando analizzi i dati delle serie temporali, spesso è preferibile lavorare con i dati in formato tabellare. La funzione UNPACK di Bigtable può aiutarti.

UNPACK è una funzione con valori di tabella (TVF) Bigtable che restituisce un'intera tabella di output anziché un singolo valore scalare e viene visualizzata nella clausola FROM come una sottoquery della tabella. La TVF UNPACK espande ogni valore con timestamp in più righe, una per ogni timestamp, e sposta il timestamp nella colonna _timestamp.

L'input di UNPACK è una sottoquery in cui with_history => true.

L'output è una tabella espansa con una colonna _timestamp in ogni riga.

Una famiglia di colonne di input MAP<key, ARRAY<STRUCT<timestamp, value>>> viene decomposta in MAP<key, value> e un qualificatore di colonna ARRAY<STRUCT<timestamp, value>>> viene decomposto in value. Gli altri tipi di colonne di input rimangono invariati. Le colonne devono essere selezionate nella sottoquery per essere decompresse e selezionate. Non è necessario selezionare la nuova colonna _timestamp per decomprimere i timestamp.

Ampliando l'esempio di serie temporale in Eseguire query sui dati delle serie temporali e utilizzando la query in quella sezione come input, la query UNPACK viene formattata nel seguente modo:

  SELECT temp_versioned, _timestamp
  FROM
  UNPACK((
    SELECT metrics['temperature'] AS temperature_versioned
    FROM
    sensorReadings(with_history => true, after => TIMESTAMP('2023-01-04T23:00:00.000Z'),
          before => TIMESTAMP('2023-01-05T01:00:00.000Z'))
    WHERE _key LIKE 'sensorA%'
  ));

La query restituisce i dati in questo formato:

temp_versioned	_timestamp
55	1672898400
55	1672894800
56	1672891200

Query JSON

Le funzioni JSON consentono di manipolare i dati JSON archiviati come valori Bigtable per i carichi di lavoro operativi.

Ad esempio, puoi recuperare il valore dell'elemento JSON abc dall'ultima cella della famiglia di colonne session insieme alla chiave di riga utilizzando la seguente query.

  SELECT _key, JSON_VALUE(session['payload'],'$.abc') AS abc FROM analytics

Caratteri speciali di escape e parole riservate

Bigtable offre un'elevata flessibilità nella denominazione di tabelle e colonne. Di conseguenza, nelle query SQL, i nomi delle tabelle potrebbero dover essere preceduti dal carattere di escape a causa di caratteri speciali o parole riservate.

Ad esempio, la seguente query non è SQL valido a causa del punto nel nome della tabella.

  -- ERROR: Table name format not supported

  SELECT * FROM my.table WHERE _key = 'r1'

Tuttavia, puoi risolvere il problema racchiudendo gli elementi tra caratteri di apice inverso (`).

  SELECT * FROM `my.table` WHERE _key = 'r1'

Se una parola chiave riservata SQL viene utilizzata come identificatore, può essere sottoposta a escape in modo simile.

  SELECT * FROM `select` WHERE _key = 'r1'

Utilizzare SQL con una libreria client Bigtable

Le librerie client Bigtable per Java, Python e Go supportano l'esecuzione di query sui dati con SQL utilizzando l'API executeQuery. Gli esempi riportati di seguito mostrano come eseguire una query e accedere ai dati:

Vai

Per utilizzare questa funzionalità, devi utilizzare cloud.google.com/go/bigtable versione 1.36.0 o successive. Per saperne di più sull'utilizzo, consulta la documentazione relativa a PrepareStatement, Bind, Execute e ResultRow.

  import (
    "cloud.google.com/go/bigtable"
  )

  func query(client *bigtable.Client) {
    // Prepare once for queries that will be run multiple times, and reuse
    // the PreparedStatement for each request. Use query parameters to
    // construct PreparedStatements that can be reused.
    ps, err := client.PrepareStatement(
      "SELECT cf1['bytesCol'] AS bytesCol, CAST(cf2['stringCol'] AS STRING) AS stringCol, cf3 FROM myTable WHERE _key=@keyParam",
      map[string]SQLType{
        "keyParam": BytesSQLType{},
      }
    )
    if err != nil {
      log.Fatalf("Failed to create PreparedStatement: %v", err)
    }

    // For each request, create a BoundStatement with your query parameters set.
    bs, err := ps.Bind(map[string]any{
      "keyParam": []byte("mykey")
    })
    if err != nil {
      log.Fatalf("Failed to bind parameters: %v", err)
    }

    err = bs.Execute(ctx, func(rr ResultRow) bool {
      var byteValue []byte
      err := rr.GetByName("bytesCol", &byteValue)
      if err != nil {
        log.Fatalf("Failed to access bytesCol: %v", err)
      }
      var stringValue string
      err = rr.GetByName("stringCol", &stringValue)
      if err != nil {
        log.Fatalf("Failed to access stringCol: %v", err)
      }
      // Note that column family maps have byte valued keys. Go maps don't support
      // byte[] keys, so the map will have Base64 encoded string keys.
      var cf3 map[string][]byte
      err = rr.GetByName("cf3", &cf3)
      if err != nil {
        log.Fatalf("Failed to access cf3: %v", err)
      }
      // Do something with the data
      // ...
      return true
    })
  }

Java

Per utilizzare questa funzionalità, devi utilizzare java-bigtable versione 2.57.3 o successive. Per maggiori informazioni sull'utilizzo, consulta prepareStatement, executeQuery, BoundStatement, e ResultSet in Javadoc.

  static void query(BigtableDataClient client) {
    // Prepare once for queries that will be run multiple times, and reuse
    // the PreparedStatement for each request. Use query parameters to
    // construct PreparedStatements that can be reused.
    PreparedStatement preparedStatement = client.prepareStatement(
      "SELECT cf1['bytesCol'] AS bytesCol, CAST(cf2['stringCol'] AS STRING) AS stringCol, cf3 FROM myTable WHERE _key=@keyParam",
      // For queries with parameters, set the parameter names and types here.
      Map.of("keyParam", SqlType.bytes())
    );

    // For each request, create a BoundStatement with your query parameters set.
    BoundStatement boundStatement = preparedStatement.bind()
      .setBytesParam("keyParam", ByteString.copyFromUtf8("mykey"))
      .build();

    try (ResultSet resultSet = client.executeQuery(boundStatement)) {
      while (resultSet.next()) {
        ByteString byteValue = resultSet.getBytes("bytesCol");
        String stringValue = resultSet.getString("stringCol");
        Map<ByteString, ByteString> cf3Value =
            resultSet.getMap("cf3", SqlType.mapOf(SqlType.bytes(), SqlType.bytes()));
        // Do something with the data.
      }
    }
  }

Python asyncio

Per utilizzare questa funzionalità, devi utilizzare python-bigtable versione 2.30.1 o successive.

  from google.cloud.bigtable.data import BigtableDataClientAsync

  async def execute_query(project_id, instance_id, table_id):
      async with BigtableDataClientAsync(project=project_id) as client:
          query = (
            "SELECT cf1['bytesCol'] AS bytesCol, CAST(cf2['stringCol'] AS STRING) AS stringCol,"
            " cf3 FROM {table_id} WHERE _key='mykey'"
          )
          async for row in await client.execute_query(query, instance_id):
            print(row["_key"], row["bytesCol"], row["stringCol"], row["cf3"])

Utilizzo SELECT *

Le query SELECT * possono riscontrare errori temporanei quando una famiglia di colonne viene aggiunta o eliminata dalla tabella sottoposta a query. Per questo motivo, per i carichi di lavoro di produzione, ti consigliamo di specificare tutti gli ID famiglia di colonne nella query, anziché utilizzare SELECT *. Ad esempio, utilizza SELECT cf1, cf2, cf3 invece di SELECT *.

Passaggi successivi

• Esplora la documentazione di riferimento di GoogleSQL per Bigtable.
• Scopri di più su Bigtable Studio.