Exemplos de consultas do GoogleSQL para Bigtable

Observação: esta página inclui recursos disponíveis apenas na edição Enterprise Plus, como o Data Boost para SQL. Para mais informações, consulte Visão geral das edições.

Os exemplos nesta página demonstram padrões de consulta SQL para consultas comuns e avançadas do Bigtable. É possível executar consultas do GoogleSQL no editor de consultas do Bigtable Studio. Também é possível executar consultas usando a biblioteca de cliente do Bigtable para Java.

Antes de ler esta página, consulte a visão geral do GoogleSQL para Bigtable.

Observação: uma versão se refere a um valor com carimbo de data/hora para uma determinada coluna e linha. Cada versão é armazenada em uma célula. Para mais informações, consulte Modelo de armazenamento do Bigtable.

Os exemplos nesta página usam IDs e valores semelhantes aos de Dados para exemplos.

Padrões comuns de consulta SQL do Bigtable

Confira abaixo exemplos de consultas comuns para dados do Bigtable. Para ver exemplos de consultas semelhantes que chamam a API Bigtable Data, consulte Exemplos de leitura e Usar filtros. Para exemplos de consultas em chaves de linha estruturadas, consulte Consultas chave de linha estruturadas.

Recupera a versão mais recente de todas as colunas de uma determinada chave de linha.

  SELECT * FROM myTable WHERE _key = 'r1'

Recupera todas as versões de todas as colunas para uma determinada chave de linha.

  SELECT * FROM myTable(with_history => TRUE) WHERE _key = 'r1'

Recupera a versão mais recente de uma coluna específica de uma família de colunas específica para uma determinada chave de linha.

  SELECT stats_summary['os_build'] AS os
  FROM analytics
  WHERE _key = 'phone#4c410523#20190501'

Recupere as chaves de linha e a versão mais recente de várias colunas para um determinado intervalo de chave de linha.

  SELECT
    _key,
    stats_summary['os_build'] AS os,
    stats_summary['user_agent'] AS agent
  FROM analytics
  WHERE _key >= 'phone#4c410523#20190501' AND _key < 'phone#4c410523#201906201'

Recupera todas as versões de todas as colunas para vários intervalos de chave de linha, até 10 linhas.

  SELECT *
  FROM analytics(with_history => TRUE)
  WHERE
    (_key >= 'phone#4c410523#20190501' AND _key < 'phone#4c410523#201906201')
    OR (_key >= 'phone#5c10102#20190501' AND _key < 'phone#5c10102#20190601')
  LIMIT 10

Recupera todas as versões de todas as colunas para várias chaves de linha.

  SELECT *
  FROM analytics(with_history => TRUE)
  WHERE _key = 'phone#4c410523#20190501' OR _key = 'phone#4c410523#20190502'

Recupere todas as versões de todas as colunas para várias chaves de linha usando uma abordagem diferente.

  SELECT *
  FROM analytics(with_history => TRUE)
  WHERE _key IS IN ('phone#4c410523#20190501', 'phone#4c410523#20190502')

Recupera a versão mais recente de todas as colunas em um grupo de colunas para um prefixo de chave de linha.

  SELECT stats_summary
  FROM analytics
  WHERE _key LIKE 'phone#%'

Recupera as chaves de linha e as três versões mais recentes de todas as colunas em um grupo de colunas para todas as linhas da tabela. Essa consulta exige uma verificação completa da tabela. Portanto, não é recomendada para padrões de acesso de baixa latência e alta capacidade de processamento.

  SELECT _key, cell_plan FROM analytics(with_history => TRUE, latest_n => 3)

Recupera a versão mais recente de todas as colunas com chaves de linha que correspondem a uma expressão regular especificada. Essa consulta exige uma verificação completa da tabela. Portanto, ela não é recomendada para padrões de acesso de baixa latência e alta capacidade de transferência, a menos que você também forneça um prefixo de chave de linha ou um predicado de intervalo de chaves de linha na cláusula WHERE.

  SELECT *
  FROM myTable(with_history => TRUE)
  WHERE REGEXP_CONTAINS(_key, '.*#20190501$')

Recupere a versão mais recente de todas as colunas com o prefixo de chave de linha correspondente e o valor do contador maior que 123. Não é necessário fazer a conversão para essa comparação, porque os agregadores do Bigtable são numéricos.

  SELECT *
  FROM myTable
  WHERE _key LIKE 'user12%' AND counterFamily['counter'] > 123

Recupere a versão mais recente de todas as colunas para um prefixo de chave de linha se o referenciador corresponder a um valor específico.

  SELECT *
  FROM analytics
  WHERE _key LIKE 'com.mysite%' AND session['referrer'] = './home'

