Databricks Deployment

Overview

Databricks is a unified, open analytics platform built on top of Apache Spark that offers features like a collaborative workspace, data lakehouse capabilities, and integrated machine learning workflows. With the help of the Hydrolix Connector for Apache Spark, you can improve query speed and storage costs by using Hydrolix as the backing store for your Databricks cluster.

Requirements

Dependencies

• Hydrolix Cluster: A running Hydrolix cluster with a version compatible with your connector version. See the compatibility matrix. Deployment instructions for your preferred cloud vendor (AWS, Google, Linode, or Microsoft) can be found here.
• Databricks Spark Cluster: A Databricks account with a deployed Spark cluster.

Hydrolix credentials

Setup requires credentials for a Hydrolix account that has the required permissions. These credentials may be either:

• A username and password for a Hydrolix user account
• A service account token. This requires connector v3.4.0 or later. See Service account token setup.

Service account token setup

Run the following commands in one terminal session. They require curl, jq, and a super admin account to perform the API calls. Replace the cluster URL and the admin credentials.

1. Log in and get the org UUID:

   HDX="https://{myhost}.hydrolix.live/config/v1"
   
   LOGIN=$(curl -s -X POST "$HDX/login" \
     -H 'Content-Type: application/json' \
     -d '{"username":"{admin email}","password":"{admin password}"}')
   
   ADMIN_TOKEN=$(echo "$LOGIN" | jq -r '.auth_token.access_token')
   ORG=$(echo "$LOGIN" | jq -r '.orgs[0].uuid')
   

2. Create a role with the permissions listed in Required user permissions:

   ROLE=$(curl -s -X POST "$HDX/roles/" \
     -H "Authorization: Bearer $ADMIN_TOKEN" \
     -H 'Content-Type: application/json' \
     -d '{
       "name": "spark-connector-reader",
       "policies": [
         {
           "permissions": ["config_blob_org","catalog_urls_table",
             "view_hdxstorage","view_table","view_transform","view_view",
             "select_sql","select_catalog_sql","select_metadata_sql",
             "show_columns_sql","show_tables_sql","show_databases_sql",
             "dictGet_sql","view_function"],
           "scope_type": "org", "scope_id": "'"$ORG"'"
         },
         { "permissions": ["view_org"] }
       ]
     }')
   
   ROLE_ID=$(echo "$ROLE" | jq -r '.id')
   

3. Create a service account and assign the role to it:

   SA=$(curl -s -X POST "$HDX/service_accounts/" \
     -H "Authorization: Bearer $ADMIN_TOKEN" \
     -H 'Content-Type: application/json' \
     -d '{"name":"spark-connector"}')
   
   SA_UUID=$(echo "$SA" | jq -r '.uuid')
   
   curl -s -X POST "$HDX/roles/$ROLE_ID/add_user/" \
     -H "Authorization: Bearer $ADMIN_TOKEN" \
     -H 'Content-Type: application/json' \
     -d '{"users":[{"uuid":"'"$SA_UUID"'"}]}'
   

4. Issue the token:

   curl -s -X POST "$HDX/service_accounts/$SA_UUID/tokens/" \
     -H "Authorization: Bearer $ADMIN_TOKEN" \
     -H 'Content-Type: application/json' -d '{}' | jq -r '.token'
   

The command prints the service account token. Store it in your platform's secrets manager and set it as the service_account_token connector property. Note the token's expiry for rotation.

For more detail on service accounts, roles, and tokens, see Manage Service Accounts.

Required user permissions

The connector requires specific access to query a Hydrolix cluster. Assign the following permissions to the account whose credentials you supply, whether a service account or a user account. Super admin access isn't required.

Permission name	Level
view_org	Global
config_blob_org	Org
view_hdxstorage	Org
catalog_urls_table	Org, project, or table
view_table, view_view, view_transform	Org, project, or table
select_sql, select_catalog_sql, select_metadata_sql	Org, project, or table
show_databases_sql, show_tables_sql, show_columns_sql	Org
dictGet_sql, view_function	Org

