How to separate a table from others using a column as a parameter

user_17103398 0
Viewed 35

I have the following table with hundreds of rows: table

I'm trying to separate each line and send to the following tables: "access in the last 15 days", "access in the last 30 days" and "not accessed for more than 30 days".

According to the date present in the "tbdlf fsimage accesstime" column, make this separation and send it to the respective table.

I'm doing this through Hue File Browser Query Editors

hue hive date function sql
1 Answers

you can calculate dates difference and use multi-insert to insert data to different tables based on condition:

with src as (
select t.* --list all columns which you need to insert
       --calculate category depending on difference in days
       case when datediff(current_date,to_date(accesstime))> 30 then 'not accessed for more than 30 days'
            when datediff(current_date,to_date(accesstime))> 15 then 'access in the last 30 days'
            when datediff(current_date,to_date(accesstime))<= 15 then 'access in the last 15 days'      
        end as category
  from tbdlf_fsimage t 
)

insert into table not_accessed_for_more_than_30_days
select --list columns here
 from src 
 where category='not accessed for more than 30 days'

insert into table access_in_the_last_30_days
select --list columns here
 from src 
 where category='access in the last 30 days'

insert into table access_in_the_last_15_days
select --list columns here
 from src 
 where category='access in the last 15 days'
; --end of multi-insert
user_27003440
Related