Consultas de vistas materializadas continuas

Para crear una vista materializada continua de una tabla de Bigtable, ejecuta una consulta en SQL que defina la vista materializada continua.

En este documento, se describen conceptos y patrones para ayudarte a preparar tu consulta en SQL de vista materializada continua. Antes de leer este documento, debes familiarizarte con las vistas materializadas continuas y GoogleSQL para Bigtable.

Las vistas materializadas continuas usan una sintaxis de SQL restringida. El siguiente patrón muestra cómo compilar una consulta en SQL de vista materializada continua:

SELECT
  expression AS alias [, ...]
FROM from_item
[ WHERE bool_expression ]
GROUP BY expression [, ...];

from_item:
    {
      table_name [ as_alias ]
      | field_path
      }

as_alias:
    [ AS ] alias

Si deseas crear una consulta en SQL de vista materializada continua como un índice secundario global, usa la cláusula ORDER BY:

SELECT
  expression AS alias [, ...]
FROM from_item
[ WHERE bool_expression ]
ORDER BY expression [, ...];

from_item:
    {
      table_name [ as_alias ]
      | field_path
      }

as_alias:
    [ AS ] alias

Limitaciones de las consultas

Se aplican las siguientes reglas a una consulta en SQL que se usa para crear una vista materializada continua:

• Debe ser una sentencia SELECT.
• Debe tener una cláusula GROUP BY o, para las consultas de índices secundarios globales, una cláusula ORDER BY, pero no ambas.
• Solo se deben usar las funciones de agregación admitidas.
• Puede tener varias agregaciones por grupo.

Agregaciones admitidas

Puedes usar las siguientes funciones de agregación en una consulta en SQL que define una vista materializada continua:

• COUNT
• SUM
• MIN
• MAX
• HLL_COUNT.INIT
• HLL_COUNT.MERGE
• HLL_COUNT.MERGE_PARTIAL
• ANY_VALUE
• BIT_AND
• BIT_OR
• BIT_XOR
• AVG

Si SELECT COUNT(*), debes definir una clave de fila, como en el siguiente ejemplo:

SELECT
  '*' AS _key,
  COUNT(*) AS count
FROM
  foo
GROUP BY
  _key;

Características de SQL no compatibles

No puedes usar las siguientes funciones de SQL:

• Cualquier función que no sea compatible con GoogleSQL para Bigtable
• ARRAY
• ARRAY_AGG
• ARRAY_CONCAT_AGG
• COUNT_IF
• CURRENT_TIME y otras funciones no deterministas
• DATE, DATETIME como columnas de salida (usa TIMESTAMP o almacena una cadena)
• Ordenamiento de DESC en el resultado
• Opción DISTINCT, como en SUM(*DISTINCT* value))
• LIMIT/OFFSET
• SELECT *
• Cláusula OVER para crear una agregación de ventana
• STRUCT

Tampoco puedes anidar cláusulas GROUP BY o ORDER BY, ni crear columnas de mapa. Para conocer otras limitaciones, consulta Limitaciones.

Cómo evitar las filas excluidas

Las filas de entrada se excluyen de una vista materializada continua en las siguientes circunstancias:

• Se seleccionaron más de 1 MiB de datos de la fila. Por ejemplo, si tu consulta es SELECT apple AS apples , SUM(banana) AS sum_bananas FROM my_table GROUP BY apples, se excluirá de la vista materializada continua cualquier fila que contenga más de 1 MiB de datos en las columnas apple y banana.
• Se generan más de 1 MiB de datos desde la fila. Esto puede ocurrir cuando usas consultas como SELECT REPEAT(apple, 1000) o constantes grandes.
• Se generan más de 10 veces más datos de los que se seleccionaron.
• La búsqueda no coincide con tus datos. Esto incluiría intentar dividir un cero, un desbordamiento de números enteros o esperar un formato de clave de fila que no se use en cada clave de fila.

Las filas excluidas incrementan la métrica de errores del usuario cuando se procesan por primera vez. Para obtener más información sobre las métricas que pueden ayudarte a supervisar tus vistas materializadas continuas, consulta Métricas.

Detalles de la consulta

En esta sección, se describe una consulta de vista materializada continua y cómo podrían verse los resultados cuando se consulta la vista. Los datos de la tabla de origen son la entrada, y los datos de resultado de la vista materializada continua son la salida. Los datos de salida se agregan o no (en la clave definida).

Declaración SELECT

La instrucción SELECT configura las columnas y las agregaciones que se usan en la vista materializada continua. La instrucción debe usar una cláusula GROUP BY para agregar datos en todas las filas o una cláusula ORDER BY para crear un índice secundario global.

SELECT * no es compatible, pero SELECT COUNT(*) sí lo es.

Al igual que en una sentencia SELECT típica, puedes tener varias agregaciones por un conjunto de datos agrupados. Las columnas sin agrupar deben ser un resultado de agregación.

Este es un ejemplo de una consulta de agregación GROUP BY estándar en SQL:

SELECT
  myfamily["node"] AS node,
  myfamily["type"] AS type,
  COUNT(clicks) AS clicks_per_key
FROM
  mytable
GROUP BY
  node,
  type

Claves de fila y datos sin agregar

Puedes especificar un _key como clave de fila para una vista materializada continua. Si no lo haces, las columnas de la cláusula GROUP BY formarán la clave en la vista.

Claves de fila definidas por una columna _key

