hive的基本操作

表的增删改查操作

创建表

使用if not exists 如果存在则跳过，comment为注释。

create table if not exists mydb.employees(
	name string comment 'Employee name',
	salary float comment 'Employee salary',
	subordinates array<string> comment 'Names of subordinates',
	deduction map<string, float>,
	address struct<street:string, city:string, state:string, zip:int> comment 'Home address')

comment 'descriptions of table'
location '/user/hive/warehouse/mydb.db/employees';

描述表

显示表的字段和结构，使用desc/describe

--显示表的字段和数据类型
desc table_name;
--显示对应字段的数据类型
desc table_name.columns

管理表和外部表

管理表是hive创建的表，由hive控制其生命周期，hive默认情况下会将数据存在在配置文件指定的目录当中，由hive.metastore.warehouse.dir指定。当使用hive删除表的时候，对应的数据也会被删除，即hdfs文件系统中的数据也会被删除。管理表的缺点在于无法共享数据，比如利用pig等工具创建的数据，hive对其没有权限。当使用hive查询这些数据的时候就可以使用一个外部表指向这份数据，而不需要对其的权限。外部表需要使用external修饰。

create external table if not exists stocks(
	exchange		string,
	symbol			string,
	ymd				string,
	price__open		string,
	price__high		string,
	price__low		string,
	price__close	float,
	volume			int,
	price_adj_close	float)
row format delimited fields terminated by ',' '分隔符为,'
location 'data/stocks';

加上external字段值后，删除表并不会删除这份数据，不过描述标的元数据信息会被删除。元数据可以理解为对该表的描述信息，而不是表内数据。

需要注意的是如果语句省略了external关键字同事源表是外部表，那么新表也是外部表，如果源表是管理表，新表也是管理表。在加上external之后，无论源表是管理表还是外部表，新表都是外部表。

分区表

在建表过程中，会根据分区字段创建对应目录，优点在于分层存储，可以加快查询速度，而缺点在于一些数据存在于文件目录下，但是hive只能从表中读取数据，因此会造成资源浪费。分区表创建：

create table employees(
	name			string,
	salary			float,
	subordinates	array<string>,
	deduction		map<string,float>,
	address			struct<street:string, city:string, city:string, state:string>
)
partitioned by (country string, state string);

在建表的时候hive在hdfs上的目录为…/employees/country/state

查看表中存在所有分区

show partitions employees;

查询指定分区

show partitions employees partition(country='CHINA')

删除表

drop table if exists table_name;

对于管理表，表的元数据和表内数据都会被删除。

修改表

　表的重命名

将表从ａ重命名为ｂ

alter table a rename to b;

增加表分区

alter table add partiion
--在一个查询语句中增加多个分区
alter table table_name if not exists 
partition(...) location '/user/hive/warehouse/a'
partition(...) location '/user/hive/warehouse/b'
partition(...) location '/user/hive/warehouse/c'

修改列的信息

将列名从a改到b，并且将其移到serverity字段后面。

alter table table_name change 
column a b type_name '修改列的数据类型'
comment 'xxx'
after serverity

增加新的列

alter table table_name add column(
    app_name string comment 'Application name',
    session_id long comment 'the current session id';
)

删除或替换列

alter table  table_name replace columns（
	hour_mins_secs INT comment 'xxx'
	severity string comment 'xxx';
)

将之前的列都删除，只留下replace的列

修改表的属性

alter table table_name set tblproperties('notes'='xxx');

修改表的存储属性

alter table table_name partition(a=xxx,b=xxx,c=xxx) set fileformat sequencefile;

指定对应的分区中的表，并且重新设置其格式。

加载和导出数据

从本地加载数据

load data local inpath '/home/hadopp/data.txt'
overwrite into table employees
partition(country='US',state='CA');

需要注意的是创建分区表的时候使用的是partition by。如果目录不存在，hive会先创建分区目录。

通过查询语句加载数据

insert overwrite table employees
partition(country='US',state='CA')
select * from table_name where xxx=xxx;

通过查询语句建表

create table if not exists table_name as 
select * from table_name_b;

导出数据

--方法一，谁用hadoop提供的工具
hadoop fs -cp source_path target_path
--方法二
insert overwrite local directory '/home/hadoop/employees'
select * from employees;

hive的连接操作

table stu

1	chenli	21
2	xuzeng	22
3	xiaodan	23
4	hua		24

table sub

1	chinese
2	english
3	science
5	nature

内连接

inner join，关键字 join on。仅列出两个表中符合连接条件的数据。

select a.*,b.* from stu a join sub b on a.id=b.id
--结果
1	chenli	21	1	chinese
2	xuzeng	22	2	english
3	xiaodan	23	3	science

join后面连接表，而on指定连接条件。

左连接和右连接

左连接，显示左边表的所有数据，如果右边表有与之对应的数据则显示，否则显示为NULL。

select a.* from stu a left outer join sub b on a.id=b.id;
--结果
1	chenli	21	1	chinese
2	xuzeng	22	2	english
3	xiaodan	23	3	science
4	hua		24	NULL	NULL

右连接与左连接相反，使用的关键字为 right outer join xxx on xxx。

标准查询关键字执行顺序为 from->where->group by->having->order by。

全连接

左表和右表都显示，如果没有对应数据，则都显示为NULL

select a.* from stu a full outer join sub b on a.id=b.id;
--结果
1		chenli	21		1		chinese
2		xuzeng	22		2		english
3		xiaodan	23		3		science
4		hua		24		NULL	NULL
NULL	NULL	NULL	5		nature

左半开连接

左半开连接。left semi join，语法与左连接不一样，只能选择出左边表的数据，此数据符合on后面的条件。

select a.* from stu a left semi join sub b on a.id=b.id;
--结果
1	chenli	21
2	xuzeng	22
3	xiaodan	23
--下列语句会报错
select a.*,b.* from stu a left semi join sub b on a.id=b.id;

笛卡尔连接

select a.*,b.* from cl_student a join cl_stu_sub b;
--结果
1	chenli	21	1	chinese
1	chenli	21	2	english
1	chenli	21	3	science
1	chenli	21	5	nature
2	xuzeng	22	1	chinese
2	xuzeng	22	2	english
2	xuzeng	22	3	science
2	xuzeng	22	5	nature
3	xiaodan	23	1	chinese
3	xiaodan	23	2	english
3	xiaodan	23	3	science
3	xiaodan	23	5	nature
4	hua		24	1	chinese
4	hua		24	2	english
4	hua		24	3	science
4	hua		24	5	nature

花了几天的时间整理了hive的用法，终于不用在对着SQL摸瞎了，加油吧进击的SQL boy！ 日常福利(●´∀｀●)