hive的操作语句

create table if not exists part1(
	uid int,
	uname string,
	uage int
)
PARTITIonED BY (country string)
	row format delimited 
	fields terminated by ','
;

alter table part1 add if not exists partition(country='china');
alter table part1 add if not exists partition(country='japan');

show partitions part1

create table if not exists part2(
uid int,
uname string,
uage int
)
PARTITIonED BY (year string,month string)
row format delimited 
fields terminated by ','
;

alter table part2 add if not exists partition(year=2015,month=12);

show partitions part2



alter table part1 add partition(country='Vietnam')
	location '/user/hive/warehouse/xxx'
;

alter table part1 drop partition(country='Vietnam');


alter table part1 partition(country='Vietnam') set location ‘hdfs://hadoop01:9000/user/hive/warehouse/brz.db/part1/country=Vietnam’




load data local inpath '/usr/local/xxx' into table part1
partition(year,month); 


create table if not exists part_tmp(
uid int,
uname string,
uage int,
year string,
month string
)
row format delimited 
fields terminated by ','
;




create table if not exists dy_part2(
uid int,
uname string,
uage int
)
PARTITIonED BY (year string,month string)
row format delimited 
fields terminated by ','
;

insert into dy_part2 partition(year='2018',month)
select uid,uname,uage,month from part_tmp
;

show partitions dy_part2


insert into dy_part2 partition(year='2018',month) values('1','2','3','7')
insert into dy_part2 partition(year='2017',month='1') values('1','2','3')



desc formatted hive_clickhouse
desc formatted partX
desc formatted dy_part2
desc formatted part2
desc formatted ffge_test
desc formatted test_fx

SHOW CREATE TABLE hive_clickhouse

create table if not exists dt_part1(
uid int,
uname string,
uage int
)
PARTITIonED BY (year string,month string)
row format delimited 
fields terminated by ','
;


load data local inpath '/usr/local/xxx' into table part1
partition(year,month); 


create table if not exists part_tmp(
uid int,
uname string,
uage int,
year string,
month string
)
row format delimited 
fields terminated by ','
;

insert into dt_part1 partition(year,month)
select * from part_tmp;

select PART_NAME FROM PARTITIONS WHERe TBL_ID=(SELECt TBL_ID FROM TBLS WHERe TBL_NAME='dy_part2');


insert into acct_wt_user_avg_stock_hold_days partition 
(part_init_date='20190425',interval_type=1) ;


select '20190425','4567890',30 from acct_wt_user_hold_stock_earnging_ratio limit 1;


alter table person_partition add if not exists partition(year=2015,month=12,day=26);