Skip to main content

Proceso de Carga de SMS de Twilio a MySQL

1. Resumen General

Este proceso automatizado descarga los registros de mensajes SMS de Twilio y los carga en una base de datos MySQL (ccrepo) para análisis y almacenamiento. Consta principalmente de dos scripts de Python que se ejecutan diariamente a través de cron:

  1. bulk_job_request_twilio.py: Solicita a Twilio la creación de un trabajo de exportación masiva para un rango de fechas específico (configurado para "hoy - 2 días"). Twilio procesa este trabajo de forma asíncrona y notifica por correo electrónico cuando el archivo está listo.
  2. upload_daily_twilio_sms_to_db.py: Obtiene directamente la URL de la exportación diaria pre-generada por Twilio para una fecha específica ("hoy - 2 días"), descarga el archivo (Gzip + JSON Lines), lo descomprime y carga los datos en la tabla twilio_messages_sms de la base de datos MySQL ccrepo. Este es el script principal.

El proceso utiliza credenciales de Twilio almacenadas en variables de entorno y credenciales de base de datos almacenadas en /etc/odbc.ini.

2. APIs de Twilio Utilizadas

2.1. API: Create Twilio Bulk Export Job (Crear Trabajo de Exportación)

  • Propósito: Inicia un trabajo asíncrono para exportar datos de Mensajes de Twilio para un rango de fechas especificado.
  • Endpoint: POST https://bulkexports.twilio.com/v1/Exports/Messages/Jobs
  • Autenticación: HTTP Basic Auth (usando TWILIO_ACCOUNT_SID y TWILIO_AUTH_TOKEN).
  • Tipo de Cuerpo de Solicitud: application/x-www-form-urlencoded
  • Parámetros Clave:
    • StartDay (Fecha YYYY-MM-DD, Requerido): Fecha inicio (inclusive).
    • EndDay (Fecha YYYY-MM-DD, Requerido): Fecha fin (inclusive).
    • FriendlyName (String, Opcional): Nombre descriptivo para el trabajo.
    • Email (String, Opcional): Dirección de correo para notificar cuando el trabajo esté listo.
    • WebhookUrl (URL String, Opcional): URL para notificar vía webhook.
    • WebhookMethod (String GET/POST, Opcional): Método HTTP para el webhook.
  • Respuesta Exitosa: Objeto JSON describiendo el trabajo creado, incluyendo el job_sid.
  • Notas: El trabajo se ejecuta asíncronamente. El enlace de descarga del archivo resultante se obtiene consultando el estado del trabajo (job_sid) a través de la API de Jobs. Este API fue usada por el script bulk_job_request_twilio.py.

2.2. API: Get Twilio Daily Message Export (Obtener Exportación Diaria)

  • Propósito: Recupera la URL de descarga directa para el archivo de exportación diaria pre-generada de datos de Mensajes para una fecha específica.
  • Endpoint: GET https://bulkexports.twilio.com/v1/Exports/Messages/Days/{day}
  • Parámetro en URL:
    • {day} (Fecha YYYY-MM-DD, Requerido): El día específico para la exportación en la ruta URL.
  • Autenticación: HTTP Basic Auth (usando TWILIO_ACCOUNT_SID y TWILIO_AUTH_TOKEN).
  • Respuesta Exitosa: HTTP 307 Temporary Redirect. El encabezado Location en la respuesta contiene la URL final de descarga (usualmente una URL de S3 pre-firmada). Clientes como la librería requests de Python siguen esta redirección automáticamente.
  • Respuesta de Error: Puede devolver HTTP 404 si la exportación para el día especificado no se encuentra o no está disponible vía este endpoint. Esto puede ocurrir si el día resultó sin registros (Twilio podría redirigir a un placeholder como empty.json que luego resulta en 404).
  • Formato del Archivo: El archivo obtenido de la URL de descarga final es típicamente Gzip (.gz). En la implementación de este proyecto, se observó que el contenido era JSON Lines (NDJSON), aunque la documentación de Twilio a menudo se refiere a CSV. El script upload_daily_twilio_sms_to_db.py está diseñado para manejar Gzip + JSON Lines.

3. Scripts de Python

3.1. Script: bulk_job_request_twilio.py

  • Propósito: Crea una solicitud diaria de trabajo de exportación masiva de mensajes en Twilio.
  • Flujo de Trabajo:
    1. Calcula la fecha objetivo (hoy - 2 días).
    2. Construye un FriendlyName incluyendo la fecha actual (ej: MagicalCredit_TwilioSmsReport_YYYY-MM-DD).
    3. Lee las credenciales de Twilio (TWILIO_ACCOUNT_SID, TWILIO_AUTH_TOKEN) de las variables de entorno.
    4. Llama a la API "Create Export Job" (POST .../Jobs) con StartDay y EndDay establecidos en la fecha objetivo (hoy - 2 días), el FriendlyName generado y una dirección de correo configurada.
    5. Imprime la respuesta de la API (éxito o error).
  • Configuración:
    • Requiere las variables de entorno TWILIO_ACCOUNT_SID y TWILIO_AUTH_TOKEN.
  • Dependencias: Librería requests (pip3 install requests).
  • Ejecución: 
    python3 /usr/local/cleverideas/bulk_job_request_twilio.py
  • Ejemplo de Configuración Cron: 
    # En crontab (asegurarse que las variables estén definidas)
