How to work with HCL Domino Data in Apache Spark using SQL

Apache Spark is a fast and general engine for large-scale data processing. When paired with the CData JDBC Driver for HCL Domino, Spark can work with live HCL Domino data. This article describes how to connect to and query HCL Domino data from a Spark shell.

The CData JDBC Driver offers unmatched performance for interacting with live HCL Domino data due to optimized data processing built into the driver. When you issue complex SQL queries to HCL Domino, the driver pushes supported SQL operations, like filters and aggregations, directly to HCL Domino and utilizes the embedded SQL engine to process unsupported operations (often SQL functions and JOIN operations) client-side. With built-in dynamic metadata querying, you can work with and analyze HCL Domino data using native data types.

Install the CData JDBC Driver for HCL Domino

Download the CData JDBC Driver for HCL Domino installer, unzip the package, and run the JAR file to install the driver.

Start a Spark Shell and Connect to HCL Domino Data

1. Open a terminal and start the Spark shell with the CData JDBC Driver for HCL Domino JAR file as the jars parameter: $ spark-shell --jars /CData/CData JDBC Driver for HCL Domino/lib/cdata.jdbc.domino.jar
2. With the shell running, you can connect to HCL Domino with a JDBC URL and use the SQL Context load() function to read a table.

   Connecting to Domino

   To connect to Domino data, set the following properties:

   • URL: The host name or IP of the server hosting the Domino database. Include the port of the server hosting the Domino database. For example: http://sampleserver:1234/
   • DatabaseScope: The name of a scope in the Domino Web UI. The driver exposes forms and views for the schema governed by the specified scope. In the Domino Admin UI, select the Scopes menu in the sidebar. Set this property to the name of an existing scope.

   Authenticating with Domino

   Domino supports authenticating via login credentials or an Azure Active Directory OAuth application:

   Login Credentials

   To authenticate with login credentials, set the following properties:

   • AuthScheme: Set this to "OAuthPassword"
   • User: The username of the authenticating Domino user
   • Password: The password associated with the authenticating Domino user

   The driver uses the login credentials to automatically perform an OAuth token exchange.

   AzureAD

   This authentication method uses Azure Active Directory as an IdP to obtain a JWT token. You need to create a custom OAuth application in Azure Active Directory and configure it as an IdP. To do so, follow the instructions in the Help documentation. Then set the following properties:

   • AuthScheme: Set this to "AzureAD"
   • InitiateOAuth: Set this to GETANDREFRESH. You can use InitiateOAuth to avoid repeating the OAuth exchange and manually setting the OAuthAccessToken.
   • OAuthClientId: The Client ID obtained when setting up the custom OAuth application.
   • OAuthClientSecret: The Client secret obtained when setting up the custom OAuth application.
   • CallbackURL: The redirect URI defined when you registered your app. For example: https://localhost:33333
   • AzureTenant: The Microsoft Online tenant being used to access data. Supply either a value in the form companyname.microsoft.com or the tenant ID.

     The tenant ID is the same as the directory ID shown in the Azure Portal's Azure Active Directory > Properties page.

   Built-in Connection String Designer

   For assistance in constructing the JDBC URL, use the connection string designer built into the HCL Domino JDBC Driver. Either double-click the JAR file or execute the jar file from the command-line.

   java -jar cdata.jdbc.domino.jar

   Fill in the connection properties and copy the connection string to the clipboard.

   Configure the connection to HCL Domino, using the connection string generated above.

   scala> val domino_df = spark.sqlContext.read.format("jdbc").option("url", "jdbc:domino:Server=https://domino.corp.com;AuthScheme=OAuthPassword;User=my_domino_user;Password=my_domino_password;").option("dbtable","ByName").option("driver","cdata.jdbc.domino.DominoDriver").load()
3. Once you connect and the data is loaded you will see the table schema displayed.

4. Register the HCL Domino data as a temporary table:

   scala> domino_df.registerTable("byname")

5. Perform custom SQL queries against the Data using commands like the one below:

   scala> domino_df.sqlContext.sql("SELECT Name, Address FROM ByName WHERE City = Miami").collect.foreach(println)

   You will see the results displayed in the console, similar to the following:

Using the CData JDBC Driver for HCL Domino in Apache Spark, you are able to perform fast and complex analytics on HCL Domino data, combining the power and utility of Spark with your data. Download a free, 30 day trial of any of the 200+ CData JDBC Drivers and get started today.