Integrare tecnologie differenti senza farti venire il mal di testa. Python e SQL Server.

Qualche tempo fa ho iniziato un processo di unificazione delle basi dati di un cliente per facilitarne le operazioni di analisi e processing dei dati. E questo mi ha fatto pensare a quanto sia importante oggi saper integrare tecnologie differenti, senza farti venire il mal di testa!

Forse ti chiederai: “ma a me cosa importa” ?

Ebbene a volte integrare tecnologie così diverse può essere frustrante, e farti perdere molto tempo. Per questo voglio descriverti un caso d’uso reale nel quale ho dovuto integrare python con un database SQL Server…e tutto è andato alla grande! Senza perdite di tempo e senza problemi di alcun tipo!

Spero che se ti troverai nella stessa necessità potrai ricordarti di questo articolo per non perdere tempo!

Tante, tante, tecnologie

Già da tempo molte aziende hanno rinunciato ad usare una sola tecnologia per implementare tutti i requisiti di business, ma negli ultimi tempi questa frammentazione è diventata davvero evidente!

Spesso mi trovo ad utilizzare tecnologie diverse in base al requisito da soddisfare: python/java (o Spark e la sua integrazione pySpark per python) per eseguire batch, o “near-real-time-data-processing” (vedi l’integrazione con Kafka?) ed analisi dati. Questo perché ha una pletora di librerie scientifiche che per me è superiore ad altri linguaggi!

Ma non saprei nemmeno scegliere una piattaforma diversa da SQL Server o PostgreSql per gestire enormi quantità di dati strutturati (tanto più che SQL Server adesso gestisce anche il paradigma a grafi!). Per non parlare del caso in cui servano database documentali, a grafi, ecc…

E nemmeno scambierei ASP.NET Core per la realizzazione di un sistema di api e backend (forse Flask? Django?).

E poi non parliamo delle piattaforme! Cloud? On-Premise? Hybrid? E i container dove li mettiamo?

Insomma, sono davvero tante le tecnologie a disposizione e quindi ritengo sia importante saper scegliere quella giusta per il caso d’uso specifico senza per forza introdurre troppi “scalini” tecnologici.

Ma torniamo alla pratica… nella quale ho dovuto integrare tecnologie differenti: ovvero python e Microsoft SQL Server.

Ecco come è andata con il cliente

Inizialmente erano state sviluppate delle api in ASP.NET Core basate su SQL Server per servire i dati ad alcune applicazioni angular.

Ed inoltre, dei batch sviluppati in python avevano il compito di analizzare i log, arricchire i dati, e storicizzarli su un database PostgreSql.

Poi è nata la necessità di integrare i dati da PostgreSql in SQL Server, e di conseguenza il nuovo requisito: python doveva integrarsi perfettamente con SQL Server in modo che i batch andassero a scrivere direttamente sul nuovo database.

Non è stato immediato, ma nemmeno una tragedia. La soluzione prevedeva di installare sui server linux il driver ODBC di SQL Server e installare una piccola libreria (pyodbc) che si è andata a sostituire in maniera trasparente a quella usata per comunicare con PostgreSql (psycopg2) e che funziona da bridge con l’ODBC di SQL Server.

Ed ecco i passi:

1) Installazione dei driver ODBC per la tua distribuzione linux

Microsoft ha una pagina ufficiale (https://docs.microsoft.com/en-us/sql/connect/odbc/linux-mac/installing-the-microsoft-odbc-driver-for-sql-server?view=sql-server-ver15) dalla quale seguire le istruzioni di installazione che ti riporto di seguito (in base alla mia distribuzione).

sudo su 
curl https://packages.microsoft.com/keys/microsoft.asc | apt-key add -

curl https://packages.microsoft.com/config/ubuntu/18.04/prod.list > /etc/apt/sources.list.d/mssql-release.list

exit
sudo apt-get update
sudo ACCEPT_EULA=Y apt-get install msodbcsql17
# optional: for bcp and sqlcmd
sudo ACCEPT_EULA=Y apt-get install mssql-tools
echo 'export PATH="$PATH:/opt/mssql-tools/bin"' >> ~/.bash_profile
echo 'export PATH="$PATH:/opt/mssql-tools/bin"' >> ~/.bashrc
source ~/.bashrc
# optional: for unixODBC development headers
sudo apt-get install unixodbc-dev

2) Installazione del pacchetto pyodbc

Io generalmente utilizzo i virtual environment per separare i runtime python per i diversi progetti. Se questo è il tuo caso allora quello che dovrai fare sarà semplicemente attivare l’environment che ti interessa e installare il pacchetto pyodbc:

