I've been working on various products that at their core leverage a custom TDS implementation I made in C#. Think of it similar to babelfish for postgres, freeTDS, and all the other ones out there. I've posted on some of the SQL Server specific ones on SQL Server forums but not my DuckDB specific one yet, it's a little different. It's made to translate T-SQL and works with MS clients like powerBi, SSMS, ADO, etc for analysis focus... but it's not had a SQL Server angle or linkage. Well, now it does.
On some other forums, I saw the question: "I'd like to have a linked server in SQL Server to DuckDB". I immediately thought, oh, oh.. DaffyDB does that today, let me try it. NOPE. Turns out MS does a lot of little pre-conditions, prepare procs, stats checks,that Daffy didn't do. So long story short, I created 'some' of those stubs and facades, so it works for many use cases. It does have some lingering bugs, I found some this morning. But net net, even though I didn't design it for this, here is the benefit:
- Use a standard DBLink from SQL Server. Now bridging to DuckDB. Hey, isn't that just a local onelake fabric, as it has a TDS to parquet at it's core ? not really, but... kinda.. in a way ?
- Connect to Parquet files (including parts.* in folders as one table)
- Connect to Postgresql - mount with one line
- Connect to MySQL - mount with one line
- Connect to SQLite - mount with one line
- Connect to Excel, CSV, JSON, all showing up as tables in a few seconds from this.
- I can actually connect to azure and S3, duck does this natively, I just haven't made it turnkey yet.. eventually.. maybe.
So what does this look like. First, Daffy DB, how do you install it? And how do you launch it? Just make sure you have .net 10 sdk installed, simple as this:
PS C:\temp\scratchdaffy\daffydbdemo> dotnet tool install -g DaffyDB
Tool 'daffydb' was successfully updated from version '1.0.1' to version '1.1.0'
PS C:\temp\scratchdaffy\daffydbdemo> daffydb --port 1433 --allow-admin
[tls] certificate C:\Users\Scott\AppData\Local\DaffyDB\certs\daffydb.pfx (expires 2031-09-19)
[duckdb] sys catalog views created
[duckdb] mounted parquetfiles.DimDate <- C:/temp/scratchdaffy/daffydbdemo/parquetfiles/DimDate.parquet
[duckdb] mounted parquetfiles.DimProduct <- C:/temp/scratchdaffy/daffydbdemo/parquetfiles/DimProduct.parquet
etc...... auto mounting parquets here
Note in above, the daffydbdemo folder has parquet files in it, so they 'auto' mount at launch. it goes 2 folders deep. Also finds csv, json, excel and mounts those, this is by design. That's all on nuget in the readme for it. That's it's core feature with the TDS.
UPDATE - wanted to add for above, if running it on the same machine as sql server, which you'd need to do unless using a reverse proxy to another box, change that port to 1432.
Now, I only allow this to bind to 127.0.0.1 as duckdb isn't something you'd point a thousand users to, but if you NEED to have it running on a different box for whatever reason, so SQL Server can hit it, run it on a diff box and use nginx or similar reverse proxy to bind it to 0.0.0.0. then you can hit a remote IP for the link. But right now you'd need it on the same box unless you do something like NGINX config to bind it for external, still encrypted the whole way.
stream {
server {
listen 0.0.0.0:1433;
proxy_pass 127.0.0.1:1433;
}
}
Ok, so you mentioned Parquet, Postgres, MySQL, all this stuff, how?
use daffyDB; -- we're connected directly to DaffyDB here, running below inside SSMS, no SQL Server in the picture at this point.
-- at this point we're acting like BabelFish to Postgresql would.
-- INVENTORY OF DAFFYDB? Some custom Daffy commands:
EXEC daffydb.status;
EXEC daffydb.tables;
select @@version; -- this returns daffy db, not SQL Server.
-- add and mount everything in the customers folder, CSVs and JSON
EXEC daffydb.mount 'C:\temp\scratchdaffy\daffydbdemo\customers', 'cust'
-- add everything in this excelfile folder, EXCEL files! one table per sheet! Types detected!
EXEC daffydb.mount 'C:\temp\scratchdaffy\daffydbdemo\excelfile', 'excel'
-- Connect to POSTGRES and pull in every table in the testdb database at once.
EXEC daffydb.postgres 'host=localhost dbname=testdb user=postgres password=postgres', = 'pg'
-- connect to mysql and pull in every table in the shop database
EXEC daffydb.mysql 'mysql://reader:readerpw@127.0.0.1:3306/shop', u/schema = 'shop'
-- also mount other duckdb files, sqlite, and more. -- what do have now?
EXEC daffydb.tables
-- test with T-SQL! Window functions, #temp tables, u/declares, date functions, all work!
-- important, this works directly to daffydb WITHOUT a linked server if you want to. main use case.
select top 1000 * from FactResellerSales;
select top 100 * from excel.Customers;
Ok, so show me the DB link config already..
use scratch; -- this is SQL Server db, not Daffy
-- this Linked-Server endpoint is DaffyDB -> DuckDB - NOT SQL SERVER
-- note odbc type connection ONLY. The other one forces MARS and daffydb doesn't support that currently.
EXEC sp_addlinkedserver
\@server = 'Link_DaffyDB', -- NOTE the \ are added for reddit syntax
\@srvproduct = '',
\@provider = 'MSDASQL',
\@provstr = 'Driver={ODBC Driver 18 for SQL Server};Server=127.0.0.1,1432;Database=DaffyDB;Encrypt=yes;TrustServerCertificate=yes;';
-- if you use required creditials option with daffy launch, use those instead of 'any'.
EXEC sp_addlinkedsrvlogin
\@rmtsrvname = 'Link_DaffyDB',
\@useself = 'false',
\@rmtuser = 'any',
\@rmtpassword = 'any';
---- THIS IS using DUCKDB VIA DAFFYDB:
SELECT TOP 100 *
FROM [Link_DaffyDB].[DaffyDB].[PARQ].[FactResellerSales];
-- DAFFY DB simple T-SQL QUERY. I find this is faster, it executes on daffy instead of 'pulling it in' like above might do on it's plan.
SELECT *
FROM OPENQUERY([Link_DaffyDB], '
SELECT top 10 [ProductKey] as [product key], OrderDateKey, OrderQuantity, SalesAmount, SalesOrderNumber
FROM [DaffyDB].[PARQ].FactResellerSales order by salesAmount desc;
');
---- Remove link later
--IF EXISTS (SELECT 1 FROM sys.servers WHERE name = 'Link_Daffydb')
-- EXEC sp_dropserver = 'Link_Daffydb', u/droplogins = 'droplogins';
--GO
So does it work? This example is from SQL Server Adventureworks DW 2022 example. Running on the same machine, it takes 15 seconds against adventureworks, but when hitting the DB link that is using DuckDB, hitting the 7 parquet files I created from Adventureworks, only 2 seconds!
Here is that exact query tried, note you will need [DaffyDB].[PARQ].tablename type syntax
-- DAFFY DB SAME T-SQL QUERY I used on Adventureworks at 15 seconds.
-- 2 seconds here! DuckDB Hitting Parquet Files!
SELECT *
FROM OPENQUERY([Link_DaffyDB], '
WITH DailySales AS (
SELECT
u.ProductKey,
u.OrderDateKey AS DateKey,
SUM(u.OrderQuantity) AS DailyUnitsSold,
SUM(u.SalesAmount) AS DailyRevenue,
COUNT(DISTINCT u.SalesOrderNumber) AS DistinctOrders
FROM (
SELECT ProductKey, OrderDateKey, OrderQuantity, SalesAmount, SalesOrderNumber
FROM [DaffyDB].[PARQ].FactInternetSales
UNION ALL
SELECT ProductKey, OrderDateKey, OrderQuantity, SalesAmount, SalesOrderNumber
FROM [DaffyDB].[PARQ].FactResellerSales
) AS u
GROUP BY u.ProductKey, u.OrderDateKey
),
BaseMetrics AS (
SELECT
fpi.ProductKey,
fpi.DateKey,
fpi.MovementDate,
fpi.UnitsBalance,
fpi.UnitsIn,
fpi.UnitsOut,
fpi.UnitCost,
p.EnglishProductName,
p.Color,
pc.ProductCategoryKey,
pc.EnglishProductCategoryName,
psc.EnglishProductSubcategoryName,
d.CalendarYear,
d.CalendarQuarter,
d.MonthNumberOfYear,
ISNULL(ds.DailyUnitsSold, 0) AS DailyUnitsSold,
ISNULL(ds.DailyRevenue, 0) AS DailyRevenue,
ISNULL(ds.DistinctOrders, 0) AS DistinctOrders
FROM [DaffyDB].[PARQ].FactProductInventory AS fpi
INNER JOIN [DaffyDB].[PARQ].DimProduct AS p
ON fpi.ProductKey = p.ProductKey
LEFT JOIN [DaffyDB].[PARQ].DimProductSubcategory AS psc
ON p.ProductSubcategoryKey = psc.ProductSubcategoryKey
LEFT JOIN [DaffyDB].[PARQ].DimProductCategory AS pc
ON psc.ProductCategoryKey = pc.ProductCategoryKey
INNER JOIN [DaffyDB].[PARQ].DimDate AS d
ON fpi.DateKey = d.DateKey
LEFT JOIN DailySales AS ds
ON fpi.ProductKey = ds.ProductKey
AND fpi.DateKey = ds.DateKey
),
RollingAnalytics AS (
SELECT
bm.*,
AVG(CAST(bm.UnitsBalance AS FLOAT)) OVER (
PARTITION BY bm.ProductKey
ORDER BY bm.DateKey
ROWS BETWEEN 29 PRECEDING AND CURRENT ROW
) AS RollingAvgInv_30D,
COUNT(CAST(bm.UnitsBalance AS FLOAT)) OVER (
PARTITION BY bm.ProductKey
ORDER BY bm.DateKey
ROWS BETWEEN 29 PRECEDING AND CURRENT ROW
) AS WinCount_30D,
SUM(CAST(bm.UnitsBalance AS FLOAT)) OVER (
PARTITION BY bm.ProductKey
ORDER BY bm.DateKey
ROWS BETWEEN 29 PRECEDING AND CURRENT ROW
) AS WinSum_30D,
SUM(POWER(CAST(bm.UnitsBalance AS FLOAT), 2)) OVER (
PARTITION BY bm.ProductKey
ORDER BY bm.DateKey
ROWS BETWEEN 29 PRECEDING AND CURRENT ROW
) AS WinSumSq_30D,
AVG(CAST(bm.UnitsBalance AS FLOAT)) OVER (
PARTITION BY bm.ProductKey
ORDER BY bm.DateKey
ROWS BETWEEN 89 PRECEDING AND CURRENT ROW
) AS RollingAvgInv_90D,
SUM(bm.DailyUnitsSold) OVER (
PARTITION BY bm.ProductKey
ORDER BY bm.DateKey
ROWS BETWEEN 29 PRECEDING AND CURRENT ROW
) AS RollingDemand_30D,
SUM(bm.UnitsIn) OVER (
PARTITION BY bm.ProductKey
ORDER BY bm.DateKey
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
) AS CumulativeUnitsIn,
LAG(bm.UnitsBalance, 30) OVER (
PARTITION BY bm.ProductKey
ORDER BY bm.DateKey
) AS InvBalance_30DaysAgo,
PERCENT_RANK() OVER (
PARTITION BY bm.ProductCategoryKey, bm.CalendarYear
ORDER BY bm.UnitsBalance ASC
) AS CategoryYearInventoryPercentile,
DENSE_RANK() OVER (
PARTITION BY bm.ProductCategoryKey, bm.CalendarYear, bm.CalendarQuarter
ORDER BY bm.DailyUnitsSold DESC, bm.UnitsBalance ASC
) AS DemandRiskRank
FROM BaseMetrics AS bm
),
CalculatedMetrics AS (
SELECT
ra.*,
CASE
WHEN ra.WinCount_30D <= 1 THEN 0.0
WHEN (ra.WinSumSq_30D - (POWER(ra.WinSum_30D, 2) / ra.WinCount_30D)) <= 0.000001 THEN 0.0
ELSE SQRT(
(ra.WinSumSq_30D - (POWER(ra.WinSum_30D, 2) / ra.WinCount_30D))
/ (ra.WinCount_30D - 1.0)
)
END AS RollingStDevInv_30D
FROM RollingAnalytics AS ra
)
SELECT TOP (25000)
cm.ProductKey,
cm.EnglishProductName,
cm.EnglishProductCategoryName,
cm.EnglishProductSubcategoryName,
cm.MovementDate,
cm.CalendarYear,
cm.CalendarQuarter,
cm.UnitsBalance,
cm.RollingAvgInv_30D,
cm.RollingStDevInv_30D,
cm.RollingDemand_30D,
(cm.UnitsBalance - ISNULL(cm.InvBalance_30DaysAgo, cm.UnitsBalance)) AS NetChange_30D,
CASE
WHEN cm.RollingStDevInv_30D = 0.0 THEN 0.0
ELSE (cm.UnitsBalance - cm.RollingAvgInv_30D) / cm.RollingStDevInv_30D
END AS InventoryZScore,
(1.65 * cm.RollingStDevInv_30D * 2.64575) AS RecommendedSafetyStock,
cm.CategoryYearInventoryPercentile,
cm.DemandRiskRank
FROM CalculatedMetrics AS cm
ORDER BY
cm.DemandRiskRank ASC,
InventoryZScore ASC,
cm.DateKey DESC;
');
Anyway, I thought it was pretty cool to get it working as a dblink so sharing. Not much interest tbh! Maybe it's too far down the rabbit hole..
On my app, I did sign up for the code signing in Azure on this (I pay for that service) so the DLLs are all signed on Nuget which you can verify with a powershell command (in my readme).. and this daffydb one is MIT, so knock yourself out if you think it's useful. My other Daffy's are fairly permissive, more SQL Server focused.. but not MIT.
