Altere os aplicativos Python e Perl para oferecer suporte à migração do banco de dados do Microsoft SQL Server para o Amazon Aurora Edição Compatível com PostgreSQL

Siga estas etapas de conversão de código para migrar seu aplicativo para o PostgreSQL.

Os épicos a seguir fornecem instruções detalhadas para algumas dessas tarefas de conversão para aplicativos Python e Perl.

Use uma lista de verificação para cada etapa da migração.

Adicione o seguinte à sua lista de verificação para cada etapa da migração do aplicativo, incluindo a etapa final:

Analise sua base de código Python existente.

Sua análise deve incluir o seguinte para facilitar o processo de migração do aplicativo:

Converta suas conexões de banco de dados para suportar o PostgreSQL.

A maioria dos aplicativos Python usa a biblioteca pyodbc para se conectar aos bancos de dados do SQL Server da seguinte maneira.

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))

Converta a conexão do banco de dados para suportar o PostgreSQL da seguinte maneira.

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))

Altere as consultas SQL em linha para PostgreSQL.

Converta suas consultas SQL em linha em um formato compatível com PostgreSQL. Por exemplo, a consulta do SQL Server a seguir recupera uma string de uma tabela.

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)

Após a conversão, a consulta SQL em linha compatível com PostgreSQL tem a seguinte aparência.

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)

Gerencie consultas SQL dinâmicas.

O SQL dinâmico pode estar presente em um script ou em vários scripts do Python. Exemplos anteriores mostraram como usar a função string replace (substituição de strings) do Python para inserir variáveis para estruturar consultas SQL dinâmicas. Uma abordagem alternativa é anexar a string de consulta com variáveis sempre que aplicável. 

No exemplo a seguir, a string de consulta é estruturada dinamicamente com base nos valores retornados por uma função.

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

Esses tipos de consultas dinâmicas são muito comuns durante a migração do aplicativo. Siga estas etapas para lidar com consultas dinâmicas:

Gerencie conjuntos de resultados, variáveis e data frames.

No Microsoft SQL Server, você usa métodos Python, como fetchone() ou fetchall() para recuperar o conjunto de resultados do banco de dados. Você também pode usar fetchmany(size) e especificar o número de registros a serem retornados do conjunto de resultados. Para fazer isso, você pode usar o objeto de conexão pyodbc, conforme mostrado no exemplo a seguir.

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()

No Aurora, para realizar tarefas semelhantes, como conectar-se ao PostgreSQL e buscar conjuntos de resultados, você pode usar psycopg2 ou. SQLAlchemy Essas bibliotecas do Python fornecem o módulo de conexão e o objeto cursor para percorrer os registros do banco de dados do PostgreSQL, conforme mostrado no exemplo a seguir.

psycopg2 (Aurora compatível com 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 (Compatível com 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)

Teste seu aplicativo durante e após a migração.

Testar o aplicativo do Python migrado é um processo contínuo. Como a migração inclui alterações no objeto de conexão (psycopg2 ou SQLAlchemy), tratamento de erros, novos recursos (quadros de dados), alterações de SQL em linha, funcionalidades de cópia em massa (bcpem vez deCOPY) e alterações semelhantes, ela deve ser testada cuidadosamente durante e após a migração do aplicativo. Verifique se existe:

Analise sua base de código Perl existente.

Sua análise deve incluir o seguinte para facilitar o processo de migração do aplicativo. Você deve identificar:

Converta as conexões do aplicativo do Perl e do módulo do DBI para suportar o PostgreSQL.

Os aplicativos baseados em Perl geralmente usam o módulo do Perl DBI, que é um módulo padrão de acesso ao banco de dados para a linguagem de programação Perl. Você pode usar o mesmo módulo do DBI com drivers diferentes para SQL Server e PostgreSQL.

Para obter mais informações sobre os módulos do Perl, instalações e outras instruções necessários, consulte a documentação do DBD::Pg. O exemplo a seguir se conecta ao Aurora compatível com PostgreSQL em 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}
      );

Altere as consultas SQL em linha para PostgreSQL.

Seu aplicativo pode ter consultas SQL em linha com SELECT, DELETE, UPDATE e declarações semelhantes que incluam cláusulas de consulta que o PostgreSQL não suporta. Por exemplo, palavras-chave de consulta como TOP e NOLOCK não são suportadas no PostgreSQL. Os exemplos a seguir mostram como você pode gerenciar variáveis TOP, NOLOCK e booleanas.

No 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)

Para o PostgreSQL, converta para:

$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)"

Gerencie consultas SQL dinâmicas e variáveis do Perl.

As consultas SQL dinâmicas são instruções SQL criadas no runtime do aplicativo. Essas consultas são estruturadas dinamicamente quando o aplicativo está em execução, dependendo de determinadas condições, portanto, o texto completo da consulta não é conhecido até o runtime. Um exemplo é um aplicativo de análise financeira que analisa as 10 principais ações diariamente, e essas ações mudam todos os dias. As tabelas SQL são criadas com base nos melhores desempenhos e os valores não são conhecidos até o runtime.

Digamos que as consultas SQL em linha deste exemplo sejam passadas para uma função de encapsulamento para obter o conjunto de resultados em uma variável e, em seguida, uma variável use uma condição para determinar se a tabela existe:

Este é um exemplo de manipulação de variáveis, seguido pelas consultas SQL Server e PostgreSQL para este caso de uso.

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
}

SQL Server:

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')";

O exemplo a seguir usa uma variável do Perl em SQL em linha, que executa uma instrução SELECT com uma JOIN para buscar a chave primária da tabela e a posição da coluna chave.

SQL Server:

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' ;";

Converta estruturas adicionais do SQL Server em PostgreSQL.

As alterações a seguir se aplicam a todos os aplicativos, independentemente da linguagem de programação.

Aproveite os serviços da AWS para aprimorar o desempenho.

Ao migrar para a nuvem AWS, você pode refinar o design do aplicativo e do banco de dados para aproveitar os serviços da AWS. Por exemplo, se as consultas do seu aplicativo do Python, que está conectado a um servidor de banco de dados compatível com o Aurora PostgreSQL, estiverem demorando mais do que as consultas originais do Microsoft SQL Server, considere criar um feed de dados históricos diretamente para um bucket do Amazon Simple Storage Service (Amazon S3) a partir do servidor Aurora e usar consultas SQL baseadas no Amazon Athena para gere relatórios e consultas de dados analíticos para seus painéis de usuário.