นําเข้าและแปลงข้อมูลทะเลสาบด้วย OPENROWSET และ T-SQL

นําไปใช้กับ: < / c0 >< c1 / >คลังสินค้าใน Microsoft Fabric

โดยใช้ ฟังก์ชัน OPENROWSET คุณสามารถเปิดเผยไฟล์ Parquet, CSV หรือ JSONL เป็นแหล่งความสัมพันธ์สําหรับคําสั่งนําเข้า T-SQL ไฟล์เหล่านี้สามารถอยู่ใน OneLake, Azure Data Lake Storage หรือ Azure Blob Storage ตัวอย่างต่อไปนี้ดึงเนื้อหาของไฟล์ Parquet โดยใช้ URL ADLS แบบสมบูรณ์ของไฟล์นั้น:

SELECT *
FROM OPENROWSET(
    BULK 'https://<storage-account>.dfs.core.windows.net/<container>/<folder>/<file-name>.parquet'
) AS source;

เนื่องจาก OPENROWSET ส่งคืนข้อมูลเป็นชุดแถว คุณสามารถใช้เป็นแหล่งที่มาของคําสั่งนําเข้า T-SQL ที่รองรับได้ทุกคําสั่ง รวมถึง:

CREATE TABLE AS SELECT (CTAS) หรือ SELECT ... INTO สร้างตารางคลังสินค้าใหม่จากไฟล์อ้างอิงหนึ่งไฟล์หรือมากกว่า
INSERT ... SELECT เพื่อเพิ่มเนื้อหาไฟล์ลงในตารางที่มีอยู่
MERGE หากต้องการแทรกแถวใหม่ ให้อัปเดตแถวที่มีอยู่ หรือทั้งสองอย่าง โดยอิงจากข้อมูลในไฟล์ที่อ้างอิง

นอกจากการนําเข้าแล้ว คุณยังสามารถใช้การแปลง T-SQL ใดก็ได้ก่อนโหลดข้อมูลลงในตาราง ตัวอย่างเช่น คุณสามารถแปลงชนิดข้อมูล สร้างค่าคอลัมน์ใหม่ กรองแถว รวมข้อมูลไฟล์กับตารางที่มีอยู่ และทําการรวมข้อมูลเป็นส่วนหนึ่งของกระบวนการนําเข้า

Prerequisites

คลังข้อมูล Fabric และสิทธิ์ในการสร้างหรือแก้ไขตารางปลายทาง
เข้าถึงไฟล์ต้นทางที่ใช้ในการค้นหาคลังสินค้า

สร้างตารางด้วย CTAS

คําสั่ง CTAS ต่อไปนี้อ่านพาร์ติชัน Parquet รายปีและสร้างตารางข้อเท็จจริง นอกจากคอลัมน์ต้นทางทางกายภาพแล้ว ยังสร้างมาตรวัดทางธุรกิจและเก็บเส้นทางไฟล์ต้นทางสําหรับสายสืบทอด:

CREATE TABLE dbo.FactOnlineSales
AS
SELECT
    source.SalesOrderID, source.ProductID, CAST(source.OrderDateTime AS date) AS OrderDate,
    source.Quantity, CAST(source.Quantity * source.UnitPrice AS decimal(18, 2)) AS SalesAmount, CAST(source.filepath() AS varchar(1024)) AS SourceFilePath
FROM OPENROWSET(
    BULK 'https://<storage-account>.dfs.core.windows.net/<container>/sales/year=2026/*.parquet'
) WITH (
    SalesOrderID bigint,
    ProductID int,
    OrderDateTime datetime2(6),
    Quantity int,
    UnitPrice decimal(18, 2)
) AS source
WHERE source.Quantity > 0;

OrderDate และ SalesAmount คํานวณขณะโหลดแถว และเงื่อนไขปริมาณจะไม่รวมธุรกรรมที่ไม่ถูกต้อง filepath() เพิ่ม URI ต้นทางทั้งหมดลงในแต่ละแถวปลายทาง ซึ่งช่วยติดตามข้อเท็จจริงกลับไปยังไฟล์ที่ส่งมา

สร้างตารางที่มี SELECT INTO

คําสั่งต่อไปนี้สร้างตารางนําเข้าของลูกค้าจากไฟล์ CSV หลายไฟล์ มันทําความสะอาดค่าต้นทางและเพิ่มทั้งเมตาดาต้าไฟล์ต้นทางและข้อมูลเมตาขณะโหลด:

SELECT
    source.CustomerID, TRIM(source.CustomerName) AS CustomerName, source.Region, CAST(source.filepath() AS varchar(1024)) AS SourceFilePath,
    CAST(GETDATE() AS datetime2(6)) AS IngestionTime
