# SQL queries

## 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 

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

### Examples

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

```java
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.

```java
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](https://duckdb.org/docs/api/java) on how to leverage their features.

*Return* - In-process database connection

*Exception -* Thrown when a connection cannot be made to the internal database&#x20;

***

`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.&#x20;

*Exception -* Thrown when table failed to be created

***Parameters***

<table><thead><tr><th width="200.33333333333331">Parameter</th><th width="367">Description</th><th>Type</th></tr></thead><tbody><tr><td>tableDefinition</td><td>Table SQL definition</td><td>String</td></tr></tbody></table>

***

`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***

<table><thead><tr><th width="200.33333333333331">Parameter</th><th width="367">Description</th><th>Type</th></tr></thead><tbody><tr><td>schema</td><td>Schema where table exists</td><td>String</td></tr><tr><td>table</td><td>Name of database table</td><td>String</td></tr><tr><td>fields</td><td>List of fields to create index upon</td><td>String[]</td></tr><tr><td>unique</td><td>Flag to indicate if a unique index is to be created. True for unique indexes false otherwise</td><td>Boolean</td></tr></tbody></table>

***

`TableMetaData getTableMetaData(final String tablename) throws FailedSQLProcessingException`

*Description -* Get table metadata, see [TableMetaData](#tablemetadata) section for class details

*Exception -* Thrown due to an internal database issue

***Parameters***

<table><thead><tr><th width="200.33333333333331">Parameter</th><th width="367">Description</th><th>Type</th></tr></thead><tbody><tr><td>table</td><td>Name of the table</td><td>String</td></tr></tbody></table>

***

`void dropTable(final String table) throws FailedSQLProcessingException`

*Description -* Drop the table from the database.

*Exception -* Thrown when table failed to be dropped

***Parameters***

<table><thead><tr><th width="200.33333333333331">Parameter</th><th width="367">Description</th><th>Type</th></tr></thead><tbody><tr><td>table</td><td>Name of the table to be dropped</td><td>String</td></tr></tbody></table>

***

`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***

<table><thead><tr><th width="200.33333333333331">Parameter</th><th width="367">Description</th><th>Type</th></tr></thead><tbody><tr><td>queryName</td><td>Name of the query to be executed</td><td>String</td></tr><tr><td>params</td><td>Array of parameters to be passed in to query</td><td>Object[]</td></tr></tbody></table>

***

### 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***

<table><thead><tr><th width="198.33333333333331">Parameter</th><th>Description</th><th>Type</th></tr></thead><tbody><tr><td>queryName</td><td>Name of query to be registered</td><td>String</td></tr><tr><td>query</td><td>Well formed SQL query</td><td>String</td></tr></tbody></table>

***

`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](#sqlqueryresults) for more information

*Exception - Thrown when a query execution fails*&#x20;

***Parameters***

<table><thead><tr><th width="200.33333333333331">Parameter</th><th width="367">Description</th><th>Type</th></tr></thead><tbody><tr><td>queryName</td><td>Name of the query to be executed</td><td>String</td></tr><tr><td>params</td><td>Array of parameters to be passed in to query</td><td>Object[]</td></tr></tbody></table>

***

`Map<String, String> getAvailableQueries()`

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

*Response -* Map query names to associated SQL&#x20;

***

`boolean unregisterQuery(String queryName)`

*Description* - Unregister a cached query

*Response - T*rue query unregistered false otherwise

***Parameters***

<table><thead><tr><th width="200.33333333333331">Parameter</th><th width="367">Description</th><th>Type</th></tr></thead><tbody><tr><td>queryName</td><td>Name of the query to be unregistered</td><td>String</td></tr></tbody></table>

***

## **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>` &#x20;

### TableMetaData

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


---

# Agent Instructions: Querying This Documentation

If you need additional information that is not directly available in this page, you can query the documentation dynamically by asking a question.

Perform an HTTP GET request on the current page URL with the `ask` query parameter:

```
GET https://docs.fractalworks.io/joule/developer-guides/builder-sdk/analytics-api/sql-queries.md?ask=<question>
```

The question should be specific, self-contained, and written in natural language.
The response will contain a direct answer to the question and relevant excerpts and sources from the documentation.

Use this mechanism when the answer is not explicitly present in the current page, you need clarification or additional context, or you want to retrieve related documentation sections.