สอบถามไฟล์ Data Lake ภายนอก

นําไปใช้กับ:✅ จุดสิ้นสุดการวิเคราะห์ SQL และ Warehouse ใน Microsoft Fabric

Fabric Data Warehouse และตําแหน่งข้อมูลการวิเคราะห์ SQL ช่วยให้คุณสามารถสืบค้นข้อมูลที่จัดเก็บไว้ในไฟล์ภายใน Data Lake โดยใช้โค้ด Transact-SQL (T-SQL)

มีพื้นที่ผิวการสืบค้น T-SQL ที่คุ้นเคยซึ่งรองรับการสืบค้นข้อมูลที่มีโครงสร้าง กึ่งโครงสร้าง และไม่มีโครงสร้าง ใน Fabric Data Warehouse และตําแหน่งข้อมูลการวิเคราะห์ SQL คุณสามารถสืบค้นรูปแบบไฟล์ต่างๆ เช่น Parquet, CSV และ JSONL

OPENROWSET

ไวยากรณ์แบบง่ายของ OPENROWSET ฟังก์ชันคือ:

OPENROWSET( BULK '{file path}', [ options...] )
   [ WITH ( {result set column schema} ) ]

OPENROWSET รองรับสามวิธีในการระบุตําแหน่งไฟล์:

• เส้นทางแบบสัมบูรณ์ ที่มี URL แบบเต็มไปยังไฟล์ที่คุณต้องการอ่าน นี่เป็นวิธีที่ง่ายที่สุดเมื่อคุณทราบตําแหน่งไฟล์ที่แน่นอน
• เส้นทางสัมพัทธ์กับแหล่งข้อมูล - เส้นทางสัมพัทธ์จะถูกผนวกเข้ากับตําแหน่งรากที่กําหนดไว้ในแหล่งข้อมูลเพื่อสร้างเส้นทางแบบเต็ม แหล่งข้อมูลสามารถชี้ไปที่ URL ที่เก็บข้อมูลภายนอก (ตัวอย่างเช่น Azure Blob, ADLS Gen2) หรือ URL รากของ Lakehouse ใน Fabric
• เส้นทางสัมพัทธ์ที่ขึ้นต้นด้วย /Files - ตัวเลือกนี้ใช้ได้เฉพาะเมื่อสืบค้น Lakehouse ผ่านปลายทางการวิเคราะห์ SQL เท่านั้น /Filesโฟลเดอร์แสดงถึงพื้นที่ไฟล์ Lakehouse และคุณสามารถใช้เส้นทางสัมพัทธ์ได้โดยไม่ต้องกําหนดแหล่งข้อมูล

ไฟล์สามารถวางไว้ในตัวเลือกที่เก็บข้อมูลอย่างใดอย่างหนึ่งต่อไปนี้:

• Azure Data Lake Storage (ADLS) - บริการที่เก็บข้อมูลบนคลาวด์แบบลําดับชั้นที่ปรับขนาดได้ซึ่งปรับให้เหมาะสมสําหรับปริมาณงานการวิเคราะห์ข้อมูลขนาดใหญ่
• Azure Blob Storage - บริการจัดเก็บอ็อบเจ็กต์เอนกประสงค์สําหรับจัดเก็บข้อมูลที่ไม่มีโครงสร้างจํานวนมาก เช่น ไฟล์ รูปภาพ และบันทึก
• Fabric OneLake - ที่เก็บข้อมูลทะเลสาบดั้งเดิมสําหรับ Microsoft Fabric ที่ให้ที่จัดเก็บข้อมูลดิบเชิงตรรกะแบบครบวงจรสําหรับปริมาณงาน Fabric ทั้งหมด OneLake ยังช่วยให้สามารถเข้าถึงข้อมูลที่จัดเก็บไว้ในตําแหน่งภายนอกทางอ้อม ซึ่งรวมถึง:
  • Amazon S3 - บริการพื้นที่จัดเก็บอ็อบเจ็กต์ที่ให้บริการโดย Amazon Web Services
  • Google Cloud Storage (GCS) - บริการพื้นที่เก็บข้อมูลอ็อบเจ็กต์สําหรับ Google Cloud Platform
  • SharePoint - บริการจัดเก็บเอกสารและไฟล์สําหรับการทํางานร่วมกันใน Microsoft 365
  • OneDrive - บริการที่เก็บข้อมูลบนคลาวด์ส่วนบุคคลสําหรับไฟล์ใน Microsoft 365

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