Categorizar uma determinada linha com base no valor de uma determinada coluna. Essa consulta é semelhante ao uso de um filtro condicional de composição na API Bigtable Data.

  SELECT
    *,
    CASE cell_plan['data_plan']
      WHEN '10gb' THEN 'passed-filter'
      ELSE 'filtered-out'
      END
      AS label
  FROM analytics

Recupere a chave de linha e os qualificadores de coluna em um grupo de colunas específico para um intervalo de chaves de linha especificado. Em SQL, os grupos de colunas são representados pelo tipo de dados de mapa, em que cada qualificador de coluna e valor é mapeado como um par de chave-valor. Essa consulta SQL é semelhante ao uso de um filtro de valor de remoção na API Bigtable Data.

  SELECT _key, MAP_KEYS(cell_plan) AS keys
  FROM analytics
  WHERE _key >= 'phone#4c410523#20190501' AND _key < 'phone#4c410523#201906201'

A função UNPACK permite transformar dados do Bigtable em um formato de série temporal tabular, o que é útil ao realizar análises de série temporal. Considere um exemplo em que você tem uma coluna clicks em um grupo de colunas engagement. A consulta a seguir usa UNPACK para mostrar a performance de determinadas campanhas agregando os cliques em um minuto da última hora.

  SELECT
    FORMAT_TIMESTAMP('%M', _timestamp) AS minute,
    COUNT(clicks) AS total_clicks
  FROM
    UNPACK((
      SELECT engagement['clicks'] as clicks
      FROM metrics(with_history => true, after => TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 1 HOUR))
      WHERE _key = @campaign_id
    ))
  GROUP BY
    minute;

Padrões avançados de consulta SQL do Bigtable

Os exemplos a seguir demonstram padrões mais avançados.

Com a consulta a seguir, é possível recuperar a chave de linha e o valor mais recente do atributo JSON abc no grupo de colunas session. Para mais informações, consulte JSON functions.

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

Com a consulta a seguir, é possível recuperar a chave de linha e calcular a duração média da sessão usando o valor mais recente de duas células agregadas do Bigtable, que são numéricas, para cada linha da tabela.

  SELECT
    _key AS userid,
    session['total_minutes'] / session['count'] AS avg_session_length
  FROM analytics

Com a consulta a seguir, é possível recuperar a versão mais recente de todas as colunas para um determinado prefixo de chave de linha se o grupo de colunas session contiver referrer, origin ou server como um qualificador de coluna. Como alternativa, essa consulta também pode ser escrita como uma série de comparações individuais, como session['referrer'] IS NOT NULL OR session['origin'] IS NOT NULL. No entanto, para consultas que envolvem um grande número de comparações, recomendamos a seguinte abordagem.

  SELECT *
  FROM analytics
  WHERE
    _key LIKE 'com.abc%'
    AND ARRAY_INCLUDES_ANY(MAP_KEYS(session), ['referrer', 'origin', 'server'])

Com a consulta a seguir, é possível recuperar a versão mais recente de todas as colunas para um determinado prefixo de chave de linha se o grupo de colunas session contiverreferrer, origin e server como qualificadores de coluna. Como alternativa, essa consulta pode ser escrita como uma série de comparações individuais, como session['referrer'] IS NOT NULL AND session ['origin'] IS NOT NULL.

  SELECT *
  FROM analytics
  WHERE
    _key LIKE 'com.abc%'
    AND ARRAY_INCLUDES_ALL(MAP_KEYS(session), ['referrer', 'origin', 'server'])

Com a consulta a seguir, é possível recuperar a versão mais recente de todas as colunas para um determinado prefixo de chave de linha se o grupo de colunas session contiver com.google.search, com.google.maps ou com.google.shopping como valores.

  SELECT *
  FROM analytics
  WHERE
    _key LIKE 'com.abc%'
    AND ARRAY_INCLUDES_ANY(
      MAP_VALUES(session),
      ['com.google.search', 'com.google.maps', 'com.google.shopping'])

Com a consulta a seguir, é possível recuperar a versão mais recente de todas as colunas se os pares de chave-valor no grupo de colunas cell_plan incluírem data_plan:unlimited e roaming:North America.

  SELECT *
  FROM analytics
  WHERE
    ARRAY_INCLUDES_ALL(
      CAST(
        MAP_ENTRIES(cell_plan)
        AS ARRAY<STRUCT<key STRING, value STRING>>),
      [('data_plan', 'unlimited'), ('roaming', 'North America')])

Com a consulta a seguir, é possível recuperar as leituras de row key e temperature dos sensores meteorológicos para casos em que a temperatura excedeu 70 graus nas últimas sete medições.

  SELECT
    _key AS sensorid,
    ARRAY_FILTER(
      CAST(
        sensor['temperature']
        AS ARRAY<STRUCT<timestamp TIMESTAMP, value STRING>>),
      e -> CAST(e.value AS FLOAT32) > 70) AS high_temperature
  FROM weather(with_history => TRUE, latest_n => 7)

