离线计算系统-第07天hive详解v

1、目录课程大纲（HIVE 增强）3Hive 基本概念41.1.1Hive 简介.21.1.3Hive4为什么使用 Hive4Hive 的特点41.2Hive 架构5架构图5基本组成5各组件的基本功能5Hive 与Hadoop 的关系6Hive 与传统数据库对比Hive 的数据6Hive 基本操作72.2.1DDL 操作.22.1.3创建表7修改表9显示命令112.2DML 操作11Load11Insert13SELECT. Hive Join18Hive S使用进阶21Hive 命令行212.5.2.

2、Hive 参数配置方式23Hive 函数24内置运算符24内置函数24Hive 自定义函数和 Transform24自定义函数类别24UDF 开发实例24Transform 实现25Hive 执行过程实例分析26JOIN26GROUP BY26DISTINCT276. Hive 使用（各种小细节）2字符集27压缩28count(distinct)2子查询28Join 中处理 null 值的语义区别28分号字符29Insert29新增数据29次序30.3 初始值30Hive 优化技巧31HADOOP 计算框架特性317.2 优化的常用概述3

3、17.3 全排序327.3.1 例 1327.3.2 例 23怎样写 exist/in 子句36怎样决定 reducer 个数36合并 MapReduce 操作367.7 Bucket 与 Sling37Partition 优化38JOIN 优化39JOIN 原则39Map Join39数据倾斜40空值数据倾斜40不同数据类型关联产生数据倾斜41大表 Join 的数据偏斜41合并小文件42Group By 优化43Map 端部分聚合：43有数据倾斜的时候进行负载均衡438. Hive 实战44Hive 实战案例 1数据 ETL44需求：44数据示例：44实现步骤：45Hiv

4、e 实战案例 2时长统计47需求：47实现步骤：47Hive 实战案例 3级联求和48需求：48实现步骤48课程大纲（HIVE 增强）学习目标：1、熟练掌握 hive 的使用 2、熟练掌握 hql 的编写 3、理解 hive 的工作原理4、具备 hive 应用实战能力Hive 增强HIVE 基本概念HIVE 架构及运行机制HQL-DDL 基本语法HQL-DML 基本语法HIVE 的 joinHIVE UDF 函数HIVE s基本操作HIVE 参数配置HIVE 自定义函数和TransformHIVE 执行 HQL 的实例分析HIVE 最佳实践HIVE 优化策略HIVE 实战案例 1HIVE 实战

5、案例 2HIVE 实战案例 31. Hive 基本概念1.1 Hive 简介1.1.1HiveHive 是基于 Hadoop 的一个数据仓库工具，可以将结构化的数据文件并提供类 SQL 查询功能。主要用途：用来做离线数据分析，比直接用 mapreduce 开发效率更高为一张数据库表，1.1.2 为什么使用 Hive直接使用 hadoop 所学习成本太高项目周期要求太短MapReduce 实现复杂查询逻辑开发难度太大为什么要使用 Hive操作接口采用类 SQL 语法，提供快速开发的能力。避免了去写MapReduce，减少开发功能扩展很方便。的学习成本。1.1.3 Hive 的特点可扩展Hive

6、可以的扩展集群的规模，一般情况下不需要重启服务。延展性Hive 支持用户自定义函数，用户可以根据自己的需求来实现自己的函数。容错良好的容错性，节点出现问题 SQL 仍可完成执行。1.2 Hive 架构1.2.1 架构图Jobtracker是hadoop1.x中 的 组 件 ， 它 的 功 能 相 当 于 ：Resourcemanager+AppMasterTaskTracker 相当于：Nodemanager+yarnchild1.2.2 基本组成用户接口：包括 CLI、JDBC/ODBC、WebGUI。, derby 中。元数据：通常是在关系数据库如解释器、编译器、优化器、执行器。1.2.3

