Hive基础与编程

1、企业信息化部企业信息化部把信息化打造成为中国电信企业核心竞争力之一把信息化打造成为中国电信企业核心竞争力之一Hive基础与编程2014年中国电信大数据技术与应用培训2014年06月江西电信大数据支撑团队企业信息化部企业信息化部把信息化打造成为中国电信企业核心竞争力之一把信息化打造成为中国电信企业核心竞争力之一目录1、Hive简介2、Hive开发工具3、Hive的表4、Hive的元数据5、Hive分区6、Hive编程7、HiveQL优化8、HiveSQL与SQL的比较9、Hive的用户自定义函数企业信息化部企业信息化部把信息化打造成为中国电信企业核心竞争力之一把信息化打造成为中国电信企业核心竞争

2、力之一主题一：Hive简介Hive是什么？Hive是基于Hadoop的一个数据仓库工具；使用hadoop-hdfs作为数据存储层；通过hadoop-mapreduce完成数据计算，提供类似SQL的语言（HQL）；通过HQL语言提供使用者部分传统RDBMS一样的表格查询特性和分布式存储计算特性；其优点是学习成本低，不必开发专门的MapReduce应用，十分适合数据仓库的统计分析。企业信息化部企业信息化部把信息化打造成为中国电信企业核心竞争力之一把信息化打造成为中国电信企业核心竞争力之一主题一：Hive简介Hive不是什么Hive运算基于Hadoop的MapReduce模型，MapReduce不能

3、保证低延迟。因此，hive的查询也不能保证低延迟。Hive的工作模式是：提交一个任务，等到任务结束时被通知，而不是实时查询。相对应的是，类似于Oracle这样的系统当运行小数据集的时候，响应非常快，可当处理的数据集非常大的时候，可能需要数小时。需要说明的是，hive即使在很小的数据集上运行，也可能需要数分钟才能完成。总之，低延迟不是hive追求的首要目标。Hive的设计目标是：可伸缩、可扩展、容错及输入格式松耦合。企业信息化部企业信息化部把信息化打造成为中国电信企业核心竞争力之一把信息化打造成为中国电信企业核心竞争力之一主题一：Hive简介Hive与传统数据库的异同查询语言HQLSQL数据存储

4、位置HDFSRaw Device（裸设备）或者Local FS数据格式用户定义系统决定数据更新不支持支持索引有有执行MapReduceExecutor分区支持支持执行延迟高低可扩展性高低数据规模大小数据检查读时模式写时模式企业信息化部企业信息化部把信息化打造成为中国电信企业核心竞争力之一把信息化打造成为中国电信企业核心竞争力之一主题一：Hive简介Hive架构主要分为以下几个部分：用户接口主要有三个：CLI，Client 和 WUI元数据存储Hive 将元数据存储在数据库中，如 oracle、mysql、derby等解释器、编译器、优化器、执行器完成 HQL 查询语句从词法分析、语法分析、编译

5、、优化以及查询计划的生成HadoopHive 的数据存储在 HDFS 中，大部分的查询由 MapReduce 完成企业信息化部企业信息化部把信息化打造成为中国电信企业核心竞争力之一把信息化打造成为中国电信企业核心竞争力之一主题一：Hive简介Hive开发语言Hive提供了丰富的SQL查询方式来分析存储在Hadoop 分布式文件系统中的数据，通常称之为：HQL不支持：INSERTVALUES、UPDATE和DELETE等操作BEGIN、COMMIT和ROLLBACK等操作企业信息化部企业信息化部把信息化打造成为中国电信企业核心竞争力之一把信息化打造成为中国电信企业核心竞争力之一主题二：Hive开

