ODBC/JDBC (Beta) - PostgreSQL Wire Protocol

GroveStreams ODBC/JDBC Adapter Beta
PostgreSQL Wire Protocol Connector for BI Tools

Beta — This feature is available for early access. Multi-statement transactions are not yet supported (each statement autocommits); full transaction support is on the roadmap. Other limitations are documented below.

Overview

GroveStreams exposes a PostgreSQL v3 wire protocol endpoint that allows any ODBC or JDBC client to connect and query your data using standard SQL. This means enterprise BI tools such as Tableau, Power BI Desktop, DBeaver, Excel, Grafana, and any application with a PostgreSQL driver can query GroveStreams directly — no OData URLs, no API keys, no custom connectors required.

The adapter is read-only by default and exposes two kinds of SQL tables:

BASE TABLEs — Component templates via TEQ (Template Entity Queries). Each template appears as a table, each stream as a typed column, each component as a row. Returns the current (last) value of each stream.
VIEWs — GS SQL Views defined via CREATE VIEW or CREATE MATERIALIZED VIEW. Views can be internal (querying organization data) or external (querying a remote database via JDBC), and materialized (cached) or non-materialized (live). They give BI tools access to temporal data — historical ranges, roll-ups, cycles, and aggregations — without custom query syntax. See Views below.

Your existing GroveStreams RBAC permissions (organization roles, group capabilities, and folder-level access controls) are fully enforced on both table types.

How It Works

This is not PostgreSQL. GroveStreams does not run PostgreSQL or any PostgreSQL-derived database. The adapter is a thin wire-protocol layer in front of the GS SQL query engine — the same engine that powers the GroveStreams REST API, the OData connector, and the AI Chat assistant. By speaking the PostgreSQL v3 wire protocol on the network, GroveStreams lets you reuse any existing PostgreSQL driver (ODBC, JDBC, libpq, Npgsql, psycopg2) without writing GS-specific code. The data, storage, query planner, and execution engine remain GroveStreams.

GroveStreams listens on TCP port 5433, speaking the PostgreSQL v3 wire protocol.
BI tools connect using any standard PostgreSQL ODBC or JDBC driver.
You authenticate with your GroveStreams email and password (not an API key). Full three-tier RBAC is enforced.
The ODBC/JDBC Access capability must be enabled for the user's group (Admin → Security → Users and Groups → edit group → Capabilities). Organization owners always have access. See Access Permissions.
The database name in the connection string is your GroveStreams Organization UID.
Queries are executed by the GS SQL engine over your existing organization data — component templates appear as tables. Use standard SQL (SELECT, WHERE, JOIN, GROUP BY, ORDER BY) to query them.
The connection is encrypted with TLS (TLSv1.2/1.3).

Connection Settings

Use these settings in any PostgreSQL ODBC/JDBC driver or BI tool:

Setting	Value
Host	`grovestreams.com`
Port	`5433`
Database	Your Organization UID (found in Admin → Organization → UID)
Username	Your GroveStreams email address
Password	Your GroveStreams password
SSL Mode	`require` (recommended)

Schema & Tables

Schema mapping (catalogs ↔ PG schemas)

GS catalogs surface as PG schemas over the wire. PG clients (DBeaver, DataGrip, Power BI, dbt, psql) browse the schema tree as: connection (database = your org UID) → schemas → tables.

GS catalog	PG schema	Notes
(no catalog assigned)	`public`	GS auto-assigns the literal "default" catalog when none is explicitly chosen.
`default` (literal name)	`public`	Collapsed at the wire layer for PG-client compatibility (case-insensitive).
any other name (e.g. `sampledb`)	same name (e.g. `sampledb`)	Real catalog names pass through unchanged.
(views)	`public`	Saved-query views have no catalog field; they always appear under `public`.

The mapping is bidirectional: queries against public.<table> resolve to default-catalog templates, so SELECT * FROM public.customers and SELECT * FROM customers return identical results. dbt's profiles.yml default of schema: public works without configuration.

