Hive基础应用

一.hive基础概念

1.数据模型

分区:按照表之外的虚拟字段将一个大表分成多个小表,例如按照年份进行分割
分桶:按照表中的真实字段的hash值将表进行分割
目的:都是为了提高数据处理的效率

2.数据类型

• 数值类型

  • 

• 日期类型

  • 

• 字符类型

  • 

  • varchar和char的联系

    • 相同点
      • 都是存储字符串的
    • 不同点
      • varchar是变长,char是定长
    • 应用场景
      • varchar一般用于用户名等长度不固定的数据存储
      • char一般用于手机号等定长的数据存储

• 布尔类型

  • 

• 字节数组

  • 

• 复杂类型

  • 

3.数据操作分类

• DDL(数据定义语言)

  • 

• DML(数据操作语言)

  • 

• DQL(数据查询语言)

  • 

二.Hive SQL

1.DDL

• 内表与外表
  • 内表:元数据和数据本身都由hive管理,删除表会将数据和数据本身都删除
  • 外表:元数据由hive管理,数据本身由hdfs管理,删除表只会删除元数据,数据本身不会影响
• 建表模板

CREATE [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]

• 名词解释
  • external 区分内外表的唯一标识
  • partitioned by 分区字段,0-n个
  • clustered by 分桶字段0-n个
  • row format 行分割符号
  • stored as 存储的文件格式txt或者orc
  • location hdfs文件的存储路径,不指定会在hive指定的默认路径中
• 示例

CREATE  TABLE student(
id string comment '学号',
username string comment '姓名',
classid int comment '班级id',
classname string comment '班级名称'
)
comment '学生信息主表'
partitioned by (come_date string comment '按入学年份分区')
ROW FORMAT DELIMITED 
FIELDS TERMINATED BY '\001' 
LINES TERMINATED BY '\n'
STORED AS  textfile;

2.DML

1.加载本地数据

LOAD DATA local INPATH './student.txt' OVERWRITE INTO TABLE student PARTITION (come_date=20170903);

注意:新版的hive已经不支持加载本地数据了

2.加载hdfs数据

LOAD DATA INPATH '/tmp/tianliangedu/input_student_info/student.txt' OVERWRITE INTO TABLE student PARTITION (come_date=20170904);

注意:原始的hdfs文件数据将被move到目标表的数据目录当中，注意源文件目录和目标文件目录的权限。

3.将查询结果插入到数据表中

insert overwrite table student partition(come_date='20170905')
select
id,username,classid,classname 
from student 
where come_date='20170904';

4.多插入模式

一次查询多次插入

from student
insert overwrite table student partition(come_date='20170906') select id,username,classid,classname where come_date='20170905'
insert overwrite table student partition(come_date='20170907') select id,username,classid,classname where come_date='20170905'
insert overwrite table student partition(come_date='20170908') select id,username,classid,classname where come_date='20170905'

5.动态分区模式

让分区成为被查询出来的结果表的字段名称变量
设置非严格模式

set hive.exec.dynamic.partition.mode=nonstric;

insert overwrite table student partition(come_date)  
select id,username,classid,classname,come_date from student_outer

6.将查询结果写入到hdfs目录中

insert overwrite directory "/tmp/output2/" 
select * from student where come_date='20170905';

7.内外表的创建规则

• 如果表数据是外部load过来的，即为不可hive内部自生成，此时的表必须是外表。
• 如果表数据是hive内部可以自生成的，即不需要依赖外部的load数据，则此时的表应该是内表。

3.DQL

1.模板

SELECT [DISTINCT] select_expr, select_expr, ...
FROM table_reference
[WHERE where_condition]
[GROUP BY col_list [HAVING condition]]
[ CLUSTER BY col_list
   | [DISTRIBUTE BY col_list] [SORT BY| ORDER BY col_list]
]
[LIMIT number]

• DISTRIBUTE BY col_list

以指定字段作为key作hash partition,保证相同的key会到同一个reduce去处理。

• Sort By col_list

以指定字段作为单个reduce排序的key，保证单个reduce内的key有序排列输出。

• Order By col_list

只会生成一个reduce任务，对全部排序

• CLUSTER BY col_list
  ol_list

以指定字段作为key做hash partition，保证相同key会到同一个reduce去处理。该命令相当于distributed by col_list和sort by col_list的联合使用。

2.连接查询

inner join
left join
right join
full join

3.union

union all 将所有表数据，完全叠加在一起，不去重。
union 将所有表数据，完全叠加在一起，去重。

4.hive中的一些注意点

• 子查询的时候必须给别名
• Null判断要用is
• 不支持update,delete操作

三.hive系统函数

• 查看所有的函数
  • show functions;
• 函数分类
  • 
• 查看一个函数的说明
  • desc function split;
• 一些常用的函数
  • count() 统计
  • if if(Test Condition, True Value, False Value)
  • COALESCE( value1,value2,… )将参数列表中第1个不为null的值作为最后的值
  • case…when

CASE  [ expression ] 
  WHEN condition1 THEN result1 
  WHEN condition2 THEN result2 
  ... 
  WHEN conditionn THEN resultn 
  ELSE result 
END

• split 将字符串拆分成一个数组
• explode 将一个集合元素，打散成一行一行的组成，即将一行改成多行
• lateral view 与explode联用，形成一张新表