Microsoft Fabric Deployment

Overview

Microsoft Fabric is an end-to-end data platform that unifies data engineering, data science, real-time analytics, and business intelligence under one SaaS offering. With the help of the Hydrolix Connector for Apache Spark, you can improve query speed and storage costs of your Fabric-deployed Spark cluster by using Hydrolix as the backing store.

Requirements

Prerequisites

• 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 in Select your cloud provider.
• Fabric Spark Cluster: A Microsoft Fabric account with a deployed Spark cluster.

Microsoft Requirements

Dependency	Description	Instructions
Resource Group	The primary purpose of setting up a new Resource Group is to keep related services logically organized together.	Create a resource group
Capacity	Capacity refers to the resource limits and reservations assigned to nodes for managing workloads in Microsoft Fabric.	Create a new capacity
(Optional) Key Vault	Azure Key Vault is a cloud service provided by Microsoft Azure that helps you securely store and manage sensitive information, such as cryptographic keys, passwords, certificates, and other secrets. Use one to securely store Hydrolix credentials.	Use Microsoft's Secrets documentation to create a Key Vault using your preferred method (Portal, CLI)

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.

Setup steps

1. Create a summary table
2. Create and configure a workspace

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

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.

Create and configure a workspace

A workspace in MS Fabric is a collaborative environment where users can create, manage, and share data, reports, dashboards, and other analytics assets. The following steps walk you through the process of creating and configuring a workspace to query Hydrolix data with the Hydrolix Connector for Apache Spark using Python notebooks.

To create a new workspace, follow these steps.

1. Open the Capacity created in a previous step. Verify that it's running.
2. Navigate to Microsoft Fabric.
3. In the left-hand panel, select Workspaces > + New workspace.
4. Fill in a name for the new workspace.
5. Under License mode, select Fabric Capacity and choose the created capacity from the available options.
6. Save the workspace.
7. Open your created workspace.
8. Select Workspace settings > Data Engineering/Science > Spark settings > Environment.
9. Enable Set default environment then select Workspace default > New environment.

1. Provide a custom name for the new environment. The new environment settings will open immediately after creation.
2. Navigate to Custom Library and upload the Hydrolix Connector for Apache Spark JAR file.

1. Navigate to Spark properties and configure the connection to your Hydrolix cluster using the following properties.

Property	Value	Description
spark.sql.catalog.hydrolix	io.hydrolix.connectors.spark.SparkTableCatalog	The fully qualified name of the class to instantiate when you ask for the hydrolix catalog
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.hydrolix.cluster_url	https://hostname.hydrolix.live	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.hydrolix.username	{hdx-username}	Username to login to the Hydrolix cluster
spark.sql.catalog.hydrolix.password	{hdx-password}	Password to login to the Hydrolix cluster
spark.sql.catalog.hydrolix.service_account_token	{hdx-service-account-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.hydrolix.hdx_partitions_per_task	1	Optional. Defines how many HDX 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 HDX partitions, the query launches 20 Spark tasks each processing 2 HDX partitions. Left unset, 40 Spark tasks would be launched processing 1 HDX partition each.
spark.driver.extraJavaOptions	-Dio.netty.tryReflectionSetAccessible=true	Optional. Set this option if you want to enable MS Fabric Native execution engine for your Spark queries.
spark.executor.extraJavaOptions	-Dio.netty.tryReflectionSetAccessible=true	Optional. Set this option if you want to enable MS Fabric Native execution engine for your Spark queries.
spark.sql.catalog.hydrolix.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.hydrolix.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.hydrolix.jdbc_url	jdbc:ch://{host}:{port}/{database}?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.hydrolix.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.

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.

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.

  For example:

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

  This example uses hydrolix as the catalog name; use whatever name you configured for the connector. On Unity Catalog-enabled Databricks workspaces, the catalog name must be spark_catalog.

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.

1. Save and Publish your Spark configurations. Wait until the publishing process is complete.
2. Return to your workspace, select the newly created environment as your default environment, and select Save.
3. Add a new item by selecting + New item > Notebook in your workspace.
4. Run a simple query from the Notebook to test the connection.

query = """SELECT app, avg(num_partitions) FROM hydrolix.hydro.logs WHERE app in ('query-peer', 'query-head', 'intake-head', 'intake-peer')
and timestamp >= '2025-01-17 12:00:00' AND timestamp < '2025-01-18 12:00:00' GROUP BY app"""
df = spark.sql(query)
df.show(10, truncate=False)

Query

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

Catalog name

The examples below use hydrolix as the catalog name. Use the catalog name you configured for the connector. On Unity Catalog-enabled Databricks workspaces, this must be spark_catalog, which requires USE CATALOG spark_catalog and spark_catalog.{schema}.{table}.

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 hydrolix")
• SQL fragment: use hydrolix;

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

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 hydrolix.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 hydrolix
• 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 hydrolix.my_project.my_summary_table GROUP BY department

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.
• On Unity Catalog-enabled Databricks workspaces, run USE CATALOG spark_catalog. On a workspace without Unity Catalog, run the use hydrolix fragment. 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

• Get More Insights From Log Data With Hydrolix and Microsoft Fabric - How to use the Hydrolix Spark Connector with Microsoft Fabric for large-scale log analytics