GoogleSQL per Bigtable panoramica

Anteprima

Questo prodotto o funzionalità è soggetto ai "Termini delle Offerte pre-GA" nella sezione Termini generali del servizio dei Termini specifici del servizio. I prodotti e le funzionalità pre-GA sono disponibili "così come sono" e potrebbero avere un supporto limitato. Per saperne di più, consulta le descrizioni della fase di lancio.

Puoi usare le istruzioni SQL di Google per eseguire query Dati Bigtable. GoogleSQL è un servizio conforme ad ANSI Structured Query Language (SQL) 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 Bigtable e ne descrive la relazione Schema della tabella Bigtable. Prima di leggere questo documento, ti consigliamo di avere familiarità con l'archiviazione Bigtable modello e progettazione dello schema di base.

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 ulteriori informazioni, consulta Utilizzare SQL con libreria client di Bigtable.

Casi d'uso

GoogleSQL per Bigtable è ottimale per la bassa latenza lo sviluppo di applicazioni. Inoltre, l'esecuzione di query SQL La console Google Cloud può essere utile per ottenere rapidamente una rappresentazione visiva dello schema di una tabella, verificando che alcuni dati siano stati scritti o eseguendo il debug possibili problemi relativi ai dati.

La release attuale di GoogleSQL per Bigtable non supporta i costrutti SQL più comuni, inclusi, a titolo esemplificativo, seguenti:

• Istruzioni DML (Data Manipulation Language) dopo SELECT, come INSERT, UPDATE o DELETE
• istruzioni DDL (Data Definition Language) come CREATE, ALTER o DROP
• Istruzioni di controllo dell'accesso ai dati
• Sintassi delle query per le 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 utilizzati per raggruppare le colonne. Quando esegui una query su una tabella Bigtable GoogleSQL, lo schema della tabella è quanto segue:

• Una colonna speciale denominata _key che corrisponde alle chiavi di riga nell'elemento della query tavola
• 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 dati MAP<key, value>, ed è stato progettato specificamente per le famiglie di colonne.

Per impostazione predefinita, ogni riga in una colonna della mappa contiene coppie chiave-valore, in cui una chiave viene 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 una 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, versioni dei dati: nelle colonne puoi aggiungere un valore temporale , ad esempio with_history, all'istruzione SQL.

In questo caso, le mappe che rappresentano famiglie di colonne vengono nidificate e restituite come un array di dati. Nell'array, ogni chiave è a sua volta una mappa costituita da un timestamp come chiave e dati delle celle 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 è il famiglia di colonne, user_123 è la chiave di riga e city e state sono la colonna qualificatori. Ogni coppia timestamp-valore (STRUCT) in un array rappresenta le celle in queste colonne in quella riga e vengono ordinate per timestamp in ordine decrescente.

