Microsoft SQL Server から Amazon Aurora PostgreSQL 互換エディションへのデータベース移行をサポートするように 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 は、1 つのスクリプトに存在することも、複数の 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 互換)

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

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

移行中や移行後にアプリケーションをテストします。

移行後の Python アプリケーションのテストは継続的なプロセスです。移行には接続オブジェクトの変更 (psycopg2 または SQLAlchemy)、エラー処理、新機能 (データフレーム)、インライン SQL の変更、大容量レプリケーション機能 (COPY ではなく bcp)、および同様の変更が含まれるため、アプリケーションの移行中および移行後に慎重にテストする必要があります。以下の項目を確認してください。

既存の Perl コードベースを分析します。

アプリケーションの移行プロセスを円滑に進めるため、以下の項目を分析に含める必要があります。以下の項目を特定する必要があります。

Perl アプリケーションと DBI モジュールからの接続を PostgreSQL をサポートするように変換します。　

Perl ベースのアプリケーションは通常、Perl プログラミング言語の標準データベースアクセスモジュールである Perl DBI モジュールを使用します。　 同じ DBI モジュールを SQL Server 用と PostgreSQL 用の異なるドライバーで使用できます。

必要な Perl モジュール、インストール、およびその他の手順の詳細については、DBD::Pg のドキュメントを参照してください。次の例は、exampletest-aurorapg-database.cluster-sampleclusture.us-east.-rds.amazonaws.com で Aurora PostgreSQL 互換に接続しています。

#!/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、およびブール型変数の処理方法を示しています。

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

次の例では、インライン SQL の Perl 変数を使用して、SELECT と一緒に JOIN ステートメントを実行し、テーブルのプライマリキーとキーカラムの位置を取得します。

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 互換データベースサーバーに接続されている Python アプリケーションからのクエリが、元の Microsoft SQL Server クエリよりも時間がかかる場合は、Aurora サーバーから Amazon Simple Storage Service (Amazon S3) バケットに履歴データのフィードを直接作成し、Amazon Athena ベースの SQL クエリを使用してユーザーダッシュボード用のレポートと分析データクエリを生成することを検討できます。