Work with Exact Online 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 Exact Online, Spark can work with live Exact Online data. This article describes how to connect to and query Exact Online data from a Spark shell.

The CData JDBC Driver offers unmatched performance for interacting with live Exact Online data due to optimized data processing built into the driver. When you issue complex SQL queries to Exact Online, the driver pushes supported SQL operations, like filters and aggregations, directly to Exact Online 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 Exact Online data using native data types.

Install the CData JDBC Driver for Exact Online

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

Start a Spark Shell and Connect to Exact Online Data

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

   Exact Online uses the OAuth authentication standard. The InitiateOAuth connection property facilitates the OAuth flow -- by default, this is set to GETANDREFRESH. You can also use the embedded OAuth credentials or you can register an OAuth app with Exact to obtain your own. In addition to the OAuth values, provide the Region. If Division is not set, the default Division is determined.

   See the "Getting Started" chapter of the help documentation for more information.

   Built-in Connection String Designer

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

   java -jar cdata.jdbc.exactonline.jar

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

   Configure the connection to Exact Online, using the connection string generated above.

   scala> val exactonline_df = spark.sqlContext.read.format("jdbc").option("url", "jdbc:exactonline:Region='United States';Division=5512;").option("dbtable","Accounts").option("driver","cdata.jdbc.exactonline.ExactOnlineDriver").load()
3. Once you connect and the data is loaded you will see the table schema displayed.

4. Register the Exact Online data as a temporary table:

   scala> exactonline_df.registerTable("accounts")

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

   scala> exactonline_df.sqlContext.sql("SELECT Name, CreditLinePurchase FROM Accounts WHERE IsCompetitor = False").collect.foreach(println)

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

Using the CData JDBC Driver for Exact Online in Apache Spark, you are able to perform fast and complex analytics on Exact Online 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.