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

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

Install the CData JDBC Driver for Webflow

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

Start a Spark Shell and Connect to Webflow Data

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

   $ spark-shell --jars /CData/CData JDBC Driver for Webflow/lib/cdata.jdbc.api.jar
   

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

   Authentication

   Webflow uses OAuth 2.0 authentication to ensure secure access to sites, CMS collections, e-commerce data, and other resources. This authentication method allows you to securely connect to your Webflow workspace and manage resources with proper authorization.

   OAuth 2.0 Setup and Configuration

   Step 1: Create a Webflow OAuth Application

   To set up OAuth authentication:

   1. Visit the Webflow Developer Portal
   2. Navigate to "Apps & Integrations" in your Webflow account
   3. Click "Register an App" to create a new OAuth application
   4. Configure the application name, description, and redirect URI (CallbackURL)
   5. Copy the Client ID and Client Secret for use in your connection

   Required Connection Properties

   • AuthScheme: Set this to OAuth (required)
   • OAuthClientId: Client ID from your Webflow OAuth application (required)
   • OAuthClientSecret: Client secret from your Webflow OAuth application (required)
   • CallbackURL: Redirect URI specified in your OAuth application (required)
   • InitiateOAuth: Set to GETANDREFRESH for automatic token management (recommended)

   Required OAuth Scopes

   The Webflow API Profile requires the following OAuth scopes:

   • sites:read - Read access to site information and configuration
   • pages:read - Read access to site pages
   • cms:read - Read access to CMS collections and items
   • forms:read - Read access to forms and form submissions
   • assets:read - Read access to media assets and folders
   • ecommerce:read - Read access to products, orders, and inventory
   • authorized_user:read - Read access to the authorized user

   Built-in Connection String Designer

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

   java -jar cdata.jdbc.api.jar
   

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

   

   Configure the connection to Webflow, using the connection string generated above.

   scala> val api_df = spark.sqlContext.read.format("jdbc").option("url", "jdbc:api:Profile=C:\profiles\Webflow.apip;AuthScheme=OAuth;InitiateOAuth=GETANDREFRESH;OAuthClientId=your_client_id;OAuthClientSecret=your_client_secret;CallbackUrl=your_callback_url;").option("dbtable","Sites").option("driver","cdata.jdbc.api.APIDriver").load()
   

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

4. Register the Webflow data as a temporary table:

   scala> api_df.registerTable("sites")

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

   scala> api_df.sqlContext.sql("SELECT ,  FROM Sites WHERE Id = your_site_id").collect.foreach(println)

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

   

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