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    

Connection String and DSN

Connection String Format

Apache Ignite ODBC Driver supports standard connection string format. Here is the formal syntax:

connection-string ::= empty-string[;] | attribute[;] | attribute; connection-string
empty-string ::=
attribute ::= attribute-keyword=attribute-value | DRIVER=[{]attribute-value[}]
attribute-keyword ::= identifier
attribute-value ::= character-string

In simple terms, an ODBC connection URL is a string with parameters of the choice separated by semicolon.

Supported Arguments

Apache Ignite ODBC driver supports and uses several connection string/DSN arguments. All parameter names are case-insensitive - ADDRESS, Address and address all are valid parameter names and refer to the same parameter. If an argument is not specified, the default value is used. The exception to this rule is the ADDRESS attribute. If it is not specified, SERVER and PORT attributes are used instead.

Attribute keywordDescriptionDefault value
ADDRESSAddress of the remote node to connect to. The format is: [:]. For example: localhost, example.com:12345, 127.0.0.1, 192.168.3.80:5893.

If this attribute is specified, then SERVER and PORT arguments are ignored.
SERVERAddress of the node to connect to.

This argument value is ignored if ADDRESS argument is specified.
PORTPort on which OdbcProcessor of the node is listening.

This argument value is ignored if ADDRESS argument is specified.
10800
USERUsername 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 user, respectively.
Empty string
PASSWORDPassword 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 user, respectively.
Empty string
SCHEMASchema name.PUBLIC
DSNDSN name to connect to.
PAGE_SIZENumber of rows returned in response to a fetching request to the data source. Default value should be fine in most cases. Setting low value can result in slow data fetching while setting high value can result in additional memory usage by the driver, and additional delay when the next page is being retrieved.1024
DISTRIBUTED_JOINSEnables non-collocated distributed joins feature for all queries that will be executed over an ODBC connection.false
ENFORCE_JOIN_ORDEREnforces a join order of tables in SQL queries. If set to true, the query optimizer will not reorder tables in the join.false
PROTOCOL_VERSIONUsed to specify ODBC protocol version to use. Currently, there are following versions: 2.1.0, 2.1.5, 2.3.0, 2.3.2, 2.5.0. You can use earlier versions of the protocol for backward compatibility.2.3.0
REPLICATED_ONLYSpecify that the query will be executed over fully replicated tables. This is a hint for potentially more effective execution optimizations.false
COLLOCATEDSet this parameter to true if your SQL statement includes a GROUP BY clause that groups the results by either primary or affinity key.
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 colocated together on the same node and you group by a primary or affinity key, then Ignite makes significant performance and network optimizations by grouping data locally on each node participating in the query.
false
LAZYLazy 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 as a hint for Ignite to fetch the result set lazily, thus minimizing memory consumption at the cost of moderate performance hit.
false
SKIP_REDUCER_ON_UPDATEEnables server side update feature.

When Ignite executes a DML operation, first, 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 saturate network if a DML operation has to move many entries over it.

Use this flag as a hint for Ignite to do 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
SSL_MODEDetermines whether a SSL connection will be negotiated with the server. Use require or disable mode as needed.
SSL_KEY_FILESpecifies the name of the file containing the SSL server private key.
SSL_CERT_FILESpecifies the name of the file containing the SSL server certificate.
SSL_CA_FILESpecifies the name of the file containing the SSL server certificate authority (CA).

Connection String Samples

You can find samples of the connection string below. These strings can be used with SQLDriverConnect ODBC call to establish connection with an Apache Ignite node.

DRIVER={Apache Ignite};
ADDRESS=localhost:10800;
SCHEMA=somecachename;
USER=yourusername;
PASSWORD=yourpassword;
SSL_MODE=[require|disable];
SSL_KEY_FILE=<path_to_private_key>;
SSL_CERT_FILE=<path_to_client_certificate>;
SSL_CA_FILE=<path_to_trusted_certificates>
DRIVER={Apache Ignite};ADDRESS=localhost:10800;CACHE=yourCacheName
DRIVER={Apache Ignite};ADDRESS=localhost:10800
DSN=MyIgniteDSN
DRIVER={Apache Ignite};ADDRESS=example.com:12901;CACHE=MyCache;PAGE_SIZE=4096

Configuring DSN

The same arguments can be used if you prefer to use DSN (Data Source Name) for connection purposes.

To configure DSN on Windows, you should use a system tool called odbcad32 (for 32-bit [x86] systems) or odbcad64 (for 64-bit systems) which is an ODBC Data Source Administrator.

When installing the DSN tool, if you use the pre-built msi file, make sure you've installed Microsoft Visual C++ 2010 (32-bit/x86 or 64-bit/x64).

Launch the tool via Control panel->Administrative Tools->Data Sources (ODBC). Once the ODBC Data Source Administrator is launched, select Add...->Apache Ignite and configure your DSN in the desired way.

To do the same on Linux, you have to locate the odbc.ini file. The file location varies among Linux distributions and depends on a specific Driver Manager used by the Linux distribution. As an example, if you are using unixODBC then you can run the following command that will print system wide ODBC related details:

odbcinst -j

A path to the file of interest will be shown in between SYSTEM DATA SOURCES and
USER DATA SOURCES properties.

Once the odbc.ini file has been found, open it with any editor of your choice and add DSN section to it, as shown below:

[DSN Name]
description=<Insert your description here>
driver=Apache Ignite
<Other arguments here...>

Updated 4 months ago

Connection String and DSN


Suggested Edits are limited on API Reference Pages

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