Each component template in your organization is exposed as a SQL table. The table name matches the template name.

Every template table includes these built-in columns plus one column per stream defined in the template:

Column	Type	Description
`_component_uid`	text	Component UID (unique identifier)
`_component_id`	text	Component ID (user-assigned identifier)
`_component_name`	text	Component display name
`_folder_path`	text	Component folder path
`_component_created_date`	timestamp with time zone	Component creation date
(stream columns)	varies	One column per template stream, typed according to the stream's value type (e.g., double precision, bigint, text, boolean)

The table below shows how GroveStreams stream value types are reported to ODBC/JDBC clients as PostgreSQL types over the wire. BI tools see these PG types in their schema browsers and generate appropriate SQL accordingly.

Note about GS SQL types. The wire-level types below are richer than the type keywords GS SQL accepts in CAST or CREATE TABLE statements. GS SQL itself recognizes BOOLEAN, LONG, DOUBLE, STRING, and BIGDECIMAL (plus the PostgreSQL aliases int8, int4, text, numeric, etc., added in the May 2026 release). Stream value types like Short, Float, Latitude, and Direction360 are stored at the stream level but coerce to one of the GS SQL types when referenced in a query expression.

Stream Value Type	PostgreSQL Type
Boolean	boolean
Short	smallint
Integer	integer
Long	bigint
Float	real
Double, Latitude, Longitude, Elevation, Direction360	double precision
Big Decimal	numeric
String, File	text
DateTime	timestamp with time zone
Byte Array	bytea

Views

GS SQL Views appear as SQL VIEWs over the ODBC/JDBC adapter. Views are the primary way to expose temporal data — historical ranges, roll-ups, cycles, and cross-stream correlations — to BI tools that can't generate GS SQL temporal parameters directly.

Why Views?

Standard BI tools (Tableau, Power BI, Excel) can't generate GS SQL temporal parameters like range(sd=-1d) or cycle=Weekly. Views solve this: define the temporal query once via GS SQL DDL, and the result becomes a standard SQL VIEW that any BI tool can SELECT from like a regular table.

View kinds

GS SQL Views are first-class objects defined and managed via standard CREATE VIEW / CREATE MATERIALIZED VIEW / DROP VIEW DDL. Two orthogonal axes determine how a view behaves:

Internal vs external — an internal view queries data inside the GroveStreams organization (TEQ template tables, system tables, other views). An external view queries a remote database via a JDBC CONNECTION (e.g., a PostgreSQL or MySQL data source registered in your org).
Materialized vs non-materialized — a materialized view caches results for fast repeated reads. A non-materialized view runs the underlying query every time it is referenced (current data, no cache).

Both internal and external views can be either materialized or non-materialized.

Creating a View

-- Internal non-materialized view: runs every time it is queried
CREATE VIEW Active_High_Temp_Sensors AS
SELECT _component_id, _component_name, temperature
FROM weather_station
WHERE temperature > 80;

-- Internal materialized view: caches results, refresh with REFRESH MATERIALIZED VIEW
CREATE MATERIALIZED VIEW Daily_Sensor_Avg AS
SELECT _component_name, AVG(temperature) AS avg_temp
FROM weather_station
GROUP BY _component_name;

-- External non-materialized view: each SELECT proxies to the remote database
CREATE VIEW External_Customer_Orders AS
SELECT customer_id, order_date, total
FROM remote_customers
CONNECTION my_postgres_conn;

See DDL Reference for the full CREATE VIEW grammar.

Schema Discovery

-- List all tables and views
SELECT table_name, table_type FROM information_schema.tables

-- Show only VIEWs
SELECT table_name FROM information_schema.tables WHERE table_type = 'VIEW'

-- Show columns for a specific VIEW
SELECT column_name, data_type FROM information_schema.columns WHERE table_name = 'your_view_name'

Querying a View

-- Read from the view
SELECT * FROM Active_High_Temp_Sensors

-- Filter and sort
SELECT region, total_kwh FROM Monthly_Energy_Report WHERE total_kwh > 1000 ORDER BY total_kwh DESC