INTO dbo.CustomerImport
FROM OPENROWSET(
    BULK 'https://<storage-account>.blob.core.windows.net/<container>/customers/*.csv',
    HEADER_ROW = TRUE
) AS source
WHERE source.CustomerID IS NOT NULL;

สัญลักษณ์แทนจะรวมไฟล์ CSV ที่ตรงกันทั้งหมดไว้ในแหล่งข้อมูลเชิงสัมพันธ์เดียว HEADER_ROW = TRUE อ่านชื่อคอลัมน์จากแถวแรกของแต่ละไฟล์ TRIM มาตรฐานชื่อลูกค้า filepath() บันทึกสายพันธุ์แถว และ GETDATE() บันทึกเมื่อคลังสินค้านําเข้าแถวนั้น เงื่อนไข null-key ป้องกันไม่ให้ระเบียนลูกค้าที่ไม่สามารถใช้งานได้เข้าสู่ตารางใหม่

เพิ่มชุดข้อมูลแบบเพิ่มทีละน้อยด้วย INSERT ...เลือก

ตัวอย่างต่อไปนี้โหลดพาร์ติชัน JSONL ที่เลือกลงในตารางที่มีอยู่ นอกจากคุณสมบัติการเรียงลําดับทางกายภาพแล้ว มันยังรับปีและเดือนที่เข้ารหัสในเส้นทางโฟลเดอร์ และบันทึกลําดับไฟล์และเวลาการนําเข้า:

INSERT INTO dbo.Sales
    (OrderID, CustomerID, OrderDate, Region, TotalAmount,
     OrderYear, OrderMonth, SourceFilePath, IngestionTime)
SELECT
    source.OrderID, source.CustomerID, source.OrderDate, source.Region, source.TotalAmount,
    CAST(source.filepath(1) AS smallint), CAST(source.filepath(2) AS smallint), source.filepath(), GETDATE()
FROM OPENROWSET(
    BULK 'https://<storage-account>.dfs.core.windows.net/<container>/orders/year=*/month=*/*.jsonl'
) AS source
WHERE source.filepath(1) = '2026'
  AND source.filepath(2) IN ('07', '08', '09');

ไวลด์การ์ดตัวแรกและตัวที่สองตรงกับyearmonthค่าของโฟลเดอร์และโฟลเดอร์ filepath(1)และfilepath(2)เปิดเผยค่าพาร์ทิชันเหล่านั้นเป็นข้อมูลตัวอักษร เพื่อให้คําสั่งค้นหาแปลงค่าเหล่านั้นsmallintก่อนเก็บเป็น OrderYear และOrderMonth filepath() ไม่มีอาร์กิวเมนต์จะเก็บเส้นทางต้นทางทั้งหมด ไวลด์การ์ดจะช่วยให้คิวรีสามารถนํากลับมาใช้ใหม่ได้ ขณะที่ตัวบ่งชี้จะเลือกชุดงานปัจจุบันและอนุญาตให้ OPENROWSET ข้ามโฟลเดอร์ที่ไม่ตรงกันได้

ซิงโครไนซ์ไฟล์ชุดกับ MERGE

ใช้ MERGE เมื่อไฟล์ชุดหนึ่งสามารถมีทั้งแถวใหม่และแถวที่เปลี่ยนแปลงแล้ว ตัวอย่างต่อไปนี้อ่านไฟล์ผลิตภัณฑ์ปัจจุบัน กําหนดค่าคําอธิบายมาตรฐาน และบันทึกเวลาการแก้ไขต้นทางเข้าไปในการจับคู่:

MERGE dbo.DimProductFromFiles AS target
USING (
    SELECT
        source.ProductID, TRIM(source.ProductName) AS ProductName,
        source.Category, source.ModifiedDate
    FROM OPENROWSET(
        BULK 'https://<storage-account>.dfs.core.windows.net/<container>/products/current/*.parquet'
    ) AS source
) AS source
ON target.ProductKey = source.ProductID
WHEN MATCHED AND source.ModifiedDate > target.ModifiedDate THEN
    UPDATE SET ProductName = source.ProductName, Category = source.Category,
        ModifiedDate = source.ModifiedDate
WHEN NOT MATCHED BY TARGET THEN
    INSERT (ProductKey, ProductName, Category, ModifiedDate)
    VALUES (source.ProductID, source.ProductName, source.Category, source.ModifiedDate);

