Microsoft SQL Server에서 Amazon Aurora PostgreSQL-Compatible Edition으로 데이터베이스 마이그레이션을 지원하도록 Python 및 Perl 애플리케이션 변경

다음 코드 변환 단계에 따라 애플리케이션을 PostgreSQL로 마이그레이션합니다.

다음 에픽은 Python 및 Perl 애플리케이션의 이러한 변환 작업 중 일부에 대한 자세한 지침을 제공합니다.

마이그레이션 각 단계마다 체크리스트를 사용합니다.

최종 단계를 포함하여 애플리케이션 마이그레이션의 각 단계에 대한 체크리스트에 다음을 추가합니다.

기존 Python 코드 베이스를 분석합니다.

분석에는 애플리케이션 마이그레이션 프로세스를 용이하게 하는 다음 내용이 포함되어야 합니다.

PostgreSQL을 지원하도록 데이터베이스 연결을 변환합니다.

대부분의 Python 애플리케이션은 다음과 같이 pyodbc 라이브러리를 사용하여 SQL Server 데이터베이스에 연결합니다.

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

다음과 같이 PostgreSQL을 지원하도록 데이터베이스 연결을 변환합니다.

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

인라인 SQL 쿼리를 PostgreSQL로 변경합니다.

인라인 SQL 쿼리를 PostgreSQL 호환 형식으로 변환합니다. 예를 들어 다음 SQL Server 쿼리는 테이블에서 문자열을 검색합니다.

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)

변환 후 PostgreSQL 호환 인라인 SQL 쿼리는 다음과 같습니다.

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)

동적 SQL 쿼리를 처리합니다.

동적 SQL은 하나의 스크립트 또는 여러 Python 스크립트에 존재할 수 있습니다. 이전 예제에서는 Python의 문자열 대체 함수를 사용하여 동적 SQL 쿼리를 구성하기 위한 변수를 삽입하는 방법을 보여 주었습니다. 또 다른 접근 방식은 해당하는 경우 쿼리 문자열에 변수를 추가하는 것입니다. 

다음 예제에서는 함수가 반환한 값에 따라 쿼리 문자열을 즉시 구성합니다.

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

이러한 유형의 동적 쿼리는 애플리케이션 마이그레이션 중에 매우 일반적입니다. 동적 쿼리를 처리하려면 다음 단계를 따르세요.

결과 세트, 변수 및 데이터 프레임을 처리합니다.

Microsoft SQL Server의 경우 fetchone() 또는 fetchall()과 같은 Python 메서드를 사용하여 데이터베이스에서 결과 세트를 검색합니다. fetchmany(size)를 사용하여 결과 세트에서 반환할 레코드 수를 지정할 수도 있습니다. 이를 위해 다음 예제와 같이 pyodbc 연결 객체를 사용할 수 있습니다.

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

Aurora에서는 PostgreSQL에 연결하고 결과 세트를 가져오는 것과 같은 유사한 작업을 수행하기 위해 psycopg2 또는 SQLAlchemy를 사용할 수 있습니다. 이러한 Python 라이브러리는 다음 예제와 같이 PostgreSQL 데이터베이스 레코드를 탐색할 수 있는 연결 모듈과 커서 객체를 제공합니다.

psycopg2 (Aurora PostgreSQL-Compatible)

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 (Aurora PostgreSQL-Compatible)

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)

마이그레이션 도중과 마이그레이션 후에 애플리케이션을 테스트합니다.

마이그레이션된 Python 애플리케이션을 테스트하는 것은 지속적인 프로세스입니다. 마이그레이션에는 연결 객체 변경(psycopg2 또는 SQLAlchemy), 오류 처리, 새 기능(데이터 프레임), 인라인 SQL 변경, 대량 복사 기능(COPY 대신 bcp) 및 유사한 변경 사항이 포함되므로 애플리케이션 마이그레이션 도중 및 후에 신중하게 테스트해야 합니다. 다음을 확인합니다.