-- Join a view with a template table
SELECT v.site_name, v.avg_temperature, w.humidity
FROM Daily_Sensor_Avg v
JOIN weather_station w ON v.site_name = w._component_name

Visibility

Only views the connected user has read/traverse permissions to are visible.
Organization owners see every view.

Refresh (materialized views)

Materialized views serve cached results. To update:

REFRESH MATERIALIZED VIEW your_view_name — refresh on demand via SQL.
Schedule periodic refreshes via the GroveStreams scheduler (Tools → Views → Schedule).
Use the HTTP API to trigger a refresh programmatically.

Non-materialized views always reflect current data — no refresh needed.

SQL Query Examples

The following examples assume a component template named weather_station with streams temperature (double), humidity (double), and city (text).

Select all stations

SELECT * FROM weather_station

Filter by stream value

SELECT _component_name, temperature, humidity
FROM weather_station
WHERE temperature > 90
ORDER BY temperature DESC

Aggregate across components

SELECT city, AVG(temperature) AS avg_temp, MAX(humidity) AS max_humidity
FROM weather_station
GROUP BY city
ORDER BY avg_temp DESC

Count components per folder

SELECT _folder_path, COUNT(*) AS station_count
FROM weather_station
GROUP BY _folder_path

Join two templates

Assuming a second template solar_panel with a site_name stream:

SELECT w._component_name, w.temperature, s.power_output
FROM weather_station w
JOIN solar_panel s ON w.city = s.site_name
WHERE w.temperature > 80

Schema discovery (used automatically by BI tools)

-- List all available tables
SELECT table_name FROM information_schema.tables

-- List columns for a specific table
SELECT column_name, data_type
FROM information_schema.columns
WHERE table_name = 'weather_station'

Connecting BI Tools

Tableau Desktop

Open Tableau Desktop. Select Connect → To a Server → PostgreSQL.
Enter the connection settings above.
Click Sign In. Your component templates will appear as tables.
Drag tables onto the canvas and build visualizations as you would with any PostgreSQL data source.

Power BI Desktop

Open Power BI Desktop. Select Get Data → Database → PostgreSQL database.
Enter Server: grovestreams.com:5433 and Database: your Organization UID.
Select Database for credentials and enter your GroveStreams email and password.
Choose the template tables you want to import and click Load or Transform Data.

DBeaver

Open DBeaver. Select Database → New Database Connection → PostgreSQL.
Enter Host: grovestreams.com, Port: 5433, Database: your Organization UID.
Enter your GroveStreams email as username and password.
On the SSL tab, check Use SSL and set SSL Mode to require.
Click Test Connection, then Finish.

Excel (via ODBC)

Install the PostgreSQL ODBC driver for your operating system.
Create a new ODBC Data Source (DSN) with the connection settings above.
In Excel, select Data → Get Data → From Other Sources → From ODBC.
Select your DSN. Choose the template tables and click Load.

Grafana

In Grafana, go to Configuration → Data Sources → Add data source → PostgreSQL.
Enter the connection settings. Set TLS/SSL Mode to require.
Click Save & Test.
Create dashboards using SQL queries against your template tables.

Any PostgreSQL Client (psql, pgAdmin, DataGrip, etc.)

psql "host=grovestreams.com port=5433 dbname=YOUR_ORG_UID user=you@example.com sslmode=require"

JDBC Connection String

For Java applications and JDBC-based tools:

jdbc:postgresql://grovestreams.com:5433/YOUR_ORG_UID?sslmode=require

Driver class: org.postgresql.Driver

Maven dependency:

<dependency>
    <groupId>org.postgresql</groupId>
    <artifactId>postgresql</artifactId>
    <version>42.7.1</version>
</dependency>

Java example:

import java.sql.*;

String url = "jdbc:postgresql://grovestreams.com:5433/YOUR_ORG_UID?sslmode=require";
Connection conn = DriverManager.getConnection(url, "you@example.com", "your_password");

