drop_partition in a script
Hello,
I'd like to use the drop_partition in an sql request where I retrieve partition id from the partitions table. I cannot manage to make it. Something like:
WITH partition_to_be_deleted AS (SELECT DISTINCT PARTITION_KEY FROM partitions WHERE TABLE_SCHEMA='TEST' AND PROJECTION_NAME LIKE 'MY_TABLE%' AND PARTITION_KEY < ....))
SELECT DROP_PARTITION('TEST1.MY_TABLE', partition_nb)WHERE partition_nb IN (SELECT PARTITION_KEY FROM partition_to_be_deleted);
Is it possible?
Tx
I'd like to use the drop_partition in an sql request where I retrieve partition id from the partitions table. I cannot manage to make it. Something like:
WITH partition_to_be_deleted AS (SELECT DISTINCT PARTITION_KEY FROM partitions WHERE TABLE_SCHEMA='TEST' AND PROJECTION_NAME LIKE 'MY_TABLE%' AND PARTITION_KEY < ....))
SELECT DROP_PARTITION('TEST1.MY_TABLE', partition_nb)WHERE partition_nb IN (SELECT PARTITION_KEY FROM partition_to_be_deleted);
Is it possible?
Tx
0
Comments
The solution I found is a bash script: vsql -At -h ${DB_HOST} -U ${DB_ADMIN_USER} -w ${DB_ADMIN_PASS} -c "SELECT DISTINCT PARTITION_KEY FROM partitions WHERE TABLE_SCHEMA='${schema}' AND PROJECTION_NAME LIKE '${tables}%' AND PARTITION_KEY < ${part_nb} ;" | xargs -I{} bash -c "vsql -h ${DB_HOST} -U ${DB_ADMIN_USER} -w ${DB_ADMIN_PASS} -c \"SELECT DROP_PARTITION(${tables},{}\"" Hope it helps.