Descripción general de GoogleSQL para Bigtable

Puedes usar instrucciones de GoogleSQL para consultar tus datos de Bigtable. GoogleSQL es un lenguaje de consulta estructurado (SQL) compatible con ANSI que también se implementa para 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 en 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, Python o Go. Para obtener más información, consulta Usa SQL con una biblioteca cliente de Bigtable.

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

No puedes usar Data Boost con GoogleSQL para Bigtable.

Casos de uso

GoogleSQL para Bigtable es óptimo para el desarrollo de aplicaciones de baja latencia. Además, ejecutar consultas de SQL en la consola deGoogle 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 posibles problemas de datos.

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

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

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

Vistas

Puedes usar GoogleSQL para Bigtable para crear los siguientes recursos:

• Vista materializada: Es un resultado calculado previamente de una consulta en SQL que se ejecuta de forma continua, incluidos los datos agregados, que se sincroniza con su tabla de origen con actualizaciones incrementales.
• Vista lógica: Es una consulta guardada y con nombre que se puede consultar como una tabla.

Para comparar estos tipos de vistas, así como las vistas autorizadas, consulta Tablas y vistas.

Conceptos clave

En esta sección, se analizan los conceptos clave que debes conocer 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 para esa columna.

A continuación, se muestra 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 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 devuelven como un array. En el array, cada valor es un mapa que consta de una marca de tiempo como clave y datos de la celda como valor. El formato es MAP<key, ARRAY<STRUCT<timestamp, value>>>.

En el siguiente ejemplo, se devuelven 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 tiene el siguiente aspecto. 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 de marca de tiempo y valor (STRUCT) en un array representa celdas en esas columnas de 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 una columna no se usa en una fila, no se almacenan datos para la columna. Una fila puede tener una columna y la siguiente puede tener 100. En cambio, en una tabla de base de datos relacional, todas las filas contienen todas las columnas, y un valor NULL se suele almacenar 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 no utilizada se representa con un mapa vacío y se devuelve como un valor NULL. Estos valores de NULL se pueden usar como predicados de búsqueda. Por ejemplo, un predicado como WHERE family['column1'] IS NOT NULL se puede usar para devolver una fila solo si se usa column1 en la fila.

Bytes

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

Dado 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 conversión, consulta Funciones de conversión.

Filtros temporales

En la siguiente tabla, se enumeran los argumentos que puedes usar cuando accedes a 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 consultas avanzadas.

Consultas básicas

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

Recupera la versión más reciente

Si bien Bigtable te permite almacenar varias versiones de datos en cada columna, GoogleSQL para Bigtable devuelve 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 dentro de 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 para user1, puedes usar una sentencia SELECT como la siguiente.

   SELECT street, city FROM myTable WHERE _key = 'user1'

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

Recupera 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 llevaron al estado actual de una fila, puedes recuperar las marcas de tiempo de cada valor recuperando el historial completo. Por ejemplo, para saber cuándo user1 se mudó a su dirección actual y de 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 devuelve 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 de forma cronológica inversa, por lo que los datos más recientes siempre son el primer elemento que se devuelve.

La respuesta de la búsqueda 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

Usar un filtro as_of te permite recuperar el estado de una fila en un momento determinado. Por ejemplo, si quieres saber la dirección de user el 10 de enero de 2022 a las 13:14 h, puedes ejecutar la siguiente consulta.

  SELECT address
  FROM myTable(as_of => TIMESTAMP('2022-01-10T13:14:00.234Z'))
  WHERE _key = 'user1'

El resultado muestra cuál habría sido la última dirección conocida el 10 de enero de 2022 a las 13:14 h, 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 lograr 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 una alarma de humo sonó 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 búsqueda.

  SELECT _key AS location, alarmType['carbonMonoxide'] AS CO_sensor
  FROM alarms(as_of => TIMESTAMP('2023-04-01T09:00:00.000Z'))
  WHERE _key LIKE 'building1%' and alarmType['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 marcas 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(with_history => true, after => TIMESTAMP('2023-01-04T23:00:00.000Z'),
         before => TIMESTAMP('2023-01-05T01:00:00.000Z'))
   WHERE _key LIKE 'sensorA%'

La consulta devuelve los datos en este formato:

UNPACK datos de series temporales

Cuando analizas datos de series temporales, suele ser preferible trabajar con los datos en formato tabular. La función UNPACK de Bigtable puede ayudarte.

UNPACK es una función con valor de tabla (TVF) de Bigtable que devuelve una tabla de salida completa en lugar de un solo valor escalar y aparece en la cláusula FROM como una subconsulta de tabla. La TVF UNPACK expande cada valor con marca de tiempo en varias filas (una por marca de tiempo) y mueve la marca de tiempo a la columna _timestamp.

