hive支持thrift服务。
启动方式:(假如是在lantingshuxu上):
【bin/hiveserver2是hive安装目录bin下】
启动为前台:bin/hiveserver2
启动为后台:nohup bin/hiveserver2 1>/var/log/hiveserver.log 2>/var/log/hiveserver.err &
【执行上面的命令后,命令行会阻塞住,我们可以再创建一个新窗口】
启动成功后,可以使用其他支持thrift的工具连接了。hive自带的beeline也可以,在bin目录下。
使用beeline连接方式如下【其中localhost为ip,如果是其他机器连接,需要使用其他机器的ip】:
方式 1):
输入: hive安装路径/bin/beeline
回车,进入beeline的命令界面,输入命令
!connect jdbc:hive2//localhost:10000
连接hiveserver2。
方式 2):
直接输入 hive安装路径/bin/beeline -u jdbc:hive2://localhost:10000 -n hadoop
【连接后,会提示输入用户名,用户名就是Linux中启动hive的用户名,无密码】
Hive操作
(一)表结构操作
Hive和MySQL的表操作语句类似。
(1)先创建一个student1的内部表。1
2
3
4
5CTEATE TABLE IF NOT EXISTS student1
(sno INT, sname STRING,age INT,sex STRING)
ROW FORMAT DELIMITED
FIELDS TERMINATED BY '\t'
STORED AS TEXTFILE;
建表的规则如下 :【备注:全大写的为关键字;| 表示二选一;[]里面的是可选,实际sql中不加上此方括号;()里面的为一个整体,在实际sql中也需要加上此圆括号;小写的为自定义的名字】1
2
3
4
5
6
7
8CREATE [EXTERNAL] TABLE [IF NOT EXISTS] table_name
[(col_name data_type [COMMENT col_comment],......)]
[COMMENT table_comment]
[PARTITIONED BY (col_name data_type [COMMENT col_comment],...)]
[CLUSTERED BY (col_name,col_name,...) [SORTED BY (col_name [ASC|DESC])] INTO num_buckets BUCKETS]
[ROW FORMAT row_format]
[STORED AS file_format]
[LOCATION hdfs_path]
其中:
1、 CREATE TABLE创建一个指定名字的表。如果相同名字的表已经存在,则抛出异常;IF NOT EXIST选项表示忽略此异常。
2、 EXTERNAL关键字表示创建一个外部表,在建表的同时指定一个指向实际数据的路径(也就是使用 LOCATION hdfs_path)。
3、 LIKE允许用户复制现有的表结构,但不复制数据
4、 ROW FORMAT DELIMITED [FIELDS TERMINATED BY char] [COLLECTION ITEMS TERMINATED BY char] [MAP KEYS TERMINATED BY char][LINES TERMINATED BY char] | SERDE serde_name [WITH SERDEPROPERTIES (property_name=property_value,property_name=property_value,…)] 在建表的时候可以自定义SerDe或者使用自带的SerDe。如果没有指定ROW FORMAT或者ROW FORMAT DELIMITED,将会使用自带的SerDe。在建表的时候,用户还需要为表指定列,用户在指定表的列的同时也会指定自定义的SerDe,Hive通过SerDe确定表的具体列的数据。
5、 STORED AS
SEQUENCEFILE
| TEXTFILE
| RCFILE
| INPUTFORMAT input_format_classname OUTPUTFORMAT output_format_classname
如果文件数据是纯文本,可以使用STORED AS TEXTFILE。如果数据需要压缩,使用STORED AS SEQUENCE。
(2)创建外部表1
2
3
4
5
6CREATE EXTERNAL TABLE IF NOT EXISTS student2
(sno INT, sname STRING,age INT,sex STRING)
ROW FORMAT DELIMITED
FIELDS TERMINATED BY '\t'
STORED AS TEXTFILE
LOCATION '/home/lanting/hive/external'
(3)删除表1
DROP TABLE test1;
(4)修改表结构(例如:为表增加字段)1
2
3
4
5
6
7
8
9
10
11# 先查看原来表结构
DESC student1;
sno int
sname string
age int
sex string
# 增加两个字段
ALTER TABLE student1 ADD COLUMNS
(address STRING, grade STRING);
(5)修改表名student1为student31
ALTER TABLE student1 RENAME TO student3;
(6)创建和已知表相同结构的表1
CREATE TABLE copy_student1 LIKE student1;
(二)导入数据
(1)导入本地数据1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28# 查看student1表结构
DESC student1;
sno int
sname string
age int
sex string
address string
grade string
# 模拟输入如下(放置在/home/lanting/data/student1.txt)
201501001 张三 22 男 北京 大三
201501003 李四 23 男 上海 大二
201501004 王娟 22 女 广州 大三
201501010 周王 24 男 深圳 大四
201501011 李红 23 女 北京 大三
# 加载文件到student1中
LOAD DATA LOCAL INPATH '/home/lanting/data/student1.txt' INTO TABLE student1;
# 查询数据是否加载成功
SELECT * FROM student1;
201501001 张三 22 男 北京 大三
201501003 李四 23 男 上海 大二
201501004 王娟 22 女 广州 大三
201501010 周王 24 男 深圳 大四
201501011 李红 23 女 北京 大三
(2)加载hdfs中的文件【与加载本地数据相比,加载本地数据多了LOCAL关键字】1
2# 假设hdfs中/lanting/data下有个student1.txt文件
LOAD DATA INPATH '/lanting/data/student1.txt' INTO TABLE copy_student1;
(3)单表插入数据1
2# 将student1表中的数据插入到copy_student1表中去
INSERT OVERWRITE TABLE copy_student1 SELECT * FROM student1;
(4)向多个表中插入数据1
2
3
4
5
6# 向copy_student1和copy_student2中插入数据
FROM student1
INSERT OVERWRITE TABLE copy_student1
SELECT *
INSERT OVERWRITE TABLE copy_student2
SELECT *;
(三)表内容查询
(1)查询表的所有内容/某个字段1
2
3
4
5#查询表的所有内容
SELECT * FROM student1;
#查询表的某个字段属性
SELECT sname FROM student1;
(2)where条件查询1
SELECT * FROM student1 WHERE sno > 201501004 AND address="北京";
(3)DISTINCT去重1
2
3
4
5
6
7
8
9
10
11
12
13
14# 假设原来有数据
22 大三
23 大二
22 大三
24 大四
23 大三
# 使用distinct查询
SELECT DISTINCT age,grade FROM student1
22 大三
23 大三
23 大二
24 大四
(4)limit行数限制1
2
3
4
5
6
7# 只显示前四行
SELECT * FROM student1 LIMIT 4;
201501001 张三 22 男 北京 大三
201501003 李四 23 男 上海 大二
201501004 王娟 22 女 广州 大三
201501010 周王 24 男 深圳 大四
(5)group by分组查询
这里之所以在count()和sum()两个聚合函数中能同时使用distinct,是因为他们针对的是同一列1
2
3
4# 将group_test聚合后的数据插入group_gender_agg
INSERT OVERWRITE TABLE group_gender_agg
SELECT group_test.gender,count(distinct group_test.uid),count(*),sum(distinct group_test.uid)
FROM group_test GROUP BY group_test.gender;
(6)ORDER BY 排序查询
ORDER BY 会对输入做全局排序,因此只有一个Reduce(多个Reduce无法保证全局有序)会导致当输入规模较大时,需要较长的计算时间。使用ORDER BY查询的时候,为了优化查询的速度,应该使用hive.mapred.mode
属性。1
2hive.mapred.mode = nonstrict;(默认值)
hive.mapred.mode=strict;
在hive.mapred.mode=strict;
下必须制定limit,否则执行会报错。正确使用的例子如下:1
2
3
4
5
6
7
8# 设置hive.mapred.mode模式
set hive.mapred.mode=strict
SELECT * FROM group_test ORDER BY uid LIMIT 4;
01 male 192.168.1.2
01 male 192.168.1.32
01 male 192.168.1.26
01 male 192.168.1.22
(7)SORT BY 查询
sort by 不受hive.mapred.mode
的影响,sort by 的数据只能保证在 同一个Reduce中 的数据可以按指定字段排序。
使用 sort by 可以指定执行的 Reduce 个数(set mapred.reduce.tasks=< n>
)这样可以输出更多的数据。对输出的数据再执行归并排序,即可以得到全部结果。1
2
3
4#设置reduce的个数
set mapred.reduce.tasks=4
select * from group_test sort by uid ;
(8)DISTRIBUTE BY 排序查询
按照指定的字段对数据划分到不同的输出Reduce文件中。1
2
3INSERT OVERWRITE LOCAL DIRECTORY '/home/lanting/test'
SELECT * FROM group_test
DISTRIBUTE BY length(gender)
此方法根据gender的长度划分到不同的Reduce中,最终输出到不同的文件中。length是内建函数,表示取内容长度
(9)CLUSTER BY 查询
cluster by除了具有distribute by的功能外还兼具sort by 的功能。
视图操作
创建一个视图,length()为内建函数,取字段内容长度1
2
3CREATE VIEW test_view(id,name_length)
AS
SELECT id,length(name) FROM test;
(四)分区操作
Hive 的分区通过在创建表时启动 PARTITION BY 实现,用来分区的维度并不是实际数据的某一列,具体分区的标志是由插入内容时给定的。当要查询某一分区的内容时可以采用 WHERE 语句, 例如使用 “WHERE tablename.partition_key>a” 创建含分区的表。创建分区语法如下1
2
3
4CREATE TABLE table_name(
...
)
PARTITION BY (dt STRING,country STRING)
(1)创建分区
Hive 中创建分区表没有什么复杂的分区类型(范围分区、列表分区、hash 分区,混合分区等)。分区列也不是表中的一个实际的字段,而是一个或者多个伪列。意思是说,在表的数据文件中实际并不保存分区列的信息与数据。创建一个简单的分区表。1
2
3CREATE TABLE partition_test(member_id STRING,name STRING)
PARTITIONED BY (stat_date STRING, province STRING)
ROW FORMAT DELIMITED FIELDS TERMITED BY ',';
这个例子中创建了stat_date和province两个字段作为分区列。通常情况下需要预先创建好分区,然后才能使用该分区,例如:1
ALTER TABLE partition_test ADD PARTITION (stat_date='2015-01-02',province='chengdu');
这样就创建了一个分区,这时会看到Hive在HDFS存储中创建了一个相应的文件夹。每个分区都有一个独立的文件夹,此例子中,stat_date是主层次,province是副层次。
(2)插入数据
假设partition_test_input有 member_id,name,stat_date,province这几列。1
2
3
4
5
6#向partition_test的分区中插入partition_test_input中的数据
INSERT OVERWRITE TABLE partition_test
PARTITION (stat_date='2015-01-02',province='chengdu')
SELECT member_id,name FROM partition_test_input
WHERE stat_date='2015-01-02' AND province='chengdu';
向多个分区插入数据:1
2
3
4
5
6
7FROM partition_test_input
INSERT OVERWRITE TABLE partition_test PARTITION(stat_date='2013-02-03',province='sichuan')
SELECT member_id,name WHERE stat_date='2013-02-03' AND province='sichuan'
INSERT OVERWRITE TABLE partition_test PARTITION(stat_date='2013-04-03',province='chengdu')
SELECT member_id,name WHERE stat_date='2013-04-03' AND province='chengdu'
(3)动态分区
按照上面的方法向分区表中插入数据,如果数据源很大,针对一个分区就要写一个 insert,非常麻烦。使用动态分区可以很好地解决上述问题。动态分区可以根据查询得到的数据自动匹配到相应的分区中去。
动态分区可以通过下面的设置来打开:1
2set hive.exec.dynamic.partition=true;
set hive.exec.dynamic.partition.mode=nonstrict;
动态分区的使用方法很简单,假设向 stat_date=’2015-01-18’这个分区下插入数据,至于 province 插到哪个子分区下让数据库自己来判断。stat_date叫做静态分区列,province 叫做动态分区列。1
2
3INSERT OVERWRITE TABLE partition_test
PARTITION(stat_date='2015-01-02',province)
SELECT member_id,name,province FROM partition_test_input WHERE stat_date='2015-01-02';
【注意,动态分区不允许主分区采用动态列而副分区采用静态列,这样将导致所有的主分区都要创建副分区静态列所定义的分区】
hive.exec.max.dynamic.partitions.pernode:每一个 MapReduce Job 允许创建的分区的最大数量,如果超过这个数量就会报错(默认值100)。
hive.exec.max.dynamic.partitions:一个 dml 语句允许创建的所有分区的最大数量(默认值100)。
hive.exec.max.created.files:所有 MapReduce Job 允许创建的文件的最大数量(默认值10000)。
尽量让分区列的值相同的数据在同一个 MapReduce 中,这样每一个 MapReduce 可以尽量少地产生新的文件夹,可以通过 DISTRIBUTE BY 将分区列值相同的数据放到一起,命令如下。1
2
3INSERT OVERWRITE TABLE partition_test PARTITION(stat_date,province)
SELECT member_id,name,stat_date,province
FROM partition_test_input DISTRIBUTE BY stat_date,province;
(五)桶操作
Hive中table可以拆分成Partition table和桶(BUCKET),桶操作是通过Partition的CLUSTERED BY 实现的,BUCKET中的数据可以通过SORT BY 排序。
BUCKET主要作用如下:
1)数据Sampling
2)提升某些查询操作效率,例如:Map-Side Join。
需要特别注意的是,CLUSTERED BY和SORT BY不会影响数据的导入,这意味着,用户必须自己负责数据的导入,包括数据的分桶和排序,’set hive.enforce.bucketing=true’可以自动控制上一轮Reduce的数量从而适配BUCKET的个数,当然,用户也可以自主设置mapre.reduce.tasks去适配BUCKET个数,推荐使用:1
set hive.enforce.bucketing=true;
操作示例如下。
1)创建临时表student_temp,并导入数据。1
2DESC student_temp;
SELECT * FROM student_temp;
2)创建student表1
2
3
4CREATE TABLE student(id INT,age INT,name STRING)
PARTITIONED BY (stat_date STRING)
CLUSTERED BY (id) SORTED BY (age) INTO 2 BUCKET
ROW FORMAT DELIMITED FIELDS TERMINATED BY ',';
3)设置环境变量1
set hive.enforce.bucketing=true;
4)插入数据1
2
3FROM student_temp;
INSERT OVERWRITE TABLE student PARTITION(stat_date='2015-01-02')
SELECT id,age,name WHERE stat_date='2015-01-02' SORT BY age;
5)查看文件目录1
hadoop fs -ls /usr/hive/warehouse/student/stat_date=2015-01-02/
6)查看sampling数据1
SELECT * FROM student TABLESAMPLE(BUCKET 1 OUT OF 2 ON id);
tablesample是抽样语句,语法为:1
TABLESAMPLE(BUCKET x OUT OF y);
y必须是table中BUCKET 总数的倍数或者因子。
(六)Hive复合类型
hive提供了复合数据类型:
1)Structs: structs内部的数据可以通过DOT(.)来存取。例如,表中一列c的类型为STRUCT{a INT; b INT},我们可以通过c.a来访问域a。
2)Map(K-V对):访问指定域可以通过[“指定域名称”]进行。例如,一个Map M包含了一个group-》gid的kv对,gid的值可以通过M[‘group’]来获取。
3)Array:array中的数据为相同类型。例如,假如array A中元素[‘a’,’b’,’c’],则A[1]的值为’b’
Struct的使用
1)建表1
2
3CREATE TABLE student_test(id INT,info STRUCT<name:STRING,age:INT>)
ROW FORMAT DELIMITED FIELDS TERMINATED BY ','
COLLECTION ITEMS TERMINATED BY ":";
【这里的”FIELDS TERMINATED BY “表示字段与字段之间的分隔符;”COLLECTION ITEMS TERMINATED BY “表示字段内的item之间的分隔符】
2)导入数据
假设数据内容如下(,分隔符分割的是字段;:分隔符是分割字段内的item):1
2
3
4
5
6
71,zhou:30
2,yan:30
3,chen:20
4,li:80
# 复习一下导入命令
LOAD DATA LOCAL INPATH '/home/lanting/data/test.txt' INTO TABLE student_test;
3)查询数据1
SELECT info.age FROM student_test;
Array的使用
1)建表1
2
3
4CREATE TABLE class_test(name STRING,student_id_list ARRAY<INT>)
ROW FORMAT DELIMITED
FIELDS TERMINATED BY ','
COLLECTION ITEMS TERMINATED BY ':';
2)导入数据
假设数据内容如下(,分隔符分割的是字段;:分隔符是分割字段内的item):1
2
3
4
5
6034,1:2:3:4
035,5:6
036,7:8:9:10
# 复习一下导入命令
LOAD DATA LOCAL INPATH '/home/lanting/data/test.txt' INTO TABLE class_test;
3)查询1
SELECT student_id_list[3] FROM class_test;
Map的使用
1)建表
\t是字段分割符; ,是字段内item分隔符;:是item内key和value的分隔符。1
2
3
4
5CREATE TABLE employee (id STRING,perf MAP<STRING,INT>)
ROW FORMAT DELIMITED
FIELDS TERMINATED BY '\t'
COLLECTION ITEMS TERMINATED BY ','
MAP KEYS TERMINATED BY ':';
2)导入数据
假设数据内容如下(\t分隔符分割的是字段;,分隔符是分割字段内的item;: 分隔符是item内key和value的分隔符):1
2
3
4
5
61 job:80,team:60,person:70
2 job:60,team:80
3 job:90,team:70,person:100
# 复习一下导入命令
LOAD DATA LOCAL INPATH '/home/lanting/data/test.txt' INTO TABLE employee;
3)查询1
SELECT perf['person'] FROM employee;
(七)Hive的JOIN用法
hive只支持等连接,外连接,左半连接,而且,hive可以join两个以上的表。
1)等连接1
2
3
4
5SELECT a.* FROM a JOIN b ON (a.id = b.id)
# 或者
SELECT a.* FROM a JOIN b ON (a.id = b.id AND a.department = b.department)
2)多表连接
同个查询可以join两个以上的表1
2
3SELECT a.val,b.val,c.val FROM a
JOIN b ON (a.key=b.key1)
JOIN c ON (c.key=b.key2);
3)join的缓存和任务转换
hive转换多表join时,如果每个表在join字句中,使用的都是同一个列,只会转换成一个单独的map/reduce,例如:1
2
3SELECT a.val,b.val,c.val FROM a
JOIN b ON (a.key=b.key1)
JOIN c ON (c.key=b.key1)
下面这种将会被转换为两个map/reduce任务,因为key1列在第一个join条件中被使用,二b表的key2列在第二个join条件中使用,第一个map/reduce任务join a和b,第二个任务是第一个任务的结果join c。1
2
3SELECT a.val,b.val,c.val FROM a
JOIN b ON (a.key=b.key1)
JOIN c ON (c.key=b.key2)
(八)Hive常用内置操作符与函数
字符串函数
1)字符串长度函数:length
语法: length(string A)
返回值: int
说明:返回字符串A的长度1
2
3SELECT length('abcedfg') FROM dual;
7
2)字符串反转函数:reverse
语法: reverse(string A)
返回值: string
说明:返回字符串A的反转结果1
2
3select reverse('abcedfg') from dual;
gfdecba
3)字符串连接函数:concat
语法: concat(string A, string B…)
返回值: string
说明:返回输入字符串连接后的结果,支持任意个输入字符串1
2
3select concat('abc','def','gh') from dual;
abcdefgh
4) 带分隔符字符串连接函数:concat_ws
语法: concat_ws(string SEP, string A, string B…)
返回值: string
说明:返回输入字符串连接后的结果,SEP表示各个字符串间的分隔符1
2
3select concat_ws(','abc','def','gh') from dual;
abc,def,gh
5) 字符串截取函数:substr,substring
语法: substr(string A, int start),substring(string A, int start)
返回值: string
说明:返回字符串A从start位置到结尾的字符串1
2
3
4
5
6
7
8
9
10
11select substr('abcde',3) from dual;
cde
select substring('abcde',3) from dual;
cde
select substr('abcde',-1) from dual;
e
6) 字符串截取函数:substr,substring
语法: substr(string A, int start, int len),substring(string A, int start, int len)
返回值: string
说明:返回字符串A从start位置开始,长度为len的字符串1
2
3
4
5
6
7
8
9
10
11select substr('abcde',3,2) from dual;
cd
select substring('abcde',3,2) from dual;
cd
select substring('abcde',-2,2) from dual;
de
【其他字符串类型的函数就不一一举例了】还有:
7)字符串转大写函数:upper,ucase
语法: upper(string A) ucase(string A)
返回值: string
8)字符串转小写函数:lower,lcase
语法: lower(string A) lcase(string A)
返回值: string
9) 去空格函数:trim
语法: trim(string A)
返回值: string
10)左边去空格函数:ltrim
语法: ltrim(string A)
返回值: string
11)右边去空格函数:rtrim
语法: rtrim(string A)
返回值: string
集合统计函数
1) 个数统计函数 count。
语法:count(),count(expr),count(distinct expr)
返回值 int。
count()统计检索出行的个数,包括 NULL 值的行;
count(expr)返回指定字段的非空值的个数;
count(distinct expr)返回指定字段的不同的非空值的个数。
2) 总和统计函数 sum。
语法:sum(col),sum(distinct col)
返回值 double。
sum(col) 统计结果集中 col 的相加的结果;
sum(distinct col) 统计结果中 col 不同值相加的结果。
3) 平均值统计函数avg。
语法:avg(col),avg(distinct col)
返回值 double。
avg(col) 统计结果集中的平均值;
avg(distinct col) 统计结果中 col 不同值相加的平均值。
4) 最小值统计函数 min。统计结果集中 col 字段的最小值。
语法:min(col)
返回值double。
5) 最大值统计函数 max。统计结果集中 col 字段的最大值。
语法:max(col)
返回值 double。