Chequeo Digital ETL

Sistema de sincronizacion automatizada de datos de encuestas desde SQL Server hacia Google BigQuery.

Chequeo Digital es una herramienta de diagnostico de madurez digital para empresas, desarrollada por Banco Interamericano de Desarrollo (BID) e implementada en Paraguay por el Ministerio de Industria y Comercio (MIC) y el Laboratorio de Aceleracion del Programa de las Naciones Unidas para el Desarrollo (AccLabPy). Este repositorio contiene el pipeline ETL que extrae los resultados de las encuestas almacenados en SQL Server, los transforma y los carga en BigQuery para su analisis.

---

Tabla de Contenidos

• Arquitectura
• Requisitos Previos
• Instalacion
• Configuracion
• Uso
• Automatizacion con Task Scheduler
• Adaptacion a otras instancias de Chequeo Digital
• Estructura del Proyecto
• Documentacion Adicional
• Licencia
• Maintainers

---

Arquitectura

El sistema implementa un pipeline ETL de 4 pasos orquestado por PowerShell, con una interfaz grafica en Python (Tkinter) para operacion y configuracion.

+---------------+     +----------+     +-------------------+     +--------------+
|  SQL Server   |---->|   CSV    |---->|  Google Cloud      |---->|   BigQuery   |
|  (BID_v2)     |     |  local   |     |  Storage (GCS)     |     |  (cd_2025)   |
+---------------+     +----------+     +-------------------+     +--------------+
      Paso 1              |                 Paso 2                    Paso 3
  test_export.ps1         |            export_to_gcs.ps1         load_to_bigquery.ps1
                          |
                     Paso 4: Normalizacion (normalize_bigquery_data.ps1)

Sincronizacion Incremental (DeltaTracking)

El sistema utiliza una tabla DeltaTracking en SQL Server para rastrear el ultimo IdTestUsuario sincronizado. Cada ejecucion incremental exporta solo los registros nuevos (IdTestUsuario > LastTestUserID), evitando duplicados y reduciendo tiempos de carga. Esta tabla tambien se sincroniza a BigQuery como registro de auditoria.

Componentes principales

Componente	Tecnologia	Funcion
GUI Admin	Python / Tkinter	Interfaz grafica para exportar, configurar y programar tareas
Pipeline ETL	PowerShell	Orquestacion del flujo SQL Server -> CSV -> GCS -> BigQuery
DeltaTracking	SQL Server + BigQuery	Control de sincronizacion incremental y auditoria
Task Scheduler	Windows Task Scheduler	Automatizacion diaria del pipeline

---

Requisitos Previos

Requisito	Version
Windows	Server 2012 R2 o superior / Windows 10+
Python	3.8 o superior
PowerShell	5.1 o superior
SQL Server	2014 o superior, con base de datos BID_v2
Google Cloud SDK	Ultima version (gcloud, bq, gsutil)
Driver ODBC SQL Server	Descargar aqui

Servicios de Google Cloud requeridos

• BigQuery API habilitada
• Cloud Storage con un bucket configurado
• Service Account con roles: BigQuery Data Editor, BigQuery Job User, Storage Object Admin
• Archivo de credenciales (sa.json) descargado

---

Instalacion

1. Clonar el repositorio

git clone https://github.com/UNDP-AccLabPy/chequeo-etl.git C:\chequeo
cd C:\chequeo

2. Instalar dependencias

Opcion automatizada (recomendado):

install_system.bat

O con PowerShell (mas opciones):

.\install_system.ps1           # Instalacion completa
.\install_system.ps1 -Minimal  # Solo dependencias esenciales

Opcion manual:

pip install -r requirements-minimal.txt

3. Configurar credenciales GCP

1. Coloca el archivo de Service Account en config\sa.json
2. Autentica el SDK de Google Cloud:

gcloud auth activate-service-account --key-file="C:\chequeo\config\sa.json"
gcloud config set project tu-proyecto-id

4. Crear la tabla DeltaTracking en SQL Server

Ejecuta en SQL Server Management Studio (o sqlcmd) sobre tu base de datos:

sqlcmd -S TU_SERVIDOR -d TU_BASE_DATOS -i scripts\setup_delta_tracking_complete.sql

Si migras desde un servidor que ya tenia datos en BigQuery, ajusta el LastTestUserID inicial al maximo IdTestUsuario que ya esta en BigQuery. Ver docs/INSTALLATION_GUIDE.md.

---

Configuracion

