Consultar un registro de cambios de Bigtable en BigQuery

En esta página se ofrecen directrices y consultas de ejemplo para ayudarle a procesar un registro de cambios de Bigtable en BigQuery.

Esta página está dirigida a usuarios que hayan completado lo siguiente:

En esta guía se da por hecho que tienes algunos conocimientos sobre BigQuery. Para obtener más información, puedes seguir la guía de inicio rápido que muestra cómo cargar y consultar datos.

Abrir la tabla de registro de cambios

  1. En la Google Cloud consola, ve a la página BigQuery.

    Ir a BigQuery

  2. En el panel Explorador, expande tu proyecto.

  3. Despliega el conjunto de datos.

  4. Haga clic en la tabla con el sufijo _changelog.

Formato de tabla

Todo el esquema de salida contiene varias columnas. En esta guía se explica cómo conectar las filas con sus columnas y valores, así como cómo analizar los valores en formatos analizables.

Consultas básicas

En los ejemplos de esta sección se usa una tabla de Bigtable para hacer un seguimiento de las ventas con tarjeta de crédito. La tabla tiene una familia de columnas (cf) y las siguientes columnas:

  • Clave de fila con el formato credit card number#transaction timestamp
  • Comerciante
  • Importe
  • Categoría
  • Fecha de la transacción

Consultar una columna

Filtra los resultados para que solo se muestre una familia de columnas y una columna mediante una cláusula WHERE.

SELECT row_key, column_family, column, value, timestamp,
FROM your_dataset.your_table
WHERE
  mod_type="SET_CELL"
  AND column_family="cf"
  AND column="merchant"
LIMIT 1000

Analizar valores

Todos los valores se almacenan como cadenas o cadenas de bytes. Puedes convertir un valor al tipo que quieras con las funciones de conversión.

SELECT row_key, column_family, column, value, CAST(value AS NUMERIC) AS amount
FROM your_dataset.your_table
WHERE
  mod_type="SET_CELL"
  AND column_family="cf"
  AND column="amount"
LIMIT 1000

Realizar agregaciones

Puedes realizar más operaciones, como agregaciones de valores numéricos.

SELECT SUM(CAST(value AS NUMERIC)) as total_amount
FROM your_dataset.your_table
WHERE
  mod_type="SET_CELL"
  AND column_family="cf"
  AND column="amount"

Dinamizar los datos

Para realizar consultas que impliquen varias columnas de Bigtable, debes dinamizar la tabla. Cada fila nueva de BigQuery incluye un registro de cambio de datos devuelto por el flujo de cambios de su fila correspondiente en tu tabla de Bigtable. En función de tu esquema, puedes usar una combinación de la clave de fila y la marca de tiempo para agrupar los datos.

SELECT * FROM (
  SELECT row_key, timestamp, column, value
  FROM your_dataset.your_table
)
PIVOT (
  MAX(value)
  FOR column in ("merchant", "amount", "category", "transaction_date")
)

Crear una tabla dinámica con un conjunto de columnas dinámico

Si tienes un conjunto dinámico de columnas, puedes hacer un procesamiento adicional para obtener todas las columnas e incluirlas en la consulta de forma programática.

DECLARE cols STRING;
SET cols = (
  SELECT CONCAT('("', STRING_AGG(DISTINCT column, '", "'), '")'),
  FROM your_dataset.your_table
);

EXECUTE IMMEDIATE format("""
SELECT * FROM (
  SELECT row_key, timestamp, column, value
  FROM your_dataset.your_table
)
PIVOT (
  MAX(value)
  FOR column in %s
)""", cols);

Datos JSON

Si vas a definir todos los valores con JSON, debes analizarlos y extraer los valores en función de las claves. Puedes usar funciones de análisis una vez que hayas derivado el valor del objeto JSON. En estos ejemplos se usan los datos de ventas de tarjetas de crédito que se han introducido anteriormente, pero, en lugar de escribir datos en varias columnas, los datos se escriben en una sola columna como objeto JSON.

SELECT
  row_key,
  JSON_VALUE(value, "$.category") as category,
  CAST(JSON_VALUE(value, "$.amount") AS NUMERIC) as amount
FROM your_dataset.your_table
LIMIT 1000

Consultas de agregación con JSON

Puedes realizar consultas de agregación con valores JSON.

SELECT
  JSON_VALUE(value, "$.category") as category,
  SUM(CAST(JSON_VALUE(value, "$.amount") AS NUMERIC)) as total_amount
FROM your_dataset.your_table
GROUP BY category

Siguientes pasos