Enhancement: MotherDuck Data Connector

Goal-State/What/Result

MotherDuck can be used as a data connector with the Spice OSS

Milestones:

M1 MotherDuck Data Connector Alpha

Why/Purpose

MotherDuck is a cloud based, serverless compute option integrated with DuckDB. Spice runtime users can seamlessly leverage their MotherDuck compute services in Spice.

By When

Issue/Spec written and reviewed: 05/14

M1: TBD Done-Done: TBD

Done-Done

[x] First Principles Driven
[x] The Algorithm
[x] PM/Design Review
[ ] UX Review
[ ] Threat Model / Security Review
[ ] Tests
[ ] Documentation
[ ] Samples/Quickstarts/Tutorials

The Algorithm

[x] Every requirement questioned?
[x] Delete (Scope) any part you can.
[x] Simplify.
[x] Break down into smaller iterations/milestones.
[x] Opportunities for automation.

Spec

M1 MotherDuck Data Connector Alpha

[x] MotherDuck Data Connector can be defined in Spicepod.

- from: motherduck:database.schema.table
  name: my_table
  params: 
    motherduck_token: <token>

[ ] Pushdowns are correctly supported for MD federated queries
[ ] MotherDuck token based login support is added to Spice CLI:

spice login motherduck <token>

Token from secrets is used in this case.

- from: motherduck:database.schema.table
  name: my_table

[ ] ~Support for MotherDuck OAuth flow to retrieve access token: spice login motherduck~
[x] link-dead-code compile option is added to default build in order to get motherduck extension working. This will increase target build size from 153mb to 249mb (+63%) - the decision is that this is ok and we would like to proceed with this approach until we have proper support for motherduck by duckdb-rs. We compile slim Docker version w/o this flag and don't support Motherduck Data Connector in slim version

How/MAP

[x] Implement MotherDuck Data Connector
[ ] Implement spice login motherduck <token> support (token-based auth)
[ ] ~Implement spice login motherduck (OAuth flow)~
[ ] Quickstart to query data from MotherDuck' pre-defined sample_data.nyc.taxi dataset
[ ] MotherDuck Data Connector documentation

Implementation (issues)

QA

The following query must be used for manual QA to confirm Data Connector performance.

SELECT
regexp_extract(url, 'http[s]?://([^/]+)/', 1) AS domain,
count(*) AS count
FROM hacker_news
WHERE url IS NOT NULL AND regexp_extract(url, 'http[s]?://([^/]+)/', 1) != ''
GROUP BY domain
ORDER BY count DESC
LIMIT 20;

spiceai / spiceai

Enhancement: MotherDuck Data Connector #1159