Hive 数据类型

基本数据类型

注：Hive会隐式地将类型转换为两个整型类型中值较大的那个类型；

集合数据类型

示例（用类似JSON的结构描述）：

{
    "names": ["Smith" , "Jones"] ,   //列表Array, names[1]="Jones"
    "deductions": {                  //键值Map, deductions[’Federal’]=0.2
        "Federal": 0.2 ,
        "State": 0.05,
        "Insurance": 0.1
    }
    "address": {                     //结构Struct, address.city=”Chicago”
        "street": "1 Michigan Ave." ,
        "city": "Chicago" ,
        "state": "IL" ,
        "zip": 60600
    }
}

在Hive中实际存储形式：

Smith_Jones,Federal:0.2_State:0.05_Insurance:0.1,Michigan Ave._Chicago_1L_60600
Jan_Ketty,Federal:0.2_State Taxes:0.05_Insurance:0.1,Guang dong._China_0.5L_60661

注：MAP,STRUCT和ARRAY里的元素间关系都可以用同一个字符表示，这里用“_”

取数：

select names[1], deductions['Federal'],address.city
from learn.employees;
------
OK
Jones     0.2    Chicago
Ketty    0.2    China
Time taken: 0.123 seconds, Fetched: 2 row(s)

文本文件数据编码

Hive中默认的记录和字段分隔符

在创建表格的时候，可规定分隔符：

create table employee(
name            string,
salary          float,
subordinates    array<string>,
deductions      map<string, float>,
address         struct<street:string, city:string, state:string, zip:int>
)
row format delimited
fields terminated by '\001'    //列分隔符
collection items terminated by '\002'               //集合元素间的分隔符
map keys terminated by '\003'                      //map 键值对的分隔符
lines terminated by '\n'                        //行与行之间的分隔符，只支持'\n'
stored as textfile;        

读时模式

Hive处理的数据是大数据，在保存表数据时不对数据进行校验，而是在读数据时校验，不符合格式的数据设置为NULL；

读时模式的优点是，加载数据库快。

HiveQL：数据定义

Hive不支持行级插入操作、更新操作和删除操作，Hive也不支持事务。
HiveQL数据定义语言部分，用于创建、修改和删除数据库、表、试图、函数和索引

Hive中的数据库

创建数据库：

create database financials;

防止因“已存在”而抛出错误：

create database if not exits financials;

查看包含的数据库：

show databases;
---
default
financials

使用正则表达式匹配：

show databases like 'h.*';
---
human_resources

存储位置：
Hive会为每个数据库创建一个目录。数据库中的表将会以这个数据库目录子目录的形式存储。数据库所在目录位于属性hive.metastore.warehouse.dir所指定的顶层目录之后。可通过如下命令修改：

hive> create database financials
    > location '/my/preferred/directory'

描述信息：

hive> create database financials
    > comment 'Holds all financial tables'

hive> describe database financials;
---
financials Holds all financial tables
  hdfs://master-server/user/hive/warehouse/financials.db

设置为当前的工作数据库

hive> use financials;

显示正在使用的数据库

hive> set hive.cli.print.current.db=true;

hive (financials)> use default;

hive (default)> set hive.cli.print.current.db=false;

hive> ...

删除数据库

hive> drop database if exists financials;  //if exists可选，避免抛出警告

注：默认情况下，Hive是不允许用户删除一个包含有表的数据库的；要么先删表，再删库；要么加上关键字 cascade

hive> drop database if exists financials cascade;

修改数据库
使用 alter database命令为某个数据库的dbproperties设置键-值对属性，来描述这个数据库的属性信息。其他信息不可更改。

hive> alter database financials set dbproperties ('edited-by' = 'Joe Dba')

创建表

在mydb库中增加employees表：

create table if not exists mydb.employees(
    name              string                comment    'employee name'//comment 后面是属性
    salary            float                 comment    'employee salary'
    subordinates      array<string>         comment  'names of subordinates'
    deductions        map<string, float>    comment   'names,values'
    address           struct<street:string, city:string, state:string, zip:int> comment 'home address'
)
comment  'description of the table'
tblproperties  ('creator'='me','created_at'='2012-01-02')//可通过键值对增加配置信息
location '/user/hive/warehouse/mydb.db/employees';

