If you’re working with large datasets and advanced analytics, Databricks is a powerful platform that combines data engineering, machine learning, and analytics in one place. But what if you want to bring that data into Jedox for planning and reporting? In this guide, we’ll walk through the basics of connecting to Databricks via its REST API and pulling data into Jedox.
Step 1: Open Databricks and Understand the Structure
Start by logging into Databricks. Once inside, you’ll see the Catalog section on the left-hand side. Here’s what the main components mean:
- Catalog: The top-level container for organizing data assets.
- Workspace: Your environment where notebooks, jobs, and data live.
- Database: A logical grouping of tables within a catalog.
- Table: The actual dataset you query. In our example, the table is orders_2_drillthrough.
In the screenshot above, you can see:
- Catalog: main
- Workspace: default
- Database: default
- Table: orders_2_drillthrough
This table contains columns like Year, Month, Products, Customers, and Measures—perfect for analysis in Jedox.
Step 2: Generate a Bearer Token
To access Databricks programmatically, you need an authentication token. Here’s how:
- In Databricks, go to User Settings.
- Click Generate New Token.
- Copy the token and keep it safe (you’ll need it for the API call).
Step 3: Use the REST API to Query Data
Databricks provides a REST API that allows you to run SQL queries against your tables. The endpoint for executing SQL statements is:
POST https://yourinstancename.cloud.databricks.com/api/2.0/sql/statements/
Headers:
Authorization: Bearer {Token}
Content-Type: application/json
Body:
Here’s an example request to fetch data from orders_2_drillthrough:
{ "statement": "SELECT * FROM default.orders_2_drillthrough LIMIT 10", "warehouse_id": "your id"}
warehouse_id: The ID of the SQL warehouse you want to use (you can find this in Databricks under SQL Warehouses in the left menu).- In Jedox Integrator, create a new REST connection.
- Set the URL to the Databricks API endpoint.
- Add the Authorization header with your Bearer token.
- Configure the POST method and include the JSON body with your SQL statement.
- Map the returned data to your Jedox cube or dimension.
Step 4: Validate and Load Data
Run the extract in Jedox Integrator and check the preview. You should see rows from your Databricks table, ready to be used in Jedox for planning, reporting, or drill-through analysis.
Final Thoughts
Connecting Jedox to Databricks is a great way to keep your planning models in sync with the latest data. A common use case is pulling detailed sales transactions from Databricks into Jedox for accurate forecasting and margin analysis. Instead of exporting CSVs or relying on outdated snapshots, you can query Databricks directly and make decisions based on real-time numbers—whether it’s adjusting budgets, tracking product performance, or analyzing customer trends.
Project can be found here. If you think this helped you in your projects and saved you some time don’t hesitate to support our blog at https://buymeacoffee.com/stefanvelickovic33. It helps us to run and host the website.