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 ครับ 😎
=Sql.Database(server as text, database as text, optional options as nullable record) as table
Function Metrics
Popularity
8/10
Difficulty
4/10
Usefulness
9/10
Syntax & Arguments
=Sql.Database(server as text, database as text, optional options as nullable record) as table
| Argument | Type | Required | Default | Description |
|---|---|---|---|---|
| server | text | Yes | ชื่อหรือ hostname ของ SQL Server ที่ต้องการเชื่อมต่อ สามารถระบุ port ได้โดยใช้ colon หรือ comma คั่น เช่น “localhost” หรือ “server.com:1433” | |
| database | text | Yes | ชื่อของฐานข้อมูลที่ต้องการเข้าถึงใน SQL Server เช่น “AdventureWorks” หรือ “Northwind” | |
| options | nullable record | Optional | null | Record ที่ใช้สำหรับกำหนดตัวเลือกเพิ่มเติมในการควบคุมการเชื่อมต่อและการทำงานของฟังก์ชัน เช่น Query สำหรับเขียน native SQL query โดยตรง, CommandTimeout สำหรับกำหนดระยะเวลา timeout ของการทำงาน, HierarchicalNavigation เพื่อให้แสดง tables จัดกลุ่มตาม schema name, EnableCrossDatabaseFolding เพื่ออนุญาตให้ทำ query folding ข้ามหลาย database ได้, MaxDegreeOfParallelism สำหรับการทำ parallel processing และตัวเลือกอื่นๆ อีกมากมาย |
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…
.
ที่เจ๋งคือตอนเราใช้ Table.SelectRows มันจะเกิด query folding กลับไปที่ SQL Server เลย ดังนั้นการกรองจะเกิดที่ฐานข้อมูลโดยตรง ไม่ต้องดึงข้อมูลทั้งหมดมาก่อนครับ
.
ส่วนตัวผมชอบวิธีนี้มาก เพราะได้ประสิทธิภาพสูงสุดและดึงเฉพาะข้อมูลที่ต้องการมาเท่านั้นครับ 😎
let
Source = Sql.Database("localhost", "AdventureWorks"),
SalesTable = Source{[Schema="Sales",Item="Customer"]}[Data],
FilteredRows = Table.SelectRows(SalesTable, each [CustomerID] > 100)
in
FilteredRows
table ที่มีข้อมูล Customer จาก Sales schema โดยกรองเฉพาะ CustomerID > 100
ตัวอย่างที่ 2: การใช้ native SQL query
let Source = Sql.Database( "sqlserver.company.com", "SalesDB", [ Query = " SELECT c.CustomerName, SUM(o.Amount) as TotalSales FROM Customers c INNER JOIN Orders…
.
เราทำการ JOIN ข้อมูลจากหลาย table ใช้ WHERE clause กรองข้อมูลตามเงื่อนไข ทำ GROUP BY เพื่อจัดกลุ่มและสรุปข้อมูล แล้วใช้ HAVING กรองผลลัพธ์หลังจากจัดกลุ่ม query ทั้งหมดจะถูกประมวลผลบน SQL Server ก่อนที่จะส่งผลลัพธ์สุดท้ายมาที่ Power Query
.
เอาจริงๆ วิธีนี้ช่วยลดปริมาณข้อมูลที่ต้องถ่ายโอนผ่าน network และเพิ่มความเร็วในการประมวลผลได้เยอะมาก ส่วนตัวผมใช้แบบนี้กับ query ที่ซับซ้อนเสมอครับ 💡
let
Source = Sql.Database(
"sqlserver.company.com",
"SalesDB",
[
Query = "
SELECT
c.CustomerName,
SUM(o.Amount) as TotalSales
FROM Customers c
INNER JOIN Orders o ON c.CustomerID = o.CustomerID
WHERE o.OrderDate >= '2024-01-01'
GROUP BY c.CustomerName
HAVING SUM(o.Amount) > 10000
"
]
)
in
Source
table ที่มีคอลัมน์ CustomerName และ TotalSales โดย filter เฉพาะยอดขายมากกว่า 10,000
ตัวอย่างที่ 3: การตั้งค่า CommandTimeout และ HierarchicalNavigation
let Source = Sql.Database( "bigdata-server.com:1433", "WarehouseDB", [ CommandTimeout = #duration(0, 0, 30, 0), HierarchicalNavigation = true, CreateNavigationP…
.
ส่วน HierarchicalNavigation ตั้งเป็น true จะทำให้ tables แสดงผลแบบจัดกลุ่มตาม schema name ที่ต้องระวังคือถ้าฐานข้อมูลมีหลายร้อย table การจัดกลุ่มแบบนี้จะช่วยให้หาได้ง่ายขึ้นเยอะครับ
.
นอกจากนี้ CreateNavigationProperties ที่ตั้งเป็น true จะสร้าง navigation properties ขึ้นมา ทำให้การเข้าถึงข้อมูลสะดวกและรวดเร็วขึ้นมากครับ 😎
let
Source = Sql.Database(
"bigdata-server.com:1433",
"WarehouseDB",
[
CommandTimeout = #duration(0, 0, 30, 0),
HierarchicalNavigation = true,
CreateNavigationProperties = true
]
),
InventorySchema = Source{[Name="Inventory"]}[Data],
ProductsTable = InventorySchema{[Name="Products"]}[Data]
in
ProductsTable
table ที่มีข้อมูล Products จาก Inventory schema พร้อม CommandTimeout 30 นาที
ตัวอย่างที่ 4: การใช้ EnableCrossDatabaseFolding และ MaxDegreeOfParallelism
let Source = Sql.Database( "analytics.server.com", "MainDB", [ EnableCrossDatabaseFolding = true, MaxDegreeOfParallelism = 4, ConnectionTimeout = #duration(0, 0…
.
ส่วน MaxDegreeOfParallelism ตั้งเป็น 4 จะบอกให้ SQL Server ใช้ parallel processing สูงสุด 4 threads พร้อมกันในการประมวลผล query นอกจากนี้ ConnectionTimeout ถูกตั้งเป็น 2 นาทีสำหรับกำหนดระยะเวลาสูงสุดในการรอการเชื่อมต่อ
.
เอาจริงๆ การตั้งค่าต่างๆ เหล่านี้ช่วยเพิ่มประสิทธิภาพได้มากเวลาทำงานกับข้อมูลขนาดใหญ่และ complex queries ครับ ส่วนตัวผมใช้แบบนี้กับ data warehouse เสมอเลย 💡
let
Source = Sql.Database(
"analytics.server.com",
"MainDB",
[
EnableCrossDatabaseFolding = true,
MaxDegreeOfParallelism = 4,
ConnectionTimeout = #duration(0, 0, 2, 0)
]
),
MainTable = Source{[Schema="dbo",Item="Sales"]}[Data],
JoinedData = Table.NestedJoin(
MainTable,
{"ProductID"},
Sql.Database("analytics.server.com", "RefDB"){[Schema="dbo",Item="Products"]}[Data],
{"ProductID"},
"ProductInfo",
JoinKind.Inner
)
in
JoinedData
table ที่ join ข้อมูลจาก 2 database (MainDB และ RefDB) โดยใช้ query folding
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
Resources
-
Microsoft Learn: Sql.Database
documentation -
Microsoft Learn: SQL Server Connector
guide -
Power Query Query Folding Guide
guide
Related functions
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 ครับ 😎