Toda la configuracion se gestiona desde un unico archivo JSON y desde la GUI.

Archivo de configuracion

config\gui_config.json

{
  "sql_server": "TU_SERVIDOR\\INSTANCIA",
  "sql_database": "BID_v2",
  "sql_user": "ChequeoReader",
  "sql_password": "TuPassword",
  "sql_auth_type": "sql",
  "gcp_project_id": "tu-proyecto-gcp",
  "gcp_dataset_id": "cd_2025",
  "gcp_table_id": "cd_data_copy",
  "gcp_bucket": "tu-bucket-gcs",
  "gcp_sa_path": "C:\\chequeo\\config\\sa.json",
  "task_name": "ChequeoDigital-Sync",
  "task_time": "23:00",
  "base_dir": "C:\\chequeo",
  "logs_dir": "C:\\chequeo\\logs",
  "exports_dir": "C:\\chequeo\\exports"
}

Tambien puedes editar estos valores directamente desde la pestana Configuracion de la GUI.

Crear usuario SQL Server (solo lectura + DeltaTracking)

CREATE LOGIN ChequeoReader WITH PASSWORD = 'TuPassword';
USE BID_v2;
CREATE USER ChequeoReader FOR LOGIN ChequeoReader;
ALTER ROLE db_datareader ADD MEMBER ChequeoReader;
GRANT SELECT, INSERT ON dbo.DeltaTracking TO ChequeoReader;

---

Uso

Interfaz Grafica (recomendado)

ChequeoAdmin.bat

O directamente:

python gui\chequeo_admin.py

La GUI tiene 4 pestanas:

Pestana	Funcion
Exportacion	Ejecutar sincronizacion incremental o completa, comparar datos SQL vs BigQuery, ver historial de tracking
Configuracion	Editar conexion SQL Server, parametros GCP, probar conexiones
Programacion	Crear/editar tareas programadas en Windows Task Scheduler
Logs	Visualizar logs del sistema en tiempo real

Linea de comandos

Exportacion incremental completa (SQL -> GCS -> BigQuery):

.\scripts\export_to_bigquery.ps1

Exportacion completa (ignora delta, re-exporta todo):

.\scripts\export_to_bigquery.ps1 -Full

Comparar datos entre SQL Server y BigQuery:

.\scripts\compare_data_sync_simple.ps1

Verificar estado del sistema:

.\scripts\system_check.ps1

Ver historial de DeltaTracking:

.\scripts\view_tracking_history.ps1

---

Automatizacion con Task Scheduler

El sistema puede ejecutarse automaticamente cada dia usando Windows Task Scheduler.

Configuracion rapida

Desde la GUI: pestana Programacion -> Crear Tarea.

O desde PowerShell:

.\scripts\setup_task_scheduler.ps1
.\scripts\setup_task_scheduler.ps1 -ExecutionTime "02:00"  # Hora personalizada

Tareas recomendadas

Tarea	Horario	Script
Pre-verificacion	01:30 AM	scripts\pre_sync_check.ps1
Sincronizacion	02:00 AM	scripts\scheduled_export_automated.ps1
Limpieza de logs	Domingos 03:00 AM	scripts\cleanup_logs.ps1

Ver docs/TASK_SCHEDULER_AUTOMATION_GUIDE.md para la guia completa.

---

Adaptacion a otras instancias de Chequeo Digital

Este sistema puede adaptarse para sincronizar datos desde cualquier instancia de Chequeo Digital que use SQL Server como backend. Los pasos son:

1. Clonar y configurar

git clone https://github.com/UNDP-AccLabPy/chequeo-etl.git C:\chequeo

2. Ajustar parametros de conexion

Edita config\gui_config.json con los datos de tu entorno:

• sql_server: nombre del servidor SQL Server (ej. MISERVIDOR\SQLEXPRESS)
• sql_database: nombre de la base de datos BID (ej. BID_v2, BID_v3)
• sql_user / sql_password: credenciales del usuario SQL con acceso de lectura
• gcp_project_id: ID de tu proyecto en Google Cloud
• gcp_dataset_id: nombre del dataset en BigQuery donde se cargaran los datos
• gcp_table_id: nombre de la tabla destino en BigQuery
• gcp_bucket: nombre del bucket de Cloud Storage para archivos temporales

3. Crear proyecto GCP y Service Account

