Modifier les applications Python et Perl pour prendre en charge la migration de bases de données de Microsoft SQL Server vers Amazon Aurora PostgreSQL Compatible Edition

Suivez ces étapes de conversion de code pour migrer votre application vers PostgreSQL.

Les epics suivants fournissent des instructions détaillées pour certaines de ces tâches de conversion pour les applications Python et Perl.

Utilisez une liste de contrôle pour chaque étape de la migration.

Ajoutez les éléments suivants à votre liste de contrôle pour chaque étape de la migration des applications, y compris la dernière étape :

Analysez votre base de code Python existante.

Votre analyse doit inclure les éléments suivants pour faciliter le processus de migration des applications :

Convertissez vos connexions de base de données pour qu'elles soient compatibles avec PostgreSQL.

La plupart des applications Python utilisent la bibliothèque pyodbc pour se connecter aux bases de données SQL Server comme suit.

import pyodbc
....
try:
    conn_string = "Driver=ODBC Driver 17 for SQL
    Server;UID={};PWD={};Server={};Database={}".format (conn_user, conn_password,
    conn_server, conn_database)
    conn = pyodbc.connect(conn_string)
    cur = conn.cursor()
    result = cur.execute(query_string)
    for row in result:
    print (row)
except Exception as e:
    print(str(e))

Convertissez la connexion à la base de données pour qu'elle prenne en charge PostgreSQL comme suit.

import pyodbc
import psycopg2
....
try:
    conn_string = ‘postgresql+psycopg2://’+
    conn_user+’:’+conn_password+’@’+conn_server+’/’+conn_database
    conn = pyodbc.connect(conn_string, connect_args={‘options’:’-csearch_path=dbo’})
    cur = conn.cursor()
    result = cur.execute(query_string)
    for row in result:
    print (row)
except Exception as e:
    print(str(e))

Remplacez les requêtes SQL en ligne par PostgreSQL.

Convertissez vos requêtes SQL en ligne dans un format compatible avec PostgreSQL. Par exemple, la requête SQL Server suivante extrait une chaîne d'une table.

