Hive的基础语法

Hive的基础语法

1. DDL

1.1 DataBase

--创建数据库语法
CREATE DATABASE [IF NOT EXISTS] database_name
  [COMMENT database_comment]
  [LOCATION hdfs_path]
  [WITH DBPROPERTIES (property_name=property_value, ...)];
  
e.g.
create database if not exists hive
comment 'the 1st database demo'
localtion 'hdfs://192.168.10.101//user/hive/warehouse'
with dbproperties('name'='qwer');

create database if not exists hive2
comment 'the 2nd database demo'
with dbproperties('name'='rocklee');

--删除数据库语法
drop database [if exists] database_name [cascsde];

--修改数据库
alter database database_name set dbproperties
(property_name=property_value,...) 
--(Note: schema added in hive 0.14.0) #修改数据库的属性设置

alter (database) database_name setowner [user|role] user_or_role;
--(Note:Hive 0.13.0 and later;schema added in hive0.14.0)
-- 修改用户名和角色

alter (database) database_name set location hdfs_path;
--(Note:Hive 2.2.1,2.4.0 and later)
-- 修改数据存放在hdfs的路径

--切换数据库
use database_name;

1.2 Table

1.2.1 建表

--内部表
create table if not exists `hive`.`t_usr`(
`id` int comment 'user id',
`name` string comment 'user name',
`age` int comment 'user age'
)
comment 'user info'
row format delimited
fields terminated by ','
stored as textfile;

load data local inpath '/root/user.txt' into table `hive`.`t_user`;

--外部表
create external table if not exists `hive`.`t_user2`(
`id` int comment 'user id',
`name` string comment 'username',
 `age` int comment 'user age'
)
comment 'user info'
row format delimited
fields terminated by ','
stored as textfile
location '/t_user2';

load data local inpath '/root/user.txt' into table `hive`.`t_user2`;
--内部表和外部表的区别：
--在删除内部表和外部表时，内部表会删除数据和元数据。但是外部表只会删除元数据，本身数据不会被删除。

--临时表(临时表是没有元数据的，也没有数据存放在hdfs上，那么他的元数据和数据都存在内存之中，所以一旦hive客户端和服务端断开连，这些数据全部移除。)
create temporary table if not exists `hive`.`t_user3`(
`id` int comment 'userid',
`name` string comment 'username',
`age` int comment 'user age'
)comment 'user info'
row format delimited
fields terminated by ','
stored as textfile;

load data local inpath '/home/user.txt' into table `hive`.`t_user3`;

--like和as select的区别
create table if not exists `hive`.`t_user2` as select * from `hive`.`t_user`; --产生了新的目录和文件

create external table if not exists `hive`.`t_user3` like `hive`.`t_user` location '/user/hive/warehouse/hive.db/t_user';
--引用了原表的数据，一旦原表没有了，这个表也会受到影响

1.2.2 修改表

--1. 重命名表
alter table table_name rename to new_table_name;

--2. 修改表的批准信息
alter table table_name set tblproperties('comment'=new_comment);

1.2.3 修改列

--修改列信息(修改列字段的类型或者对数据的格式是没有影响的，但是由于修改了元数据，会对查询的结果造成影响)
alter table table_name change [column] col_old_name col_new_name column_type [comment col_comment] [first|after coumn_name] [cascsde];

e.g.
alter table `hive`.`t_1` change column `id` `uid` string comment 'this is uid';

--添加或者删除列字段
alter table table_name add|replace columns (col_name data_type [comment col_comment], ...) [cascade]

e.g.
alter table `hive`.`t_1` add colums (`salary` double,`common` double); # 添加列
alter table `hive`.`t_1` replace columns (`salary` double,`common` double); # 用指定列讲原来的列全部替换

2. DML

2.1 加载数据

--1. load
LOAD DATA [LOCAL] INPATH 'filepath' [OVERWRITE] INTO TABLE tablename [PARTITION (partcol1=val1, partcol2=val2 ...)]

e.g.
load data local inpath '/home/user.txt' into table `t_1`;

load data inpath '/user.txt' into table `t_1`;
/* local 是从本地文件系统中复制数据到表目录下
 * 不加local是从HDFS文件系统中剪切数据到表目录下
 * load方式加载数据只能用于表是textfile格式的情况
 */
 
 --2. insert
 insert into t_user3 values(4,'narudo',18);
 /* 我们发现这种传统的方式也可以插入一条数据，但是一条数据就是一个小文件被存
  * 放到hdfs中，还会调用mr才能出结果。效率很低，并且大量小文件，实际应用中
  * 不会使用
  */
  
