Apache Ignite SQL Documentation

The Apache Ignite SQL Developer Hub

Welcome to the Apache Ignite SQL developer hub. You'll find comprehensive guides and documentation to help you start working with Apache Ignite SQL as quickly as possible, as well as support if you get stuck. Let's jump right in!

Get Started    

JDBC Driver

Ignite JDBC Thin Driver

Ignite is shipped with JDBC drivers that allow processing distributed data using standard SQL statements like SELECT, INSERT, UPDATE or DELETE directly from the JDBC side.

Presently, there are two drivers supported by Ignite: the lightweight and easy to use JDBC Thin Driver described in this documentation and the one that interacts with the cluster by means of a client node:

JDBC Thin Driver

The JDBC Thin driver is a default and lightweight driver provided by Ignite. To start using the driver, just add ignite-core-{version}.jar to an application's classpath.

The driver connects to one of the cluster nodes and forwards all the queries to it for final execution. The node handles the query distribution and the result's aggregations. Then, the result is sent back to the client application.

The JDBC connection string may be formatted with one of two patterns: URL query or semicolon:

// URL query pattern
jdbc:ignite:thin://<hostAndPortRange0>[,<hostAndPortRange1>]...[,<hostAndPortRangeN>][/schema][?<params>]

hostAndPortRange := host[:port_from[..port_to]]

params := param1=value1[&param2=value2]...[&paramN=valueN]

// Semicolon pattern
jdbc:ignite:thin://<hostAndPortRange0>[,<hostAndPortRange1>]...[,<hostAndPortRangeN>][;schema=<schema_name>][;param1=value1]...[;paramN=valueN]

  • host is required and defines the host of the cluster node to connect to.
  • port_from the beginning of the port range to open the connection. 10800 is used by default if this parameter is omitted.
  • port_to is optional. It is set to the port_from value by default if this parameter is omitted.
  • schema schema name to access. PUBLIC is used by default. This name should correspond to SQL ANSI-99 standard. Non-quoted identifiers are not case sensitive. Quoted identifiers are case sensitive. When semicolon format is used, the schema may be defined as a parameter with name schema.
  • <params> are optional.

The name of the driver's class is org.apache.ignite.IgniteJdbcThinDriver. For instance, this is how you can open a JDBC connection to the Ignite cluster node listening on IP address 192.168.0.50:

// Register JDBC driver.
Class.forName("org.apache.ignite.IgniteJdbcThinDriver");
 
// Open the JDBC connection.
Connection conn = DriverManager.getConnection("jdbc:ignite:thin://192.168.0.50");
  

Put the JDBC URL in quotes when connecting from bash

Make sure to put the connection URL in double quotes (" ") when connecting from a bash environment, for example: "jdbc:ignite:thin://[address]:[port];user=[username];password=[password]"

Parameters

The table below lists all the parameters that are accepted and supported by the JDBC connection string:

Parameters
Description
Default value

user

Username for SQL Connection. This parameter is required if authentication is enabled on the server.

See Authentication and CREATE user docs for more details on how to enable authentication and create a user, respectively.

ignite

password

Password for SQL Connection. This parameter is required if authentication is enabled on the server.

See Authentication and CREATE user docs for more details on how to enable authentication and create a user, respectively.

ignite

distributedJoins

Whether to use distributed joins for non-collocated data.

false

enforceJoinOrder

Whether to enforce join order of tables in the query. If set to true query optimizer will not reorder tables in the join.

false

collocated

Whether your data is co-located. Whenever Ignite executes a distributed query, it sends sub-queries to individual cluster members. If you know in advance that the elements of your query selection are collocated on the same node, Ignite can make significant performance and network optimizations.

false

replicatedOnly

Whether the query contains only replicated tables. This is a hint for potentially more effective execution.

false

autoCloseServerCursor

Whether to close server-side cursor automatically when last piece of result set is retrieved. When enabled, a call to ResultSet.close() will not require a network call, this could improve performance. However, if the server-side cursor is already closed, you may get an exception when trying to call ResultSet.getMetadata(). This is why it defaults to false.

false

socketSendBuffer

Socket send buffer size. When set to 0, OS default will be used.

0