source venv/bin/activate 
pip install pyodbc

3) Utilizzo del pacchetto nel codice python

Di seguito le parti di codice dove utilizzare pyodbc

#file: settings.py

#############################################
# GLOBAL SETTINGS SECTION
#############################################
class GlobalSettings():
   
    sql_alchemy_connection_string = "mssql+pyodbc://user:pwd@server,1433/dbname?driver=/opt/microsoft/msodbcsql17/lib64/libmsodbcsql-17.4.so.2.1"
    
    pyodbc_driver = "/opt/microsoft/msodbcsql17/lib64/libmsodbcsql-17.4.so.2.1"
    pyodbc_server = "sertver"
    pyodbc_database = "db"
    pyodbc_uid = "user"
    pyodbc_pwd = "pwd"


#------------------------------------------------------------------------------------------------------------------
#file: log_analyzer.py
from settings import NginxSettings, GlobalSettings
import pandas as pd
import os
import shutil
import datetime
import uuid
from nginx_parser import parse_apache_time, get_codice_articolo, get_ricerca_articolo, get_country_by_ip, get_token
from sqlalchemy import create_engine
import pyodbc 


class NginxLogAnalyzer:

    def analyze_log(self, log_folder=None, log_file=None):
        start_time = datetime.datetime.now()
        is_success = True
        message = None
        batch_id = uuid.uuid4()
        if log_folder is None:
            log_folder = NginxSettings.log_folder
        if log_file is None:
            log_file = NginxSettings.log_file

        try:
            result_df = self.generate_base_data_frame(log_file) # generazione dataframe pandas
            
            # altre lavorazioni pandas
            
            engine = create_engine(GlobalSettings.sql_alchemy_connection_string) # creazione engine sqlalchemy con pyodbc
            result_df.to_sql(NginxSettings.db_log_destination_table, engine, if_exists='append') # append dataframe pandas a tabella del db

        except Exception as e:
            message = str(e)
            print(message)
            is_success = False

        finally:
            end_time = datetime.datetime.now()
            print("WRITING BATCH IMPORT LOG")

            sql_cmd = "insert into dbo.application_log(batch_type, batch_id, start_time, end_time, is_success, message) values (?, ?, ?, ?, ?, ?)"
            connection = pyodbc.connect(
                'Driver=' + GlobalSettings.pyodbc_driver + ';'
                'Server=' + GlobalSettings.pyodbc_server  + ';'
                'Database=' + GlobalSettings.pyodbc_database + ';'
                'UID=' + GlobalSettings.pyodbc_uid + ';'
                'PWD=' + GlobalSettings.pyodbc_pwd
            )
            cur = connection.cursor()
            cur.execute(sql_cmd, (NginxSettings.batch_type, str(batch_id), start_time, end_time, is_success, message))
            connection.commit()
            cur.close()
            connection.close()

    @staticmethod
    def generate_base_data_frame(file_path):
        df = pd.read_csv(file_path,
                         sep=r'\s(?=(?:[^"]*"[^"]*")*[^"]*$)(?![^\[]*\])',
                         engine='python',
                         usecols=[0, 3, 4, 5, 6, 7, 8],
                         names=['ip', 'time', 'request', 'status', 'size', 'referer', 'user_agent'],
                         na_values='-',
                         header=None
                         )
        return df

Come puoi notare è semplicissimo: se usi la libreria sqlalchemy basterà istanziare la connessione passando il tipo di driver e, mi raccomando, la posizione dello standard object del driver!

Stesso discorso se vuoi connetterti a basso livello creando la connessione direttamente dall’oggetto pyodbc: dovrai specificare la posizione dello standard object linux.

Ad ognuno il suo

Spero che queste poche righe possano esserti utili nel caso ti trovi nella stessa necessità e possano farti risparmiare tempo prezioso!

Nel mio percorso ho raccolto molte informazioni interessanti in merito agli argomenti di cui ti ho parlato in questo articolo, ed ho scritto un libro “Why Your Data Matter”.

Essendo il frutto della mia passione ed esperienza diretta, ho scelto di mettere questo libro gratuitamente a disposizione di tutti gli IT Manager ed i CIO delle aziende che come te vogliono ottenere grandi risultati dalle loro scelte e dal loro lavoro (evitando di trovarsi in situazioni scomode e da risolvere con urgenza).

Ti invito a leggere le prime pagine scaricandole!
Se poi ti piacerà sarò felice di inviartene una copia GRATUITA direttamente nel tuo ufficio.