Event Hub、Stream Analytics及Data Lake数据管道技术问题咨询

Hey there! Since you're new to these Azure services, let's walk through each of your questions with practical, actionable advice based on real-world use cases:

1. Data Storage Format & File Splitting #

First, let's clarify the tradeoffs for your options:

• Single large file: Terrible for scalability and query performance—definitely avoid this for massive Datastore data.
• Split files (by time/size): The sweet spot for most streaming scenarios, balancing organization and performance.
• Single object per file: Not recommended unless you have strict business requirements, as it will cripple write performance (too many small files create massive overhead for Data Lake Storage).

To fix your current issue of daily large files and enable smart splitting:

• Change output format: In your Stream Analytics output settings for Data Lake, switch the JSON format from Array to LineSeparated (each Event Hub entry becomes a standalone JSON object on its own line).
• Add windowing to your query: Modify your SQL to use a tumbling or hopping window to batch writes, which controls how often new files are created. For example:

  SELECT *
  INTO [my-data-lake]
  FROM [my-event-hub]
  TIMESTAMP BY EventEnqueuedUtcTime
  GROUP BY TumblingWindow(second, 30) -- Adjust window size based on your data throughput
  

• Configure file size limits: In the output settings, set a Max file size (e.g., 100MB)—once a file hits this size, Stream Analytics will automatically create a new one.
• Use granular path variables: Combine {date}, {time}, {partitionId}, and {windowStart} in your output path to split files into organized chunks (e.g., output/{date}/{time}/{partitionId}_window_{windowStart}.json).

If you absolutely need one file per Event Hub entry, you’d have to add a unique identifier to each record (like a GUID or Datastore entity ID) and include that in your output path. But again, this is not ideal for large-scale data.

2. Custom Filenames & Overwriting #

• Custom filenames: Stream Analytics lets you build custom paths/filenames using built-in variables (like {date}, {time}, {partitionId}) and even custom fields from your data. For example, if your records have a region field, you can use:

  output/{date}/{region}/data_{time}.json
  

  Just make sure any custom fields you use are string-type and don’t contain invalid filesystem characters.
• Overwriting files: By default, Stream Analytics won’t overwrite existing files—it appends a numeric suffix (e.g., data.json becomes data_1.json) if a duplicate name exists. There’s no native setting to enable overwriting, but you can work around this with an Azure Function triggered by Data Lake Storage events: when a new file is created, the function deletes any older file with the same name, or renames the new file to replace the old one.

3. Handling Incomplete JSON Arrays in U-SQL #

If you stick with the default JSON array format, U-SQL will throw an error when trying to read an incomplete array (since it’s invalid JSON). Here’s how to address this:

• Switch to LineSeparated JSON: As mentioned earlier, this writes each record as a standalone JSON object. Even if the file is still being written, the lines that are already saved are valid JSON, so U-SQL can read them without issues using OPENROWSET or JsonTuple.
• If you must use arrays: Use U-SQL’s IGNORE_INVALID_ROWS option when reading the file to skip the incomplete final array, or use a custom extraction logic to handle partial data. For example:

  @data =
      EXTRACT json_content string
      FROM "/output/{date}/large_file.json"
      USING Extractors.Text(delimiter:'\b'); -- Treat the whole file as a single string
  
  @parsed =
      SELECT JsonTuple(json_content) AS props
      FROM @data
      WHERE json_content IS NOT NULL AND json_content != '';
  

  Note that this is a workaround and might not capture partial data reliably. The LineSeparated format is the better long-term solution.

4. JSON Array vs. Line-Separated Objects #

Line-separated JSON (one object per line) is far better for your streaming pipeline:

• Real-time readiness: Every line written is valid JSON—no waiting for the entire array to be completed before you can query the data.
• Query performance: Tools like U-SQL, Spark, or Azure Synapse can parallelize reads across lines, which is way faster than parsing a single huge array.
• Resilience: If one line is corrupted, it only affects that single record, not the entire file.
• Flexibility: It plays nicely with Stream Analytics’ file-splitting features and makes incremental data processing much easier.

The array format only makes sense for small, batch-processed datasets—not for streaming from Event Hub.

内容的提问来源于stack exchange，提问作者Vitor Durante