Ejemplos de consultas de GoogleSQL para Bigtable

En los ejemplos de esta página se muestran patrones de consultas SQL para consultas de Bigtable comunes y avanzadas. Puedes ejecutar consultas de GoogleSQL en el editor de consultas de Bigtable Studio. También puedes ejecutar consultas con la biblioteca de 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 de ejemplo.

Patrones de consultas de SQL de Bigtable habituales

A continuación, se muestran ejemplos de consultas habituales de datos de Bigtable. Para ver ejemplos de consultas similares que llamen a la API Data de Bigtable, consulta Ejemplos de lectura y Usar filtros. Para ver ejemplos de consultas sobre claves de fila estructuradas, consulta Consultas de claves de fila estructuradas.

Recupera la última versión de todas las columnas de una clave de fila determinada.

  SELECT * FROM myTable WHERE _key = 'r1'

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

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

Recupera la versión más reciente de una columna concreta de una familia de columnas concreta 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 de un intervalo de claves 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 de varios intervalos de claves 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 de 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 de 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 de una familia de columnas de un prefijo de clave de fila.

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

Recupera las claves de las filas y las tres versiones más recientes de todas las columnas de una familia de columnas de 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 alto rendimiento.

  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 alto rendimiento, a menos que también proporcione un prefijo de clave de fila o un predicado de intervalo de claves 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 de contador superior a 123. No es necesario que conviertas los datos para hacer 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 de 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'

Categoriza una fila determinada en función del valor de una columna concreta. Esta consulta es similar a usar un filtro condicional compuesto en la API 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 de una familia de columnas específica para un intervalo de claves de fila determinado. En SQL, las familias de columnas se representan mediante el tipo de datos de mapa, donde cada calificador y valor de columna se asigna como un par clave-valor. Esta consulta SQL es similar a usar un filtro de valor de cadena en la API 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 serie temporal tabular, lo que resulta útil al realizar análisis de series temporales. Supongamos que tienes una columna clicks en una familia de columnas engagement. La siguiente consulta usa UNPACK para ver el rendimiento de determinadas campañas agregando los clics de 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 de SQL de Bigtable avanzados

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

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

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

Con la siguiente consulta, puede obtener la clave de fila y calcular la duración media de la sesión mediante el valor más reciente de dos celdas agregadas de Bigtable, que son numéricas, de cada fila de la tabla.

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

Con la siguiente consulta, puede obtener la versión más reciente de todas las columnas de un prefijo de clave de fila determinado si la familia de columnas session contiene referrer, origin o server como calificador de columna. También se puede escribir esta consulta 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 un gran número de comparaciones, se recomienda el siguiente método.

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

Con la siguiente consulta, puede obtener la versión más reciente de todas las columnas de un prefijo de clave de fila determinado si la familia de columnas session contiene referrer, origin y server como calificadores de columna. También se puede escribir esta consulta 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, puede obtener la versión más reciente de todas las columnas de 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, puede obtener 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 obtener las lecturas de row key y temperature de los sensores meteorológicos en los casos en los que la temperatura haya superado 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 es el último, por lo que una consulta como after => X, before => y, latest_n => 3 devuelve los tres valores más recientes que cumplen las condiciones after y before. Si tu caso práctico requiere que latest_n tenga prioridad, puedes proporcionar latest_n como único filtro temporal y, a continuación, aplicar el resto de los filtros temporales mediante operadores de consulta en tu instrucción SELECT, tal 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)

Al igual que en el ejemplo anterior, puedes aplicar un filtro temporal diferente a cada familia de columnas de tu consulta. Por ejemplo, la siguiente consulta devuelve 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, puede recuperar todas las versiones de todas las columnas si los pares clave-valor de la familia de columnas de direcciones 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 concreto, no es necesario convertir el tipo, por lo que también se puede escribir de la siguiente forma, que es más breve.

  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

Siguientes pasos