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. Você pode executar consultas 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.

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

Padrões comuns de consulta SQL do Bigtable

Confira a seguir exemplos de consultas comuns para dados do Bigtable. Para ver exemplos de consultas semelhantes que chamam os registros de dados do Bigtable a API, consulte Leia exemplos e Use filtros.

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

Extrai 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 chaves 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 chaves 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

Recupere 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 diversas chaves de linha usando uma abordagem humilde.

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

Recuperar a versão mais recente de todas as colunas em um grupo de colunas para uma 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 de 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 para 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 determinada linha com base no valor de uma determinada 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

Recuperar a chave de linha e os qualificadores de coluna em um grupo de colunas específico para uma em 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. Isso A consulta SQL é semelhante ao uso de um valor de remoção filtro no 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 JSON de comando.

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

Com a consulta a seguir, você pode recuperar a chave da 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, você pode recuperar a versão mais recente de todas as colunas para um 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, a abordagem a seguir é recomendada.

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

Com a consulta a seguir, você pode recuperar a versão mais recente de todas as colunas para um prefixo de chave de linha se o grupo de colunas session contiver referrer; origin e server como qualificadores de coluna. Como alternativa, esta consulta pode ser escrito 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, você pode recuperar a versão mais recente de todas as colunas para um prefixo de chave de linha fornecido 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 no grupo de colunas cell_plan incluem tanto 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 últimos valores que satisfazem a antes e depois das condições. 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 o 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ços incluem 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 em particular, a transmissão não é necessária, então isso também pode ser gravado no formato mais curto a seguir.

  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