기존 Perl 코드베이스를 분석합니다.

분석에는 애플리케이션 마이그레이션 프로세스를 용이하게 하는 다음 내용이 포함되어야 합니다. 다음을 식별해야 합니다.

PostgreSQL을 지원하도록 Perl 애플리케이션과 DBI 모듈의 연결을 변환합니다.

Perl 기반 애플리케이션은 일반적으로 Perl 프로그래밍 언어의 표준 데이터베이스 액세스 모듈인 Perl DBI 모듈을 사용합니다. SQL Server 및 postgreSQL의 드라이버가 다른 동일한 DBI 모듈을 사용할 수 있습니다.

필수 Perl 모듈, 설치 및 기타 지침에 대한 자세한 내용은 DBD::Pg 설명서를 참조하세요. 다음 예제는 exampletest-aurorapg-database.cluster-sampleclusture.us-east.-rds.amazonaws.com에서 Aurora PostgreSQL-Compatible에 연결합니다.

#!/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}
      );

인라인 SQL 쿼리를 PostgreSQL로 변경합니다.

애플리케이션에 SELECT, DELETE, UPDATE, 및 PostgreSQL에서 지원하지 않는 쿼리 절을 포함하는 유사한 문을 포함하는 인라인 SQL 쿼리가 있을 수 있습니다. 예를 들어 TOP 및 NOLOCK과 같은 쿼리 키워드는 PostgreSQL에서 지원되지 않습니다. 다음 예는 TOP, NOLOCK 및 부울 변수를 처리할 수 있는 방법을 보여 줍니다.

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)

PostgreSQL의 경우 다음과 같이 변환합니다.

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

동적 SQL 쿼리와 Perl 변수를 처리합니다.

동적 SQL 쿼리는 애플리케이션 런타임 시 빌드되는 SQL 문입니다. 이러한 쿼리는 특정 조건에 따라 애플리케이션이 실행 중일 때 동적으로 구성되므로 런타임까지 쿼리의 전체 텍스트를 알 수 없습니다. 예를 들면 상위 10개 주식을 매일 분석하는 재무 분석 애플리케이션이 있으며, 이러한 주식은 매일 바뀝니다. SQL 테이블은 최고 성과자에 따라 생성되며 런타임까지 값을 알 수 없습니다.

이 예제의 인라인 SQL 쿼리를 래퍼 함수에 전달하여 변수에 결과 세트를 가져온 다음 변수가 조건을 사용하여 테이블이 존재하는지 여부를 판별한다고 가정해 보겠습니다.

다음은 변수 처리의 예와 이 사용 사례에 대한 SQL Server 및 PostgreSQL 쿼리입니다.

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

다음 예제에서는 JOIN과 함께 SELECT 문을 실행하여 테이블의 프라이머리 키와 키 열의 위치를 가져 오는 인라인 SQL의 Perl 변수를 사용합니다.

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

추가 SQL Server 구문을 PostgreSQL로 변환합니다.

다음 변경 사항은 프로그래밍 언어에 상관없이 모든 애플리케이션에 적용됩니다.

AWS 서비스를 활용하여 성능을 개선합니다.

AWS 클라우드로 마이그레이션하면 애플리케이션 및 데이터베이스 설계를 개선하여 AWS 서비스를 활용할 수 있습니다. 예를 들어 Aurora PostgreSQL-Compatible 데이터베이스 서버에 연결된 Python 애플리케이션의 쿼리가 원래 Microsoft SQL Server 쿼리보다 시간이 더 많이 걸리는 경우 Aurora 서버에서 Amazon Simple Storage Service(S3) 버킷으로 직접 기록 데이터 피드를 생성하고 Amazon Athena 기반 SQL 쿼리를 사용하여 보고서를 생성하고 사용자 대시보드에 대한 데이터 쿼리를 분석하는 것을 고려할 수 있습니다.