/*----------+------------------------------------------------------------------+
 |   _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 un Tabella Bigtable, se una colonna non è utilizzata in una riga, non vengono archiviati dati per la colonna. Una riga può avere una colonna e la riga successiva può avere 100 colonne. Al contrario, in una tabella di un 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 non utilizzata è rappresentata con una mappa vuota e viene restituita come Valore NULL. Questi valori NULL possono essere utilizzati come predicati di query. Ad esempio, un può essere usato un predicato come WHERE family['column1'] IS NOT NULL per restituire una riga solo se nella riga viene utilizzato column1.

Byte

Quando fornisci una stringa, per impostazione predefinita GoogleSQL trasmette in modo implicito da STRING valori a BYTES valori. Ciò significa, ad esempio, che puoi fornisci 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 In GoogleSQL puoi definire uno schema in tempo di lettura con l'CAST personalizzata. Per ulteriori informazioni sulla trasmissione, consulta la sezione Conversioni .

Filtri temporali

Nella tabella seguente sono elencati gli argomenti che puoi utilizzare durante l'accesso 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 fornisci un timestamp valido.

Argomento	Descrizione
as_of	Timestamp: Restituisce gli ultimi valori con timestamp minori o uguale al timestamp fornito.
with_history	Booleano: Consente di stabilire se restituire l'ultimo valore sotto forma di valori scalari o con timestamp come STRUCT.
after_or_equal	Timestamp: I valori con timestamp dopo l'input, inclusi. Richiede with_history => TRUE
before	Timestamp: Valori con timestamp prima dell'input, esclusivi. Richiede with_history => TRUE
latest_n	Numero intero. Il numero di valori con timestamp da restituire per colonna qualificatore (tasto mappa). Deve essere maggiore o uguale a 1. Richiede with_history => TRUE.

Per altri esempi, consulta Query avanzata modelli.

Query di base

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

Recupera l'ultima versione

Sebbene Bigtable consenta di archiviare più versioni dei dati ogni colonna, 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 è stato riposizionato due volte nello stato di New York e una volta nella città di Brooklyn. In questo esempio, address è la famiglia di colonne e i relativi qualificatori sono street, city e state. Le celle in una colonna sono separate da righe vuote.

		address
_key	via	città	stato
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 l'ultima versione di ogni colonna per user1, puoi utilizzare un SELECT simile alla seguente.

   SELECT * FROM myTable WHERE _key = 'user1'

La risposta contiene l'indirizzo corrente, che è una combinazione delle i valori recenti di vie, città e province (scritti in orari diversi) stampati come JSON. I timestamp non sono inclusi nella risposta.

_key	address
utente1	{street:'Via Xyz 113', città:'Brooklyn', stato: :'NY'}

Recupera tutte le versioni

Per recuperare 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 portano allo stato attuale di una riga, puoi: recupera i timestamp per ogni valore recuperando la cronologia completa. Per ad esempio per capire quando user1 si è trasferito al suo indirizzo attuale e dove da cui sono trasferiti, 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 è restituito 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 vengono sempre il primo articolo restituito.

Nota: i criteri di garbage collection controllano la data di scadenza o il numero le versioni mantenute per una famiglia di colonne. Per ulteriori informazioni, consulta Panoramica della raccolta dei rifiuti.

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 funzioni array come mostrato nella query seguente:

  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 ti consente di recuperare lo stato di una riga così com'era in in un determinato momento. Ad esempio, se vuoi conoscere l'indirizzo di user come del 10 gennaio 2022 alle 13:14, puoi eseguire questa 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.

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

Lo stesso risultato si può ottenere 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 accensione o spegnimento del fumo e rilevatori di monossido di carbonio. La famiglia di colonne è alarmType e la colonna qualificatori sono smoke e carbonMonoxide. Le celle in ogni colonna sono separate per righe vuote.

	alarmType
_key	fumo	carbonMonoxide
edificio1#sezione1	2023/04/01-09:10:15.000: "off" 2023/04/01-08:41:40.000: "il" 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'
edificio1#sezione2	2021/03/11-07:15:04.000: "off" 2021/03/11-07:00:25.000: 'on'

Puoi trovare sezioni di building1 in cui è stato attivato un allarme fumo alle 09:00 di aprile 1, 2023 e lo stato dell'allarme di monossido di carbonio in quel momento utilizzando il 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:

località	CO_sensor
building1#section1	'on'

Esegui query su dati di serie temporali

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

	metriche
_key	temperatura	umidità
sensoreA#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
sensoreA#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 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 nel seguente 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}

JSON di query

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

Ad esempio, puoi recuperare il valore dell'elemento JSON abc dalla l'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

Esegui l'escape di caratteri speciali e parole riservate

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

Ad esempio, la seguente query non è SQL valida a causa del punto nella tabella nome.

  -- ERROR: Table name format not supported

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

Tuttavia, puoi risolvere il problema racchiudendo gli elementi con un accento grave (`).

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

Se viene utilizzata come identificatore una parola chiave riservata SQL, può essere utilizzata in modo analogo con caratteri di escape.

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

Usa SQL con una libreria client Bigtable

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

Java

Per usare questa funzionalità, devi usare java-bigtable versione 2.41.0 o successiva. Per per altre informazioni sull'utilizzo, vedi executeQuery, Dichiarazione, e ResultSet nel Javadoc.

  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
      }
    }
  }

Python asyncio

Per usare questa funzionalità, devi usare python-bigtable 2.26.0 o versioni 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"])

Passaggi successivi

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