拷贝一张已经存在的表的表模式（而无需拷贝数据）：

create table if not exists mydb.employees2
like mydb.employees;

呈现所有表

use mydb;
show tables;
----
employees
employees2

呈现所有表，不在当前数据库情况下：

show tables in mydb;
----
employees
employees2

过滤所需表

show tables '*.ees2'
----
employees2

查看表结构信息：

describe extended mydb.employees;
----
name string employee name
...

查看更详细信息

describe formatted mydb.employees;
----
name string employee name
...

只查看某一列的信息

described mydb.employees.salary;
---
salary float employees salary

管理表vs外部表

上面创建的是所谓的管理表，也被称为内部表；删除表的时候，会删除数据。
现在可以创建外部表，其可以读取所有位于/data/stocks 目录下的以逗号分隔的数据：

create external table if not exists stocks (
    exchange      string,
    symbol        string,
    ymd           string,
    price_open    float,
    price_high    float,
    price_low     float,
    price_close   float,
    volume        int,
    price_adj_close    float
)
row format delimited fields terminated by ','
location  '/data/stocks'

external告诉hive这个表是外部的，location告诉hive数据位于哪个路径。这个表对相关文件无控制权，因此删除表并不会删除数据。
通过describe extended tablename查看表信息的时候，可查看是管理表还是外部表：

...tableType:managed_table;
...tableType:external_table;

用户还可以对一张存在的表进行表结构的复制（不复制数据），这里external关键词决定了无论源表是管理表还是外部表，创建的表都是外部表。

create external table if not exists mydb.employees3
like mydb.employees
location '/path/to/data';

分区表、管理表

分区管理表具有明显的性能优势
先按照country再按照state来对数据进行分析：

create table if not exists mydb.employees(
    name              string                comment    'employee name'
    salary            float                 comment    'employee salary'
    subordinates      array<string>         comment  'names of subordinates'
    deductions        map<string, float>    comment   'names,values'
    address           struct<street:string, city:string, state:string, zip:int> comment 'home address'
)
partitioned by (country string,state string);

之前的表存储在如下目录：

hdfs://master_server/user/hive/warehouse/mydb.db/employees

数据是按照如下子目录存储的：

...
.../employees/country=CA/state=AB
.../employees/country=CA/state=BC
...
.../employees/country=US/state=AL
.../employees/country=US/state=AK
...

如何在分区表查找信息：

select *
from employees
where country = 'US' and state = 'AL'

设置“strice”模式，对分区表查询的时候如果不对分区进行过滤，则任务不会提交：

set hive.mapred.mode = strict;
set hive.mapred.mode = nostrict;

查看所有分区

show partitions employees;
-----
country=CA/state=AB
country=CA/state=BC
...

show partitions employees partition (country='US');
-----
country=US/state=AL
country=US/state=AK
...

如何引用HOME环境变量：

load data local inpath '${env:HOME}/california-employees'
into table employees
patition (country = 'US', state = 'CA');

Hive会把${env:HOME}/california-employees这个目录下面的文件将会拷贝到上述分区目录下。

外部分区表

外部表也可以使用分区，可首先创建分区，然后载入数据

create external table if not exists log_messages(
    hms           int,
    severity      string,
    server        string,
    process_id    int,
    message       string
)
partitoned by (year int, month int, day int)
row format delimited fields terminated by '\t';
//这里不要求location子句

可通过alter table语句可以单独进行增加分区（同样适用于管理表）

alter table log_messages add partition(year=2012,month=1,day=2)
location 'hdfs://master_server/data/log_messages/2012/01/02';

删除表

drop table if exists employees

管理表：删除表的元信息和数据
外部表：删除表的元信息

修改表

修改表只会影响表的元信息，用户需自己确保数据符合元信息要求。

表重命名

