Visão geral do GoogleSQL para Bigtable

Visualização

Este produto ou recurso está sujeito aos "Termos das ofertas pré-GA" na seção "Termos de Serviço gerais" dos Termos específicos do serviço. Os produtos e recursos pré-GA estão disponíveis "no estado em que se encontram" e podem ter suporte limitado. Para mais informações, consulte as descrições da fase de lançamento.

É possível usar instruções do GoogleSQL para consultar os dados do Bigtable. O GoogleSQL é uma linguagem de consulta estruturada (SQL) compatível com ANSI, que também é implementada para outros serviços do Google Cloud, como o BigQuery e o Spanner.

Este documento apresenta uma visão geral do GoogleSQL para Bigtable. Ele fornece exemplos de consultas SQL que podem ser usadas com o Bigtable e descreve como elas se relacionam a um esquema de tabela do Bigtable. Antes de ler este documento, familiarize-se com o modelo de armazenamento do Bigtable e os conceitos de design de esquemas.

É possível criar e executar consultas no Bigtable Studio no console do Google Cloud ou executar de maneira programática usando a biblioteca de cliente do Bigtable para Java. Para mais informações, consulte Usar SQL com uma biblioteca de cliente do Bigtable.

As consultas SQL são processadas pelos nós do cluster da mesma forma que as solicitações de dados NoSQL. Portanto, as mesmas práticas recomendadas se aplicam ao criar consultas SQL para executar contra os dados do Bigtable, como evitar verificações de tabelas completas ou filtros complexos. Para mais informações, consulte Leituras e desempenho.

Não é possível usar o Data Boost com o GoogleSQL para Bigtable.

Casos de uso

O GoogleSQL para Bigtable é ideal para o desenvolvimento de aplicativos de baixa latência. Além disso, a execução de consultas SQL no console do Google Cloud pode ser útil para receber rapidamente uma representação visual do esquema de uma tabela, verificar se determinados dados foram gravados ou depurar possíveis problemas de dados.

A versão atual do GoogleSQL para Bigtable não oferece suporte a alguns conceitos comuns do SQL, incluindo, entre outros:

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

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

Principais conceitos

Esta seção discute os principais conceitos que você precisa conhecer ao usar o GoogleSQL para consultar seus dados do Bigtable.

Grupos de colunas em respostas SQL

No Bigtable, uma tabela contém um ou mais grupos de colunas, que são usados para agrupar colunas. Quando você consulta uma tabela do Bigtable com o GoogleSQL, o esquema da tabela consiste no seguinte:

• Uma coluna especial chamada _key que corresponde às chaves de linha 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

Mapear tipo de dados

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

Por padrão, cada linha em uma 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 mais recente para essa coluna.

Confira a seguir um exemplo de consulta SQL que retorna uma tabela com o valor da chave de linha e o valor mais recente do qualificador de um mapa chamado columnFamily.

  SELECT _key, columnFamily['qualifier'] FROM myTable

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

Nesse caso, os mapas que representam famílias de colunas são aninhados e retornados como uma matriz. No array, cada chave é um mapa que consiste em um carimbo de data/hora como a chave e dados móveis como o valor. O formato é MAP<key, ARRAY<STRUCT<timestamp, value>>>.

O exemplo a seguir retorna todas as células do grupo de colunas "info" de uma única linha.

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

O mapa retornado é semelhante ao seguinte. Na tabela consultada, info é o grupo de colunas, user_123 é a chave de linha e city e state são os qualificadores de coluna. Cada par de carimbo de data/hora-valor (STRUCT) em uma matriz representa células nessas colunas na linha e é classificado por carimbo de data/hora decrescente.

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

Tabelas esparsas

Um recurso importante do Bigtable é o modelo de dados flexível. Em uma tabela Bigtable, se uma coluna não for usada em uma linha, nenhum dado será armazenado para ela. Uma linha pode ter uma coluna e a próxima pode ter 100 colunas. Por outro lado, em uma tabela de banco 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 você consulta uma tabela do Bigtable com o GoogleSQL, uma coluna não utilizada é representada por um mapa vazio e retornada como um valor NULL. Esses valores NULL podem ser usados como predicados de consulta. Por exemplo, um predicado como WHERE family['column1'] IS NOT NULL pode ser usado para retornar uma linha somente se column1 for usado na linha.

Bytes

Quando você fornece uma string, o GoogleSQL implicitamente converte de valores STRING para valores BYTES. Isso significa, por exemplo, que você pode fornecer a string 'qualifier' em vez da sequência de bytes b'qualifier'.

Como o Bigtable trata todos os dados como bytes por padrão, a maioria das colunas do Bigtable não contém informações de tipo. No entanto, com o GoogleSQL, é possível definir um esquema no momento da leitura com a função CAST. Para mais informações sobre a transmissão, consulte Funções de conversão.

Filtros temporais

A tabela a seguir lista os argumentos que podem ser usados ao acessar elementos temporários de uma tabela. Os argumentos são listados na ordem em que são filtrados. Por exemplo, with_history é aplicado antes de latest_n. Você precisa fornecer um carimbo de data/hora válido.

Argumento	Descrição
as_of	Timestamp. Retorna os valores mais recentes com carimbos de data/hora menores ou iguais ao fornecido.
with_history	Boolean. Controla se o valor mais recente será retornado como um valor escalar ou com carimbo de data/hora como STRUCT.
after_or_equal	Timestamp. Valores com carimbos de data/hora após a entrada. Requer with_history => TRUE
before	Timestamp. Valores com carimbos de data/hora antes da entrada, exclusivos. Requer with_history => TRUE
latest_n	Inteiro. O número de valores com carimbo de data/hora a serem retornados por qualificador de coluna (chave do mapa). Precisa ser maior ou igual a 1. Requer with_history => TRUE.

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

