Consulta un registro de cambios de Bigtable en BigQuery

En esta página, se proporcionan instrucciones y ejemplos de consultas para ayudarte a procesar un registro de cambios de Bigtable en BigQuery.

Esta página está destinada a los usuarios que completaron lo siguiente:

En esta guía, se da por sentado 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.

Abre la tabla del registro de cambios.

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

    Ir a BigQuery

  2. En el panel Explorador, expande tu proyecto.

  3. Expande tu conjunto de datos.

  4. Haz clic en la tabla con el sufijo: _changelog.

Formato de la tabla

Todo el esquema de salida contiene varias columnas. En esta guía, se enfoca en conectar las filas a sus columnas y valores, y 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 tarjetas 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
  • Comercio
  • Importe
  • Categoría
  • Fecha de la transacción

Cómo consultar una columna

Filtra los resultados a una sola familia de columnas y una columna con 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

Analiza los valores

Todos los valores se almacenan como cadenas o cadenas de bytes. Puedes transmitir un valor a su tipo previsto 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

Cómo realizar agregaciones

Puedes realizar más operaciones, como las agregaciones en 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"

Cómo utilizar los datos dinámicos

Para realizar consultas que involucren varias columnas de Bigtable, debes girar la tabla. Cada fila nueva de BigQuery incluye un registro de cambios de datos que muestra el flujo de cambios de su fila correspondiente en tu tabla de Bigtable. Según 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")
)

Cómo crear un pivote con un conjunto de columnas dinámico

Si tienes un conjunto dinámico de columnas, puedes realizar un procesamiento adicional para obtener todas las columnas y colocarlas 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 configuras todos los valores con JSON, debes analizarlos y extraer los valores según 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 con tarjetas de crédito que se presentaron antes, pero en lugar de escribir datos en varias columnas, los datos se escriben como una sola columna como un 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

¿Qué sigue?