Ejemplos de consultas de GoogleSQL para Bigtable

En los ejemplos de esta página se muestran patrones de consulta en SQL para usos comunes y avanzados. en las consultas 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 consultas de 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 de 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'

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

  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 claves de filas, 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 nombre diferente enfoque.

  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, así que es no se recomienda para patrones de acceso con 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 correspondiente 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'

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

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

Recuperar la clave de fila y los calificadores de columna de 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 La consulta en SQL es similar a usar un valor para quitar filtro en la 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 de 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 el promedio la duración de la sesión con el valor más reciente de dos agregados de Bigtable celdas , que son numéricos, 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 última versión 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. 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. De manera alternativa, esta consulta se puede escrita 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 última versión 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, puedes recuperar la última versión de todas las columnas si Los pares clave-valor en la familia de columnas cell_plan incluyen ambos 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 row key y temperature. lecturas de los sensores meteorológicos para casos en los 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 queda en último lugar, por lo que una consulta como after => X, before => y, latest_n => 3 muestra los últimos tres valores que satisfacen la condiciones anteriores y posteriores. 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 Temporal filtros.

  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 para 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 manera 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?