Arrow Flight SQL JDBC

The Arrow Flight SQL JDBC driver is an open-source driver that is based on the specifications for the Java Database Connectivity (JDBC) API. The Flight SQL JDBC driver uses Apache Arrow, so it is able to move large amounts of data faster, in part because it does not need to serialize and then deserialize data.

This driver is licensed under Apache-2.0.

Prerequisites

You can use the Arrow Flight SQL JDBC 18.3.0 driver on systems that:

• Support Java versions: Java 11+
• Run the following 64-bit operating systems:
  • Linux: RedHat/CentOS
  • Windows 10 and later
  • macOS

Supported Authentication Method

You can use personal access tokens for authenticating to Dremio. To generate one, see Personal Access Tokens.

Download and Install

1. Download the Driver: You can download the driver at Arrow Flight SQL JDBC Driver.
2. Integrate the Driver: To integrate the driver into your development environment, you need to add the location of the driver to your classpath to inform the Java Virtual Machine (JVM) and the Java compiler where to locate the driver class files and resources during compilation and runtime.
3. For name the of the driver class, specify org.apache.arrow.driver.jdbc.ArrowFlightJdbcDriver.

Connect to Dremio

Use this template to create a direct connection to Dremio Cloud:

Template for the JDBC URL

jdbc:arrow-flight-sql://data.dremio.cloud:443/?token=<encoded_pat>[&catalog=<project_id>][&schema=<schema>][&<properties>]

• token: The personal access token to use to authenticate to Dremio. See Personal Access Tokens for information about enabling and creating PATs. You must URL-encode PATs that you include in JDBC URLs. See URL-encoding Values of Properties for suggested steps.
• catalog: Specifies the project ID of a project in Dremio Cloud. You can use this to connect to non-default Dremio Cloud projects.
• schema: The name of the schema (data source or folder, including child paths, such as mySource.folder1 and folder1.folder2) to use by default when a schema is not specified in a query.
• <properties>: A list of JDBC properties for encrypting connections and routing queries to particular engines. Values must be URL-encoded. See URL-encoding Values for suggested steps.

To authenticate to Dremio Cloud, pass in a personal access token (PAT) with the token property. Use the PAT as the value. See Personal Access Tokens for information about enabling the use of PATs in Dremio and about creating PATs. You must URL-encode PATs that you include in JDBC URLs. To encode a PAT locally on your system, you can follow the steps in URL-encoding Values.

Connection Parameters

Encryption Parameters

If you are setting up encrypted communication between your JDBC client applications and the Dremio server, use the SSL JDBC connection parameters and fully qualified hostname to configure the JDBC connection string and connect to Dremio.

note

This driver does not yet support these features:

• Disabling host verification
• Impersonation

Properties	Value	Required	Description
disableCertificateVerification	true or false	[Optional]	If true, Dremio does not verify the host certificate against the truststore. The default value is false.
trustStoreType	string	[Optional]	Default: JKS The trustStore type. Allowed values are : JKS, PKCS12 If the useSystemTrustStore option is set to true (on Windows only), the allowed values are: Windows-MY, Windows-ROOT Import the certificate into the Trusted Root Certificate Authorities and set trustStoreType=Windows-ROOT. Also import the certificate into Trusted Root Certificate Authorities or Personal and set trustStoreType=Windows-MY.
trustStore	string	[Optional]	Path to the truststore. If not provided, the default Java truststore is used (usually $JAVA_HOME/lib/security/cacerts) and the trustStorePassword parameter is ignored.
useSystemTrustStore	true or false	[Optional]	By default, the value is true. Bypasses trustStoreType and automatically picks the correct truststore based on the operating system: Keychain on MacOS, Local Machine and Current User Certificate Stores on Windows, and default truststore on other operating systems. If you are using an operating system other than MacOS or Windows, you must use the trustStorePassword property to pass the password of the truststore. Here is an example of a connection string for Linux: jdbc:arrow-flight-sql://data.dremio.cloud:443/?useEncryption=true&token=1234&trustStorePassword=901234
trustStorePassword	string	[Optional]	Password to the truststore.

Advanced Parameters

Name	Type	Description	Default Value
quoting	string	Specifies which type of character to use to delimit values in queries. The value can be BACK_TICK, BRACKET, or DOUBLE_QUOTE.	DOUBLE_QUOTE
routing_tag	string	When this parameter is set, the specified tag is associated with all queries executed within a session. Rules can check for the presence of a tag with the function "tag()". For more information, see Workload Management.	N/A
stringColumnLength	string	The maximum length of data in columns of the STRING datatype and of complex datatypes. The range is 1 to 2147483647.	1024

URL-encode Values

To encode a personal access token (PAT) or property value locally on your system, you can follow these steps:

1. In a browser window, right-click an empty area of the page and select Inspect.
2. Click Console.
3. Type encodeURIComponent("<PAT-or-value>"), where <PAT-or-value> is the personal access token that you obtained from Dremio or the value of a supported JDBC property. The URL-encoded PAT or value appears on the next line. You can highlight it and copy it to your clipboard.

Parameterized Queries with Prepared Statements

Prepared statements allow you to dynamically pass parameters to SQL queries using placeholders, ensuring safer query execution by separating the query structure from the values in parameters.

With a prepared statement, parameters (?) can be set at runtime using set methods to reuse queries with different values.

note

This feature requires Apache Arrow 18.3.0 or later. It supports SELECT statements, but may not support all set methods.

