Use output of SHOW PARTITION commands in Sub-Query/CTE/Function

pshah83 — Thu, 11 Aug 2022 23:20:55 GMT

I am using SHOW PARTITIONS <<table_name>> to get all the partitions of a table. I want to use max() on the output of this command to get the latest partition for the table.

However, I am not able to use SHOW PARTITIONS <<table_name>> in a CTE/sub-query/table function. All of them fail with syntax error. Any inputs

with t as (
SHOW PARTITIONS <<table_name>>
)
select * from t

select * from (
SHOW PARTITIONS <<table_name>>
)

topic Use output of SHOW PARTITION commands in Sub-Query/CTE/Function in Data Engineering

Use output of SHOW PARTITION commands in Sub-Query/CTE/Function