Información general sobre GoogleSQL para Bigtable

Puede usar instrucciones de GoogleSQL para consultar sus datos de Bigtable. GoogleSQL es un lenguaje de consulta estructurado (SQL) que cumple el estándar ANSI y que también se ha implementado en otros servicios, como BigQuery y Spanner. Google Cloud

En este documento se ofrece una descripción general de GoogleSQL para Bigtable. En este artículo se ofrecen ejemplos de consultas de SQL que puedes usar con Bigtable y se 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 Google Cloud consola o hacerlo de forma programática con la biblioteca de cliente de Bigtable para Java, Python o Go. Para obtener más información, consulta Usar SQL con una biblioteca de cliente de Bigtable.

Las consultas de SQL se gestionan mediante nodos de clúster de la misma forma que las solicitudes de datos NoSQL. Por lo tanto, se aplican las mismas prácticas recomendadas al crear consultas de SQL para ejecutarlas en tus datos de Bigtable, como evitar los análisis completos de tablas o los filtros complejos. Para obtener más información, consulta Lecturas y rendimiento.

No puedes usar Data Boost con GoogleSQL para Bigtable.

Casos prácticos

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

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

• Declaraciones de lenguaje de manipulación de datos (DML) que no sean SELECT, como INSERT, UPDATE o DELETE
• Instrucciones del lenguaje de definición de datos (DDL), como CREATE, ALTER o DROP
• Instrucciones de control de acceso a datos
• Sintaxis de consulta para subconsultas, JOIN, UNION y CTEs

Para obtener más información, como 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

Puede usar GoogleSQL para Bigtable para crear los siguientes recursos:

• Vista materializada continua: resultado precalculado de una consulta de SQL que se ejecuta continuamente, incluidos los datos agregados, que se sincroniza con su tabla de origen mediante actualizaciones incrementales. Esta función está en versión preliminar.
• Vista lógica: una consulta guardada 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 describen los conceptos clave que debes tener en cuenta al usar GoogleSQL para consultar tus datos de Bigtable.

Familias de columnas en 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 las filas de la tabla consultada.
• Una sola columna por cada familia de columnas de Bigtable de la tabla, que contiene los datos de la familia de columnas de esa fila

Tipo de datos de mapa

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

De forma predeterminada, cada fila de una columna de mapa contiene pares clave-valor, donde 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.

A continuación, se muestra un ejemplo de una consulta 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 añadir un filtro temporal, como with_history, a tu instrucción SQL.

En este caso, los mapas que representan familias de columnas se anidan y se devuelven como un array. En la matriz, cada valor es un mapa que consta de una marca de tiempo como clave y datos de 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" de una sola fila.

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

El mapa devuelto 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) de una matriz representa las celdas de esas columnas en esa fila y se ordenan por marca de tiempo de forma descendente.

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

Tablas dispersas

Una de las características 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 almacena ningún dato en la columna. Una fila puede tener una columna y la siguiente, 100 columnas. Por el contrario, en una tabla de una base de datos relacional, todas las filas contienen todas las columnas y, normalmente, se almacena un valor NULL en la columna de una fila que no tiene datos en 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 devuelve como un valor NULL. Estos valores NULL se pueden usar como predicados de consulta. Por ejemplo, se puede usar un predicado como WHERE family['column1'] IS NOT NULL para devolver una fila solo si se usa column1 en la fila.

Bytes

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

Como 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 las funciones de conversión.

Filtros temporales

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

Argumento	Descripción
as_of	Timestamp. Devuelve los valores más recientes con marcas de tiempo inferiores o iguales a la marca de tiempo proporcionada.
with_history	Booleano. Controla si se devuelve el valor más reciente como un escalar o los valores con marca de tiempo como STRUCT.
after	Timestamp. Valores con marcas de tiempo posteriores a la entrada (sin incluir). Requiere with_history => TRUE.
after_or_equal	Timestamp. Valores con marcas de tiempo posteriores a la entrada, ambos incluidos. Requiere with_history => TRUE.
before	Timestamp. Valores con marcas de tiempo anteriores a la entrada, exclusivo. Requiere with_history => TRUE.
latest_n	Número entero. Número de valores con marca de tiempo que se van a devolver por calificador de columna (clave de mapa). Debe ser superior o igual a 1. Requisitos: with_history => TRUE.

Para ver más ejemplos, consulta Patrones de consulta avanzada.

Consultas básicas

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

Recuperar la versión más reciente

Aunque Bigtable te permite almacenar varias versiones de los 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.

Tomemos como ejemplo el siguiente conjunto de datos, 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 columna son street, city y state. Las celdas de una columna están separadas por líneas vacías.

		address
_key	calle	city	state
usuario1	2023/01/10-14:10:01.000: '113 Xyz Street' 2021/12/20-09:44:31.010: '76 Xyz Street' 2005/03/01-11:12:15.112: '123 Abc Street'	2021/12/20-09:44:31.010: 'Brooklyn' 2005/03/01-11:12:15.112: 'Queens'	2005/03/01-11:12:15.112: 'NY'

