¿Qué es ETL?
ETL es un proceso que extrae datos de varios sistemas de origen, los cambia (a través de cálculos, concatenaciones, etc.) y luego los coloca en el sistema Data Warehouse. ETL significa Extraer, Transformar y Cargar.
Es fácil creer que construir un almacén de datos es tan simple como extraer datos de numerosas fuentes e introducirlos en una base de datos de almacén de datos. Esto está lejos de ser el caso, y se requiere un procedimiento ETL complicado. El proceso ETL, que es técnicamente complejo, implica la participación activa de una variedad de partes interesadas, incluidos desarrolladores, analistas, evaluadores y altos ejecutivos.
Para preservar su valor como herramienta de toma de decisiones, el sistema de almacenamiento de datos debe desarrollarse en sincronía con los desarrollos comerciales. ETL es un proceso regular (diario, semanal, mensual) de un sistema de almacenamiento de datos que debe ser ágil, automatizado y debidamente documentado.
¿Cómo funciona ETL?
Aquí aprenderemos cómo funciona el proceso ETL paso a paso:
Paso 1) Extracción
Los datos se extraen del sistema de origen y se colocan en el área de ensayo durante la extracción. Si se requieren transformaciones, se realizan en el área de preparación para que el rendimiento del sistema de origen no se vea afectado. La reversión será difícil si los datos dañados se transfieren directamente desde el origen a la base de datos del almacén de datos. Antes de mover los datos extraídos al almacén de datos, se pueden validar en el área de ensayo.
Los almacenes de datos pueden combinar sistemas con diferentes hardware, sistemas de administración de bases de datos, sistemas operativos y protocolos de comunicación. Los almacenes de datos deben combinar sistemas con diferentes DBMS, hardware, sistemas operativos y protocolos de comunicación. Las fuentes pueden incluir programas heredados como mainframes, aplicaciones personalizadas, dispositivos de punto de contacto como cajeros automáticos y conmutadores de llamadas, archivos de texto, hojas de cálculo, ERP, datos de proveedores y socios, etc.
Por lo tanto, antes de extraer datos y cargarlos físicamente, se requiere un mapa de datos lógico. La conexión entre las fuentes y los datos de destino se muestra en este mapa de datos.
Tres métodos de extracción de datos:
- Extracción parcial – Si el sistema de origen le avisa cuando se modifica un registro, esa es la forma más sencilla de obtener los datos.
- Extracción parcial (sin notificación de actualización) – No todos los sistemas pueden enviar una notificación cuando se produce una actualización; pero, pueden indicar a los registros que han sido cambiados y proporcionar la extracción de esos registros.
- extracto completo – Ciertos sistemas son incapaces de determinar qué datos se han modificado en absoluto. En este escenario, la única forma de sacar los datos del sistema es realizar una extracción completa. Este enfoque requiere tener una copia de seguridad del extracto anterior en el
mismo formato a la mano para identificar los cambios que se han hecho.
Independientemente del método adoptado, la extracción no debería tener un impacto en el rendimiento o el tiempo de respuesta de los sistemas fuente. Estas son bases de datos de producción en tiempo real. Cualquier ralentización o bloqueo podría tener un impacto en los resultados de la empresa.
Paso 2) Transformación
Los datos recuperados del servidor de origen son crudos e inutilizables en su estado original. Como resultado, debe limpiarse, mapearse y transformarse. En realidad, este es el paso clave en el que el proceso ETL agrega valor y transforma los datos para producir informes de BI significativos.
Es un concepto clave de ETL en el que aplica una colección de funciones a los datos extraídos. movimiento directo or pasar por esoa es el tipo de datos que no requiere ninguna transformación.
Puede ejecutar operaciones personalizadas en los datos durante el paso de transformación. Por ejemplo, suponga que el cliente desea una suma de ingresos por ventas que no existe en la base de datos. o si el nombre y apellido en una tabla están en columnas separadas. Antes de cargar, se pueden concatenar.
Los siguientes son algunos ejemplos de problemas de integridad de datos:
- Diferentes ortografías del mismo individuo, como Prashant, Parshant, etc.
- Hay muchas formas de representar el nombre de una empresa, como Google, Google Inc.
- Se utilizan varios nombres, como Cleaveland y Cleveland.
- Es posible que diferentes aplicaciones produzcan varios números de cuenta para el mismo cliente.
- Algunos archivos de datos necesarios se dejan en blanco.
Paso 3) Cargando
La etapa final en el proceso de ETL es cargar datos en la base de datos del almacén de datos de destino. Un gran volumen de datos se carga en un período de tiempo relativamente corto en un almacén de datos típico. Como resultado, el proceso de carga debe optimizarse para el rendimiento.
En caso de falla de carga, se deben implementar procedimientos de recuperación para que las operaciones puedan reiniciarse desde el punto de falla sin comprometer la integridad de los datos. Los administradores de almacenamiento de datos deben monitorear, continuar y detener las cargas en función del rendimiento del servidor.
Tipos de carga:
- Carga inicial - llenando todo
las tablas del almacén de datos - Carga incremental — implementando en curso
modificaciones según sea necesario sobre una base regular - Actualización completa — borrar el contenido
de una o más tablas y recargarlas con datos nuevos
Verificación de carga
- Verifique que los datos del campo clave no falten o sean nulos.
- Se deben probar las vistas de modelado basadas en tablas de destino.
- Examine los valores combinados3 y las medidas calculadas.
- Comprobaciones de datos en las tablas de dimensiones e historial.
- Examine los informes de BI en la tabla de hechos y dimensiones cargada.
Configuración de ETL usando PythonScript
Como resultado, debe ejecutar la carga de transformación de extracción (ETL) básica desde varias bases de datos a un almacén de datos para realizar la agregación de datos para la inteligencia comercial. Hay varios paquetes ETL disponibles que creía que eran excesivos para su caso de uso básico.
Le mostraré cómo extraer datos de MySQL, SQL-server y firebird en este artículo. Usando Python 3.6, transforme los datos y cárguelos en el servidor SQL (almacén de datos).
En primer lugar, tenemos que crear un directorio para nuestro proyecto:
python_etl |__main.py |__db_credentials.py |__variables.py |__sql_queries.py |__etl.py
Para configurar ETL usando Python, deberá generar los siguientes archivos en el directorio de su proyecto.
- credenciales_db.py: Debe tener toda la información necesaria para conectarse a todas las bases de datos. como la contraseña de la base de datos, el número de puerto, etc.
- sql_consultas.py: Todas las consultas de base de datos de uso común para extraer y cargar datos en formato de cadena deben estar disponibles.
- etl.py: Conéctese a la base de datos y realice las consultas necesarias realizando todos los procedimientos necesarios.
- principal.py: Responsable de administrar el flujo de operaciones y ejecutar las operaciones esenciales en un orden específico.
En esta sección de sql_queries.py, este es el lugar donde almacenaremos todas nuestras consultas sql para extraerlas de las bases de datos de origen e importarlas a nuestra base de datos de destino (almacén de datos)
Configurar credenciales y variables de la base de datos
En variables.py, crea una variable para registrar el nombre de la base.
datawarehouse_name = 'su_datawarehouse_name'
Configure todas las cadenas de conexión y las credenciales de la base de datos de origen y de destino en db_credentials.py como se muestra a continuación. Guarde la configuración como una lista para que podamos iterarla cuando sea necesario a través de muchas bases de datos más adelante.
from variables import datawarehouse_name datawarehouse_name = 'your_datawarehouse_name' # sql-server (target db, datawarehouse) datawarehouse_db_config = { 'Trusted_Connection': 'yes', 'driver': '{SQL Server}', 'server': 'datawarehouse_sql_server', ' database': '{}'.format(datawarehouse_name), 'user': 'your_db_username', 'password': 'your_db_password', 'autocommit': True, } # sql-server (source db) sqlserver_db_config = [ { 'Trusted_Connection ': 'yes', 'driver': '{SQL Server}', 'server': 'your_sql_server', 'database': 'db1', 'user': 'your_db_username', 'password': 'your_db_password', ' autocommit': True, } ] # mysql (source db) mysql_db_config = [ { 'user': 'your_user_1', 'password': 'your_password_1', 'host': 'db_connection_string_1', 'database': 'db_1', } , { 'usuario': 'su_usuario_2', 'contraseña': 'su_contraseña_2', 'host': 'db_connection_string_2', 'database': 'db_2', }, ] # firebird (fuente db) fdb_db_config = [ { 'dsn' : "/su/ruta/a/fuente.db", 'usuario': "su_nombre_de_usuario", 'contraseña': "usted r_contraseña", } ]
Consultas SQL
En esta sección de sql_queries.py, este es el lugar donde almacenaremos todas nuestras consultas sql para extraerlas de las bases de datos de origen e importarlas a nuestra base de datos de destino (almacén de datos).
Tenemos que implementar varias sintaxis para cada base de datos porque estamos trabajando con múltiples plataformas de datos. Podemos hacer esto separando las consultas según el tipo de base de datos.
# consultas de ejemplo, serán diferentes en diferentes plataformas de base de datos firebird_extract = (''' SELECT fbd_column_1, fbd_column_2, fbd_column_3 FROM fbd_table; ''') firebird_insert = (''' INSERT INTO table (column_1, column_2, column_3) VALUES (?, ?, ?) ''') firebird_extract_2 = (''' SELECCIONE fbd_column_1, fbd_column_2, fbd_column_3 FROM fbd_table_2; ''') firebird_insert_2 = (''' INSERT INTO table_2 (column_1, column_2, column_3) VALORES (?, ?, ? ) ''') sqlserver_extract = (''' SELECT sqlserver_column_1, sqlserver_column_2, sqlserver_column_3 FROM sqlserver_table ''') sqlserver_insert = (''' INSERTAR EN tabla (columna_1, columna_2, columna_3) VALORES (?, ?, ?) ''' ) mysql_extract = (''' SELECT mysql_column_1, mysql_column_2, mysql_column_3 FROM mysql_table ''') mysql_insert = (''' INSERT INTO table (column_1, column_2, column_3) VALUES (?, ?, ?) ''') # exportando consultas class SqlQuery: def __init__(self, extract_query, load_query): self.extract_query = extract_query self.load_query = load_query # cre ate instances for SqlQuery class fbd_query = SqlQuery(firebird_extract, firebird_insert) fbd_query_2 = SqlQuery(firebird_extract_2, firebird_insert_2) sqlserver_query = SqlQuery(sqlserver_extract, sqlserver_insert) mysql_query = SqlQuery(mysql_extract, mysql_insert) # store as list for iteration fbd_queries = [fbdquery, fbd_query_2] sqlserver_queries = [sqlserver_query] mysql_queries = [mysql_query]
Extraer Transformar Cargar
Para configurar ETL usando Python para las fuentes de datos mencionadas anteriormente, necesitará los siguientes módulos:
# módulos de Python import mysql.connector import pyodbc import fdb # variables from variables import datawarehouse_name
Podemos usar dos técnicas en esto: etl() y etl_process().
etl_process() es el procedimiento para establecer una conexión de fuente de base de datos y llamar al método etl() basado en la plataforma de la base de datos.
Y en el segundo método, que es el método etl(), primero ejecuta la consulta de extracción, luego almacena los datos SQL en los datos variables y los inserta en la base de datos de destino, que es nuestro almacén de datos. La transformación de datos se puede lograr alterando la variable de datos de la tupla de tipo.
def etl(query, source_cnx, target_cnx): # extraer datos de la base de datos fuente source_cursor = source_cnx.cursor() source_cursor.execute(query.extract_query) data = source_cursor.fetchall() source_cursor.close() # cargar datos en la base de datos del almacén si datos: target_cursor = target_cnx.cursor() target_cursor.execute("USE {}".format(datawarehouse_name)) target_cursor.executemany(query.load_query, data) print('datos cargados en la base de datos del almacén') target_cursor.close() else : print('los datos están vacíos') def etl_process(consultas, target_cnx, source_db_config, db_platform): # establecer conexión db fuente if db_platform == 'mysql': source_cnx = mysql.connector.connect(**source_db_config) elif db_platform == 'sqlserver': source_cnx = pyodbc.connect(**source_db_config) elif db_platform == 'firebird': source_cnx = fdb.connect(**source_db_config) else: return '¡Error! plataforma de base de datos no reconocida' # recorrer consultas sql para consulta en consultas: etl(query, source_cnx, target_cnx) # cerrar la conexión de base de datos fuente source_cnx.close()
Poniendo todo junto
Ahora, en el siguiente paso, podemos recorrer todas las credenciales en main.py y ejecutar el etl para todas las bases de datos.
Para eso tenemos que Importar todas las variables y métodos requeridos:
# variables de db_credentials import datawarehouse_db_config, sqlserver_db_config, mysql_db_config, fbd_db_config from sql_queries import fbd_queries, sqlserver_queries, mysql_queries from variables import * # métodos de etl import etl_process
El código en este archivo es responsable de iterar sobre las credenciales para conectarse a la base de datos y ejecutar las operaciones necesarias de ETL usando Python.
def main(): print('starting etl') # establecer la conexión para la base de datos de destino (sql-server) target_cnx = pyodbc.connect(**datawarehouse_db_config) # recorrer las credenciales # mysql para la configuración en mysql_db_config: try: print("loading db: " + config['base de datos']) etl_process(mysql_queries, target_cnx, config, 'mysql') excepto Excepción como error: print("etl para {} tiene error".format(config['database'])) print ('mensaje de error: {}'.format(error)) continue # sql-server for config in sqlserver_db_config: try: print("loading db: " + config['database']) etl_process(sqlserver_queries, target_cnx, config, ' sqlserver') excepto Excepción como error: print("etl para {} tiene error".format(config['database'])) print('mensaje de error: {}'.format(error)) continue # firebird for config in fbd_db_config: try: print("loading db: " + config['database']) etl_process(fbd_queries, target_cnx, config, 'firebird') excepto Excepción como error: print("etl para {} tiene error".format(config ['base de datos'])) print('mensaje de error: {}'.fo rmat(error)) continue target_cnx.close() if __name__ == "__main__": main()
En su terminal, escriba python main.py y acaba de crear un ETL utilizando un script de python puro.
Herramientas ETL
Hay varias herramientas de Data Warehousing en el mercado. Estos son algunos de los ejemplos más famosos:
1. MarkLogic:
MarkLogic es un sistema de almacenamiento de datos que utiliza una variedad de capacidades comerciales para hacer que la integración de datos sea más fácil y rápida. Puede consultar muchos tipos de datos, como documentos, relaciones y metadatos.
https://www.marklogic.com/product/getting-started/
2. Oráculo:
Oracle es la base de datos más popular de la industria. Ofrece una amplia variedad de soluciones de almacenamiento de datos para servicios locales y en la nube. Ayuda a mejorar las experiencias de los clientes al aumentar la eficiencia operativa.
https://www.oracle.com/index.html
3. Amazon RedShift:
Redshift es una solución de almacenamiento de datos de Amazon. Es una solución simple y rentable para analizar varios tipos de datos con SQL estándar y herramientas de inteligencia empresarial existentes. También permite la ejecución de consultas complejas sobre petabytes de datos estructurados.
https://aws.amazon.com/redshift/?nc2=h_m1
Conclusión
Este artículo le dio una comprensión profunda de lo que es ETL, así como un tutorial paso a paso sobre cómo configurar su ETL en Python. También le dio una lista de las mejores herramientas que la mayoría de las organizaciones utilizan hoy en día para construir sus canalizaciones de datos ETL.
La mayoría de las organizaciones hoy en día, por otro lado, tienen una gran cantidad de datos con una estructura altamente dinámica. Crear una canalización ETL desde cero para tales datos es un procedimiento difícil, ya que las organizaciones tendrán que usar una gran cantidad de recursos para crear esta canalización y luego asegurarse de que pueda mantenerse al día con el alto volumen de datos y los cambios de esquema.
Sobre el Autor
Prashant Sharma
Actualmente, estoy cursando mi Licenciatura en Tecnología (B.Tech) del Instituto de Tecnología de Vellore. Estoy muy entusiasmado con la programación y sus aplicaciones reales, incluido el desarrollo de software, el aprendizaje automático, el aprendizaje profundo y la ciencia de datos.
Espero que les guste el artículo. Si quieres conectarte conmigo entonces puedes conectarte en:
o para cualquier otra duda, puedes enviar un correo a mi tambien
Relacionado:
- '
- "
- Mi Cuenta
- lector activo
- Todos
- Amazon
- Analytics
- aplicaciones
- Reservada
- artículo
- Confirmación de Viaje
- Backup
- impulsar
- build
- Construir la
- inteligencia empresarial
- llamar al
- Cheques
- cleveland
- Soluciones
- servicios en la nube
- código
- Comunicación
- compañía
- conexión
- continue
- Creamos
- Referencias
- datos
- integración de datos
- Ciencia de los datos
- almacenamiento de datos
- almacenes de datos
- Base de datos
- bases de datos
- deep learning
- desarrollar
- desarrolladores
- Desarrollo
- Dispositivos
- Dimensiones
- documentos
- conductor
- eficiencia
- etc.
- ejecución
- ejecutivos.
- Experiencias
- Extracción
- Extractos
- Fracaso
- Nombre
- de tus señales
- formato
- fresco
- ser completados
- guía
- Materiales
- esta página
- Alta
- historia
- Cómo
- Como Hacer
- HTTPS
- Identifique
- Impacto
- importador
- Incluye
- información
- integración
- Intelligence
- cuestiones
- IT
- Clave
- large
- APRENDE:
- aprendizaje
- línea
- Etiqueta LinkedIn
- Lista
- carga
- máquina de aprendizaje
- Management
- mapa
- Mercado
- Medios
- Más popular
- movimiento
- nombres
- .
- números
- Ofertas
- funcionamiento
- sistemas operativos
- Operaciones
- oráculo
- solicite
- para las fiestas.
- Otro
- socios
- Contraseña
- actuación
- plataforma
- Plataformas
- Popular
- producido
- Producción
- Programación
- Programas
- proyecto
- tracción
- Python
- Crudo
- en tiempo real
- Realidad
- archivos
- recuperación
- Relaciones
- Informes
- Recursos
- respuesta
- ingresos
- Ciencia:
- Servicios
- set
- En Corto
- sencillos
- So
- Software
- Desarrollo de software ad-hoc
- Soluciones
- SQL
- Etapa
- Estado
- tienda
- tiendas
- te
- Todas las funciones a su disposición
- Target
- tecnología
- técnicas
- Tecnología
- La Fuente
- equipo
- tutoriales
- Actualizar
- propuesta de
- vendedores
- volumen
- Manejo de
- Servicios de Almacenaje
- una vez por semana
- Actividades:
- funciona