Azure Synapse Analytics 中專用 SQL 集區的資料載入策略
傳統 SMP 專用 SQL 集區會使用擷取、轉換和載入 (ETL) 程序來載入資料。 Azure Synapse Analytics 中的 Synapse SQL 使用分散式查詢處理架構，可利用計算和儲存體資源的延展性和彈性。
使用擷取、載入及轉換 (ELT) 程序可以利用內建分散式查詢處理功能，而且不需要載入之前轉換資料的資源。
雖然專用 SQL 集區支援許多載入方法，包括熱門的 SQL Server 選項 (例如 bcp 和 SqlBulkCopy API)，但是載入資料最快速且最能調整的方式是透過 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 檔案，會從 Hadoop 檔案格式 (例如 ORC 和 Parquet) 載入。 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 資料類型 |
|---|---|---|
| BOOLEAN | bit | |
| BINARY/BYTE_ARRAY | varbinary | |
| DOUBLE | FLOAT | |
| FLOAT | real | |
| INT32 | int | |
| INT64 | BIGINT | |
| INT96 | datetime2 | |
| FIXED_LEN_BYTE_ARRAY | BINARY | |
| BINARY | UTF8 | NVARCHAR |
| BINARY | STRING | NVARCHAR |
| BINARY | ENUM | NVARCHAR |
| BINARY | UUID | UNIQUEIDENTIFIER |
| BINARY | DECIMAL | decimal |
| BINARY | JSON | nvarchar(MAX) |
| BINARY | 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 |
| INT32 | DECIMAL | decimal |
| INT32 | TIME (MILLIS) | time |
| INT64 | INT(64, true) | BIGINT |
| INT64 | INT(64, false) | decimal(20,0) |
| INT64 | DECIMAL | decimal |
| INT64 | TIME (MILLIS) | time |
| INT64 | TIMESTAMP (MILLIS) | datetime2 |
| 複雜類型 | 清單 | 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 未提供的額外載入功能。 若要執行範例教學課程，請參閱紐約州計程車 COPY 教學課程。
- 具有 T-SQL 的 PolyBase 需要您定義外部資料物件。
- 搭配使用 PolyBase 和 COPY 陳述式與 Azure Data Factory (ADF) 是另一個協調工具。 它會定義管線並排程作業。
- 具有 SSIS 的 PolyBase 會在您的來源資料位於 SQL Server 時正常運作。 SSIS 會定義來源至目的地資料表對應，也會協調載入。 如果您已經有 SSIS 套件，您可以將套件修改為搭配新的資料倉儲目的地。
- 搭配使用 PolyBase 與 Azure DataBricks，可以使用 PolyBase 將資料從資料表移轉到 Databricks 資料框架和/或將資料從 Databricks 資料框架寫入至資料表。
其他載入選項
除了 PolyBase 和 COPY 陳述式以外，您還可以使用 bcp 或 SqlBulkCopy API。 bcp 會直接載入至資料庫而不需要透過 Azure Blob 儲存體，僅適用於小型載入。
注意
這些選項的載入效能會顯著低於 PolyBase 和 COPY 陳述式。
5.轉換資料
當資料在暫存表格時，執行您的工作負載需要的轉換。 然後將資料移至生產資料表中。
6.將資料插入生產資料表
INSERT INTO ...SELECT 陳述式會從暫存表格將資料移至永久資料表。
當您設計 ETL 程序時，嘗試在小型測試範例上執行程序。 嘗試從資料表將 1000 個資料列擷取至檔案，將它移至 Azure，然後嘗試將它載入暫存表格。
合作夥伴載入解決方案
我們有許多合作夥伴皆提供載入解決方案。 若要深入了解，請參閱我們的解決方案合作夥伴清單。
後續步驟
如需載入指導，請參閱資料載入最佳做法。