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

ParameterDescriptionType

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

ParameterDescriptionType

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

ParameterDescriptionType

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

ParameterDescriptionType

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

ParameterDescriptionType

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

ParameterDescriptionType

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

ParameterDescriptionType

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

ParameterDescriptionType

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.

Last updated