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 los ejemplos.

Patrones comunes de consulta en SQL de Bigtable

Los siguientes son ejemplos de consultas comunes para los datos de Bigtable. Para ver ejemplos de consultas similares que llaman a la API de Bigtable Data, consulta Lee ejemplos y Usa filtros.

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 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 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 productividad, 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 un valor de contador superior a 123. No necesitas transmitir esta comparación, ya que los agrupamientos 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'

Categorizar una fila determinada en función del valor de una columna determinada Esta consulta es similar al uso de 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 clave de fila especificado. En SQL, las familias de columnas se representan con el tipo de datos del mapa, en el que cada calificador y valor de columna 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'

Patrones de consulta en SQL avanzados 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 células 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 involucran 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 las lecturas de row key y temperature de los sensores meteorológicos para 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 después y antes. 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 sentencia 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-04 23:51:00+00'))
      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 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 el casting, por lo que también se puede escribir en 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

¿Qué sigue?

• Explora la documentación de referencia de GoogleSQL para Bigtable.
• Usa el conector de Bigtable Spark.