I am having a table A (without partition) which gets overwritten every month. I want to use partition to save this data. What is the best way to achieve this?
My current steps: Create a new table B with partition every month, Update table B with table A information.
If my steps are correct, please help to write an optimised query for the same. My code is given below.
`CREATE TABLE A
AS
select
Col1,
col2
create table B
as
SELECT
dt DATE FORMAT yyyy-mm-dd,
Col1 string
Col2 string
PARTITION BY RANGE_N(dt BETWEEN DATE '2022-05-23' AND DATE '2028-12-31' EACH INTERVAL '1' MONTH);
insert into table B
select
current_date as dt,
a.*,
from A a
left join
(select * from B) b
on b.col1 = a.col1`
I am having a table A (without partition) which gets overwritten every month. I want to use partition to save this data. What is the best way to achieve this?
My current steps: Create a new table B with partition every month, Update table B with table A information.
If my steps are correct, please help to write an optimised query for the same. My code is given below.
`CREATE TABLE A
AS
select
Col1,
col2
create table B
as
SELECT
dt DATE FORMAT yyyy-mm-dd,
Col1 string
Col2 string
PARTITION BY RANGE_N(dt BETWEEN DATE '2022-05-23' AND DATE '2028-12-31' EACH INTERVAL '1' MONTH);
insert into table B
select
current_date as dt,
a.*,
from A a
left join
(select * from B) b
on b.col1 = a.col1`