6、发工具命令行接口方式Web接口方式Thrift Server方式企业信息化部企业信息化部把信息化打造成为中国电信企业核心竞争力之一把信息化打造成为中国电信企业核心竞争力之一主题二：Hive开发工具命令行接口方式示例HQL语句：select a.acc_nbr from tb_mid_serv_day a$HIVE_HOME/bin/hive -e select a.acc_nbr from tb_mid_serv_day a使用静音模式执行HQL$HIVE_HOME/bin/hive -S -e select a.col from tab1 a企业信息化部企业信息化部把信息化打造成为中国电信企

7、业核心竞争力之一把信息化打造成为中国电信企业核心竞争力之一主题二：Hive开发工具Web接口方式Hive的web界面：HWI HWI是Hive Web Interface的简称，是hive cli的一个web替换方案。HWI与CLI对比的优缺点：优点：HWI支持浏览器的方式浏览，方便直观。缺点：无执行过程提示。企业信息化部企业信息化部把信息化打造成为中国电信企业核心竞争力之一把信息化打造成为中国电信企业核心竞争力之一主题二：Hive开发工具Thrift Server方式Toad For CloudCloudera HueEclipse IDESquirrle SQL企业信息化部企业信息化部把信

8、息化打造成为中国电信企业核心竞争力之一把信息化打造成为中国电信企业核心竞争力之一主题三：Hive的表Hive表hive的表逻辑上由存储的数据和描述表格中的数据形式的相关元数据组成。Hive里的表有两种类型一种叫普通表，一种叫外部表。普通表与外部表的区别：（1）创建外部表的时候table之前要加关键字external（2）在做drop操作时（drop是hive删除表的命令），普通表执行drop命令的时候，会删除元数据和存储的数据，而外部表执行drop命令时候只删除元数据库里的数据，而不会删除存储的数据。企业信息化部企业信息化部把信息化打造成为中国电信企业核心竞争力之一把信息化打造成为中国电信企业

9、核心竞争力之一主题三：Hive的表创建Hive表实例创建普通表实例create table normal_table (col1 string,col2 stringcol3 string);创建外部表实例create external table external_table (col1 string,col2 stringcol3 string);企业信息化部企业信息化部把信息化打造成为中国电信企业核心竞争力之一把信息化打造成为中国电信企业核心竞争力之一主题三：Hive的表Hive中创建HBase外部表Hive与HBase利用两者本身对外的API来实现整合。Hive访问HBase中表数据，

10、实质上是通过MapReduce读取HBase表数据。企业信息化部企业信息化部把信息化打造成为中国电信企业核心竞争力之一把信息化打造成为中国电信企业核心竞争力之一主题四：Hive的元数据Hive元数据Hive的数据分为表数据和元数据。元数据的存储Hive将元数据存储在RDBMS中，可以支持Derby,Mysql,Oracle,PostgreSQL,常用为Derby和Mysql数据库存储模式有三种模式可以连接到数据库： 1）single User Mode2）Multi User Mode3）Remote Server Mode企业信息化部企业信息化部把信息化打造成为中国电信企业核心竞争力之一把信

11、息化打造成为中国电信企业核心竞争力之一主题四：Hive的元数据Hive元数据组织结构表名说明关联键TBLS所有hive表的基本信息TBL_ID,SD_IDTABLE_PARAM表级属性，如是否外部表，表注释等TBL_IDCOLUMNSHive表字段信息(字段注释，字段名，字段类型，字段序号)SD_IDSDS所有hive表、表分区所对应的hdfs数据目录和数据格式SD_ID,SERDE_IDSERDE_PARAM序列化反序列化信息，如行分隔符、列分隔符、NULL的表示字符等SERDE_IDPARTITIONSHive表分区信息PART_ID,SD_ID,TBL_IDPARTITION_KEYSH

