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:

  1. Edit docker-compose.seektable.env file (usually located in /opt/seektable)
  2. Add this line:
    SeekTable_ST__Connectors__DuckDB=true
  3. Re-create SeekTable docker containers to apply this change (docker compose stop + docker compose up -d).
  4. 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:

  1. Edit docker-compose.yml file (usually located in /opt/seektable)
  2. 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.
  3. Re-create SeekTable docker containers (docker compose stop + docker compose up -d) to apply these changes.
  4. 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.