Exemplos de consulta do GoogleSQL para Bigtable

Os exemplos desta página demonstram padrões de consulta SQL para consultas comuns e avançadas da 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, leia 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 o modelo de armazenamento do Bigtable.

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

Padrões de consulta SQL comuns do Bigtable

Confira a seguir exemplos de consultas comuns para dados do Bigtable. Para conferir exemplos de consultas semelhantes que chamam a API Bigtable Data, consulte Exemplos de leitura e Usar filtros.

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

  SELECT * FROM myTable WHERE _key = 'r1'

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

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

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

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

Extraia 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'

Extraia 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

Extraia 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'

Extraia 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')

Extrair 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#%'

Extraia 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 na tabela. Essa consulta requer uma verificação completa da tabela. Portanto, ela 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)

Extrai 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 alto rendimento, 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$')

Extrai 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 converter para essa comparação, porque os agregados da Bigtable são numéricos.

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

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

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

Categorizar uma linha com base no valor de uma coluna. Essa consulta é semelhante ao uso de um filtro condicional composto na API Bigtable Data.

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

Extraia a chave de linha e os qualificadores de coluna em um grupo de colunas específico para um intervalo de chave de linha especificado. No SQL, os grupos de colunas são representados pelo tipo de dados do 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 retirada na API Bigtable Data.

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

Padrões de consulta SQL avançados 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 Funções JSON.

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

Com a consulta a seguir, você pode recuperar a chave de linha e calcular a duração média da sessão usando o valor mais recente de duas células de agregação da Bigtable, que são numéricas, para cada linha na 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 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 abordagem a seguir.

  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 de um determinado prefixo de chave de linha se o grupo de colunas session contiver referrer, 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 para sensores meteorológicos nos 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 "depois" e "antes". 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 temporários 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-04 23:51:00+00'))
      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 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 atribuição não é necessária, então ele também pode ser escrito no seguinte formato mais curto.

  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

• Procure a documentação de referência do GoogleSQL para Bigtable.
• Use o conector do Bigtable Spark.