SQL queries

Leverage the power of ANSI SQL within custom processors using the in-memory database

Overview

Joule embeds DuckDB, an in-memory database, in to the runtime process. The solution is ideal for supporting custom processor logic using various methods such as:

• Hosting and accessing custom reference data

• Scratchpad for stateful processing

• Ad-hoc custom complex queries

• Capture and exporting streaming events

SQLQueryInterface APIs

The below provides the required API documentation to leverage the internal in-memory database for your use cases.

Package

com.fractalworks.streams.sdk.analytics.sql.SQLQueryInterface

Examples

The below is a uses the raw internal database connection via a API call.

try (Statement statement = SQLQueryInterface.getConnection().createStatement()) {
    statement.execute(query);
} catch (SQLException e) {
    throw new FailedSQLProcessingException(String.format("Failed to execute query [%s]", query), e);
}

This example registers a query, executes and returns an unpacked resultset and then unregisters the query.

SQLQueryInterface.registerQuery("duckDBSetting","SELECT * FROM duckdb_settings();");
List<SQLQueryResult> results = SQLQueryInterface.executeSelectQuery("duckDBSetting", null);
SQLQueryInterface.unregisterQuery("duckDBSetting");

Database Ops

DuckDBConnection getConnection() throws SQLException

Description - Get an internal database connection. This will provide you the ability to interact directly using your own code. Visit the DuckDB JDBC documentation on how to leverage their features.

Return - In-process database connection

Exception - Thrown when a connection cannot be made to the internal database

---

void createSchema(final String schemaName) throws FailedSQLProcessingException

Description - Create a database schema namespace

Exception - Thrown when the schema cannot be created due to an internal database issue

---

Table Management

void createTable(final String tableDefinition) throws FailedSQLProcessingException

Description - Create a database table, if it does not exist, using the provided table SQL definition.

Exception - Thrown when table failed to be created

Parameters

Parameter	Description	Type
tableDefinition	Table SQL definition	String

---

boolean createIndex(final String schema, final String table, String[] fields, boolean unique)

Description - Create a unique index on table. If the index already exists it will be dropped and recreated.

Return - True if index created otherwise false

Exception - Thrown when index failed to be created

Parameters

Parameter	Description	Type
schema	Schema where table exists	String
table	Name of database table	String
fields	List of fields to create index upon	String[]
unique	Flag to indicate if a unique index is to be created. True for unique indexes false otherwise	Boolean

---

TableMetaData getTableMetaData(final String tablename) throws FailedSQLProcessingException

Description - Get table metadata, see TableMetaData section for class details

Exception - Thrown due to an internal database issue

Parameters

Parameter	Description	Type
table	Name of the table	String

---

void dropTable(final String table) throws FailedSQLProcessingException

Description - Drop the table from the database.

Exception - Thrown when table failed to be dropped

Parameters

Parameter	Description	Type
table	Name of the table to be dropped	String

---

void deleteTable(final String table, final String criteria) throws FailedSQLProcessingException

Description - Delete table records using the passed criteria

Exception - Thrown when failed to delete table

Parameters

Parameter	Description	Type
queryName	Name of the query to be executed	String
params	Array of parameters to be passed in to query	Object[]

---

Query Processing

void registerQuery(String queryName, String query) throws DuplicateQueryException

Description - Register a query in to the internal SQL engine. The query will be cached to improve runtime performance.

Exception - Thrown when the query name has already been used

Parameters

Parameter	Description	Type
queryName	Name of query to be registered	String
query	Well formed SQL query	String

---

List<SQLQueryResult> executeSelectQuery(String queryName, Object[] params) throws SQLException

Description - Execute a registered query with the passed parameters and return a list of query results

Response - List of SQLQueryResults, see SQLQueryResults for more information

Exception - Thrown when a query execution fails

Parameters

Parameter	Description	Type
queryName	Name of the query to be executed	String
params	Array of parameters to be passed in to query	Object[]

---

Map<String, String> getAvailableQueries()

Description - Get a list of available queries and the associated query SQL

Response - Map query names to associated SQL

---

boolean unregisterQuery(String queryName)

Description - Unregister a cached query

Response - True query unregistered false otherwise

Parameters

Parameter	Description	Type
queryName	Name of the query to be unregistered	String

---

Data Structures

We have kept the number of data structures to the minimum and as simple as possible.

SQLQueryResults

This class is a convenience class to hold the query results as a HashMap<String,Object>

TableMetaData

This class provides a DuckDB specific unwrapping of the metadata for a specific database table.