Accessing Spark SQL through JDBC and ODBC

With the use of the Spark Thrift Server, remote access to Spark SQL is possible over JDBC or ODBC. With this approach you can access data through JDBC (via the JDBC Beeline client) or ODBC (via the Simba driver).

The following considerations and prerequisites apply to JDBC and ODBC access.

Considerations:

The Spark Thrift Server works in YARN client mode only.
ODBC and JDBC client configurations must match Spark Thrift Server configuration parameters. For example, if the Thrift Server is configured to listen in binary mode, the client should send binary requests and use HTTP mode when the Thrift Server is configured over HTTP.
When using JDBC or ODBC to access Spark SQL in a production environment, note that the Spark Thrift Server does not currently support the doAs authorization property, which propagates user identity. Workaround: use programmatic APIs or spark-shell, submitting the job under your identity.
All client requests coming to Spark Thrift Server share a SparkContext.

Prerequisites:

The Spark Thrift Server must be deployed on the cluster.
- For an Ambari-managed cluster, deploy and launch the Spark Thrift Server using the Ambari Web UI (see Installing and Configuring Spark Over Ambari).
- For a cluster that is not managed by Ambari, see Starting the Spark Thrift Server in the Non-Ambari Cluster Installation Guide.
If SPARK_HOME is not already defined, set it to your Spark directory. For example:
export SPARK_HOME=/usr/hdp/current/spark-client

To list available Thrift Server options, run ./sbin/start-thriftserver.sh --help.

To manually stop the Spark Thrift Server:

su spark

./sbin/stop-thriftserver.sh

Accessing Spark SQL through JDBC

Connect to the Thrift Server over the Beeline JDBC client.

Launch Beeline from SPARK_HOME:
su spark
./bin/beeline

On the Beeline prompt, connect to the Spark SQL Thrift Server:

beeline> !connect jdbc:hive2://localhost:10015

The host port must match the host port where the Spark Thrift Server is running.

You should see output similar to the following:

beeline> !connect jdbc:hive2://localhost:10015
Connecting to jdbc:hive2://localhost:10015
Enter username for jdbc:hive2://localhost:10015:
Enter password for jdbc:hive2://localhost:10015:
...
Connected to: Spark SQL (version 1.6.2)
Driver: Spark Project Core (version 1.6.2.2.4.0.0-169)
Transaction isolation: TRANSACTION_REPEATABLE_READ
0: jdbc:hive2://localhost:10015>

Once connected, issue a Spark SQL statement.

The following example executes a SHOW TABLES query:

0: jdbc:hive2://localhost:10015> show tables;
+------------+--------------+--+
| tableName | isTemporary |
+------------+--------------+--+
| sample_07 | false |
| sample_08 | false |
| testtable | false |
+------------+--------------+--+
3 rows selected (2.399 seconds)
0: jdbc:hive2://localhost:10015>

Accessing Spark SQL through ODBC

For documentation and driver download links, see "Hortonworks ODBC Driver for SparkSQL (1.5 & 1.6)" in Hortonworks Data Platform Add-Ons.

​Accessing Spark SQL through JDBC and ODBC

​Accessing Spark SQL through JDBC

​Accessing Spark SQL through ODBC

Accessing Spark SQL through JDBC and ODBC

Accessing Spark SQL through JDBC

Accessing Spark SQL through ODBC