This documentation page assumes that you already have a SeekTable account. Create your free account by signing up.

Configure DuckDB for pivot table reports

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 setup

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

How to enable DuckDB connector

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:

Edit docker-compose.seektable.env file (usually located in /opt/seektable)
Add this line:
```
SeekTable_ST__Connectors__DuckDB=true
```
Re-create SeekTable docker containers to apply this change (docker compose stop + docker compose up -d).
Login as "admin" user, click on "Connect to Database", choose "SQL-compatible database" (Data Source Type) and then "DuckDB" (Database Connector).

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".

DuckDB connector setup

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: `:memory:` Connect to a new in-memory database `:memory:?cache=shared` Connect to a shared, in-memory database `path_to_duckdb_file` Connect to DuckDB file `md:?motherduck_token=<TOKEN>` Connect to MotherDuck using specified access token.
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`.

How to organize an access to local files

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:

Edit docker-compose.yml file (usually located in /opt/seektable)

Add a volume for "seektable/pivotdataservice" image that maps server's folder (for instance, /var/duckdb-files) to container's folder (/app-data/duckdb-files):

  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.

Re-create SeekTable docker containers (docker compose stop + docker compose up -d) to apply these changes.
Now you can keep data files in host's /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:
- in the connection string:
```
DataSource = /app-data/duckdb-files/test.db;ACCESS_MODE=READ_ONLY
```
- in the cube's Select Query:
```
select * from read_csv('/app-data/duckdb-files/chicago-crimes.csv')
```

It is possible to mount multiple host's folders if needed.