Azure Synapse Analytics 中專用 SQL 集區的資料載入策略
傳統的 SMP 專用 SQL 集區會使用擷取、轉換和載入 （ETL） 程式來載入資料。 Azure Synapse Analytics 中的 Synapse SQL 會使用分散式查詢處理架構，利用計算和記憶體資源的延展性和彈性。
使用擷取、載入和轉換 （ELT） 程式會使用內建的分散式查詢處理功能，並排除載入數據轉換所需的資源。
雖然專用 SQL 集區支援許多載入方法，包括 bcp 和 SqlBulkCopy API 等熱門 SQL Server 選項，但載入資料的最快且最可調整的方式是透過 PolyBase 外部數據表和 COPY 語句。
透過 PolyBase 和 COPY 語句，您可以透過 T-SQL 語言存取儲存在 Azure Blob 記憶體或 Azure Data Lake Store 中的外部數據。 對於載入時最大的彈性，我們建議使用 COPY 語句。
什麼是 ELT？
擷取、載入和轉換 （ELT） 是一個程式，數據會從來源系統擷取、載入至專用 SQL 集區，然後轉換。
實作 ELT 的基本步驟如下：
- 將來源資料擷取至文字檔。
- 將數據登陸至 Azure Blob 記憶體或 Azure Data Lake Store。
- 準備要載入的數據。
- 使用 PolyBase 或 COPY 命令將數據載入臨時表。
- 轉換資料。
- 將資料插入生產資料表。
如需載入教學課程，請參閱 從 Azure Blob 記憶體載入數據。
1.將源數據擷取到文字檔
從來源系統取得數據取決於儲存位置。 目標是將數據移至支援的分隔文字或 CSV 檔案。
支援的檔案格式
使用 PolyBase 和 COPY 語句，您可以從 UTF-8 和 UTF-16 編碼的分隔文字或 CSV 檔案載入數據。 除了分隔的文字或 CSV 檔案之外，也會從 ORC 和 Parquet 等 Hadoop 檔格式載入。 PolyBase 和 COPY 語句也可以從 Gzip 和 Snappy 壓縮檔載入數據。
不支援擴充 ASCII、固定寬度格式和巢狀格式，例如 WinZip 或 XML。 如果您要從 SQL Server 匯出，您可以使用 bcp 命令行工具 將資料匯出成分隔的文字檔。
2.將數據登陸至 Azure Blob 記憶體或 Azure Data Lake Store
若要將數據放在 Azure 記憶體中，您可以將它移至 Azure Blob 記憶體 或 Azure Data Lake Store Gen2。 在任一位置，數據應該儲存在文本檔中。 PolyBase 和 COPY 語句可以從任一位置載入。
您可以用來將資料移至 Azure 儲存體 的工具和服務：
- Azure ExpressRoute 服務可增強網路輸送量、效能和可預測性。 ExpressRoute 是一項服務，可透過專用的私人連線將您的數據路由傳送至 Azure。 ExpressRoute 連線不會透過公用因特網路由傳送數據。 線上可提供比透過公用因特網的一般連線更高的可靠性、更快的速度、較低的延遲和更高的安全性。
- AzCopy 公用程式會透過公用因特網將數據移至 Azure 儲存體。 如果您的數據大小小於 10 TB，則適用。 若要使用 AzCopy 定期執行負載，請測試網路速度，以查看是否可接受。
- Azure Data Factory （ADF） 具有可在本機伺服器上安裝的閘道。 然後，您可以建立管線，將數據從本機伺服器移至 Azure 儲存體。 若要搭配專用 SQL 集區使用 Data Factory，請參閱 載入專用 SQL 集區的數據。
3.準備數據以載入
在載入之前，您可能需要準備和清除記憶體帳戶中的數據。 當您的數據位於來源、將數據匯出至文本檔時，或在數據處於 Azure 儲存體 之後，即可執行數據準備。 盡可能早地處理數據是最簡單的方式。
定義數據表
首先，使用 COPY 語句時，定義您要載入至專用 SQL 集區中的數據表。
如果您使用PolyBase，必須先在專用SQL集區中定義外部資料表，才能載入。 PolyBase 會使用外部數據表來定義及存取 Azure 儲存體 中的數據。 外部數據表類似於資料庫檢視。 外部數據表包含數據表架構，並指向儲存在專用 SQL 集區外部的數據。
定義外部數據表牽涉到指定數據源、文字檔的格式，以及數據表定義。 您需要的 T-SQL 語法參考文章如下：
載入 Parquet 檔案時，請使用下列 SQL 資料類型對應：
| Parquet 類型 | Parquet 邏輯類型 (註釋) | SQL 資料類型 |
|---|---|---|
| 布爾 | bit | |
| BINARY / BYTE_ARRAY | varbinary | |
| DOUBLE | float | |
| FLOAT | real | |
| INT32 | int | |
| INT64 | bigint | |
| INT96 | datetime2 | |
| FIXED_LEN_BYTE_ARRAY | binary | |
| 二元的 | UTF8 |
nvarchar |
| 二元的 | STRING |
nvarchar |
| 二元的 | ENUM |
nvarchar |
| 二元的 | UUID |
uniqueidentifier |
| 二元的 | DECIMAL |
decimal |
| 二元的 | JSON |
nvarchar（MAX） |
| 二元的 | BSON |
varbinary(MAX) |
| FIXED_LEN_BYTE_ARRAY | DECIMAL |
decimal |
| BYTE_ARRAY | INTERVAL |
varchar（MAX） |
| INT32 | INT(8, true) |
smallint |
| INT32 | INT(16, true) |
smallint |
| INT32 | INT(32, true) |
int |
| INT32 | INT(8, false) |
tinyint |
| INT32 | INT(16, false) |
int |
| INT32 | INT(32, false) |
bigint |
| INT32 | DATE |
date |
| INT32 | DECIMAL |
decimal |
| INT32 | TIME (MILLIS) |
time |
| INT64 | INT(64, true) |
bigint |
| INT64 | INT(64, false ) |
十進位（20,0） |
| INT64 | DECIMAL |
decimal |
| INT64 | TIME (MILLIS) |
time |
| INT64 | TIMESTAMP (MILLIS) |
datetime2 |
| 複雜類型 | LIST |
varchar(max) |
| 複雜類型 | MAP |
varchar(max) |
重要
- SQL 專用集區目前不支援 MICROS 和 NANOS 精確度的 Parquet 資料類型。
- 如果 Parquet 和 SQL 之間類型不符，或您不支援的 Parquet 數據類型，可能會遇到下列錯誤：
HdfsBridge::recordReaderFillBuffer - Unexpected error encountered filling record reader buffer: ClassCastException:... - 不支援將超出 0-127 範圍的值載入 Parquet 和 ORC 檔案格式的 tinyint 資料行。
如需建立外部物件的範例，請參閱 建立外部數據表。
格式化文字檔
如果您使用 PolyBase，定義的外部物件必須對齊文本文件的數據列與外部數據表和檔案格式定義。 文本檔中每個數據列的數據必須與數據表定義一致。
若要格式化文字檔：
- 如果您的資料來自非關係型來源，您需要將其轉換成數據列和數據行。 無論數據來自關係型或非關係型來源，都必須轉換數據，以符合您打算載入數據之數據表的數據行定義。
- 格式化文本文件中的數據，以配合目的地數據表中的數據行和數據類型。 外部文本檔和專用 SQL 集區數據表中數據類型之間的不對齊會導致載入期間拒絕數據列。
- 以終止符分隔文字檔中的欄位。 請務必使用源數據中找不到的字元或字元序列。 使用您以 CREATE EXTERNAL FILE FORMAT 指定的終止符。
4.使用 PolyBase 或 COPY 語句載入數據
將數據載入臨時表是最佳做法。 臨時表可讓您處理錯誤，而不會干擾生產數據表。 臨時表也可讓您在將數據插入生產數據表之前，先使用專用SQL集區平行處理架構進行數據轉換。
載入的選項
若要載入資料，您可以使用下列任一載入選項：
- COPY 語句是建議的載入公用程式，因為它可讓您順暢且靈活地載入數據。 語句具有 PolyBase 未提供的許多額外載入功能。 請參閱 NY 計程車 COPY 教學課程，以透過範例教學課程執行。
- 使用 T-SQL 的 PolyBase 需要您定義外部數據物件。
- 使用 Azure Data Factory 的 PolyBase 和 COPY 語句是 另一個協調流程工具。 它會定義管線並排程作業。
- 當您的源數據位於 SQL Server 時，具有 SSIS 的 PolyBase 運作良好。 SSIS 會定義目的地數據表對應的來源，並同時協調負載。 如果您已經有 SSIS 套件，您可以修改套件以使用新的資料倉儲目的地。
- 具有 Azure Databricks 的 PolyBase 會將數據從數據表傳輸到 Databricks 數據框架，以及/或使用 PolyBase 將數據從 Databricks 數據框架寫入數據表。
檢閱可用的教學課程：
- 教學課程：使用Microsoft Entra ID 載入外部數據
- 教學課程：使用受控識別載入外部數據
- 教學課程：載入紐約Taxicab數據集
- 教學課程：將數據載入 Azure Synapse Analytics SQL 集區
- 將 Contoso 零售數據載入 Azure Synapse Analytics 中的專用 SQL 集區
其他載入選項
除了 PolyBase 和 COPY 語句之外，您還可以使用 bcp 或 SqlBulkCopy API。 公用 bcp 程式會直接載入至資料庫，而不需要經過 Azure Blob 記憶體，而且僅適用於小型負載。
注意
這些選項的載入效能比 PolyBase 和 COPY 語句慢。
5.轉換數據
當數據位於臨時表中時，請執行工作負載所需的轉換。 然後將數據移至生產數據表。
6.將數據插入生產數據表
INSERT INTO ...SELECT 語句會將數據從臨時表移至永久數據表。
當您設計 ETL 程式時，請嘗試在小型測試範例上執行程式。 請嘗試從數據表擷取 1,000 個數據列到檔案，將其移至 Azure，然後嘗試將它載入臨時表。
合作夥伴載入解決方案
我們的許多合作夥伴都有載入解決方案。 若要深入瞭解，請參閱我們的 解決方案合作夥伴清單。