7、 各组件的基本功能用户接口主要由三个：CLI、JDBC/ODBC 和 WebGUI。其中，CLI 为 s命令行；JDBC/ODBC 是 Hive 的 JAVA 实现，与传统数据库 JDBC 类似；WebGUI 是通过浏览器Hive。元数据：Hive 将元数据在数据库中。Hive 中的元数据包括表的名字，表的列和分区及其属性，表的属性（是否为外部表等），表的数据所在目录等。解释器、编译器、优化器完成 HQL 查询语句从词法分析、语法分析、编译、优化以在 HDFS 中，并在随后有 MapReduce及查询计划的生成。生成的查询计划用执行。调1.3Hive 与 Hadoop 的关系Hive 利用 H

8、DFS数据，利用MapReduce 查询分析数据1.4Hive 与传统数据库对比hive 用于海量数据的离线数据分析总结：hive 具有 sql 数据库的外表，但应用场景完全不同，hive 只适合用来做批量数据统计分析1.5Hive 的数据1、 Hive 中所有的数据都在 HDFS 中，没有专门的数据格式（可支持Text，SequenceFile，ParquetFile，RCFILE 等）SequenceFile 是 hadoop 中的一种文件格式： 文件内容是以序列化的kv 对象来组织的2、只需要在创建表的时候告诉 Hive 数据中的列分隔符和行分隔符，Hive 就可以3、Hive 中包含以

9、下数据模型：DB、Table，External Table，Partition，Bucket。数据。db：在 hdfs 中表现为$hive.metastore.warehouse.dir目录下一个文件夹table：在 hdfs 中表现所属 db 目录下一个文件夹external table：与 table 类似，不过其数据存放位置可以在任意指定路径partition：在 hdfs 中表现为 table 目录下的子目录bucket：在 hdfs 中表现为同一个表目录下根据 hash 散列之后的多个文件1.6 HIVE 的安装部署1.6.1 安装derby 版：1、解压 hive2、解决一下版本不

10、兼容问题：替换 /root/apps/hadoop/share/hadoop/yarn/lib 中的老版本jline为 hive 的 lib 中的jline-2.12.jar缺点：多个地方安装 hive 后，每一个 hive 是拥有一套自己的元数据，大家的库、表就不统一；元数据库1、解压版：2、修改配置文件1.6.2 使用方式Hive 交互 sbin/hiveHive JDBC 服务hive 也可以启动为一个服务器，来对外提供启动方式，（假如是在 hadoop01 上）：启动为前台：bin/hiveserver2启动为：nohup bin/hiveserver2 1/var/log/hives

11、erver.log 2/var/log/hiveserver.err &启动成功后，可以在别的节点上用 beeline 去连接方式（1）hive/bin/beeline回车，进入 beeline令界面输入命令连接 hiveserver2beeline !connect jdbc:hive2/mini1:10000（hadoop01 是 hiveserver2 所启动的那台主机名，端口默认是 10000）方式（2） 或者启动就连接：bin/beeline -u jdbc:hive2:/mini1:10000 -n hadoop接下来就可以做正常 sql 查询了Hive 命令hadoophdp-n

12、ode-02 $ hive-esql示例：bin/hive -e select * from tingting.t_zuoxi2. Hive 基本操作2.1 DDL 操作2.1.1 创建表建表语法CREATE EXTERNAL TABLE IF NOT EXISTS table_name(col_name data_type COMMENTment, .)COMMENTmentPARTITIONED BY (col_name data_type COMMENTCLUSTERED BY (col_name, col_name, .)ment, .)SORTED BY (col_name ASC|

13、DESC, .)ROW FORMAT row_formatSTORED AS file_format LOCATION hdfs_pathO num_buckets BUCKETS说明：1、 CREATE TABLE 创建一个指定名字的表。如果相同名字的表已经存在，则抛出异常；用户可以用 IF NOT EXISTS 选项来忽略这个异常。2、 EXTERNAL 关键字可以让用户创建一个外部表，在建表的同时指定一个指向实际数据的路径（LOCATION），Hive 创建表时，会将数据移动到数据仓库指向的路径；若创建外部表，仅数据所在的路径，不对数据的位置做任何改变。在删除表的时候，表的元数据和数据会