Para obtener la última versión de cada columna de user1, puedes usar una instrucción SELECT como la siguiente.

   SELECT address['street'], address['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 momentos diferentes) en formato JSON. Las marcas de tiempo no se incluyen en la respuesta.

_key	address
usuario1	{street:'113 Xyz Street', city:'Brooklyn', state: :'NY'}

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 muestra en el siguiente ejemplo.

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

Para entender mejor los eventos que han llevado al estado actual de una fila, puedes obtener las marcas de tiempo de cada valor consultando el historial completo. Por ejemplo, para saber cuándo se mudó user1 a su dirección actual y de dónde venía, 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 de SQL, la respuesta se devuelve como MAP<key, ARRAY<STRUCT<timestamp, value>>>. Cada elemento de la matriz es un valor con marca de tiempo de 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 devuelto.

Nota: Las políticas de recogida de elementos no utilizados controlan el tiempo de vencimiento o el número de versiones que se conservan de una familia de columnas. Para obtener más información, consulta la descripción general de la recogida de elementos no utilizados.

La respuesta de la consulta es la siguiente.

moved_to			moved_from	moved_on
Calle Xyz, 113			76 Xyz Street	10/01/2023

También puedes obtener el número de versiones de cada fila mediante funciones de matriz, como se muestra en la siguiente consulta:

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

Recuperar datos de un momento concreto

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

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

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

address
{street:'76 Xyz Street', city:'Brooklyn', state: :'NY'}

También se puede conseguir el mismo resultado usando marcas de tiempo de Unix.

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

Consideremos el siguiente conjunto de datos, que muestra el estado de activación o desactivación de los detectores de humo y de monóxido de carbono. La familia de columnas es alarmType y los calificadores de columna son smoke y carbonMonoxide. Las celdas de cada columna están separadas por líneas en blanco.

	alarmType
_key	humo	carbonMonoxide
building1#section1	2023/04/01-09:10:15.000: 'off' 2023/04/01-08:41:40.000: 'on' 2020/07/03-06:25:31.000: 'off' 2020/07/03-06:02:04.000: 'on'	2023/04/01-09:22:08.000: 'off' 2023/04/01-08:53:12.000: 'on'
building1#section2	2021/03/11-07:15:04.000: 'off' 2021/03/11-07:00:25.000: 'on'

Puedes encontrar las secciones de building1 en las que la alarma de humo estaba activada a las 9:00 del 1 de abril del 2023 y el estado de la alarma de monóxido de carbono en ese momento con la siguiente consulta.

  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:

ubicación	CO_sensor
building1#section1	'on'

Consultar datos de series temporales

Un caso práctico habitual de Bigtable es el almacenamiento de datos de series temporales. Considera el siguiente conjunto de datos de ejemplo, 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 columna 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.

	métricas
_key	temperatura	humedad
sensorA#20230105	2023/01/05-02:00:00.000: 54 2023/01/05-01:00:00.000: 56 2023/01/05-00:00:00.000: 55	2023/01/05-02:00:00.000: 0.89 2023/01/05-01:00:00.000: 0.9 2023/01/05-00:00:00.000: 0.91
sensorA#20230104	2023/01/04-23:00:00.000: 56 2023/01/04-22:00:00.000: 57	2023/01/04-23:00:00.000: 0.9 2023/01/04-22:00:00.000: 0.91

Puedes recuperar un intervalo específico de valores de marca de tiempo mediante 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:

temp_versioned
[{timestamp: '2023/01/05-01:00:00.000', value:56} {timestamp: '2023/01/05-00:00:00.000', value: 55}]
[{timestamp: '2023/01/04-23:00:00.000', value:56}]

Datos de serie temporal de UNPACK

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 único valor escalar y aparece en la cláusula FROM como una subconsulta de tabla. La función UNPACK TVF amplía cada valor con marca de tiempo en varias filas (una por cada marca de tiempo) y mueve la marca de tiempo a la columna _timestamp.

La entrada de UNPACK es una subconsulta donde with_history => true.

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

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

Si ampliamos el ejemplo de serie temporal de Consultar datos de series temporales y usamos la consulta de esa sección como entrada, la consulta UNPACK tendrá el siguiente formato:

  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:

temp_versioned	_timestamp
55	1672898400
55	1672894800
56	1672891200

Consultar JSON

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

Por ejemplo, puedes obtener el valor del elemento JSON abc de la última celda de la familia de columnas session junto con la clave de fila mediante la siguiente consulta.

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

Evitar caracteres especiales y palabras reservadas

Bigtable ofrece una gran flexibilidad a la hora de asignar nombres a tablas y columnas. Por lo tanto, es posible que tengas que usar caracteres de escape en los nombres de las tablas en tus consultas de SQL debido a caracteres especiales o palabras reservadas.

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

  -- ERROR: Table name format not supported

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

Sin embargo, puede solucionar este problema incluyendo los elementos entre comillas inversas (`).

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

Si se usa una palabra clave reservada de SQL como identificador, se puede usar el mismo método.

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

Usar SQL con una biblioteca de cliente de Bigtable

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

Go

Para usar esta función, debes usar la versión 1.36.0 de cloud.google.com/go/bigtable o una posterior. Para obtener más información sobre el uso, consulta la documentación de PrepareStatement, bind, execute y ResultRow.

  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
    })
  }

Java

Para usar esta función, debes usar la versión 2.57.3 de java-bigtable o una posterior. Para obtener más información sobre el uso, consulta prepareStatement, executeQuery, BoundStatement y ResultSet en Javadoc.

  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.
      }
    }
  }

Python asyncio

Para usar esta función, debes usar la versión 2.30.1 o una posterior de python-bigtable.

  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 añade o se elimina una familia de columnas de la tabla consultada. Por este motivo, en el caso de las cargas de trabajo de producción, le recomendamos que especifique todos los IDs de familias de columnas en su consulta en lugar de usar SELECT *. Por ejemplo, usa SELECT cf1, cf2, cf3 en lugar de SELECT *.

Siguientes pasos

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