一、HiveSQL基本操作

文章目录
一、Hive数据类型
1、基本类型
2、复杂类型
二、存储格式
1、textfile
2、SequenceFile
3、RCFile
4、ORCFile
5、Parquet
三、操作数据库
1、创建数据库
2、删除数据库
3、进入数据库
四、操作表属性
1、显示数据库下面所有表
2、删除表
3、显示建表结果
4、显示表分区
5、修改表属性
6、表重命名
7、新增列
8、删除列
9、修改列
10、删除分区
11、增加分区
12、修改分区、LOCATION
四、创建表
五、LOAD DATA语句
六、hive内置运算符
1、算术运算符
2、逻辑运算符
3、复杂运算符
七、hive视图与索引
八、实例
1、把本地文件放在新建表中
2、把hdfs文件放在新建表中
3、把查询结果放在一个新建表中
4、把查询结果放在本地文件中
5、把查询结果放在HDFS文件中
6、差集使用左右连接
7、其他
8、shell 运行hive
九、注意
一、Hive数据类型
Hive所有数据类型分为两种：

基本类型
复杂类型
1、基本类型
类型    描述    示例
boolean    true/false    true
tinyint    1字节的有符号整数    -127~128
smallint    2个字节的有符号整数，-32768~32767    1S
int    4个字节的带符号整数    1
bigint    8字节带符号整数    1L
float    4字节单精度浮点数    1.0
double    8字节单精度浮点数    1.0
deicimal    任意精度的带符号小数    1.0
string    字符串，变长    “a”，‘b’
varchar    变长字符串    “a”，‘b’
char    固定长度字符串    “a”，‘b’
binary    字节数组    
timestamp    时间戳，纳秒精度    
date    日期    ‘2018-04-07’
2、复杂类型
类型    描述    示例
array    有序的的同类型的集合    array(1,2)
map    key-value：key必须为原始类型，value可以任意类型    map(‘a’,1,’b’,2)
struct    字段集合,类型可以不同    struct(‘1’,1,1.0), named_stract(‘col1’,’1’,’col2’,1,’clo3’,1.0)
二、存储格式
Hive会为每个创建的数据库在HDFS上创建一个目录，该数据库的表会以子目录形式存储，表中的数据会以表目录下的文件形式存储。对于default数据库，默认的缺省数据库没有自己的目录，default数据库下的表默认存放在/user/hive/warehouse目录下。

1、textfile
textfile为默认格式，存储方式为行存储。数据不做压缩，磁盘开销大，数据解析开销大。

2、SequenceFile
SequenceFile是Hadoop API提供的一种二进制文件支持，其具有使用方便、可分割、可压缩的特点。
SequenceFile支持三种压缩选择：NONE, RECORD, BLOCK。 Record压缩率低，一般建议使用BLOCK压缩。

3、RCFile
一种行列存储相结合的存储方式。

4、ORCFile
数据按照行分块，每个块按照列存储，其中每个块都存储有一个索引。hive给出的新格式，属于RCFILE的升级版,性能有大幅度提升,而且数据可以压缩存储,压缩快 快速列存取。

5、Parquet
Parquet也是一种列式存储，同时具有很好的压缩性能；同时可以减少大量的表扫描和反序列化的时间。

三、操作数据库
1、创建数据库
-- 创建数据库语法：
CREATE DATABASE IF NOT EXISTS database_name;

--例如：
CREATE DATABASE IF NOT EXISTS userdb;
1
2
3
4
5
2、删除数据库
-- 删除数据库语法：
DROP DATABASE StatementDROP (DATABASE|SCHEMA) [IF EXISTS] database_name；

-- 例如：
DROP DATABASE IF EXISTS userdb;
1
2
3
4
5
3、进入数据库
-- 进入数据库
USE database_name;
1
2
四、操作表属性
1、显示数据库下面所有表
SHOW TABLES;
1
2、删除表
DROP TABLE [IF EXISTS] table_name;
1
3、显示建表结果
SHOW CREATE TABLE table_name;
1
4、显示表分区
SHOW PARTITION table_name;
1
5、修改表属性
-- 内部表转外部表
ALTER TABLE table_name SET TBLPROPERTIES ('EXTERNAL'='TRUE'); 

-- 外部表转内部表
ALTER TABLE table_name SET TBLPROPERTIES ('EXTERNAL'='FALSE'); 
1
2
3
4
5
6、表重命名
ALTER TABLE table_name RENAME TO new_table_name;
1
7、新增列
ALTER TABLE table_name ADD COLUMNS (cloumn_name string);
1
8、删除列
ALTER TABLE name DROP [COLUMN] column_name;
1
9、修改列
ALTER TABLE name CHANGE column_name new_name new_type;
1
10、删除分区
ALTER TABLE table_name DROP IF EXISTS PARTITION(day=);     -- 这里分区是day
1
11、增加分区
-- 分区字段包含:p_hour,p_city,p_loctype
ALTER TABLE table_name IF NOT EXISTS \
ADD PARTITION (p_hour='2017113003', p_city='573', p_loctype='MHA');
1
2
3
12、修改分区、LOCATION
-- 修改location
ALTER TABLE table_name SET LOCATION "new_location_name";

-- partition修改location路径
ALTER TABLE table_name PARTITION (dt='2008-08-08') SET LOCATION "new_location_name";

