Vista geral do GoogleSQL para Bigtable

Pode usar declarações GoogleSQL para consultar os seus dados do Bigtable. O GoogleSQL é uma linguagem de consulta estruturada (SQL) em conformidade com a norma ANSI que também é implementada para outros serviços, como o BigQuery e o Spanner. Google Cloud

Este documento oferece uma vista geral do GoogleSQL para o Bigtable. Fornece exemplos de consultas SQL que pode usar com o Bigtable e descreve a respetiva relação com um esquema de tabela do Bigtable. Antes de ler este documento, deve familiarizar-se com o modelo de armazenamento do Bigtable e os conceitos de design de esquemas.

Pode criar e executar consultas no Bigtable Studio na Google Cloud consola ou executá-las programaticamente através da biblioteca cliente do Bigtable para Java, Python ou Go. Para mais informações, consulte o artigo Use SQL with a Bigtable client library (Use o SQL com uma biblioteca cliente do Bigtable).

As consultas SQL são processadas pelos nós do cluster da mesma forma que os pedidos de dados NoSQL. Por conseguinte, aplicam-se as mesmas práticas recomendadas quando cria consultas SQL para executar em relação aos seus dados do Bigtable, como evitar análises completas de tabelas ou filtros complexos. Para mais informações, consulte o artigo Leituras e desempenho.

Não pode usar o Data Boost com o GoogleSQL para Bigtable.

Exemplos de utilização

O GoogleSQL para Bigtable é ideal para o desenvolvimento de aplicações com baixa latência. Além disso, a execução de consultas SQL na Google Cloud consola pode ser útil para obter rapidamente uma representação visual do esquema de uma tabela, verificar se determinados dados foram escritos ou depurar possíveis problemas de dados.

A versão atual do GoogleSQL para Bigtable não suporta algumas construções SQL comuns, incluindo, entre outras, as seguintes:

• Instruções da linguagem de manipulação de dados (DML) além de SELECT, como INSERT, UPDATE ou DELETE
• Declarações de linguagem de definição de dados (LDD), como CREATE, ALTER ou DROP
• Declarações de controlo de acesso a dados
• Sintaxe de consulta para subconsultas, JOIN, UNION e CTEs

Para mais informações, incluindo funções, operadores, tipos de dados e sintaxe de consulta suportados, consulte a documentação de referência do GoogleSQL para Bigtable.

Visualizações

Pode usar o GoogleSQL para o Bigtable para criar os seguintes recursos:

• Vista materializada contínua: um resultado pré-calculado de uma consulta SQL em execução contínua, incluindo dados agregados, que se sincroniza com a respetiva tabela de origem com atualizações incrementais. Esta funcionalidade está em pré-visualização.
• Vista lógica: uma consulta guardada com nome que pode ser consultada como uma tabela.

Para comparar estes tipos de visualizações, bem como as visualizações autorizadas, consulte Tabelas e visualizações.

Conceitos-chave

Esta secção aborda os principais conceitos a ter em atenção quando usa o GoogleSQL para consultar os seus dados do Bigtable.

Famílias de colunas em respostas SQL

No Bigtable, uma tabela contém uma ou mais famílias de colunas, que são usadas para agrupar colunas. Quando consulta uma tabela do Bigtable com o GoogleSQL, o esquema da tabela consiste no seguinte:

• Uma coluna especial denominada _key que corresponde às chaves de linhas na tabela consultada
• Uma única coluna para cada família de colunas do Bigtable na tabela, que contém os dados da família de colunas nessa linha

Tipo de dados de mapa

O GoogleSQL para Bigtable inclui o tipo de dados MAP<key, value>, que foi concebido especificamente para acomodar famílias de colunas.

Por predefinição, cada linha numa coluna de mapa contém pares de chave-valor, em que uma chave é o qualificador de coluna do Bigtable na tabela consultada e o valor é o valor mais recente dessa coluna.