INSERT OVERWRITE TABLE tablename1 [PARTITION (partcol1=val1, partcol2=val2 ...) [IF NOT EXISTS]] 
select_statement1 FROM from_statement;
INSERT INTO TABLE tablename1 [PARTITION (partcol1=val1, partcol2=val2 ...)] select_statement1 FROM from_statement;

e.g.
create table `t_user4` like `t_user3`;
insert overwrite table `t_user4` if not exists select * from t_user3;

2.2 多数据插入

FROM from_statement
INSERT OVERWRITE TABLE tablename1 [PARTITION (partcol1=val1, partcol2=val2 ...) [IF NOT EXISTS]] select_statement1
[INSERT OVERWRITE TABLE tablename2 [PARTITION ... [IF NOT EXISTS]] select_statement2]
[INSERT INTO TABLE tablename2 [PARTITION ...] select_statement2] ...;

e.g.
create table `t_user5` like `t_user3`;
create table `t_user6` like `t_user3`;

from `t_user3`
insert overwrite table `t_user5` if not exists select *
insert overwrite table `t_user6` if not exists select *;

2.3 写数据

INSERT OVERWRITE [LOCAL] DIRECTORY directory1
  [ROW FORMAT row_format] [STORED AS file_format] 
  SELECT ... FROM ...
e.g.
INSERT OVERWRITE DIRECTORY '/write'
ROW FORMAT DELIMITED
SELECT * FROM `hive`.`t_user5`;

2.4 Join

2.4.1 语法

SELECT [ALL | DISTINCT] select_expr, select_expr, ...
  FROM table_reference
  [WHERE where_condition]
  [GROUP BY col_list]
  [ORDER BY col_list]
  [CLUSTER BY col_list
    | [DISTRIBUTE BY col_list] [SORT BY col_list]
  ]
 [LIMIT [offset,] rows]
 [UNION/UNION ALL]
 
 join_table:
    table_reference [INNER] JOIN table_factor [join_condition]
  | table_reference {LEFT|RIGHT|FULL} [OUTER] JOIN table_reference join_condition
  | table_reference LEFT SEMI JOIN table_reference join_condition
  | table_reference CROSS JOIN table_reference [join_condition]

2.4.2 数据

//1. emp
1,lixi,1
2,zhangshuai,2
3,cuikai,3
4,xiaojianxiong,4
6,ranldo,10

//2. dept
1,Java
2,Bigdata
3,UI
4,HTML5
5,Python

2.4.3 测试

//1. 建立表
create table if not exists t_emp(
eid int,
ename string,
did int
)
row format delimited
fields terminated by ','
;

create table if not exists t_dept(
did int,
dname string
)
row format delimited
fields terminated by ','
;

//2. 导入数据
load data local inpath '/home/emp.txt' into table t_emp;
load data local inpath '/home/dept.txt' into table t_dept;


//3. 内连接
select
e.eid,
e.ename,
d.dname
from
t_emp e
join
t_dept d
on
e.did = d.did
;

//4. 左连接
select
e.eid,
e.ename,
d.dname
from
t_emp e
left join
t_dept d
on
e.did = d.did
;

//5. 右连接
select
e.eid,
e.ename,
d.dname
from
t_emp e
right join
t_dept d
on
e.did = d.did
;

//6. 全连接
select
e.eid,
e.ename,
d.dname
from
t_emp e
full join
t_dept d
on
e.did = d.did
;

//7. 左半连接 : 查询所有符合条件的左边的表的数据(右表数据不能查询，否则报错)
select
e.eid,
e.ename,
e.did
from
t_emp e
left semi join
t_dept d
on
e.did = d.did
;

2.5 group by

2.5.1 准备数据

//1. emp
1,lixi,1
2,zhangshuai,2
3,cuikai,2
4,xiaojianxiong,2
6,ranldo,1

//2. dept
1,Java
2,Bigdata
3,UI
4,HTML5
5,Python

2.5.2 分组查询–每个部门的人数

SELECT
d.did,
COUNT(*)
FROM
t_emp e
JOIN
t_dept d
ON
e.did = d.did
GROUP BY
d.did
;

SELECT
d.dname,
COUNT(*)
FROM
t_emp e
JOIN
t_dept d
ON
e.did = d.did
group by
d.did, d.dname
;

tip:
我们发现在mysql中能够成功运行的sql代码，在hive中的不能运行。这个分组的sql中在hive里有一个要求，分组字段才能够做select的字段被查询出来。非分组字段在hive中不能使用！！！

2.6 Order By 和Sort By的区别

- 局部排序，只保证单个的reduce内有序
e.g.
select * from a
distribute by (id) sort by(name);

- 全局有序，保证所有的reduce中的数据有序
e.g.
select * from a order by (name);