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.

Você pode usar instruções GoogleSQL para consultar 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 mostra exemplos de consultas SQL que podem ser usadas com o Bigtable e descreve como eles se relacionam esquema da 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 é possível executá-los de maneira programática usando o cliente do Bigtable biblioteca para Java. Para mais informações, consulte Usar SQL com um Biblioteca de cliente do 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 construções SQL comuns, incluindo, mas não se limitando a:

• Declarações de linguagem de manipulação de dados (DML, na sigla em inglês) 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 do controle de acesso a dados
• A sintaxe de consulta para as 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

Nesta seção, abordamos os principais conceitos que você precisa conhecer ao usar GoogleSQL para consultar os 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 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 grupo de colunas do Bigtable na tabela que contém os dados do grupo de colunas naquela 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, onde uma chave é o qualificador de coluna do Bigtable na tabela consultada é o valor mais recente dessa coluna.

Veja a seguir um exemplo de consulta SQL que retorna uma tabela com o chave de linha mais recente e o último valor 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 os grupos de colunas são aninhados e retornados como uma matriz. Na matriz, cada chave é um mapa que consiste em um carimbo de data/hora como o dados-chave e de células como o valor. O formato é MAP<key, ARRAY<STRUCT<timestamp, value>>>:

O exemplo a seguir retorna todas as células na coluna "info" um único grupo de colunas linha de comando.

  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 a coluna qualificadores. 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 um Tabela do Bigtable. Se uma coluna não for utilizada em uma linha, nenhum dado será armazenado para a coluna. 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 colunas, e um valor NULL normalmente é armazenado na coluna de uma linha que não tem os dados da 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 converte implicitamente de valores STRING para BYTES. Isso significa, por exemplo, que é possível forneça 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 transmissão, consulte 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 menos carimbos de data/hora ou igual ao carimbo de data/hora 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	Número inteiro. O número de valores com carimbo de data/hora a serem retornados por coluna qualificador (tecla 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 outros exemplos de consulta, acesse Padrão de consulta do GoogleSQL para Bigtable exemplos.

Recuperar 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 amostra, que mostra que user1 foi realocado duas vezes no estado de NY e uma vez na cidade do Brooklyn. Neste exemplo, address é o grupo de colunas, e os qualificadores de coluna são street, city e state. As células em uma coluna são separadas por linhas vazias.

		address
_key	street	cidade	state
Usuário 1	2023/01/10-14:10:01.000: "Rua Xyz, 113" 2021/12/20-09:44:31.010: "76 Xyz Street" 2005/03/01-11:12:15.112: "Abc Street, 123"	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 recentes de rua, cidade e estado (escritos em momentos diferentes) impressos como JSON. Os carimbos de data/hora não estão incluídos 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 definir um 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 levam ao estado atual de uma linha, você pode recuperar os carimbos de data/hora de cada valor recuperando o histórico completo. Para exemplo, para entender quando user1 mudou para seu endereço atual e para onde de onde foram movidos, você 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 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 organizados 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 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
Rua Xyz, 113			76 Xyz Street	10/01/2023

Você também pode 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 especificado

O uso de um filtro as_of permite recuperar o estado de uma linha como ele estava em em um determinado momento. Por exemplo, se você quiser saber o endereço de user como de 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', estado: :'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 a coluna Os qualificadores são smoke e carbonMonoxide. As células de cada coluna são separadas por linhas vazias.

	alarmType
_key	fumaça	carbonMonoxide
edifício1#seção1	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'
edifício1#seção2	2021/03/11-07:15:04.000: "desativado" 2021/03/11-07:00:25.000: "ativado"

É possível encontrar seções do(a) building1 em que um detector de fumaça estava ativado às 9h de abril 1o de janeiro de 2023 e o status do alarme de monóxido de carbono no momento usando o a seguir.

  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	"ativado"

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,00	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 recuperar o valor do elemento JSON abc do última célula do grupo de colunas session junto com a chave de linha usando a a seguir.

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

Evitar caracteres especiais e palavras reservadas

O Bigtable oferece alta 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 é um SQL válido devido ao período na tabela. nome.

  -- ERROR: Table name format not supported

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

No entanto, é possível resolver esse problema colocando os itens com um 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 utilizar esse recurso, utilize a versão 2.41.0 ou posterior do 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 o GoogleSQL para Bigtable documentação de referência.
• Saiba mais sobre o Bigtable Studio.