ตัวเลือก OPENROWSET ที่เกี่ยวข้อง

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

ตัวเลือกทั่วไปได้แก่:

• FIELDTERMINATOR, , ROWTERMINATORและ FIELDQUOTE - ระบุอักขระที่แยกเขตข้อมูลและแถว ตัวเลือกเหล่านี้ช่วยให้มั่นใจได้ว่าการแยกวิเคราะห์คอลัมน์และเรกคอร์ดถูกต้อง นอกจากนี้ยังจัดการค่าที่อ้างในไฟล์ที่คั่นเพื่อรักษาข้อความที่มีตัวคั่น
• HEADER_ROW และ FIRSTROW - ระบุว่าไฟล์มีแถวส่วนหัวหรือไม่ และกําหนดว่าแถวใดควรถือว่าเป็นแถวข้อมูลแรก
• CODEPAGE - ตั้งค่าการเข้ารหัสอักขระเพื่อตีความอักขระพิเศษและข้อความที่ไม่ใช่ ASCII อย่างถูกต้อง

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

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

กรณีการใช้งานสําหรับ OPENROWSET

เพื่อมอบประสบการณ์การคิวรีแบบแทนที่ที่ราบรื่นสําหรับข้อมูลที่จัดเก็บไว้ใน Data Lake Fabric Data Warehouse และปลายทางการวิเคราะห์ SQL จะใช้ฟังก์ชัน OPENROWSET เพื่ออ้างอิงไฟล์และอ่านเนื้อหา

ฟังก์ชันนี้มี OPENROWSET ความสามารถมากมายสําหรับการสืบค้นไฟล์ รวมถึง:

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

สืบค้นไฟล์ PARQUET ด้วย OPENROWSET

Parquet เป็นรูปแบบไฟล์แบบคอลัมน์ที่ปรับให้เหมาะกับปริมาณงานการวิเคราะห์ จัดเก็บข้อมูลตามคอลัมน์แทนที่จะเป็นแถว ซึ่งช่วยให้การบีบอัดมีประสิทธิภาพ ลด I/O และประสิทธิภาพการสืบค้นที่เร็วขึ้น โดยเฉพาะอย่างยิ่งเมื่อสืบค้นชุดย่อยของคอลัมน์

ฟังก์ชันนี้ OPENROWSET ช่วยให้เข้าถึงไฟล์ Parquet ได้ง่ายและใช้งานง่ายโดยตรงจากโค้ด T-SQL

เมื่อต้องการสืบค้นไฟล์ Parquet ให้ระบุ URL ไปยังไฟล์ Parquet โดยใช้ OPENROWSET ฟังก์ชัน:

SELECT * FROM
OPENROWSET( BULK 'https://myaccount.dfs.core.windows.net/mycontainer/mysubfolder/data.parquet');

ฟังก์ชันส่งคืน OPENROWSET ทุกแถวจากไฟล์ Parquet เป็นแถวในชุดผลลัพธ์ คอลัมน์ในชุดผลลัพธ์ตรงกับ Schema ของไฟล์ Parquet

สําหรับตัวอย่างการใช้งาน โปรดดู คิวรีไฟล์ Parquet

สืบค้นไฟล์ที่คั่นด้วย OPENROWSET

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

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

ไฟล์ที่คั่นได้รับการสนับสนุนอย่างกว้างขวางและง่ายต่อการสร้างในระบบและเครื่องมือต่างๆ

รูปแบบตัวคั่นที่ใช้บ่อยที่สุดคือรูปแบบค่าที่คั่นด้วยจุลภาค (CSV) ใน CSV แถวจะถูกคั่นด้วยบรรทัดใหม่ และค่าด้วยเครื่องหมายจุลภาคที่ล้อมรอบด้วยเครื่องหมายคําพูดคู่ อย่างไรก็ตาม มีหลายรูปแบบ เช่น ค่าที่คั่นด้วยแท็บ (TSV) และรูปแบบตัวคั่นแบบกําหนดเองอื่นๆ

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

