Dentro de este proyecto, afrontamos el desafío de gestionar 37.000 nombres de empresas provenientes de dos orígenes distintos. La complejidad radica en la posible discrepancia entre cómo cotizan empresas idénticas en estas fuentes.
El objetivo de este artículo es enseñarle a ejecutar Python de forma nativa dentro del servidor Microsoft SQL. Utilizar complementos y bibliotecas externas, así como realizar un procesamiento posterior de las tablas resultantes con SQL.
Esta es la estrategia que seguiré al construir los algoritmos:
- Bloqueo — Dividir conjuntos de datos en bloques o grupos más pequeños según atributos comunes para reducir la complejidad computacional al comparar registros. Reduce el espacio de búsqueda y mejora la eficiencia en las tareas de búsqueda de similitudes.
- Preprocesamiento — Limpiar y estandarizar los datos sin procesar para prepararlos para el análisis mediante tareas como conversión de minúsculas, eliminación de puntuación y eliminación de palabras vacías. Este paso mejora la calidad de los datos y reduce el ruido.
- Aplicación del modelo de búsqueda por similitud — Aplicar modelos para calcular la similitud o distancia entre pares de registros basados en representaciones tokenizadas. Esto ayuda a identificar pares similares, utilizando métricas como similitud de coseno o distancia de edición, para tareas como vinculación de registros o deduplicación.
Bloqueo
Mis conjuntos de datos son muy desproporcionados: tengo 1.361.373 entidades en una tabla y solo 37.171 nombres de empresas en la segunda tabla. Si intento hacer coincidir la tabla sin procesar, el algoritmo tardaría mucho en hacerlo.
Para bloquear las tablas, necesitamos ver qué características comunes hay entre 2 conjuntos de datos. En mi caso, las empresas están todas asociadas a proyectos internos. Por lo tanto haré lo siguiente:
- Extraiga el nombre distintivo de la empresa y el código del proyecto de la tabla más pequeña.
- Recorra los códigos del proyecto e intente encontrarlos en la tabla más grande.
- Asigne todos los fondos para ese proyecto y sáquelos de la mesa grande.
- ¡Repita para el próximo proyecto!
De esta manera, reduciré el gran conjunto de datos con cada iteración y, al mismo tiempo, me aseguraré de que el mapeo sea rápido debido a un conjunto de datos más pequeño y filtrado a nivel de proyecto.
Ahora, filtraré ambas tablas por el código del proyecto, así:
Con este enfoque, nuestra tabla pequeña solo tiene 406 filas para el proyecto 'ABC' que podemos mapear, mientras que la tabla grande tiene 15,973 filas para que las mapeemos. Esta es una gran reducción de la tabla cruda.
Estructura del programa
Este proyecto constará de funciones Python y SQL en el servidor SQL; Aquí hay un breve resumen de cómo funcionará el programa para tener una comprensión más clara de cada paso:
Ejecución del programa:
- Imprimir el código del proyecto en un bucle es la versión más simple de esta función:
Rápidamente se hace evidente que el cursor SQL consume demasiados recursos. En resumen, esto sucede porque los cursores operan a nivel de fila y recorren cada fila para realizar una operación.
Puede encontrar más información sobre por qué los cursores en SQL son ineficientes y es mejor evitarlos aquí: https://stackoverflow.com/questions/4568464/sql-server-temporary-tables-vs-cursors (respuesta 2)
Para aumentar el rendimiento, usaré tablas temporales y eliminaré el cursor. Aquí está la función resultante:
Ahora se necesitan aproximadamente 3 segundos por proyecto para seleccionar el código del proyecto y los datos de la tabla de mapeo grande, filtrados por ese proyecto.
Para fines de demostración, solo me centraré en 2 proyectos; sin embargo, volveré a ejecutar la función en todos los proyectos cuando lo haga en producción.
La función final con la que trabajaremos se ve así:
Preparación de la mesa de mapeo
El siguiente paso es preparar los datos para las funciones de preprocesamiento y mapeo de Python, para esto necesitaremos 2 conjuntos de datos:
- Los datos filtrados por código de proyecto de la gran tabla de mapeo
- Los datos filtrados por código de proyecto de la tabla de pequeñas empresas.
Así es como se ve la función actualizada con los datos de 2 tablas seleccionadas:
Importante: las funciones pitónicas en SQL solo aceptan 1 entrada de tabla. Asegúrate de poner tus datos en un mesa ancha individual antes de introducirlo en una función de Python en SQL.
Como resultado de esta función obtenemos los proyectos, los nombres de las empresas y las fuentes de cada proyecto.
¡Ahora estamos listos para Python!
Python en SQL Server, a través de sp_execute_external_scriptle permite ejecutar código Python directamente dentro de SQL Server.
Permite la integración de las capacidades de Python en los flujos de trabajo de SQL con el intercambio de datos entre SQL y Python. En el ejemplo proporcionado, se ejecuta un script de Python, creando un DataFrame de pandas a partir de los datos de entrada.
El resultado se devuelve como una salida única.
¡Cuan genial es eso!
Hay algunas cosas importantes a tener en cuenta acerca de la ejecución de Python en SQL:
- Las cadenas se definen entre comillas dobles (“), no entre comillas simples ('). Asegúrese de verificar esto, especialmente si está utilizando expresiones regulares, para evitar perder tiempo en el seguimiento de errores.
- Solo se permite 1 salida, por lo que su código Python dará como resultado 1 tabla en la salida
- Puede utilizar declaraciones impresas para depurar y ver los resultados impresos en la pestaña 'Mensajes' dentro de su servidor SQL. Al igual que:
Bibliotecas Python en SQL
En SQL Server, varias bibliotecas vienen preinstaladas y son fácilmente accesibles. Para ver la lista completa de estas bibliotecas, puede ejecutar el siguiente comando:
Así es como se verá el resultado:
Volviendo a nuestra tabla generada, ahora podemos hacer coincidir los nombres de empresas de diferentes fuentes usando Python. Nuestro procedimiento Python tomará la tabla larga y generará una tabla con las entidades asignadas. Debería mostrar la coincidencia que cree que es más probable en la tabla de mapeo grande al lado de cada registro de la tabla de la pequeña empresa.
Para hacer esto, primero agreguemos una función de Python a nuestro procedimiento SQL. El primer paso es simplemente introducir el conjunto de datos en Python. Lo haré con un conjunto de datos de muestra y luego con nuestros datos. Aquí está el código:
Este sistema nos permite introducir nuestras dos tablas en la función pitónica como entradas y luego imprime ambas tablas como salidas.
Preprocesamiento en Python
Para hacer coincidir nuestras cadenas de manera efectiva, debemos realizar algún preprocesamiento en Python, esto incluye:
- Eliminar acentos y otros caracteres especiales específicos del idioma
- Quitar los espacios en blanco
- Quitar puntuación
El primer paso se realizará con la intercalación en SQL, mientras que los otros 2 estarán presentes en el paso de preprocesamiento de la función de Python.
Así es como se ve nuestra función con preprocesamiento:
El resultado de esto son 3 columnas, una con el nombre de la empresa en minúscula, minúscula y sin espacios, la segunda columna es la columna del proyecto y la tercera columna es la fuente.
Coincidencia de cadenas en Python
Aquí tenemos que ser creativos ya que estamos bastante limitados en la cantidad de bibliotecas que podemos usar. Por lo tanto, primero identifiquemos cómo queremos que se vea nuestra salida.
Queremos hacer coincidir los datos provenientes de la fuente 2 con los datos de la fuente 1. Por lo tanto, para cada valor en la fuente 2, debemos tener un conjunto de valores coincidentes de la fuente 1 con puntuaciones para representar la cercanía de la coincidencia.
Usaremos bibliotecas integradas de Python En primer lugar, evitar la necesidad de importar bibliotecas y, por tanto, simplificar el trabajo.
La lógica:
- Recorre cada proyecto
- Haga una tabla con los fondos por fuente, donde la fuente 1 es la tabla grande con los datos del mapeo y 2 es el conjunto de datos inicial de la empresa.
- Seleccione los datos del pequeño conjunto de datos en una matriz
- Compare cada elemento de la matriz resultante con cada elemento del marco de datos de mapeo grande
- Devolver las puntuaciones de cada entidad.
El código:
Y aquí está el resultado final:
En esta tabla, tenemos el nombre de cada empresa, el proyecto al que pertenece y la fuente, ya sea de la tabla de mapeo grande o de la tabla de empresas pequeñas. La puntuación de la derecha indica la métrica de similitud entre el nombre de la empresa de la fuente 2 y la fuente 1. Es importante tener en cuenta que la empresa 4, que proviene de la fuente 2, siempre tendrá una puntuación de coincidencia del 1 al 100 %, como se está emparejado contra sí mismo.
La ejecución de scripts de Python dentro de SQL Server a través de Machine Learning Services es una característica poderosa que permite análisis en la base de datos y tareas de aprendizaje automático. Esta integración permite el acceso directo a los datos sin necesidad de movimiento de datos, lo que optimiza significativamente el rendimiento y la seguridad para operaciones con uso intensivo de datos.
Sin embargo, existen limitaciones que hay que tener en cuenta. El medio ambiente sustenta una entrada única, lo que podría restringir la complejidad de las tareas que se pueden realizar directamente dentro del contexto SQL. Además, sólo un Hay un conjunto limitado de bibliotecas de Python disponibles., lo que puede requerir soluciones alternativas para ciertos tipos de análisis de datos o tareas de aprendizaje automático que no son compatibles con las bibliotecas predeterminadas. Además, los usuarios deben navegar por las complejidades del entorno de SQL Server, como el espaciado complejo en las consultas T-SQL que incluyen código Python, lo que puede ser una fuente de errores y confusión.
A pesar de estos desafíos, existen numerosas aplicaciones en las que ejecutar Python en SQL Server resulta ventajoso:
1. Limpieza y transformación de datos — Python se puede utilizar directamente en SQL Server para realizar tareas complejas de preprocesamiento de datos, como manejar datos faltantes o normalizar valores, antes de realizar más análisis o informes.
2. Análisis predictivo — La implementación de modelos de aprendizaje automático de Python directamente dentro de SQL Server permite realizar predicciones en tiempo real, como la pérdida de clientes o la previsión de ventas, utilizando datos de bases de datos en vivo.
3. Análisis avanzado — Las capacidades de Python se pueden aprovechar para realizar análisis estadísticos sofisticados y extracción de datos directamente en la base de datos, ayudando en los procesos de toma de decisiones sin la latencia de la transferencia de datos.
4. Informes y visualización automatizados — Los scripts de Python pueden generar visualizaciones de datos e informes directamente a partir de datos de SQL Server, lo que permite actualizaciones y paneles de control automatizados.
5. Operacionalización de modelos de aprendizaje automático — Al integrar Python en SQL Server, los modelos se pueden actualizar y administrar directamente dentro del entorno de la base de datos, simplificando el flujo de trabajo operativo.
En conclusión, si bien la ejecución de Python en SQL Server presenta algunos desafíos, también abre una gran cantidad de posibilidades para mejorar y simplificar el procesamiento de datos, el análisis y el modelado predictivo directamente dentro del entorno de la base de datos.
PD: para ver más de mis artículos, puedes seguirme en LinkedIn aquí: https://www.linkedin.com/in/sasha-korovkina-5b992019b/
