Visão geral do GoogleSQL para Bigtable

É possível usar instruções do GoogleSQL para consultar seus dados do Bigtable. O GoogleSQL é uma linguagem de consulta estruturada (SQL) compatível com ANSI e também implementada para outros serviços do Google Cloud, como BigQuery e 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 esquema.

É possível criar e executar consultas no Bigtable Studio no console Google Cloud ou de forma programática usando a biblioteca de cliente do Bigtable para Java, Python ou Go. 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 nos dados do Bigtable, como evitar verificações completas de tabelas 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, executar consultas SQL no console doGoogle Cloud pode ser útil para ter 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 é compatível com algumas construções comuns de SQL, incluindo, entre outras, as seguintes:

• Instruções da linguagem de manipulação de dados (DML) além de SELECT, como INSERT, UPDATE ou DELETE
• Instruções da linguagem de definição de dados (DDL, na sigla em inglês), como CREATE, ALTER ou DROP
• Instruções de controle de acesso a dados
• Sintaxe de consulta para subconsultas, JOIN, UNION, 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.

Visualizações

É possível usar o GoogleSQL para Bigtable e criar os seguintes recursos:

• Visualização materializada: um resultado pré-calculado de uma consulta SQL em execução contínua, incluindo dados agregados, que é sincronizado com a tabela de origem com atualizações incrementais.
• Visualização lógica: consulta salva e nomeada que pode ser consultada como uma tabela.

Para comparar esses tipos de visualizações e as autorizadas, consulte Tabelas e visualizações.

Principais conceitos

Esta seção discute conceitos importantes 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. Ao consultar 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 grupo de colunas do Bigtable na tabela, que contém os dados do grupo de colunas nessa linha

Tipo de dados de mapa

O GoogleSQL para Bigtable inclui o tipo de dados MAP<key, value>, que foi 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 valor mais recente dessa coluna.

Confira um exemplo de uma 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, à sua instrução SQL.

Nesse caso, os mapas que representam grupos de colunas são aninhados e retornados como uma matriz. No array, cada valor é 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 no grupo de colunas "info" de uma única linha.

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

O mapa retornado é semelhante a este. 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 carimbo de data/hora-valor (STRUCT) em uma matriz representa células nessas colunas nessa linha, e elas são classificadas por carimbo de data/hora em ordem 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 do 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. Em contraste, em uma tabela de banco de dados relacional, todas as linhas contêm todas as colunas, e um valor NULL geralmente é 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 usada é representada com 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 nela.

Bytes

Quando você fornece uma string, o GoogleSQL converte implicitamente os valores de STRING para BYTES por padrão. 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 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 temporais de uma tabela. Os argumentos são listados na ordem em que são filtrados. Por exemplo, with_history é aplicado antes de latest_n. É necessário fornecer um carimbo de data/hora válido.

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

Consultas de base

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.

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 exemplo, que mostra que user1 mudou de local duas vezes no estado de Nova York 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 em uma coluna são separadas por linhas vazias.

Para extrair a versão mais recente de cada coluna para user1, use uma instrução SELECT como esta:

   SELECT street, city 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 (escritos em momentos diferentes) impressos como JSON. As marcações de tempo não são incluídas na resposta.

Recuperar todas as versões

Para recuperar versões mais antigas (células) dos dados, use a flag with_history. Você também pode 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, recupere os carimbos de data/hora de cada valor ao acessar o histórico completo. Por exemplo, para saber quando user1 se mudou para o endereço atual e de onde veio, execute 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 ordenados em ordem cronológica inversa, então os dados mais recentes são sempre o primeiro item retornado.

A resposta da consulta é esta.

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

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

Recuperar dados de um horário especificado

Usar um filtro as_of permite recuperar o estado de uma linha 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 seguinte consulta.

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

O resultado mostra o que 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 20/12/2021, às 09h44:31.010, e o estado de 01/03/2005, às 11h12:15.112.

O mesmo resultado pode ser alcançado usando carimbos de data/hora do Unix.

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

Considere o conjunto de dados a seguir, que mostra o estado ligado ou desligado 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 em cada coluna são separadas por linhas vazias.

Você pode encontrar seções de building1 em que um alarme de fumaça estava ligado à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, 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:

Dados de série temporal de consulta

Um caso de uso comum do Bigtable é o armazenamento de dados de série temporal. Considere o seguinte conjunto de dados de exemplo, que mostra 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 em branco, e cada célula representa uma leitura de sensor com carimbo de data/hora.

É 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(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 retorna os dados neste formato:

Dados de série temporal UNPACK

Ao analisar dados de série temporal, geralmente é preferível trabalhar com os dados em formato tabular. A função UNPACK do Bigtable pode ajudar.

UNPACK é uma função com valor de tabela (TVF) do Bigtable que retorna 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 UNPACK expande cada valor com carimbo de data/hora em várias linhas (uma por carimbo) e move o carimbo para a coluna _timestamp.

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

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

Um grupo de colunas de entrada MAP<key, ARRAY<STRUCT<timestamp, value>>> é descompactado em MAP<key, value>, e um qualificador de coluna ARRAY<STRUCT<timestamp, value>>> é descompactado em value. Outros tipos de coluna de entrada permanecem inalterados. As colunas precisam ser selecionadas na subconsulta para serem descompactadas e selecionadas. Não é necessário selecionar a nova coluna _timestamp para que os carimbos de data/hora sejam descompactados.

Expandindo o exemplo de série temporal em Consultar dados de séries temporais, e usando a consulta nessa seção como entrada, sua consulta UNPACK é formatada assim:

  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 retorna os dados neste formato:

Consultar JSON

Com as funções JSON, é possível manipular dados JSON armazenados como valores do Bigtable para cargas de trabalho operacionais.

Por exemplo, é possível recuperar o valor do elemento JSON abc da célula mais recente na família de colunas session junto com a chave de linha usando a consulta a seguir.

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

Fazer o escape de caracteres especiais e palavras reservadas

O Bigtable oferece alta flexibilidade na nomeação de tabelas e colunas. Como resultado, nas suas consultas SQL, talvez seja necessário usar caracteres 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 ponto no nome da tabela.

  -- ERROR: Table name format not supported

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

No entanto, você pode resolver esse problema colocando os itens entre caracteres de crase (`).

  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, Python e Go permitem consultar dados com SQL usando a API executeQuery. Os exemplos a seguir mostram como emitir uma consulta e acessar os dados:

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

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

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

Uso do SELECT *

As consultas SELECT * podem apresentar erros temporários quando um grupo de colunas é adicionado ou excluído da tabela consultada. Por isso, para cargas de trabalho de produção, recomendamos que você especifique todos os IDs de família de colunas na consulta, em vez de usar SELECT *. Por exemplo, use SELECT cf1, cf2, cf3 em vez de SELECT *.

A seguir

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