Segue-se um exemplo de uma consulta SQL que devolve uma tabela com o valor da chave da linha e o valor mais recente do qualificador de um mapa denominado columnFamily.

  SELECT _key, columnFamily['qualifier'] FROM myTable

Se o seu esquema do Bigtable envolver o armazenamento de várias células ou versões dos dados em colunas, pode adicionar um filtro temporal, como with_history, à sua declaração SQL.

Neste caso, os mapas que representam famílias de colunas estão aninhados e são devolvidos como uma matriz. Na matriz, cada valor é, em si mesmo, um mapa que consiste num carimbo de data/hora como chave e dados da célula como valor. O formato é MAP<key, ARRAY<STRUCT<timestamp, value>>>.

O exemplo seguinte devolve todas as células na família de colunas "info" para uma única linha.

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

O mapa devolvido tem o seguinte aspeto. Na tabela consultada, info é a família de colunas, user_123 é a chave da linha e city e state são os qualificadores de colunas. Cada par de carimbo de data/hora-valor (STRUCT) numa matriz representa células nessas colunas nessa linha e são ordenados por carimbo de data/hora descendente.

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

Tabelas esparsas

Uma funcionalidade essencial do Bigtable é o seu modelo de dados flexível. Numa tabela do Bigtable, se uma coluna não for usada numa linha, não são armazenados dados para a coluna. Uma linha pode ter uma coluna e a linha seguinte pode ter 100 colunas. Por outro lado, numa tabela de base de dados relacional, todas as linhas contêm todas as colunas, e um valor NULL é normalmente armazenado na coluna de uma linha que não tem dados para essa coluna.

No entanto, quando consulta uma tabela do Bigtable com o GoogleSQL, uma coluna não usada é representada com um mapa vazio e é devolvida como um valor NULL. Estes valores NULL podem ser usados como predicados de consulta. Por exemplo, um predicado como WHERE family['column1'] IS NOT NULL pode ser usado para devolver uma linha apenas se column1 for usado na linha.

Bytes

Quando fornece uma string, o GoogleSQL converte implicitamente os valores STRING em valores BYTES por predefinição. Isto significa, por exemplo, que pode fornecer a string 'qualifier' em vez da sequência de bytes b'qualifier'.

Uma vez que o Bigtable trata todos os dados como bytes por predefinição, a maioria das colunas do Bigtable não contém informações de tipo. No entanto, com o GoogleSQL, pode definir um esquema no momento da leitura com a função CAST. Para mais informações sobre a transmissão, consulte as funções de conversão.

Filtros temporais

A tabela seguinte apresenta os argumentos que pode usar quando acede a elementos temporais de uma tabela. Os argumentos são apresentados pela ordem em que são filtrados. Por exemplo, with_history é aplicado antes de latest_n. Tem de indicar uma indicação de tempo válida.

Argumento	Descrição
as_of	Data/hora. Devolve os valores mais recentes com datas/horas inferiores ou iguais à data/hora fornecida.
with_history	Booleano. Controla se deve devolver o valor mais recente como um valor escalar ou valores com indicação de data/hora como STRUCT.
after	Data/hora. Valores com datas/horas após a entrada, exclusivos. Requer with_history => TRUE.
after_or_equal	Data/hora. Valores com datas/horas posteriores à entrada, inclusive. Requer with_history => TRUE.
before	Data/hora. Valores com datas/horas anteriores à entrada, exclusivos. Requer with_history => TRUE.
latest_n	Inteiro. O número de valores com data/hora a devolver por qualificador de coluna (chave do mapa). Tem de ser igual ou superior a 1. Requer with_history => TRUE.

Para mais exemplos, consulte os Padrões de consulta avançados.

Consultas básicas

Esta secção descreve e mostra exemplos de consultas básicas de SQL do Bigtable e como funcionam. Para consultas de exemplo adicionais, consulte os exemplos de padrão de consulta do GoogleSQL para Bigtable.

