Supercharge Your Hydrolix Data in Databricks With Spark

Observability is about more than just collecting data—it’s about transforming that data into actionable insights. The ability to retain all your data is powerful, but its true value lies in how effectively you can analyze it across different time scales and complexities. Spark enables you to tackle the deeper, long-running, and intricate analytical tasks that demand more advanced processing. It’s this ability to embrace complexity that helps you unlock your data and drive innovation, from uncovering hidden patterns to powering machine learning models and strategic decision making. 

What issues could you solve more easily if you could retain and query petabytes of event and log data,  analyze that data using tools like Databricks, and answer business-critical questions—all without worrying about access, costly data movement, or huge bills?

With our new Hydrolix Connector for Apache Spark, you can combine the power of Spark and Databricks with Hydrolix’s innovative approach to data management. Hydrolix eliminates the need for creating separate, potentially complex ETL pipelines because you can query your data directly from Hydrolix partitions within the Spark environment. Hydrolix has its own ETL process that automatically transforms and standardizes high volumes of data in real time. In Hydrolix, all data is “hot,” so you get low-latency queries whether the data is a minute or a year old, maximizing its value for operations, BI, and other use cases.

This solves a major challenge for data teams: accessing petabytes of full-fidelity, long-range data to tackle complex business problems—while significantly reducing the costs of ingesting, storing, and analyzing that data.

With this integration, you can:

• Use Databricks to explore event and log data stored in Hydrolix.
• Use Databricks notebooks to analyze and visualize your Hydrolix data.
• Join your log data in Hydrolix with data from other sources to generate new insights through data federation.
• Use MLlib for machine learning tasks to address business-critical use cases such as fraud detection, capacity prediction, and anticipating customer churn.
• Use the power of Hydrolix summary tables for real-time summaries in Databricks.

In this post, you’ll learn how the connector works, and how combining Hydrolix and Databricks can give your teams a competitive edge when it comes to analyzing and understanding log data.

You’ll also see the results of benchmarking data that show Hydrolix running faster than Delta Lake for most queries we tested, as well as Hydrolix’s superior compression performance over Delta Lake.

How the Hydrolix Connector for Apache Spark Works

The Hydrolix Connector for Apache Spark is much more than a basic API. It combines the strengths of both Hydrolix and Spark to accelerate query performance and perform complex operations on your Hydrolix data from Databricks.

The following graphic shows how our engineering team combined the best of both systems to create a connector that uses Spark’s DataSource V2 API to return many query results faster than Delta Lake. See the benchmarking numbers on compression and query performance.

Let’s take a closer look at each step.

1. User enters query: A user runs a Spark SQL query in Databricks, a Spark or Jupyter notebook, or anywhere they are running Apache Spark with the Hydrolix Connector for Apache Spark.
2. Spark parallelizes Hydrolix workers: For queries within Hydrolix, Hydrolix handles the parallelization of query workers. However, in the case of the connector, Spark handles parallelization. You get all the benefits of Spark’s elasticity, fault tolerance, and shuffling partitions. Plus, because the connector uses Databricks compute, it ensures that analytical workloads run independently, solving multi-tenancy challenges by preventing heavy analysis from impacting dashboards and other real-time queries on Hydrolix clusters.
3. Hydrolix optimizes the query plan: The Hydrolix connector uses Spark’s Data Source V2 API to enhance Spark’s query planning. It does this by supporting several pushdowns at the data source level, such as column pruning, predicate and aggregate pushdowns, and partition pruning. Hydrolix translates the query to ClickHouse SQL and optimizes the query plan for each parallelized work unit.
4. Hydrolix workers retrieve data from HDX partitions: Hydrolix uses a proprietary format that typically achieves 90-99% compression rates, and Hydrolix workers use indexing and predicate pushdown to retrieve narrow byte ranges of data from partitions. The data is then translated back into Spark objects. You get the benefit of Hydrolix using vectorized query execution to improve performance further.
5. Spark performs heavy computations, massive JOINs, and ML workflows: Spark is designed to handle complex resource-intensive operations, whether it’s performing massive JOINs across datasets or executing advanced machine learning workflows. It can combine Hydrolix log data with data from other sources, making it an ideal platform not just for analyzing log data but also for powering ML workflows requiring preprocessing and training.
6. Query results returned in Databricks: The final results are returned to Databricks using SQL.

