Skip to content

Latest commit

 

History

History
179 lines (129 loc) · 5.82 KB

jdbc.asciidoc

File metadata and controls

179 lines (129 loc) · 5.82 KB

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 either by downloading it from the elastic.co site or by using a Maven-compatible tool with the following dependency:

  org.elasticsearch.plugin
  x-pack-sql-jdbc
  {version}

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

  
    elastic.co
    https://artifacts.elastic.co/maven

Setup

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

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

jdbc:es://<1>[http|https]?<2>[host[:port]]*<3>/[prefix]*<4>[?[option=value]&<5>]*

  1. jdbc:es:// prefix. Mandatory.

  2. type of HTTP connection to make - http (default) or https. Optional.

  3. host (localhost by default) and port (9200 by default). Optional.

  4. prefix (empty by default). Typically used when hosting {es} under a certain path. Optional.

  5. Parameters for the JDBC driver. Empty by default. Optional.

The driver recognized the following parameters:

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.cert.allow.self.signed (default false)

Whether or not to allow self signed certificates

ssl.protocol(default TLS)

SSL protocol to be used

Proxy

proxy.http

Http proxy host name

proxy.socks

SOCKS proxy host name

To put all of it together, the following URL:

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

Opens up a {es-sql} connection to server on port 3456, setting the JDBC connection timezone to UTC and its pagesize to 250 entries.

API usage

One can use JDBC through the official java.sql and javax.sql packages:

java.sql

The former through java.sql.Driver and DriverManager:

include-tagged::{jdbc-tests}/JdbcIntegrationTestCase.java[connect-dm]

  1. The server and port on which Elasticsearch is listening for HTTP traffic. The port is by default 9200.

  2. Properties for connecting to Elasticsearch. An empty Properties instance is fine for unsecured Elasticsearch.

javax.sql

Accessible through the javax.sql.DataSource API:

include-tagged::{jdbc-tests}/JdbcIntegrationTestCase.java[connect-ds]

  1. The server and port on which Elasticsearch is listening for HTTP traffic. By default 9200.

  2. Properties for connecting to Elasticsearch. An empty Properties instance is fine for unsecured Elasticsearch.

Which one to use? Typically client applications that provide most configuration parameters in the URL rely on the DriverManager-style while DataSource is preferred when being passed around since it can be configured in one place and the consumer only has to call getConnection without having to worry about any other parameters.

To connect to a secured Elasticsearch server the Properties should look like:

include-tagged::{security-tests}/JdbcSecurityIT.java[admin_properties]

Once you have the connection you can use it like any other JDBC connection. For example:

include-tagged::{jdbc-tests}/SimpleExampleTestCase.java[simple_example]