Troubleshooting Late-Arriving Data in Hydrolix

Late-arriving data is a common issue in real-time analytics, and it’s often unavoidable. For issues like network bottlenecks, late-arriving data from mobile devices and other sensors, and outages in external services, you need to have a solution like Hydrolix that handles and sorts late-arriving data instead of discarding it.

But if possible, you should fix the root cause of late-arriving data. Maybe some bad code made it into production or you have services that need more compute resources. When log data arrives late, it can lead to inaccurate real-time queries and metrics, longer mean time to resolution (MTTR), and negative impacts for your customers.

To address the issue, you can monitor the latency of your services and ingest pipelines. Once you detect which sources are having issues with ingest latency, you can investigate further. Let’s take a look at how you can monitor ingest latency in Hydrolix.

Monitoring Ingest Latency in Hydrolix

Hydrolix transforms all incoming data before storage, giving you tremendous flexibility over the shape of your data. Each data source has its own transform, and you can add an ingest_latency (or similarly named) field for any sources where you want to monitor latency.

Hydrolix is optimized for time, and every data source must include a timestamp column that is marked as "primary" in its corresponding transform. Typically, this is the time that the event occurred. Log data may also have other timestamps such as the log creation time.

The latency field will compute the difference between the timestamp of the log creation time (or when the event happened if there isn’t a timestamp at creation time) and “now”—the moment when Hydrolix transforms the log line.

If you aren’t familiar with transforms yet, check out this post on how to transform your data in Hydrolix.

The snippet below shows part of a transform file with the configuration you’ll need to include an ingest_latency field.

{
  "name": ...,
  "description": ...,
  "type": ...,
  "settings": {
    ...
    "sql_transform": "SELECT datediff('s', timestamp, now64(3)) as ingest_latency, ... * FROM {STREAM}", 
    "output_columns": [
      {
        "name": "timestamp",
        "datatype": {
          "type": "epoch",
          "format": "s",
          "primary": true,
          "resolution": "ms"
        }
      },
      {
        "name": "ingest_latency",
        "datatype": {
          "type": "uint32",
          "index": true,
          "source": {
            "from_input_field": "sql_transform"
          },
          "format": null,
          "resolution": "seconds",
          "default": null,
          "script": null,
          "suppress": false
        }
      },
...
]
}

Note that the ... are placeholders in the snippet above, not part of the syntax. You’ll need to replace them with the rest of your configuration.

Next, you can specify a SQL transform that will compute the latency at ingest time. You can do this in the "sql_transform" field with a\ SELECT… * FROM {STREAM} statement. Here, the ... is a placeholder for other SQL transforms you may be including. * is required to ensure that all the SQL transforms you specify are applied, and {STREAM} is Hydrolix-specific syntax that refers to the log data that the transform file is processing. In this case, you want to compute the difference between the primary timestamp and now:

SELECT datediff('s', timestamp, now64(3)) as ingest_latency...

Finally, you need to specify the ingest_latency column in the transform:

...
{
        "name": "ingest_latency",
        "datatype": {
          "type": "uint32",
          "index": true,
          "source": {
            "from_input_field": "sql_transform"
          },
          "format": null,
          "resolution": "seconds",
          "default": null,
          "script": null,
          "suppress": false
        }
      },
...

The key thing to note here is the source, which needs to be the sql_transform:

"source": {
  "from_input_field": "sql_transform"
 },

The destination table will now include an ingest_latency column for the source. With Hydrolix, you can ingest many sources into a single table, so you can easily compare latency across logically-grouped sources (such as CDNs).

Now you can query ingest_latency and add it to dashboards and alerts. Here’s a sample query using quantiles that you can use as a daily check:

SELECT 
toStartOfDay(timestamp) as time, 
quantiles(0.5, 0.75, 0.95, 0.99)(ingest_latency) as quantiles_50_75_95_99, 
avg(ingest_latency) 
from destination_table.logs 
WHERE timestamp < now()
AND timestamp > now() - INTERVAL 1 DAY
GROUP BY time

Just change destination_table to the name of the table being queried and this will return the 50th, 75th, 95th, and 99th quantile for ingest_latency. This can be a good query to add to one of your dashboards.

Next Steps

• Check out the transform documentation to learn more about transforming data in Hydrolix. 
• Learn how Hydrolix handles late-arriving data by design.
• If you’re not using Hydrolix yet and would like to learn more, contact Hydrolix about a proof of concept or demo.