SQL JDBC
editSQL JDBC
editElasticsearch’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
editThe 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:
<dependency> <groupId>org.elasticsearch.plugin</groupId> <artifactId>jdbc</artifactId> <version>6.3.2</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
editThe 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://[http|https]?[host[:port]]*/[prefix]*[?[option=value]&]*
-
jdbc:es://
- Prefix. Mandatory.
-
[http|https]
-
Type of HTTP connection to make. Possible values are
http
(default) orhttps
. 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 parameters:
Essential
edit-
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
edit-
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
edit-
user
- Basic Authentication user name
-
password
- Basic Authentication password
SSL
edit-
ssl
(default false) - Enable SSL
-
ssl.keystore.location
- key store (if used) location
-
ssl.keystore.pass
- key store password
-
ssl.keystore.type
(defaultJKS
) -
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
(defaultfalse
) - Whether or not to allow self signed certificates
-
ssl.protocol
(defaultTLS
) - SSL protocol to be used
Proxy
edit-
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 Elasticsearch SQL connection to server
on port 3456
, setting the JDBC connection timezone to UTC
and its pagesize to 250
entries.