Panoramica di GoogleSQL per Bigtable

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

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 conoscere il modello di archiviazione di Bigtable e i concetti di progettazione dello schema.

Puoi creare ed eseguire query in Bigtable Studio nella console Google Cloud oppure eseguirle in modo programmatico utilizzando la libreria client Bigtable per Java. Per saperne di più, consulta Utilizzare SQL con una libreria client Bigtable.

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

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 console Google Cloud può essere utile per ottenere rapidamente una rappresentazione visiva dello schema di una tabella, verificare la scrittura di determinati dati o eseguire il debug di possibili problemi relativi ai dati.

La versione corrente 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
• Dichiarazioni di controllo dell'accesso ai dati
• Sintassi delle query per sottoquery, JOIN, UNION, GROUP BY, 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.

Concetti fondamentali

Questa sezione illustra i concetti chiave da tenere presente quando utilizzi GoogleSQL per eseguire query sui dati di 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 su cui è stata eseguita la query
• Una singola colonna per ogni famiglia di colonne Bigtable nella tabella, che contiene i dati della famiglia di colonne nella riga

Tipo di dati mappa

GoogleSQL per Bigtable include il tipo di dato MAP<key, value>, che è progettato specificamente per supportare le famiglie di colonne.

Per impostazione predefinita, ogni riga di una colonna mappa contiene coppie chiave-valore, in cui 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 di 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 chiave è a sua volta una mappa composta 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 ha il seguente aspetto. 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 di timestamp e 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 è utilizzata in una riga, non vengono memorizzati dati per la colonna. Una riga potrebbe avere una colonna e la riga successiva 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.

Tuttavia, quando esegui una query su una tabella Bigtable con GoogleSQL, 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 nella riga viene utilizzato column1.

Byte

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

Poiché Bigtable tratta per impostazione predefinita tutti i dati come byte, 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 sulla trasmissione, consulta la sezione 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.

Per altri esempi, consulta la sezione Pattern di query avanzate.

Query di base

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

Recuperare la versione più recente

Sebbene Bigtable ti consenta di archiviare più versioni di dati in ogni colonna, per impostazione predefinita GoogleSQL per Bigtable restituisce la versione più recente (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 all'interno della città di Brooklyn. In questo esempio, address è la famiglia di colonne e i relativi qualificatori sono street, city e state. Le celle di una colonna sono separate da righe vuote.

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

   SELECT * FROM myTable WHERE _key = 'user1'

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

Recupera tutte le versioni

Per recuperare le versioni precedenti (celle) dei dati, utilizza il flag with_history. Puoi anche assegnare un 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 corrente di una riga, puoi recuperare i timestamp per ogni valore recuperando la cronologia completa. Ad esempio, per capire quando user1 si è trasferito all'indirizzo attuale e da dove proveniva, 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, pertanto i dati più recenti sono sempre il primo elemento restituito.

La risposta alla query è la seguente.

Puoi anche recuperare il numero di versioni in ogni riga utilizzando le funzioni 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 specificato

L'utilizzo di un filtro as_of ti consente di recuperare lo stato di una riga così com'era 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/10-13:14:00'))
  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.

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/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.

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 al momento utilizzando la seguente query.

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

Il risultato è il seguente:

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.

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

   SELECT metrics['temperature'] AS temp_versioned
   FROM
   sensorReadings(after => TIMESTAMP('2023/01/04-23:00:00'),
         before => TIMESTAMP('2023/01/05-01:00:00'))
   WHERE _key LIKE 'sensorA%'

La query restituisce i dati in questo formato:

Query JSON

Le funzioni JSON ti 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 dalla 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 potrebbe essere necessario applicare il codice di escape ai nomi delle tabelle a causa di caratteri speciali o parole riservate.

Ad esempio, la seguente query non è SQL valida 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 virgolette singole (`)

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

Se una parola chiave riservata di SQL viene utilizzata come identificatore, può essere inserita in modo simile.

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

Utilizzare SQL con una libreria client Bigtable

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

  static void query(BigtableDataClient client) {
    try (ResultSet resultSet =
        client.executeQuery(
            Statement.of(
                "SELECT cf1['bytesCol'] AS bytesCol, CAST(cf2['stringCol'] AS STRING) AS stringCol, cf3 FROM myTable WHERE _key='mykey'"))) {
      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
      }
    }
  }

  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"])

Passaggi successivi

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