Federación de datos sin ETL de BigQuery a AlloyDB

1. Descripción general

En la Parte 1, transformamos correctamente archivos PDF caóticos y no estructurados en tablas limpias, inteligentes y estructuradas en BigQuery con Knowledge Catalog y DataScan. Ahora tenemos un almacén de datos sólido.

Si necesitas un recordatorio rápido, en el lab de la parte 1, tomamos el caso de uso de una franquicia ficticia de Frozen Yogurt y convertimos 400 de sus archivos PDF no estructurados, que abarcan texto, tablas e imágenes, en tablas de BigQuery estructuradas de forma limpia con relaciones inferidas automáticamente entre ellas con BigQuery Knowledge Catalog y Dataplex.

Qué compilarás

En esta sesión, configuraremos AlloyDB para PostgreSQL y haremos algo mágico: federar nuestros datos de BigQuery directamente en AlloyDB. Esto significa que nuestra app transaccional puede consultar nuestros datos del almacén en tiempo real, sin copiarlos ni duplicarlos.

Como desarrollador, debes hacerte esta pregunta en esta etapa:

"Si los datos ya están en BigQuery, ¿por qué incluir AlloyDB? ¿Por qué la aplicación no ejecuta una instrucción SELECT directamente en BigQuery?".

Este es el motivo:

Con Lakehouse Federation, puedes usar el motor de consultas de AlloyDB para potenciar las cargas de trabajo transaccionales y analíticas de tu aplicación desde la misma interfaz. También puedes materializar o importar estos datos en AlloyDB para obtener un acceso más rápido para usarlos en tus aplicaciones, lo que te permite usar AlloyDB AI y el motor de columnas.

Puedes usar AlloyDB como una base de datos transaccional y también tener grandes cantidades de datos en BigQuery o BigLake. Por lo general, tus aplicaciones se integran de forma independiente con ambos sistemas para acceder a los datos en estos diferentes servicios de Google Cloud. Lakehouse Federation para AlloyDB te permite usar la compatibilidad con consultas federadas de AlloyDB implementada como un wrapper de datos externos para acceder a los datos de BigQuery y AlloyDB con una interfaz SQL en AlloyDB.

En lugar de compilar una canalización de ETL frágil para consultar los datos de BigQuery desde AlloyDB, usaremos consultas federadas. AlloyDB actuará como un extremo unificado, que llegará sin problemas a BigQuery cuando sea necesario.

Comencemos a compilar.

Qué aprenderás

• Cómo configurar el clúster, la instancia y la red de AlloyDB con un solo clic
• Cómo configurar la extensión para prepararse para la federación
• Cómo configurar la federación de BigQuery a AlloyDB
• Pruébalo

Requisitos

• Un navegador, como Chrome o Firefox.
• Un proyecto de Google Cloud con facturación habilitada.
• Conocimientos básicos de SQL

2. Antes de comenzar

Crea un proyecto

1. En la página del selector de proyectos de la consola de Google Cloud, selecciona o crea un proyecto de Google Cloud.
2. Asegúrate de que la facturación esté habilitada para tu proyecto de Cloud. Obtén información para verificar si la facturación está habilitada en un proyecto.

Hay funciones limitadas en este lab que puedes experimentar incluso sin una cuenta de facturación. Si deseas explorar la práctica en esa capacidad, tienes notas según corresponda que se incluyen en los calificadores "¿NO TIENES UNA CUENTA DE FACTURACIÓN?". Ten cuidado con ellos.

3. Usarás Cloud Shell, un entorno de línea de comandos que se ejecuta en Google Cloud. Haz clic en Activar Cloud Shell en la parte superior de la consola de Google Cloud.

4. Una vez que te conectes a Cloud Shell, verifica que ya te autenticaste y que el proyecto esté configurado con tu ID del proyecto con el siguiente comando:

gcloud auth list

5. En Cloud Shell, ejecuta el siguiente comando para confirmar que el comando gcloud conoce tu proyecto.