Why Use Hydrolix With Spark and Databricks?

TL;DR: With Hydrolix, you can store and analyze petabytes of full-fidelity log data at a fraction of the cost—all while keeping all your data hot for queries. Teams using Spark on Databricks can query that data for new insights and train machine learning models—with no need to move that data, or disrupt other users working on the Hydrolix platform. 

For data analytics, data science, machine learning model development, and technical operations, teams need data that’s both high-fidelity and long-range. This ensures that data is granular, high-quality, and contains all outliers. However, with most platforms, especially observability platforms, log data typically isn’t retained for very long—and even worse, common practices like downsampling reduce the fidelity and quality of the data. This log data includes data that your systems generate as well as any other immutable, timestamped data that your business is collecting.

A significant issue is cost. The volume of log data has gone up 5x on average over the three-year period ending in 2023, and 35% of enterprises are ingesting at least a terabyte of log data per day. As a result, many enterprises are forced to pay high costs for that data, discard it after a short period of time, or move it into cold storage where it’s difficult to access.

Hydrolix is a platform for high volumes of log data that offers cost-effective, long-term “hot” data retention for logs. You get high-fidelity, long-term data retention (15 months by default) and the ability to quickly query any of that data regardless of age.

With the Hydrolix Connector for Apache Spark, you can combine that high-quality, long-term data with data analytics from Databricks for data science, BI, and machine learning needs.

Combine Summary Tables and Raw Data

With Hydrolix, data scientists and analysts can use powerful summary tables that can aggregate and compute incoming data in real time. By using these rollups, teams can avoid querying the underlying raw data for many queries, improving performance and reducing compute costs.

Hydrolix summary tables also handle late-arriving data without any loss in accuracy. Many platforms have issues handling late-arriving data while keeping aggregations accurate.

While aggregated data provides a high-level view, it can also serve as a transformative input for machine learning models. Precomputed rollups—such as per-minute quantiles, averages, or distinct counts—can become valuable features that enrich your models. For example, having these metrics readily available allows you to uncover correlations and patterns that would be impossible or computationally expensive to detect if these rollups had to be generated on the fly (such as with per-minute metrics over the year in a single Spark job). This accelerates experimentation and makes it easier to incorporate high-quality, temporal features into your ML workflows.

At the same time, retaining the underlying raw data long-term is equally valuable. For example, you might want to get a better understanding of why total sales dipped during a specific period. Was it related to site availability, ad placement, or seasonality? You should be able to drill down into the underlying raw data for a deeper understanding of what happened.

Unfortunately, it’s all too common for many enterprises to keep only the aggregated data while discarding the underlying data, typically because the raw data costs too much to store long-term. Even when that data is kept, it’s typically moved into frozen storage where it’s inconvenient to access.

Hydrolix uses high-density compression and decoupled object storage to dramatically reduce storage costs, allowing enterprises to keep petabytes of data much more cost-effectively than other solutions. This unique combination empowers teams to use data at every level of granularity, enriching analysis, accelerating machine learning, and driving deeper insights.

Use Cases for Hydrolix with Databricks

Let’s take a look at some of the use cases for using Hydrolix with Databricks.