To restrict the account to specific data, scope the project-or-table-level permissions to those projects or tables instead of the org. To query multiple tables in the same Hydrolix project, scope those permissions to the project level instead of granting the permissions for each table.

Grant view_org at the global level

Org-level view_org isn't sufficient: the connector discovers the org by listing /orgs, which requires view_org at the global level. Without it, queries fail with the error config not loaded, yet. The Hydrolix UI can't create a global-level policy, so create the role with the Config API using a policy that has no scope fields:

{
    "name": "spark-connector-reader",
    "policies": [
        {
            "permissions": ["config_blob_org","catalog_urls_table",
            "view_hdxstorage","view_table","view_transform","view_view",
            "select_sql","select_catalog_sql","select_metadata_sql",
            "show_columns_sql","show_tables_sql","show_databases_sql",
            "dictGet_sql","view_function"],
            "scope_type": "org",
            "scope_id": "{org uuid}"
        },
        { "permissions": ["view_org"] }
    ]
}

The API stores the second policy, which has no scope fields, as a global-level policy.

Runtime Requirements

Java Runtime: Must use Java 11 or later

Databricks cluster compatibility

The connector JAR is built for Scala 2.12, so it requires a Scala 2.12 Databricks runtime.

Databricks Runtime 16.4 LTS is the last Scala 2.12 runtime: 17.x and later are Scala 2.13/Spark 4 and aren't supported. Note that 16.4 LTS is offered in two variants: choose 16.4 LTS (Scala 2.12), not the Scala 2.13 variant.

Use Dedicated access mode, labeled Dedicated (formerly: Single user) in the Databricks UI, with the Unrestricted policy:

Runtime version	Supported	Access mode: Dedicated (formerly: Single user)	Access mode: Standard (formerly: Shared)	Notes
17.x and later	✗	--	--	Scala 2.13 / Spark 4 runtimes; not compatible with the Scala 2.12 connector JAR.
16.4 LTS (Scala 2.12)	✓	✓	✗	Defaults to Java 17; no JNAME setting needed.
15.4 LTS	✓	✓	✗	Works with Java 17. Set the environment variable JNAME=zulu17-ca-amd64.
14.3 LTS	✓	✓	✗	Works with Java 11. Set the environment variable JNAME=zulu11-ca-amd64.

The connector must be registered under the spark_catalog name. See Catalog name.

Setup steps

1. Create a Summary Table of the Data You Will Be Querying
2. Set the Hydrolix Connector Parameters
3. Set the JNAME Environment Variable
4. Upload and Install the Hydrolix Connector for Apache Spark

At which point you can start querying your data using Spark.

Optional steps are the following:

1. Joining Hydrolix tables with Unity Catalog tables
2. Troubleshooting

Create a summary table

Create a summary table, including a transform, of the data you plan to query. Using a summary table allows you to perform some aggregation operations in the cluster, effectively reducing query time and result data set size. Instructions for creating a summary table using the Hydrolix UI and API are on the Summary Tables page. While this step can be skipped, it's highly recommended.

The general structure of summary transforms and their limitations are explained in this section on creating summary transforms.

Set the Hydrolix connector parameters

The Hydrolix Connector for Apache Spark requires some configuration parameters. Set these parameters in the Databricks UI on your existing Spark cluster. Your Hydrolix credentials, which are included in these parameters, should be stored in the Databricks Secrets Manager.

Catalog name

Register the connector as spark_catalog, replacing Spark's default catalog.

Databricks requires this name. Compute ignores custom catalog names, so under any other name the catalog never appears, and queries fail with catalog not found.

Authentication mode

The connector authenticates to the Hydrolix cluster with one of two kinds of Hydrolix credentials:

• Hydrolix user account: Set username and password to the Hydrolix user account credentials.
• Service account token: Set the service_account_token property. username and password are ignored.

Store credentials in your platform's secrets manager rather than pasting them into the cluster configuration.

Service account tokens require connector v3.4.0 or later

Connector versions before v3.4.0 (up to and including v3.0.0-v5.6.0) must authenticate to the cluster's Config API with username and password. Service account tokens won't work.

Spark configuration is dynamically reloaded for every query