เมื่อต้องการสืบค้นไฟล์ที่คั่น ให้ระบุ URL ของไฟล์และกําหนดตัวเลือกการแยกวิเคราะห์ที่เหมาะสมเมื่อใช้ OPENROWSET ฟังก์ชัน:

SELECT * FROM
OPENROWSET( BULK '/Files/mysubfolder/data.csv');

ฟังก์ชันจะ OPENROWSET ส่งคืนทุกแถวจากไฟล์ที่คั่นเป็นแถวในชุดผลลัพธ์ คอลัมน์ในชุดผลลัพธ์เป็นไปตามโครงสร้างของไฟล์ที่คั่น

คุณสามารถปรับแต่งวิธีการแยกวิเคราะห์ไฟล์ข้อความที่คั่น (เช่น CSV, TSV หรือตัวแปรอื่นๆ) ได้โดยการระบุตัวเลือก เช่น ตัวสิ้นสุดฟิลด์ ตัวสิ้นสุดแถว อักขระ Escape และการตั้งค่าอื่นๆ ที่เกี่ยวข้องกับรูปแบบเพื่อให้ตรงกับโครงสร้างไฟล์ของคุณ

สําหรับตัวอย่างการใช้งาน โปรดดู คิวรีไฟล์ข้อความที่คั่น

สืบค้นไฟล์ JSONL ด้วย OPENROWSET

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

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

หากต้องการสืบค้นไฟล์ JSONL ให้ระบุ URL ของไฟล์เมื่อใช้ฟังก์ชัน:OPENROWSET

SELECT * FROM
OPENROWSET( BULK '/mysubfolder/data.jsonl', DATA_SOURCE='MyStorage');

เมื่อสืบค้นไฟล์ JSON Lines (JSONL) ออบเจ็กต์ JSON แต่ละรายการในไฟล์จะถือว่าเป็นแถวแยกต่างหากในชุดผลลัพธ์

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

สําหรับตัวอย่างการใช้งาน โปรดดู คิวรีไฟล์ JSONL

สคีมาขณะอ่าน

Fabric Data Warehouse ช่วยให้นักพัฒนา SQL สามารถใช้ Schema ในเวลาคิวรีเมื่ออ่านข้อมูลโดยตรงจากไฟล์ที่จัดเก็บไว้ใน Data Lake

วิธีการ Schema On-Read นี้ช่วยให้ข้อมูลยังคงอยู่ในรูปแบบเดิมในขณะที่โครงสร้างถูกกําหนดแบบไดนามิกระหว่างการสืบค้น คุณสามารถเลือกระหว่างรูปแบบ Schema On-Read ได้สองแบบ:

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

การอนุมานสคีมาอัตโนมัติ

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

Fabric Data Warehouse และตําแหน่งข้อมูลการวิเคราะห์ SQL จะตรวจสอบไฟล์ต้นฉบับโดยอัตโนมัติเพื่อระบุชื่อคอลัมน์และชนิดข้อมูล พวกเขาใช้ข้อมูลเมตาระดับไฟล์ เช่น ส่วนหัวของไฟล์ Parquet หรือวิเคราะห์ตัวอย่างข้อมูลที่เป็นตัวแทนสําหรับรูปแบบ เช่น CSV และ JSONL

เมื่อคุณละเว้นส่วนคําสั่งออกจากWITHOPENROWSETคําสั่ง Fabric Data Warehouse จะวิเคราะห์ไฟล์ต้นแบบโดยอัตโนมัติ และรับชื่อคอลัมน์และชนิดข้อมูล ณ เวลาคิวรี

SELECT * FROM
OPENROWSET( BULK 'https://myaccount.dfs.core.windows.net/mycontainer/mysubfolder/data.parquet');

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

คําจํากัดความ Schema ที่ชัดเจน

