BigQuery a las escondidas: tras la pista de tablas y datasets esquivos

En el mundo del análisis de datos, que avanza a toda velocidad, Google BigQuery destaca como un data warehouse serverless muy potente que permite ejecutar consultas SQL ultrarrápidas sobre datasets enormes. Ya seas data scientist, data engineer o experto en analítica, recorrer las amplias capacidades de BigQuery puede sentirse como descubrir tesoros escondidos. A medida que tu organización crece, tu footprint en BigQuery también crece, y uno de los retos más comunes es listar tablas y datasets de forma eficiente en distintos ámbitos.

El objetivo de este post es guiarte por los distintos métodos para listar tablas y datasets en BigQuery, con la meta concreta (y final) de listarlos en toda tu organización y, de paso, mostrarte las limitaciones de cada enfoque.

A medida que avancemos por las distintas técnicas, desde interacciones básicas en la GUI hasta consultas SQL más avanzadas y llamadas a la API, irás viendo sus puntos fuertes y sus restricciones. Al final, te presentaremos una solución que ofrece la mayor flexibilidad, aunque requiere algo más de trabajo… ¡salvo que ya lo hayamos hecho por ti!

Repasaremos las siguientes opciones:

• UI web de Google Cloud Console
• BigQuery CLI
• Dataplex (Data Catalog)
• INFORMATION_SCHEMA
• Script personalizado

Y, en el camino, evaluaremos algunas dimensiones:

• Alcance del listado
• Nivel de detalle
• Flexibilidad
• Precios

Un buen punto de partida es la conocida UI web de Google Cloud Console. Si entras a BigQuery Studio dentro de la página del producto BigQuery, encontrarás los datasets y tablas de tu proyecto actual (y de otros proyectos, si los agregas) en el panel Explorer. También puedes buscar recursos (dataset, tabla o vista) por nombre o etiqueta y obtener resultados que abarcan tanto los projects como las organisations a las que tengas acceso.

Datasets (izquierda) y tablas (derecha) en la UI de BigQuery

Pros:

• Se accede fácilmente desde BigQuery Studio.
• Permite buscar entre projects y organisations, así que no hay limitaciones de alcance.
• Es gratis.

Contras:

• No entrega un listado exhaustivo de todos los datasets o tablas de tu organización ni de aquellos a los que tienes acceso.
• No incluye otros metadatos sobre tus tablas.

Si las UIs no son lo tuyo y prefieres trabajar en la línea de comandos, seguramente te llame más la atención el BigQuery CLI.

Dado un PROJECT_ID, puedes listar todos los datasets de ese proyecto con:

bq ls --project_id $PROJECT_ID

Datasets en BigQuery CLI

O bien, con un PROJECT_ID y un DATASET_ID, puedes listar todas las tablas de ese dataset con:

bq ls --project_id $PROJECT_ID --dataset_id=$DATASET_ID

Tablas en BigQuery CLI

Pros:

• CLI fácil de usar.
• La salida sirve para procesamiento ligero downstream con, por ejemplo, jq, sobre todo al usar la flag --format: <none|json|prettyjson|csv|sparse|pretty>.
• Las operaciones de metadatos son gratuitas.

Contras:

• El alcance del listado es limitado: a nivel de dataset, el ámbito es un proyecto; a nivel de tabla, un dataset.
• El detalle que devuelve el listado también es limitado.

Cuando se piensa en encontrar todos los datos relevantes de tu organización, ¡Data Catalog viene a la mente de inmediato! Antes era un producto independiente, pero es una funcionalidad de Dataplex desde mediados de 2022. Dataplex es la plataforma inteligente de gestión de datos de Google Cloud, que automatiza la organización, la seguridad y el análisis de los datos en data lakes, warehouses y bases de datos, y apoya a tu organización en descubrimiento, gobierno y cumplimiento. Data Catalog funciona como inventario central de los activos de datos de tu organización.