12、ive分区表分区键TBL_IDPARTITION_KEY_VALSHive表分区名(键值)PART_ID企业信息化部企业信息化部把信息化打造成为中国电信企业核心竞争力之一把信息化打造成为中国电信企业核心竞争力之一主题五：Hive分区Hive分区的概念partition是hive提供的一种机制：用户通过指定一个或多个partition key，决定数据存放方式，进而优化数据的查询。一个表可以指定多个partition key，每个partition在hive中以文件夹的形式存在。企业信息化部企业信息化部把信息化打造成为中国电信企业核心竞争力之一把信息化打造成为中国电信企业核心竞争力之一主题五：H

13、ive分区Hive分区使用说明在其他数据库中，一般向分区表中插入数据时系统会校验数据是否符合该分区，如果不符合会报错。而在hive中，向某个分区中插入什么样的数据完全是由人来控制的，因为分区键是伪列，不实际存储在文件中。如果需要创建有分区的表，需要在create表的时候调用可选参数partitioned by分区表类型：动态分区静态分区企业信息化部企业信息化部把信息化打造成为中国电信企业核心竞争力之一把信息化打造成为中国电信企业核心竞争力之一主题五：Hive分区Hive分区适用场景分区提供了一种便捷的方式去隔离数据和优化查询。但是，并不是所有的数据集都适合去进行分区，需要根据适当的数据集大小合

14、理的进行分区。企业信息化部企业信息化部把信息化打造成为中国电信企业核心竞争力之一把信息化打造成为中国电信企业核心竞争力之一主题五：Hive分区Hive分区使用总结1、减少查询操作处理的数据规模2、优化查询、加快数据的检索速度3、分区对应的就是表下的一个目录企业信息化部企业信息化部把信息化打造成为中国电信企业核心竞争力之一把信息化打造成为中国电信企业核心竞争力之一主题六：Hive编程HiveQL是什么Hive 定义了简单的类 SQL 查询语言，称为 HQL，它允许熟悉 SQL 的用户查询数据。HQL的特点：(1)帮助无MapReduce开发经验的数据分析人员，有能力处理大数据(2)构建标准化的M

15、apReduce开发过程企业信息化部企业信息化部把信息化打造成为中国电信企业核心竞争力之一把信息化打造成为中国电信企业核心竞争力之一主题六：Hive编程Hive基础操作DDL操作（1）建表：建立普通表，建立外部表，建立分区表，复制一个空表（2）修改表结构：增加/删除分区，重命名表，修改列的名字、类型、位置、注释，增加/更新列（3）删除表（4）创建数据库（5）创建删除视图（6）显示命令 ：show tables; show databases; show partitions; show functions;DML操作（1）向数据表内加载文件（2）将查询结果插入到Hive表中Hive的SELEC

16、T操作（1）基本的SELECT操作（2）基于PARTITION的查询企业信息化部企业信息化部把信息化打造成为中国电信企业核心竞争力之一把信息化打造成为中国电信企业核心竞争力之一主题六：Hive编程DDL操作建表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,

17、 col_name, .) SORTED BY (col_name ASC|DESC, .) INTO num_buckets BUCKETS ROW FORMAT row_format STORED AS file_format LOCATION hdfs_path企业信息化部企业信息化部把信息化打造成为中国电信企业核心竞争力之一把信息化打造成为中国电信企业核心竞争力之一主题六：Hive编程DDL操作创建外部表实例CREATE EXTERNAL TABLE SERV_REP(ID INT, CUST_NAME STRING,SEX STRING, AGE STRING,ADDRESS STR

18、ING COMMENT 家庭住址) ROW FORMAT DELIMITED FIELDS TERMINATED BY t STORED AS TEXTFILE;企业信息化部企业信息化部把信息化打造成为中国电信企业核心竞争力之一把信息化打造成为中国电信企业核心竞争力之一主题六：Hive编程DDL操作建分区表CREATE TABLE partition_table(ID INT, CUST_NAME STRING,AGE STRING,SEX STRING,ADDRESS STRING)PARTITIONED BY(DAYID STRING, LANCODE STRING)ROW FORMAT