คําค้นหาจะอัปเดตผลิตภัณฑ์เฉพาะเมื่อไฟล์มีเวอร์ชันใหม่กว่าและแทรกรหัสผลิตภัณฑ์ที่ไม่ได้อยู่ในมิติ ตรวจสอบให้แน่ใจว่าชุดไฟล์จะคืนค่าไม่เกินหนึ่งแถวสําหรับแต่ละ ProductID. เพิ่ม WHEN NOT MATCHED BY SOURCE THEN DELETE เฉพาะเมื่อเส้นทางแสดงถึงภาพสแนปช็อตที่เชื่อถือได้อย่างสมบูรณ์ ไม่ใช่ชุดข้อมูลที่เพิ่มขึ้นทีละน้อย

อ่านไฟล์จาก OneLake

ใช้ URL OneLake ฉบับสมบูรณ์สําหรับการค้นหาเป็นครั้งคราว ตัวอย่างนี้โหลดพาร์ติชันรายปีและเก็บ OneLake URI กับแต่ละเที่ยวแท็กซี่:

INSERT INTO dbo.TaxiTrips
    (TripID, PickupDateTime, TripDistance, TotalAmount, SourceFilePath)
SELECT
    source.TripID, source.PickupDateTime, source.TripDistance,
    source.TotalAmount, source.filepath()
FROM OPENROWSET(
    BULK 'https://onelake.dfs.fabric.microsoft.com/<workspace-id>/<lakehouse-id>/Files/year=2026/month=*/*.parquet'
) AS source;

URL ระบุพื้นที่ทํางาน บ้านพักริมทะเลสาบ /Files พื้นที่ และโฟลเดอร์ที่แบ่งพาร์ติชัน สัญลักษณ์แทนเดือนจะอ่านไฟล์รายเดือนทั้งหมดของปี 2026 ในขณะที่ filepath() ยังคงรักษาไฟล์ OneLake ที่สร้างแถวปลายทางแต่ละแถวไว้ สําหรับการสืบค้นที่นํากลับมาใช้ใหม่ ให้กําหนดแหล่งข้อมูลภายนอกที่รากของบ้านทะเลสาบ เพื่อให้ตัวระบุพื้นที่ทํางานและบ้านพักอยู่ในอ็อบเจ็กต์เดียวกัน:

CREATE EXTERNAL DATA SOURCE SalesFilesRoot
WITH (
    LOCATION = 'https://onelake.dfs.fabric.microsoft.com/<workspace-id>/<lakehouse-id>'
);

SalesFilesRoot เป็นวัตถุที่ถูกเก็บถาวรในคลังข้อมูลปัจจุบันและต้องได้รับสิทธิ์ในการสร้างแหล่งข้อมูลภายนอก มันกลายเป็นชื่อที่นํากลับมาใช้ใหม่สําหรับราก OneLake ดังนั้นการเปลี่ยนตําแหน่งสามารถทําได้ครั้งเดียวแทนที่จะทําในทุกคําสั่งนําเข้า

จากนั้นคุณสามารถใช้เส้นทางสัมพัทธ์โดยยังคงใช้สัญลักษณ์แทนและพฤติกรรมพาร์ติชันเหมือนเดิม:

CREATE TABLE dbo.TaxiTrips AS
SELECT *
FROM OPENROWSET(
    BULK '/Files/year=2026/month=*/*.parquet',
    DATA_SOURCE = 'SalesFilesRoot'
) AS source;

DATA_SOURCEรวมราก OneLake ที่เก็บไว้กับเส้นทางสัมพัทธ์/Files สิ่งนี้ช่วยให้คําสั่งนําเข้ามุ่งเน้นไปที่โฟลเดอร์ พาร์ติชัน และตรรกะการแปลง แทนที่จะซ้ําตัวระบุตําแหน่ง

เลือก OPENROWSET หรือ COPY

ใช้ OPENROWSET ร่วมกับคําสั่งนําเข้า T-SQL เมื่อโหลดประกอบด้วยการกรอง, การรวม, การคํานวณ, การสร้างสคีมา หรือการซิงโครไนซ์ สําหรับการโหลดจํานวนมากโดยตรงจากต้นทางไปยังปลายทางโดยไม่มีการแปลง ให้ใช้คําสั่ง COPY

การตรวจสอบการรับข้อมูล

คําสั่งนําเข้าไฟล์จะปรากฏร่วมกับคําขอ T-SQL อื่น ๆ ในประวัติ/กิจกรรมการสืบค้นคลังข้อมูล ใช้ ข้อมูลเชิงลึกของคําค้นหา เพื่อตรวจสอบรายละเอียดการดําเนินการ

คำติชม

หน้านี้มีประโยชน์หรือไม่

อัปเดตล่าสุดเมื่อ 2026-10-16