Connecting to Spark query server by using Spark JDBC Driver

To connect to the Spark query server using a JDBC client, such as DBeaver, set up watsonx.data driver in DBeaver.

1. Open DBeaver and in the menu bar click on Database > Driver Manager.
2. Search for Hive. You can find Apache Hive 4+ driver under Hadoop category.
3. Click Copy.
4. Change the following settings :
   1. In the Settings tab, change the name to Spark watsonx.data.
      Draft comment: swathikrishna.r@ibm.com
      Manjot >> Kindly let us know the fields to be modified.
   2. In the Libraries tab, if any existing JAR files are listed, select each one and click Delete to remove them. Then, add the Spark JDBC client JAR file.
   3. Click Find Class to scan the uploaded JAR and confirm the driver class is detected.
   4. When the class list appears, select com.ibm.wxd.spark.jdbc.QueryServerDriver as the driver class and verify that it reflects in the Driver Class field of the driver manager.
5. Select Database Navigator, click on New Connection and complete the following steps:
   1. Select the newly created driver.
   2. Click Connect by and select URL
   3. Provide the JDBC URL using the following format : jdbc:hive2://<HOST>:443/default;instance=<INSTANCE>;httpPath=<URI>. Generate the trustore file by using the following.

      echo QUIT | openssl s_client -showcerts \
        -connect cpd-cpd-instance.apps.mss-exp.cp.fyre.ibm.com:443 \
        | awk '/-----BEGIN CERTIFICATE-----/ {p=1}; p; /-----END CERTIFICATE-----/ {p=0}' \
        > demovikas.crt
      
      keytool -import \
        -alias spark-query-server-demovikas \
        -file demovikas.crt \
        -keystore ./demovikas.jks

   4. Add the SSL certificate details to the JDBC URL
      • Append the trust store path and password to the JDBC URL to enable SSL connectivity.

        jdbcUrl += "sslTrustStore=tech_trust.jks;trustStorePassword=Test@123";

      • Include the sslTrustStore and trustStorePassword parameters in the URL. The format becomes

        jdbc:hive2://<HOST>:443/default;instance=<INSTANCE>;httpPath=<URI>;sslTrustStore=<jks cert path>;trustStorePassword=<trueststore password>

   5. Select Authentication, provide Username as your username and your IAM API key as the password.
   6. Save and connect to the connection by double-clicking.

Ensure your Java CLASSPATH includes the downloaded JDBC driver. For example:

java -cp hive-jdbc-4.3.0-SNAPSHOT-standalone.jar App.java

You can specify the following parameters and use the following Java code to connect to the Spark query server. When using the v2 API, set the <api_version> parameter to v2; for the v3 API, set it to v3.

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.Statement;

public class App {
    public static void main(String[] args) throws Exception {
        // Set the below configurations from Connection Details of QueryS Server
        // Exclude having https/http/www, just domain
        String host = "example.com";
        String Instance = "CRN/OR/INSTANCE-ID";
        String uri = "/lakehouse/api/<api_version>/spark_engines/.../query_servers/.../connect/cliservice";
        String user = "EMAIL-ID/OR/USER-ID";
        String apikey = "API-KEY";

        String jdbcUrl = String.format("jdbc:hive2://%s/default;instance=%s;httpPath=%s;", host, Instance, uri);

        // Required if your domain requires SSL certificates
        // This is not required for SaaS, hence comment the below line for SaaS
        // Else, we need provide trust-store path which has the SSL certificates for the host
        jdbcUrl += "sslTrustStore=tech_trust.jks;trustStorePassword=Test@123";

        try {
            // Load the Hive JDBC driver
            Class.forName("com.ibm.wxd.spark.jdbc.QueryServerDriver");

            // Connect to Hive
            Connection con = DriverManager.getConnection(jdbcUrl, user, apikey);
            Statement stmt = con.createStatement();

            System.out.println("Connected to watsonx.data Spark Query Server");

            // Sample query
            String sql = "show databases";

            ResultSet rs = stmt.executeQuery(sql);
            ResultSetMetaData rsmd = rs.getMetaData();
            int columnCount = rsmd.getColumnCount();

            // The column count starts from 1
            for (int i = 1; i <= columnCount; i++ ) {
                System.out.println(rsmd.getColumnName(i));
            }

            // Print result
            while (rs.next()) {
                System.out.println(rs.getString(1)); // Or loop through columns
            }

            // Clean up
            rs.close();
            stmt.close();
            con.close();

        } catch (Exception e) {
            e.printStackTrace();
        }
    }

To connect to the Spark query server using a Python program, do the following:

1. Ensure you have Python version 3.12 or below.
2. Install pyHive using pip install thrift "PyHive[hive_pure_sasl]==0.7.0".
3. Save the following in a file, connect.py. When using the v2 API, set the <api_version> parameter to v2; for the v3 API, set it to v3.

   import ssl
   import thrift
   import base64
   from pyhive import hive
   
   import requests
   import thrift.transport
   import thrift.transport.THttpClient
   
   import logging
   import contextlib
   from http.client import HTTPConnection
   
   
   # Change the following inputs. When using the v2 API, set the <api_version> parameter to `v2`; for the v3 API, set it to `v3`.
   class Credentials:
       host = "https://example.ibm.com"
       uri = "/lakehouse/api/<api_version>/spark_engines/.../query_servers/.../connect/cliservice"
       instance_id = "CRN/OR/INSTANCE-ID"
       username = "EMAIL-ID/OR/USER-ID"
       apikey = "API-KEY"
   
   
   creds = Credentials()
   
   
   def disable_ssl(ctx):
       ctx.check_hostname = False
       ctx.verify_mode = ssl.CERT_NONE
   
       ssl.SSLContext.verify_mode = property(lambda self: ssl.CERT_NONE, lambda self, newval: None)
   
   
   def get_access_token(apikey):
       try:
           headers = {
               'Content-Type': 'application/x-www-form-urlencoded',
               'Accept': 'application/json',
           }
   
           data = {
               'grant_type': 'urn:ibm:params:oauth:grant-type:apikey',
               'apikey': apikey,
           }
   
           response = requests.post('https://iam.cloud.ibm.com/identity/token', headers=headers, data=data)
           return response.json()['access_token']
       except Exception as inst:
           print('Error in getting access token')
           print(inst)
           exit
   
   ctx = ssl.create_default_context()
   
   ## If you require to disable SSL, uncomment the below line
   # disable_ssl(ctx)
   
   transport = thrift.transport.THttpClient.THttpClient(
       uri_or_host="{host}:{port}{uri}".format(
           host=creds.host, uri= creds.uri, port=443,
       ),
       ssl_context=ctx,
   )
   
   headers = {
       "AuthInstanceId": creds.instance_id
   }
   
   if creds.instance_id.isdigit():
       # Software installation
       headers["Authorization"] =  "ZenApiKey " + base64.b64encode(f"{creds.username}:{creds.apikey}".encode('utf-8')).decode('utf-8')
   else:
       # Cloud installation
       headers["Authorization"] = "Bearer {}".format(get_access_token(creds.apikey))
   
   transport.setCustomHeaders(headers)
   
   cursor = hive.connect(thrift_transport=transport).cursor()
   print("Connected to Spark Query Server")
   
   cursor.execute('show databases')
   print(cursor.fetchall())
   
   cursor.close()

4. Run using python connect.py.