GoogleSQL para Bigtable resumen

Puedes usar las instrucciones de GoogleSQL para consultar tus en los datos de Bigtable. GoogleSQL es una base de datos un lenguaje de consulta estructurado (SQL) que también se implementa para otros servicios de Google Cloud servicios 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 manera 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.

Casos de uso

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

La versión actual de GoogleSQL para Bigtable no admite las construcciones comunes de SQL, incluidos, sin limitaciones, lo siguiente:

• Declaraciones de lenguaje de manipulación de datos (DML) más allá de SELECT, como INSERT, UPDATE o DELETE
• Declaraciones 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 admitidas, los operadores, los tipos de datos y sintaxis de consulta, revisa GoogleSQL para Bigtable referencia documentación de Google Cloud.

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 el archivo consultado tabla
• 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>, diseñado específicamente para adaptarse a 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 devuelve una tabla con el valor de la clave de fila y el valor más reciente del calificador de un mapa denominado 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 sentencia SQL.

En este caso, los mapas que representan familias de columnas se anidan y se muestran como un . 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 en el campo “info” familia de columnas 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 la familia de columnas, user_123 es la clave de fila y city y state son la columna calificadores. Cada par marca de tiempo-valor (STRUCT) de un array representa celdas en esas columnas en esa fila, y se ordenan de forma descendente según la 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 una columna en una fila no se usa, no se almacenan datos para la columna. Una fila puede tener una columna y la siguiente fila 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.

Cuando consultas una tabla de Bigtable con GoogleSQL, sin embargo, una columna sin usar se representa con un mapa vacío y se devuelve como Valor NULL. Estos valores 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 Proporciona 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 En GoogleSQL, puedes definir un esquema en el momento de la lectura con el CAST . Para obtener más información sobre la transmisión, consulta el artículo Conversión funciones.

Filtros temporales

En la siguiente tabla, se enumeran los argumentos que puedes usar cuando accedes 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 básicas

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.

Recupera la versión más reciente

Aunque Bigtable te permite almacenar varias versiones de datos en cada columna, GoogleSQL 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 están separadas por líneas vacías.

Para recuperar la versión más reciente de cada columna de user1, puedes usar un 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 recientes de calles, ciudades y estados (escritos en diferentes horarios) impresos como JSON Las marcas de tiempo no se incluyen en la respuesta.

Recuperar todas las versiones

Para recuperar versiones anteriores (celdas) de los datos, usa la marca with_history. Tú también puede 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 si recuperas el historial completo. Para ejemplo, para comprender cuándo user1 se mudó a su dirección actual y dónde podrías 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 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 forma cronológica inversa, por lo que los datos más recientes siempre es el primer elemento devuelto.

La respuesta a la consulta es la siguiente.

También puedes recuperar la cantidad de versiones en cada fila con las 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)

Recupera datos de un momento específico

El uso de un filtro as_of te permite recuperar el estado de una fila como estaba en un en un momento determinado. Por ejemplo, si quieres saber la dirección de user como del 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 cuál habría sido la última dirección conocida el 10 de enero, de 2022 1:14 p.m., que es la combinación de calle y ciudad de la 2021/12/20-09:44:31.010 y el estado de 2005/03/01-11:12:15.112.

El mismo resultado también se puede lograr 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 en cada columna están separadas por líneas vacías.

Puedes encontrar secciones de building1 en las que la alarma de humo estuvo activada a las 9 a.m. en abril de 2023 y el estado de la alarma de monóxido de carbono en ese momento usando el 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 están separadas por líneas vacías, y cada celda 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 en SQL, es posible que debas escapar los nombres de tus tablas 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 consultas datos con SQL usando la API de executeQuery. Los siguientes ejemplos muestran cómo emitir 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 GoogleSQL para Bigtable documentación de referencia.
• Obtén más información sobre Bigtable Studio.