JSON column: StreamFailureError

[alanzhang25]

I have a JSON column on a CH table and am just trying to retrieve that information and get a error of something like:

clickhouse_connect.driver.exceptions.StreamFailureError: unrecognized data found in stream: 20617373697374616e742e3049206e65656420796f7520746f20736f6c76652074686973206d6174682070726f626c656d3a2031202b2031203d203f01010673797374656d047573657200000000000000000000000000000000ff012b6d6574612d6c6c616d612f4d6574612d4c6c616d612d332e312d38422d496e7374727563742d547572626fff00020000000000000001011c596f752061726520612068656c7066756c20617373697374616e742e3049206e65656420796f7520746f20736f6c76652074686973206d6174682070726f626c656d3a2031202b2031203d203f01010673797374656d047573657200000000000000000000000000000000ff012b6d6574612d6c6c616d612f4d6574612d4c6c616d612d332e312d38422d496e7374727563742d547572626f01ff2222633063646336376537323635623930636261643436363261656163383931346622ff000100000000000000010473746f70000000000000000000012c54686520736f6c7574696f6e20746f20746865206d6174682070726f626c656d2031202b203120697320322e0109617373697374616e7400000000000000000001c0eb094ef9d005c20000000000000000ff0041172e6900000000ff011f6f4d58414563312d34594e4362342d39613736303866363063346263666535ff012b6d6574612d6c6c616d612f4d6574612d4c6c616d612d332e312d38422d496e7374727563742d547572626fff010f636861742e636f6d706c6574696f6eff000000000000000000ff000000000000000000ff001000000000000000ff003900000000000000ff004900000000000000ff000000000000000000ff001000000000000000ff003900000000000000ff00490000000000000000000000000000000000000000000000086475726174696f6e05496e7436347841ca1700000000400fc617000000000b7374617475735f636f64650555496e743800000e7374617475735f6d657373616765104e756c6c61626c6528537472696e672901010000056c696e6b73104e756c6c61626c6528537472696e67290000025b5d025b5d106576656e74732e54696d657374616d70144172726179284461746554696d65363428392929000000000000000000000000000000000b6576656e74732e4e616d651d4172726179284c6f7743617264696e616c69747928537472696e672929010000000000000000000000000000000000000000000000116576656e74732e417474726962757465731a4172726179284d617028537472696e672c20537472696e67292900000000000000000000000000000000

I have got around this for now by converting the JSON to a string and then converting back to JSON in python logic. Please fix this

[joe-clickhouse]

Thanks @alanzhang25.

• What version of CH server are you seeing this with?
• What version of clickhosue-connect are you using?
• Can you give an example of the nature of JSON data you're storing?
• Is it consistent or intermittent?
• Was the data inserted via clickhouse-connect or another way?

[alanzhang25]

What version of CH server are you seeing this with?

25.8 Cloud

What version of clickhosue-connect are you using?

0.10.0

Can you give an example of the nature of JSON data you're storing?

extremely long like that of OpenAI return

Is it consistent or intermittent?

consistent

Was the data inserted via clickhouse-connect or another way?

inserted via clikchosue connect

[joe-clickhouse]

Thanks @alanzhang25! Will take a look at this today.

[joe-clickhouse]

@alanzhang25 ok so I've found the issue. It looks like your data is exceeding the server's max_dynamic_paths default of 1024. When this happens, paths beyond this value get thrown into a shared data structure. On the clickhouse-connect side, I discovered a bug that we have incorrectly defined this shared data type as an Array instead of a Map. I have fix this bug in a local branch, but then discovered that our current JSON column reconstruction code does not rebuild paths beyond max_dynamic_paths so you'd only receive as many path as max_dynamic_paths is equal to. I have fixed this locally as well, but only for String values.

Are the values in your JSON strings? Are they mixed types? This will help me determine how to go about the next steps.

In the short term, if you know a max limit of your max_dynamic_paths in your data, you might be able to completely get around the problem by increasing the value on the column to something greater that what you expect. Note that it's not recommended to increase it too much for performance reasons, but it could be worth looking at.

EDIT: I just checked and it looks like that setting can not be modified one the column is created so it'd require re-creating the column from the start with a higher value e.g.

CREATE TABLE json_many_paths
(
    id Int32,
    data JSON(max_dynamic_paths = 5000)
)
ENGINE = MergeTree
ORDER BY id

[alanzhang25]

Hey @joe-clickhouse ,

Thanks for looking into this. I do not know the max limit but I am curious as to how I hit the limit so fast because last time I had checked I was at around 500 and didn't expect that many new paths. Is there a quick way to check what the paths are across the entire table? Do you know if nested JSON structures will interfere with the parent max_dynamic_paths?

The values in the JSON are of mixed types.

[joe-clickhouse]

No problem!

You can investigate the unique paths and their counts per data part (which is where the max_dynamic_paths limit is applied) using a query like this:

SELECT
    _part,
    length(distinctJSONPaths(json_col)) AS distinct_paths_in_part_count,
    distinctJSONPaths(json_col) AS paths_in_part
FROM your_table
GROUP BY _part
ORDER BY distinct_paths_in_part_count DESC;

This query will show you, for each physical data part in your table (_part is a virtual column available in MergeTree tables), how many distinct JSON paths exist within that part. You might find that collectively across rows in a single part, you have many more unique paths than you expected from individual rows.

You can read a bit more about reaching the dynamic path limit here.

Do you know if nested JSON structures will interfere with the parent max_dynamic_paths?

Yes. Nested JSON structures do contribute to the max_dynamic_paths budget. ClickHouse flattens nested JSON paths, so for example, {"user": {"address": {"city": "New York"}}} would generate the path user.address.city. Every unique "leaf" key, regardless of its nesting level, consumes one slot from the max_dynamic_paths budget within a given data part.

Regarding the fix I have so far:
The fix I have implemented addresses how clickhouse-connect reads data that has spilled over into the shared data area due to exceeding max_dynamic_paths. However, it has a limitation: it currently only supports primitive types (Integers, Strings, Floats, Booleans, etc.) when those values are found in the shared data. So if a path like "nested.scalar_value" (e.g., "nested.age": 30) overflows into shared data, it will be read correctly. However, if a path's value is a complex type, such as an Array (e.g., "nested.items": [1, 2, 3]) or a Map/Object (e.g., "nested.details": {"key": "value"}), and this path overflows into shared data, clickhouse-connect will not be able to fully deserialize the complex value.

This current limitation exists because reading complex types in the shared data area would require a new kind of "single-value" binary reading implementation for each type, which is different from the "chunked column" reading approach the client already uses so I couldn't re-use any existing machinery. Even so, as it stands now the fix covers the vast majority of cases with scalar values...but full support for complex types in the shared data would be a pretty significant undertaking.

So all that said:

1. when you say "mixed types" do you mean just primitive types, or complex types as well?
2. what distinct path counts are you seeing per part from the query I provided above?

[joe-clickhouse]

Consider this more of a note to self to test on Monday but in doing some research I just read about a server setting called output_format_native_use_flattened_dynamic_and_json_serialization (https://clickhouse.com/docs/operations/settings/formats#output_format_native_use_flattened_dynamic_and_json_serialization) that if we set to 1 that might allow the client's current reading method to work correctly for both below and above limit data. Will report back Monday.

FOLLOW UP:
Doesn't seem like this just works out of the box so I think the two options at this point are:

1. increase max_dynamic_paths if you can (but not too much)
2. implement a proper fix for dealing with the shared data. This is mostly done in a local branch for primitive types.