14、被一起删除，而外部表只删除元数据，不删除数据。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, .)用户在建表的时候可以自定

15、义 SerDe 或者使用自带的 SerDe。如果没有指定 ROWFORMAT 或者 ROW FORMAT DELIMITED，将会使用自带的 SerDe。在建表的时候，用户还需要为表指定列，用户在指定表的列的同时也会指定自定义的 SerDe，Hive通过 SerDe 确定表的具体的列的数据。5、 STORED AS SEQUENCEFILE|TEXTFILE|RCFILE如果文件数据是纯文本，可以使用 STORED AS TEXTFILE。如果数据需要压缩，使用 STORED AS SEQUENCEFILE。6、CLUSTERED BY对于每一个表（table）或者分区， Hive 可以进一步

16、组织成桶，也就是说桶是更为细粒度的数据范围划分。Hive 也是 针对某一列进行桶的组织。Hive 采用对列值哈希，然后除以桶的个数求余的方式决定该条存放在哪个桶当中。把表（或者分区）组织成桶（Bucket）有两个理由：（1）获得更高的查询处理效率。桶为表加上了额外的结构，Hive 在处理有些查询时能利用这个结构。具体而言，连接两个在（包含连接列的）相同列上划分了桶的表，可以使用 Map 端连接 （Map-side join）高效的实现。比如 JOIN 操作。对于 JOIN 操作两个表有一个相同的果对这两个表都进行了桶操作。那么将保存相同列值的桶进行 JOIN 操作就可以，可以大大较少 JOIN

17、 的数据量。（2）使取样（sling）更高效。在处理大规模数据集时，在开发和修改查询的阶段，如果能在数据集的一小部分数据上试运行查询，会带来很多方便。具体实例1、 创建表 mytable。2、 创建外部表 pageview。3、 创建分区表 invites。4、 创建带桶的表 student。2.1.2 修改表增加/删除分区语法结构ALTER TABLE table_name ADD IF NOT EXISTS partition_spec LOCATION location1 partition_spec LOCATION location2 .partition_spec: PARTITI

18、ON (partition_col = partition_col_value, partition_col = partiton_col_value, .)删除分区ALTER TABLE table_name DROP partition_spec, partition_spec,.具体实例alter table student_p add partition(part=a) partition(part=b);create table student_p(Sno,Sname string,Sex string,Sage,Sdept string) partitioned by(part s

19、tring) row format delimited fields terminated by ,stored as textfile;重命名表语法结构ALTER TABLE table_name RENAME T具体实例_table_name增加/更新列语法结构ALTER TABLE table_name ADD|REPLACE COLUMNS (col_name data_type COMMENT ment, .)注：ADD 是代表新增一字段，字段位置在所有列后面(partition 列前)，REPLACE 则是表示替换表中所有字段。ALTERTABLEtable_nameCHANGEC

20、OLUMNcol_old_namecol_new_namecolumn_type COMMENT具体实例ment |AFTER column_name2.1.3 显示命令show tables;show databases;shortitions table_name;show functions;desc extended table_name; desc formatted table_name;2.2 DML 操作2.2.1 Load语法结构LOAD DATA LOCAL INPATH filepath OVERWRITEOTABLE tablename PARTITION (partc

21、ol1=val1, partcol2=val2 .)说明：1、 Load 操作只是单纯的/移动操作，将数据文件移动到 Hive 表对应的位置。2、 filepath：相对路径，例如：project/data1绝对路径，例如：/user/hive/project/data1包含模式的完整 URI，：hdfs:/namenode:9000/user/hive/project/data13、 LOCAL 关键字如果指定了 LOCAL， load 命令会去查找本地文件系统中的 filepath。如果没有指定 LOCAL 关键字，则根据 inpath 中的uri 查找文件4、 OVERWRITE 关键字

22、如果使用了OVERWRITE 关键字，则目标表（或者分区）中的内容会被删除，然后再将 filepath 指向的文件/目录中的内容添加到表/分区中。如果目标表（分区）已经有一个文件，并且文件名和么现有的文件会被新文件所替代。中的文件名，那filepath具体实例1、 加载相对路径数据。2、 加载绝对路径数据。3、 加载包含模式数据。4、 OVERWRITE 关键字使用。2.2.2 Insert将查询结果Hive 表语法结构利用查询语句，将查询结果新的表TABLEINSERTOVERWRITEOtablename1PARTITION(partcol1=val1,partcol2=val2 .) s

23、elect_sement1 FROM from_sement一条数据INSERTO TABLE VALUES(XX,YY,ZZ);Multi Inserts 多重FROM from_sementINSERT OVERWRITE TABLE tablename1 PARTITION (partcol1=val1, partcol2=val2 .) select_sement1INSERT OVERWRITE TABLE tablename2 PARTITION .select_sement2 .:Dynamic partition inserts 动态分区:INSERT OVERWRITE TA

24、BLE tablename PARTITION (partcol1=val1, partcol2=val2 .) select_sement FROM from_sement具体实例1、基本模式。2、多模式。3、自动分区模式。导出表数据语法结构INSERT OVERWRITE LOCAL DIRECTORY directory1 SELECT . FROM .multiple inserts:FROM from_sementINSERT OVERWRITE LOCAL DIRECTORY directory1 select_sement1INSERT OVERWRITE LOCAL DIREC

25、TORY directory2 select_sement2 .具体实例1、导出文件到本地。说明：数据写入到文件系统时进行文本序列化，且每列用A 来区分，n 为换行符。用 more 命令查看时不容易看出分割符，可以使用: sed -e s/x01/|/g filename 来查看。2、导出数据到 HDFS。2.2.3SELECT基本的 Select 操作语法结构SELECT ALL | DISTINCT select_expr, select_expr, . FROM table_referenceJOable_other ON exprWHERE where_conditionGROUP B

26、Y col_list HAVING condition CLUSTER BY col_list| DISTRIBUTE BY col_list SORT BY| ORDER BY col_listLIMIT number注：1、order by 会对输入做全局排序，因此只有一个 reducer，会导致当输入规模较大时，需要较长的计算时间。2、sort by 不是全局排序，其在数据进入 reducer 前完成排序。因此，如果用 sort by 进行排序，并且设置 mapred.reduce.tasks1，则 sort by 只保证每个 reducer 的输出有序，不保证全局有序。3、distri

27、bute by(字段)根据指定的字段将数据分到不同的 reducer，且分发算法是 hash 散列。4、Cluster by(字段) 除了具有 Distribute by 的功能外，还会对该字段进行排序。因此，如果分桶和 sort 字段是同一个时，此时，cluster by = distribute by + sort by分桶表的作用：最大的作用是用来提高join 操作的效率；（思考这个问题：select a.id,b.addr from a join b on a.id = b.id;如果 a 表和 b 表已经是分桶表，而且分桶的字段是 id 字段做这个 join 操作时，还需要全表做积吗

28、？）具体实例1、获取大的 3 个学生。2、查询学生信息按，降序排序。3、按学生名称汇总学生。2.3 Hive Join语法结构join_table:table_reference JOable_factor join_condition| table_reference LEFT|RIGHT|FULL OUTER JOable_reference join_condition| table_reference LEFT SEMI JOable_reference join_conditionHive 支持等值连接（equality joins）、外连接（outer joins）和（left/r

29、ight joins）。 Hive 不支持非等值的连接，因为非等值连接非常难转化到 map/reduce 任务。另外，Hive 支持多于 2 个表的连接。写 join 查询时，需要注意几个关键点：1. 只支持等值 join例如：SELECT a.* SELECT a.*ON (a.idFROM a FROM a= b.idJOIN b ON (a.id = b.id) JOIN bAND a.department = b.department)是正确的，然而:SELECT a.* FROM a是错误的。JOIN b ON (a.idb.id)2. 可以 join 多于 2例如个表。SELECT

30、 a.val, b.val, c.val ON (a.key = b.key1) JOIN如果 join 中多个表的 join key例如：FROM a JOIN bc ON (c.key = b.key2)是同一个，则 join 会被转化为单个 map/reduce任务，SELECT a.val,ON (a.key = ON (c.key =b.val, c.valb.key1) JOIN b.key1)FROM a JOIN bc被转化为单个 map/reduce 任务，因为 join 中只使用了 b.key1 作为 join key。SELECTJOIN而这一b.key2a.val, b

31、.val, c ON (c.key =join 被转化为c.val FROM a JOIN b ON (a.key = b.key1) b.key2)2 个 map/reduce 任务。因为 b.key1 用于第一次 join 条件，而用于第二次 join。3join 时，每次 map/reduce 任务的逻辑：reducer 会缓存 join 序列中除了最后一个表的所有表的，再通过最后一个表将结果序列化到文件系统。这一实现有助于在 reduce 端减少内存的使用量。实践中，应该把最大的那个表写在最后（否则会因为缓存浪费大量内存）。例如：SELECT a.val, b.val, c.val F

32、ROM aJOIN b ON (a.key = b.key1) JOIN c ON (c.key = b.key1)所有表都使用同一个 join key（使用 1 次 map/reduce 任务计算）。Reduce 端会缓存 a 表和 b 表的，然后每次取得一个 c 表的就计算一次 join 结果，类似的还有：SELECTJOIN这里用了a.val, b.val, c.val FROM ab2ON (a.key = b.key1) JOIN c ON (c.key = b.key2)次 map/reduce 任务。第一次缓存 a 表，用 b 表序列化；第二次缓存第一次任务的结果，然后用 c 表

33、序列化。map/reduce4LEFT，RIGHT 和 FULL OUTER 关键字用于处理 join 中空的情况例如：SELECT a.val, b.val FROMa LEFT OUTER JOIN b对应所有 a 表中的a.key=b.key 时，而当a.val, NULL所以 a 表中的所有 “a RIGHT OUTER JOINON (a.key=b.key)都有一条输出。输出的结果应该是 a.val, b.val，当b.key 中找不到等值的 a.key时也会输出:都被保留了；b”会保留所有 b 表的。Join 发生在 WHERE 子句之前。如果你想限制 join 的输出，应该在

34、WHERE 子句中写过滤条件或是在 join 子句中写。这里面一个容易SELECT a.val, b.val FROM aLEFT OUTER JOIN b ON (a.key=b.key)WHERE a.ds=2009-07-07 AND b.ds=2009-07-07是表分区的情况：会 join a 表到 b 表（OUTER JOIN），列出 a.val 和 b.val 的。WHERE 从句中可以使用其他列作为过滤条件。但是，如前所述，如果 b 表中找不到对应 a 表的，b 表的所有列都会列出 NULL，包括 ds 列。也就是说，join 会过滤 b 表中不能找到匹配 a 表 join k

35、ey 的所有。这样的话，LEFT OUTER 就使得查询结果与 WHERE 子句无关了。解决的办法是在 OUTER JOIN 时使用以下语法：SELECT a.val, b.val FROM a LEFT OUTER JOIN bON (a.key=b.key AND b.ds=2009-07-07 AND a.ds=2009-07-07)这一查询的结果是预先在 join 阶段过滤过的，所以不会存在上述问题。这一逻辑也可以应用于 RIGHT 和 FULL 类型的 join 中。Join 是不能交换位置的。无论是 LEFT 还是 RIGHT join，都是左连接的。SELECT a.val1,