# Defines Twilio credentials for cron session
TWILIO_ACCOUNT_SID="xxxxxxxxxxxxxxxxxxxxxxxx"
TWILIO_AUTH_TOKEN="xxxxxxxxxxxxxxxxxxxxxxxxxx"

# Run daily at 4:00 AM
0 4 * * * /usr/bin/python3 /usr/local/cleverideas/bulk_job_request_twilio.py >> /usr/local/cleverideas/bulk_job_request_twilio.log 2>&1
  • Logging: Redirige stdout y stderr a /usr/local/cleverideas/bulk_job_request_twilio.log.
  • Notas: Este script solo solicita el trabajo. No descarga ni procesa datos. Depende de la notificación por correo de Twilio (o potencialmente revisiones manuales) para saber cuándo la exportación está lista.

3.2. Script: upload_daily_twilio_sms_to_db.py (Principal en Uso)

  • Propósito: Obtiene la exportación diaria de mensajes de Twilio (para hoy - 2 días), descarga el archivo, y carga sus contenidos (esperado JSON Lines) en la tabla MySQL ccrepo.twilio_messages_sms.
  • Flujo de Trabajo:
    1. Calcula la fecha objetivo (hoy - 2 días).
    2. Lee las credenciales de Twilio (TWILIO_ACCOUNT_SID, TWILIO_AUTH_TOKEN) de las variables de entorno.
    3. Llama a la API "Get Daily Export" (GET .../Days/{fecha_objetivo}).
    4. Sigue la redirección HTTP 307 para obtener la URL final de descarga de S3. Maneja errores 404 si la exportación diaria no se encuentra.
    5. Lee los detalles de conexión a la base de datos del archivo /etc/odbc.ini (específicamente la sección [Repo]).
    6. Establece una conexión a la base de datos MySQL ccrepo usando el conjunto de caracteres utf8mb4 y la collation utf8mb4_unicode_ci.
    7. Descarga el archivo desde la URL de S3 como un stream.
    8. Descomprime el stream Gzip sobre la marcha.
    9. Lee el contenido descomprimido línea por línea, asumiendo el formato JSON Lines (NDJSON).
    10. Por cada línea JSON válida:
      • Parsea el objeto JSON.
      • Formatea los campos de fecha (date_created, date_sent, date_updated) para compatibilidad con MySQL.
      • Mapea las claves JSON (sid, account_sid, to, from, status, etc.) a las columnas de base de datos correspondientes (sid, account_sid, to_number, from_number, status, etc.).
      • Ejecuta una sentencia INSERT ... ON DUPLICATE KEY UPDATE para la tabla twilio_messages_sms. Maneja errores potenciales de base de datos por fila.
    11. Realiza un COMMIT de la transacción de base de datos después de procesar todas las líneas.
    12. Cierra la conexión de base de datos.
  • Configuración:
    • Requiere las variables de entorno TWILIO_ACCOUNT_SID, TWILIO_AUTH_TOKEN.
    • Requiere el archivo /etc/odbc.ini con una sección [Repo] que contenga las credenciales de BD (Server, User, Password, Database, Port).
    • La variable TABLE_NAME en el script debe estar configurada con el nombre de la tabla destino (ej: twilio_messages_sms).
  • Dependencias: requests, mysql.connector==8.0.27, configparser, datetime, gzip, json, io, os (pip3 install requests mysql-connector-python==8.0.27).
  • Ejecución: 
    python3 /usr/local/cleverideas/upload_daily_twilio_sms_to_db.py
  • Ejemplo de Configuración Cron: 
    # En crontab (asegurarse que las variables estén definidas)
# Defines Twilio credentials for cron session
TWILIO_ACCOUNT_SID="xxxxxxxxxxxxxxxxxxxxxxxxxx"
TWILIO_AUTH_TOKEN="xxxxxxxxxxxxxxxxxxxxxxxxxx"

# Run daily at 5:00 AM
0 5 * * * /usr/bin/python3 /usr/local/cleverideas/upload_daily_twilio_sms_to_db.py >> /usr/local/cleverideas/upload_daily_twilio_sms_to_db.log 2>&1
  • Logging: Redirige stdout y stderr a /usr/local/cleverideas/upload_daily_twilio_sms_to_db.log. Incluye mensajes básicos de progreso y advertencias/errores durante el procesamiento.

