Apache Hive

 ·  ☕ 17  · 👀...

介绍

基本介绍

说明 适用场景
Hive 简介
- 重在分析
- 延迟高
- 使用SQL来读、写、管理主流在分布式存储系统大型数据集
- 可以使用命令行和连接到hive
- 运行在hadoop之上,用来汇集查询数据
0.14版本开始支持事务和行级更新(需附加配置,缺省不支持)
特性
- 在数据库中存放schema,处理数据到HDFS
- 可扩展、可伸缩、速度快
- 数据仓库
- 提供类SQL语言HQL(HiveQL)
- OLAP:OnLine Analyze Process
- 非关系数据库
- 非OLTP
- 不适合实时查询和底层更新操作

基本组件

组件 说明
UI
MetaStore 编写SQL代替MR程序
HQL Process Engein 处理查询,生成结果
HDFS 存储数据

基本概念

内部表

外部表

分区

静态分区

所有手动指定的分区都属于静态分区。

动态分区

介绍

如果需要创建非常多的分区,那么用户就需要写非常多的SQL。
不过幸运的是,Hive提供了一个动态分区功能,其可以基于查询参数推断出需要创建的分区名称。

动态分区属性

属性名称 缺省值 描述
hive.exec.dynamic.partition false 是否开启动态分区功能
hive.exec.dynamic.partition.name strict nonstrict:表示所有分区都是动态的
hive.exec.max.dynamic.partitions.pernode 100 每个 mapper 或 reducer 可以创建的最大动态分区个数。如果某个 mapper 或 reducer 尝试创建大于这个值的分区的话则会抛出一个致命错误信息
hive.exec.max.dynamic.partitions 1000 一个动态分区创建语句可以创建的最大动态分区个数。如果超过这个值则会抛出一个致命错误信息
hive.exec.max.created.fles 100000 全局可以创建的最大文件个数。有一个Hadoop计数器会跟踪记录创建了多少个文件,如果超过这个值则会抛出一个致命错误信息

分区表

外部分区表

分桶表

函数

数学函数

返回值类型 样式 描述
BIGINT round(DOUBLE d) 返回DOUBLE型 d 的 BIGINT类型的近似值
DOUBLE round(DOUBLE d,INT n) 返回DOUBLE型 d 的保留 n 位小数的 DOUBLE 型的近似值
BIGINT floor(DOUBLE d) 返回<=d 的最大 BIGINT 型值
BIGINT ceil(DOUBLE d)
ceiling(DOUBLE d)
返回<=d 的最小 BIGINT 型值
DOUBLE rand()
rand(INT seed)
每行返回一个 DOUBLE 型随机数,整数 seed 是随机因子
DOUBLE exp(DOUBLE d) 返回e 的 d 幂次方
DOUBLE ln(DOUBLE d) 以自然数为底 d 的对数
DOUBLE log10(DOUBLE d) 以10为底的对数
DOUBLE log2(DOUBLE d) 以2为底的对数
DOUBLE log(DOUBLE base,DOUBLE d) 以 base 为底 d 的对数
DOUBLE pow(DOUBLE d,DOUBLE p)
power(DOUBLE d,DOUBLE p)
计算 d 的 p 次幂
DOUBLE sqrt(DOUBLE d) 计算 d 的平方根
STRING bin(BIGINT i) 计算二进制值
STRING hex(BIGINT i) 计算十六进制
STRING hex(STRING str) 计算十六进制
STRING hex(BINARY b) 计算十六进制
STRING unhex(STRING i) hex(STRING str)的逆方法
STRING conv(BIGINT num,INT from_base,INT to_base) 进制转换
STRING conv(STRING num,INT from_base,INT to_base) 进制转换
DOUBLE abs(DOUBLE d) 计算绝对值
INT pmod(INT i1,INT i2) 取模
DOUBLE pmod(DOUBLE d1,DOUBLE d2) 取模
DOUBLE sin(DOUBLE d) 正弦
DOUBLE asin(DOUBLE d) 反正弦
DOUBLE cos(DOUBLE d) 余弦
DOUBLE acos(DOUBLE d) 反余弦
DOUBLE tan(DOUBLE d) 正切
DOUBLE atan(DOUBLE d) 反正切
DOUBLE degrees(DOUBLE d) 弧度转角度
DOUBLE radians(DOUBLE d) 角度转弧度
INT positive(INT d) 返回 INT 型值 i(其等价的有效表达式是+i)
DOUBLE positive(DOUBLE d) 返回 DOUBLE 型值 d(其等价的有效表达式是+d)
INT negative(INT i) 返回 INT 型值 i 的负数(其等价的有效表达式是-i)
DOUBLE negative(DOUBLE d) 返回 DOUBLE 型值 d 的负数(其等价的有效表达式是-d)
FLOAT sign(DOUBLE d) 如果 DOUBLE 型值 d 是正数的话,则返回 FLOAT值 1.0;如果 d 是负数的话,则返回-1.0;否则返回0.0
DOUBLE e() 数学常数e,也就是超越数的 DOUBLE 型值
DOUBLE pi() 数学常数 pi,也就是圆周率的 DOUBLE 型值

