Skip to main content

DBeaver

DBeaver connects to databases over JDBC. For Send2Bucket data, use its DuckDB driver to read your bucket or table directly, or connect it to a warehouse that already has the data.

You haveUse
No warehouse yetThe DuckDB driver with an init script
Trino, Starburst or Athena over the bucket or Iceberg tableA Trino or Athena connection, with the tables from Trino or Amazon Athena
BigQueryA BigQuery connection, with the tables from BigQuery
ClickHouseA ClickHouse connection, with the tables from ClickHouse
Records loaded into PostgreSQL or MySQLA PostgreSQL or MySQL connection, with the tables from PostgreSQL or MySQL
note

We ran the DuckDB JDBC driver that DBeaver downloads, with the init script and connection options below, against our own sample data. The steps in DBeaver's dialogs follow DuckDB's DBeaver guide and DBeaver's documentation; we did not run DBeaver itself.

DuckDB driver​

DuckDB runs inside DBeaver and reads your bucket, Iceberg table or DuckLake table in place. Nothing is loaded first, so every query sees the latest deliveries.

  1. Save the statements for your destination from DuckDB in a file, for example s2b-init.sql. For an Amazon S3 destination:

    INSTALL httpfs; LOAD httpfs;
    CREATE SECRET IF NOT EXISTS s2b_bucket (
    TYPE s3, KEY_ID '<access_key_id>', SECRET '<secret_access_key>', REGION '<region>');

    CREATE OR REPLACE VIEW records AS
    SELECT * FROM read_json(
    's3://<bucket>/<prefix>/s2b/stream=*/date=*/hour=*/*.ndjson.gz',
    format = 'newline_delimited',
    hive_partitioning = true,
    columns = {v: 'INTEGER', delivery_batch_id: 'VARCHAR', batch_id: 'VARCHAR',
    received_at: 'TIMESTAMPTZ', signal: 'VARCHAR',
    resource: 'JSON', scope: 'JSON', data: 'JSON'});

    Use a storage key that can only read the bucket. To use your AWS profile or environment variables instead of a key in the file, write PROVIDER credential_chain in place of KEY_ID and SECRET; DBeaver must then be started with that profile or those variables.

  2. In DBeaver, choose Database > New Database Connection, search for DuckDB, and click Next.

  3. Connect by URL and enter:

    jdbc:duckdb::memory:;session_init_sql_file=/path/to/s2b-init.sql

    The driver runs the file before it hands each connection to DBeaver. session_init_sql_file must be the first option in the URL.

  4. When DBeaver offers to download the driver files, click Download. Then click Finish.

  5. Open a SQL editor on the connection and query records:

    SELECT data->>'$.event' AS event,
    count(*) AS events,
    sum((data->>'$.cents')::INTEGER) / 100 AS revenue_usd
    FROM records
    WHERE signal = 'event' AND date >= current_date - 7
    GROUP BY ALL
    ORDER BY events DESC;

    The other example queries work unchanged.

For a table destination, put the ATTACH and view for your catalog from DuckDB in the file instead. DuckLake catalogs need DuckDB 2.0 or later; check the driver version under Database > Driver Manager > DuckDB > Libraries, and use Download/Update to move to a newer one.

Why an init script​

DBeaver opens more than one JDBC connection per data source: one for the database navigator and one per SQL editor by default. With an in-memory database, each of those connections gets its own empty database, so a view created in one editor does not exist in the next. session_init_sql_file runs the whole file on each connection, so every editor has the secret and the records view.

You can get the same effect without the URL option:

  • Bootstrap queries. Edit the connection, open Connection settings > Initialization, and add the statements under Bootstrap queries. DBeaver runs them on every new connection.
  • A database file. Set the connection's path to a file such as s2b.duckdb instead of :memory:, and run the script once in a SQL editor with CREATE PERSISTENT SECRET in place of CREATE SECRET. The view is stored in the file and the secret in ~/.duckdb/stored_secrets, so later sessions need no script. The secret is stored unencrypted, so use a read-only key.

Read-only​

The DuckDB driver cannot switch a connection to read-only after it is open, so DBeaver's read-only setting does not protect a DuckDB connection. For a database file, set the driver property duckdb.read_only to true (or access_mode to READ_ONLY) after the views exist: queries still run, and CREATE and INSERT fail. With a read-only database file, keep only the CREATE SECRET in the init script, since views cannot be created then. In every case the storage key is what keeps the bucket safe, so give it read access only.

Warehouse connections​

If the data is already queryable in a warehouse, connect DBeaver to that engine and query the tables or views its guide creates. Use a read-only database user or role.

EngineDBeaver connectionFieldsTables from
Trino or StarburstTrinoHost, port, user and password; catalog and schema optionalTrino
Amazon AthenaAthenaRegion, S3 location for query results, and an access key as user name and passwordAmazon Athena
BigQueryGoogle BigQueryProject, and authentication by key file, default credentials or browser sign-inBigQuery
ClickHouseClickHouseHost, port (8443 for HTTPS), user, password and databaseClickHouse
PostgreSQLPostgreSQLHost, port, database, user and passwordPostgreSQL
MySQL or MariaDBMySQL or MariaDBHost, port, database, user and passwordMySQL

Snowflake also has a DBeaver connection; use it once your records are in a Snowflake table. Some authentication options, such as AWS IAM for Athena, are marked as DBeaver PRO features in DBeaver's driver pages.

Notes​

  • INSTALL httpfs downloads the extension from DuckDB's extension repository the first time. Behind a proxy or without internet access, install the extensions once with the DuckDB CLI of the same version on that machine.
  • A view over bucket objects reads the bucket on every query. Filter on date to read fewer objects, and limit large results: DBeaver fetches the first page of rows, but DuckDB still reads every object the query needs.
  • The init file holds a storage key in plain text. Keep it outside shared folders and version control, or use PROVIDER credential_chain.
  • DBeaver stores connection passwords for warehouse connections in its own credential store; the DuckDB connection has no password, so its only secret is the one in the init script or persistent secret.