How to work with Adobe Experience Manager 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 Adobe Experience Manager, Spark can work with live Adobe Experience Manager data. This article describes how to connect to and query Adobe Experience Manager data from a Spark shell.

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

Install the CData JDBC Driver for Adobe Experience Manager

Download the CData JDBC Driver for Adobe Experience Manager installer, unzip the package, and run the JAR file to install the driver.

Start a Spark Shell and Connect to Adobe Experience Manager Data

1. Open a terminal and start the Spark shell with the CData JDBC Driver for Adobe Experience Manager JAR file as the jars parameter:

   $ spark-shell --jars /CData/CData JDBC Driver for Adobe Experience Manager/lib/cdata.jdbc.adobeexperiencemanager.jar
   

2. With the shell running, you can connect to Adobe Experience Manager with a JDBC URL and use the SQL Context load() function to read a table.

   The driver connects to Adobe Experience Manager (AEM) instances that expose the JCR repository over WebDAV. It supports both on-premises AEM and AEM as a Cloud Service deployments.

   To establish a connection, set the following properties:

   • URL: The WebDAV-enabled JCR server URL.
     • AEM as a Cloud Service: https://author-pXXXXX-eXXXXX.adobeaemcloud.com/crx/server
     • Local development: http://localhost:4502/crx/server
   • User: Your AEM username.
   • Password: Your AEM password.

   Note: Tables are dynamically generated based on the JCR repository structure. Ensure that the configured user has sufficient permissions to access the required content paths in the AEM repository.

   Built-in Connection String Designer

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

   java -jar cdata.jdbc.adobeexperiencemanager.jar
   

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

   

   Configure the connection to Adobe Experience Manager, using the connection string generated above.

   scala> val adobeexperiencemanager_df = spark.sqlContext.read.format("jdbc").option("url", "jdbc:adobeexperiencemanager:URL=https://author-p12345-e67890.adobeaemcloud.com/crx/server;User=admin;Password=admin;").option("dbtable","Content").option("driver","cdata.jdbc.adobeexperiencemanager.AdobeExperienceManagerDriver").load()
   

3. Once you connect and the data is loaded you will see the table schema displayed.

4. Register the Adobe Experience Manager data as a temporary table:

   scala> adobeexperiencemanager_df.registerTable("content")

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

   scala> adobeexperiencemanager_df.sqlContext.sql("SELECT Id, Name FROM Content WHERE Name = example").collect.foreach(println)

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

   

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