To use parameterized queries with prepared statements, follow these steps:

1. Use the prepareStatement() method to define a query with parameters, which act as placeholders for dynamic values.
2. Set the values by replacing each parameter with a value using the appropriate set methods.
3. Ensure all parameters are set before running the query, with indexing starting at 1. If parameters are not set before running the query, JBDC throws an exception.
4. Call executeQuery() to run the query and retrieve results.

Java example

PreparedStatement preparedStatement = connection.prepareStatement(
  "SELECT * FROM employees WHERE department = ? AND salary > ?");
preparedStatement.setString(1, "Engineering");
preparedStatement.setDouble(2, 75000);
ResultSet resultSet = preparedStatement.executeQuery();

Supported Data Types and Set Methods

Column Data Type	Supported Set Methods
Integer	setInt(), setShort(), setNull()
Numeric	setInt(), setShort(), setLong(), setBigDecimal(), setNull()
Decimal	setShort(), setInt(), setLong(), setBigDecimal(), setNull()
BigInt	setShort(), setInt(), setLong(), setBigDecimal(), setNull()
Double	setDouble(), setFloat(), setNull()
Float	setFloat(), setNull()
Char	setString(), setNull()
Varchar	setString(), setNull()
Boolean	setBoolean(), setNull()
Time	setTime(), setNull()
Timestamp	setTimestamp(), setNull()
Date	setNull()
VarBinary	setBytes(), setNull()

Limitations

The JDBC client does not support the setDate() method due to mismatched date encoding formats between the Arrow Flight JDBC client and Dremio.

Differences between the Arrow Flight SQL JDBC and the Dremio JDBC (Legacy) Driver

The Arrow Flight SQL JDBC driver differs from the Dremio JDBC (Legacy) driver in the following:

• Requires Java 11+.

• Supports ResultSet.getBoolean() on varchar columns in which boolean values are represented as these strings: "0", "1", "true", "false".

• Supports null Calendar in calls to ResultSet.getDate(), ResultSet.getTime(), and ResultSet.getTimestamp()
  When a call to one of these methods has no Calendar parameter, or the Calendar parameter is null, the Flight JDBC driver uses the default timezone when it constructs the returned object.

• Supports ResultSet.getDate(), ResultSet.getTime(), and ResultSet.getTimestamp() on varchar columns in which dates, times, or timestamps are represented as strings.

• Supports varchar values that represent numeric values in calls to ResultSet.getInteger(), ResultSet.getFloat(), ResultSet.getDouble(), ResultSet.getShort(), ResultSet.getLong(), and ResultSet.getBigDecimal()
  

• Supports integer values in calls to getFloat()
  Integers returned gain one decimal place.

• Supports the native SQL complex types List, Map, and Struct
  Dremio's legacy JDBC driver uses String representations of these types.

• Supports using the Interval data type in SQL functions.

• Removes support for calling ResultSet.getBinaryStream() on non-binary data types. Though such support exists in traditional JDBC drivers, it is not in the specification for the JDBC API.

note

Calling DatabaseMetadata.getCatalog() when connected to Dremio returns empty. Other DatabaseMetadata methods return null values in the TABLE_CAT column. This is expected behavior because Dremio does not have a catalog.

Supported Conversions from Dremio Datatypes to JDBC Datatypes

DREMIO TYPE	JDBCARROW TYPE
BIGINT	Int
BIT	Bool
DATE	Date
DECIMAL	Decimal
DOUBLE	FloatingPoint(DOUBLE)
FIXEDSIZEBINARY	FixedSizeBinary
FLOAT	FloatingPoint(SINGLE)
INT	Int
INTERVAL_DAY_SECONDS	Interval(DAY_TIME)
INTERVAL_YEAR_MONTHS	Interval(YEAR_MONTH)
LIST	List
MAP	Map
NULL	Null
OBJECT	Not Supported
STRUCT	Struct
TIME	Time(MILLISECOND)
TIMESTAMP	Timestamp(MILLISECOND)
VARBINARY	Binary
VARCHAR	Utf8

Add the Root CA Certificate to Your System Truststore

1. At a command-line prompt, run this command:

   openssl s_client -showcerts -connect data.dremio.cloud:443 </dev/null

2. Copy the last certificate, including the lines -----BEGIN CERTIFICATE----- and -----END CERTIFICATE-----, to your clipboard.

3. Create a text file and paste the certificate into it.

4. Save the text file as cert.pem.

5. If you are using MacOS, follow these steps:

   a. In Finder, double-click the cert.pem file.

   b. In the dialog that opens, select the option to add the root certificate to the system truststore.

6. If you are using Windows, follow these steps:

   a. At a command-line prompt, enter one of these commands:

   • certlm if you want to add the certificate for all user accounts on your Windows system.
   • certmgr if you want to add the certificate only for the current user account.

   b. Right-click the folder Trusted Root Certification Authorities.

   c. Select Import.

   d. Browse for the cert.pem file and import it.

7. If you are using a version of Linux, follow the instructions for your version.

8. If you are developing your own client application to use the driver to connect to Dremio, add the certificate to the Java truststore. You must know the path to the cacerts file from $JAVA_HOME.

   • If you are using Java 11, run this command:

     keytool -import -trustcacerts -file cert.pem -alias gtsrootr1ca -keystore $JAVA_HOME/lib/security/cacerts

Limitations

• Impersonation is not supported.
• Disabling host verification is not supported.