1. Crear un proyecto en Google Cloud Console
2. Habilitar las APIs: BigQuery, Cloud Storage
3. Crear una Service Account con los roles: BigQuery Data Editor, BigQuery Job User, Storage Object Admin
4. Descargar el JSON de credenciales y guardarlo como config\sa.json
5. Crear un bucket en Cloud Storage

4. Preparar SQL Server

1. Crear un usuario con permisos de lectura (ver seccion Configuracion)
2. Crear la tabla DeltaTracking ejecutando scripts\setup_delta_tracking_complete.sql
3. Si ya tienes datos en BigQuery de una instancia anterior, ajusta el LastTestUserID en DeltaTracking al maximo IdTestUsuario que ya esta cargado

5. Adaptar la consulta SQL (si es necesario)

La consulta principal esta en scripts\tests\test_export.ps1. Si tu esquema de base de datos difiere (otros nombres de tablas, columnas adicionales, etc.), debes modificar la consulta SQL en ese archivo y el schema JSON correspondiente en scripts\schemas\.

6. Ejecutar primera sincronizacion

# Verificar que todo este configurado
.\scripts\system_check.ps1

# Ejecutar exportacion completa inicial
.\scripts\export_to_bigquery.ps1 -Full -CreateDataset

# Verificar sincronizacion
.\scripts\compare_data_sync_simple.ps1

7. Programar ejecucion automatica

.\scripts\setup_task_scheduler.ps1 -ExecutionTime "02:00"

---

Estructura del Proyecto

C:\chequeo\
|-- gui/                            # Aplicaciones GUI (Python/Tkinter)
|   |-- chequeo_admin.py            # GUI principal de administracion
|   |-- bigquery_exporter.py        # Wrapper CLI en Python
|   +-- modern_exporter*.py         # GUIs alternativas de exportacion
|
|-- scripts/                        # Scripts de automatizacion (PowerShell)
|   |-- tests/
|   |   +-- test_export.ps1         # Generacion de CSV desde SQL Server
|   |-- export_to_bigquery.ps1      # Orquestador principal del pipeline
|   |-- export_to_gcs.ps1           # Subida a Google Cloud Storage
|   |-- load_to_bigquery.ps1        # Carga de GCS a BigQuery
|   |-- sync_delta_tracking_*.ps1   # Sincronizacion de tabla de tracking
|   |-- scheduled_export_*.ps1      # Scripts para ejecucion programada
|   |-- compare_data_sync*.ps1      # Comparacion SQL Server vs BigQuery
|   |-- system_check.ps1            # Verificacion del sistema
|   |-- schemas/
|   |   +-- delta_tracking_schema.json
|   +-- *.sql                       # Scripts de creacion de tablas
|
|-- config/                         # Configuracion
|   |-- gui_config.json             # Parametros de conexion y ejecucion
|   +-- sa.json                     # Credenciales GCP (no incluido en repo)
|
|-- exports/                        # CSVs temporales generados
|-- logs/                           # Logs de ejecucion
|-- docs/                           # Documentacion detallada
|
|-- ChequeoAdmin.bat                # Punto de entrada principal (lanza GUI)
|-- install_system.bat              # Script de instalacion
|-- install_system.ps1              # Script de instalacion (PowerShell)
|-- requirements.txt                # Dependencias completas
|-- requirements-minimal.txt        # Dependencias minimas
+-- README.md

---

Documentacion Adicional

Documento	Descripcion
Guia de Instalacion	Instalacion paso a paso con verificacion
Guia Windows Server	Instalacion en Windows Server 2012+
Manual de Usuario GUI	Manual completo de la interfaz grafica
Integracion BigQuery	Detalle del pipeline y configuracion GCP
Automatizacion Task Scheduler	Configuracion completa de tareas programadas
Configuracion de Horarios	Como cambiar horarios de tareas programadas
Parametros SQL Server	Referencia de parametros de conexion por archivo
Compilacion a EXE	Como compilar la GUI como ejecutable portable
Migracion Win Server 2012	Guia de migracion a servidores antiguos
Soluciones Implementadas	Bugs resueltos y fixes aplicados
Resumen del Sistema	Vision tecnica completa del sistema

---

Licencia

Este proyecto esta licenciado bajo la GNU General Public License v3.0.

---

Maintainers

UNDP Acceleration Lab Paraguay (AccLabPy)

• Laboratorio de Aceleracion - PNUD Paraguay

---

Nota: Los archivos config/sa.json (credenciales GCP) y datos sensibles no se incluyen en el repositorio. Cada instancia debe configurar sus propias credenciales.