Adding support for DuckDB

[enzo-cappa]

Hello!

It would be interesting to have support for DuckDB in the different SQL input, processors, and outputs.

DuckDB has a Go driver (see https://duckdb.org/docs/stable/clients/go.html) and the dialect is, for the most part, compatible with PostgreSQL (see https://duckdb.org/docs/stable/sql/dialect/postgresql_compatibility)

Is this something that could be considered?

Is there any documentation explaining how to add support for a new driver in case I dare to try creating a PR?

Thanks!

[gregfurman]

Hey! Given the popularity of DuckDB, think it'd be great to have our SQL components be better integrated. You can look at how we import in drivers in public/components/sql i.e

bento/public/components/sql/package.go

Lines 7 to 12 in b3acc11

	import (
	// Bring in the base plugin definitions.
	_ "github.com/warpstreamlabs/bento/public/components/sql/base"
	// Import all (supported) sql drivers.
	_ "github.com/ClickHouse/clickhouse-go/v2"

Then you'd just have to add a new duckdb option driver to the the config.

bento/internal/impl/sql/conn_fields.go

Line 20 in b3acc11

var driverField = service.NewStringEnumField("driver", "mysql", "postgres", "clickhouse", "mssql", "sqlite", "oracle", "snowflake", "trino", "gocosmos", "spanner").

As well as update the DSN field docs to include any duckdb specific data source name information required to connect to a DuckDB source.

bento/internal/impl/sql/conn_fields.go

Line 23 in b3acc11

var dsnField = service.NewStringField("dsn").

Then, I'd add an integration test, running DuckDB in a docker container, to ensure Bento can connect up and operate successfully. You can give the spanner test a look for some inspiration (here is the original PR #123 where Spanner support was added):

bento/internal/impl/sql/integration_test.go

Line 759 in b3acc11

func TestIntegrationSpanner(t *testing.T) {

[jem-davies]

@enzo-cappa fyi: there was this PR a while back that added support for a new sql driver - you mention about support for creating a new PR potentially it will help idk

EDIT @gregfurman - see that you already pointed this out -- sorry!

[gregfurman]

@enzo-cappa 👋 Are you keen to take this on?

No pressure at all. Jem and myself would be happy to provide any guidance if you're up for it! 😃

[enzo-cappa]

@gregfurman I've not been able to make the time to work on this. I really appreciate that both you and @jem-davies provided such detailed guidance, and I'd give it a try as soon as I can.
Of course, if anyone else wants to work on this it's more than fine for me. I'm hoping to be able to contribute back to this amazing project in the future, but I don't want to delay any potential progress.

[gregfurman]

@enzo-cappa Think it's a great initiative so would be happy for a community member to pick it up! Understand that perhaps it's a large undertaking 😄

We'll leave this open for now and see if someone feels like donating a PR for a 🦆 + 🍱 connector.

[James-Gilbert-]

@gregfurman I spent some time looking into this for a project -- the main difficulty is that go-duckdb uses prebuilt binaries which aren't compatible with the image in Dockerfile.cgo (mentioned in an issue here).

Breaking Changes and Deprecations
Now that all mainstream Linux distributions use glibc 2.28 or newer, DuckDB's official Linux binaries require at least glibc 2.28 or newer. The release is built using the manylinux_2_28 image from Python, which combines an older glibc with a newer compiler.

What are the current thoughts on build tags for individual components like zmq or duckdb?

File-based duckdb support was workable by:

1. Changing the code as you suggested
2. Using a sqlite-style setup for the test, since the db can just be a file on disk
3. Modifying the CGO dockerfile to build duckdb from source + link properly with go-duckdb

Running it from the image:

--- PASS: TestIntegrationDuckDB (0.00s)
    --- PASS: TestIntegrationDuckDB/file_based (1.35s)
        --- PASS: TestIntegrationDuckDB/file_based/basic (0.02s)
        --- PASS: TestIntegrationDuckDB/file_based/parallel (1.12s)
        --- PASS: TestIntegrationDuckDB/file_based/batch_input_output (0.01s)
        --- PASS: TestIntegrationDuckDB/file_based/raw_input_output (0.04s)
        --- PASS: TestIntegrationDuckDB/file_based/raw (0.04s)
        --- PASS: TestIntegrationDuckDB/file_based/deprecated (0.04s)
PASS
ok      github.com/warpstreamlabs/bento/internal/impl/sql       1.440s

[gregfurman]

@James-Gilbert- Thanks for picking this up! I have no issues with using build tags here 😄

We can take a similar approach to what we do with couchbase since arm builds are disabled:

arm build tag is a no-op:

bento/public/components/couchbase/package_32bit.go

Lines 1 to 3 in 7dfd5a4

	//go:build arm
	package couchbase

Otherwise actually import the component:

bento/public/components/couchbase/package.go

Lines 1 to 8 in 7dfd5a4

	//go:build !arm
	package couchbase
	import (
	// Bring in the internal plugin definitions.
	_ "github.com/warpstreamlabs/bento/internal/impl/couchbase"
	)

But instead of disabling ARM, we only ever build when not in CGO i.e

//go:build !cgo

// In public/components/duckdb/package.go

package duckdb

import (
	// Bring in the internal plugin definitions.
	_ "github.com/warpstreamlabs/bento/internal/impl/duckdb"
)

Then add a file for no-op importing when in CGO to ensure the imports in public/components/all work fine:

//go:build cgo

// In public/components/duckdb/package_cgo.go

package duckdb

// TODO: go-duckdb uses prebuilt binaries which aren't compatible with the image in Dockerfile.cgo (see issue https://github.com/duckdb/duckdb/issues/17632).
// When fixed, remove this file to allow DuckDB component to be imported when CGO is enabled.

[gregfurman]

Hey @James-Gilbert- 👋 Let us know if you need any assistance on the above. Seems like you've started building out a component and would love the opportunity to get this in!

[James-Gilbert-]

PR here: #365