socketReceiveBuffer

Socket receive buffer size. When set to 0, OS default will be used.

0

tcpNoDelay

Whether to use TCP_NODELAY option.

true

lazy

Lazy query execution.

By default, Ignite attempts to fetch the whole query result set to memory and send it to the client. For small and medium result sets, this provides optimal performance and minimize duration of internal database locks, thus increasing concurrency.

However, if the result set is too big to fit in the available memory, then it can lead to excessive GC pauses and even OutOfMemoryError. Use this flag to tell Ignite to fetch the result set lazily, thus minimizing memory consumption at the cost of a moderate performance hit.

false

skipReducerOnUpdate

Enables server side updates.

When Ignite executes a DML operation, it fetches all the affected intermediate rows for analysis to the query initiator (also known as reducer), and only then prepares batches of updated values that will be sent to remote nodes.

This approach might affect performance, and can saturate the network if a DML operation has to move many entries over it.

Use this flag to tell Ignite to perform all intermediate rows analysis and updates "in-place" on corresponding remote data nodes.

Defaults to false, meaning that intermediate results will be fetched to the query initiator first.

false

sslMode

Enables SSL connection. Available modes:

  • require: SSL protocol is enabled on the client. Only SSL connection is available.
  • disable: SSL protocol is disabled on the client. Only plain connection is supported.

disable

sslProtocol

Protocol name for secure transport. If not specified, TLS protocol will be used. Protocols implementations supplied by JSEE: SSLv3 (SSL), TLSv1 (TLS), TLSv1.1, TLSv1.2

TLS

sslKeyAlgorithm

Key manager algorithm to be used to create a key manager. Note that in most cases the default value works fine.
Algorithms implementations supplied by JSEE: PKIX (X509 or SunPKIX), SunX509.

sslClientCertificateKeyStoreUrl

URL of the client key store file.
This is a mandatory parameter since SSL context cannot be initialized without a key manager.

If sslMode is require and the key store URL isn't specified by Ignite properties, the value of the JSSE property javax.net.ssl.keyStore will be used.

The value of the javax.net.ssl.keyStore system property.

sslClientCertificateKeyStorePassword

Client key store password.

If sslMode is require and the key store password isn't specified by Ignite properties, the JSSE property javax.net.ssl.keyStorePassword will be used.

The value of the javax.net.ssl.keyStorePassword system property.

sslClientCertificateKeyStoreType

Client key store type used in context initialization.

If sslMode is require and the key store type isn't specified by Ignite properties, the JSSE property javax.net.ssl.keyStoreType will be used.

The value of the javax.net.ssl.keyStoreType system property.
If the system property is not defined, the default value is JKS.

sslTrustCertificateKeyStoreUrl

URL of the trust store file. This is an optional parameter; however, one of sslTrustCertificateKeyStoreUrl, sslTrustAll properties must be set.

If sslMode is require and the trust store URL isn't specified by Ignite properties, the JSSE property javax.net.ssl.trustStore will be used.

The value of the javax.net.ssl.trustStore system property.

sslTrustCertificateKeyStorePassword

Trust store password.

If sslMode is require and the trust store password isn't specified by Ignite properties, the JSSE property javax.net.ssl.trustStorePassword will be used.

The value of the javax.net.ssl.trustStorePassword system property

sslTrustCertificateKeyStoreType

Trust store type.

If sslMode is require and the trust store type isn't specified by Ignite properties, the JSSE property javax.net.ssl.trustStoreType will be used.

The value of the javax.net.ssl.trustStoreType system property.
If the system property is not defined the default value is JKS

sslTrustAll

Disables server's certificate validation. Sets to true to trust any server certificate (revoked, expired or self-signed SSL certificates).

Note: Do not enable this option in production on a network you do not entirely trust. Especially anything going over the public internet.

false

sslFactory

Class name of the custom implementation of the Factory<SSLSocketFactory>.

If sslMode is require and factory is specified, the custom factory will be used instead of JSSE socket factory. In this case, other SSL properties will be ignored.

null

