Descripción general de GoogleSQL para Bigtable

Puedes usar instrucciones de GoogleSQL para consultar tus data de Bigtable. GoogleSQL es un lenguaje de consulta estructurado (SQL) que cumple con ANSI y que también se implementa en otros servicios de Google Cloud, como BigQuery y Spanner.

En este documento, se proporciona una descripción general de GoogleSQL para Bigtable. Proporciona ejemplos de consultas de SQL que puedes usar con Bigtable y describe cómo se relacionan con un esquema de tabla de Bigtable. Antes de leer este documento, debes familiarizarte con el modelo de almacenamiento de Bigtable y los conceptos de diseño de esquemas.

Puedes crear y ejecutar consultas en Bigtable Studio en la consola de Google Cloud, o bien ejecutarlas de forma programática con la biblioteca cliente de Bigtable para Java. Para obtener más información, consulta Cómo usar SQL con una biblioteca cliente de Bigtable.

Los nodos del clúster controlan las consultas de SQL de la misma manera que las solicitudes de datos de NoSQL. Por lo tanto, se aplican las mismas prácticas recomendadas cuando se crean consultas de SQL para ejecutarse en tus datos de Bigtable, como evitar análisis completos de tablas o filtros complejos. Para obtener más información, consulta Lecturas y rendimiento.

No puedes usar Data Boost con Google SQL para Bigtable.

Casos de uso

GoogleSQL para Bigtable es óptimo para el desarrollo de aplicaciones de baja latencia. Además, ejecutar consultas SQL en la console de Google Cloud puede ser útil para obtener rápidamente una representación visual del esquema de una tabla, verificar que se hayan escrito ciertos datos o depurar posibles problemas de datos.

La versión actual de GoogleSQL para Bigtable no admite algunas construcciones comunes de SQL, incluidas, entre otras, las siguientes:

• Declaraciones de lenguaje de manipulación de datos (DML) más allá de SELECT, como INSERT, UPDATE o DELETE
• Sentencias de lenguaje de definición de datos (DDL), como CREATE, ALTER o DROP
• Declaraciones de control de acceso a los datos
• Sintaxis de consulta para subconsultas, JOIN, UNION, GROUP BY, UNNEST y CTEs

Para obtener más información, incluidas las funciones, los operadores, los tipos de datos y la sintaxis de consulta compatibles, consulta la documentación de referencia de GoogleSQL para Bigtable.

Conceptos clave

En esta sección, se analizan los conceptos clave que debes tener en cuenta cuando usas GoogleSQL para consultar tus datos de Bigtable.

Familias de columnas en las respuestas de SQL

En Bigtable, una tabla contiene una o más familias de columnas, que se usan para agrupar columnas. Cuando consultas una tabla de Bigtable con GoogleSQL, el esquema de la tabla consta de lo siguiente:

• Una columna especial llamada _key que corresponde a las claves de fila en la tabla consultada
• Una sola columna para cada familia de columnas de Bigtable en la tabla, que contiene los datos de la familia de columnas en esa fila

Tipo de datos del mapa

GoogleSQL para Bigtable incluye el tipo de datos MAP<key, value>, que está diseñado específicamente para admitir familias de columnas.

De forma predeterminada, cada fila de una columna de mapa contiene pares clave-valor, en los que una clave es el calificador de columna de Bigtable en la tabla consultada y el valor es el valor más reciente de esa columna.

El siguiente es un ejemplo de una consulta en SQL que muestra una tabla con el valor de clave de fila y el valor más reciente del calificador de un mapa llamado columnFamily.

  SELECT _key, columnFamily['qualifier'] FROM myTable

Si tu esquema de Bigtable implica almacenar varias celdas (o versiones de los datos) en columnas, puedes agregar un filtro temporal, como with_history, a tu instrucción de SQL.

En este caso, los mapas que representan familias de columnas se anidan y se muestran como un array. En el array, cada clave es un mapa que consta de una marca de tiempo como clave y los datos de la celda como valor. El formato es MAP<key, ARRAY<STRUCT<timestamp, value>>>.

En el siguiente ejemplo, se muestran todas las celdas de la familia de columnas "info" para una sola fila.

  SELECT _key, info FROM users(with_history => TRUE) WHERE _key = 'user_123';

El mapa que se muestra es similar al siguiente. En la tabla consultada, info es la familia de columnas, user_123 es la clave de fila y city y state son los calificadores de columna. Cada par marca de tiempo-valor (STRUCT) en un array representa las celdas de esas columnas en esa fila y se ordenan de forma descendente por marca de tiempo.

/*----------+------------------------------------------------------------------+
 |   _key   |                              info                                |
 +----------+------------------------------------------------------------------+
 | user_123 | {"city":{<t5>:"Brooklyn", <t0>:"New York"}, "state":{<t0>:"NY"}} |
 +----------+------------------------------------------------------------------*/

Tablas dispersas

