HiveSql

HiveSql案例

数据:

人员表
id,姓名，爱好，住址
1,小明1,lol-book-movie,beijing:xisanqi-shanghai:pudong
2,小明2,lol-book-movie,beijing:xisanqi-shanghai:pudong
3,小明3,lol-book-movie,beijing:xisanqi-shanghai:pudong
4,小明4,lol-book-movie,beijing:xisanqi-shanghai:pudong
5,小明5,lol-movie,beijing:xisanqi-shanghai:pudong
6,小明6,lol-book-movie,beijing:xisanqi-shanghai:pudong
7,小明7,lol-book,beijing:xisanqi-shanghai:pudong
8,小明8,lol-book,beijing:xisanqi-shanghai:pudong
9,小明9,lol-book-movie,beijing:xisanqi-shanghai:pudong

创建user表:

create table user(
id bigint,
name string,
likes array<string>, 	
address map<string,string>
)
//设置分割符,加载规则
row format delimited
fields terminated by ','
collection items terminated by '-'
map keys terminated by ':';

查看表描述:

desc formatted user;

批量导入数据:

load data local inpath '/路径/数据名称' into table user;

创建外部表:(外部表在删除表的时候不会把元数据删除,只是引用而已)

create table user1(
id bigint,
name string,
likes array<string>,
address map<string,string>
)
//设置分割符,加载规则
row format delimited
fields terminated by ','
collection items terminated by '-'
map keys terminated by ':'
location '/user1';//指定目录,这里为根目录

删除表:

drop table user;

分区partitioned:

create table user3(
id bigint,
name string,
likes array<string>,
address map<string,string>
)
//设置分割符,加载规则
partitioned by (age int)//分区(不能再定义列表当中出现)
row format delimited
fields terminated by ','
collection items terminated by '-'
map keys terminated by ':';

如果目标是一个表分区表,在导数据的时候一定要用到partition关键字

load data local inpath '/路径/数据名称' into table user3 partition(age=10);