聚合函数

返回值类型 样式 描述
BIGINT count(*) 计算总行数,包括含有NULL值的行
BIGINT count(expr) 计算提供的expr表达式的值非NULL的行数
BIGINT count(DISTINCT expr[,expr_.]) 计算提供的expr表达式的值排重后非NULL的行数
DOUBLE sum(col) 指定列求和
DOUBLE sum(DISTINCT col) 计算排重后值的和
DOUBLE avg(col) 指定列求平均值
DOUBLE avg(DISTINCT col) 指定列排重后求平均值
DOUBLE min(col) 最小值
DOUBLE max(col) 最大值
DOUBLE variance(col)
var_pop(col)
方差
DOUBLE var_samp(col) 样本方差
DOUBLE stddev_pop(col) 标准偏差
DOUBLE stddev_samp(col) 标准样本偏差
DOUBLE covar_pop(col1,col2) 协方差
DOUBLE covar_samp(col1,col2) 样本协方差
DOUBLE corr(col1,col2) 相关系数
DOUBLE percentile(BIGINT int_expr,DOUBLE p) int_expr在p(范围是:[0,1])处的对应的百分比
ARRAY percentile(BIGINT int_expr,ARRAY(P1 DOUBLE[,P2]…)) int_expr在p处对应处的百分比,其中p是一个DOUBLE型数组
DOUBLE percentile_approx(DOUBLE col,p[,NB]) col在p(范围是:[0,1])处的对应的百分比,其中p是一个DOUBLE型述职,NB是用于估计的直方图中的仓库数量(默认是10000)
ARRAY percentile_approx(DOUBLE col,ARRAY(p1[,p2]…)[,NB]) col在p(范围是:[0,1])处的对应的百分比,其中p是一个DOUBLE型数组,NB是用于估计的直方图中的仓库数量(默认是10000)
ARRAY<STRUCT{‘x’,‘y’}> histogram_nimberic(col,NB) 返回NB数值的直方图仓库数组,返回结果中的值x是中心,值y是仓库的高
ARRAY collect_set(col) 返回集合col元素排重后的数组

表生成函数

返回值类型 样式 描述
N行结果 explode(ARRAY array) 返回0到多行结果,每行都对应输入的array数组中的一个元素
N行结果 explode(MAP map) 返回0到多行结果,每行对应每个map键值对,其中一个字段是map的键,另一个字段对应map的值(Hive 0.8.0版本新增)
数组的类型 explode(ARRAY a) 对于a中的每个元素,explode()会生成一行记录包含这个元素
结果插入表中 inline(ARRAY<STRUCT[,STRUCT]>) 将结构体数组提取出来并插入表中(HIVE 0.10.0版本新增)
TUPLE json_tuple(STRING jsonStr,p1,p2,…,pn) 本函数可以接受多个标签名称,对输入的JSON字符串进行处理,这个get_json_object跟UDF类似,不过更高效,其通过一次调用就可以获得多个键值
TUPLE parse_url_truple(url,partname1,partname2,…,partnameN)
N≥1
从URL中解析出N个部分信息。其输入参数是:URL,以及多个要抽取的部分的名称。所有输入的参数的类型都是STRING。部分名称是大小写敏感的,而且不应该包含有空格:HOST、PATH、QUERY、REF、PROTOCOL、AUTHORITY、FILE、USERINFO、QUERY:<KEY_NAME>
N行结果 stack(INT n,col1,…,colM) 把M列转换成N行,每行有M/N个字段。
其中 n 必须是个常数

其它内置函数