The Hydrolix Connector reloads its configuration for every query. To change the configuration inside spark-shell or a Jupyter notebook, set spark.conf.set("some_setting", "value").

Verify user permissions

Make sure the user supplied in the Spark properties has the required permissions for the underlying Hydrolix cluster.

Connector parameters

Property	Value	Description
spark.sql.catalog.spark_catalog	io.hydrolix.connectors.spark.SparkTableCatalog	The fully qualified name of the class to instantiate for the connector's catalog. The catalog name must be spark_catalog. See Catalog name.
spark.sql.extensions	io.hydrolix.connectors.spark.SummaryUdfExtension	A comma-separated list of fully-qualified SQL extension classes -- using summary tables requires including SummaryUdfExtension in this set.
spark.sql.catalog.spark_catalog.cluster_url	https://hostname.hydrolix.live	The Hydrolix cluster URL. This is the recommended way to configure the connection. If this field is set, the legacy jdbc_url and api_url fields are unnecessary and will be ignored.
spark.sql.catalog.spark_catalog.username	{{secrets/path/to/username}}	Username to log into the Hydrolix cluster.
spark.sql.catalog.spark_catalog.password	{{secrets/path/to/password}}	Password to log into the Hydrolix cluster.
spark.sql.catalog.spark_catalog.service_account_token	{{secrets/path/to/token}}	Service account token used to authenticate to the Hydrolix cluster as an alternative to username/password (connector v3.4.0 or later). When set, username and password are ignored.
spark.sql.catalog.spark_catalog.jdbc_protocol	https	Optional. Defaults to https if not provided. Used with the cluster_url and jdbc_port configs to derive the JDBC URL.
spark.sql.catalog.spark_catalog.jdbc_port	8088	Optional. Defaults to 8088 if not provided. Used with the cluster_url and jdbc_protocol configs to derive the JDBC URL.
spark.sql.catalog.spark_catalog.hdx_partitions_per_task	1	Optional. Defines how many Hydrolix partitions will be read by each Spark partition. Default value is 1. For example, if this setting is set to 2 and partition planning returns 40 Hydrolix partitions, the query launches 20 Spark tasks each processing 2 Hydrolix partitions. Left unset, 40 Spark tasks would be launched processing 1 Hydrolix partition each.
spark.sql.catalog.spark_catalog.jdbc_url	jdbc:clickhouse:https://hostname.hydrolix.live:8088?ssl=true	Legacy. Prefer cluster_url. JDBC URL of the Hydrolix query head. Note that the ClickHouse JDBC driver requires a valid database name in the URL, but the connector will read any database the user has access to. This parameter is ignored when cluster_url is set.
spark.sql.catalog.spark_catalog.api_url	https://hostname.hydrolix.live/config/v1/	Legacy. Prefer cluster_url. URL of the Hydrolix config API, usually must end with /config/v1/ including the trailing slash. This parameter is ignored when cluster_url is set.

