Apache Spark Connector for MS Excel

Apache Spark Connector lets you connect to Apache Spark, a unified engine for large-scale data analytics.

In this article you will learn how to quickly and efficiently integrate Apache Spark data in MS Excel without coding. We will use high-performance Apache Spark Connector to easily connect to Apache Spark and then access the data inside MS Excel.

Let's follow the steps below to see how we can accomplish that!

Download Documentation

Apache Spark Connector for MS Excel is based on ZappySys JDBC Bridge Driver which is part of ODBC PowerPack. It is a collection of high-performance ODBC drivers that enable you to integrate data in SQL Server, SSIS, a programming language, or any other ODBC-compatible application. ODBC PowerPack supports various file formats, sources and destinations, including REST/SOAP API, SFTP/FTP, storage services, and plain files, to mention a few.

Prerequisites

Before we begin, make sure you meet the following prerequisite: Java Runtime Environment (JRE) or Java Development Kit (JDK) must be installed on your system.

Minimum required version: Java 8
- Amazon Corretto 8
- Oracle JRE 8
Recommended Java version: Java 21
- Amazon Corretto 21
- Oracle JRE 21

If your JDBC Driver targets a different Java version (e.g., 11 / 17 / 21), install the corresponding or newer Java version.

If you already have a JRE installed, you can try using it too. However, if you experience any issues, we recommend using one of the distributions mentioned above (you can install an additional JRE next to the existing one; just don't forget to configure the default Java in the Windows Environment Variables).

Download Apache Spark JDBC driver

To connect to Apache Spark in MS Excel, you will have to download JDBC driver for it, which we will use in later steps. Let's perform these little steps right away:

Visit MVN Repository.
Download the JDBC driver, and save it locally, e.g. to D:\Drivers\JDBC\hive-jdbc-standalone.jar.
Make sure to download the standalone version of the Apache Hive JDBC driver to avoid Java library dependency errors, e.g., hive-jdbc-4.0.1-standalone.jar (commonly used driver to connect to Spark).
Done! That was easy, wasn't it? Let's proceed to the next step.

Create ODBC Data Source (DSN) based on ZappySys JDBC Bridge Driver

Step-by-step instructions

To get data from Apache Spark using MS Excel we first need to create a DSN (Data Source) which will access data from Apache Spark. We will later be able to read data using MS Excel. Perform these steps:

Download and install ODBC PowerPack.
Open ODBC Data Sources (x64):
Create a User data source (User DSN) based on ZappySys JDBC Bridge Driver

ZappySys JDBC Bridge Driver
- Create and use User DSN if the client application is run under a User Account. This is an ideal option in design-time, when developing a solution, e.g. in Visual Studio 2019. Use it for both type of applications - 64-bit and 32-bit.
- Create and use System DSN if the client application is launched under a System Account, e.g. as a Windows Service. Usually, this is an ideal option to use in a production environment. Use ODBC Data Source Administrator (32-bit), instead of 64-bit version, if Windows Service is a 32-bit application.
Now, we need to configure the JDBC connection in the new ODBC data source. Simply enter the Connection string, credentials, configure other settings, and then click Test Connection button to test the connection:

ApacheSparkDSN

jdbc:hive2://spark-thrift-server-host:10000

D:\Drivers\JDBC\hive-jdbc-standalone.jar

[]

Use these values when setting parameters:
- Connection string: jdbc:hive2://spark-thrift-server-host:10000
- JDBC driver file(s): D:\Drivers\JDBC\hive-jdbc-standalone.jar
- Connection parameters: []
You should see a message saying that connection test is successful:

Otherwise, if you are getting an error, check out our Community for troubleshooting tips.
We are at the point where we can preview a SQL query. For more SQL query examples visit JDBC Bridge documentation:
ApacheSparkDSN
```
-- Basic SELECT with a WHERE clause
SELECT
    id,
    name,
    salary
FROM employees
WHERE department = 'Sales';
```
```
-- Basic SELECT with a WHERE clause
SELECT
    id,
    name,
    salary
FROM employees
WHERE department = 'Sales';
```
You can also click on the <Select Table> dropdown and select a table from the list.

The ZappySys JDBC Bridge Driver acts as a transparent intermediary, passing SQL queries directly to the JDBC driver, which then handles the query execution. This means the Bridge Driver simply relays the SQL query without altering it.

Some JDBC drivers don't support INSERT/UPDATE/DELETE statements, so you may get an error saying "action is not supported" or a similar one. Please, be aware, this is not the limitation of ZappySys JDBC Bridge Driver, but is a limitation of the specific JDBC driver you are using.
Click OK to finish creating the data source.

Video Tutorial

Read data in Excel from the DSN

In Excel click Data, then select Get Data, proceed with From Other Sources and choose From ODBC item. This will get data from ODBC data source we created:
A small window opens, then simply select the data source you created in previous steps:

ApacheSparkDSN
Most likely, you will be asked to authenticate to a newly created DSN. Just select Windows authentication option together with Use my current credentials option:

ApacheSparkDSN
Finally, you will be asked to select a table or view to get data from. Select one and load the data!
Finally, finally, use data extracted from Apache Spark API in an Excel worksheet:

Refresh data source in Excel

When data updates in Apache Spark, it is not automatically refreshed in Excel. To update the worksheet, go to Data or Table Design tab and click the Refresh button:

Helpful Links (JDBC Driver)

Below are some useful community articles to help you troubleshoot and configure the ZappySys JDBC Bridge Driver:

How to combine multiple JAR files
Learn how to merge multiple .jar dependencies when your JDBC driver requires more than one file.
How to fix JBR error: “Data lake is not available / Unable to verify trust for server certificate chain”
Resolve SSL or certificate validation issues encountered during JDBC connections.
System Exception: “Java is not installed or not accessible”
Fix Java path or environment issues that prevent the JDBC Bridge from launching Java.
JDBC Bridge Driver disconnect from Java host error
Troubleshoot unexpected disconnection problems between SSIS and the Java process.
Error: Could not open jvm.cfg while using JDBC Bridge Driver
Resolve JVM configuration path errors during driver initialization.
How to enable JDBC Bridge Driver logging
Enable detailed driver logging for better visibility during troubleshooting.
How to pass JDBC connection parameters (not by URL)
Learn how to specify connection properties programmatically instead of embedding them in the JDBC URL.
How to fix JDBC Bridge error: “No connection could be made because the target machine actively refused it”
Troubleshoot firewall or local port binding issues preventing communication with the Java host.
How to use JDBC Bridge options (System Property for Java command line, e.g., classpath, proxy)
Configure custom Java options like classpath and proxy using JDBC Bridge system properties.

Conclusion

In this article we showed you how to connect to Apache Spark in MS Excel and integrate data without any coding, saving you time and effort. It's worth noting that ZappySys JDBC Bridge Driver allows you to connect not only to Apache Spark, but to any Java application that supports JDBC (just use a different JDBC driver and configure it appropriately).

We encourage you to download Apache Spark Connector for MS Excel and see how easy it is to use it for yourself or your team.

If you have any questions, feel free to contact ZappySys support team. You can also open a live chat immediately by clicking on the chat icon below.

Download Apache Spark Connector for MS Excel Documentation