Work with X-Cart Data in Apache Spark Using SQL

Ready to get started?

Download for a free trial:

Download Now

Learn more:

X-Cart JDBC Driver

Rapidly create and deploy powerful Java applications that integrate with X-Cart.

Access and process X-Cart Data in Apache Spark using the CData JDBC Driver.

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

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

Install the CData JDBC Driver for X-Cart

Download the CData JDBC Driver for X-Cart installer, unzip the package, and run the JAR file to install the driver.

Start a Spark Shell and Connect to X-Cart Data

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

    An API Key must be created for your XCart Installation. In order to obtain the API Key, you need to install and configure the REST API Module for your X-Cart installation.

    • Login to your X-Cart Installation. Click on My addons on the bottom of the left side-bar navigation panel.
    • Search for REST API Module. Install the Module.
    • After the module has been installed, you need to set it up.
      • Go to the Settings section for the REST API Module.
      • On the settings page specify the API Key (only read)
    • Set the ApiKey and Url to the connection property to connect to data.

    Built-in Connection String Designer

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

    java -jar cdata.jdbc.xcart.jar

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

    Configure the connection to X-Cart, using the connection string generated above.

    scala> val xcart_df ="jdbc").option("url", "jdbc:xcart:Url=;ApiKey=MyApiKey;").option("dbtable","PROFILES").option("driver","cdata.jdbc.xcart.XCartDriver").load()
  3. Once you connect and the data is loaded you will see the table schema displayed.
  4. Register the X-Cart data as a temporary table:

    scala> xcart_df.registerTable("profiles")
  5. Perform custom SQL queries against the Data using commands like the one below:

    scala> xcart_df.sqlContext.sql("SELECT ProfileId, FirstName FROM PROFILES WHERE Language = en").collect.foreach(println)

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

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