返回值类型 样式 描述
STRING ascii(STRING s) 返回字符串s中首个ASCII字符的整数值
STRING base64(BINARY bin) 将二进制值bin转换成基于64位的字符串(Hive 0.12.0版本新增)
BINARY binary(STRING s)
binary(BINARY b)
将输入的值转换成二进制值(Hive 0.12.0版本新增)
返回类型就是type定义的类型 cast( as ) 将expr转换成type类型。
如果转换过程失败,则返回NULL
STRING concat(BINARY s1,BINARY s2,…) 将二进制字节码按次序拼接成一个字符串(Hive 0.12.0版本新增)
STRING concat(STRING s1,STRING s2,…) 将字符串s1、s2拼接成一个字符串。
STRING concat_ws(STRING separator,STRING s1,STRING s2,…) 和concat类似,不过是使用指定的分隔符进行拼接的
STRING concat_ws(BINARY separator,BINARY s1,BINARY s2,…) 和concat类似,不过是使用指定的分隔符进行拼接的(Hive 0.12.0版本新增)
ARRAY<STRUCT<STRING,DOUBLE» context_ngrams(array<array>,array,int K,int pf) 和ngrams类似,但是从每个外层数组的第二个单词数组来查找前K个字尾
STRING decode(BINARY bin,STRING charset) 使用指定的字符集 charset 将二进制值 bin 解码成字符串(支持的字符集有:‘US_ASCII’、‘ISO-8859-1’、‘UTF-8’、‘UTF-16BE’、‘UTF-16LE’、‘UTF-16’)。
如果任一输入参数为NULL,则结果为NULL(Hive 0.12.0版本新增)
BINARY encode(STRING src,STRING charset) 使用指定的字符集 charset 将字符串src 编码成二进制值(支持的字符集有:‘US_ASCII’、‘ISO-8859-1’、‘UTF-8’、‘UTF-16BE’、‘UTF-16LE’、‘UTF-16’)。
如果任一输入参数为NULL,则结果为NULL(Hive 0.12.0版本新增)
INT find_in_set(STRING s,STRING commaSeparatedString) 返回在以逗号分隔的字符串中 s 出现的位置,如果没有找到则返回NULL
STRING format_number(NUMBER x,INT d) 将数值 x 转换成’#,###,###.##’ 格式字符串,并保留d位小数。如果d为0,那么输出值就没有小数点后面的值
STRING get_json_object(STRING json_string,STRING path) 从给定路径上的JSON字符串中抽取出JSON对象,并返回这个对象的JSON字符串形式。如果输入的JSON字符串是非法的,则返回NULL
BOOLEAN in 例如:test in(val1,val2,…),其表示如果test值等于后面列表中的任一值的话,则返回true
BOOLEAN in_file(STRING s,STRING filename) 如果文件名为filename的文件中有完整一行数据和字符串s完全匹配的话,则返回true
INT instr(STRING str,STRING substr) 查找字符串s的长度
INT length(STRING s) 计算字符串 s 的长度
INT locate(STRING substr,STRING str[,INT pos]) 查找在字符串str中的pos位置后字符串substr第一次出现的位置
STRING lower(STRING s) 字符串转小写
STRING lcase(STRING s) 字符串转小写。和lower一样
STRING lpad(STRING s,INT len,STRING pad) 从左边开始对字符串 s 使用字符串pad进行填充,最终达到len长度为止。如果字符串 s 本身长度比len大的话,那么多余的部分会被去除掉。
STRING ltrim(STRING s) 将字符串s 前面出现的空格全部去除掉
ARRAY<STRUCT<STRING,DOUBLE» narams(ARRAY<ARRAY>,INT N,INT K,INT pf) 估算文件中前K个字符串。pf是精度系数。
STRING parse_url(STRING url,STRING partname[,STRING key]) 从URL中抽取指定部分的内容。参数url表示一个URL字符串,参数partname表示要抽取的部分名称,其是大小写敏感的。
可选值有:HOST、PATH、QUERY、REF、PROTOCOL、AUTHORITY、FILE、USERINFO、QUERY:
如果partname是QUERY的话,那么还需要指定第三个参数key。
STRING printf(STRING format,Obj… args) 按照 printf 风格格式化输出输入的字符串(Hive 0.9.0 版本新增)
STRING regexp_extract(STRING subject,STRING regex_pattern,STRING index) 抽取字符串subject中符合正则表达式regex_pattern的第index个部分的子字符串
STRING regexp_replace(STRING s,STRING regex,STRING replacement) 按照Java正则表达式regex将字符串s中符合条件的部分替换成replacement所指定的字符串a。
如果replacement部分是空的话,那么符合正则的部分就会被去除掉。
例如:rgexp_replace(‘hive’,'[ie]',‘z'的结果是’hzvz’)
STRING repeat(STRING s,INT n) 重复输出n次字符串s
STRING reverse(STRING s,INT len,STRING pad) 反转字符串
STRING rpad(STRING s,INT len,STRING pad) 从右边开始对字符串s使用字符串pad进行填充,最终达到len长度为止。
如果字符串 s 本身长度比 len 大的话,那么多余的部分会被去除掉
STRING rtrim(STRING s) 将字符串 s 后面出现的空格全部去除掉。
ARRAY<ARRAY> sentences(STRING s,STRING lang,STRING locale) 将输入字符串 s 转换成句子数组,每个句子又由一个单词数组构成。参数lang和locale是可选的,如果没有使用的,则使用默认的本地化信息
INT size(MAP<K.V>) 返回map中元素的个数
INT size(ARRAY) 返回数组ARRAY的元素个数
STRING space(INT n) 返回 n 个空格
ARRAY split(STRING s,STRING pattern) 按照正则表达式分割字符串 s,并将分割后的部分字符串数组的方式返回
MAP<STRING,STRING> str_to_map(STRING s,STRING delim1,STRING delim2) 将字符串 s 按照指定分隔符转换成Map,第一个参数是输入的字符串,第二个参数是键值对之间的分隔符,第三个参数是键和值之间的分隔符
STRING substr(STRING s,STRING start_index)
substring(STRING s,STRING start_index)
对于字符串s,从start位置开始截取length长度的字符串,作为子字符串。
例如:substr(‘abcdefgh’,3,2)的结果是’cd’
STRING substr(BINARY s,STRING start_index)
substring(BINARY s,STRING start_index)
对于二进制自截止s,从staart位置开始截取length长度的字符串作为子字符串(Hive 0.12.0 新增)
STRING translate(STRING input,STRING from,STRING to)
STRING trim(STRING A) 将字符串s前后出现的空格全部去除掉。
BINARY unbase64(STRING str) 将基于64位的字符串str转换成二进制值(Hive 0.12.0 新增)
STRING upper(STRING A)
ucase(STRING A)
将字符串中所有字母转换成大写字母。
STRING from_unixtime(BEGINT unixtime[,STRING format]) 将时间戳秒数转换成UTC时间,并用字符串表示,可以通过format规定的时间格式,指定输出的时间格式
BIGINT unix_timestamp() 获取当前本地时区下的当前时间戳
BIGINT unix_timestamp(STRING date) 输入的时间字符串格式必须是yyyy-MM-dd HH:mm:ss,如果不符合则返回0,如果符合则将此时间字符串转换成Unix时间戳。
BIGINT unix_timestamp(STRING date,STRING pattern) 将指定时间字符串转换成Unx时间戳,如果格式不对则返回0。
STRING to_date(STRING timestamp) 返回时间字符串的日期部分。
INT year(STRING date) 返回时间字符串中的年并用INT类型表示
INT month(STRING date) 返回时间字符串中的月并用INT类型表示
INT day(STRING date)
dayofmonth(STRING date)
返回时间字符串中的天并用INT类型表示
INT hour(STRING date) 返回时间字符串中的小时并用INT类型表示
INT minute(STRING date) 返回时间字符串中的分钟并用INT类型表示
INT second(STRING date) 返回时间字符串中的秒并用INT类型表示
INT weekofyear(STRING date) 返回时间字符串位于一年中第几个周内。
INT datediff(STRING enddate,STRING startdate) 计算开始时间startdate到结束时间enddata相差的天数。
STRING date_add(STRING startdate,INT days) 为开始时间startdata增加day天。
STRING date_sub(STRING startdate,INT days) 从开始时间startdata中减去day天。
TIMESTAMP from_utc_timestamp(TIMESTAMP timestamp,STRING timezone) 如果给定的时间戳并非UTC,则将其转化成指定的时间区间下的时间戳(Hive 0.8.0 版本新增)
TIMESTAMP to_utc_timestamp(TIMESTAMP timestamp,STRING timezone) 如果给定的时间戳是指定的时区下的时间戳,则将其转化成UTC下的时间戳(Hive 0.8.0 版本新增)

兼容性

Hadoop与Hive兼容性

Hadoop版本 Hive版本
3.x.y - 3.1.1
- 3.1.0
- 3.0.0
2.x.y - 2.3.5
- 2.3.4
- 2.3.3
- 2.3.2
- 2.3.1
- 2.3.0
- 2.1.1
- 2.1.0
- 2.0.1
- 2.0.0
1.x.y、2.x.y - 1.2.2
- 1.2.1
- 1.1.1
- 1.1.0
- 1.0.1
- 1.0.0
- 0.13.1
- 0.13.0
- 0.12.0
- 0.11.0
- 0.10.0
0.20.x、0.23.x.y - 0.13.1
- 0.13.0
- 0.12.0
- 0.11.0
- 0.10.0

更多兼容性参考 http://hive.apache.org/downloads.html

JDBC与HiveServer兼容性

JDBC版本 HiveServer0.13 HiveServer1.2 HiveServer2.1
jdbc0.13
jdbc1.21
jdbc2.1.0
jdbc2.1.1

HiveQL

数据库

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
# 显示库
hive> SHOW DATABASES;

# 切换库
hive> USE databaseName;

# 创建库
hive> CREATE DATABASE databaseName;

# 删除数据库
hive> DROP DATABASE databaseName;

# 修改数据库
hive> ALTER DATABASE

 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
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
# 显示表
hive> SHOW TABLES;

# 创建表
hive> CREATE TABLE tableName(
    name            STRING,
    salary          FLOAT,
    subordinates    ARRAY<String>,
    deductions      MAP<STRING,FLOAT>,
    address         STRUCT<street:STRING,city:STRING,state:STRING,zip:INT>
    )
    [PARTITIONED BY(country STRING,state STRING)];

# 增加、修改、删除分表分区
hive> ALTER TABLE log_messages ADD IF NOT EXISTS
      PARTITION (year=2011,month=1,day=1) LOCATION '/logs/2011/01/01'
      PARTITION (year=2011,month=1,day=2) LOCATION '/logs/2011/01/02'
      PARTITION (year=2011,month=1,day=3) LOCATION '/logs/2011/01/03'
      ...;

# 修改某分区的位置
hive> ALTER TABLE log_messages PARTITION (year=2011,month=1,day=2) 
      SET LOCATION 's3n://ourbucket/logs/2011/01/02';

# 删除分区
hive> ALTER TABLE log_messages DROP IF EXISTS PARTITION(year=2011,month=1,day=12)# 修改列信息
hive> ALTER TABLE log_messages 
      CHANGE COLUMN hms hours_minutes_seconds INT
      COMMENT 'The hours,minutes,and seconds part of the timestamp'
      AFTER severity;

# 增加列
hive> ALTER TABLE log_messages ADD COLUMNS(
      app_name STRING COMMENT 'Application name',
      session_id LONG COMMENT 'The current session id');

# 删除或者替换列
hive> ALTER TABLE log_messages REPLACE COLUMNS(
      hours_mins_secs INT       COMMENT 'hour,minute,seconds from timestamp',
      severity        STRING    COMMENT 'The message severity',
      message         STRING    COMMENT 'The rest of the message');

# 修改表属性
hive> ALTER TABLE log_messages SET TBLPROPERTIES(
      'notes'='the process id is no longer captured;this column is always NULL');

# 修改存储属性
hive> ALTER TABLE log_messages 
      PARTITION (year=2011,month=1,day=1) 
      SET FILEFORMAT SEQUENCEFILE;

# 删除表
hive> DROP TABLE [IF EXISTS] tableName;

# 重命名表
hive> ALTER TABLE oldTableName RENAME TO newTableName;

数据操作

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
# 向管理表中装载数据
    # PARTITION:目标表是分区表。
    # LOCAL:这个路径应该为本地文件系统路径。如果省略掉该关键字,那么这个路径应该是分布式文件系统中的路径。
hive> LOAD DATA LOCAL INPUTPATH '${env:HOME}/california-employees'
       OVERWRITE INTO TABLE employees
       PARTITION (country='US',state='CA');

# 通过查询语句向表中插入数据
hive> INSERT INTO TABLE employees
       PARTITION (country='US',state='OR')
       SELECT * FROM staged_employees se
       WHERE se.cnty='US' AND se.st='OR';

# 动态分区插入(基于查询参数推断出需要创建的分区名称)
hive> INSERT OVERWRITE TABLE employees
       PARTITION (country,state)
       SELECT * FROM staged_employees se;

导出数据

如果数据文件恰好是用户需要的格式,那么只需要简单地拷贝文件或者文件夹就可以了。否则,用户可以使用INSERT…DIRECTORY…

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
# 使用拷贝文件或者文件夹导出数据
$ hadoop fs -cp source_path target_path

# 使用INSERT...DIRECTORY...导出数据
hive> INSERT OVERWRITE local DIRECTORY '/tmp/ca_employees'
      SELECT name,salary,address
      FROM employees
      WHERE se.state='CA';

# 指定多个输出文件夹目录
hive> FROM staged_employees se
      INSERT OVERWRITE DIRECTORY '/tmp/or_employees'
            SELECT * WHERE se.city='US' and se.st='OR'
      INSERT OVERWRITE DIRECTORY '/tmp/ca_employees'
            SELECT * WHERE se.city='US' and se.st='CA'
      INSERT OVERWRITE DIRECTORY '/tmp/il_employees'
            SELECT * WHERE se.city='US' and se.st='IL';

查询

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
# 基本查询
hive> SELECT name,salary FROM employess;

# 使用别名查询
hive> SELECT e.name,e.salary FROM employees e;

# 查询集合
    # subordinates:map集合
hive> SELECT name,subordinates[0] FROM employees;
hive> SELECT name,deductions["State Taxes"] FROM employees;
    # address:STRUCT集合
hive> SELECT name,address.city FROM employees;

# 使用正则表达式来指定列
hive> SELECT symbol,'price.*' FROM stocks;

# 使用列值进行计算
hive> SELECT upper(name),salary,deductions["Federal Taxes"],
      round(salary * (1-deductions["Federal Taxes"])) 
      FROM employees;

LIMIT

1
2
3
# LIMIT:限制返回的行数
hive> SELECT upper(name),salary FROM employees
      LIMIT 2;

列别名

1
hive> SELECT name,salary,round(salary * (1-deductions["Federal Taxes"])) as salart_minus_fed_taxes

嵌套SELECT语句

1
2
3
4
5
6
7
hive> FROM(
      SELECT upper(name),salary,deductions["Federal Taxes"],
      round(salary * (1-deductions["Federal Taxes"])) 
      FROM employees;
      ) e
      SELECT e.name,e.salary_minus_fed_taxes
      WHERE e.salary_minus_fed_taxes>70000;

CASE…WHEN…THEN句式

1
2
3
4
5
6
hive> SELECT name,salary,
      CASE 
            WHEN salary < 50000.0 THEN 'low'
            WHEN salary >= 50000.0 ADN salary < 70000.0 THEN 'middle'
            WHEN salary >= 70000.0 ADN salary < 100000.0 THEN 'high'
      END AS barcket FROM employees;

GROUP BY语句

1
2
3
hive> SELECT year(ymd),avg(price_close) FROM stocks
      WHERE exchange='NASDAQ' AND symbol='AAPL'
      GROUP BY year(ymd);

HAVING语句

1
2
3
4
hive> SELECT year(ymd),avg(price_close) FROM stocks
      WHERE exchange='NASDAQ' AND symbol='AAPL'
      GROUP BY year(ymd)
            HAVING avg(price_close) > 50.0;

JOIN语句

INNER IOIN

1
2
3
hive> SELECT a.ymd,a.price_close,b.price_close
      FROM stocks a JOIN stocks b ON a.ymd=b.ymd
      WHERE a.symbol='AAPL' AND b.symbol='IBM';

LEFT OUTER IOIN

1
2
3
hive> SELECT s.ymd,s.symbol,s.price_close,d.dividend
      FROM stocks s LEFT OUTER JOIN dividends d ON s.ymd=d.ymd AND s.symbol=d.symbol
      WHERE s.symbol='AAPL';

RIGHT OUTER JOIN

1
2
3
hive> SELECT s.ymd,s.symbol,s.price_close,d.dividend
      FROM dividends d RIGHT OUTER JOIN stocks s ON s.ymd=d.ymd AND s.symbol=d.symbol
      WHERE s.symbol='AAPL';

FULL OUTER JOIN

1
2
3
hive> SELECT s.ymd,s.symbol,s.price_close,d.dividend
      FROM dividends d FILL OUTER JOIN stocks s ON s.ymd=d.ymd AND s.symbol=d.symbol
      WHERE s.symbol='AAPL';

LEFT SEMI-JOIN

LEFT SEMI-JOIN(左半开连接)会返回左边表的记录,前提是其记录对于左边表满足ON语句中的判定条件。
可以实现INNER JOIN(内连接)同样的目的。
SEMI-JOIN比通常的INNER JOIN要更高效。原因:对于左表中一条指定的记录,在右变表中一旦找到匹配的记录,Hive就会立即停止扫描。

1
2
hive> SELECT s.ymd,s.symbol,s.price_close
      FROM stocks s LEFT SEMI JOIN dividends d ON s.ymd=d.ymd AND s.symbol=d.symbol;

笛卡尔积JOIN

1
2
hive> SELECT * FROM stocks JOIN dividends
      WHERE stock.symbol = dividends.symbol and stock.symbol='APPL';

map-side JOIN

1
2
3
4
5
hive> set hive.auto.convert.join=true;
hive> set hive.mapjoin.smalltable.filesize=25000000;
hive> SELECT s.ymd,s.symbol,s.price_close,d.dividend
      FROM stocks s JOIN dividends d ON s.ymd=d.ymd AND s.symbol=d.symbol
      WHERE s.symbol='AAPL';

ORDER BY 和 SORT BY

Hive新增了一个可供选择的方式,也就是SORT BY,其只会在每个reducer中对数据进行排序,也就是执行一个局部排序过程。这可以保证每个reducer的输出数据都是有序的(但并非全局有序)。
这样可以提高后面进行全局排序的效率。

CLUSTER BY

CLUSTER BY = SORT BY(1.列完全相同;2.升序排列(默认排序方式))

抽样查询

数据块抽样

按照抽样百分比进行抽样。
这种抽样方式不一定适用于所有的文件格式。
另外,这种抽样的最小抽样单元是一个HDFS数据块。因此,如果表的数据大小小于普通的块的大小128MB的话,那么将会返回所有行。

1
hive> SELECT * FROM numbersflat TABLESAMPLE(0.1 PERCENT) s;
分桶表的输入裁剪
1
hive> SELECT * FROM numbersflat TABLESAMPLE(BICKET 2 OUT OF 3 ON NUMBER) s;

UNION ALL

UNION ALL可以将2个或多个表进行合并。
每个union子查询都必须具有相同的列,而且对应的每个字段类型必须是一致的。

1
2
3
4
5
6
7
8
9
# 日志数据合并
hive> SELECT log.ymd,log.level,log.message
      FROM 
            SELECT 11.ymd,11.level,11.message,'Log1' AS source
            FROM log1 11
      UNION ALL
            SELECT 12.ymd,12.level,12,message,'Log2' AS source
            FROM log1 12
      SORT BY log.ymd ASC;

类型转换

1
2
3
4
5
hive> SELECT name,salary FROM employees
      WHERE cast(salary AS FLOAT) < 100000.0;

hive> SELECT (2.0*cast(cast(b AS string) as double)) 
      FROM src;

HIVE中不支持的查询语句

  1. HIVE 支持通常的SQL JOIN语句,但是只支持等值连接。
  2. HIVE 目前还不支持在ON子句中的为此间使用OR。
  3. HIVE 目前还不支持IN…EXISTS
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
# 错误示范1:Hive中只支持等值连接
hive> SELECT a.ymd,a.price_close,b.price_close
      FROM stocks a JOIN stocks b 
      ON a.ymd<=b.ymd         
      WHERE a.symbol='AAPL' AND b.symbol='IBM';

# 错误示范2:不支持IN...EXISTS
hive> SELECT a.ymd,a.symbol,b.price_close       
      WHERE a.ymd,a.symbol IN
      (SELECT d.ymd,d.symbol FROM dividends d);

视图

索引

客户端工具

CLI

HiveServer2/beeline

Web UI

DbVisualizer

官网:https://www.dbvis.com

调优

JOIN优化

  1. 当对3个或更多个表进行JOIN连接时,如果每个ON子句都使用相同的连接键的话,那么只会产生一个MapReduce job
  2. HIVE同时假定查询中最后一张表是最大的那个表。在对每行记录进行连接操作时,它会尝试将其它表缓存起来,然后扫描最后那个表进行计算。因此,用户需要保证连续查询中的表的大小从左到右是依次增加的。

安全

开发

使用JDBC连接Hive

参见 hivebasic

Hive命令

CLI选项

1
$ hive --help
参数选项 名称 说明
cli 命令行界面 默认服务。
hiveserver Hive Server 监听来自于其它进程的Thrift连接的一个守护进程
hwi Hive Web界面 一个可以执行查询语句和其它命令的简单的Web界面,这样可以不用登陆到集群中的某台机器上使用CLI来进行查询。
jar hadoop jar命令的一个扩展,这样可以执行需要Hive环境的应用
metastore 启动一个扩展的Hive元数据服务,可以供多客户端使用。
rcfilecat 一个可以打印出RCFile格式文件内容的工具
–auxpath 允许用户指定一个以冒号分割的“附属的”jar包,这些文件中包含有用户可能需要的自定义扩展等。
–config 这个命令允许用户覆盖$HIVE_HOME/conf中默认的属性配置,而指向一个新的配置文件目录。

变量和属性

命名空间 使用权限 描述
hivevar 可读、可写 用户自定义变量(Hive 0.8.0及以后版本)
hiveconf 可读、可写 Hive相关的配置属性
system 可读、可写 Java定义的配置属性
env 可读 Shell环境定义的环境变量
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
##### 定义变量 #####
# 定义变量(方式1)
$ hive --define foo=bar

# 定义变量(方式2)
hive> set foo:bar;


#####  读取变量 #####
# 读取所有变量
    # 不加-v: set命令会打印出命名空间hivevar、hiveconf、system、env中所有的变量。
    # -v:打印Hadoop中所定义的所有属性。
hive> set;

# 读取变量foo
hive> set foo;

“一次使用”命令

1
2
3
# 执行结束后hive CLI立即退出(临时应急时可以将查询结果保存到一个文件中)
# -S:开启静默模式。这样可以在输出结果中去掉“OK”和“Time taken”等行,以及其它一些无关紧要的输出信息。
$ hive -e "SELECT * FROM mytable LIMIT 3";

从文件中执行Hive查询

1
2
# 执行指定文件中的一个或者多个查询语句
$ hive -f /path/to/file/withqueries.hql

加载hiverc文件

1
2
# 当CLI启动时,在提示符出现前会先执行.hiverc文件,Hive会自动在HOME目录下寻找名为.hiverc的文件,而且会自动执行这个文件中的命令。
$ hive -i xxx.hiverc

.hiverc文件示例:

ADD JAR /path/to/custom_hive_extensions.jar;
set hive.cli.print.current.db=true;
set hive.exec.mode.local.auto=true;

执行shell命令

1
2
3
4
5
6
7
8
# 输出文本
hive> ! /bin/echo "hello bingbing";

# 显示当前路径
hive> ! pwd;

# 清屏
hive> !clear;

使用Hadoop的dfs命令

1
2
hive> dfs -ls / ;
hive> dfs -help;

运维

安装(CentOS)

安装环境

安装jdk

参考 {% post_link jdk安装 %}

安装Hadoop

参考 {% post_link ‘Apache Hadoop安装’ %}

安装Hive

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
# 下载 http://www.apache.org/dyn/closer.cgi/hive/
$ cd /usr/local/
$ wget https://mirrors.tuna.tsinghua.edu.cn/apache/hive/hive-3.1.1/apache-hive-3.1.1-bin.tar.gz
# 解压
$  
# 配置环境变量
$ 
##### 环境变量开始 #####
HIVE_HOME
PATH
##### 环境变量结束 ##### 
# 配置Hive
$ vi /usr/local/hive/conf/hive-env.sh
# 增加如下内容
HADOOP_HOME=/usr/local/hadoop

# 配置Hive的元数据库
$ cd /usr/local/hive/conf
$ cp hive-default.xml.template hive-site.xml
# 替换${system:java.io.tmp.dir}=/home/hive
# 替换${system:user.name}=hive/user

# 初始化Schema库(完成后在当前目录下创建一个文件夹metastore_db)
$ schematool -initSchema -dbType derby

安装(Ubuntu)

参考


Wanglibing
Wanglibing
Engineer,Lifelong learner