• Predicting inventory and product demand: Forecasting inventory can be extremely challenging, and involves a deep understanding of factors such as manufacturing, supply chains, delivery, customer demand, seasonality, and pricing. Log data from warehouse and delivery truck sensors, customer transactions, and even CDN data on which assets have been most viewed can help predict product demand and production needs.
• Capacity planning: Planning capacity for systems is critical whether it’s for major events or off-peak times. How do you ensure you have enough capacity for a major streaming event or a sale so that you don’t have outages or other issues? Conversely, how can you save money and increase margins during off-peak times by lowering capacity?
• Detecting outliers for anomaly and threat detection: Outliers can skew datasets, and they may be disregarded for certain use cases for this very reason. But outliers can be both highly unusual and mission critical. Consider, for example, an anomaly or threat that is extremely rare (and may only have a handful of related datapoints over the course of a year), but that consistently precedes a major outage or issue. Data that has been sampled or that covers only a short period is likely to miss these outliers.
• Fraud detection: Fraud costs are over $5 trillion per year, with businesses losing an average of 5% of their annual revenue to fraud. By observing transaction logs in real time and creating predictive models to indicate when fraud is likely occurring, businesses can reduce losses due to fraud and increase their margins.
• Training machine learning models: Model training is an ongoing process, and the Hydrolix Connector for Apache Spark allows teams to continuously train models on log data for a wide range of use cases, including all of the ones listed here.

Benchmarking Hydrolix vs Delta Lake

Using Spark with Hydrolix as the underlying data store, our results showed that Hydrolix performed significantly better on most queries we benchmarked and also achieved greater compression rates compared to using Databricks with Delta Lake. The benchmarked data includes ~8.5 billion rows of log data collected over a period of ~9.5 months. Altogether, the uncompressed volume is approximately 13 TiB of data. See the full methodology.

Compression: Hydrolix vs Delta Lake

Data in Delta Lake is stored in Parquet, a columnar format with high compressibility. Meanwhile, Hydrolix uses a proprietary high-density compression format (.hdx) which is designed specifically for log and event data. Both compression formats are very effective for log data, which often has a high number of repeated values.

For Delta Lake, the complete dataset is stored in AWS S3 and configured as hive_metastore. The compressed data is 543.9 GiB in-bucket across 6474 files, leading to an admirable compression rate of 23.7:1 (95.8%) compression.

For Hydrolix, the complete dataset is stored in Google Cloud Storage (GCS) and consists of 321.24 GiB in-bucket across 5434 partitions, leading to an even greater compression rate of 41.1:1 (97.6%) compression.

Both Delta Lake and Hydrolix performed extremely well in terms of compression. Hydrolix’s 41x compression rate did significantly outperform Delta Lake’s 24x compression rate, which means greater cost-effectiveness and a smaller storage footprint, especially for petabyte-scale volumes of data.

Query Performance: Hydrolix vs Databricks on Delta Lake

Our engineering team compared the results of 5 queries, including a histogram, a basic indexed query, and several aggregate queries.

The Hydrolix queries were made from Databricks to Hydrolix using the Hydrolix Connector for Apache Spark. The Databricks queries were made to Delta Lake. Note that these queries do not use Databricks Photon because the Hydrolix Spark connector doesn’t currently support some of the columnar execution features that are required by Photon. We plan to add Photon support and will provide a benchmarking update in the near future. And considering the higher cost of using Photon with Databricks (2x more than standard Databricks compute), Hydrolix can give you many of the performance gains of Photon while lowering instead of increasing your TCO.

An overview of how each query performed (10 runs across the full dataset) is included in the next graphic.

Hydrolix with the Spark connector performed significantly better for the histogram, group by integer, group by string, and basic indexed queries.

Delta Lake performed better on the approximate percentile query. When the query was modified to benefit from the Hydrolix summary tables that store aggregate data, Hydrolix demonstrated a greater than 10x speedup.

Histogram query

This query generates a histogram of how many times a particular message, destination pair appears where the destination is one of few known values. In Hydrolix, this query uses a string index on destination and a hash-based group by on message, destination.

This query was nearly 3x as fast for Hydrolix compared to Delta Lake.

Group by string query