Una característica clave de Bigtable es su modelo de datos flexible. En una tabla de Bigtable, si no se usa una columna en una fila, no se almacenan datos para la columna. Una fila puede tener una columna y la siguiente puede tener 100 columnas. Por el contrario, en una tabla de base de datos relacional, todas las filas contienen todas las columnas, y un valor NULL suele almacenarse en la columna de una fila que no tiene datos para esa columna.

Sin embargo, cuando consultas una tabla de Bigtable con GoogleSQL, una columna sin usar se representa con un mapa vacío y se muestra como un valor NULL. Estos valores de NULL se pueden usar como predicados de consulta. Por ejemplo, un predicado como WHERE family['column1'] IS NOT NULL se puede usar para mostrar una fila solo si se usa column1 en la fila.

Bytes

Cuando proporcionas una cadena, GoogleSQL convierte de forma implícita los valores de STRING a los valores de BYTES de forma predeterminada. Esto significa, por ejemplo, que puedes proporcionar la cadena 'qualifier' en lugar de la secuencia de bytes b'qualifier'.

Debido a que Bigtable trata todos los datos como bytes de forma predeterminada, la mayoría de las columnas de Bigtable no contienen información de tipo. Sin embargo, con GoogleSQL, puedes definir un esquema en el momento de la lectura con la función CAST. Para obtener más información sobre la transmisión, consulta Funciones de conversión.

Filtros temporales

En la siguiente tabla, se enumeran los argumentos que puedes usar cuando accedes a los elementos temporales de una tabla. Los argumentos se enumeran en el orden en que se filtran. Por ejemplo, with_history se aplica antes de latest_n. Debes proporcionar una marca de tiempo válida.

Para obtener más ejemplos, consulta Patrones de consulta avanzados.

Consultas fundamentales

En esta sección, se describen y muestran ejemplos de consultas básicas de SQL de Bigtable y cómo funcionan. Para obtener más consultas de muestra, consulta Ejemplos de patrones de consulta de GoogleSQL para Bigtable.

Cómo recuperar la versión más reciente

Aunque Bigtable te permite almacenar varias versiones de datos en cada columna, Google SQL para Bigtable muestra de forma predeterminada la versión más reciente (la celda más reciente) de los datos de cada fila.

Considera el siguiente conjunto de datos de muestra, que muestra que user1 se mudó dos veces en el estado de Nueva York y una vez en la ciudad de Brooklyn. En este ejemplo, address es la familia de columnas y los calificadores de columnas son street, city y state. Las celdas de una columna se separan por líneas vacías.

Para recuperar la versión más reciente de cada columna para user1, puedes usar una sentencia SELECT como la siguiente.

   SELECT * FROM myTable WHERE _key = 'user1'

La respuesta contiene la dirección actual, que es una combinación de los valores más recientes de la calle, la ciudad y el estado (escritos en diferentes momentos) impresos como JSON. Las marcas de tiempo no se incluyen en la respuesta.

Cómo recuperar todas las versiones

Para recuperar versiones anteriores (celdas) de los datos, usa la marca with_history. También puedes asignar alias a columnas y expresiones, como se ilustra en el siguiente ejemplo.

  SELECT _key, columnFamily['qualifier'] AS col1
  FROM myTable(with_history => TRUE)

Para comprender mejor los eventos que llevan al estado actual de una fila, puedes recuperar las marcas de tiempo de cada valor recuperando el historial completo. Por ejemplo, para comprender cuándo user1 se mudó a su dirección actual y desde dónde se mudó, puedes ejecutar la siguiente consulta:

  SELECT
    address['street'][0].value AS moved_to,
    address['street'][1].value AS moved_from,
    FORMAT_TIMESTAMP('%Y-%m-%d', address['street'][0].timestamp) AS moved_on,
  FROM myTable(with_history => TRUE)
  WHERE _key = 'user1'

Cuando usas la marca with_history en tu consulta en SQL, la respuesta se muestra como MAP<key, ARRAY<STRUCT<timestamp, value>>>. Cada elemento del array es un valor con marca de tiempo para la fila, la familia de columnas y la columna especificadas. Las marcas de tiempo se ordenan en orden cronológico inverso, por lo que los datos más recientes siempre son el primer elemento que se muestra.

La respuesta a la consulta es la siguiente.

También puedes recuperar la cantidad de versiones en cada fila con funciones de array, como se muestra en la siguiente consulta:

  SELECT _key, ARRAY_LENGTH(MAP_ENTRIES(address)) AS version_count
  FROM myTable(with_history => TRUE)

Cómo recuperar datos de un momento específico

El uso de un filtro as_of te permite recuperar el estado de una fila tal como estaba en un momento determinado. Por ejemplo, si quieres conocer la dirección de user al 10 de enero de 2022 a las 1:14 p.m., puedes ejecutar la siguiente consulta.

  SELECT address
  FROM myTable(as_of => TIMESTAMP('2022/01/10-13:14:00'))
  WHERE _key = 'user1'