-- 修改分区名
ALTER TABLE table_name PARTITION (dt='2008-08-08') RENAME TO PARTITION (dt='20080808');
1
2
3
4
5
6
7
8
四、创建表
语法：

CREATE [TEMPORARY] [EXTERNAL] TABLE [IF NOT EXISTS] [db_name.] table_name

[(col_name data_type [COMMENT col_comment], ...)]
[COMMENT table_comment]
[ROW FORMAT row_format]
[STORED AS file_format]
1
2
3
4
5
6
举例：

CREATE TABLE IF NOT EXISTS employee ( eid int, name String,
salary String, destination String)
COMMENT ‘Employee details’
ROW FORMAT DELIMITED
FIELDS TERMINATED BY ‘\t’
LINES TERMINATED BY ‘\n’
STORED AS TEXTFILE;
1
2
3
4
5
6
7
五、LOAD DATA语句
语法如下：

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

-- 说明：
LOCAL是标识符指定本地路径。它是可选的。
OVERWRITE 是可选的，覆盖表中的数据
PARTITION 这是可选的
1
2
3
4
5
6
7
参数描述：

LOCAL是标识符，指本地路径，它是可选的
命令中带LOCAL，表示从本地文件系统中加载数据，可以是相对路径，绝对路径，目录，URL(file:///user/hive/project/data)
命令中不带LOCAL，表示从HDFS加载文件，可以是完整的URL方式或者使用fa.default.name定义的值
FILEPATH 文件路径，可以是：
相对路径，如：project/data/file.txt
绝对路径，如：/user/hive/project/data/file.txt
完整的URL，如：hdfs://namenode:9000/user/hive/project/data/file.txt
目录，如：/user/project/data；表示加载目录下的所有文件
OVERWRITE：
命令中带OVERWRITE，加载数据之前会先清空目标表或分区中的内容
命令中不带OVERWRITE，向目标表后者分区追加内容
例如：

本地数据加载到表
load data local inpath '/opt/test/emp.txt' \
into table emp_part partition (year='2016', month='3');
1
2
六、hive内置运算符
1、算术运算符
2、逻辑运算符
3、复杂运算符
七、hive视图与索引
八、实例
1、把本地文件放在新建表中
-- 创建表时加载
CREATE TABLE table_name (year string, temperature int) \
ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t' LOAD DATA LOCAL INPATH 'xxx/input.txt' \
OVERWRITE INTO TABLE table_name;
1
2
3
4
2、把hdfs文件放在新建表中
-- 创建表时加载
CREATE TABLE table_name (year string, temperature int) \
ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t' \
LOAD DATA INPATH '/emp.txt' INTO TABLE table_name partition (year='2016', month='6'); 
1
2
3
4
3、把查询结果放在一个新建表中
描述：
create table 表名 as sql

举例：

create table meiying_rate as \
select a.pkg_name,b.installed-b.uninstall,a.rank from yinyl.meiying_applist a left outer join user.user_app_stat_daily b \
on a.pkg_name=b.packageid \
where b.day=20151101 and a.rank<=100000 \
order by a.rank;
1
2
3
4
5
4、把查询结果放在本地文件中
描述：
insert overwrite local directory ‘路径’ row format delimited fields terminated by ‘|’ sql

举例：

-- 第一种方式
insert overwrite local directory '${dir_in}rate_${DATE}/' \
row format delimited fields terminated by '|' 、
select a.pkg_name,b.installed-b.uninstall,a.rank from yinyl.meiying_applist a left outer join user.user_app_stat_daily b on a.pkg_name=b.packageid where b.day=${DATE} and a.rank<=100000 order by a.rank；

-- 第二种方式
hive>
hive -e 'select * from testdb2.emp'  >> ./emp_export.txt

1
2
3
4
5
6
7
8
9
5、把查询结果放在HDFS文件中
insert overwrite directory '${dir_in}rate_${DATE}/' \
row format delimited fields terminated by '|' 、
select a.pkg_name,b.installed-b.uninstall,a.rank from yinyl.meiying_applist a left outer join user.user_app_stat_daily b on a.pkg_name=b.packageid where b.day=${DATE} and a.rank<=100000 order by a.rank；
1
2
3
6、差集使用左右连接
select from 表a right(left) outer join 表b on 条件 where

create table diff_in_bj_idc_mangguo as SELECT BB.uid FROM (SELECT dw_user_lastlogin.uid  FROM report_dw_mdp.dw_user_lastlogin where dw_user_lastlogin.day=20151028 and  dw_user_lastlogin.app_id ='X0LuNCmE6t6VuvA1b3EyW4') AA RIGHT OUTER JOIN (SELECT user_bj_idc_20151028.uid FROM yuankai.user_bj_idc_20151028) BB ON (AA.uid= BB.uid) WHERE AA.uid IS NULL;
1
7、其他
去重：distinct
分组：group by
排序：order by （若有where放在其后）

8、shell 运行hive
hive>hive -e “insert overwrite local directory ‘${dir_in}rate_${DATE}/’ row format delimited fields terminated by ‘|’ select a.pkg_name,b.installed-b.uninstall,a.rank from yinyl.meiying_applist a left outer join user.user_app_stat_daily b on a.pkg_name=b.packageid where b.day=${DATE} and a.rank<=100000 order by a.rank”

九、注意
上述实例，创建表时，使用外部表，否则删除表时，会将原始数据删掉；