Exemplos de consultas GoogleSQL para Bigtable

Os exemplos nesta página demonstram padrões de consultas SQL para consultas comuns e avançadas do Bigtable. Pode executar consultas GoogleSQL no editor de consultas do Bigtable Studio. Também pode executar consultas através da biblioteca cliente do Bigtable para Java.

Antes de ler esta página, leia a vista geral do GoogleSQL para o Bigtable.

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

Padrões de consultas SQL comuns do Bigtable

Seguem-se exemplos de consultas comuns para dados do Bigtable. Para ver exemplos de consultas semelhantes que chamam a Bigtable Data API, consulte os exemplos de leitura e use filtros. Para ver exemplos de consultas em chaves de linhas estruturadas, consulte o artigo Consultas de chaves de linhas estruturadas.

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

  SELECT * FROM myTable WHERE _key = 'r1'

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

Obtenha as chaves de linhas e a versão mais recente de várias colunas para um determinado intervalo de chaves de linhas.

  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'

Obtenha todas as versões de todas as colunas para vários intervalos de chaves de linhas, 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

Obter todas as versões de todas as colunas para várias chaves de linhas.

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

Obtenha todas as versões de todas as colunas para várias chaves de linhas através de uma abordagem diferente.

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

Obtenha a versão mais recente de todas as colunas numa família de colunas para um prefixo de chave de linha.

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

Obtenha as chaves das linhas e as três versões mais recentes de todas as colunas numa família de colunas para todas as linhas na tabela. Esta consulta requer uma análise completa da tabela, pelo que não é recomendada para padrões de acesso de baixa latência e elevado débito.

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

Obtenha a versão mais recente de todas as colunas com chaves de linhas que correspondam a uma expressão regular especificada. Esta consulta requer uma análise completa da tabela, pelo que não é recomendada para padrões de acesso de baixa latência e elevado débito, a menos que 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 da chave da linha correspondente e o valor do contador superior a 123. Não precisa de fazer a conversão para esta comparação, porque os agregados 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. Esta consulta é semelhante à utilização 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

Obter a chave da linha e os qualificadores de coluna numa família de colunas específica para um intervalo de chaves de linhas especificado. Em SQL, as famílias de colunas são representadas pelo tipo de dados de mapa, em que cada qualificador e valor de coluna é mapeado como um par de chave-valor. Esta consulta SQL é semelhante à utilização 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-lhe transformar dados do Bigtable num formato de série cronológica tabular, o que é útil quando realiza análises de séries cronológicas. Considere um exemplo em que tem uma coluna clicks numa família de colunas engagement. A seguinte consulta usa UNPACK para ver o desempenho de determinadas campanhas agregando os cliques durante 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 de consulta SQL do Bigtable avançados

Os exemplos seguintes demonstram padrões mais avançados.

Com a seguinte consulta, pode obter a chave da linha e o valor mais recente do atributo JSON abc na família de colunas session. Para mais informações, consulte as JSON funções.

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

Com a seguinte consulta, pode obter a chave da 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 na tabela.

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

Com a seguinte consulta, pode obter a versão mais recente de todas as colunas para um determinado prefixo de chave de linha se a família de colunas session contiver referrer, origin ou server como um qualificador de coluna. Em alternativa, esta 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 envolvam 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 seguinte consulta, pode obter a versão mais recente de todas as colunas para um determinado prefixo de chave de linha se a família de colunas session contiver referrer, origin e server como qualificadores de colunas. Em alternativa, esta 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 seguinte consulta, pode obter a versão mais recente de todas as colunas para um determinado prefixo de chave de linha se a família 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 seguinte consulta, pode obter a versão mais recente de todas as colunas se os pares de chave-valor na família 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 seguinte consulta, pode obter as leituras row key e temperature para sensores meteorológicos nos casos em que a temperatura excedeu os 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 aparece por último, pelo que uma consulta como after => X, before => y, latest_n => 3 devolve os três valores mais recentes que satisfazem as condições after e before. Se o seu exemplo de utilização exigir que latest_n tenha precedência, pode fornecer latest_n como o único filtro temporal e, em seguida, aplicar os restantes filtros temporais através de operadores de consulta na sua declaração SELECT, conforme mostrado no exemplo. Para mais informações, consulte a secção 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)

Tal como no exemplo anterior, pode aplicar um filtro temporal diferente a cada família de colunas na sua consulta. Por exemplo, a seguinte consulta devolve 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 seguinte consulta, pode obter todas as versões de todas as colunas se os pares de chave-valor na família de colunas de endereço incluírem city:Savannah ou city:Nashville em 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, pelo que também pode ser escrita na 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

O que se segue?