Drop all partitions from redshift for an external table
up vote
1
down vote
favorite
I am trying to drop all the partitions on an external table in a redshift cluster. I am unable to find an easy way to do it. I am currently doing this by running a dynamic query to select the dates from the table and concatenating it with the drop logic and taking the result set and running it separately like this
select 'ALTER TABLE procore_iad_ext.active_histories DROP PARTITION (values='''||rtrim(ltrim(values, '["'),'"]') ||''');' from svv_external_partitions
where tablename = 'xyz';
values looks like this ->["2009-03-10"]
Looking for a simpler direct solution. Thanks.
amazon-web-services amazon-redshift amazon-redshift-spectrum
asked Nov 9 at 21:58
Dinesh Jayapathy
61
add a comment |
up vote
1
down vote
favorite
I am trying to drop all the partitions on an external table in a redshift cluster. I am unable to find an easy way to do it. I am currently doing this by running a dynamic query to select the dates from the table and concatenating it with the drop logic and taking the result set and running it separately like this
select 'ALTER TABLE procore_iad_ext.active_histories DROP PARTITION (values='''||rtrim(ltrim(values, '["'),'"]') ||''');' from svv_external_partitions
where tablename = 'xyz';
values looks like this ->["2009-03-10"]
Looking for a simpler direct solution. Thanks.
amazon-web-services amazon-redshift amazon-redshift-spectrum
asked Nov 9 at 21:58
Dinesh Jayapathy
61
2
Why not use drop table and recreate it, that will be much faster than dropping partitions.
– Kannaiyan
Nov 9 at 22:17
add a comment |
up vote
1
down vote
favorite
up vote
1
down vote
favorite
I am trying to drop all the partitions on an external table in a redshift cluster. I am unable to find an easy way to do it. I am currently doing this by running a dynamic query to select the dates from the table and concatenating it with the drop logic and taking the result set and running it separately like this
select 'ALTER TABLE procore_iad_ext.active_histories DROP PARTITION (values='''||rtrim(ltrim(values, '["'),'"]') ||''');' from svv_external_partitions
where tablename = 'xyz';
values looks like this ->["2009-03-10"]
Looking for a simpler direct solution. Thanks.
amazon-web-services amazon-redshift amazon-redshift-spectrum
asked Nov 9 at 21:58
Dinesh Jayapathy
61
I am trying to drop all the partitions on an external table in a redshift cluster. I am unable to find an easy way to do it. I am currently doing this by running a dynamic query to select the dates from the table and concatenating it with the drop logic and taking the result set and running it separately like this
select 'ALTER TABLE procore_iad_ext.active_histories DROP PARTITION (values='''||rtrim(ltrim(values, '["'),'"]') ||''');' from svv_external_partitions
where tablename = 'xyz';
values looks like this ->["2009-03-10"]
Looking for a simpler direct solution. Thanks.
amazon-web-services amazon-redshift amazon-redshift-spectrum
amazon-web-services amazon-redshift amazon-redshift-spectrum
asked Nov 9 at 21:58
Dinesh Jayapathy
61
asked Nov 9 at 21:58
Dinesh Jayapathy
61
asked Nov 9 at 21:58
Dinesh Jayapathy
61
asked Nov 9 at 21:58
Dinesh Jayapathy
61
asked Nov 9 at 21:58
Dinesh Jayapathy
61
61
2
Why not use drop table and recreate it, that will be much faster than dropping partitions.
– Kannaiyan
Nov 9 at 22:17
add a comment |
2
Why not use drop table and recreate it, that will be much faster than dropping partitions.
– Kannaiyan
Nov 9 at 22:17
2
2
Why not use drop table and recreate it, that will be much faster than dropping partitions.
– Kannaiyan
Nov 9 at 22:17
Why not use drop table and recreate it, that will be much faster than dropping partitions.
– Kannaiyan
Nov 9 at 22:17
add a comment |
active
oldest
votes
active
oldest
votes
active
oldest
votes
active
oldest
votes
active
oldest
votes
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function ()
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53233766%2fdrop-all-partitions-from-redshift-for-an-external-table%23new-answer', 'question_page');
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
2
Why not use drop table and recreate it, that will be much faster than dropping partitions.
– Kannaiyan
Nov 9 at 22:17