[大数据]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的方式加载数据*/
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的方式加载数据*/
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);