topic Query text truncated for queries longer than 153,596 characters in Data Engineering

Query text truncated for queries longer than 153,596 characters

mangosta — Tue, 21 Jan 2025 21:26:05 GMT

Hi,

When using the `query_history.list` function of the python SDK workspace client the queries that have more than 153,596 characters are truncated.

I could not find anywhere in the documentation this limit so I wanted to know if this is documented somewhere and if it is configurable.

Thanks,

Fede

Re: Query text truncated for queries longer than 153,596 characters

Miguel_Suarez — Wed, 22 Jan 2025 17:21:50 GMT

Hi @mangosta,

Our 'Notebook outputs and results' Document references the limit of truncated query rows at 60,000 (ref: https://docs.gcp.databricks.com/en/notebooks/notebook-outputs.html#:~:text=If%20the%20data%20returned%20is%20truncated), but this seems to be the only document mentioning a limit. I hope this helps!

Re: Query text truncated for queries longer than 153,596 characters

mangosta — Wed, 22 Jan 2025 17:32:54 GMT

Hi Miguel,

Thanks for the reply but I am referring to the length of the query, not the returned rows. When I use the ListQueries API '/api/2.0/sql/queries' the query text appears to be truncated if the queries are longer than `153,596` characters.

So I want to know if that is documented somewhere and if there is any setting to change it.

Thanks,

Fede

Re: Query text truncated for queries longer than 153,596 characters

brockb — Thu, 23 Jan 2025 23:50:26 GMT

Hi @mangosta , I did some testing internally and was able to replicate the behavior you described.

The query text limit is a limitation not of the SDK or the API, but rather of the backing system table `system.query.history`. More information on this system table can be seen in the Query History System Table.

There is a limit imposed on the query text size due to storage limitations which maps to the System table column `statement_text`. Due to differing compression characteristics of the query text, there is no guarantee that the limit will always be 153,596.

I have requested internally that we update the docs on this `system.query.history` system table to clarify this.

Thanks for your question.

Re: Query text truncated for queries longer than 153,596 characters

mangosta — Fri, 24 Jan 2025 01:10:54 GMT

Thanks a lot @brockb ! Please let me know when the documentation related to the Query History System Table is updated so I can get all the information and we can plan on our side how to address the problem.

Regards,

Fede

Re: Query text truncated for queries longer than 153,596 characters

brockb — Wed, 05 Feb 2025 17:51:37 GMT

Hi @mangosta , the System Tables docs have been updated with additional context on the `statement_text` here: https://docs.databricks.com/en/admin/system-tables/query-history.html#using-the-query-history-table

Thanks again for letting us know.