4. Esquema de Base de Datos

  • Nombre de Tabla: twilio_messages_sms
  • Base de Datos: ccrepo (Configurada en la sección [Repo] de /etc/odbc.ini)
  • Propósito: Almacena los registros individuales de mensajes SMS obtenidos de Twilio Bulk Exports.
  • Requisitos:
    • La base de datos MySQL debe soportar el conjunto de caracteres utf8mb4 para almacenar correctamente emojis y otros caracteres especiales.
    • La conexión desde el script de Python debe especificar charset='utf8mb4' y collation='utf8mb4_unicode_ci' (especialmente si el servidor MySQL es versión < 8.0).
  • Sentencia CREATE TABLE (MySQL): 
    CREATE TABLE twilio_messages_sms (
    sid VARCHAR(34) PRIMARY KEY,
    account_sid VARCHAR(34) NOT NULL,
    to_number VARCHAR(20),
    from_number VARCHAR(20),
    messaging_service_sid VARCHAR(34) NULL,
    body TEXT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL,
    status VARCHAR(20),
    direction VARCHAR(20),
    num_segments INT NULL,
    num_media INT NULL,
    price DECIMAL(10, 5) NULL,
    error_code INT NULL,
    date_created TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    date_sent TIMESTAMP NULL,
    date_updated TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    tags TEXT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
  • Columnas Clave:
    • sid: Clave Primaria (Message SID de Twilio).
    • body: Contenido del mensaje (usa utf8mb4).
    • status: Estado de entrega.
    • date_created, date_sent, date_updated: Timestamps relevantes.

5. Guía de Configuración e Instalación

Esta sección resume los pasos necesarios para configurar el entorno.

5.1. Variables de Entorno (Credenciales Twilio)

Los scripts leen el Account SID y el Auth Token de Twilio desde variables de entorno.

  1. Método Recomendado (Crontab): Definir las variables directamente al inicio del archivo crontab (ver sección Cron más abajo).
  2. Método Alternativo (Usuario): Añadir a ~/.bashrc del usuario que ejecuta los scripts: 
    nano ~/.bashrc
# Añadir al final:
export TWILIO_ACCOUNT_SID="TU_TWILIO_ACCOUNT_SID"
export TWILIO_AUTH_TOKEN="TU_AUTH_TOKEN_REAL_AQUI"
# Guardar (Ctrl+O, Enter), Salir (Ctrl+X)
source ~/.bashrc
    Recordatorio: Si usas cron, definir las variables en el crontab es más seguro.

5.2. Configuración Base de Datos (odbc.ini)

El script upload_daily_twilio_sms_to_db.py lee las credenciales de MySQL desde /etc/odbc.ini. Asegúrate de que el archivo exista y contenga la sección correcta (por defecto [Repo]) con los parámetros necesarios:

[Repo]
Driver=MYSQL
Description=Repositorio BI
Server=10.128.0.56        # <-- Host/IP de tu BD
User=ccuser             # <-- Usuario de BD
Password=ccdatapassword2008 # <-- Contraseña de BD
Database=ccrepo           # <-- Nombre de la BD
Option=3
Port=3306               # <-- Puerto de BD
Charset=utf8mb4           # <-- Opcional aquí, pero bueno tenerlo
  • El script necesita permisos de lectura para /etc/odbc.ini.
  • El usuario de BD (ccuser) necesita permisos INSERT, SELECT, UPDATE en la tabla twilio_messages_sms de la BD ccrepo.

5.3. Dependencias de Python

Instalar las librerías necesarias:

pip3 install requests mysql-connector-python==8.0.27

(configparser, datetime, gzip, json, io, os son estándar)

5.4. Permisos de Scripts

Asegurar que los scripts de Python sean legibles (y opcionalmente ejecutables) por el usuario que los corre:

# Opcional, pero buena práctica si no se hizo antes
# sudo chmod +x /usr/local/cleverideas/bulk_job_request_twilio.py
# sudo chmod +x /usr/local/cleverideas/upload_daily_twilio_sms_to_db.py
# Asegurar permisos de lectura
sudo chmod u+r /usr/local/cleverideas/*.py
# O ajustar propietario/grupo si es necesario con chown/chgrp

5.5. Configuración de Cron Job

  1. Encontrar la ruta de python3: which python3 (ej: /usr/bin/python3)

  2. Editar el crontab del usuario deseado: crontab -e (usar select-editor si es necesario para elegir nano)

  3. Añadir las siguientes líneas, ajustando rutas y credenciales:

    # Define Twilio credentials for cron session
TWILIO_ACCOUNT_SID="AC57a0c62acc536a32ad1921f6c2f9e0df"
TWILIO_AUTH_TOKEN="93af1484bbf3dfedc0336ea2af8f0538" # ¡Reemplazar por el real!

# (Opcional) Request Twilio export job daily at 4:00 AM
# 0 4 * * * /usr/bin/python3 /usr/local/cleverideas/bulk_job_request_twilio.py >> /usr/local/cleverideas/bulk_job_request_twilio.log 2>&1

# Download daily export and load to DB daily at 5:00 AM
0 5 * * * /usr/bin/python3 /usr/local/cleverideas/upload_daily_twilio_sms_to_db.py >> /usr/local/cleverideas/upload_daily_twilio_sms_to_db.log 2>&1

  4. Guardar y cerrar el editor.

5.6. Logging

  • La salida y errores de cada script se registran en archivos .log separados dentro de /usr/local/cleverideas/ (ej: upload_daily_twilio_sms_to_db.log).
  • Monitorear estos logs regularmente para verificar la ejecución exitosa o diagnosticar errores.
Back to top