gcloud config list project

6. Si deseas autenticarte

gcloud auth login

7. Si tu proyecto no está configurado, usa el siguiente comando para hacerlo:

export PROJECT_ID=<YOUR_PROJECT_ID>
gcloud config set project <YOUR_PROJECT_ID>

8. Habilita las APIs requeridas: Ejecuta este comando para habilitar todas las APIs requeridas:

gcloud services enable alloydb.googleapis.com

Problemas y solución de problemas

El síndrome del "proyecto fantasma"	Ejecutaste gcloud config set project, pero en realidad estás viendo un proyecto diferente en la IU de la consola. Verifica el ID del proyecto en el menú desplegable de la esquina superior izquierda.
La barrera de facturación	Habilitaste el proyecto, pero olvidaste la cuenta de facturación. AlloyDB es un motor de alto rendimiento; no se iniciará si el "tanque de combustible" (facturación) está vacío.
Demora en la propagación de la API	Hiciste clic en "Habilitar APIs", pero la línea de comandos aún dice Service Not Enabled. Espera 60 segundos. La nube necesita un momento para activar sus neuronas.
Cuotas	Si usas una cuenta de prueba nueva, es posible que alcances una cuota regional para las instancias de AlloyDB. Si falla us-central1, prueba con us-east1.

3. Resumen rápido de los datos de la Parte 1

En esta sección, debes asegurarte de que los datos estructurados que extrajimos de los archivos PDF no estructurados estén disponibles en BigQuery. Si te perdiste la parte 1 o no tienes una cuenta de facturación, puedes completar los siguientes pasos y comenzar:

Ve a la consola de Google Cloud desde tu cuenta personal de Gmail y haz clic en el botón Activar Cloud Shell en la esquina superior derecha de la consola:

Luego, sigue los pasos de la sección sin cuenta de facturación que se muestra a continuación:

¿NO TIENES UNA CUENTA DE FACTURACIÓN?

Si estás leyendo este codelab, de forma práctica, pero de alto nivel sin una cuenta de facturación, es probable que no hayas podido configurar el trabajo de DataScan ni realizar la inferencia de Knowledge Catalog. O te perdiste el lab de la parte 1, por lo que aquí están los datos disponibles para ti, inferidos a través de los pasos anteriores de los 400 archivos PDF de recetas y proveedores: https://github.com/AbiramiSukumaran/raw-data-to-gold/tree/main/data.

Pasos para seguir experimentando los datos con la cuenta de facturación:

1. Obtén los archivos csv (datos de BigQuery) del vínculo del repositorio de GitHub que se encuentra más arriba.
2. Ejecuta el siguiente comando desde la terminal de Cloud Shell:

____________________________________________

bq mk --location us-central1 --dataset froyo_data

____________________________________________

3. Ejecuta los siguientes comandos uno por uno para crear estas tablas con los datos de tu conjunto de datos recién creado.

-----------------------------------------------------------------

bq load \

--autodetect \

--source_format=CSV \

--skip_leading_rows=1 \

--allow_quoted_newlines \

--quote="" \

froyo_data.allergen \

./froyo_data.allergen.csv allergen_name:STRING

-----------------------------------------------------------------

bq load \

--autodetect \

--source_format=CSV \

--skip_leading_rows=1 \

--allow_quoted_newlines \

--quote="" \

froyo_data.consistsof \

./froyo_data.consistsof.csv product_id:STRING,ingredient_id:STRING

-----------------------------------------------------------------

bq load \

--autodetect \

--source_format=CSV \

--skip_leading_rows=1 \

--allow_quoted_newlines \

--quote="" \

froyo_data.containsallergen \

./froyo_data.containsallergen.csv ingredient_id:STRING,allergen_name:STRING

-----------------------------------------------------------------

bq load \

--autodetect \

--source_format=CSV \

--skip_leading_rows=1 \

--allow_quoted_newlines \

