How to handle Dremio Limit 32000 bytes of a field

[Vijay]

While accessing a collection (XYZ) in a MongoDB, Dremio is showing an error “Field ‘description’ exceeds the size limit of 32000 bytes”. As per Dremio documentation, this is the limit in Dremio.

So, created a copy of the collection (XYZ_Copy) and dropped the “description” field, it is confirmed that the “description” field is deleted from the collection XYZ_Copy. When accessed XYZ_Copy collection from Dremio, it is still showing the same error “Field ‘description’ exceeds the size limit of 32000 bytes” even after the field is dropped.

[Rakesh_Malugu]

Hello @Vijay

What do you mean by copy?

You created a VDS on top of XYZ without “description” and still hitting the issue?

Thanks,
Rakesh

[Vijay]

No, a copy of the collection in MongoDB itself without the “description” field.

[balaji.ramaswamy]

@Vijay

Is it possible a different column also has a field > 32K

[Vijay]

No, there is no other field of size > 32K. Dremio is showing error only on this field.

[Vijay]

Deleting a field does not literally delete the field in MongoDB, but UNSET the field. So, internally the field exists, so the meta data. As Dremio accesses metadata, it is finding that field which was deleted (by using UNSET).

After troubleshooting, We are creating a new collection with the needed fields only, instead of copying the whole collection and UNSET a field.

New collection with only the needed is now accessible from Dremio. Working on it . Will keep posted the updates.

[Vijay]

Dear Dremio Community,

Like to share a solution to handle 32000 bytes limit in Dremio.

We had an issue accessing mongoDB Collection havng a value of size 32000 bytes in a field even that field is not selected in Dremio.

Solution to fix this is, Create a view in mongoDB with the fields you need without that 32000 bytes field causing the error. Then, access the view from Dremio, It is Working well.

[tfedyanin]

You can use for current session in jdbc connection:

alter session set limits.single_field_size_bytes = 99000;

or system wide (affect to webUI too):

alter system set limits.single_field_size_bytes = 99000;

[vyz101]

For me after running

alter session set limits.single_field_size_bytes = 99000;(from the UI)

The error changed to new limit size wierdly lesser than 99000

Field with index 7 exceeds the size limit of 65536 bytes

Is 65536 bytes the max field size set by you folks ?

[balaji.ramaswamy]

@vyz101

Yes 65K is the other limit, are you able to truncate your source column to 32K at source (probably on a new column) and bring that into Dremio?

Thanks
Bali

[datalifenyc]

I am working with geospatial data: NYC Building Footprints.

Alter Session and Alter System did not adjust the limit for me, so I changed it by following these steps in GUI:

1. Settings
2. Support (URL: http://localhost:9047/settings/support)
3. In the Support Key field, enter limits.single_field_size_bytes
4. Click Show button
5. Enter size: 128000 (comma will automatically be applied)
6. Click Save button

[balaji.ramaswamy]

@datalifenyc Did the alter system command complete successfully and yet it did not apply or the alter system failed? If it failed, what was the error?

[datalifenyc]

@balaji.ramaswamy Sorry for the delayed reply.

I went back today to try it again and to provide screenshots. Since I am a new user, I “can only put one embedded media item in a post.”

ALTER SYSTEM works now. However, ALTER SESSION does not seem to have an impact.

ALTER SYSTEM

ALTER SYSTEM
SET limits.single_field_size_bytes = 64000
;

Output:

ok	summary
true	limits.single_field_size_bytes updated.

ALTER SESSION

ALTER SESSION
SET limits.single_field_size_bytes = 32000
;

Output:

ok	summary
true	limits.single_field_size_bytes updated.

The Settings > Support Settings > Support Keys UI was the same after running each command:

Is there a way to query system and session settings?

Thanks for following up.

[balaji.ramaswamy]

@datalifenyc ALTER SESSON is only for the life of that session, so if you do it on the UI and navigate to admin-support and see the support keys it would not reflect. ALTER SESSION use case is when you want a certain value of a support key for just one query or a bunch of queries that are fired under one session

[datalifenyc]

I think what may have been happening for ALTER SESSION is that I was using separate scripts for ALTER SESSION and processing the data.

If I understand the documentation correctly, it appears that I would have to run ALTER SESSION in the same script as the query I am using to COPY INTO or INSERT the data.

Dremio Sonar > Querying and Managing Data > Querying Your Data > 6. SQL Editor

Thanks for the assistance.

[Arnie]

To tune this limit it pretty fine but is there another setting to retrieve the text until this limit whatever if the field is over this limit ? I think it’s better to extract even if it is limited than to fail ?

[balaji.ramaswamy]

@Arnie No size limit for select path but if you know a string column is large, you can limit with other string operators like SUBSTR

[Arnie]

Sorry Baladji, but the schema applied makes a column with more than 32k bytes.When the query runs it fails …
My PDS is made of a csv file (french style - with ; …), it seems one of the line contains a column with a lot of characters (more than 32k) … If I run select * from pds limit 100 it is ok, while if I ruen select * from pds (without limit) it fails and I can’t identify which line failed.
Even if I run select substr(column,4) from PDS, it fails with same error.
My dream would be if dremio would cast the cast (left(), substr()) by it self.

Being able to change the limit is nice, but when you analyze logs or json, it contains wild information and you don’t want dremio to fail, it should skip or cast.