ด้วยข้อกําหนด Schema ที่ชัดเจน นักพัฒนา SQL จะควบคุมวิธีการแมปข้อมูลไฟล์กับคอลัมน์เชิงสัมพันธ์โดยการระบุชื่อคอลัมน์ ชนิดข้อมูล และตําแหน่งคอลัมน์ภายในไฟล์ต้นฉบับ (หากมี)

วิธีการนี้ให้การแมปที่แม่นยําและคาดเดาได้เมื่อสืบค้นไฟล์โดยใช้OPENROWSET

เมื่อต้องการกําหนด Schema ให้เพิ่มส่วนคําสั่งเพิ่มเติมWITHในใบแจ้งยอดของคุณOPENROWSET

SELECT * FROM
OPENROWSET( BULK '/Files/mysubfolder/data.parquet') 
WITH (
      Column1 int, 
      Column2 varchar(20),
      Column3 varchar(max)
);

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

สืบค้นไฟล์หรือโฟลเดอร์หลายไฟล์ด้วย OPENROWSET

เมื่อต้องการสอบถามข้อมูลในหลายแฟ้มหรือหลายโฟลเดอร์ ให้ระบุเส้นทางของแฟ้มที่มีอักขระตัวแทน (*) อย่างน้อยหนึ่งตัว

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

กฎต่อไปนี้ใช้เมื่อใช้สัญลักษณ์ตัวแทนในเส้นทางไฟล์:

• *สัญลักษณ์ตัวแทนแสดงถึงอักขระหนึ่งตัวหรือหลายตัว และคุณสามารถใช้ในเส้นทางไดเรกทอรีและในชื่อไฟล์ได้
• คุณสามารถระบุสัญลักษณ์ตัวแทนหลายตัว * ภายในเส้นทางหรือชื่อไฟล์เดียวกันเพื่อให้ตรงกับรูปแบบที่ซับซ้อน
• เมื่อเส้นทางลงท้ายด้วยอักขระตัวแทนแบบเรียกซ้ํา (ตัวอย่างเช่น /**) ชุดผลลัพธ์แบบสอบถามจะรวมไฟล์ทั้งหมดที่อยู่ภายใต้โฟลเดอร์รากที่ระบุและโฟลเดอร์ย่อย

ตัวอย่างต่อไปนี้สาธิตวิธีการใช้รูปแบบสัญลักษณ์ตัวแทนในเส้นทางแฟ้มเพื่อสอบถามแฟ้ม Parquet หลายแฟ้มในโฟลเดอร์ในแบบสอบถามเดียว:

SELECT * FROM
OPENROWSET( BULK '/myroot/*/mysubfolder/*.parquet', DATA_SOURCE='MyStorage');

ฟังก์ชันข้อมูลเมตาของไฟล์

OPENROWSET มีฟังก์ชันเมตาดาต้าที่คุณสามารถใช้เพื่อเข้าถึงข้อมูลเกี่ยวกับไฟล์ที่คุณกําลังสืบค้น:

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

ตัวอย่างต่อไปนี้สาธิตการสืบค้นที่ดึงข้อมูลเนื้อหาไฟล์พร้อมกับ URI แบบเต็มและชื่อไฟล์สําหรับแต่ละไฟล์ โดยส่งคืนเฉพาะไฟล์จาก /year=2025/month=10 โฟลเดอร์

SELECT 
 rows.filepath(),
 rows.filename(),
 rows.filepath(2) AS [month],
 rows.*
FROM 
OPENROWSET(
  BULK 'https://myaccount.dfs.core.windows.net/myroot/year=2025/month=*/*.parquet'
  ) AS rows
WHERE rows.filepath(1) = '2025'

สําหรับข้อมูลเพิ่มเติมเกี่ยวกับ filepath() และ filename()ให้ดูที่ ฟังก์ชันเมตาดาต้าของไฟล์

เนื้อหาที่เกี่ยวข้อง

• คิวรีคลังสินค้าหรือจุดสิ้นสุดการวิเคราะห์ SQL
• คิวรีไฟล์ JSON
• สอบถามไฟล์ Parquet
• คิวรีไฟล์ที่คั่น