--quote="" \

froyo_data.froyo_data_materialized \

./froyo_data.froyo_data_materialized.csv allergen:STRING,containsallergen:STRING,ingredient:STRING,product:STRING,suppliedby:STRING,supplier:STRING,ref:STRING,md5_hash:STRING

-----------------------------------------------------------------

bq load \

--autodetect \

--source_format=CSV \

--skip_leading_rows=1 \

--allow_quoted_newlines \

--quote="" \

froyo_data.ingredient \

./froyo_data.ingredient.csv ingredient_id:STRING,ingredient_name:STRING,purity:STRING,moisture_content:STRING,ph_range:STRING,specific_gravity_range:STRING

-----------------------------------------------------------------

bq load \

--autodetect \

--source_format=CSV \

--skip_leading_rows=1 \

--allow_quoted_newlines \

--quote="" \

froyo_data.product \

./froyo_data.product.csv product_id:STRING,product_name:STRING,category:STRING,stability:STRING

-----------------------------------------------------------------

bq load \

--autodetect \

--source_format=CSV \

--skip_leading_rows=1 \

--allow_quoted_newlines \

--quote="" \

froyo_data.suppliedby \

./froyo_data.suppliedby.csv ingredient_id:STRING,supplier_id:STRING

-----------------------------------------------------------------

bq load \

--autodetect \

--source_format=CSV \

--skip_leading_rows=1 \

--allow_quoted_newlines \

--quote="" \

froyo_data1.supplier \

./froyo_data.supplier.csv supplier_id:STRING,supplier_name:STRING,production_site_id:STRING,facility_grade:STRING

-----------------------------------------------------------------

Una vez que se creen el conjunto de datos, las tablas y los datos, puedes continuar para probar y experimentar los datos que acabamos de analizar.

¿NO TIENES UNA CUENTA DE FACTURACIÓN?

Ahora que tenemos los datos en BigQuery, pasemos a los siguientes pasos.

4. Configura el clúster, la instancia y la red de AlloyDB

Hay una aplicación de inicio rápido basada en la Web que te ayudará a configurar el clúster, la instancia y otras dependencias de AlloyDB. Puedes seguir los pasos 2 a 4 de este lab para configurarlo con un solo clic:

https://codelabs.developers.google.com/quick-alloydb-setup

Una vez que se cree el clúster, ve a la página Descripción general del clúster y copia los detalles de la cuenta de servicio desde allí.

5. Configuración de permisos

Otorga permisos de BigQuery a esta cuenta de servicio

1. Navega a IAM y administración > IAM.
2. Haz clic en Otorgar acceso.
3. Pega la dirección de la cuenta de servicio de AlloyDB en el campo Miembros nuevos.
4. Asigna los siguientes roles:

• Visualizador de datos de BigQuery (roles/bigquery.dataViewer): Permite leer los datos.
• Usuario de BigQuery (roles/bigquery.user): Permite ejecutar las consultas.
• (Opcional, pero recomendado) Usuario de la sesión de lectura de BigQuery (roles/bigquery.readSessionUser): Optimiza la lectura de conjuntos de datos grandes a través de la API de Storage Read.

6. Conéctate a AlloyDB y habilita la extensión de BigQuery

Ahora nos conectaremos a nuestra instancia nueva de AlloyDB para configurar la extensión de federación. Para ello, usaremos AlloyDB Studio.

1. En la página Descripción general del clúster (consola de AlloyDB), haz clic en AlloyDB Studio.

2. Conéctate con tu base de datos, nombre de usuario y contraseña que configuraste en el momento del paso de configuración rápida de AlloyDB.
3. Una vez que te conectes, en la pestaña Editor de consultas, en el lado derecho, ingresa las siguientes instrucciones y EJECUTA una por una:

CREATE EXTENSION IF NOT EXISTS  bigquery_fdw;

CREATE SERVER bigquery_server FOREIGN DATA WRAPPER bigquery_fdw;

