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
Examples
The below is a uses the raw internal database connection via a API call.
This example registers a query, executes and returns an unpacked resultset and then unregisters the query.
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.
Last updated