Additional Connection String Examples

  • jdbc:ignite:thin://myHost - connect to myHost on the port 10800 with all defaults.
  • jdbc:ignite:thin://myHost:11900 - connect to myHost on custom port 11900 with all defaults;.
  • jdbc:ignite:thin://myHost:11900;user=ignite;password=ignite - connect to myHost on custom port 11900 with user credentials for authentication.
  • jdbc:ignite:thin://myHost:11900;distributedJoins=true&autoCloseServerCursor=true - connect to myHost on custom port 11900 with enabled distributed joins and autoCloseServerCursor optimization.
  • jdbc:ignite:thin://myHost:11900/myschema; - connect to myHost on custom port 11900 and access to MYSCHEMA.
  • jdbc:ignite:thin://myHost:11900/"MySchema";lazy=false - connect to myHost on custom port 11900 with disabled lazy query execution and access to MySchema (schema name is case sensitive).

Multiple Endpoints

It's possible to set multiple connection endpoints in the connection string to enable automatic failover if a current connection is broken. JDBC driver randomly picks an address from the list to connect it. If the original connection fails, the driver will select another address from the list until the connection is restored. JDBC stops reconnecting and throws an Exception if all the endpoints are unreachable.

The example below shows how to pass three addresses via the connection string:‚Äč

// Register JDBC driver.
Class.forName("org.apache.ignite.IgniteJdbcThinDriver");
 
// Open the JDBC connection passing several connection endpoints.
Connection conn = DriverManager.getConnection(
  "jdbc:ignite:thin://192.168.0.50:101,192.188.5.40:101, 192.168.10.230:101");

Cluster Configuration

In order to accept and process requests from JDBC Thin Driver, a cluster node binds to a local network interface on port 10800 and listens to incoming requests.

Use ClientConnectorConfiguration, set via IgniteConfiguration, to change any parameters:

IgniteConfiguration cfg = new IgniteConfiguration()
    .setClientConnectorConfiguration(new ClientConnectorConfiguration());
<bean id="ignite.cfg" class="org.apache.ignite.configuration.IgniteConfiguration">
  <property name="clientConnectorConfiguration">
    <bean class="org.apache.ignite.configuration.ClientConnectorConfiguration" />
  </property>
</bean>

The following parameters are supported:

Properties
Description
Default

host

Host name or IP address to bind to. When set to null, binding is done to localhost.

null

port

TCP port to bind to. If the specified port is already in use, Ignite will try to find another available port using portRange property.

10800

portRange

Defines number of ports to try to bind to. E.g. if the port is set to 10800 and portRange is 100, then server will consequentially try to bind to any port in the [10800, 10900] range until it finds a free port.

100

maxOpenCursorsPerConnection

Maximum number of cursors that can be opened simultaneously for a single connection.

128

threadPoolSize

Number of request-handling threads in the thread pool.

MAX(8, CPU cores)

socketSendBufferSize

Size of the TCP socket send buffer. When set to 0, the system default value is used.

0

socketReceiveBufferSize

Size of the TCP socket receive buffer. When set to 0, the system default value is used.

0

tcpNoDelay

Whether to use TCP_NODELAY option.

true

idleTimeout

Idle timeout for client connections.
Clients will automatically be disconnected from the server after being idle for the configured timeout.
When this parameter is set to zero or a negative value, idle timeout will be disabled.

0

isJdbcEnabled

Whether access through JDBC is enabled.

true

isThinClientEnabled

Whether access through thin client is enabled.

true

sslEnabled

If SSL is enabled, only SSL client connections are allowed. The node allows only one mode of connection: SSL or plain. A node cannot receive both types of client connections. But this option can be different for different nodes in the cluster.

false

useIgniteSslContextFactory

Whether to use SSL context factory from Ignite configuration (see IgniteConfiguration.sslContextFactory).

true

sslClientAuth

Whether client authentication is required.

false

sslContextFactory

The class name that implements Factory<SSLContext> to provide node-side SSL. See here for more information.

null

JDBC thin driver is not thread safe

The JDBC objects Connections, Statements, and ResultSet are not thread safe.
You must not use all statements and results sets of a single JDBC Connection in multiple threads.

