cancel
Showing results for 
Search instead for 
Did you mean: 
Data Engineering
cancel
Showing results for 
Search instead for 
Did you mean: 

Optimization failed for timestampNtz

Jennifer
New Contributor III

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?

1 REPLY 1

Kaniz
Community Manager
Community Manager

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'