Thep Excel
Sql.Databaseเชื่อมต่อฐานข้อมูล SQL Server ใน Power Query
Sql.Database เป็นฟังก์ชันที่ใช้เชื่อมต่อกับฐานข้อมูล SQL Server แล้วดึงข้อมูลเข้ามาประมวลผลใน Power Query ได้เลยครับ
.
ฟังก์ชันนี้จะคืนค่าออกมาเป็น table ที่แสดงรายการของ tables, views และ stored functions ทั้งหมดที่มีในฐานข้อมูลที่เราระบุ ที่เจ๋งคือฟังก์ชันนี้รองรับการทำ query folding ซึ่งทำให้การประมวลผลข้อมูลเกิดขึ้นที่ SQL Server โดยตรง ได้ประสิทธิภาพสูงสุดและลดการใช้ memory ลงมาก
.
นอกจากนี้ยังมี options หลากหลายให้เราปรับแต่งการเชื่อมต่อได้ เช่น การกำหนด CommandTimeout สำหรับ query ที่ใช้เวลานาน การเขียน native SQL query เพื่อดึงข้อมูลแบบกำหนดเองเลย การทำ cross-database folding เพื่อทำงานข้ามหลายฐานข้อมูล และรองรับ authentication หลายรูปแบบด้วย ส่วนตัวผมใช้บ่อยมากเวลาทำงานกับข้อมูลขนาดใหญ่จาก SQL Server ครับ 😎
Syntax & Arguments
| อาร์กิวเมนต์ | ชนิด | ค่าเริ่มต้น | คำอธิบาย |
|---|---|---|---|
| server | text | ชื่อหรือ hostname ของ SQL Server ที่ต้องการเชื่อมต่อ สามารถระบุ port ได้โดยใช้ colon หรือ comma คั่น เช่น “localhost” หรือ “server.com:1433” | |
| database | text | ชื่อของฐานข้อมูลที่ต้องการเข้าถึงใน SQL Server เช่น “AdventureWorks” หรือ “Northwind” | |
| [options]ไม่บังคับ | nullable record | null | Record ที่ใช้สำหรับกำหนดตัวเลือกเพิ่มเติมในการควบคุมการเชื่อมต่อและการทำงานของฟังก์ชัน เช่น Query สำหรับเขียน native SQL query โดยตรง, CommandTimeout สำหรับกำหนดระยะเวลา timeout ของการทำงาน, HierarchicalNavigation เพื่อให้แสดง tables จัดกลุ่มตาม schema name, EnableCrossDatabaseFolding เพื่ออนุญาตให้ทำ query folding ข้ามหลาย database ได้, MaxDegreeOfParallelism สำหรับการทำ parallel processing และตัวเลือกอื่นๆ อีกมากมาย |
[ ] = อาร์กิวเมนต์ที่ไม่บังคับ
Additional Notes
Sql.Database เป็น Power Query function ที่ใช้เชื่อมต่อกับ SQL Server แล้วดึงข้อมูลจาก tables, views และ stored functions มาใช้งานได้เลยครับ
.
ที่เจ๋งคือมันรองรับ query folding ทำให้การประมวลผลเกิดขึ้นที่ SQL Server โดยตรง ไม่ต้องดึงข้อมูลทั้งหมดมาที่เครื่องเรา ได้ทั้งความเร็วและประหยัด memory มากครับ
.
ส่วนตัวผมใช้บ่อยมากเวลาทำงานกับข้อมูลขนาดใหญ่ เพราะมี options ให้ปรับแต่งได้หลากหลาย ทั้ง CommandTimeout, native SQL query และ cross-database folding ครับ 😎
How it works
การเชื่อมต่อ SQL Server ในองค์กร
ใช้สำหรับการเชื่อมต่อกับ SQL Server ภายในองค์กรเพื่อดึงข้อมูลจาก tables และ views มาทำการวิเคราะห์และสร้างรายงาน โดยใช้ประโยชน์จากคุณสมบัติ query folding เพื่อให้การประมวลผลเกิดขึ้นที่ฐานข้อมูลโดยตรง ทำให้ได้ประสิทธิภาพสูงสุดและลดภาระของเครื่อง client
การใช้ native SQL query
ดึงข้อมูลโดยการเขียน SQL query เองแทนการเลือกจาก table โดยตรง ทำให้สามารถใช้ประโยชน์จากความสามารถของ SQL อย่างเต็มที่ เช่น การทำ JOIN เพื่อรวมหลาย table, การใช้ WHERE clause เพื่อกรองข้อมูล, การทำ GROUP BY สำหรับการจัดกลุ่มและสรุปข้อมูล และ SQL features อื่นๆ ได้ทันทีก่อนที่ข้อมูลจะถูกส่งเข้ามาใน Power Query
การจัดการ timeout และ performance
ใช้สำหรับการควบคุมและปรับแต่งประสิทธิภาพการทำงาน โดยการกำหนด CommandTimeout เพื่อให้รองรับ query ที่ใช้เวลาในการประมวลผลนานขึ้น การตั้งค่า MaxDegreeOfParallelism เพื่อให้สามารถทำ parallel processing ได้หลาย thread พร้อมกัน และการเปิดใช้งาน EnableCrossDatabaseFolding เพื่อให้สามารถทำงานข้ามหลาย database บน server เดียวกันได้อย่างมีประสิทธิภาพและรวดเร็ว
Examples
ตัวอย่างที่ 1: การเชื่อมต่อพื้นฐานlet Source = Sql.Database("localhost", "AdventureWorks"), SalesTable = Source{[Schema="Sales",Item="Customer"]}[Data], FilteredRows = Table.SelectRows(SalesTabl…
เราเชื่อมต่อกับ SQL Server ที่ชื่อ localhost แล้วเลือกใช้งานฐานข้อมูล AdventureWorks จากนั้นเข้าถึง Customer table ที่อยู่ใน Sales schema แล้วกรองข้อมูลเอาเฉพาะลูกค้าที่มี CustomerID มากกว่า 100
ที่เจ๋งคือตอนเราใช้ Table.SelectRows มันจะเกิด query folding กลับไปที่ SQL Server เลย ดังนั้นการกรองจะเกิดที่ฐานข้อมูลโดยตรง ไม่ต้องดึงข้อมูลทั้งหมดมาก่อนครับ
ส่วนตัวผมชอบวิธีนี้มาก เพราะได้ประสิทธิภาพสูงสุดและดึงเฉพาะข้อมูลที่ต้องการมาเท่านั้นครับ 😎
ตัวอย่างที่ 2: การใช้ native SQL querylet Source = Sql.Database( "sqlserver.company.com", "SalesDB", [ Query = " SELECT c.CustomerName, SUM(o.Amount) as TotalSales FROM Customers c INNER JOIN Orders…
ตัวอย่างนี้เราใช้ native SQL query เพื่อดึงข้อมูลโดยตรงเลยครับ ทำให้เราสามารถใช้ความสามารถของ SQL ได้อย่างเต็มรูปแบบ
เราทำการ JOIN ข้อมูลจากหลาย table ใช้ WHERE clause กรองข้อมูลตามเงื่อนไข ทำ GROUP BY เพื่อจัดกลุ่มและสรุปข้อมูล แล้วใช้ HAVING กรองผลลัพธ์หลังจากจัดกลุ่ม query ทั้งหมดจะถูกประมวลผลบน SQL Server ก่อนที่จะส่งผลลัพธ์สุดท้ายมาที่ Power Query
เอาจริงๆ วิธีนี้ช่วยลดปริมาณข้อมูลที่ต้องถ่ายโอนผ่าน network และเพิ่มความเร็วในการประมวลผลได้เยอะมาก ส่วนตัวผมใช้แบบนี้กับ query ที่ซับซ้อนเสมอครับ 💡
ตัวอย่างที่ 3: การตั้งค่า CommandTimeout และ HierarchicalNavigationlet Source = Sql.Database( "bigdata-server.com:1433", "WarehouseDB", [ CommandTimeout = #duration(0, 0, 30, 0), HierarchicalNavigation = true, CreateNavigationP…
ตัวอย่างนี้เราตั้งค่า options ต่างๆ เพื่อควบคุมพฤติกรรมการเชื่อมต่อครับ CommandTimeout ถูกกำหนดเป็น 30 นาทีสำหรับรองรับ query ที่ใช้เวลาประมวลผลนานๆ
ส่วน HierarchicalNavigation ตั้งเป็น true จะทำให้ tables แสดงผลแบบจัดกลุ่มตาม schema name ที่ต้องระวังคือถ้าฐานข้อมูลมีหลายร้อย table การจัดกลุ่มแบบนี้จะช่วยให้หาได้ง่ายขึ้นเยอะครับ
นอกจากนี้ CreateNavigationProperties ที่ตั้งเป็น true จะสร้าง navigation properties ขึ้นมา ทำให้การเข้าถึงข้อมูลสะดวกและรวดเร็วขึ้นมากครับ 😎
ตัวอย่างที่ 4: การใช้ EnableCrossDatabaseFolding และ MaxDegreeOfParallelismlet Source = Sql.Database( "analytics.server.com", "MainDB", [ EnableCrossDatabaseFolding = true, MaxDegreeOfParallelism = 4, ConnectionTimeout = #duration(0, 0…
ตัวอย่างนี้แสดงการใช้งานระดับขั้นสูงสำหรับเพิ่มประสิทธิภาพครับ การตั้งค่า EnableCrossDatabaseFolding เป็น true จะอนุญาตให้ระบบทำ query folding ข้ามหลาย database ที่อยู่บน SQL Server เดียวกันได้ ทำให้การ join ข้าม database มีประสิทธิภาพสูงมาก
ส่วน MaxDegreeOfParallelism ตั้งเป็น 4 จะบอกให้ SQL Server ใช้ parallel processing สูงสุด 4 threads พร้อมกันในการประมวลผล query นอกจากนี้ ConnectionTimeout ถูกตั้งเป็น 2 นาทีสำหรับกำหนดระยะเวลาสูงสุดในการรอการเชื่อมต่อ
เอาจริงๆ การตั้งค่าต่างๆ เหล่านี้ช่วยเพิ่มประสิทธิภาพได้มากเวลาทำงานกับข้อมูลขนาดใหญ่และ complex queries ครับ ส่วนตัวผมใช้แบบนี้กับ data warehouse เสมอเลย 💡
FAQs
Sql.Database แตกต่างจาก Sql.Databases อย่างไร+
Sql.Database ใช้เชื่อมต่อกับ database เดียวที่ระบุชื่อชัดเจน ส่วน Sql.Databases ใช้เรียกดูรายการ databases ทั้งหมดบน SQL Server นั้น
ส่วนตัวผมใช้ Sql.Database เวลาดึงข้อมูลจริง ส่วน Sql.Databases ใช้เวลาอยากรู้ว่ามี database อะไรบ้างบน server ครับ
Query folding ใน Sql.Database คืออะไรและทำไมถึงสำคัญ+
Query folding คือการที่ Power Query แปลง transformation steps (เช่น filter, sort, join) เป็น SQL query แล้วส่งไปประมวลผลบน SQL Server แทนที่จะดึงข้อมูลทั้งหมดมาประมวลผลบน Power Query
ที่เจ๋งคือมันทำให้ได้ผลลัพธ์เร็วกว่าและใช้ memory น้อยกว่ามาก ส่วนตัวผมแนะนำให้ใช้ Table.SelectRows, Table.Sort แทนการใช้ functions ที่ไม่รองรับ query folding นะครับ 💡
จะตรวจสอบว่า query ของเราทำ query folding หรือไม่ได้อย่างไร+
ใน Power Query Editor คลิกขวาที่ step ใดๆ แล้วเลือก "View Native Query" ถ้าเห็น SQL query แสดงว่า step นั้นทำ query folding ครับ
แต่ถ้าขึ้น error หรือ disabled แสดงว่าไม่สามารถ fold ได้ ที่ต้องระวังคือควรหลีกเลี่ยงการใช้ custom functions หรือ operations ที่ซับซ้อนเกินไป ซึ่งจะทำลาย query folding ได้นะครับ 😅
CommandTimeout และ ConnectionTimeout ต่างกันอย่างไร+
ConnectionTimeout คือเวลาที่รอให้การเชื่อมต่อสำเร็จ (default ขึ้นอยู่กับ driver) ส่วน CommandTimeout คือเวลาที่รอให้ query ทำงานเสร็จหลังจากเชื่อมต่อแล้ว (default 10 นาที)
ถ้า query ใช้เวลานาน เช่น 20-30 นาที ต้องเพิ่ม CommandTimeout ให้สูงขึ้น เช่น [CommandTimeout=#duration(0,0,30,0)] สำหรับ 30 นาทีครับ
ควรใช้ native SQL query หรือ Power Query transformations+
ถ้า query ซับซ้อน มี complex JOIN หรือ aggregate functions ส่วนตัวผมแนะนำใช้ native SQL query จะเร็วกว่าครับ
แต่ถ้า transformation ง่ายๆ เช่น filter, sort, select columns ควรใช้ Power Query เพื่อประโยชน์จาก query folding และความยืดหยุ่นในการแก้ไข ที่ต้องระวังคือ native SQL query จะได้ result set เดียวและไม่สามารถ fold steps ถัดไปได้นะครับ
EnableCrossDatabaseFolding ใช้เมื่อไหร่+
ใช้เมื่อต้องการ join หรือ combine ข้อมูลจากหลาย databases บน SQL Server เดียวกันครับ
โดย default Power Query จะไม่ fold queries ข้าม database เพื่อความปลอดภัย แต่ถ้าตั้ง EnableCrossDatabaseFolding=true จะทำให้ query ทั้งหมดทำงานบน SQL Server
เอาจริงๆ มันเร็วกว่าการดึงข้อมูลจาก 2 database มา merge ใน Power Query เยอะเลยครับ 😎
HierarchicalNavigation มีประโยชน์อย่างไร+
เมื่อตั้ง HierarchicalNavigation=true ฐานข้อมูลที่มีหลาย schemas จะแสดง tables จัดกลุ่มตาม schema name (เช่น dbo, Sales, HR) ทำให้หา table ได้ง่ายขึ้นมากในฐานข้อมูลขนาดใหญ่ที่มีหลายร้อย tables
default เป็น false คือแสดง tables แบบ flat list ทั้งหมด ส่วนตัวผมใช้ true เสมอเวลาทำงานกับ enterprise database ครับ
Resources & Related
ฟังก์ชันที่ผู้เขียนโยงไว้กับ Sql.Database จัดกลุ่มตามหมวด · ชี้ที่ชื่อใดชื่อหนึ่ง แล้วฟังก์ชันที่ใช้คู่กันจะมีจุดทอง
MySQL.Database เป็นฟังก์ชันตัวเชื่อมต่อ (connector) ที่ใช้ส่งข้อมูลจากฐานข้อมูล MySQL ไปยัง Power Query ช่วยให้คุณสามารถโหลดตารางและมุมมองจากเซิร์ฟเวอร์ MySQL ได้
Odbc.DataSource เชื่อมต่อแหล่งข้อมูล ODBC และดึงรายชื่อตารางพร้อมวิว ช่วยให้คุณเข้าถึงฐานข้อมูลจากเครื่อง ODBC-compliant โดยไม่ต้องใช้ SQL Server Connection โดยตรง
Odbc.Query ใช้สำหรับดำเนินการคำสั่ง SQL โดยตรงต่อฐานข้อมูล ODBC ใดๆ เช่น SQL Server, MySQL, PostgreSQL, Oracle และส่งคืนผลลัพธ์เป็นตาราง
OleDb.DataSource ใช้สำหรับเชื่อมต่อกับฐานข้อมูล OLE DB (SQL Server, Access, MySQL ผ่าน ODBC เป็นต้น) และนำเข้าตารางหรือแสดงผลจากคำสั่ง SQL
Sql.Databases ใช้สำหรับการประมวลผล
ยังไม่มีบทความที่เกี่ยวข้องกับฟังก์ชันนี้
Comments
อีเมลของคุณจะไม่ถูกเผยแพร่