This query averages the bytes (integer) field from rows where app (a string) is one of a few constant values, grouped by message, path (both strings). In Hydrolix, this query makes use of string indexing on app and a hash-based group by on message, path.

This query is ~1.3x as fast on Hydrolix compared to Delta Lake.

Group by integer query

This query adapts the group by string query to use only integers. It returns the average bytes for each grouping of data.mem_size, data.index_size, data.data_size (three integer values).

This query is ~1.6x as fast on Hydrolix compared to Delta Lake.

Basic indexed query

This is a simple aggregation query that returns the value of app, a string that is one of a few constant values. In Hydrolix, this query makes use of string indexing on app.

This query is ~2.3x as fast on Hydrolix compared to Delta Lake.

Approximate percentile query

This query uses various aggregators to estimate the median, p95, p99, and max bytes value per day of logs in the dataset. There are three versions of this query:

• The Delta Lake query uses the spark percentile_approx aggregator. 
• The Hydrolix version uses the spark percentile_approx aggregator
• A third version uses Hydrolix with summary tables. This query makes use of Clickhouse’s quantile aggregator, as maintained by the Hydrolix summary table. Instead of querying the raw data, this version of the query uses the summary table.

Hydrolix is ~0.8x as fast as Databricks for the approximate percentile query. With a summary table, the Hydrolix query is 12.7x faster.

Delta Lake also has aggregate table functionality, which was not tested for these benchmarks. The goal of benchmarking Hydrolix summary tables in this situation is not to compare apples to apples, but rather to demonstrate how summary tables can dramatically speed up aggregate queries.

As these results show, Hydrolix was faster for all queries except for the approximate percentile query. This is likely because the Hydrolix Connector for Apache Spark connector does not currently “push down” percentile_approx, so it must stream enough data from cloud storage to the Spark executors to do the aggregation in-memory, impacting performance. We are planning future optimizations on the connector to further improve performance across a wide range of queries.

Methodology

Queries were run using r6id.2xlarge (64GB / 8 CPU) for the driver and 4x c5d.12xlarge (96GB / 48CPU) for workers, with autoscaling on, but min and max both set to 4 workers. The queries mostly used the default configuration, with a few exceptions, including Photon being disabled.

Each query was executed 10 times against each data source, in pairs starting with Hydrolix (Hydrolix, Deltalake), with 10 pairs of trials per query. Queries ran one at a time, with new queries starting only after the previous query finished processing all results.

Each query ran with the framework spark.sql(<query>).write.mode("overwrite").format("noop").save(). This ensures the entire query runs and all data is sent back to the driver.

No caches were explicitly reset between query executions (eg, .unpersist() or spark.conf.set('spark.databricks.io.cache.enabled', False)), and no caches were explicitly set between query executions (eg, .cache()).

Before running any queries, each system ran a warm-up query of SELECT * FROM {table} WHERE timestamp <= {min_timestamp}. This allowed both systems the opportunity to run any first-run setup while only preloading a single partition (to avoid excessive caching that may skew results for the next query).

Queries were timed using the Python standard library.

Caveats

Both Hydrolix and Spark are powerful query engines with the potential for tremendous optimizations and tuning. Machines weren’t resource-optimized for these queries, nor were all Spark settings tuned. No additional effort was made to optimize Delta Lake performance, and most notably, Photon was disabled for the benchmarking.

When the Spark cluster is underprovisioned for query load, both Hydrolix and Delta Lake can still produce results, but Deltalake does so faster. In other words, for small clusters, over the same dataset, Delta Lake is more performant than Hydrolix. For large clusters that are appropriately sized for the workload, Hydrolix is more performant than Delta Lake.

Next Steps

Stay tuned for future updates to the Hydrolix Connector for Apache Spark—that includes interoperability with other tools beyond Databricks as well as performance optimizations. We’ll also be writing more on how we use the connector internally to monitor and analyze our infrastructure.

Interested in trying out Hydrolix with Databricks? Contact Hydrolix about a proof of concept or demo.