本文為英文版的機器翻譯版本，如內容有任何歧義或不一致之處，概以英文版為準。
更改 Python 和 Perl 應用程序以支持從 Microsoft SQL 服務器遷移到 Amazon Aurora PostgreSQL 兼容版本的數據庫
創建者：德瓦里卡帕特拉（AWS）和傑亞普拉卡什（AWS）
環境：PoC 或試點 | 來源：SQL 服務器 | 目標：Aurora 郵政兼容 |
R 類型：重新平台 | 工作負載：Microsoft；開源 | 技術：移轉；資料庫 |
AWS 服務：Amazon Aurora |
Summary
此模式描述了將資料庫從 Microsoft SQL Server 遷移到 Amazon Aurora PostgreSQL 相容版本時可能需要的應用程式儲存庫變更。此模式假設這些應用程式是以 Python 為基礎或 Perl，並為這些指令碼語言提供個別的指示。
將 SQL 伺服器資料庫移轉至 Aurora PostgreSQL 相容涉及結構描述轉換、資料庫物件轉換、資料移轉和資料載入。由於 PostgreSQL 和 SQL Server 之間的差異 (與資料類型、連線物件、語法和邏輯有關)，因此最困難的移轉工作包括對程式碼基底進行必要的變更，以便它可以與 PostgreSQL 正常運作。
對於基於 Python 的應用程序，連接對象和類分散在整個系統中。此外，Python 代碼庫可能會使用多個庫來連接到數據庫。如果資料庫連線介面變更，則執行應用程式內嵌查詢的物件也需要變更。
對於 Perl 型應用程式而言，變更包括連線物件、資料庫連線驅動程式、靜態和動態內嵌 SQL 陳述式，以及應用程式如何處理複雜的動態 DML 查詢和結果集。
遷移應用程式時，您也可以考慮 AWS 上可能的增強功能，例如以 Amazon Simple Storage Service (Amazon S3) 存取取代 FTP 伺服器。
應用程式移轉程序包含下列挑戰：
連接對象。如果連接對象分散在具有多個庫和函數調用的代碼中，則可能需要找到一種通用的方法來更改它們以支持 PostgreSQL。
記錄檢索或更新期間的錯誤或異常處理。如果您對傳回變數、結果集或資料框架的資料庫具有條件式建立、讀取、更新和刪除 (CRUD) 作業，則任何錯誤或例外狀況都可能會導致具有串聯效果的應用程式錯誤。這些應該通過適當的驗證和保存點仔細處理。一個這樣的保存點是調用BEGIN...EXCEPTION...END塊內的大型內聯 SQL 查詢或數據庫對象。
控制交易及其驗證。其中包括手動和自動提交和復原。Perl 的 PostgreSQL 驅動程序要求您始終明確設置自動提交屬性。
處理動態 SQL 查詢。這需要對查詢邏輯和迭代測試有深刻的了解，以確保查詢按預期工作。
效能。您應該確定程式碼變更不會導致應用程式效能降低。
此模式詳細解釋了轉換過程。
先決條件和限制
先決條件
Python 和 Perl 語法的工作知識。
SQL 伺服器和 PostgreSQL 的基本技能。
瞭解您現有的應用程式架構。
存取您的應用程式程式碼、SQL 伺服器資料庫和 PostgreSQL 資料庫。
存取 Windows 或 Linux (或其他 Unix) 開發環境，其中包含用於開發、測試和驗證應用程式變更的認證。
對於基於 Python 的應用程序，您的應用程序可能需要的標準 Python 庫，例如熊貓來處理數據框，以及用於數據庫連接的 psycopg 2 或 SQLAlchemy。
對於 Perl 型應用程式，需要具有相依程式庫或模組的 Perl 套件。全面的 Perl 歸檔網絡（CPAN）模塊可以支持大多數應用程序需求。
所有必需的依賴自定義庫或模塊。
SQL Server 讀取存取權和對 Aurora 的讀取/寫入存取權的資料庫認證。
PostgreSQL 可透過服務和使用者驗證和偵錯應用程式變更。
在應用程序遷移期間訪問開發工具，例如視覺工作室代碼，崇高的文本或 PG Admin。
限制
某些 Python 或 Perl 版本、模組、程式庫和套件與雲端環境不相容。
某些用於 SQL Server 的協力廠商程式庫和架構無法取代，以支援 PostgreSQL 移轉。
效能差異可能需要變更應用程式、內嵌 Transact-SQL (T-SQL) 查詢、資料庫函數和預存程序。
PostgreSQL 支持表名，列名和其他數據庫對象的小寫名稱。
某些資料類型 (例如 UUID 資料行) 僅以小寫儲存。Python 和 Perl 應用程序必須處理這種情況的差異。
字元編碼差異必須使用 PostgreSQL 資料庫中對應文字欄的正確資料類型來處理。
產品版本
架構
源, 技術, 堆棧
目標技術堆疊
移轉架構
工具
AWS 服務和工具
其他工具
史诗
| 任務 | 描述 | 所需技能 |
|---|
請依照下列程式碼轉換步驟，將您的應用程式遷移至 PostgreSQL。 | 為 PostgreSQL 設置特定於數據庫的 ODBC 驅動程序和庫。例如，您可以使用一個 CPAN 模塊的 Perl 和 pyodbc，通靈 2，或 SQL 煉金我的 Python。 通過使用這些庫連接到 Aurora PostgreSQL 兼容轉換數據庫對象。 在現有應用程式模組中套用程式碼變更以取得相容的 T-SQL 陳述式。 在應用程序代碼中重寫數據庫特定的函數調用和存儲過程。 處理應用程式變數及其用於內嵌 SQL 查詢之資料類型的變更。 處理不相容的資料庫特定功能。 完整的數據庫遷移轉換後的應用程序代碼 end-to-end 測試。 將 Microsoft SQL 伺服器的結果與您遷移到 PostgreSQL 的應用程式進行比較。 執行 Microsoft SQL 伺服器和 PostgreSQL 之間的應用程式效能基準測試。 修訂應用程式呼叫的預存程序或內嵌 T-SQL 陳述式，以改善效能。
以下史詩提供了一些 Python 和 Perl 應用程序轉換任務的詳細說明。 | 應用程式開發人員 |
針對移轉的每個步驟使用檢查清單。 | 針對應用程式遷移的每個步驟，將下列項目新增至檢查清單，包括最後一個步驟： 請參閱 PostgreSQL 文件，確保您的所有變更都與 PostgreSQL 標準相容。 檢查資料行是否有整數和浮動值。 識別插入、更新和擷取的列數，以及欄名稱和日期/時間戳記。您可以使用 diff 公用程式或撰寫指令碼來自動執行這些檢查。 完成大型內嵌 SQL 陳述式的效能檢查，並檢查應用程式的整體效能。 通過使用多個 try/catch 塊檢查數據庫操作和優雅程序退出的正確錯誤處理。 檢查以確保正確的日誌記錄過程已到位。
| 應用程式開發人員 |
| 任務 | 描述 | 所需技能 |
|---|
分析您現有的 Python 程式碼庫。 | 您的分析應包括以下內容，以促進應用程序遷移過程： 識別程式碼中的所有連線物件。 識別所有不相容的內嵌 SQL 查詢 (例如 T-SQL 陳述式和預存程序)，並分析必要的變更。 檢閱程式碼的文件，並追蹤控制流程，以瞭解程式碼功能。稍後當您測試應用程式的效能或負載比較時，這會很有幫助。 了解應用程序的目的，以便您可以在數據庫轉換後有效地對其進行測試。大多數作為資料庫遷移轉換的候選 Python 應用程式可能是將資料從其他來源載入資料庫資料表的摘要，或是擷取資料從資料表擷取資料並將其轉換為不同的輸出格式 (例如 CSV、JSON 或平面檔案)，適合建立報表或進行 API 呼叫以執行驗證。
| 應用程式開發人員 |
將您的資料庫連線轉換為支援 PostgreSQL。 | 大多數 Python 應用程序使用 pyodbc 庫與 SQL 服務器數據庫連接如下。 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
這些類型的動態查詢在應用程式移轉期間非常常見。請遵循下列步驟來處理動態查詢： 檢查整體語法 (例如，含JOIN子句之SELECT陳述式的語法)。 驗證查詢中使用的所有變數或資料行名稱，例如i和id。 檢查查詢中使用的函數、引數和傳回值 (例如，get_id_filter及其引數ids)。
| 應用程式開發人員 |
處理結果集、變數和資料框架。 | 對於 Microsoft SQL 服務器，您可以使用 Python 方法，fetchall()如fetchone()或從數據庫中檢索結果集。您也可以使用fetchmany(size)並指定要從結果集傳回的記錄數。若要這麼做，您可以使用 pyodbc 連線物件，如下列範例所示。 軟件（Microsoft SQL 服務器） 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 和獲取結果集，您可以使用通靈 2 或 SQLAlchemy。這些 Python 庫提供了連接模塊和光標對象通過 PostgreSQL 數據庫記錄遍歷，如下面的例子。 精神科技 2（Aurora 兼容） 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 煉金術（Aurora 後兼容） 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 更改，批量複製功能（bcp而不是COPY）和類似的更改，因此必須在應用程序遷移期間和之後仔細測試。檢查： 錯誤條件和處理 移轉後的任何記錄不相符 記錄更新或刪除 執行應用程式所需的時間
| 應用程式開發人員 |
| 任務 | 描述 | 所需技能 |
|---|
分析您現有的 Perl 代碼庫。 | 您的分析應包括以下內容，以促進應用程序遷移過程。您應該識別： 任何 INI 或基於配置的代碼 資料庫特定的標準開放式資料庫連線 (ODBC) Perl 驅動程式或任何自訂驅動程式 內嵌和 T-SQL 查詢所需的程式碼變更 各種 Perl 模塊之間的相互作用（例如，由多個功能組件調用或使用的單個 Perl ODBC 連接對象） 資料集和結果集處理 外部相依的 Perl 程式庫 應用程式中使用的任何 API Perl 版本兼容性和驅動程序兼容 Aurora PostgreSQL 兼容
| 應用程式開發人員 |
將來自 Perl 應用程序和 DBI 模塊的連接轉換為支持 PostgreSQL。 | Perl 型應用程式通常使用 Perl DBI 模組，這是 Perl 程式設計語言的標準資料庫存取模組。您可以使用相同的 DBI 模塊與不同的驅動程序 SQL 服務器和 PostgreSQL。 如需有關所需 Perl 模組、安裝和其他指示的詳細資訊，請參閱 DBD:: Pg 文件。下面的例子連接到 Aurora PostgreSQL 兼容。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}
);
| 應用程式開發人員 |
將內聯 SQL 查 PostgreSQL 改為 | 您的應用程式可能具有SELECT、DELETE、和類似陳述式的內嵌 SQL 查詢UPDATE，其中包含 PostgreSQL 不支援的查詢子句。例如，PostgreSQL 中NOLOCK不支援查詢關鍵字，例如TOP和。下列範例說明如何處理TOPNOLOCK、和布林變數。 在 SQL 伺服器中： $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 伺服器和 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 伺服器： 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 變數，該變數會執行含有 a 的SELECT陳述式，JOIN以擷取資料表的主索引鍵和索引鍵資料行的位置。 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' ;";
| 應用程式開發人員 |
| 任務 | 描述 | 所需技能 |
|---|
將其他 SQL 伺服器建構轉換為 PostgreSQL。 | 下列變更適用於所有應用程式，不論程式語言為何。 以新的和適當的結構描述名稱來限定應用程式使用的資料庫物件。 使用 PostgreSQL 中的定序功能處理相似運算子，以區分大小寫的比對。 處理不受支援的資料庫特定功能 DATEDIFFDATEADD，例如GETDATE、CONVERT、和CAST運算子。如需與 PostgreSQL 相容的同等函式，請參閱其他資訊一節中的原生或內建 SQL 函數。 處理比較陳述式中的布林值。 處理函數的返回值。這些可以是記錄集，數據幀，變量和布爾值。根據您的應用程式的需求來處理這些問題，並支援 PostgreSQL。 使用新的、使用者定義的 PostgreSQL 函式處理匿名區塊 (例如BEGIN TRAN)。 轉換列的批量插入。相當於 SQL Server 大量複製 (bcp) 公用程式的 PostgreSQL，這是從應用程式內部呼叫的。COPY 轉換列連接運算符。SQL 伺服器用+於字串串連，但 PostgreSQL 會使用。||
| 應用程式開發人員 |
| 任務 | 描述 | 所需技能 |
|---|
利用 AWS 服務進行效能增強。 | 遷移到 AWS 雲端時，您可以優化應用程式和資料庫設計以利用 AWS 服務。例如，如果來自 Python 應用程式的查詢連接至 Aurora PostgreSQL 相容資料庫伺服器，所花費的時間比原始 Microsoft SQL Server 查詢還要長，您可以考慮從 Aurora 伺服器直接建立歷史資料饋送至 Amazon 簡單儲存服務 (Amazon S3) 儲存貯體，並使用 Amazon Athena 為使用者儀表板產生報表和分析資料查詢。 | 應用程式開發人員、雲端 |
相關資源
其他資訊
這兩個 Microsoft SQL 服務器和 Aurora PostgreSQL 兼容是 ANSI SQL 投訴。不過，當您將 Python 或 Perl 應用程式從 SQL Server 遷移到 PostgreSQL 時，您仍然應該注意語法、資料行資料類型、原生資料庫特定函數、大量插入和區分大小寫方面的任何不相容性。
以下各節提供有關可能不一致的詳細資訊。
資料類型比較
從 SQL Server 到 PostgreSQL 的資料類型變更可能會導致應用程式運作所產生的資料顯著差異。有關數據類型的比較，請參閱 S qlines 網站上的表格。
原生或內建 SQL 函數
SQL 伺服器和 PostgreSQL 資料庫之間的某些函式的行為不同。下表提供了一個比較。
Microsoft SQL Server | 描述 | PostgreSQL |
|---|
CAST
| 將一個值從某個資料類型轉換至另一個類型。 | PostgreSQL type :: operator |
GETDATE()
| 以YYYY-MM-DD hh:mm:ss.mmm格式返回當前數據庫系統的日期和時間。 | CLOCK_TIMESTAMP
|
DATEADD
| 將時間/日期間隔添加到日期。 | INTERVAL表達
|
CONVERT
| 將值轉換為特定資料格式。 | TO_CHAR
|
DATEDIFF
| 返回兩個日期之間的差異。 | DATE_PART
|
TOP
| 限制SELECT結果集中的列數。 | LIMIT/FETCH
|
匿名圖塊
結構化 SQL 查詢會組織成多個區段，例如宣告、可執行檔和例外狀況處理。下表比較了一個簡單的匿名塊的 Microsoft SQL 服務器和 PostgreSQL 版本。對於複雜的匿名塊，我們建議您在應用程序中調用自定義數據庫函數。
Microsoft SQL Server | PostgreSQL |
|---|
my $sql_qry1=
my $sql_qry2 =
my $sqlqry = "BEGIN TRAN
$sql_qry1 $sql_qry2
if @\@error !=0 ROLLBACK
TRAN
else COMIT TRAN";
| my $sql_qry1=
my $sql_qry2 =
my $sql_qry = " DO \$\$
BEGIN
$header_sql $content_sql
END
\$\$";
|
其他差異
my $sql_qry = "SELECT $record_id FROM $exampleTable WHERE LOWER($record_name) = \'failed transaction\'";
