Skip to main content

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 haveUse
No warehouse yetA DuckDB data source with a startup script
Trino, Starburst or Athena over the bucket or Iceberg tableA Trino or Athena data source, with the tables from Trino or Amazon Athena
BigQueryA BigQuery data source, with the tables from BigQuery
ClickHouseA ClickHouse data source, with the tables from ClickHouse
Records loaded into PostgreSQL or MySQLA PostgreSQL or MySQL data source, with the tables from PostgreSQL or MySQL
note

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.

  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, or PROVIDER credential_chain to use the AWS profile or environment variables the IDE was started with.

  2. Choose File > New > Data Source > DuckDB.

  3. Set Connection type to URL only and enter:

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

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

  4. Click Download missing driver files if the link appears, then Test Connection and OK.

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

EngineData sourceFieldsTables from
Trino or StarburstTrinoHost, port, user and password; catalog and schema optionalTrino
Amazon AthenaAmazon Athena, or a custom driver with AWS's Athena JDBC driverRegion, workgroup and S3 output location, for example jdbc:athena://Region=<region>;WorkGroup=<workgroup>;OutputLocation=s3://<results_bucket>/Amazon Athena
BigQueryBigQueryProject ID, default dataset, and authentication by service account key file, Google user account or application default credentialsBigQuery
ClickHouseClickHouseHost, port, user, password and databaseClickHouse
PostgreSQLPostgreSQLHost, port, database, user and passwordPostgreSQL
MySQL or MariaDBMySQL or MariaDBHost, port, database, user and passwordMySQL

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 httpfs downloads 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 date to 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.