使用 Transact-SQL 將資料內嵌至倉儲

適用於✅:Microsoft Fabric 的倉儲

Transact-SQL 語言提供選項,您可以利用這些選項將資料從 Lakehouse 和倉儲中的現有資料表大規模載入至倉儲中的新資料表。 如果您需要使用彙總資料建立新版本的資料表、具有資料列子集的資料表版本,或建立資料表作為複雜查詢的結果,這些選項會很方便。 讓我們探索一些範例。

選擇一個 T-SQL 擷取陳述式

T-SQL 語言提供了豐富的語句集合,可以用來在資料倉儲中擷取和轉換資料。 你可以將資料載入新資料表或現有資料表、新增資料列、在資料匯入過程中轉換資料,或與現有資料同步變更。 Microsoft Fabric 中的倉儲支援以下 T-SQL 匯入語句:

  • CREATE TABLE AS SELECT(CTAS)語句允許您從 SELECT 語句的輸出在資料倉儲中建立新的表格。
  • SELECT INTO query 子句可讓您從任何數據表來源選取結果,並將結果重新導向至新的數據表。 這是 T-SQL 語言的標準功能。
  • INSERT ... SELECT 將查詢結果附加到目的資料表。 當你想將新批次或增量子集載入結構描述由你控制的資料表時,請使用此語句。
  • MERGE 比較來源與目標列,並有條件地插入、更新或刪除目標列。 當你需要同步新資料列和變更資料列,或將目的地與完整來源快照對照時,請使用此語句。

根據目標資料表是否已存在,以及你是否想要建立、附加或同步資料來選擇陳述式。 每個語句的來源都是 T-SQL 查詢。 你可以使用支援的表達式、函式、過濾器、連接和聚合,在擷取過程中塑造資料。 你可以使用以下資料來源來讀取 T-SQL 語句中的資料:

  • 讀取資料倉儲表,例如暫存表。
  • 在 Lakehouse 的 SQL 分析端點中使用自動產生的數據表讀取 Lakehouse Delta Lake 資料夾。
  • 使用 OPENROWSET 函式直接從 Azure Data Lake 或 Azure Blob 記憶體讀取 CSV、Parquet 或 JSONL 檔案。

使用 CTAS 建立表格

CTAS 會一次操作建立目的資料表並載入查詢結果。 第一個範例展示了如何建立一個新資料表,該資料表是現有 dbo.TaxiTrips 資料表的複製品,但篩選出只包含 2023 年的資料:

CREATE TABLE dbo.TaxiTrips_2023
AS
SELECT * 
FROM dbo.TaxiTrips 
WHERE DATEPART(YEAR, tpep_pickup_datetime) = '2023';

使用 CTAS 從查詢結果建立並填入新的資料表。 當目的資料表不存在,或你想從一個或多個來源具體化轉換或合併的資料時,此方法非常有用。

使用 SELECT INTO 建立表格

SELECT ... INTO 也會從查詢結果建立目的表:

SELECT * 
INTO dbo.TaxiTrips_2023
FROM MyLakehouse.dbo.TaxiTrips 
WHERE DATEPART(YEAR, tpep_pickup_datetime) = '2023';

CTAS 與 SELECT ... INTO 都會從查詢結果推斷目的欄位。 當目標節點需要特定資料型態、精確度或長度時,將運算式明確轉型。

使用 INSERT ... SELECT 來新增資料

INSERT ... SELECT 將查詢結果載入現有資料表。 你可以透過轉換來自外部公開 Parquet 檔案的資料,將資料附加到表格上:

INSERT INTO dbo.<table name>
SELECT *
FROM OPENROWSET(BULK 'https://<storage account>.blob.core.windows.net/public/<subfolder>/<file name>.parquet') AS data;

使用 MERGE 來同步資料

MERGE 透過穩定鍵匹配來源與目的地列。 以下模式假設 dbo.DimCustomer 已存在,且具有該陳述所使用的欄位。 確保來源對每個目的索引鍵傳回的資料列不超過一列。

MERGE dbo.DimCustomer AS target
USING staging.Customers AS source
ON target.CustomerID = source.CustomerID
WHEN MATCHED AND source.ModifiedDate > target.ModifiedDate THEN
    UPDATE SET CustomerName = source.CustomerName, City = source.City,
        ModifiedDate = source.ModifiedDate
WHEN NOT MATCHED BY TARGET THEN
    INSERT (CustomerID, CustomerName, City, ModifiedDate)
    VALUES (source.CustomerID, source.CustomerName, source.City, source.ModifiedDate);

僅在來源查詢代表在範圍內的資料列之完整權威資料集時使用 WHEN NOT MATCHED BY SOURCE THEN DELETE。 不要在增量來源批次中使用那個子句,因為目的地中未出現在該批次中的資料列可能會被刪除。

選擇資料來源

這些資料擷取語句在支援的來源類型間運作一致。 只有來源參照會變更。

湖中的檔案

用OPENROWSET來讀取 OneLake、Azure Data Lake Storage 或 Azure Blob 儲存體 中的 Parquet、CSV 或 JSONL 檔案。 你可以鎖定一個檔案、多個使用萬用字元的檔案,或分割的資料夾。

關於檔案路徑、結構推論、分割區過濾、元資料函式,以及四個攝取語句的範例,請參見 使用 OPENROWSET 和 T-SQL 進行湖資料的匯入與轉換。

Lakehouse 中的 Delta 資料表

Lakehouse 的 SQL 分析端點會公開其 /Tables 區域中的 Delta 資料表。 在同一工作空間的倉庫中,使用三部分名稱 <lakehouse>.<schema>.<table>來參考資料表。 這不局限於實體存放在 Lakehouse 中的 Delta 資料表。 在該 /Tables 區域中可透過 OneLake 捷徑存取的資料表,也可以參照儲存在其他位置的資料,包括 lakehouse /Files 區域、外部儲存體(例如 ADLS 或 Amazon S3)、鏡像資料,以及其他工作區中的 OneLake 資料。 透過此架構,您可以透過 lakehouse SQL 分析端點查詢捷徑支援的資料,而無需將資料複製到 lakehouse 中。

關於資料表建立、增量載入與同步範例,請參見 使用 T-SQL 的 Ingest Delta Lake 資料表。

目前倉庫中的資料表

使用兩部分名稱,例如 <schema>.<table>,從目前倉庫中的資料表匯入資料。

關於從銅到銀再到金的範例,這些例子能在一個倉庫內聯結、聚合並逐步載入資料表,請參考 使用 T-SQL 從倉庫資料表和鏡像資料庫匯入。

鏡像資料庫及其他工作區項目

若要從鏡像資料庫、其他倉儲或同一工作區中支援的其他項目擷取資料,請將該項目新增至總管,並使用三部分名稱 <item>.<schema>.<table>。 若要深入了解跨倉儲查詢,請參閱撰寫跨資料庫 SQL 查詢。 關於使用兩種命名模式的範例,請參見 T-SQL 的「從倉庫資料表與鏡像資料庫擷取」。

審核與監控 T-SQL 的匯入

CTAS、SELECT ... INTO、INSERT ... SELECT 和 MERGE 作業會出現在 倉庫查詢歷史記錄/活動中。 利用 query insights 檢視執行細節並排解擷取查詢問題。

資料擷取選項

當載入包含過濾、型別轉換、連接、計算、聚合或同步時,請使用 T-SQL 匯入。 在以下情況下選擇其他選項:其他將資料匯入倉庫的方式包括: