大数据培训sql on hadoopHive背景及应用场景

1、东方国信hadoop培训SQL On Hadoop主要内容Hive背景及应用场景1Hive基本架构2Hive使用方式3HQL查询语句4Hive总结及其他类似开源系统5主要内容Hive背景及应用场景1Hive基本架构2Hive使用方式3HQL查询语句4Hive总结及其他类似开源系统5Hive是什么？(以wordcount为例)SELECT word, COUNT(*) FROM doc LATERAL VIEW explode(split(text, ) lTable as word GROUP BY word;Hive是什么？由facebook开源，最初用于解决海量结构化的日志数据统计问题；E

2、TL（Extraction-Transformation-Loading）工具构建在Hadoop之上的数据仓库；数据计算使用MR，数据存储使用HDFSHive 定义了一种类 SQL 查询语言HQL；类似SQL，但不完全相同 通常用于进行离线数据处理（采用MapReduce）；可认为是一个HQLMR的语言翻译器。Hive典型应用场景 日志分析统计网站一个时间段内的pv、uv多维度数据分析大部分互联网公司使用Hive进行日志分析，包括百度、淘宝等 其他场景海量结构化数据离线分析低成本进行数据分析（不直接编写MR）为什么使用Hive？ 简单，容易上手提供了类SQL查询语言HQL；为超大数据集设计的计

3、算/扩展能力MR作为计算引擎，HDFS作为存储系统统一的元数据管理（HCalalog）可与Pig、Presto等共享 有了Hive，还需要自己写MR程序吗？ Hive的HQL表达的能力有限迭代式算法无法表达有些复杂运算用HQL不易表达 Hive效率较低Hive自动生成MapReduce作业，通常不够智能；HQL调优困难，粒度较粗可控性差主要内容Hive背景及应用场景1Hive基本架构2Hive使用方式3HQL查询语句4Hive总结及其他类似开源系统5Hive基本架构Hive各模块组成用户接口包括 CLI，JDBC/ODBC，WebUI元数据存储（metastore）默认存储在自带的数据库der

4、by中，线上使用时一般换为MySQL驱动器（Driver）解释器、编译器、优化器、执行器Hadoop用 MapReduce 进行计算，用 HDFS 进行存储Hive部署架构-实验环境Hive部署架构-生产环境主要内容Hive背景及应用场景1Hive基本架构2Hive使用方式3HQL查询语句4Hive总结及其他类似开源系统5CLI（Command Line Interface）usage: hive -d,-define Variable substitution to apply to hive commands. e.g. -d A=B or -define A=B -e SQL from

5、command line -f SQL from files -H,-help Print help information -h Connecting to Hive Server on remote host -hiveconf Use value for given property -hivevar Variable substitution to apply to hive commands. e.g. -hivevar A=B -i Initialization SQL file -p Connecting to Hive Server on port number -S,-sil

6、ent Silent mode in interactive shell -v,-verbose Verbose mode (echo executed SQL to the console)$HIVE_HOME/bin/hive -helpCLIhive外部资源外部资源：HQL运行时需要的jar包、二进制文件、文本文件、压缩文件等外部资源需分发到集群的各个节点上使用三种外部资源：FILE：普通文件，Hadoop不会进行任何处理JAR：jar包，Hadoop自动将其加入CLASSPATH中ARCHIVE：归档文件，Hadoop可识别“.tgz”、“.tar.gz”、“.zip”等结尾的文件，并

7、自动解压CLIhive外部资源三种操作：ADD FILES | JARS | ARCHIVES * LIST FILES | JARS | ARCHIVES . DELETE FILES | JARS | ARCHIVES . Hive Web UIHive客户端程序方法1：提供JDBC/ODBC访问方式方法2：采用开源软件Thirft实现C/S模型支持任何语言编写客户端程序主要内容Hive背景及应用场景1Hive基本架构2Hive使用方式3HQL查询语句4Hive总结及其他类似开源系统5数据模型数据类型（不断增加中）类型大小举例TINYINT1 byte 有符号整型20SMALLINT2 b

8、yte 有符号整型20INT4 byte 有符号整型20BIGINT8 byte 有符号整型20BOOLEAN布尔类型trueFLOAT单精度浮点型3.14159DOUBLE双度浮点型3.14159STRING(CHAR, VARCHAR)字符串Now is the time, “for all”TIMESTAMP（Date）整型、浮点型或字符串1327882394 (Unix epoch seconds),1327882394.123456789 (Unix epochseconds plus nanoseconds),BINARY 字节数组数据类型类型解释举例STRUCT与C/C+中的结构

9、体类似，可通过“.”访问每个域的值，比如STRUCT first STRING; last STRING，可通过name.first访问第一个成员。struct(John, Doe)MAP存储key/value对，可通过key获取每个key的值，比如firstJohn and lastDoe,可通过namelast获取last name。map(first, John,last, Doe)ARRAY同种类型的数据集合，从0开始索引，比如John, Doe,，可通过name1获取“Doe”。array(John, Doe)数据定义语句（DDL）Create/Drop/Alter Database

10、Create/Drop/Truncate TableAlter Table/Partition/ColumnCreate/Drop/Alter ViewCreate/Drop/Alter IndexCreate/Drop FunctionCreate/Drop/Grant/Revoke Roles and PrivilegesShowDescribe数据定义语句（DDL）CREATE EXTERNAL TABLE IF NOT EXISTS table_name (col_name data_type, .)PARTITIONED BY (col_name data_type, .)CLUST

11、ERED BY (col_name, col_name, .) SORTED BY (col_name ASC|DESC, .) INTO num_buckets BUCKETSSKEWED BY (col_name, col_name, .) ROW FORMAT row_format STORED AS file_format LOCATION hdfs_path数据定义语句（DDL）ROW FORMAT DELIMITED：保留关键字FIELDS TERMINATED BY：列分隔符COLLECTION ITEMS TERMINATED BY ：元素间分隔符MAP KEYS TERMIN

12、ATED BY：key/value对间的分隔符LINES TERMINATED BY：行分隔符 Hive数据格式由用户自定义默认是文本文件文本文件，用户需显示指定分隔符其他格式SequenceFileAvroORC Files用户自定义支持数据压缩Bzip、GzipLZOSnappy数据定义语句（DDL）分隔符解释n记录间的分割符，默认一行一条记录A (“control” A)列分隔符，通常写成“001”BARRAY或STRUCT中元素分隔符，或MAP中key与value分隔符，通常写成“002”CMAP中key/value对间的分隔符，通常写成“003”John DoeA100000.0AM

13、ary SmithBTodd JonesAFederal TaxesC.2BStateTaxesC.05BInsuranceC.1A1 Michigan Ave.BChicagoBILB60600Mary SmithA80000.0ABill KingAFederal TaxesC.2BState TaxesC.05BInsuranceC.1A100 Ontario St.BChicagoBILB60601Todd JonesA70000.0AFederal TaxesC.15BState TaxesC.03BInsuranceC.1A200 Chicago Ave.BOak ParkBILB

14、60700Bill KingA60000.0AFederal TaxesC.15BState TaxesC.03BInsuranceC.1A300 Obscure Dr.BObscuriaBILB60100应用举例1表定义如下：CREATE TABLE logs( domain_id INT , log_time STRING , log_date STRING, log_type INT, uin BIGINT ) ROW FORMAT DELIMITED FIELDS TERMINATED BY , LOCATION /user/hivetest/logs;某网站日志格式如下：应用举例1h

15、adoop fs -put ./20130301.small /user/hivetest/logs/small1hadoop fs -put ./20130302.small /user/hivetest/logs/small2select log_time.* from logs ;应用举例2：map妙用（日志分析）（1）创建表：create table if not exists result(log map)row format delimitedcollection items terminated by , map keys terminated by = stored as te

16、xtfile（2）使用Hadoop Streaming对数据进行规整化，转化为key/value格式，并加载到Hive表中：load data inpath /home/log/result_hive/* into result（3）统计17：00到18：00日志中的独立ip数select count(distinct logip) from result where logtime 17:00:00 and logtime ADD FILE /home/dongxicheng/split_kv.pl;hive SELECT TRANSFORM (line) USING perl split_

17、kv.pl AS (key, value) FROM kv_data;使用该脚本：用户自定义函数（UDF）UDF：扩展HQL能力的一种方式三种UDF：普通UDF（1对1）UDAF：用户自定义聚集函数（多对1）UDTF：用户自定义产生表函数（1对多）函数相关操作：SHOW FUNCTIONS；DESCRIBE FUNCTION concat；DESCRIBE FUNCTION EXTENDED concat;SELECT concat(column1,column2) AS x FROM table;用户自定义函数（UDF）用户自定义函数（UDF）将函数永久加入 HQL中：修改Hive的函数注册

18、代码重新编译hive代码重新部署修改hive注册代码：找到以下文件：ql/src/java/org/apache/hadoop/hive/ql/exec/FunctionRegistry.java添加新的注册函数myfunc：org.apache.hadoop.hive.contrib.udf.example.UDFZodiacSign;.registerUDF(parse_url, UDFParseUrl.class, false);registerUDF(pzodiac, UDFZodiacSign.class, false);用户自定义函数（UDF）编写复杂的UDF、UDAF和UDTF：

19、继承类GenericUDF实现initialize、evaluate等函数相关示例：UDAF： UDTF： Hive常见调优方法使用Explain查看执行计划设置合理的Reduce Task数目Join优化善用本地模式并行执行多个stage利用虚拟列排错设置合理的Reduce Task数目设置每个Reduce Task处理数据量（默认是1GB）：hive.exec.reducers.bytes.per.reducer设置Reduce Task个数：mapred.reduce.tasksJoin调优大表与小表连接时，使用map-side join；多表连接时，将最大表写在最后面多表连接时，让同一

20、个表的字段出现在所有ON条件中（只会转化为一个MR作业）：SELECT a.val, b.val, c.val FROM a JOIN b ON (a.key = b.key1) JOIN c ON (c.key = b.key1) 善用本地模式输入数据量非常小时，可直接在本地执行：节省任务调度、启动等时间开销减小作业运行延迟在conf/hive-site.xml中增加以下配置：并行执行多个Stage一个HQL可能被转化为多个MapReduce作业默认情况下，每次只执行一个作业（Stage）增加以下配置，允许每次执行多个作业利用虚拟列排错Hive提供了两个虚拟列：数据记录所在文件：INPUT_

21、FILE_NAME数据块在文件中的偏移量：BLOCK_OFFSET_INSIDE_FILE数据记录在数据块中的偏移量： ROW_OFFSET_INSIDE_BLOCKHQL分析函数(窗口函数)解决复杂报表统计需求的而引入的函数，它可将数据分组，并基于组产生某些统计值；普通的聚合函数用group by分组，每个分组返回一个统计值；分析函数采用partition by分组，并且每组每行都可以返回一个统计值。基本语法：over(Partition By xxx Order By yyy rows between zzz)HQL分析函数(窗口函数)应用举例；查找上一年度各个销售区域排名前10的员工按区

22、域查找上一年度订单总额占区域订单总额20%以上的客户获取今年各月累计销售额查找上一年度销售最好和最差的产品获取今年各月累计销售额：SELECT month, SUM(amount) month_amount, SUM( SUM(amount) OVER (ORDER BY month ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS cumulative_amountFROM table_nameGROUP BY monthORDER BY month;HQL分析函数(截至0.13版本)LEAD/LAG/FIRST_VALUE/LAST_VALUECOUNT/SUM/MIN/MAX/AVG OVER (PARTITION BY xxx, ORDER BY yyy)RANKROW_NUMBERDENSE_RANKCUME_DISTPERCENT_RANKNTILE其他问题HQL支持索引吗？ HQL执行过程主要是并行地暴力扫描。目前Hive仅支持单表索引，但提供了索