19、DELIMITEDFIELDS TERMINATED BY t; 企业信息化部企业信息化部把信息化打造成为中国电信企业核心竞争力之一把信息化打造成为中国电信企业核心竞争力之一主题六：Hive编程DDL操作复制一个空表CREATE TABLE empty_cust_repLIKE cust_rep;企业信息化部企业信息化部把信息化打造成为中国电信企业核心竞争力之一把信息化打造成为中国电信企业核心竞争力之一主题六：Hive编程DDL操作删除表DROP TABLE table_name;企业信息化部企业信息化部把信息化打造成为中国电信企业核心竞争力之一把信息化打造成为中国电信企业核心竞争力之一主题六

20、：Hive编程DDL操作增加、删除分区增加ALTER TABLE table_name ADD IF NOT EXISTS PARTITION (partition_col1 = partition_col_value1, partition_col2 = partiton_col_value2, .) LOCATION location_path 删除ALTER TABLE table_name DROP PARTITION (partition_col1 = partition_col_value1, partition_col2 = partiton_col_value2, .)企业信息

21、化部企业信息化部把信息化打造成为中国电信企业核心竞争力之一把信息化打造成为中国电信企业核心竞争力之一主题六：Hive编程DDL操作重命名表ALTER TABLE table_name RENAME TO new_table_name企业信息化部企业信息化部把信息化打造成为中国电信企业核心竞争力之一把信息化打造成为中国电信企业核心竞争力之一主题六：Hive编程DDL操作修改列的名字、类型、位置、注释ALTER TABLE table_name CHANGE COLUMN col_old_name col_new_name column_type COMMENT col_comment FIRST

22、|AFTER column_name企业信息化部企业信息化部把信息化打造成为中国电信企业核心竞争力之一把信息化打造成为中国电信企业核心竞争力之一主题六：Hive编程DDL操作增加/更新列ALTER TABLE table_name ADD|REPLACE COLUMNS (col_name data_type COMMENT col_comment, .) 企业信息化部企业信息化部把信息化打造成为中国电信企业核心竞争力之一把信息化打造成为中国电信企业核心竞争力之一主题六：Hive编程DDL操作创建视图CREATE VIEW IF NOT EXISTS view_name (column_nam

23、e COMMENT column_comment, .) COMMENT view_commentTBLPROPERTIES (property_name = property_value, .) AS SELECT视图是只读的，不能用LOAD/INSERT/ALTER删除视图DROP VIEW view_name企业信息化部企业信息化部把信息化打造成为中国电信企业核心竞争力之一把信息化打造成为中国电信企业核心竞争力之一主题六：Hive编程DDL操作显示命令show tables ct*;show databases;show partitions table_name;show functi

24、ons;企业信息化部企业信息化部把信息化打造成为中国电信企业核心竞争力之一把信息化打造成为中国电信企业核心竞争力之一主题六：Hive编程DML操作向数据表内加载文件LOAD DATA LOCAL INPATH filepath OVERWRITE INTO TABLE tablename PARTITION (partcol1=val1, partcol2=val2 .) 企业信息化部企业信息化部把信息化打造成为中国电信企业核心竞争力之一把信息化打造成为中国电信企业核心竞争力之一主题六：Hive编程DML操作将查询结果插入Hive表基本模式INSERT OVERWRITE TABLE tabl

25、ename1 PARTITION (partcol1=val1, partcol2=val2 .) select_statement1 FROM from_statement多插入模式FROM from_statementINSERT OVERWRITE TABLE tablename1 PARTITION (partcol1=val1, partcol2=val2 .) select_statement1INSERT OVERWRITE TABLE tablename2 PARTITION . select_statement2 .自动分区模式INSERT OVERWRITE TABLE t

