Ubah aplikasi Python dan Perl untuk mendukung migrasi database dari Microsoft SQL Server ke Amazon Aurora PostgreSQL Edisi yang kompatibel

Ikuti langkah-langkah konversi kode ini untuk memigrasikan aplikasi Anda ke PostgreSQL.

Epos berikut memberikan instruksi terperinci untuk beberapa tugas konversi ini untuk aplikasi Python dan Perl.

Gunakan daftar periksa untuk setiap langkah migrasi.

Tambahkan yang berikut ini ke daftar periksa Anda untuk setiap langkah migrasi aplikasi, termasuk langkah terakhir:

Analisis basis kode Python Anda yang ada.

Analisis Anda harus mencakup hal-hal berikut untuk memfasilitasi proses migrasi aplikasi:

Konversikan koneksi database Anda untuk mendukung PostgreSQL.

Sebagian besar aplikasi Python menggunakan pustaka pyodbc untuk terhubung dengan database SQL Server sebagai berikut.

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

Mengkonversi koneksi database untuk mendukung PostgreSQL sebagai berikut.

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

Ubah kueri SQL sebaris ke PostgreSQL.

Konversikan kueri SQL sebaris Anda ke format yang kompatibel dengan PostgreSQL. Misalnya, query SQL Server berikut mengambil string dari tabel.

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)

Setelah konversi, kueri SQL sebaris yang kompatibel dengan PostgreSQL terlihat seperti berikut.

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)

Menangani query SQL dinamis.

SQL dinamis dapat hadir dalam satu skrip atau dalam beberapa skrip Python. Contoh sebelumnya menunjukkan bagaimana menggunakan fungsi penggantian string Python untuk menyisipkan variabel untuk membangun query SQL dinamis. Pendekatan alternatif adalah menambahkan string kueri dengan variabel di mana pun berlaku. 

Dalam contoh berikut, string kueri dibangun dengan cepat berdasarkan nilai yang dikembalikan oleh fungsi.

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

Jenis kueri dinamis ini sangat umum selama migrasi aplikasi. Ikuti langkah-langkah berikut untuk menangani kueri dinamis:

Menangani set hasil, variabel, dan frame data.

Untuk Microsoft SQL Server, Anda menggunakan metode Python fetchone() seperti fetchall() atau untuk mengambil hasil yang ditetapkan dari database. Anda juga dapat menggunakan fetchmany(size) dan menentukan jumlah catatan yang akan dikembalikan dari hasil yang ditetapkan. Untuk melakukan ini, Anda dapat menggunakan objek koneksi pyodbc seperti yang ditunjukkan pada contoh berikut.

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

Di Aurora, untuk melakukan tugas serupa seperti menghubungkan ke PostgreSQL dan mengambil set hasil, Anda dapat menggunakan psycopg2 atau. SQLAlchemy Pustaka Python ini menyediakan modul koneksi dan objek kursor untuk melintasi catatan database PostgreSQL, seperti yang ditunjukkan pada contoh berikut.

psycopg2 (Kompatibel dengan 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 (Kompatibel dengan 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)

Uji aplikasi Anda selama dan setelah migrasi.

Menguji aplikasi Python yang dimigrasi adalah proses yang berkelanjutan. Karena migrasi mencakup perubahan objek koneksi (psycopg2 atau SQLAlchemy), penanganan kesalahan, fitur baru (bingkai data), perubahan SQL sebaris, fungsi salinan massal (bcpbukanCOPY) dan perubahan serupa, migrasi harus diuji dengan cermat selama dan setelah migrasi aplikasi. Periksa:

Analisis basis kode Perl Anda yang ada.

Analisis Anda harus mencakup hal-hal berikut untuk memfasilitasi proses migrasi aplikasi. Anda harus mengidentifikasi:

Mengkonversi koneksi dari aplikasi Perl dan modul DBI untuk mendukung PostgreSQL.

Aplikasi berbasis Perl umumnya menggunakan modul Perl DBI, yang merupakan modul akses database standar untuk bahasa pemrograman Perl. Anda dapat menggunakan modul DBI yang sama dengan driver yang berbeda untuk SQL Server dan PostgreSQL.

Untuk informasi selengkapnya tentang modul Perl yang diperlukan, instalasi, dan instruksi lainnya, lihat dokumentasi DBD: :Pg. Contoh berikut terhubung ke Aurora PostgreSQL kompatibel di. 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}
      );

Ubah kueri SQL Inline ke PostgreSQL.

Aplikasi Anda mungkin memiliki kueri SQL sebaris denganSELECT,, DELETEUPDATE, dan pernyataan serupa yang menyertakan klausa kueri yang tidak didukung PostgreSQL. Misalnya, kata kunci kueri seperti TOP dan NOLOCK tidak didukung di PostgreSQL. Contoh berikut menunjukkan bagaimana Anda dapat menanganiTOP,NOLOCK, dan variabel Boolean.

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

Untuk PostgreSQL, konversi ke:

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

Menangani query SQL dinamis dan variabel Perl.

Query SQL dinamis adalah pernyataan SQL yang dibangun pada runtime aplikasi. Kueri ini dibangun secara dinamis saat aplikasi berjalan, tergantung pada kondisi tertentu, sehingga teks lengkap kueri tidak diketahui sampai runtime. Contohnya adalah aplikasi analitik keuangan yang menganalisis 10 saham teratas setiap hari, dan saham ini berubah setiap hari. Tabel SQL dibuat berdasarkan performa terbaik, dan nilainya tidak diketahui sampai runtime.

Katakanlah kueri SQL sebaris untuk contoh ini diteruskan ke fungsi pembungkus untuk mendapatkan hasil yang ditetapkan dalam variabel, dan kemudian variabel menggunakan kondisi untuk menentukan apakah tabel itu ada:

Berikut adalah contoh penanganan variabel, diikuti oleh SQL Server dan PostgreSQL query untuk kasus penggunaan ini.

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
}

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

Contoh berikut menggunakan variabel Perl dalam SQL inline, yang menjalankan SELECT pernyataan dengan JOIN untuk mengambil kunci utama dari tabel dan posisi kolom kunci.

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

Mengkonversi konstruksi SQL Server tambahan ke PostgreSQL.

Perubahan berikut berlaku untuk semua aplikasi, terlepas dari bahasa pemrograman.

Manfaatkan layanan AWS untuk melakukan peningkatan kinerja.

Saat bermigrasi ke AWS Cloud, Anda dapat menyempurnakan desain aplikasi dan database Anda untuk memanfaatkan layanan AWS. Misalnya, jika kueri dari aplikasi Python Anda, yang terhubung ke server database yang kompatibel dengan Aurora PostgreSQL, membutuhkan waktu lebih lama daripada kueri Microsoft SQL Server asli Anda, Anda dapat mempertimbangkan untuk membuat umpan data historis langsung ke bucket Amazon Simple Storage Service (Amazon S3) dari server Aurora, dan menggunakan bucket Amazon Athena-based Kueri SQL untuk menghasilkan laporan dan kueri data analitik untuk dasbor pengguna Anda.