alter table log_messages rename to logmsgs;

增加、修改、删除表分区

增加

alter table log_messages add if not exists
partition (year=2012, month=1, day = 1) location '/logs/2011/01/01'
partition (year=2012, month=1, day = 2) location '/logs/2011/01/02'
...

修改

alter table log_messages partition (year=2012, month=1, day = 2) 
set location 's3n:/ourbucket/logs/2011/01/02'

删除

alter table log_messages drop if exists partition (year=2012, month=1, day = 2) 

修改列信息

alter table log_messages
change columns hms hours_minutes_seconds int
comment 'the hours, minutes, and seconds part of the timestamp'
after severity;

重命名、改类型，改列顺序到severity后面（如果想移动到第一个位置则要用first替代after severity）
上述语句只修改元数据，用户需保证数据符合元数据的要求。

增加列

alter table log_messages add columns(
    app_name     string   comment 'application name',
    session_id   long     comment 'the current session id'
);

删除或替换列（等于整表替换为如下的列）

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'
)

修改表属性

alter table log_messages set tblproperties (
    'notes' = 'the process id is no longer captured;this column is always NULL'
)

HiveQL：数据操作

向管理表中装载数据

load data local inpath '${env:HOME}/california-employees' 
overwrite into table employees
patition (country='US', state='CA')

local 关键字说明指定的目录是本地的，如果没有local 关键字则应该是分布式文件系统中的路径。
overwrite 关键字说明先删除原先存在的数据，没有这个关键字则不删除。

通过查询语句向表中插入数据

insert overwrite table employees
partition (country = 'US', state = 'OR')
select * from staged_employees se
where se.cnty = 'US' and se.st = 'OR'

如果需要多种对应关系，无需写很多遍上面的语句，按如下格式插入数据效率会高（只需要扫描一遍）：

from staged_employees se
insert overwrite table employees
    partition (country = 'US', state = 'OR')
    select * where se.cnty = 'US' and se.st = 'OR'
insert overwrite table employees
    partition (country = 'US', state = 'CA')
    select * where se.cnty = 'US' and se.st = 'CA'
insert overwrite table employees
    partition (country = 'US', state = 'IL')
    select * where se.cnty = 'US' and se.st = 'IL'

动态分区插入

insert overwrite table employees
partition (country, state)
select ...,se.cnty,se.st
from staged_employees se;

hive根据select语句中最后2列来确定分区字段country和state的值

混合使用动态和静态分区

insert overwrite table employees
partition (country = 'US',state)
select  ...,se.cnty,se.st
from staged_employees se
where se.cnty = 'US'

country字段为静态，state是动态值，静态分区键必须在动态分区键之前。
动态分区属性

hive.exec.dynamic.partition 设置为true，表示开启动态分区功能

单个查询语句中创建表并加载数据

create table ca_employees
as select name, salary, address
from employees se
where se.state = 'CA'

创建表并载入数据，不能用于外部表。

导出数据

将所有的字段序列化为字符串写入到文件中

insert overwrite local directory '/tmp/ca_employees'
select name, salary, address
from employees
where state = 'CA';

输入到多个文件

from staged_employees se
insert overwrite directory '/tmp/or_employees'
    select * where se.cty = 'US' and se.st = 'OR'
insert overwrite directory '/tmp/ca_employees'
    select * where se.cty = 'US' and se.st = 'CA'
insert overwrite directory '/tmp/il_employees'
    select * where se.cty = 'US' and se.st = 'IL'

视图

Hive先执行这个视图，然后使用这个结果进行余下后续的查询

create view if not exists shipments(time, part)
comment 'time and parts for shipments。'
tblproperties ('creator' = 'me')
as
select ...;

if not exists和comment是可选子句。

• 复制视图create view shipments2 like shipments;
• 删除视图drop view if exists shipments
• 显示视图清单show tables
• 视图不能作为insert语句和load命令的目标表
• 视图是只读的

HiveQL 查询

select...from 语句

select e.name,e.salary
from employees e;

查询集合数据

查询数组