CREATE USER MAPPING FOR postgres SERVER bigquery_server;

4. Una vez que se complete correctamente, navega al panel del explorador de la izquierda y desplázate hacia abajo hasta las tablas de BigQuery:

5. Haz clic en los 3 puntos y, luego, en "Conectar tabla de BigQuery".
6. En la ventana emergente Conectar tabla de BigQuery que se abre, selecciona tu project_id y el nombre del conjunto de datos de BigQuery (creado en la parte 1) desde el que deseas consultar los datos en tu base de datos de AlloyDB.

7. Selecciona cada tabla una por una para que todos tus datos se conecten a AlloyDB. Esto es para validar los tipos de columnas y asegurarnos de que sean compatibles con AlloyDB.

Si deseas hacer lo mismo con SQL en lugar de usar el método de apuntar y hacer clic:

CREATE FOREIGN TABLE <<TABLE_NAME>> (
      "cas_number" VARCHAR, "ingredient_name" VARCHAR, "max_moisture_percentage" DOUBLE PRECISION, "ph_range" VARCHAR, "purity_percentage" DOUBLE PRECISION, "shelf_life_months" BIGINT, "specific_gravity_range" VARCHAR
    ) SERVER "bigquery_server" OPTIONS (
      project '<<PROJECT_ID>>',
      dataset 'froyo_data',
      table '<<BQ_TABLE_NAME>>'
    );

La magia!!!

Acabamos de crear "tablas externas" en AlloyDB. Se ven y actúan como tablas normales de PostgreSQL, pero no almacenan ningún dato. Cuando las consultas, AlloyDB pasa la consulta a BigQuery de inmediato, recupera los resultados y te los devuelve.

7. Prueba la federación en AlloyDB

Verifiquemos que podamos consultar nuestro conjunto de datos analíticos masivos de BigQuery directamente desde nuestra base de datos transaccional de PostgreSQL.

Aún en AlloyDB Studio, ejecutemos una consulta para averiguar qué alérgenos hay en "Midnight Swirl" (la misma pregunta que hicimos en la Parte 1, pero esta vez desde AlloyDB):

SELECT
    p.product_name,
    i.ingredient_name,
    a.allergen_name
FROM
    consistsof c
INNER JOIN product p
    ON c.product_id = p.product_id
INNER JOIN ingredient i
    ON c.ingredient_id = i.ingredient_name
LEFT OUTER JOIN containsallergen a
    ON i.ingredient_id = a.ingredient_id
WHERE
    UPPER(p.product_name) LIKE '%MIDNIGHT%SWIRL%'
    AND a.allergen_name IS NOT NULL;

Nada muy extravagante. Deberías ver exactamente los mismos resultados que en BigQuery.

8. Limpia

Una vez que finalice este lab, no olvides borrar el clúster y la instancia de AlloyDB.

Ve a https://console.cloud.google.com/alloydb/clusters. Para borrar el clúster, haz clic en los puntos suspensivos verticales junto a él y, luego, en BORRAR.

Debería limpiar el clúster junto con sus instancias.

9. Felicitaciones por tu capa de datos unificada

Piensa en lo que acabamos de lograr:

1. Nuestra app transaccional (que se ejecuta en AlloyDB) puede controlar sesiones de usuario rápidas y simultáneas.
2. Cuando necesita datos analíticos pesados o contexto histórico (como detalles del proveedor o asignaciones de ingredientes complejos), consulta el froyo_dataschema de BigQuery.
3. Cero ETL. No se interrumpen las canalizaciones de datos. No hay bases de datos desincronizadas. Almacenamos una vez (en BQ) y calculamos donde lo necesitamos.

Ahora que nuestra base de datos, tanto analítica como transaccional, es sólida y está interconectada, estamos listos para la parte divertida.

En la Parte 3, compilaremos la aplicación multiagente que se encuentra sobre esta arquitectura para ejecutar las operaciones comerciales de Froyo.