Ejemplos de consultas de GoogleSQL para Bigtable

En los ejemplos de esta página, se muestran patrones de consulta en SQL para consultas comunes y avanzadas de Bigtable. Puedes ejecutar consultas de GoogleSQL en el editor de consultas de Bigtable Studio. También puedes ejecutar consultas con la biblioteca cliente de Bigtable para Java.

Antes de leer esta página, consulta la descripción general de GoogleSQL para Bigtable.

En los ejemplos de esta página, se usan IDs y valores similares a los de Datos para ejemplos.

Patrones de consulta en SQL comunes de Bigtable

Los siguientes son ejemplos de consultas comunes para datos de Bigtable. Para ver ejemplos de consultas similares que llaman a la API de Bigtable Data, consulta Ejemplos de lectura y Usa filtros. Para ver ejemplos de consultas sobre claves de fila estructuradas, consulta Consultas de clave de fila estructuradas.

Recupera la versión más reciente de todas las columnas para una clave de fila determinada.

  SELECT * FROM myTable WHERE _key = 'r1'

Recupera todas las versiones de todas las columnas para una clave de fila determinada.

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

Recupera la versión más reciente de una columna en particular de una familia de columnas en particular para una clave de fila determinada.

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

Recupera las claves de fila y la versión más reciente de varias columnas para un rango de clave de fila determinado.

  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 las versiones de todas las columnas para varios rangos de clave de fila, hasta 10 filas.

  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 las versiones de todas las columnas para varias claves de fila.

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

Recupera todas las versiones de todas las columnas para varias claves de fila con un enfoque diferente.

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

Recupera la versión más reciente de todas las columnas dentro de una familia de columnas para un prefijo de clave de fila.

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

Recupera las claves de fila y las tres versiones más recientes de todas las columnas dentro de una familia de columnas para todas las filas de la tabla. Esta consulta requiere un análisis completo de la tabla, por lo que no se recomienda para patrones de acceso de baja latencia y alta capacidad de procesamiento.

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

Recupera la versión más reciente de todas las columnas con claves de fila que coincidan con una expresión regular especificada. Esta consulta requiere un análisis completo de la tabla, por lo que no se recomienda para patrones de acceso de baja latencia y alta capacidad de procesamiento, a menos que también proporciones un prefijo de clave de fila o un predicado de rango de clave de fila en la cláusula WHERE.

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

Recupera la versión más reciente de todas las columnas con el prefijo de clave de fila coincidente y el valor del contador superior a 123. No es necesario que realices una conversión para esta comparación, ya que los agregados de Bigtable son numéricos.

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

Recupera la versión más reciente de todas las columnas para un prefijo de clave de fila si el referente coincide con un valor específico.

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

Clasifica una fila determinada según el valor de una columna determinada. Esta consulta es similar a usar un filtro condicional de composición en la API de Bigtable Data.

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

Recupera la clave de fila y los calificadores de columna en una familia de columnas específica para un rango de claves de fila especificado. En SQL, las familias de columnas se representan con el tipo de datos de mapa, en el que cada calificador de columna y valor se asigna como un par clave-valor. Esta consulta en SQL es similar a usar un filtro de valor de eliminación en la API de Bigtable Data.

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

La función UNPACK te permite transformar datos de Bigtable en un formato de series temporales tabulares, lo que resulta útil cuando se realiza un análisis de series temporales. Considera un ejemplo en el que tienes una columna clicks en una familia de columnas engagement. La siguiente consulta usa UNPACK para ver el rendimiento de ciertas campañas mediante la agregación de los clics durante un minuto de la ú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;

Patrones de consulta en SQL avanzadas de Bigtable

En los siguientes ejemplos, se muestran patrones más avanzados.

Con la siguiente consulta, puedes recuperar la clave de fila y el valor más reciente del atributo JSON abc en la familia de columnas session. Para obtener más información, consulta las funciones.JSON

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