注：集合的字符串元素是加上引号的，而基本数据类型string的列值是不加引号的。

select name,subordinates
from employees;
---
John Doe        ["Mary Smith","Todd Jones"]
Mary Smith      ["Bill King"]
Todd Jones      [""]
Bill King       [""]

查询Map

select name,deductions
from employees;
---
John Doe        {"Federal":0.2,"State":0.05,"Insurance":0.1}
Mary Smith      {"Federal":0.2,"State":0.05,"Insurance":0.1}
Todd Jones      {"Federal":0.15,"State":0.03,"Insurance":0.1}
Bill King       {"Federal":0.15,"State":0.03,"Insurance":0.1}        

查询Struct

select name,address
from employees;
---
John Doe        {"street":"1 Michigan Ave.","City":"Chicago","state":"IL","zip":60600}
Mary Smith      {"street":"30 Michigan Ave.","City":"Chicago","state":"IL","zip":60600}
Todd Jones      {"street":"20 Michigan Ave.","City":"Chicago","state":"IL","zip":60600}
Bill King       {"street":"8 Michigan Ave.","City":"Chicago","state":"IL","zip":60600}   

引用集合数据类型中的元素

选取数组元素

注：string数据类型不再加引号

select name,subordinates[0]
from employees;
---
John Doe        Mary Smith
Mary Smith      Bill King
Todd Jones      NULL
Bill King       NULL

引用map元素

select name,deductions["State"]
from employees;
---
John Doe        0.05
Mary Smith      0.05
Todd Jones      0.03
Bill King       0.03  

引用struct的元素，使用“点”符号

select name,address.city
from employees;
---
John Doe        Chicago
Mary Smith      Chicago
Todd Jones      Oak Park
Bill King       Obscuria   

使用正则表达式制定列

select symbol,`price.*`
from stocks;
---
AAPL    195.69    197.88    194.0
AAPL    195.69    197.88    194.0
AAPL    195.69    197.88    194.0

使用列值进行计算

select upper(name),salary,deductions["Federal"]
    ,round(salary * (1-deductions["Federal"]))
from employees;
---
JOHN DOE    10000.0    0.2    8000
MARY SMITH  8000.0     0.2    6400
JOHN DOE    7000.0     0.15   5950

算数运算符

注：

1. 两种类型数据计算：值范围较小的数据类型将转换为范围更广的数据类型。
2. 需要注意数据溢出问题，数据溢出的计算结果不会自动转换为更广泛的数据类型

使用函数

数学函数

聚合函数

对多行进行计算，返回一个结果值

注：目前不允许在一个查询语句中使用多于一个的函数（distinct ...）表达式

表生成函数

将单列拓展成多列或者多行

注：explode不能和其他字段一起使用，比如select name,explode(map) from ee

Hive函数之explode和inline

其他内置函数

时间函数

LIMIT语句

select  upper(name),salary,deductions["Federal"]
    ,round(salary * (1 - deductions["Federal"]))
from employees
limit 2;

列别名

select  upper(name),salary,deductions["Federal"] as fed_taxes
    ,round(salary * (1 - deductions["Federal"])) as salary_minus_fed_taxes
from employees
limit 2;

嵌套select语句

from (
select  upper(name),salary,deductions["Federal"] as fed_taxes
    ,round(salary * (1 - deductions["Federal"])) as salary_minus_fed_taxes
from employees
) e
select e.name,e.salary_minus_fed_taxes
where e.salary_minus_fed_taxes > 70000;

case...when...then...end 语句

select name,salary
    case
        when salary < 5000 then 'low'
        when salary < 7000 then 'middle'
        else 'high'
    end as bracket
from employees

where语句

select  upper(name),salary,deductions["Federal"] as fed_taxes
    ,round(salary * (1 - deductions["Federal"])) as salary_minus_fed_taxes
from employees
where round(salary * (1 - deductions["Federal"])) > 7000 //不可使用列别名

谓语操作符

注：一般来说A和B中任一为null，则返回null

关于浮点数比较

