SeekTable includes built-in DuckDB engine which can be used as a data source with SQL-compatible database connector.
DuckDB is a fast in-process analytical OLAP database suitable for near-real time reporting and analytics. With DuckDB you can get high-performance low-cost DW that lives inside SeekTable! Also DuckDB may be useful for querying really large CSV or JSON files directly (or querying multiple files at once), query Parquet files and combine data from multiple datasets (JOIN/UNION) into one report. More than that, these datasets are not obligated to be local files: they may be downloaded from URLs and/or Amazon S3 storage.
DuckDB connector can be used as a client for MotherDuck cloud service.
Important: DuckDB connector is available only in self-hosted SeekTable.
Due to security concerns only admin users can manage DuckDB-based cubes; this means that your SeekTable installation should have active "System/users admin" subscription.
Note that MotherDuck connections (without possibility to access any external resources) are supported on seektable.com via special MotherDuck connector.
MotherDuck connector uses DuckDB only as a client to your cloud databases; an access to external resources (like local files) is prohibited. This connector can be used on seektable.com and available for all users.
Connection String should be a valid MotherDuck token. This connects SeekTable to all databases you have access to; this means that concrete database name should be specified as a part of table's name in Select Query, for example:
select * from sample_data.nyc.service_requests
For security reasons DuckDB connector is disabled in self-hosted SeekTable by default (this is because DuckDB engine can be used to access any local files). To enable DuckDB for your SeekTable installation:
SeekTable_ST__Connectors__DuckDB=true
docker compose stop
+ docker compose up -d
).
Note that only users with "admin" role can create DuckDB-based cubes (this means that your SeekTable installation should have active "System/users admin" subscription). Other users can access these cubes/reports via "Team sharing".
Connection String should be a valid connection string for DuckDB.NET driver; for example:
DataSource = /app-data/duckdb-files/test.db;ACCESS_MODE=READ_ONLY
DataSource | Required |
Determines a data source type:
|
---|---|---|
ACCESS_MODE | Optional |
Specify READ_ONLY to make connection read only.
|
threads | Optional |
Max number of threads to use (integer). For example: threads=4; .
|
memory_limit | Optional | Limit RAM usage. For example: memory_limit=12GB . |
DuckDB is a part of SeekTable's reporting engine and this means that all data processing is
performed inside seektable/pivotdataservice
container.
To access data files located on your server (a host system where self-hosted SeekTable is deployed)
host's folder should be mounted in this way:
pivotdataservice: image: seektable/pivotdataservice:latest restart: always expose: - 5000 ports: - 5200:5000 volumes: - csv-files-volume:/app-data/csv-files - /var/duckdb-files:/app-data/duckdb-files env_file: - docker-compose.pivotdataservice.env depends_on: - "seektable"You may use another folders, no special meaning in these names.
docker compose stop
+ docker compose up -d
) to apply these changes.
/var/duckdb-files
folder (note that any other host's folder can be mounted)
and then reference them as files located in /app-data/duckdb-files
:
DataSource = /app-data/duckdb-files/test.db;ACCESS_MODE=READ_ONLY
select * from read_csv('/app-data/duckdb-files/chicago-crimes.csv')
It is possible to mount multiple host's folders if needed.