Exemplos de consultas do GoogleSQL para Bigtable

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.

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 chave de linha na cláusula WHERE.

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

Recupera 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 e valor de coluna é 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. Outra opção é escrever essa consulta 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 durante as ú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 família 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

A seguir