De manera opcional, puedes especificar una columna _key cuando definas tu vista materializada continua. (Esto es diferente de la columna _key que obtienes cuando ejecutas una consulta en SQL en una tabla de Bigtable). Si especificas un _key, se aplican las siguientes reglas:

• Debes agrupar por _key y no puedes agrupar por ningún otro elemento, excepto (opcionalmente) por _timestamp. Para obtener más información, consulta Marcas de tiempo.
• La columna _key debe ser del tipo BYTES.

Especificar un _key es útil si planeas leer la vista con ReadRows en lugar de con SQL, ya que te permite controlar el formato de la clave de fila. Por otro lado, una consulta en SQL a una vista con un _key definido podría necesitar decodificar el _key de forma explícita en lugar de solo devolver columnas de claves estructuradas.

Claves de fila definidas por la cláusula GROUP BY o ORDER BY

Si no especificas un _key, las columnas sin agregar de tu lista de SELECT se convertirán en la clave de fila de la vista. Puedes asignar a las columnas clave cualquier nombre que admitan las convenciones de SQL. Usa este enfoque si planeas usar SQL para consultar la vista en lugar de una solicitud de ReadRows.

Las columnas de salida no agregadas en la lista SELECT deben incluirse en la cláusula GROUP BY. El orden en el que se escriben las columnas en la cláusula GROUP BY es el orden en el que se almacenan los datos en la clave de fila de la vista materializada continua. Por ejemplo, GROUP BY a, b, c es implícitamente ORDER BY a ASC, b ASC, c ASC.

Si usas una cláusula ORDER BY en lugar de una cláusula GROUP BY para crear un índice secundario global, las columnas de tu lista SELECT que forman parte de la cláusula ORDER BY se convierten en la clave de fila en la vista. El orden en el que se escriben las columnas en la cláusula ORDER BY es el orden en el que se almacenan los datos en la clave de fila de la vista materializada continua. Por ejemplo, ORDER BY a, b, c almacena los datos con claves de filas ordenadas por a ASC, luego por b ASC y, por último, por c ASC.

Tu filtro de SQL debe eliminar los posibles valores NULL o cualquier otro valor no válido que pueda causar errores. Las filas no válidas, como las que contienen una columna de clave NULL, se omiten de los resultados y se registran en la métrica materialized_view/user_errors. Para depurar los errores del usuario, intenta ejecutar la consulta en SQL fuera de una vista materializada continua.

Datos agregados

Las columnas de agregación en la consulta definen los cálculos que generan los datos en la vista materializada continua.

El alias de una columna de agregación se trata como un calificador de columna en la vista materializada continua.

Considera el siguiente ejemplo:

SELECT
  fam["baz"] AS baz,
  SUM(fam["foo"]) AS sum_foo,
  SUM(fam["bar"]) AS sum_bar
FROM
  TABLE

GROUP BY
  baz;

El resultado de la búsqueda tiene las siguientes características:

• El resultado para cada baz se encuentra en una fila separada en orden de baz ASC.
• Si un baz determinado tiene al menos un foo, el sum_foo de la fila de salida es un valor no NULL.
• Si un baz determinado tiene al menos un bar, el sum_bar de la fila de salida es un valor no NULL.
• Si un baz determinado no tiene valor para ninguna de las columnas, se omite de los resultados.

Luego, si consultas la vista con SELECT *, el resultado será similar al siguiente:

Marcas de tiempo

La marca de tiempo predeterminada para una celda de salida en una vista materializada continua es 0 (1970-01-01 00:00:00Z). Esto se puede ver cuando lees la vista con ReadRows y no cuando la consultas con SQL.

Para usar una marca de tiempo diferente en el resultado, puedes agregar una columna del tipo TIMESTAMP a la lista SELECT de la consulta y llamarla _timestamp. Si consultas la vista materializada continua con ReadRows, _timestamp se convierte en la marca de tiempo de las demás celdas de la fila.

La marca de tiempo no debe ser NULL, debe ser mayor o igual que cero y debe ser un múltiplo de 1,000 (precisión de milisegundos). Bigtable no admite marcas de tiempo de celdas anteriores al tiempo Unix (1970-01-01T00:00:00Z).

Considera el siguiente ejemplo, que vuelve a muestrear los datos agregados por día. La consulta usa la función UNPACK.

SELECT
  _key,
  TIMESTAMP_TRUNC(_timestamp, DAY) AS _timestamp,
  SUM(sum_family["sum_column"]) AS sum_column,
  SUM(sum_family["foo"]) AS second_sum_column
FROM
  UNPACK(
  SELECT
    *
  FROM
    my_table(with_history => TRUE))
GROUP BY
  1,
  2

Si un SUM determinado tiene una entrada no vacía para un día determinado, la fila de salida contiene un valor agregado con una marca de tiempo que coincide con el día truncado.

Si consultas la vista con SELECT *, el resultado será similar al siguiente:

Codificación

Si consultas tu vista materializada continua con SQL, no necesitas saber cómo se codifican los valores agregados, ya que SQL expone los resultados como columnas escritas.

Si lees desde la vista con ReadRows, debes decodificar los datos agregados en tu solicitud de lectura. Para obtener más información sobre las solicitudes de ReadRows, consulta Lecturas.

Los valores agregados en una vista materializada continua se almacenan con la codificación que se describe en la siguiente tabla, según el tipo de salida de la columna de la definición de la vista.

¿Qué sigue?

• Crea y administra vistas materializadas continuas
• Documentación de referencia de GoogleSQL para Bigtable