El resultado muestra la última dirección conocida el 10 de enero de 2022 a la 1:14 p.m., que es la combinación de la calle y la ciudad de la actualización del 20/12/2021 a las 9:44:31.010 y el estado del 1/3/2005 a las 11:12:15.112.

También se puede obtener el mismo resultado con marcas de tiempo de Unix.

  SELECT address
  FROM myTable(as_of => TIMESTAMP_FROM_UNIX_MILLIS(1641820440000))
  WHERE _key = 'user1'

Considera el siguiente conjunto de datos, que muestra el estado de encendido o apagado de las alarmas de humo y monóxido de carbono. La familia de columnas es alarmType y los calificadores de columnas son smoke y carbonMonoxide. Las celdas de cada columna están separadas por líneas vacías.

Puedes encontrar secciones de building1 en las que se encendió una alarma de humo a las 9 a.m. del 1 de abril de 2023 y el estado de la alarma de monóxido de carbono en ese momento con la siguiente consulta.

  SELECT _key AS location, sensorType['carbonMonoxide'] AS CO_sensor
  FROM alarms(as_of => TIMESTAMP('2023/04/01-09:00:00.000'))
  WHERE _key LIKE 'building1%' and sensorType['smoke'] = 'on'

El resultado es el siguiente:

Consultar datos de series temporales

Un caso de uso común de Bigtable es el almacenamiento de datos de series temporales. Considera el siguiente conjunto de datos de muestra, que muestra las lecturas de temperatura y humedad de los sensores meteorológicos. El ID de la familia de columnas es metrics y los calificadores de columnas son temperature y humidity. Las celdas de una columna se separan con líneas vacías, y cada una representa una lectura del sensor con marca de tiempo.

Puedes recuperar un rango específico de valores de marca de tiempo con los filtros temporales after, before o after_or_equal. En el siguiente ejemplo, se usa after:

   SELECT metrics['temperature'] AS temp_versioned
   FROM
   sensorReadings(after => TIMESTAMP('2023/01/04-23:00:00'),
         before => TIMESTAMP('2023/01/05-01:00:00'))
   WHERE _key LIKE 'sensorA%'

La consulta muestra los datos en este formato:

Cómo consultar JSON

Las funciones JSON te permiten manipular JSON almacenado como valores de Bigtable para cargas de trabajo operativas.

Por ejemplo, puedes recuperar el valor del elemento JSON abc de la celda más reciente de la familia de columnas session junto con la clave de fila con la siguiente consulta.

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

Cómo escapar caracteres especiales y palabras reservadas

Bigtable ofrece una gran flexibilidad para nombrar tablas y columnas. Como resultado, en tus consultas SQL, es posible que debas escapar los nombres de las tablas debido a caracteres especiales o palabras reservadas.

Por ejemplo, la siguiente consulta no es SQL válida debido al punto en el nombre de la tabla.

  -- ERROR: Table name format not supported

  SELECT * FROM my.table WHERE _key = 'r1'

Sin embargo, puedes resolver este problema si encierras los elementos entre caracteres de acento grave (`).

  SELECT * FROM `my.table` WHERE _key = 'r1'

Si se usa una palabra clave reservada de SQL como identificador, se puede escapar de manera similar.

  SELECT * FROM `select` WHERE _key = 'r1'

Usa SQL con una biblioteca cliente de Bigtable

Las bibliotecas cliente de Bigtable para Java y Python admiten la consulta de datos con SQL a través de la API de executeQuery. En los siguientes ejemplos, se muestra cómo enviar una consulta y acceder a los datos:

  static void query(BigtableDataClient client) {
    try (ResultSet resultSet =
        client.executeQuery(
            Statement.of(
                "SELECT cf1['bytesCol'] AS bytesCol, CAST(cf2['stringCol'] AS STRING) AS stringCol, cf3 FROM myTable WHERE _key='mykey'"))) {
      while (resultSet.next()) {
        ByteString byteValue = resultSet.getBytes("bytesCol");
        String stringValue = resultSet.getString("stringCol");
        Map<ByteString, ByteString> cf3Value =
            resultSet.getMap("cf3", SqlType.mapOf(SqlType.bytes(), SqlType.bytes()));
        // Do something with the data
      }
    }
  }

  from google.cloud.bigtable.data import BigtableDataClientAsync

  async def execute_query(project_id, instance_id, table_id):
      async with BigtableDataClientAsync(project=project_id) as client:
          query = (
            "SELECT cf1['bytesCol'] AS bytesCol, CAST(cf2['stringCol'] AS STRING) AS stringCol,"
            " cf3 FROM {table_id} WHERE _key='mykey'"
          )
          async for row in await client.execute_query(query, instance_id):
            print(row["_key"], row["bytesCol"], row["stringCol"], row["cf3"])

¿Qué sigue?

• Explora la documentación de referencia de GoogleSQL para Bigtable.
• Obtén más información sobre Bigtable Studio.