To set these parameters, expand the Advanced Options heading, open the Spark tab, and enter the key/value pairs into the Spark config. Each key should be separated from its value by a single space like the following (replacing with your Hydrolix cluster's values):

Service account tokenUsername and password

spark.sql.catalog.spark_catalog io.hydrolix.connectors.spark.SparkTableCatalog
spark.sql.extensions io.hydrolix.connectors.spark.SummaryUdfExtension
spark.sql.catalog.spark_catalog.cluster_url https://hostname.hydrolix.live
spark.sql.catalog.spark_catalog.service_account_token {{secrets/path/to/token}}

spark.sql.catalog.spark_catalog io.hydrolix.connectors.spark.SparkTableCatalog
spark.sql.extensions io.hydrolix.connectors.spark.SummaryUdfExtension
spark.sql.catalog.spark_catalog.cluster_url https://hostname.hydrolix.live
spark.sql.catalog.spark_catalog.username {{secrets/path/to/username}}
spark.sql.catalog.spark_catalog.password {{secrets/path/to/password}}

In the Databricks UI, the above configuration looks like this:

Set the JNAME environment variable

Recent Databricks runtimes (16.4 and later) default to Java 17 and require no JNAME setting. For older runtimes, select the Java version with the JNAME environment variable as listed in the Databricks cluster compatibility table. For example, JNAME=zulu17-ca-amd64 for Java 17 on Databricks 15.4 or JNAME=zulu11-ca-amd64 for Java 11 on Databricks 14.3. Other JVM implementations may work with the Hydrolix Connector as long as they're Java 11 or later.

Click the Create Compute button to create your Spark cluster.

Upload and install the Hydrolix connector

There are two options for installing the Hydrolix Connector JAR: from S3 or from a Unity Catalog Volume. After installation, you can analyze your Hydrolix data in Spark.

DBFS library installs are no longer supported

Databricks supports DBFS-hosted libraries only on Databricks Runtime 14.3 LTS and below. Use the S3 or Volumes methods below instead.

Install from an S3 bucket

In your Spark Cluster's UI:

1. Navigate to the Libraries tab and select Install new
2. Select File path/S3 for Library Source and JAR for Library Type

3. Set File path to the following:

   s3://hdx-public/spark-connector/latest/spark-connector-latest.jar
   

4. Select Install

5. Restart your cluster

Install the JAR from a volume

1. Download the latest version of Hydrolix Connector JAR
2. Navigate to the cluster's Libraries tab and select Install new
3. Select Volumes for Library Source
4. Navigate to the destination volume (/Volumes/{catalog}/{schema}/{volume}). Then either select a JAR already present, or use Drop one file here, or browse to upload the downloaded JAR
5. Select Install
6. Restart your cluster

(Google Cloud Platform only) Set Hydrolix cluster storage bucket credential

If you have set up your Hydrolix cluster with a default GCP storage bucket and you would like to query your default bucket, follow the GCP setup instructions to configure a credential with your storage bucket before querying. This credential is required so the cluster can issue the presigned URLs the connector reads partitions through. GKE workload-identity credentials can't sign GCS URLs on their own.

Query

After you have configured your cluster, you can use the Hydrolix Connector for Apache Spark in a Spark notebook.

To begin using the connector with a Spark notebook, you'll use one of the two commands depending on your use case:

• Python or Scala fragment: sql("USE CATALOG spark_catalog")
• SQL fragment: USE CATALOG spark_catalog;

Alternatively, you can prepend each table you want to query from your Hydrolix back-end with spark_catalog..

Summary table query instructions

Summary table queries have unique requirements.

Wrap summary aliases with hdxAgg()

Wrap summary aliases in SELECT statements with hdxAgg(). For example, you might run the following to query both an aliased column (summary_column_alias) and a non-summary column (non_summary_column):

SELECT hdxAgg('summary_column_alias'), non_summary_column
FROM spark_catalog.project.summary_table
GROUP BY {time_field_alias}

You can read more about creating and querying summary tables in the Summary Tables documentation.

The hdxAgg() function resolves name conflicts between summary table alias columns and Spark function names. For example, count may be a column name in a summary table, but count() is also a pre-existing Spark function. Using the hdxAgg() function disambiguates the two.

The hdxAgg() function also resolves name conflicts between multiple summary tables. For example, one summary table might have count(timestamp) AS total in its definition while another might have count(distinct username) AS total in its definition. The hdxAgg() function disambiguates total() and ensures it works when querying either table.

Load the Hydrolix datasource

Load the Hydrolix datasource. Doing so loads the required hdxAgg() function. The options for loading the Hydrolix datasource are:

• Run USE CATALOG spark_catalog
• Run any query against a non-summary table
• Run io.hydrolix.connectors.spark.HdxUdfRegistry.enableSummaryTables(spark)

Summary table query instructions (v1.0.0 only)

The v1.0.0 release of the Hydrolix Connector for Apache Spark has unique instructions for querying summary tables.

1. To enable querying the summary table you created during the Create a summary table step, run the following line in a Spark shell or in a PySpark session:

   io.hydrolix.connectors.spark.HdxUdfRegistry.registerSummaryTable(spark, "{project.summary_table_name}")
   

2. Reference any summary table column aliases using the syntax summary_column_alias(). For example, suppose there is a summary table called my_project.my_summary_table which includes the following in its definition:

   SELECT ...
   sum(salary) AS totalSalary,
   department,
   ...
   

This table can then be queried with:

SELECT totalSalary(), department FROM spark_catalog.my_project.my_summary_table GROUP BY department

Example queries

The following examples query the hydro.logs table directly rather than through a summary table. They use the spark_catalog catalog name, as Databricks requires.

PythonSqlScala

import time;

start = time.time()
df = spark.sql("SELECT app FROM spark_catalog.hydro.logs WHERE app in ('query-peer', 'query-head', 'intake-head', 'intake-peer') AND timestamp >= '2023-12-17 12:00:00' AND timestamp < '2024-02-29 12:00:00'").show()
end = time.time()
#df.show()
print(f"HDX select app query took {end - start} seconds")

start = time.time()
df = spark.sql("SELECT COUNT(*) FROM spark_catalog.hydro.logs WHERE timestamp < '2024-10-18 00:00:00'").show()
end = time.time()
print(f"HDX count query took {end - start} seconds")

%sql

USE CATALOG spark_catalog;

SELECT
  DISTINCT(`kubernetes.container_name`),
  `min_timestamp`
FROM
  hydro.logs
ORDER BY `min_timestamp` DESC
LIMIT 100

%scala
import org.apache.spark.sql.functions.col

sql("USE CATALOG spark_catalog");

val logs = spark.sqlContext.table("hydro.logs")

val recent = logs.filter(col("timestamp") > "2023-06-01T00:00:00.000Z")

recent.count()

Join Hydrolix tables with Unity Catalog tables

On a Unity Catalog-enabled workspace, the connector is already registered as spark_catalog. See Catalog name. Therefore, joining Hydrolix data with Unity Catalog tables needs no extra connector configuration:

1. Use Dedicated access mode (labeled Dedicated (formerly: Single user) in the Databricks UI) and Unrestricted policy in the cluster configuration.
2. Reference Hydrolix tables through spark_catalog and Unity Catalog tables through their own catalog names (for example main.schema.table).

The correct syntax for switching the session to the Hydrolix catalog is:

USE CATALOG spark_catalog;

Replace Spark's default catalog

Registering the connector as spark_catalog replaces Spark's default catalog with the Hydrolix implementation. Every spark.sql.catalog.* line must consistently use spark_catalog.

Now you can join Hydrolix data with any other tables including Unity Catalog tables.

Group access to Unity Catalog tables

If a dedicated cluster is created for a user group, access to Unity Catalog tables is extended based on the group permissions, not on those of individual group members. In other words, the entire group must have access to the Unity table.

Troubleshoot

Authentication error

If you see "access denied" errors from the Hydrolix database when making queries, ensure the cluster username and password are correct, and make sure that user has query permissions.

User permissions

Partitions in a table might be distributed across different storage buckets.

If the user set in the Hydrolix Connector configuration doesn't have the required permissions for the table's storage, the query fails and returns an error response.

Error querying summary tables

If a user sees this error:

org.apache.spark.sql.AnalysisException: [UNRESOLVED_ROUTINE] Cannot resolve function `hdxAgg` on search path [`system`.`builtin`, `system`.`session`, `spark_catalog`.`default`]

The fix is to load the Hydrolix datasource. You can do so by doing any one of the following:

• Run io.hydrolix.connectors.spark.HdxUdfRegistry.enableSummaryTables(spark). This performs a global setup step which creates the hdxAgg function.
• Run USE CATALOG spark_catalog. This loads the Hydrolix datasource.
• Run any query against a non-summary table. This loads the Hydrolix datasource.

Limitations

Read-only

The Hydrolix Connector for Apache Spark is read-only. ALTER and INSERT statements aren't supported.

Related information

• Supercharge Your Hydrolix Data in Databricks With Spark - How to use the Hydrolix Spark Connector to analyze Hydrolix data in Databricks notebooks
• Unlocking New Insights with Hydrolix and the Unity Catalog - How to register Hydrolix as a data source in Databricks Unity Catalog for governed access