ANALYZE TABLE is not updating columns stats

[vlado101]

Hello everyone,

So I am having an issue when running "ANALYZE TABLE COMPUTE STATISTICS FOR ALL COLUMNS". The way I understand it this should update the min/max value for a column when you run it for all or one column. One way to verify it from what I know is to use "DESCRIBE EXTENDED table_name".

The problem is that when I run ANALYZE TABLE COMPUTE STATISTICS FOR ALL COLUMNS and then use DESCRIBE EXTENDED on the table I see that that for under the "comment" column all the values are null. Per my understanding they should not be null, but have min/max values or at least something other than null.

I am also doing this for EXTERNAL tables. Did anyone run into the same issue?

[Priyanka_Biswas · Accepted solution]

Hello @vlado101 

The ANALYZE TABLE COMPUTE STATISTICS FOR ALL COLUMNS command in Databricks is used to compute statistics for all columns of a table. This information is persisted in the metastore and helps the query optimizer make decisions such as choosing the proper join type, selecting the correct build side in a hash-join, and calibrating the join order in a multi-way join.

However, it seems you are expecting the ANALYZE TABLE command to populate the "comment" column when you run the DESCRIBE EXTENDED table_name command. This is not correct. The "comment" column in the output of DESCRIBE EXTENDED is not related to the statistics computed by ANALYZE TABLE. The "comment" column is used to store any comments that have been added to the table or column when it was created or altered, not the statistics.
If you want to view the statistics of a table, you can use the DESCRIBE EXTENDED command, but you should look at the "statistics" field in the output, not the "comment" field. If the statistics are not being updated as expected, it could be due to a number of reasons, such as the table not being analyzed correctly, or a delay in the update of the metastore.

Here is an example of how you can use the DESCRIBE EXTENDED command to view the statistics of a table:
sql
DESCRIBE EXTENDED table_name;
Look for the "statistics" field in the output to see the computed statistics.