hive （一）表操作

1、创建表

create table default.test
(
web_id int,
web_url string,
agent_type int
);

create table default.test as select * from default.dual;

 2、查看建表信息

show create table ec.test;

 3、分区表

--建外部表

create table ec.table_name
(
id int,
date string
)

ROW FORMAT DELIMITED FIELDS TERMINATED BY '01'

LOCATION '/x/y/data/'

;

--建外部分区表

CREATE EXTERNAL TABLE default.test(
web_id INT,
paper_id INT
)
PARTITIONED BY(day String) 
ROW FORMAT DELIMITED FIELDS TERMINATED BY '01';

--增加分区

alter table default.test add partition (day='20170203') location '/test';

4、加载数据

写数据

insert into default.test partition(month='201707')(wid,pid)
select wd,pid
from default.test2
where month='201707';

外部数据

drop table default.test;
create table default.test
(
source string,
type string
)
ROW FORMAT DELIMITED FIELDS TERMINATED BY '	';


load data local inpath '/tmp/test/test/test.txt' into table default.test;

--查看分区

show partitions ec.test;

--查看具体某一个分区信息

desc extended ec.test partition(day=20170315);

--truncate分区

truncate table test partition(month='201710');

--字符串分割

split(l.path,"\\")[size(split(l.path,"\\"))-1]