Statement stmt = conn.createStatement();
ResultSet rs = stmt.executeQuery("SELECT _component_name, temperature FROM weather_station WHERE temperature > 80");

while (rs.next()) {
    System.out.println(rs.getString("_component_name") + ": " + rs.getDouble("temperature"));
}

rs.close();
stmt.close();
conn.close();

Python Example (psycopg2)

import psycopg2

conn = psycopg2.connect(
    host="grovestreams.com",
    port=5433,
    dbname="YOUR_ORG_UID",
    user="you@example.com",
    password="your_password",
    sslmode="require"
)

cur = conn.cursor()
cur.execute("SELECT _component_name, temperature, humidity FROM weather_station ORDER BY temperature DESC LIMIT 10")

for row in cur.fetchall():
    print(f"{row[0]}: temp={row[1]}, humidity={row[2]}")

cur.close()
conn.close()

Write Access (DML/DDL)

By default, the ODBC/JDBC adapter is read-only. All write operations (INSERT, UPDATE, DELETE) and DDL operations (CREATE TABLE, ALTER TABLE, DROP TABLE) are blocked.

Organization administrators can selectively enable write operations via ODBC SQL restriction switches in the organization settings (Admin → Organization → Settings → ODBC SQL Restrictions). These switches are independent of (and in addition to) the GSQL SQL restriction switches — both the GSQL switch and the ODBC switch must allow an operation for it to succeed via ODBC.

ODBC SQL Restriction Switches

Setting	Default	Description
ODBC Allow Insert	Off	INSERT OR REPLACE INTO system.sample (sample data insertion)
ODBC Allow Update	Off	UPDATE system.sample (sample data updates)
ODBC Allow Delete	Off	DELETE FROM system.sample (sample data deletion)
ODBC Allow Insert Entity	Off	INSERT INTO <template> (create components)
ODBC Allow Update Entity	Off	UPDATE <template> (update component stream values)
ODBC Allow Delete Entity	Off	DELETE FROM <template> (delete components)
ODBC Allow Create Table	Off	CREATE TABLE (create new templates)
ODBC Allow Alter Table	Off	ALTER TABLE (modify templates)
ODBC Allow Drop Table	Off	DROP TABLE (delete templates)
ODBC Allow Drop Table Cascade	Off	DROP TABLE CASCADE (delete template and all components)
ODBC Allow Create Other Tools	Off	CREATE CYCLE, STREAM GROUP, etc.
ODBC Allow Alter Other Tools	Off	ALTER CYCLE, STREAM GROUP, etc.
ODBC Allow Drop Other Tools	Off	DROP CYCLE, STREAM GROUP, etc.
ODBC Allow Run Other Tools	Off	RUN RUNNABLE

Dual-gate security: For an operation to succeed via ODBC, both the corresponding GSQL switch and the ODBC switch must be enabled. For example, if GSQL Allow Insert is On but ODBC Allow Insert is Off, INSERT via ODBC will be rejected. Folder-level RBAC permissions are also enforced as usual.

Write Examples

-- Insert sample data (requires ODBC Allow Insert = On)
INSERT OR REPLACE INTO system.sample (_COMPONENT_ID, _ID, _TIME, _SAMPLE) VALUES ('Sensor_A', 'temperature', 1710000000000, 22.5)

-- Create a component (requires ODBC Allow Insert Entity = On)
INSERT INTO weather_station (_component_id, temperature, humidity) VALUES ('station_42', 22.5, 60.0)

-- Update component stream values (requires ODBC Allow Update Entity = On)
UPDATE weather_station SET temperature = 23.0 WHERE _component_id = 'station_42'

-- Delete a component (requires ODBC Allow Delete Entity = On)
DELETE FROM weather_station WHERE _component_id = 'station_42'

-- Create a template (requires ODBC Allow Create Table = On)
CREATE TABLE new_sensor (temperature DOUBLE, humidity DOUBLE)

Details & Limitations