dtype = “type1”
stm = ‘“SELECT TOP 1 searchcode FROM TypesTable (NOLOCK)
WHERE code=”’ + “’” + str(dtype) + “’”
# For Microsoft SQL Server Database Connection
engine = create_engine(‘mssql+pyodbc:///?odbc_connect=%s’ % urllib.parse.quote_plus(conn_string), connect_args={‘connect_timeout’:login_timeout})
conn = engine_connect()
rs = conn.execute(stm)
for row in rs:
    print(row)

Après la conversion, la requête SQL en ligne compatible avec PostgreSQL se présente comme suit.

dtype = “type1”
stm = ‘“SELECT searchcode FROM TypesTable
WHERE code=”’ + “’” + str(dtype) + “’ LIMIT 1”
# For PostgreSQL Database Connection
engine = create_engine(‘postgres+psycopg2://%s’ %conn_string, connect_args={‘connect_timeout’:login_timeout})
conn = engine.connect()
rs = conn.execute(stm)
for row in rs:
    print(row)

Gérez les requêtes SQL dynamiques.

Le SQL dynamique peut être présent dans un ou plusieurs scripts Python. Les exemples précédents montraient comment utiliser la fonction de remplacement de chaîne de Python pour insérer des variables afin de créer des requêtes SQL dynamiques. Une autre approche consiste à ajouter des variables à la chaîne de requête, le cas échéant. 

Dans l'exemple suivant, la chaîne de requête est construite à la volée en fonction des valeurs renvoyées par une fonction.

query = ‘“SELECT id from equity e join issues i on e.permId=i.permId where e.id’”
query += get_id_filter(ids) + “ e.id is NOT NULL

Ces types de requêtes dynamiques sont très courants lors de la migration d'applications. Pour gérer les requêtes dynamiques, procédez comme suit :

Gérez les ensembles de résultats, les variables et les blocs de données.

Pour Microsoft SQL Server, vous utilisez des méthodes Python telles que fetchone() ou fetchall() pour récupérer le jeu de résultats de la base de données. Vous pouvez également utiliser fetchmany(size) et spécifier le nombre d'enregistrements à renvoyer à partir de l'ensemble de résultats. Pour ce faire, vous pouvez utiliser l'objet de connexion pyodbc comme indiqué dans l'exemple suivant.

pyodbc (Microsoft SQL Server)

import pyodbc 
server = 'tcp:myserver.database.windows.net' 
database = 'exampledb' 
username = 'exampleusername' 
password = 'examplepassword' 
conn = pyodbc.connect('DRIVER={ODBC Driver 17 for SQL Server};SERVER='+server+';DATABASE='+database+';UID='+username+';PWD='+ password)
cursor = conn.cursor()
cursor.execute("SELECT * FROM ITEMS") 
row = cursor.fetchone() 
while row: 
    print(row[0])
    row = cursor.fetchone()

Dans Aurora, pour effectuer des tâches similaires telles que la connexion à PostgreSQL et l'extraction de jeux de résultats, vous pouvez utiliser psycopg2 ou. SQLAlchemy Ces bibliothèques Python fournissent le module de connexion et l'objet curseur permettant de parcourir les enregistrements de la base de données PostgreSQL, comme illustré dans l'exemple suivant.

psycopg2 (compatible avec Aurora PostgreSQL)

import psycopg2
query = "SELECT * FROM ITEMS;"
//Initialize variables
host=dbname=user=password=port=sslmode=connect_timeout=""
connstring = "host='{host}' dbname='{dbname}' user='{user}' \
password='{password}'port='{port}'".format(host=host,dbname=dbname,\
user=user,password=password,port=port) 
conn = psycopg2.connect(connstring)
cursor = conn.cursor()
cursor.execute(query)
column_names = [column[0] for column in cursor.description]
print("Column Names: ", column_names)
print("Column values: "
for row in cursor:
    print("itemid :", row[0])
    print("itemdescrption :", row[1])
    print("itemprice :", row[3]))

SQLAlchemy (Compatible avec Aurora PostgreSQL)

from sqlalchemy import create_engine
from pandas import DataFrame
conn_string = 'postgresql://core:database@localhost:5432/exampledatabase'
engine = create_engine(conn_string)
conn = engine.connect()
dataid = 1001
result = conn.execute("SELECT * FROM ITEMS")
df = DataFrame(result.fetchall())
df.columns = result.keys()
df = pd.DataFrame()
engine.connect()
df = pd.read_sql_query(sql_query, engine, coerce_float=False)
print(“df=”, df)

Testez votre application pendant et après la migration.

Le test de l'application Python migrée est un processus continu. Étant donné que la migration inclut des modifications d'objets de connexion (psycopg2 ou SQLAlchemy), la gestion des erreurs, de nouvelles fonctionnalités (blocs de données), des modifications du code SQL intégré, des fonctionnalités de copie en bloc (bcpau lieu deCOPY) et des modifications similaires, elle doit être testée avec soin pendant et après la migration de l'application. Vérifiez :

Analysez votre base de code Perl existante.

Votre analyse doit inclure les éléments suivants pour faciliter le processus de migration des applications. Vous devez identifier :

Convertissez les connexions de l'application Perl et du module DBI pour qu'elles soient compatibles avec PostgreSQL.

Les applications basées sur Perl utilisent généralement le module Perl DBI, qui est un module d'accès aux bases de données standard pour le langage de programmation Perl. Vous pouvez utiliser le même module DBI avec différents pilotes pour SQL Server et PostgreSQL.

Pour plus d'informations sur les modules Perl requis, les installations et les autres instructions, consultez la documentation DBD : :Pg. L'exemple suivant se connecte à Aurora PostgreSQL compatible à l'adresse. exampletest-aurorapg-database.cluster-sampleclusture.us-east.-rds.amazonaws.com

#!/usr/bin/perl
use DBI;
use strict;
my $driver = "Pg";
my $hostname = “exampletest-aurorapg-database-sampleclusture.us-east.rds.amazonaws.com”
my $dsn = "DBI:$driver: dbname = $hostname;host = 127.0.0.1;port = 5432";
my $username = "postgres";
my $password = "pass123";
$dbh = DBI->connect("dbi:Pg:dbname=$hostname;host=$host;port=$port;options=$options",
      $username,
      $password,
      {AutoCommit => 0, RaiseError => 1, PrintError => 0}
      );

Remplacez les requêtes SQL en ligne par PostgreSQL.

Votre application peut comporter des requêtes SQL en ligne avecSELECT, DELETEUPDATE, et des instructions similaires qui incluent des clauses de requête non prises en charge par PostgreSQL. Par exemple, les mots clés de requête tels que TOP et NOLOCK ne sont pas pris en charge dans PostgreSQL. Les exemples suivants montrent comment vous pouvez gérer les variables TOP booléennes et NOLOCK les variables booléennes.

Dans SQL Server :

$sqlStr = $sqlStr
. "WHERE a.student_id in (SELECT TOP $numofRecords c_student_id   \
FROM active_student_record b WITH (NOLOCK) \
INNER JOIN student_contributor c WITH (NOLOCK) on c.contributor_id = b.c_st)

Pour PostgreSQL, convertissez en :

$sqlStr = $sqlStr
. "WHERE a.student_id in (SELECT TOP $numofRecords c_student_id  \
FROM active_student_record b INNER JOIN student_contributor c  \
on c.contributor_id = b.c_student_contr_id WHERE b_current_1 is true \
LIMIT $numofRecords)"

Gérez les requêtes SQL dynamiques et les variables Perl.

Les requêtes SQL dynamiques sont des instructions SQL créées lors de l'exécution de l'application. Ces requêtes sont construites dynamiquement lorsque l'application est en cours d'exécution, en fonction de certaines conditions, de sorte que le texte complet de la requête n'est pas connu avant l'exécution. Par exemple, une application d'analyse financière qui analyse les 10 meilleures actions sur une base quotidienne, et ces actions changent tous les jours. Les tables SQL sont créées en fonction des meilleures performances, et les valeurs ne sont connues qu'au moment de l'exécution.

Supposons que les requêtes SQL en ligne de cet exemple soient transmises à une fonction wrapper pour obtenir les résultats définis dans une variable, puis qu'une variable utilise une condition pour déterminer si la table existe :

Voici un exemple de gestion des variables, suivi des requêtes SQL Server et PostgreSQL pour ce cas d'utilisation.

my $tableexists = db_read( arg 1, $sql_qry, undef, 'writer');
my $table_already_exists = $tableexists->[0]{table_exists};
if ($table_already_exists){
# do some thing
}
else {
# do something else
}

Serveur SQL :

my $sql_qry = “SELECT OBJECT_ID('$backendTable', 'U') table_exists", undef, 'writer')";

PostgreSQL :

my $sql_qry = “SELECT TO_REGCLASS('$backendTable', 'U') table_exists", undef, 'writer')";

L'exemple suivant utilise une variable Perl dans le SQL en ligne, qui exécute une SELECT instruction avec un JOIN pour récupérer la clé primaire de la table et la position de la colonne clé.

Serveur SQL :

my $sql_qry = "SELECT column_name', character_maxi mum_length \
FROM INFORMATION_SCHEMA.COLUMNS \
WHERE TABLE_SCHEMA='$example_schemaInfo' \
AND TABLE_NAME='$example_table' \
AND DATA_TYPE IN ('varchar','nvarchar');";

PostgreSQL :

my $sql_qry = "SELECT c1.column_name, c1.ordinal_position \
FROM information_schema.key_column_usage AS c LEFT \
JOIN information_schema.table_constraints AS t1 \
ON t1.constraint_name = c1.constraint_name \
WHERE t1.table_name = $example_schemaInfo'.'$example_table’ \
AND t1.constraint_type = 'PRIMARY KEY' ;";

Convertissez des constructions SQL Server supplémentaires en PostgreSQL.

Les modifications suivantes s'appliquent à toutes les applications, quel que soit le langage de programmation.

Tirez parti des services AWS pour améliorer les performances.

Lorsque vous migrez vers le cloud AWS, vous pouvez affiner la conception de votre application et de votre base de données afin de tirer parti des services AWS. Par exemple, si les requêtes de votre application Python, connectée à un serveur de base de données compatible Aurora PostgreSQL, prennent plus de temps que vos requêtes Microsoft SQL Server d'origine, vous pouvez envisager de créer un flux de données historiques directement vers un bucket Amazon Simple Storage Service (Amazon S3) depuis le serveur Aurora, et d'utiliser des requêtes SQL basées sur Amazon Athena pour générer des rapports et des requêtes de données analytiques pour votre tableaux de bord utilisateur.