Consultas básicas

Esta seção descreve e mostra exemplos de consultas SQL básicas do Bigtable e como elas funcionam. Para mais exemplos de consultas, consulte Exemplos de padrões de consulta do GoogleSQL para Bigtable.

Extrair a versão mais recente

Embora o Bigtable permita armazenar várias versões de dados em cada coluna, o GoogleSQL para Bigtable retorna por padrão a versão mais recente (a célula mais recente) dos dados de cada linha.

Considere o seguinte conjunto de dados de exemplo, que mostra que user1 se mudou duas vezes no estado de NY e uma vez na cidade de Brooklyn. Neste exemplo, address é o grupo de colunas, e os qualificadores de coluna são street, city e state. As células de uma coluna são separadas por linhas em branco.

		address
_key	street	cidade	state
Usuário 1	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 extrair a versão mais recente de cada coluna para user1, use uma instrução SELECT como esta.

   SELECT * FROM myTable WHERE _key = 'user1'

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

_key	address
Usuário 1	{street:'113 Xyz Street', city:'Brooklyn', state: :'NY'}

Recuperar todas as versões

Para recuperar versões mais antigas (células) dos dados, use a flag with_history. Também é possível criar alias para colunas e expressões, conforme ilustrado no exemplo a seguir.

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

Para entender melhor os eventos que levaram ao estado atual de uma linha, você pode extrair os carimbos de data/hora de cada valor extraindo o histórico completo. Por exemplo, para entender quando user1 mudou para o endereço atual e de onde ele mudou, execute a consulta a seguir:

  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 você usa a flag with_history na consulta SQL, a resposta é retornada como MAP<key, ARRAY<STRUCT<timestamp, value>>>. Cada item na matriz é um valor com carimbo de data/hora para a linha, o grupo de colunas e a coluna especificados. Os carimbos de data/hora são ordenados em ordem cronológica inversa, de modo que os dados mais recentes são sempre o primeiro item retornado.

Observação: as políticas de coleta de lixo controlam o prazo de validade ou o número de versões retidas para um grupo de colunas. Para mais informações, consulte a Visão geral da coleta de lixo.

A resposta da consulta é a seguinte.

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

Também é possível recuperar o número de versões em cada linha usando funções de matriz, conforme demonstrado nesta consulta:

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

Extrair dados de um período específico

O uso de um filtro as_of permite recuperar o estado de uma linha como ela era em um determinado momento. Por exemplo, se você quiser saber o endereço de user em 10 de janeiro de 2022 às 13h14, execute a consulta a seguir.

  SELECT address
  FROM myTable(as_of => TIMESTAMP('2022/01/10-13:14:00'))
  WHERE _key = 'user1'

O resultado mostra qual teria sido o último endereço conhecido em 10 de janeiro de 2022 às 13h14, que é a combinação de rua e cidade da atualização de 2021/12/20-09:44:31.010 e o estado de 2005/03/01-11:12:15.112.

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

O mesmo resultado também pode ser alcançado usando carimbos de data/hora 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 ativado ou desativado dos alarmes de fumaça e monóxido de carbono. O grupo de colunas é alarmType, e os qualificadores de coluna são smoke e carbonMonoxide. As células de cada coluna são separadas por linhas vazias.

	alarmType
_key	fumaça	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'

Você pode encontrar seções de building1 em que um alarme de fumaça estava ativado às 9h do dia 1º de abril de 2023 e o status do alarme de monóxido de carbono no momento usando a seguinte consulta.

  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'

O resultado é o seguinte:

local	CO_sensor
building1#section1	'on'

Dados de série temporal de consulta

Um caso de uso comum do Bigtable é o armazenamento de dados de séries temporais. Considere o seguinte conjunto de dados de exemplo, que mostra as leituras de temperatura e umidade para sensores meteorológicos. O ID do grupo de colunas é metrics, e os qualificadores de coluna são temperature e humidity. As células em uma coluna são separadas por linhas vazias, e cada célula representa uma leitura de sensor com carimbo de data/hora.

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

É possível recuperar um intervalo específico de valores de carimbo de data/hora usando os filtros temporais after, before ou after_or_equal. O exemplo a seguir usa 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%'

A consulta retorna 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}

Consultar JSON

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

Por exemplo, é possível extrair o valor do elemento JSON abc da última célula no grupo de colunas session com a chave de linha usando a consulta abaixo.

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

Fazer o escape de caracteres especiais e palavras reservadas

O Bigtable oferece muita flexibilidade na nomenclatura de tabelas e colunas. Como resultado, nas consultas SQL, talvez seja necessário usar códigos de escape nos nomes das tabelas devido a caracteres especiais ou palavras reservadas.

Por exemplo, a consulta a seguir não é válida no SQL devido ao período no nome da tabela.

  -- ERROR: Table name format not supported

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

No entanto, você pode resolver esse problema incluindo os itens com caracteres de acento grave (``).

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

Se uma palavra-chave reservada do SQL for usada como um identificador, ela poderá ser escapada da mesma forma.

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

Usar SQL com uma biblioteca de cliente do Bigtable

As bibliotecas de cliente do Bigtable para Java e Python oferecem suporte à consulta de dados com SQL usando a API executeQuery. Os exemplos a seguir mostram como emitir uma consulta e acessar os dados:

Java

Para usar esse recurso, use a versão 2.41.0 ou mais recente da java-bigtable. Para mais informações sobre o uso, consulte executeQuery, Statement e ResultSet no 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

Para usar esse recurso, use a versão 2.26.0 ou mais recente da 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"])

A seguir

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