36、a.val2, b.val, c.valFROM JOIN LEFT先和ab ON (a.key = b.key)OUTER JOIN c ON (a.key = c.key)join a 表到 b 表，丢弃掉所有 join key 中不匹配的，然后用这一中间结果c 表做 join。这一表述有一个不太明显，就是当一个 key 在 a 表和 c 表在第一次 join，即 a JOIN b 的时候都都存在，但是 b 表中不存在的时候：整个被丢掉了（包括 a.val1，a.val2 和 a.key），然后再和 c 表 join 的时候，如果c.key 与 a.key 或 b.key 相等，就会得到这

37、样的结果：NULL,NULL,NULL,c.val具体实例1、 获取已经分配班级的学生。2、 获取尚未分配班级的学生。3、 LEFTSEMIJOIN 是 IN/EXISTS 的高效实现。参数3 Hive S3.1 Hive 命令行补充：如何才能在 yarn 的管理控制台上查看已经运行完成的 job 的信息需要在 hadoop 集群上启动一个jobhistory 服务器linux-smr-jobhistory-daemon.sh start historyserver语法结构hive -hiveconf x=y* * | -S说明：1、-i 从文件初始化 HQL。2、-e 从命令行执行指定的 H

38、QL3、-f 执行 HQL4、-v 输出执行的 HQL 语句到控制台5、-p connect to Hive Server on port number6、-hiveconf x=y Use this to set hive/hadoop configuration variables.具体实例1、运行一个查询。2、运行一个文件。3、运行参数文件。3.2 Hive 参数配置方式Hive 参数大全：开发 Hive 应用时，不可避免地需要设定 Hive 的参数。设定 Hive 的参数可以调优 HQL 代码的执行效率，或帮助定位问题。然而实践中经常遇到的一个问题是，为什么设定的参数没有起作用？这通常

39、是错误的设定方式导致的。对于一般参数，有以下三种设定方式：配置文件 命令行参数参数（全局有效）（对 hive 启动实例有效）（对 hive 的连接 ses有效）配置文件：Hive 的配置文件包括用户自定义配置文件：$HIVE_CONF_DIR/hive-site.xml默认配置文件：$HIVE_CONF_DIR/hive-default.xml用户自定义配置会覆盖默认配置。另外，Hive 也会读入 Hadoop 的配置，因为 Hive 是作为 Hadoop 的客户端启动的，Hive 的配置会覆盖 Hadoop 的配置。配置文件的设定对本机启动的所有 Hive 进程都有效。命令行参数：启动 Hi

40、ve（客户端或 Server 方式）时，可以在命令行添加-hiveconf param=value来设定参数，例如：bin/hive -hiveconf hive.root.logger=INFO,console这一设定对本次启动的 Ses（对于 Server 方式启动，则是所有请求的 Sess）有效。参数：可以在 HQL 中使用 SET 关键字设定参数，例如：setmapred.reduce.tasks=100;这一设定的作用域也是 ses级的。set hive.exec.reducers.bytes.per.reducer=每个 reduce task 的平均负载数据量hive 会估算ta

41、sk 数的总数据量，然后用总数据量除以上述参数值，就能得出需要运行的 reduceset hive.exec.reducers.max=设置reduce task 数量的上限=指定固定的 reduce task 数量set mapreduce.job.redu但是，这个参数在必要时 hive 会忽略上述三种设定方式的优先级依次递增。即参数覆盖命令行参数，命令行参数覆盖配置文件设定。注意某些系统级的参数，例如 log4j 相关的设定，必须用前两种方式设定，因为那些参数的在 Ses建立以前已经完成了。4. Hive 函数4.1 内置运算符内容较多，见Hive文档4.2 内置函数内容较多，见Hive

42、文档测试各种内置函数的快捷方法：1、创建一个 dual 表create table dual(id string);2、load 一个文件（一行，一个空格）到dual 表3、select substr(angelababy,2,3) from dual;4.3 Hive 自定义函数和 Transform当 Hive 提供的内置函数你的业务处理需要时，此时就可以考虑使用用户自定义函数（UDF：user-defined function）。4.3.1 自定义函数类别UDF作用于单个数据行，产生一个数据行作为输出。（数学函数，字符串函数）UDAF（用户定义函数）：接收多个输入数据行，并产生一个输出数