select *
from employees
where deductions['Federal'] > 0.2;
---
john 0.2
boss 0.3

为什么会返回0.2呢，这是因为用户写的浮点数0.2，hive会默认保存为double类型“0.200000000001”，而deductions是float类型的，将隐式转换为double类型“0.200000100000”
如何避免呢：可以显示的指出0.2是float类型

select *
from employees
where deductions['Federal'] > cast(0.2 as float);
---
boss 0.3

Group by 语句

group by语句通常和聚合函数一起使用，对分组进行过滤

select year(ymd),avg(price_close)
from stocks
group by year(ymd)
having avg(price_close) > 50;

join 语句

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 = 'AAPL'

注：sql不支持“非等值连接”，也不支持在on子句中使用or。

join优化

当连接3个或更多表时，Hive会嘉定最后一个表时最大的表，然后尝试将其它表缓存起来，然后扫面最后那个表进行计算。
可以显示的告诉Hive哪张表是达标：

select /*+STREAMTABLE(s)+*/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 = 'AAPL'

其他join

• left outer join
• outer join
• right outer join
• full outer join

注：where语句在连接操作执行后才会执行

left semi-join

比inner join更高效的查询方式，但是只能引用左边表的字段

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; 

排序 order by 和 sort by

order by 全局排序

select s.ymd, s.symbol, s.price_close
from stocks s
order by s.ymd asc ,s.symbol desc

sort by 只在reducer内部排序，如果有多个reducer，则不保证输出的是整体有序的。

select s.ymd, s.symbol, s.price_close
from stocks s
sort by s.ymd asc ,s.symbol desc

使用distribute by来保证相同的数据会分发到同一个reducer中进行处理：

select s.ymd, s.symbol, s.price_close
from stocks s
distribute by s.symbol    --同一个symbol放到同一个reducer中处理
sort by s.symbol, s.ymd desc

如果上面的语句没有要求按ymd 倒序排列，则可以用cluster by简化：

select s.ymd, s.symbol, s.price_close
from stocks s
cluster by s.symbol 

类型转换

cast(value as TYPE)

抽样查询

分桶抽样
分子是要分为几桶，分子是取回的第几个桶，rand()表示随机排序（如果省略rand()，则返回的结果会始终一致）

select *
from numbers tablessample(bucket 1 out of 2 on rand()) s;
---
2
4
6
8
10

数据块抽样

select *
from numbersflat tablesample(0.1 percent) s;

union all 语句

将两个或多个表进行合并，但是要求对应的字段类型必须一致。

分析函数

分析函数用于计算基于组的某种聚合值，它和聚合函数的不同之处是：对于每个组返回多行，而聚合函数对于每个组只返回一行。
基本结构：

分析函数() + over(partition by ** order by ** rows between ** and **)

基础数据.png