The JDBC thin driver guards against concurrency. If concurrent access is detected, then an exception (SQLException) is produced with the following message:
"Concurrent access to JDBC connection is not allowed [ownThread=<guard_owner_thread_name>, curThread=<current_thread_name>]",
SQLSTATE="08006"

Using SSL

Ignite thin JDBC driver allows you to use SSL socket communication to provide a secure connection between JDBC driver and Ignite node (includes the initial handshake).

See the ssl* parameters of the JDBC driver, and ssl* parameters and useIgniteSslContextFactory of the ClientConnectorConfiguration to gather detailed information.

The default implementation is based on JSSE, and works through two Java keystore files:

  • sslClientCertificateKeyStoreUrl - the client certificate keystore holds the keys and certificate for the client.
  • sslTrustCertificateKeyStoreUrl - the trusted certificate keystore contains the certificate information to validate the server's certificate.

The trusted store is an optional parameter, however one of the parameters sslTrustCertificateKeyStoreUrl or sslTrustAll must be set up.

Using the `sslTrustAll` option

Do not enable this option in production on a network you do not entirely trust. Especially anything going over the public internet.

If you want to use your own implementation or method to configure the SSLSocketFactory, you can use the sslFactory parameter of the JDBC driver. It is a string that must contain the name of the class that implemented the interface Factory<SSLSocketFactory>. The class must be available for the class loader of the JDBC driver.

Ignite DataSource

The DataSource object is used as a deployed object that can be located by logical name via JNDI naming service. Ignite's JDBC driver's org.apache.ignite.IgniteJdbcThinDataSource implements a JDBC DataSource interface allowing you to utilize DataSource interface instead.

In addition to generic DataSource properties, IgniteJdbcThinDataSource supports all the Ignite-specific properties that can be passed into a JDBC connection string. For instance, the distributedJoins property can be tweaked via the IgniteJdbcThinDataSource#setDistributedJoins() method.

Refer to JavaDocs for more details.

Example

To start processing the data located in the cluster, you need to create a JDBC Connection object using one of the methods below:

// Open the JDBC connection via DriverManager.
Connection conn = DriverManager.getConnection("jdbc:ignite:thin://192.168.0.50");

// Or open connection via DataSource.
IgniteJdbcThinDataSource ids = new IgniteJdbcThinDataSource();
ids.setUrl("jdbc:ignite:thin://192.168.0.50");
ids.setDistributedJoins(true);
        
Connection conn2 = ids.getConnection();

Then you can execute SQL SELECT queries of your choice:

// Query names of all people.
ResultSet rs = conn.createStatement().executeQuery("select name from Person");
 
while (rs.next()) {
    String name = rs.getString(1);
    ...
}
 
// Query people with specific age using prepared statement.
PreparedStatement stmt = conn.prepareStatement("select name, age from Person where age = ?");
 
stmt.setInt(1, 30);
 
ResultSet rs = stmt.executeQuery();
 
while (rs.next()) {
    String name = rs.getString("name");
    int age = rs.getInt("age");
    ...
}

Moreover, you can modify the data by using DML statements.

INSERT

// Insert a Person with a Long key.
PreparedStatement stmt = conn.prepareStatement("INSERT INTO Person(_key, name, age) VALUES(CAST(? as BIGINT), ?, ?)");
 
stmt.setInt(1, 1);
stmt.setString(2, "John Smith");
stmt.setInt(3, 25);

stmt.execute();

MERGE

// Merge a Person with a Long key.
PreparedStatement stmt = conn.prepareStatement("MERGE INTO Person(_key, name, age) VALUES(CAST(? as BIGINT), ?, ?)");
 
stmt.setInt(1, 1);
stmt.setString(2, "John Smith");
stmt.setInt(3, 25);
 
stmt.executeUpdate();

UPDATE

// Update a Person.
conn.createStatement().
  executeUpdate("UPDATE Person SET age = age + 1 WHERE age = 25");

DELETE

conn.createStatement().execute("DELETE FROM Person WHERE age = 25");

Streaming

The Ignite JDBC driver allows streaming data in bulk using the SET command. See the SET command documentation for more information.

JDBC Driver


Ignite JDBC Thin Driver

Suggested Edits are limited on API Reference Pages

You can only suggest edits to Markdown body content, but not to the API spec.