Obtenha a versão mais recente

Embora o Bigtable lhe permita armazenar várias versões de dados em cada coluna, o GoogleSQL para Bigtable devolve por predefinição a versão mais recente, ou seja, a célula mais recente, dos dados de cada linha.

Considere o seguinte conjunto de dados de exemplo, que mostra que user1 mudou de localização duas vezes no estado de NY e uma vez na cidade de Brooklyn. Neste exemplo, address é a família de colunas e os qualificadores de colunas são street, city> e state. As células numa coluna estão separadas por linhas vazias.

		address
_key	rua	city	state
user1	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'

Para obter a versão mais recente de cada coluna para user1, pode usar uma declaração SELECT como a seguinte.

   SELECT address['street'], address['city'] FROM myTable WHERE _key = 'user1'

A resposta contém a morada atual, que é uma combinação dos valores de rua, cidade e estado mais recentes (escritos em momentos diferentes) impressos como JSON. As indicações de tempo não estão incluídas na resposta.

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

Recupere todas as versões

Para obter versões anteriores (células) dos dados, use a flag with_history. Também pode usar alias para colunas e expressões, conforme ilustrado no exemplo seguinte.

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

Para compreender melhor os eventos que originaram o estado atual de uma linha, pode obter as datas/horas de cada valor, obtendo o histórico completo. Por exemplo, para saber quando user1 se mudou para a morada atual e de onde se mudou, pode executar a seguinte consulta:

  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 usa a flag with_history na sua consulta SQL, a resposta é devolvida como MAP<key, ARRAY<STRUCT<timestamp, value>>>. Cada item na matriz é um valor com data/hora para a linha, a família de colunas e a coluna especificados. As datas/horas são ordenadas por ordem cronológica inversa, pelo que os dados mais recentes são sempre o primeiro item devolvido.

Nota: as políticas de recolha de lixo controlam o tempo de expiração ou o número de versões retidas para uma família de colunas. Para mais informações, consulte a Vista geral da recolha de lixo.

A resposta da consulta é a seguinte.

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

Também pode obter o número de versões em cada linha através de funções de matriz, conforme demonstrado na seguinte consulta:

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

Obtenha dados de uma hora específica

A utilização de um filtro as_of permite-lhe obter o estado de uma linha tal como estava num determinado momento. Por exemplo, se quiser saber a morada de user a 10 de janeiro de 2022 às 13:14, pode executar a seguinte consulta.

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

O resultado mostra qual teria sido a última morada conhecida a 10 de janeiro de 2022 às 13:14, que é a combinação da rua e da cidade da atualização de 20/12/2021 às 09:44:31.010 e o distrito de 01/03/2005 às 11:12:15.112.

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

Também pode alcançar o mesmo resultado através de datas/horas Unix.

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

Considere o seguinte conjunto de dados, que mostra o estado ligado ou desligado dos detetores de fumo e monóxido de carbono. A família de colunas é alarmType e os qualificadores de coluna são smoke e carbonMonoxide. As células em cada coluna estão separadas por linhas vazias.

	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'

Pode encontrar secções de building1 onde um detetor de fumo estava ativado às 09:00 a 1 de abril de 2023 e o estado do detetor de monóxido de carbono na altura através da seguinte consulta.

  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'

O resultado é o seguinte:

localização	CO_sensor
building1#section1	'on'

Consultar dados de intervalos temporais

Um exemplo de utilização comum do Bigtable é o armazenamento de dados de séries cronológicas. Considere o seguinte conjunto de dados de exemplo, que mostra as leituras de temperatura e humidade dos sensores meteorológicos. O ID da família de colunas é metrics e os qualificadores de colunas são temperature e humidity. As células numa coluna são separadas por linhas vazias e cada célula representa uma leitura do sensor com indicação de data/hora.

	métricas
_key	temperatura	humidade
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