SELECT cookieid,createtime,pv,
SUM(pv) OVER(PARTITION BY cookieid ORDER BY createtime) AS pv1, -- 默认为从起点到当前行
SUM(pv) OVER(PARTITION BY cookieid ORDER BY createtime ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS pv2, --从起点到当前行，结果同pv1 
SUM(pv) OVER(PARTITION BY cookieid ORDER BY createtime ROWS BETWEEN 3 PRECEDING AND CURRENT ROW) AS pv3,   --当前行+往前3行
SUM(pv) OVER(PARTITION BY cookieid ORDER BY createtime ROWS BETWEEN 3 PRECEDING AND 1 FOLLOWING) AS pv4,    --当前行+往前3行+往后1行
SUM(pv) OVER(PARTITION BY cookieid ORDER BY createtime ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) AS pv5   ---当前行+往后所有行  
FROM test1;
-----
cookieid    createtime  pv  pv1  pv2    pv3  pv4  pv5
a           2017-12-01  3    3    3      3    3    3
b           2017-12-00  3    3    3      3    3    3
cookie1     2017-12-10  1    1    1      1    6    26
cookie1     2017-12-11  5    6    6      6    13   25
cookie1     2017-12-12  7    13  13      13   16   20
cookie1     2017-12-13  3    16  16      16   18   13
cookie1     2017-12-14  2    18  18      17   21   10
cookie1     2017-12-15  4    22  22      16   20   8
cookie1     2017-12-16  4    26  26      13   13   4
cookie2     2017-12-12  7    7    7      7    13   14
cookie2     2017-12-16  6    13  13      13   14   7
cookie2     2017-12-24  1    14  14      14   14   1
cookie3     2017-12-22  5    5    5      5     5   5

行数控制（window子句）：

• preceding：往前
• FOLLOWING：往后
  CURRENT ROW：当前行
  UNBOUNDED：无边界，UNBOUNDED PRECEDING 表示从最前面的起点开始， UNBOUNDED FOLLOWING：表示到最后面的终点

开窗函数

sum

sum()计算的是分区内排序后一个个叠加的值，和order by有关；如果没有order by，不仅分区内没有排序，sum()计算的pv也是整个分区的pv

select cookieid,createtime,pv,
    sum(pv) over(PARTITION BY cookieid ORDER BY createtime) as pv1 
FROM test1

max

返回最大值

min

返回最小值

ntile

NTILE(n)，用于将分组数据按照顺序切分成n片，返回当前切片值
注1：如果切片不均匀，默认增加第一个切片的分布
注2：NTILE不支持ROWS BETWEEN

SELECT cookieid,createtime,pv,
NTILE(2) OVER(ORDER BY createtime) AS ntile1, --分组内将数据分成2片
NTILE(3) OVER(ORDER BY createtime) AS ntile2,  --分组内将数据分成3片
NTILE(4) OVER(ORDER BY createtime) AS ntile3   --将所有数据分成4片
FROM test1 

row_number

row_number() 从1开始，按照顺序，生成分组内记录的序列
类似：

• rank() 生成数据项在分组中的排名，排名相等会在名次中留下空位
• dense_rank() 生成数据项在分组中的排名，排名相等会在名次中不会留下空位

SELECT cookieid,createtime,pv,
    rank() OVER(PARTITION BY cookieid ORDER BY pv desc) AS rank1,
    dense_rank() OVER(PARTITION BY cookieid ORDER BY pv desc) AS d_rank2,
    row_number() OVER(PARTITION BY cookieid ORDER BY pv DESC) AS rn3 
FROM test1 

cume_dist

返回：

比如，我们可以统计小于等于当前薪水的人数，所占总人数的比例

SELECT cookieid,createtime,pv,
    round(cume_dist() OVER(ORDER BY pv),2) AS cd1,
    round(cume_dist() OVER(PARTITION BY cookieid ORDER BY pv),2) AS cd2  
FROM test1;

类似的：percent_rank，返回：

LAG 和 LEAD

LAG(col,n,DEFAULT) 用于统计窗口内往上第n行值，第一个参数为列名，第二个参数为往上第n行（可选，默认为1），第三个参数为默认值（当往上第n行为NULL时候，取默认值，如不指定，则为NULL）

LEAD 函数则与 LAG 相反： LEAD(col,n,DEFAULT) 用于统计窗口内往下第n行值

SELECT cookieid,createtime,pv,
    ROW_NUMBER() OVER(PARTITION BY cookieid ORDER BY createtime) AS rn,
    LAG(createtime,1,'1970-01-01') OVER(PARTITION BY cookieid ORDER BY createtime) AS lag1,
    LAG(createtime,2) OVER(PARTITION BY cookieid ORDER BY createtime) AS lag2 
FROM test1;

FIRST_VALUE 和 LAST_VALUE

FIRST_VALUE 取分组内排序后，截止到当前行，第一个值;
LAST_VALUE 函数则相反，取分组内排序后，截止到当前行，最后一个值。

SELECT cookieid,createtime,pv,
    ROW_NUMBER() OVER(PARTITION BY cookieid ORDER BY createtime) AS rn,
    FIRST_VALUE(pv) OVER(PARTITION BY cookieid ORDER BY createtime) AS first  
FROM test1;

窗口函数参考文章