Te permite buscar entre organizaciones y sistemas, filtrar por tipos de datos, etiquetas, etc. Para nuestro caso de uso, podemos recuperar todos los datasets y tablas dentro de una organización específica con solo aplicar el filtro adecuado en la UI:

Lo mejor es que, para quienes prefieran CLI sobre UI, se logra lo mismo con un comando gcloud:

Para datasets:

gcloud data-catalog search "type=dataset" --include-organization-ids=YOUR_ORG_ID

Datasets en Dataplex

Para tablas:

gcloud data-catalog search "type=table" --include-organization-ids=YOUR_ORG_ID

Tablas en Dataplex

Pros:

• Busca entre organizaciones Y va más allá de buscar solo en BigQuery.

Contras:

• Data Catalog almacena distintos tipos de metadatos (de negocio y técnicos), pero deja fuera detalles como el tamaño de la tabla o el total de bytes almacenados.
• Ni Dataplex ni Data Catalog son gratuitos, aunque el costo suele ser manejable para la mayoría de los casos de uso. Consulta la página de precios para más detalles.

Quienes ya conocen BigQuery seguro están familiarizados con INFORMATION_SCHEMA. Las vistas INFORMATION_SCHEMA de BigQuery son vistas de solo lectura, definidas por el sistema, que entregan metadatos sobre tus objetos de BigQuery. Hay una gran cantidad de vistas distintas; consulta la documentación para una visión completa.

Para nuestro caso de uso, nos interesan especialmente la vista SCHEMATA y la vista TABLES:

Podemos usar la vista SCHEMATA para listar todos los datasets dentro de una región, por ejemplo us-central1:

SELECT * FROM `region-us-central1`.INFORMATION_SCHEMA.SCHEMATA;

Datasets en INFORMATION_SCHEMA

O podemos usar la vista TABLES para listar todas las tablas dentro de una región, por ejemplo us-central1:

SELECT * FROM `region-us-central1`.INFORMATION_SCHEMA.TABLES;

Tablas en INFORMATION_SCHEMA

Pros:

• Acceso fácil y programático con SQL directamente en BigQuery.
• Información muy detallada; por ejemplo, la vista TABLES incluye creation_time y ddl, y la vista TABLE_STORAGE aporta datos sobre total_rows y el tamaño de la tabla (almacenamiento físico y lógico).

Contras:

• La información se acota, como mucho, a una región (incluso en la vista a nivel de organización). Como la lista de regiones ya es bastante larga y puede cambiar dinámicamente, hacer scripts alrededor no siempre es lo ideal.
• No es gratis, aunque el costo suele ser manejable en la mayoría de los casos de uso.

Como suele pasar, nuestros clientes más técnicos nos retaron y nos pidieron tener visibilidad de toda la organización y, además, recuperar detalles relevantes de las tablas a lo largo de los proyectos.

Terminamos escribiendo un pequeño script que hace justo eso, y puedes encontrarlo también en nuestro GitHub. Implica configurar algunas variables de entorno que aprovisionan una service-account a nivel de organización y luego recorren todos los proyectos y datasets de esa organización, recuperando las tablas dentro de esos datasets junto con los detalles relevantes (en este caso: tamaño de la tabla). Si bien se necesitan conocimientos básicos de Python y algo de familiaridad con la API de BigQuery, este enfoque ofrece la mayor flexibilidad (puedes recuperar otras propiedades de la tabla revisando la documentación de la API), y los datos se pueden escribir en el formato que prefieras para consumo downstream.

Pros:

• Máxima flexibilidad:

- Extrae de la API todos los datos que necesites.

- Los resultados se pueden generar en el formato que prefieras.

- Agrega filtros o extiende el script tanto como quieras.

• Gratis, de DoiT para ti.

Contras:

• Requiere algo de tiempo y esfuerzo para escribir el script… ¡pero ya hicimos buena parte del trabajo por ti!