Questa pagina è stata tradotta dall'API Cloud Translation.

Panoramica di GoogleSQL per Bigtable

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 con Bigtable e descrive la loro relazione con lo schema di una tabella Bigtable. Prima di leggere questo documento, devi conoscere il modello di archiviazione e i concetti di progettazione dello schema di Bigtable.

Puoi creare ed eseguire query in Bigtable Studio nella console Google Cloud. oppure eseguirle in modo programmatico utilizzando il client Bigtable libreria per Java. 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. Di conseguenza, si applicano le stesse best practice quando si creano query SQL da eseguire. rispetto ai dati di Bigtable, ad esempio evitando scansioni complete delle tabelle filtri complessi. Per ulteriori informazioni, consulta la sezione Letture e dei carichi di lavoro.

Casi d'uso

GoogleSQL per Bigtable è ottimale per la bassa latenza lo sviluppo di applicazioni. 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 i costrutti SQL comuni, inclusi, a titolo esemplificativo, quanto segue:

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 le sottoquery JOIN, UNION, GROUP BY, UNNEST e CTEs

Per ulteriori informazioni, tra cui le funzioni, gli operatori, i tipi di dati supportati e sulla sintassi delle query, consulta GoogleSQL per Bigtable riferimento documentazione di Google.

Concetti fondamentali

Questa sezione illustra i concetti chiave da tenere presenti quando utilizzi a 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 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 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 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, oppure versioni dei dati: nelle colonne puoi aggiungere un valore temporale filtro, 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 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 nella sezione "info" famiglia di colonne per una singola riga di comando.

  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 di 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ò contenere 100 colonne. Al contrario, in una tabella di database relazionale, tutte le righe contengono tutte di colonne, mentre un valore NULL viene generalmente memorizzato nella colonna di una riga che non ha 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 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 esegue il passaggio implicito da valori STRING a valori BYTES. Ciò significa, ad esempio, che puoi fornisci la stringa 'qualifier' anziché la sequenza di byte b'qualifier'.

Poiché Bigtable per impostazione predefinita tratta tutti i dati come byte, Le colonne Bigtable non contengono 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

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 i valori più recenti con timestamp minore o uguale a quello 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: Valori con timestamp successivi all'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 query SQL di Bigtable di base e il loro funzionamento. Per ulteriori query di esempio, vedi Pattern di query di GoogleSQL per Bigtable esempi.

Recupera l'ultima versione

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 è 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 di una colonna sono separate da righe vuote.

		address
_key	via	città	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 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, ovvero 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:'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 hanno portato allo stato corrente di una riga, puoi recuperare 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 viene restaurata 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.

moved_to			moved_from	moved_on
Via Xyz 113			Via Xyz 76	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)

Recupera i dati da un orario 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 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 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.

	alarmType
_key	fumo	carbonMonoxide
edificio1#sezione1	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'


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
edificio1#sezione1	'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 la temperatura e l'umidità letture dei sensori meteo. L'ID famiglia di colonne è metrics e i qualificatori di colonna 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	temperature	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 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 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'

Utilizzare 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 asincrono

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