[Feature Request] Add DuckDB Support to LINO

[youen]

Proposal to Add DuckDB Support in LINO

Goal

The goal of this proposal is to enhance LINO by adding support for DuckDB as a database source. DuckDB is a lightweight, embeddable analytical database that is gaining popularity for its speed and efficiency. Integrating DuckDB into LINO will provide users with the flexibility to extract relations and tables from DuckDB databases, expanding the range of supported databases.

Implementation Details

1. Extractor for DuckDB Relations (internal/infra/relation/extractor_duckdb.go):
   • Implement a function in extractor_duckdb.go that generates the SQL query to fetch all relations in the specified schema.
   • Use existing files like extractor_postgres.go as a reference.

// internal/infra/relation/extractor_duckdb.go

package relation

// DuckDBDialect represents the DuckDB SQL dialect.
type DuckDBDialect struct{}

// SQL returns the SQL query to fetch all relations in the named schema.
func (d DuckDBDialect) SQL(schema string) string {
    return fmt.Sprintf("SELECT table_name FROM information_schema.tables WHERE table_schema = '%s'", schema)
}

2. Extractor for DuckDB Tables (internal/infra/table/extractor_duckdb.go):
   • Implement a function in extractor_duckdb.go that generates the SQL query to fetch all tables in the specified schema.
   • Use existing files like extractor_postgres.go as a reference.

// internal/infra/table/extractor_duckdb.go

package table

// DuckDBDialect represents the DuckDB SQL dialect.
type DuckDBDialect struct{}

// SQL returns the SQL query to fetch all tables in the named schema.
func (d DuckDBDialect) SQL(schema string) string {
    return fmt.Sprintf("SELECT table_name FROM information_schema.tables WHERE table_schema = '%s'", schema)
}

3. Data Destination for DuckDB (internal/infra/push/datadestination_duckdb.go):
   • Implement the SQLDialect interface in datadestination_duckdb.go to define specifics about the DuckDB SQL dialect.
   • Refer to existing files like datadestination_postgres.go for guidance.

// internal/infra/push/datadestination_duckdb.go

package push

// DuckDBDialect represents the DuckDB SQL dialect.
type DuckDBDialect struct{}

// Implement the SQLDialect interface here.
// Example: func (d DuckDBDialect) CreateUpdateStatement() string { ... }

4. Data Source for DuckDB (internal/infra/pull/datasource_duckdb.go):
   • Implement the SQLDialect interface in datasource_duckdb.go to define specifics about the DuckDB SQL dialect.
   • Refer to existing files like datasource_postgres.go for guidance.

// internal/infra/pull/datasource_duckdb.go

package pull

// DuckDBDialect represents the DuckDB SQL dialect.
type DuckDBDialect struct{}

// Implement the SQLDialect interface here.
// Example: func (d DuckDBDialect) CreateUpdateStatement() string { ... }

Once the above files are implemented, users will be able to add DuckDB as a source to LINO by running commands similar to those provided in the example for MySQL:

docker run -v /path/to/lino:/home/lino lino_lino dataconnector add source duckdb:///path/to/duckdb/database -s duckschema
docker run -v /path/to/lino:/home/lino lino_lino table extract source

This proposal aims to extend LINO's capabilities and offer users more options for data extraction. Your feedback and contributions to this feature request are highly appreciated.

Please feel free to open discussions and ask questions for further clarification.

Youen