topic Re: OPTIMIZE in Data Engineering

OPTIMIZE

Anonymous — Thu, 11 Nov 2021 02:03:54 GMT

I have been testing OPTIMIZE a huge set of data (about 775 million rows) and getting mixed results. When I tried on a 'string' column, the query return in 2.5mins and using the same column as 'integer', using the same query, it return 9.7 seconds. Please advice.

I am using 9.1 LTS on the Azure environment.

Re: OPTIMIZE

-werners- — Thu, 11 Nov 2021 07:54:16 GMT

that depends on the query, the table and what optimize you use (binning, z-order).

Delta lake by default collects statistics for the first 32 columns (which can be changed).

Building statistics for long strings is also more expensive than f.e. for integers.

Then there is also the fact that evaluating numbers is faster than strings.

https://docs.microsoft.com/en-us/azure/databricks/spark/latest/spark-sql/language-manual/delta-copy-into#load-csv-files

What could also play is auto scaling on your cluster, or spot instances which are abandoned etc.

So, not easy to pinpoint the difference.

Re: OPTIMIZE

Anonymous — Fri, 12 Nov 2021 05:52:06 GMT

@Werner Stinckens Thanks for your explanation.