Na ordem de filtragem temporal, latest_n vem por último. Portanto, uma consulta como after => X, before => y, latest_n => 3 retorna os três valores mais recentes que atendem às condições "after" e "before". Se o caso de uso exigir que latest_n tenha precedência, forneça latest_n como o único filtro temporal e aplique o restante dos filtros temporais usando operadores de consulta na instrução SELECT, conforme mostrado no exemplo. Para mais informações, consulte Filtros temporais.

  SELECT
    ARRAY_FILTER(
      CAST(
        address['street']
        AS ARRAY<STRUCT<timestamp TIMESTAMP, value STRING>>),
      e -> e.timestamp > TIMESTAMP('2021-01-04T23:51:00.000Z'))
      AS street_address
  FROM locations(with_history => TRUE, latest_n => 3)

Assim como no exemplo anterior, é possível aplicar um filtro temporal diferente a cada grupo de colunas na consulta. Por exemplo, a consulta a seguir retorna as três versões mais recentes da coluna street e as duas versões menos recentes da coluna state.

  SELECT
    ARRAY_FILTER(
      CAST(
        address['street']
        AS ARRAY<STRUCT<timestamp TIMESTAMP, value STRING>>),
      (e, i) -> i <= 2)
      AS street_address,
    ARRAY_FILTER(
      ARRAY_REVERSE(
        CAST(
          address['state']
          AS ARRAY<STRUCT<timestamp TIMESTAMP, value STRING>>)),
      (e, i) -> i <= 1)
      AS state
  FROM locations(with_history => TRUE)

Com a consulta a seguir, é possível recuperar todas as versões de todas as colunas se os pares de chave-valor no grupo de colunas de endereço incluírem city:Savannah ou city:Nashville a qualquer momento.

  SELECT *
  FROM locations(with_history => TRUE)
  WHERE
    ARRAY_LENGTH(
      ARRAY_FILTER(
        CAST(
          MAP_ENTRIES(address)
          AS ARRAY<
            STRUCT<
              key STRING,
              value ARRAY<STRUCT<timestamp TIMESTAMP, value STRING>>>>),
        e ->
          e.key = 'city'
          AND ARRAY_INCLUDES_ANY(
            ARRAY_TRANSFORM(e.value, k -> k.value), ['Savannah', 'Nashville'])))
    > 0

Neste exemplo específico, a conversão não é necessária. Portanto, também é possível escrever da seguinte forma mais curta.

  SELECT *
  FROM locations(with_history => TRUE)
  WHERE
    ARRAY_LENGTH(
      ARRAY_FILTER(
        MAP_ENTRIES(address),
        e ->
          e.key = 'city'
          AND ARRAY_INCLUDES_ANY(
            ARRAY_TRANSFORM(e.value, k -> k.value), ['Savannah', 'Nashville'])))
    > 0

Análise de SQL de alta capacidade de processamento com o Data Boost

Na edição Enterprise Plus, para cargas de trabalho analíticas de alta capacidade de processamento, você pode usar o Data Boost para executar consultas que verificam grandes quantidades de dados sem afetar o desempenho dos clusters de produção. Essas consultas são ideais para análises em tempo real e geração de insights com dados históricos ou de séries temporais. Para executar essas consultas, use um perfil de aplicativo configurado para o Data Boost.

Os exemplos a seguir demonstram padrões de SQL para análise de alta capacidade usando computação sem servidor.

Analisar dados históricos de série temporal

Com o Data Boost, é possível recuperar as chaves de linha e todas as versões de métricas de todas as linhas de uma tabela das últimas 24 horas. A consulta a seguir realiza uma verificação completa da tabela, que é processada por uma computação isolada sem servidor.

SELECT _key, metrics
FROM myTable(with_history => TRUE, after => TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 24 HOUR))

Agregar grandes conjuntos de dados

A consulta a seguir mostra como calcular o número total de eventos para cada dispositivo em um período específico em toda a tabela.

SELECT
  _key AS device_id,
  COUNT(events['event_type']) AS total_events
FROM
  UNPACK((
    SELECT events['event_type']
    FROM sensor_data(with_history => TRUE, after => TIMESTAMP('2026-01-01T00:00:00Z'))
  ))
GROUP BY
  Device_id;

Verificações de grande intervalo

A consulta a seguir mostra como recuperar todas as colunas de um grande intervalo de chaves de linha. O Data Boost garante que essa solicitação de alto volume não consuma recursos de CPU nos nós do cluster.

SELECT *
FROM analytics
WHERE _key >= 'user#000000' AND _key < 'user#999999'

A seguir

• Consulte a documentação de referência do GoogleSQL para Bigtable.
• Use o conector do Bigtable Spark.
• Gerenciar esquemas de chave de linha.