Pode obter um intervalo específico de valores de data/hora através dos filtros temporais after, before ou after_or_equal. O exemplo seguinte usa 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%'

A consulta devolve os dados neste 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}]

UNPACK dados de intervalos temporais

Quando analisa dados de séries cronológicas, é frequentemente preferível trabalhar com os dados num formato tabular. A função UNPACK do Bigtable pode ajudar.

UNPACK é uma função de valor de tabela (TVF) do Bigtable que devolve uma tabela de saída inteira em vez de um único valor escalar e aparece na cláusula FROM como uma subconsulta de tabela. A TVF expande cada valor com indicação de tempo em várias linhas, uma por indicação de tempo, e move a indicação de tempo para a coluna _timestamp.UNPACK

A entrada para UNPACK é uma subconsulta onde with_history => true.

A saída é uma tabela expandida com uma coluna _timestamp em cada linha.

Uma família de colunas de entrada MAP<key, ARRAY<STRUCT<timestamp, value>>>é descompactada para MAP<key, value> e um qualificador de coluna ARRAY<STRUCT<timestamp, value>>> é descompactado para value. Os outros tipos de colunas de entrada permanecem inalterados. As colunas têm de ser selecionadas na subconsulta para serem descompactadas e selecionadas. Não é necessário selecionar a nova coluna _timestamp para descompactar as datas/horas.

Expandindo o exemplo de intervalos temporais em Consultar dados de intervalos temporais, e usando a consulta nessa secção como entrada, a sua consulta UNPACK é formatada da seguinte forma:

  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%'
  ));

A consulta devolve os dados neste formato:

temp_versioned	_timestamp
55	1672898400
55	1672894800
56	1672891200

Consultar JSON

As funções JSON permitem manipular JSON armazenado como valores do Bigtable para cargas de trabalho operacionais.

Por exemplo, pode obter o valor do elemento JSON abc da célula mais recente na família de colunas session juntamente com a chave da linha através da seguinte consulta.

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

Escape de carateres especiais e palavras reservadas

O Bigtable oferece uma grande flexibilidade na atribuição de nomes a tabelas e colunas. Como resultado, nas suas consultas SQL, os nomes das tabelas podem ter de ser interpretados de forma literal devido a carateres especiais ou palavras reservadas.

Por exemplo, a seguinte consulta não é SQL válida devido ao ponto no nome da tabela.

  -- ERROR: Table name format not supported

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

No entanto, pode resolver este problema incluindo os itens entre carateres de acento grave (`).

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

Se for usada uma palavra-chave reservada de SQL como identificador, também pode ser ignorada.

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

Use SQL com uma biblioteca cliente do Bigtable

As bibliotecas cliente do Bigtable para Java, Python e Go suportam a consulta de dados com SQL através da API executeQuery. Os exemplos seguintes mostram como emitir uma consulta e aceder aos dados:

Go

Para usar esta funcionalidade, tem de usar a versão 1.36.0 ou posterior do cloud.google.com/go/bigtable. Para mais informações sobre a utilização, consulte a documentação de 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

Para usar esta funcionalidade, tem de usar a versão 2.57.3 ou posterior do java-bigtable. Para mais informações sobre a utilização, consulte prepareStatement, executeQuery, BoundStatement e ResultSet no 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

Para usar esta funcionalidade, tem de usar a versão 2.30.1 ou posterior do python-bigtable.

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

Utilização: SELECT *

As consultas SELECT * podem sofrer erros temporários quando uma família de colunas é adicionada ou eliminada da tabela consultada. Por este motivo, para cargas de trabalho de produção, recomendamos que especifique todos os IDs de famílias de colunas na sua consulta, em vez de usar SELECT *. Por exemplo, use SELECT cf1, cf2, cf3 em vez de SELECT *.

O que se segue?

• Explore a documentação de referência do GoogleSQL para Bigtable.
• Saiba mais sobre o Bigtable Studio.