Optimization failed for timestampNtz

[Jennifer]

We have a table using timestampNtz type for timestamp, which is also a cluster key for this table using liquid clustering. I ran OPTIMIZE <table-name>, it failed with error

Unsupported datatype 'TimestampNTZType'

 But the failed optmization also broke the query that filters on this field. Query as below will fail

select * from table-name
WHERE field_name>'2024-01-02'

 with error

TimestampNTZType (of class org.apache.spark.sql.types.TimestampNTZType$)

 How am I suppose to solve this issue?

[Kaniz_Fatma]

Hi @Jennifer, 

• Since TimestampNTZType is not currently supported for optimization, you can try a workaround by converting the timestamp column to a different data type before running the OPTIMIZE command.
• For example, you could convert the timestampNtz column to a TIMESTAMP or DATE data type (if applicable to your use case) and then proceed with optimization.
• Check if using a different timestamp data type (such as TIMESTAMP or DATE) is feasible for your use case. If so, modify the schema accordingly.
• After modifying the schema, update your queries to use the new data type. For instance, if you convert the column to TIMESTAMP, your query would look like:

  SELECT *
  FROM table-name
  WHERE field_name > '2024-01-02'