Read-only by default: All write operations are blocked unless explicitly enabled via the ODBC SQL restriction switches in org settings. TRUNCATE is never supported.
Template tables (BASE TABLE): Return the most recent (last) value for each stream. For temporal/historical data, use Views (see Views above).
Materialized View data is cached: Materialized views serve pre-computed results. Use REFRESH MATERIALIZED VIEW or a scheduled refresh to update the cache. Non-materialized views always reflect current data.
Authentication: Uses your GroveStreams username and password. Full RBAC is enforced — folder-level read/traverse permissions are checked. API keys are not used for ODBC/JDBC connections.
ODBC/JDBC Capability: The user's group must have the ODBC/JDBC Access capability enabled. This is disabled by default. Organization owners bypass this check. Enable it via Admin → Security → Users and Groups → edit group → Capabilities tab.
2FA: Two-factor authentication is not prompted for ODBC/JDBC connections (same behavior as OAuth).
Sessions: Connections maintain a session that expires after 4 hours of inactivity. Most BI tools reconnect automatically.
TLS: The server supports TLSv1.2 and TLSv1.3. SSL mode require is recommended.
Transactions: BEGIN, COMMIT, and ROLLBACK are accepted but have no effect (all operations are auto-committed).
SET commands: SET statements (e.g., SET client_encoding TO 'UTF8') are accepted and acknowledged for driver compatibility but do not change server behavior.
Extended Query Protocol: The adapter supports both the Simple Query and Extended Query (Parse/Bind/Execute) protocols used by JDBC drivers and tools like Tableau.
PostgreSQL version: Reports as PostgreSQL 14.0 in the wire-protocol startup parameters — this is purely for driver compatibility (some drivers branch on server version). GroveStreams does not run PostgreSQL or any PostgreSQL-derived database. The adapter is a wire-protocol shim in front of the GS SQL query engine; the data, storage, planner, and execution all remain GroveStreams.

ODBC/JDBC vs. OData

Both connectors provide BI tool access to GroveStreams data. Choose the one that fits your workflow:

Feature	ODBC/JDBC (this page)	OData
Protocol	PostgreSQL wire protocol	HTTP/REST (OData V4)
Authentication	GS username/password (full RBAC)	API Key or OAuth2
Data source	Component templates (live) + GS SQL Views (live or materialized; internal or external)	Query folder results (cached)
Query language	Standard SQL	OData query parameters ($filter, $top, etc.)
Schema discovery	Automatic (information_schema)	Automatic (OData $metadata)
Best for	Tableau, Power BI Desktop, DBeaver, Grafana, psql, custom JDBC/ODBC apps	Power BI Report Builder, SAP, tools requiring HTTP-only access
Setup	Standard PostgreSQL driver (already installed in most tools)	Create query folder, API key, and OData URL

Troubleshooting

Connection refused: Verify that you are connecting to port 5433 (not the default PostgreSQL port 5432).
Authentication failed: Ensure you are using your GroveStreams email and password, not an API key. Verify the password works by signing in to grovestreams.com.
"ODBC/JDBC access is not enabled": Your administrator must enable the ODBC/JDBC Access capability on your group. Go to Admin → Security → Users and Groups, edit the group, and check ODBC/JDBC Access on the Capabilities tab.
No tables visible: The database name must be your Organization UID (not your org name). Find it under Admin → Organization → UID.
Permission denied on a table: Your user must have Read and Traverse permissions on the component folder containing the template's components.
"ODBC ... disabled" error: The operation is blocked by the ODBC SQL restriction switches. Ask your org administrator to enable the appropriate setting in Admin → Organization → Settings → ODBC SQL Restrictions. Both the GSQL switch and the ODBC switch must be enabled.
SSL errors: Set SSL mode to require (not verify-full). If your tool does not support SSL, contact support.
Query timeout or slow results: Large templates with many components may take longer. Use WHERE clauses and LIMIT to reduce result size.
Column not found: Stream names are case-sensitive. Verify the exact stream name using SELECT column_name FROM information_schema.columns WHERE table_name = 'your_template'.