DataGrip
DataGrip and the database tools in other JetBrains IDEs, such as IntelliJ IDEA Ultimate and PyCharm Professional, connect to databases over JDBC. For Send2Bucket data, add a DuckDB data source that reads your bucket or table directly, or a data source for the warehouse that already has the data.
| You have | Use |
|---|---|
| No warehouse yet | A DuckDB data source with a startup script |
| Trino, Starburst or Athena over the bucket or Iceberg table | A Trino or Athena data source, with the tables from Trino or Amazon Athena |
| BigQuery | A BigQuery data source, with the tables from BigQuery |
| ClickHouse | A ClickHouse data source, with the tables from ClickHouse |
| Records loaded into PostgreSQL or MySQL | A PostgreSQL or MySQL data source, with the tables from PostgreSQL or MySQL |
We ran the DuckDB JDBC driver that DataGrip downloads, with the init script and connection options below, against our own sample data. The steps in DataGrip's dialogs follow JetBrains' DuckDB and data source settings documentation; we did not run DataGrip itself.
DuckDB data source
DuckDB runs inside the IDE and reads your bucket, Iceberg table or DuckLake table in place, so every query sees the latest deliveries.
-
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 ASSELECT * 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, or
PROVIDER credential_chainto use the AWS profile or environment variables the IDE was started with. -
Choose File > New > Data Source > DuckDB.
-
Set Connection type to URL only and enter:
jdbc:duckdb::memory:;session_init_sql_file=/path/to/s2b-init.sqlThe driver runs the file before it returns each connection.
session_init_sql_filemust be the first option in the URL. -
Click Download missing driver files if the link appears, then Test Connection and OK.
-
Open a query console and run the example queries against
records.
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; pick the driver version on the data source's Drivers tab.
Startup script instead of the URL option
The IDE can run the statements itself: on the data source's Options tab, paste them into Startup script, which runs each time a connection is established. Keep the IF NOT EXISTS and OR REPLACE forms so the script can run more than once on the same database.
Either way, the script has to run on every connection. With an in-memory database, each JDBC connection the IDE opens (for introspection and for each console) is a separate, empty database. To keep the view between sessions instead, use a database file as the File and run the script once with CREATE PERSISTENT SECRET; the view stays in the file and the secret in ~/.duckdb/stored_secrets, unencrypted.
Read-only
The DuckDB driver cannot switch an open connection to read-only, so the data source's Read-only checkbox does not stop writes to a DuckDB database. For a database file, add the driver property duckdb.read_only = true on the Advanced tab once the views exist, and keep only the CREATE SECRET in the startup script. The storage key's permissions are what protect your bucket.
Warehouse data sources
If the data is already queryable in a warehouse, add a data source for that engine and query the tables or views its guide creates. Use a read-only database user or role.
| Engine | Data source | Fields | Tables from |
|---|---|---|---|
| Trino or Starburst | Trino | Host, port, user and password; catalog and schema optional | Trino |
| Amazon Athena | Amazon Athena, or a custom driver with AWS's Athena JDBC driver | Region, workgroup and S3 output location, for example jdbc:athena://Region=<region>;WorkGroup=<workgroup>;OutputLocation=s3://<results_bucket>/ | Amazon Athena |
| BigQuery | BigQuery | Project ID, default dataset, and authentication by service account key file, Google user account or application default credentials | BigQuery |
| ClickHouse | ClickHouse | Host, port, user, password and database | ClickHouse |
| PostgreSQL | PostgreSQL | Host, port, database, user and password | PostgreSQL |
| MySQL or MariaDB | MySQL or MariaDB | Host, port, database, user and password | MySQL |
Snowflake also has a DataGrip data source; use it once your records are in a Snowflake table. With AWS's Athena JDBC driver 3.x, the driver class is com.amazon.athena.jdbc.AthenaDriver and CredentialsProvider=DefaultChain uses your AWS profile or environment (Athena JDBC 3.x).
Notes
INSTALL httpfsdownloads the extension from DuckDB's extension repository the first time the script runs on a machine.- A view over bucket objects reads the bucket on every query. Filter on
dateto read fewer objects. - The init file or startup script holds the storage key in plain text, and the IDE saves the startup script with the data source settings. Use a read-only key, or
PROVIDER credential_chain.