26、ablename PARTITION (partcol1=val1, partcol2=val2 .) select_statement FROM from_statement企业信息化部企业信息化部把信息化打造成为中国电信企业核心竞争力之一把信息化打造成为中国电信企业核心竞争力之一主题六：Hive编程DML操作将查询结果写入HDFS文件系统INSERT OVERWRITE LOCAL DIRECTORY directory1 SELECT . FROM . FROM from_statement数据写入文件系统时进行文本序列化，且每列用A 来区分，n换行企业信息化部企业信息化部把信息化打造成

27、为中国电信企业核心竞争力之一把信息化打造成为中国电信企业核心竞争力之一主题六：Hive编程DML操作INSERT INTO INSERT INTO TABLE tablename1 PARTITION (partcol1=val1, partcol2=val2 .) select_statement1 FROM from_statement企业信息化部企业信息化部把信息化打造成为中国电信企业核心竞争力之一把信息化打造成为中国电信企业核心竞争力之一主题六：Hive编程查询操作基本的Select 操作SELECT ALL | DISTINCT col1, col2, .FROM table_res

28、tWHERE where_conditionGROUP BY col_list HAVING condition CLUSTER BY col_list | DISTRIBUTE BY col_list SORT BY| ORDER BY col_listLIMIT number企业信息化部企业信息化部把信息化打造成为中国电信企业核心竞争力之一把信息化打造成为中国电信企业核心竞争力之一主题六：Hive编程查询操作基于Partition的查询一般 SELECT 查询会扫描整个表，使用 PARTITIONED BY 子句建表，查询就可以利用分区剪枝（input pruning）的特性企业信息化部企

29、业信息化部把信息化打造成为中国电信企业核心竞争力之一把信息化打造成为中国电信企业核心竞争力之一主题七：HiveQL优化优化策略1.使用Map端join2.使用Map端聚合3.使用Partition减少扫描数据量4.合并小文件5.采用原子化操作6.合理使用union all企业信息化部企业信息化部把信息化打造成为中国电信企业核心竞争力之一把信息化打造成为中国电信企业核心竞争力之一主题七：HiveQL优化join优化join查找操作的基本原则：应该将条目少的表/子查询放在 join 操作符的左边，可以有效减少发生内存溢出错误的几率。企业信息化部企业信息化部把信息化打造成为中国电信企业核心竞争力之一

30、把信息化打造成为中国电信企业核心竞争力之一主题七：HiveQL优化group by 优化数据倾斜聚合优化，设置参数： hive.groupby.skewindata = true hive.map.aggr = true是否在 map 端进行聚合，默认为 True hive.groupby.mapaggr.checkinterval = 100000在 map 端进行聚合操作的条目数目企业信息化部企业信息化部把信息化打造成为中国电信企业核心竞争力之一把信息化打造成为中国电信企业核心竞争力之一主题七：HiveQL优化使用分区合理使用分区具备如下优势：1、减少查询操作处理的数据规模2、优化查询、加

31、快数据的检索速度静态分区：静态分区在加载数据和使用时都需要在sql语句中指定动态分区（需要设置如下参数）：hive.exec.dynamic.partition参数值为true，默认值为false，hive.exec.dynamic.partition.mode=nostrick，默认为strick企业信息化部企业信息化部把信息化打造成为中国电信企业核心竞争力之一把信息化打造成为中国电信企业核心竞争力之一主题七：HiveQL优化尽量尽早地过滤数据尽早过滤数据，可以减少每个阶段的数据量,对于分区表要加分区，同时只选择需要使用到的字段。select . from tb1join tb2on tb1

32、.key = tb2.keywhere tb1.userid10 and tb2.userid10 ) ajoin ( select . from tb2 where dt=20140603 and userid add jar $HIVE_HOME/jar/udf_test.jar;STEP4：创建临时函数:hiveCREATE TEMPORARY FUNCTION add_example AS hive.udf.Add;STEP5：使用UDF函数hiveSELECT add_example(score) FROM scores;STEP6：销毁临时函数hive DROP TEMPORARY FUNCTION add_ex