Con la siguiente consulta, puedes recuperar la clave de fila y calcular la duración promedio de la sesión con el valor más reciente de dos celdas agregadas de Bigtable , que son numéricas, para cada fila de la tabla.

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

Con la siguiente consulta, puedes recuperar la versión más reciente de todas las columnas para un prefijo de clave de fila determinado si la familia de columnas session contiene referrer, origin o server como calificador de columna. Como alternativa, esta consulta también se puede escribir como una serie de comparaciones individuales, como session['referrer'] IS NOT NULL OR session['origin'] IS NOT NULL. Sin embargo, para las consultas que implican una gran cantidad de comparaciones, se recomienda el siguiente enfoque.

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

Con la siguiente consulta, puedes recuperar la versión más reciente de todas las columnas para un prefijo de clave de fila determinado si la familia de columnas session contiene referrer, origin y server como calificadores de columna. Como alternativa, esta consulta se puede escribir como una serie de comparaciones individuales, 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'])

Con la siguiente consulta, puedes recuperar la versión más reciente de todas las columnas para un prefijo de clave de fila determinado si la familia de columnas session contiene com.google.search, com.google.maps o 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'])

Con la siguiente consulta, puedes recuperar la versión más reciente de todas las columnas si los pares clave-valor de la familia de columnas cell_plan incluyen data_plan:unlimited y 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')])

Con la siguiente consulta, puedes recuperar la row key y las lecturas de temperature para los sensores meteorológicos en los casos en que la temperatura superó los 70 grados durante las últimas siete mediciones.

  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)

En el orden de filtrado temporal, latest_n aparece en último lugar, por lo que una consulta como after => X, before => y, latest_n => 3 muestra los tres valores más recientes que satisfacen las condiciones after y before. Si tu caso de uso requiere que latest_n tenga prioridad, puedes proporcionar latest_n como el único filtro temporal y, luego, aplicar el resto de los filtros temporales con operadores de consulta en tu instrucción SELECT, como se muestra en el ejemplo. Para obtener más información, consulta Filtros temporales.

  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)

De manera similar al ejemplo anterior, puedes aplicar un filtro temporal diferente a cada familia de columnas en tu consulta. Por ejemplo, la siguiente consulta muestra las tres versiones más recientes de la columna street y las dos versiones menos recientes de la columna 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)

Con la siguiente consulta, puedes recuperar todas las versiones de todas las columnas si los pares clave-valor de la familia de columnas de dirección incluyen city:Savannah o city:Nashville en cualquier 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

En este ejemplo en particular, no se requiere la conversión, por lo que también se puede escribir de la siguiente forma más corta.

  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álisis de SQL de alta capacidad de procesamiento con Data Boost

En la edición Enterprise Plus, para cargas de trabajo analíticas de alta capacidad de procesamiento, puedes usar Data Boost para ejecutar consultas que analicen grandes cantidades de datos sin afectar el rendimiento de tus clústeres de producción. Estas consultas son óptimas para el análisis en tiempo real y la generación de estadísticas a partir de datos históricos o de series temporales. Para ejecutar estas consultas, debes usar un perfil de aplicación configurado para Data Boost.

En los siguientes ejemplos, se muestran patrones de SQL para el análisis de alta capacidad de procesamiento con la computación sin servidores.

Analiza datos históricos de series temporales

Con Data Boost, puedes recuperar las claves de fila y todas las versiones de las métricas para todas las filas de una tabla de las últimas 24 horas. La siguiente consulta realiza un análisis completo de la tabla, que se controla mediante una computación sin servidores aislada.

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

Agrega conjuntos de datos grandes

En la siguiente consulta, se muestra cómo calcular la cantidad total de eventos para cada dispositivo durante un período específico en toda la tabla.

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;

Análisis de rangos grandes

En la siguiente consulta, se muestra cómo recuperar todas las columnas para un rango grande de claves de fila. El uso de Data Boost garantiza que esta solicitud de alta capacidad de procesamiento no consuma recursos de CPU en los nodos del clúster.

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

¿Qué sigue?