SQL JDBC

Elasticsearch’s SQL jdbc driver is a rich, fully featured JDBC driver for Elasticsearch. It is Type 4 driver, meaning it is a platform independent, stand-alone, Direct to Database, pure Java driver that converts JDBC calls to Elasticsearch SQL.

Installation

The JDBC driver can be obtained from:

Dedicated page

elastic.co provides links, typically for manual downloads.

Maven dependency

Maven-compatible tools can retrieve it automatically as a dependency:

<dependency>
  <groupId>org.elasticsearch.plugin</groupId>
  <artifactId>x-pack-sql-jdbc</artifactId>
  <version>7.9.1</version>
</dependency>

from artifacts.elastic.co/maven by adding it to the repositories list:

<repositories>
  <repository>
    <id>elastic.co</id>
    <url>https://artifacts.elastic.co/maven</url>
  </repository>
</repositories>

Setup

The driver main class is org.elasticsearch.xpack.sql.jdbc.EsDriver. Note the driver implements the JDBC 4.0 Service Provider mechanism meaning it is registered automatically as long as it is available in the classpath.

Once registered, the driver understands the following syntax as an URL:

jdbc:[es|elasticsearch]://[[http|https]://]?[host[:port]]?/[prefix]?[\?[option=value]&]*

jdbc:[es|elasticsearch]://

Prefix. Mandatory.

[[http|https]://]

Type of HTTP connection to make. Possible values are http (default) or https. Optional.

[host[:port]]

Host (localhost by default) and port (9200 by default). Optional.

[prefix]

Prefix (empty by default). Typically used when hosting Elasticsearch under a certain path. Optional.

[option=value]

Properties for the JDBC driver. Empty by default. Optional.

The driver recognized the following properties:

Essential

timezone (default JVM timezone)

Timezone used by the driver per connection indicated by its ID. Highly recommended to set it (to, say, UTC) as the JVM timezone can vary, is global for the entire JVM and can’t be changed easily when running under a security manager.

Network

connect.timeout (default 30s)

Connection timeout (in seconds). That is the maximum amount of time waiting to make a connection to the server.

network.timeout (default 60s)

Network timeout (in seconds). That is the maximum amount of time waiting for the network.

page.timeout (default 45s)

Page timeout (in seconds). That is the maximum amount of time waiting for a page.

page.size (default 1000)

Page size (in entries). The number of results returned per page by the server.

query.timeout (default 90s)

Query timeout (in seconds). That is the maximum amount of time waiting for a query to return.

Basic Authentication

user

Basic Authentication user name

password

Basic Authentication password

SSL

ssl (default false)

Enable SSL

ssl.keystore.location

key store (if used) location

ssl.keystore.pass

key store password

ssl.keystore.type (default JKS)

key store type. PKCS12 is a common, alternative format

ssl.truststore.location

trust store location

ssl.truststore.pass

trust store password

ssl.truststore.type (default JKS)

trust store type. PKCS12 is a common, alternative format

ssl.protocol(default TLS)

SSL protocol to be used

Proxy

proxy.http

Http proxy host name

proxy.socks

SOCKS proxy host name

Mapping

field.multi.value.leniency (default true)

Whether to be lenient and return the first value (without any guarantees of what that will be - typically the first in natural ascending order) for fields with multiple values (true) or throw an exception.

Index

index.include.frozen (default false)

Whether to include frozen-indices in the query execution or not (default).

Additional

validate.properties (default true)

If disabled, it will ignore any misspellings or unrecognizable properties. When enabled, an exception will be thrown if the provided property cannot be recognized.

To put all of it together, the following URL:

jdbc:es://http://server:3456/?timezone=UTC&page.size=250

opens up a Elasticsearch SQL connection to server on port 3456, setting the JDBC connection timezone to UTC and its pagesize to 250 entries.