hive小记（脱敏、分区）

小命令

查看文件的总行数

wc -l ./customer_details.csv 

查看文件的前两行

head -n 2 ./customer_details.csv

实例

脱敏

unbase64 脱敏
concat拼接后还能unbase64脱敏

create view if not exists vw_customer_details as
select
       customer_id,first_name,unbase64(last_name) as last_name,
       unbase64(email),gender,unbase64(address) as address,
       country,job,credit_type,unbase64(concat(unbase64(credit_no),'hello'))  as credit_no
from ext_customer_details;

分区

将表根据年月（yyyy MM）分区显示

创建分区表

create table if not exists transaction_details(
    transaction_id string,
    customer_id string,
    store_id string,
    price decimal(8,2),
    product string,
    purchase_date string,
    purchase_time date
)
partitioned by (purchase_month string);

导入数据

set hive.exec.dynamic.partition.mode=nonstrict;--开启分区

with basetb as
(select row_number() over(partition by transaction_id order by transaction_id) as rn,
       transaction_id,
       customer_id,
       store_id,
       price,
       product,
       purchase_date,
       purchase_time,
       substr(purchase_date,0,7) purchase_month --截取日期，只显示年月
from ext_transaction_details)
insert overwrite table transaction_details partition(purchase_month)--传入数据
select if(rn = 1,transaction_id,concat(transaction_id,'_fix',rn)) transaction_id,
       customer_id,store_id,price,product,purchase_date,purchase_time,purchase_month
from basetb;