本文為英文版的機器翻譯版本,如內容有任何歧義或不一致之處,概以英文版為準。
本文為英文版的機器翻譯版本,如內容有任何歧義或不一致之處,概以英文版為準。
變更 Python 和 Perl 應用程式,以支援從 Microsoft SQL Server 遷移至 Amazon Aurora PostgreSQL 相容版本 由 Dwarika Patra (AWS) 和 Deepesh Jayaprakash (AWS) 建立
Summary 此模式說明將資料庫從 Microsoft SQL Server 遷移至 Amazon Aurora PostgreSQL 相容版本時可能需要的應用程式儲存庫變更。此模式假設這些應用程式是以 Python 為基礎或 Perl 為基礎,並提供這些指令碼語言的個別指示。
將 SQL Server 資料庫遷移至 Aurora PostgreSQL 相容包含結構描述轉換、資料庫物件轉換、資料遷移和資料載入。由於 PostgreSQL 和 SQL Server (與資料類型、連線物件、語法和邏輯相關) 之間的差異,最困難的遷移任務涉及對程式碼基礎進行必要的變更,以便能夠正確搭配 PostgreSQL 使用。
對於以 Python 為基礎的應用程式,連線物件和類別分散在整個系統中。此外,Python 程式碼基礎可能會使用多個程式庫來連線至資料庫。如果資料庫連線介面變更,執行應用程式內嵌查詢的物件也需要變更。
對於 Perl 型應用程式,變更涉及連線物件、資料庫連線驅動程式、靜態和動態內嵌 SQL 陳述式,以及應用程式如何處理複雜的動態 DML 查詢和結果集。
遷移應用程式時,您也可以考慮 AWS 上可能的增強功能,例如將 FTP 伺服器取代為 Amazon Simple Storage Service (Amazon S3) 存取。
應用程式遷移程序涉及下列挑戰:
連線物件。如果連線物件分散在具有多個程式庫和函數呼叫的程式碼中,您可能必須找到一種一般方式來變更它們以支援 PostgreSQL。
在記錄擷取或更新期間處理錯誤或例外狀況。如果您在傳回變數、結果集或資料影格的資料庫上有條件式建立、讀取、更新和刪除 (CRUD) 操作,任何錯誤或例外狀況都可能導致應用程式錯誤與串聯效果。應透過適當的驗證和儲存點仔細處理這些項目。其中一個儲存點是呼叫BEGIN...EXCEPTION...END
區塊內的大型內嵌 SQL 查詢或資料庫物件。
控制交易及其驗證。這些包括手動和自動遞交和轉返。適用於 Perl 的 PostgreSQL 驅動程式需要您一律明確設定自動遞交屬性。
處理動態 SQL 查詢。這需要深入了解查詢邏輯和反覆測試,以確保查詢如預期般運作。
效能。您應該確保程式碼變更不會導致應用程式效能降低。
此模式會詳細說明轉換程序。
先決條件和限制 先決條件
Python 和 Perl 語法的工作知識。
SQL Server 和 PostgreSQL 中的基本技能。
了解您現有的應用程式架構。
存取您的應用程式碼、SQL Server 資料庫和 PostgreSQL 資料庫。
使用用於開發、測試和驗證應用程式變更的登入資料,存取 Windows 或 Linux (或其他 Unix) 開發環境。
對於 Python 型應用程式,您的應用程式可能需要的標準 Python 程式庫,例如 Pandas 處理資料影格,以及 psycopg2 或 SQLAlchemy 進行資料庫連線。
對於 Perl 型應用程式,需要具有相依程式庫或模組的 Perl 套件。Comprehensive Perl Archive Network (CPAN) 模組可支援大多數應用程式需求。
所有必要的相依自訂程式庫或模組。
用於 SQL Server 讀取存取和 Aurora 讀取/寫入存取的資料庫憑證。
PostgreSQL 可驗證應用程式變更,並對 服務和使用者進行偵錯。
在應用程式遷移期間存取開發工具,例如 Visual Studio Code、Sublime Text 或 pgAdmin 。
限制
有些 Python 或 Perl 版本、模組、程式庫和套件與雲端環境不相容。
某些用於 SQL Server 的第三方程式庫和架構無法取代,以支援 PostgreSQL 遷移。
效能變化可能需要變更您的應用程式、內嵌 Transact-SQL (T-SQL) 查詢、資料庫函數和預存程序。
PostgreSQL 支援資料表名稱、資料欄名稱和其他資料庫物件的小寫名稱。
有些資料類型,例如 UUID 資料欄,只會以小寫儲存。Python 和 Perl 應用程式必須處理此類案例差異。
角色編碼差異必須使用 PostgreSQL 資料庫中對應文字資料欄的正確資料類型來處理。
產品版本
Python 3.6 或更新版本 (使用支援您作業系統的版本)
Perl 5.8.3 或更新版本 (使用支援您作業系統的版本)
Aurora PostgreSQL 相容版本 4.2 或更新版本 (請參閱詳細資訊 )
架構 來源技術堆疊
指令碼 (應用程式程式設計) 語言:Python 2.7 或更新版本,或 Perl 5.8
資料庫:Microsoft SQL Server 第 13 版
作業系統:Red Hat Enterprise Linux (RHEL) 7
目標技術堆疊
遷移架構
工具 AWS 服務和工具
其他工具
史詩 任務 描述 所需的技能 請依照這些程式碼轉換步驟,將您的應用程式遷移至 PostgreSQL。
設定 PostgreSQL 的資料庫特定 ODBC 驅動程式和程式庫。例如,您可以將其中一個 CPAN 模組用於 Perl 和 pyodbc 、psycopg2 或 SQLAlchemy for Python。
使用這些程式庫連線至 Aurora PostgreSQL 相容,以轉換資料庫物件。
在現有應用程式模組中套用程式碼變更,以取得相容的 T-SQL 陳述式。
重寫應用程式程式碼中的資料庫特定函數呼叫和預存程序。
處理應用程式變數及其資料類型的變更,這些變數用於內嵌 SQL 查詢。
處理不相容的資料庫特定函數。
完成轉換應用程式程式碼的end-to-end測試,以進行資料庫遷移。
將 Microsoft SQL Server 的結果與您遷移至 PostgreSQL 的應用程式進行比較。
在 Microsoft SQL Server 和 PostgreSQL 之間執行應用程式效能基準測試。
修改預存程序或由應用程式呼叫的內嵌 T-SQL 陳述式,以改善效能。
下列語彙提供 Python 和 Perl 應用程式部分這些轉換任務的詳細說明。
應用程式開發人員 針對遷移的每個步驟使用檢查清單。
將以下內容新增至應用程式遷移每個步驟的檢查清單,包括最後一個步驟:
檢閱 PostgreSQL 文件,以確保您的所有變更都與 PostgreSQL 標準相容。
檢查欄的整數和浮點數。
識別插入、更新和擷取的資料列數量,以及資料欄名稱和日期/時間戳記。您可以使用 diff 公用程式或撰寫指令碼來自動化這些檢查。
完成大型內嵌 SQL 陳述式的效能檢查,並檢查應用程式的整體效能。
使用多個 try/catch 區塊,檢查資料庫操作和正常程式退出的正確錯誤處理。
檢查以確保有適當的記錄程序。
應用程式開發人員
任務 描述 所需的技能 分析現有的 Python 程式碼基礎。
您的分析應包含下列項目,以促進應用程式遷移程序:
識別程式碼中的所有連線物件。
識別所有不相容的內嵌 SQL 查詢 (例如 T-SQL 陳述式和預存程序),並分析必要的變更。
檢閱程式碼的文件並追蹤控制流程,以了解程式碼功能。當您稍後測試應用程式的效能或負載比較時,這會很有幫助。
了解應用程式的目的,讓您可以在資料庫轉換後有效地進行測試。大多數適合與資料庫遷移進行轉換的 Python 應用程式都是將資料從其他來源載入資料庫資料表的摘要,或從資料表擷取資料的擷取器,並將其轉換為適合建立報告或進行 API 呼叫來執行驗證的不同輸出格式 (例如 CSV、JSON 或一般檔案)。
應用程式開發人員 轉換資料庫連線以支援 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) + “’”
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”
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
這些類型的動態查詢在應用程式遷移期間非常常見。請依照下列步驟處理動態查詢:
檢查整體語法 (例如,含 子句的SELECT
陳述式JOIN
語法)。
驗證查詢中使用的所有變數或資料欄名稱,例如 i
和 id
。
檢查查詢中使用的函數、引數和傳回值 (例如,get_id_filter
及其引數 ids
)。
應用程式開發人員 處理結果集、變數和資料影格。
對於 Microsoft SQL Server,您可以使用 Python 方法,例如 fetchall()
fetchone()
或 從資料庫擷取結果集。您也可以使用 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 ]))
SQLAlchemy (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 變更、大量複製功能 (bcp
而非 COPY
) 和類似的變更,因此必須在應用程式遷移期間和之後仔細測試。檢查:
錯誤條件和處理
遷移後的任何記錄不相符
記錄更新或刪除
執行應用程式所需的時間
應用程式開發人員
任務 描述 所需的技能 分析現有的 Perl 程式碼基礎。
您的分析應包含下列項目,以促進應用程式遷移程序。您應該識別:
任何 INI 或組態型程式碼
資料庫特定的標準開放式資料庫連線 (ODBC) Perl 驅動程式或任何自訂驅動程式
內嵌和 T-SQL 查詢所需的程式碼變更
各種 Perl 模組之間的互動 (例如,由多個功能元件呼叫或使用的單一 Perl ODBC 連線物件)
資料集和結果集處理
外部相依 Perl 程式庫
應用程式中使用的任何 APIs
Perl 版本相容性和與 Aurora PostgreSQL 相容驅動程式相容性
應用程式開發人員 轉換 Perl 應用程式和 DBI 模組的連線以支援 PostgreSQL。
Perl 型應用程式通常使用 Perl DBI 模組,這是 Perl 程式設計語言的標準資料庫存取模組。您可以使用相同的 DBI 模組搭配 SQL Server 和 PostgreSQL 的不同驅動程式。
如需必要 Perl 模組、安裝和其他說明的詳細資訊,請參閱 DBD::Pg 文件 。下列範例會連線至 上的 Aurora PostgreSQL 相容exampletest-aurorapg-database.cluster-sampleclusture.us-east.-rds.amazonaws.com
。
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
、UPDATE
、 DELETE
和類似陳述式的內嵌 SQL 查詢,其中包含 PostgreSQL 不支援的查詢子句。例如,PostgreSQL NOLOCK
不支援查詢關鍵字,例如 TOP
和 。下列範例示範如何處理 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){
}
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。
下列變更適用於所有應用程式,無論程式設計語言為何。
限定應用程式搭配新的適當結構描述名稱使用的資料庫物件。
使用 PostgreSQL 中的定序功能 來處理 LIKE 運算子的區分大小寫比對。
處理不支援的資料庫特定函數,例如 DATEDIFF
、DATEADD
、CONVERT
、 GETDATE
和 CAST
運算子。如需同等 PostgreSQL 相容函數,請參閱其他資訊 區段中的原生或內建 SQL 函數 。
在比較陳述式中處理布林值。
處理函數的傳回值。這些值可以是記錄集、資料影格、變數和布林值。根據您應用程式的需求和支援 PostgreSQL 來處理這些項目。
使用新的使用者定義的 PostgreSQL 函數來處理匿名區塊 (例如 BEGIN TRAN
)。
轉換資料列的大量插入。從應用程式內部呼叫的 SQL Server 大量複製 (bcp
) 公用程式的 PostgreSQL 對等值是 COPY
。
轉換資料欄串連運算子。SQL Server 使用 +
進行字串串連,但 PostgreSQL 使用 ||
。
應用程式開發人員
任務 描述 所需的技能 利用 AWS 服務來增強效能。
當您遷移至 AWS 雲端時,您可以改進應用程式和資料庫設計,以利用 AWS 服務。例如,如果來自連接至 Aurora PostgreSQL 相容資料庫伺服器的 Python 應用程式的查詢花費比原始 Microsoft SQL Server 查詢更長的時間,您可以考慮直接從 Aurora 伺服器建立歷史資料的摘要至 Amazon Simple Storage Service (Amazon S3) 儲存貯體,並使用以 Amazon Athena 為基礎的 SQL 查詢來產生使用者儀表板的報告和分析資料查詢。
應用程式開發人員、雲端架構師
相關資源 其他資訊 Microsoft SQL Server 和 Aurora PostgreSQL 相容皆為 ANSI SQL 合規。不過,當您將 Python 或 Perl 應用程式從 SQL Server 遷移至 PostgreSQL 時,仍應注意語法、資料欄資料類型、原生資料庫特定函數、大量插入和大小寫方面的任何不相容。
下列各節提供有關可能的不一致的詳細資訊。
資料類型比較
從 SQL Server 到 PostgreSQL 的資料類型變更,可能會導致應用程式操作時所產生的資料出現重大差異。如需資料類型的比較,請參閱 Sqlines 網站上的 資料表。
原生或內建 SQL 函數
某些函數的行為在 SQL Server 和 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 Server 和 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\'" ;
Concatenation: SQL Server 使用 +
做為字串串連的運算子,而 PostgreSQL 則使用 ||
。
驗證: 在 PostgreSQL 的應用程式程式碼中使用內嵌 SQL 查詢和函數之前,您應該先進行測試和驗證。
ORM 程式庫包含 : 您也可以尋找將現有的資料庫連線程式庫包含或取代為 Python ORM 程式庫,例如 SQLAlchemy 和 PynomoDB 。這有助於使用物件導向範式,從資料庫輕鬆查詢和操作資料。