La entrada para UNPACK es una subconsulta en la que with_history => true.

El resultado es una tabla expandida con una columna _timestamp en cada fila.

Una familia de columnas de entrada MAP<key, ARRAY<STRUCT<timestamp, value>>> se expande en MAP<key, value>, y un calificador de columna ARRAY<STRUCT<timestamp, value>>> se expande en value. Los demás tipos de columnas de entrada no se modifican. Las columnas deben seleccionarse en la subconsulta para poder desempacarse y seleccionarse. No es necesario seleccionar la nueva columna _timestamp para que se desempaqueten las marcas de tiempo.

Si ampliamos el ejemplo de series temporales en Consulta datos de series temporales y usamos la consulta de esa sección como entrada, tu consulta de UNPACK se formatea de la siguiente manera:

  SELECT temp_versioned, _timestamp
  FROM
  UNPACK((
    SELECT metrics['temperature'] AS temperature_versioned
    FROM
    sensorReadings(with_history => true, after => TIMESTAMP('2023-01-04T23:00:00.000Z'),
          before => TIMESTAMP('2023-01-05T01:00:00.000Z'))
    WHERE _key LIKE 'sensorA%'
  ));

La consulta devuelve los datos en este formato:

Consulta JSON

Las funciones JSON te permiten manipular datos JSON almacenados 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 en 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, es posible que los nombres de tus tablas deban incluir caracteres de escape en tus consultas de SQL debido a caracteres especiales o palabras reservadas.

Por ejemplo, la siguiente consulta no es válida en SQL 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 aplicar un escape de manera similar.

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

Usa SQL con una biblioteca cliente de Bigtable

Las bibliotecas cliente de Bigtable para Java, Python y Go 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:

  import (
    "cloud.google.com/go/bigtable"
  )

  func query(client *bigtable.Client) {
    // Prepare once for queries that will be run multiple times, and reuse
    // the PreparedStatement for each request. Use query parameters to
    // construct PreparedStatements that can be reused.
    ps, err := client.PrepareStatement(
      "SELECT cf1['bytesCol'] AS bytesCol, CAST(cf2['stringCol'] AS STRING) AS stringCol, cf3 FROM myTable WHERE _key=@keyParam",
      map[string]SQLType{
        "keyParam": BytesSQLType{},
      }
    )
    if err != nil {
      log.Fatalf("Failed to create PreparedStatement: %v", err)
    }

    // For each request, create a BoundStatement with your query parameters set.
    bs, err := ps.Bind(map[string]any{
      "keyParam": []byte("mykey")
    })
    if err != nil {
      log.Fatalf("Failed to bind parameters: %v", err)
    }

    err = bs.Execute(ctx, func(rr ResultRow) bool {
      var byteValue []byte
      err := rr.GetByName("bytesCol", &byteValue)
      if err != nil {
        log.Fatalf("Failed to access bytesCol: %v", err)
      }
      var stringValue string
      err = rr.GetByName("stringCol", &stringValue)
      if err != nil {
        log.Fatalf("Failed to access stringCol: %v", err)
      }
      // Note that column family maps have byte valued keys. Go maps don't support
      // byte[] keys, so the map will have Base64 encoded string keys.
      var cf3 map[string][]byte
      err = rr.GetByName("cf3", &cf3)
      if err != nil {
        log.Fatalf("Failed to access cf3: %v", err)
      }
      // Do something with the data
      // ...
      return true
    })
  }

  static void query(BigtableDataClient client) {
    // Prepare once for queries that will be run multiple times, and reuse
    // the PreparedStatement for each request. Use query parameters to
    // construct PreparedStatements that can be reused.
    PreparedStatement preparedStatement = client.prepareStatement(
      "SELECT cf1['bytesCol'] AS bytesCol, CAST(cf2['stringCol'] AS STRING) AS stringCol, cf3 FROM myTable WHERE _key=@keyParam",
      // For queries with parameters, set the parameter names and types here.
      Map.of("keyParam", SqlType.bytes())
    );

    // For each request, create a BoundStatement with your query parameters set.
    BoundStatement boundStatement = preparedStatement.bind()
      .setBytesParam("keyParam", ByteString.copyFromUtf8("mykey"))
      .build();

    try (ResultSet resultSet = client.executeQuery(boundStatement)) {
      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"])

Uso de SELECT *

Las consultas SELECT * pueden experimentar errores temporales cuando se agrega o borra una familia de columnas de la tabla consultada. Por este motivo, para las cargas de trabajo de producción, te recomendamos que especifiques todos los IDs de familia de columnas en tu consulta, en lugar de usar SELECT *. Por ejemplo, usa SELECT cf1, cf2, cf3 en lugar de SELECT *.

¿Qué sigue?

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