43、据行。（count，max）4.3.2 UDF 开发实例简单 UDF 示例1、先开发一个java 类，继承 UDF，并重载 evaluate 方法2、打成 jar 包上传到服务器3、将 jar 包添加到 hive 的classpath hiveadd JAR /home/hadoop/udf.jar;4、创建临时函数与开发好的 java class 关联5、即可在 hql 中使用自定义的函数 tolowercase ipSelect tolowercase(name),age from t_test;Json 数据UDF 开发作业：有原始 json 数据如下：需要将数据导入到 hive 数据仓

44、库中我不管你中间用几个表，最终我要得到一个结果表：注：全在 hive 中完成，可以用自定义函数movieratetimestuid119739783022681movie:1193,rate:5,timeSt:978300760,uid:1movie:661,rate:3,timeSt:978302109,uid:1movie:914,rate:3,timeSt:978301968,uid:1movie:3408,rate:4,timeSt:978300275,uid:1movie:2355,rate:5,timeSt:978824291,uid:1movie:1197,rate:3,time

45、St:978302268,uid:1movie:1287,rate:5,timeSt:978302039,uid:1Hivecreate temporary function tolowercase as cn.bigdata.udf.ToProvince;package cn.bigdata.udfimport .apache.hadoop.hive.ql.exec.UDF; import.apache.hadoop.io.Text;public final class Lower extends UDF public Text evaluate(final Text s)if(s=null

46、)return null;return new Text(s.toString().toLowerCase();4.3.3 Transform 实现Hive 的 TRANSFORM 关键字提供了在 SQL 中调用自写适合实现 Hive 中没有的功能又不想写 UDF 的情况的功能使用示例 1：下面这句 sql 就是借用了y 对数据进行了处理.kday_mapp其中kday_mappy 内容如下4.4 HIVE 特殊分隔符处理补充：hive数据的机制：1、 首先用 InputFormat的一个具体实现类读入文件数据，返回一条一条的（可以是行，或者是你逻辑中的“行”）#!/bin/import sy

47、simport datetimefor line in sys.stdin: line = line.strip()movieid, rating, unixtime,userid = line.split(t)kday = datetime.datetime.fromtimest(float(unixtime).isokday() prt.join(movieid, rating, str(kday),userid)CREATE TABLE u_data_new ( movieidratingkdayuserid)ROW FORMAT DELIMITED FIELDS TERMINATED

48、BY t;add FILEkday_mappy;INSERT OVERWRITE TABLE u_data_new SELECTTRANSFORM (movieid , rate, timestring,uid) USING kday_mappyAS (movieid, rating,kday,userid) FROM t_rating;2、 然后利用 SerDe的一个具体实现类，对上面返回的一条一条的进行字段切割Hive 对文件中字段的分隔符默认情况下只支持单字节分隔符，如果数据文件中的分隔符是多字符的，如下所示：01|zhangsan02|lisi可用以下方式处理：4.4.1 使用 Reg

49、exSerDe 通过正则表达式来抽取字段4.4.2 通过自定义 InputFormat 解决特殊分隔符问题其原理是在 inputformat行的时候将数据中的“多字节分隔符”替换为 hive 默认的分隔符（ctrl+A 亦即 001）或用于替代的单字符分隔符，以便 hive 在 serde 操作时按照默认的单字节分隔符进行字段抽取package cn.bigdata.hive.inputformat; import java.io.IOException;import.apache.hadoop.io.LongWritable; import.apache.hadoop.io.Text;imp

50、ort.apache.hadoop.mapred.FileSplit; import.apache.hadoop.mapred.InputSplit; import.apache.hadoop.mapred.JobConf;import.apache.hadoop.mapred.LineRecordReader; import.apache.hadoop.mapred.RecordReader; import.apache.hadoop.mapred.Reporter;import.apache.hadoop.mapred.TextInputFormat;public class BiDeli

51、miterInputFormat extends TextInputFormat drop table t_bi_reg;create table t_bi_reg(id string,name string)row format serde .apache.hadoop.hive.serde2.RegexSerDe with serdeproperties(input.regex=(.*)|(.*),output.format.string=%1$s %2$s)stored as textfile;hiveload data local inpath /root/hivedata/bi.da

52、to table t_bi_reg; hiveselect * from t_bi_reg;Overridepublic RecordReader getRecordReader( InputSplit genericSplit, JobConf job, Reporter reporter) throws IOException reporter.setS us(genericSplit.toString();MyDemoRecordReader reader = new MyDemoRecordReader( new LineRecordReader(job, (FileSplit) ge

53、nericSplit);return reader;public s ic class MyDemoRecordReader implements RecordReader LineRecordReader reader; Text text;public MyDemoRecordReader(LineRecordReader reader) this.reader = reader;text = reader.createValue();Overridepublic void close() throws IOException reader.close();Overridepublic L

54、ongWritable createKey() return reader.createKey();Overridepublic Text createValue() return new Text();Overridepublic long get() throws IOException return reader.get();Overridepublic float getProgress() throws IOException return reader.getProgress();Overridepublicnext(LongWritable key, Text value) th

55、rows IOException while (reader.next(key, text) String strReplace = text.toString().toLowerCase().replaceAll(|, |); Text txtReplace = new Text();txtReplaet(strReplace);value.set(txtReplace.getBytes(), 0, txtReplace.getLength();注意：上述代码中的 api 全部使用 hadoop 的老 api 接口.apache.hadoop.mapred.然后将工程打包，并拷贝至 hive

56、 安装目录的 lib 文件夹中，并重启 hive，使用以下语句建表即可：注：还需要在 hive 中使用 add jar，才能在执行 hql 查询该表时把自定义 jar 包传递给 maptaskhiveadd jar /root/apps/hive/lib/myinput.jar5. Hive 实战Hive 实战案例 1数据 ETL需求：对 web 点击流日志基础数据表进行 etl（按照仓库模型设计）按各时间维度统计来源top10已有数据表 “t_in_weblog” ：+ +|col_name| data_type | comment |+ +hive create table t_bi(id

57、 string,name string)row format delimitedfields terminated by |stored asinputformatcn.bigdata.hive.inputformat.BiDelimiterInputFormat outputformat .apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat;hive load data local inpath /root/hivedata/bi.dato table t_bi; hiveselect * from t_bi;OKzhangsanli

58、sireturn true;return false;数据示例：实现步骤：1、对原始数据进行抽取转换-将来访 url 分离出 hostpathqueryquery id3、从前述步骤进一步分离出日期时间形成 ETL 明细表“t_etl_detail”day tmdrop table if exists t_etl_detail; create table t_etl_detail asselect b.*,substring(time_local,0,11) as daystr,substring(time_local,13) as tmstr,drop table if exists t_e

59、tl_referurl; create table t_etl_referurl as SELECT a.*,b.*FROM t_in_weblog a LATERAL VIEW parse_url_tuple(regexp_replace(http_referer, , ), HOST, PATH,QUERY, QUERY:id) b as host, path, query, query_id| true|34| - | 18/Sep/2013:13:47:35| /images/my.jpg| 200| 19939 | http:/A0d9| Mozilla/5.0 (Windows|

60、true|7 | - | 18/Sep/2013:15:39:11| /wp-content/uploads/2013/08/windjs.png| 200| 34613 | odejs./topic/521a30d4bee8d3cb1272ac0f | Mozilla/5.0 (Mac osh;| valid| string| remote_addr| string| remote_user| string| time_local| string| request| string| sus| string| body_bytes_sent | string| http_referer| st