Hadoop基础教程-第12章 Hive:进阶(12.1 内置函数)(草稿)
来源:互联网 发布:npt内螺纹软件 编辑:程序博客网 时间:2024/05/16 15:17
第12章 Hive:进阶
12.1 内置函数
为了方便测试Hive的内置函数,需要构造一个类似于Oracle的dual虚表
hive> create table dual(value string);OKTime taken: 0.117 secondshive>
hive> insert into dual values("test");WARNING: Hive-on-MR is deprecated in Hive 2 and may not be available in the future versions. Consider using a different execution engine (i.e. spark, tez) or using Hive 1.X releases.Query ID = root_20170820093018_106fdbe1-3d77-4fbb-b200-3b3d56007858Total jobs = 3Launching Job 1 out of 3Number of reduce tasks is set to 0 since there's no reduce operatorStarting Job = job_1503220733636_0016, Tracking URL = http://node1:8088/proxy/application_1503220733636_0016/Kill Command = /opt/hadoop-2.7.3/bin/hadoop job -kill job_1503220733636_0016Hadoop job information for Stage-1: number of mappers: 1; number of reducers: 02017-08-20 09:30:34,522 Stage-1 map = 0%, reduce = 0%2017-08-20 09:30:44,205 Stage-1 map = 100%, reduce = 0%, Cumulative CPU 1.76 secMapReduce Total cumulative CPU time: 1 seconds 760 msecEnded Job = job_1503220733636_0016Stage-4 is selected by condition resolver.Stage-3 is filtered out by condition resolver.Stage-5 is filtered out by condition resolver.Moving data to directory hdfs://cetc/user/hive/warehouse/dual/.hive-staging_hive_2017-08-20_09-30-18_395_4589036656384871958-1/-ext-10000Loading data to table default.dualMapReduce Jobs Launched: Stage-Stage-1: Map: 1 Cumulative CPU: 1.76 sec HDFS Read: 3674 HDFS Write: 73 SUCCESSTotal MapReduce CPU Time Spent: 1 seconds 760 msecOKTime taken: 28.658 secondshive>
hive> select 1+1 from dual;OK2Time taken: 0.176 seconds, Fetched: 1 row(s)hive> select 7%3 from dual;OK1Time taken: 0.212 seconds, Fetched: 1 row(s)hive>
12.1.1 标准函数
(1)日期函数
hive> select to_date('2016-08-31 08:30:00') from dual;OK2016-08-31Time taken: 0.129 seconds, Fetched: 1 row(s) hive> select year('2016-08-31 08:30:00') from dual;OK2016Time taken: 0.207 seconds, Fetched: 1 row(s)hive> select month('2016-08-31 08:30:00') from dual;OK8Time taken: 0.156 seconds, Fetched: 1 row(s)hive>
(2)数学函数
hive> select sqrt(2) from dual;OK1.4142135623730951Time taken: 0.17 seconds, Fetched: 1 row(s) hive> select abs(-11) from dual;OK11Time taken: 0.167 seconds, Fetched: 1 row(s)hive> select floor(3.56) from dual;OK3Time taken: 0.134 seconds, Fetched: 1 row(s)hive> select ceil(3.123) from dual;OK4Time taken: 0.131 seconds, Fetched: 1 row(s)hive> select round(3.23456) from dual;OK3.0Time taken: 0.138 seconds, Fetched: 1 row(s)hive> select round(3.23456,3) from dual;OK3.235Time taken: 0.291 seconds, Fetched: 1 row(s)hive>
(3)字符串函数
hive> select length('hadoop') from dual;OK6Time taken: 0.28 seconds, Fetched: 1 row(s)hive> select reverse('hadoop') from dual;OKpoodahTime taken: 0.257 seconds, Fetched: 1 row(s)hive> select substr('hadoop',2) from dual;OKadoopTime taken: 0.221 seconds, Fetched: 1 row(s)hive> select trim(' hadoop ') from dual;OKhadoopTime taken: 0.267 seconds, Fetched: 1 row(s)hive>
12.1.2 聚合函数
hive> select count(1) from emp;WARNING: Hive-on-MR is deprecated in Hive 2 and may not be available in the future versions. Consider using a different execution engine (i.e. spark, tez) or using Hive 1.X releases.Query ID = root_20170824100247_a5b82db6-3a76-41bb-9f33-1c23c06209daTotal jobs = 1Launching Job 1 out of 1Number of reduce tasks determined at compile time: 1In order to change the average load for a reducer (in bytes): set hive.exec.reducers.bytes.per.reducer=<number>In order to limit the maximum number of reducers: set hive.exec.reducers.max=<number>In order to set a constant number of reducers: set mapreduce.job.reduces=<number>Starting Job = job_1503582553611_0003, Tracking URL = http://node1:8088/proxy/application_1503582553611_0003/Kill Command = /opt/hadoop-2.7.3/bin/hadoop job -kill job_1503582553611_0003Hadoop job information for Stage-1: number of mappers: 1; number of reducers: 12017-08-24 10:03:07,501 Stage-1 map = 0%, reduce = 0%2017-08-24 10:03:21,721 Stage-1 map = 100%, reduce = 0%, Cumulative CPU 3.49 sec2017-08-24 10:03:33,482 Stage-1 map = 100%, reduce = 100%, Cumulative CPU 5.88 secMapReduce Total cumulative CPU time: 5 seconds 880 msecEnded Job = job_1503582553611_0003MapReduce Jobs Launched: Stage-Stage-1: Map: 1 Reduce: 1 Cumulative CPU: 5.88 sec HDFS Read: 9088 HDFS Write: 102 SUCCESSTotal MapReduce CPU Time Spent: 5 seconds 880 msecOK13Time taken: 47.277 seconds, Fetched: 1 row(s)hive>
hive> select avg(sal) from emp;WARNING: Hive-on-MR is deprecated in Hive 2 and may not be available in the future versions. Consider using a different execution engine (i.e. spark, tez) or using Hive 1.X releases.Query ID = root_20170824095107_1f447d4c-f008-491d-8537-00fc4a0d45eaTotal jobs = 1Launching Job 1 out of 1Number of reduce tasks determined at compile time: 1In order to change the average load for a reducer (in bytes): set hive.exec.reducers.bytes.per.reducer=<number>In order to limit the maximum number of reducers: set hive.exec.reducers.max=<number>In order to set a constant number of reducers: set mapreduce.job.reduces=<number>Starting Job = job_1503582553611_0001, Tracking URL = http://node1:8088/proxy/application_1503582553611_0001/Kill Command = /opt/hadoop-2.7.3/bin/hadoop job -kill job_1503582553611_0001Hadoop job information for Stage-1: number of mappers: 1; number of reducers: 12017-08-24 09:51:36,596 Stage-1 map = 0%, reduce = 0%2017-08-24 09:51:55,721 Stage-1 map = 100%, reduce = 0%, Cumulative CPU 2.95 sec2017-08-24 09:52:10,207 Stage-1 map = 100%, reduce = 100%, Cumulative CPU 6.01 secMapReduce Total cumulative CPU time: 6 seconds 10 msecEnded Job = job_1503582553611_0001MapReduce Jobs Launched: Stage-Stage-1: Map: 1 Reduce: 1 Cumulative CPU: 6.01 sec HDFS Read: 9818 HDFS Write: 118 SUCCESSTotal MapReduce CPU Time Spent: 6 seconds 10 msecOK2077.0833333333335Time taken: 65.158 seconds, Fetched: 1 row(s)hive>
hive> select max(sal) from emp where did=30;WARNING: Hive-on-MR is deprecated in Hive 2 and may not be available in the future versions. Consider using a different execution engine (i.e. spark, tez) or using Hive 1.X releases.Query ID = root_20170824095233_ba070012-65fb-42da-89de-f748d33fc9b9Total jobs = 1Launching Job 1 out of 1Number of reduce tasks determined at compile time: 1In order to change the average load for a reducer (in bytes): set hive.exec.reducers.bytes.per.reducer=<number>In order to limit the maximum number of reducers: set hive.exec.reducers.max=<number>In order to set a constant number of reducers: set mapreduce.job.reduces=<number>Starting Job = job_1503582553611_0002, Tracking URL = http://node1:8088/proxy/application_1503582553611_0002/Kill Command = /opt/hadoop-2.7.3/bin/hadoop job -kill job_1503582553611_0002Hadoop job information for Stage-1: number of mappers: 1; number of reducers: 12017-08-24 09:52:52,526 Stage-1 map = 0%, reduce = 0%2017-08-24 09:53:07,359 Stage-1 map = 100%, reduce = 0%, Cumulative CPU 4.0 sec2017-08-24 09:53:18,303 Stage-1 map = 100%, reduce = 100%, Cumulative CPU 6.7 secMapReduce Total cumulative CPU time: 6 seconds 700 msecEnded Job = job_1503582553611_0002MapReduce Jobs Launched: Stage-Stage-1: Map: 1 Reduce: 1 Cumulative CPU: 6.7 sec HDFS Read: 10207 HDFS Write: 106 SUCCESSTotal MapReduce CPU Time Spent: 6 seconds 700 msecOK2850.0Time taken: 46.863 seconds, Fetched: 1 row(s)hive>
12.1.3 表生成函数
hive> select array(1,2,3) from dual;OK[1,2,3]Time taken: 0.371 seconds, Fetched: 1 row(s)hive> select explode(array(1,2,3)) from dual;OK123Time taken: 0.265 seconds, Fetched: 3 row(s)hive>
阅读全文
0 0
- Hadoop基础教程-第12章 Hive:进阶(12.1 内置函数)(草稿)
- Hadoop基础教程-第12章 Hive:进阶(12.2 自定义函数)(草稿)
- Hadoop基础教程-第12章 Hive:进阶(12.4 Hive Metastore)(草稿)
- Hadoop基础教程-第12章 Hive:进阶(12.5 Hive外表)(草稿)
- Hadoop基础教程-第12章 Hive:进阶(12.3 HiveServer2)(草稿)
- Hadoop基础教程-第11章 Hive:SQL on Hadoop(11.1 Hive 介绍)(草稿)
- Hadoop基础教程-第11章 Hive:SQL on Hadoop(11.7 HQL:数据查询)(草稿)
- Hadoop基础教程-第11章 Hive:SQL on Hadoop(11.8 HQL:排序)(草稿)
- Hadoop基础教程-第11章 Hive:SQL on Hadoop(11.2 Hive安装与配置)(草稿)
- Hadoop基础教程-第7章 MapReduce进阶(7.1 MapReduce过程)(草稿)
- Hadoop基础教程-第7章 MapReduce进阶(7.2 MapReduce工作机制)(草稿)
- Hadoop基础教程-第7章 MapReduce进阶(7.3 MapReduce API)(草稿)
- Hadoop基础教程-第11章 Hive:SQL on Hadoop(11.4 数据类型和存储格式)(草稿)
- Hadoop基础教程-第11章 Hive:SQL on Hadoop(11.5 HQL:DDL数据定义)(草稿)
- Hadoop基础教程-第11章 Hive:SQL on Hadoop(11.6 HQL:DML数据操纵)(草稿)
- Hadoop基础教程-第10章 HBase:Hadoop数据库(10.1 NoSQL介绍)(草稿)
- Hadoop基础教程-第10章 HBase:Hadoop数据库(10.2 HBase基本概念、框架)(草稿)
- Hadoop基础教程-第10章 HBase:Hadoop数据库(10.3 HBase安装与配置)(草稿)
- 使用vs2010生成64位的dll文件
- CS0016错误解决汇编
- 关于js的单双引号嵌套问题
- WebSocket介绍以及netty对于WebSocket的支持
- 培养优秀思维
- Hadoop基础教程-第12章 Hive:进阶(12.1 内置函数)(草稿)
- MySQL 死锁与日志二三事
- 程序员面试金典(2):原串翻转(python)
- POJ a simple problem of Integers
- Spring Data Jpa+SpringMVC+Jquery.pagination.js实现分页
- poj 1149 PIGS 最大流模型
- 插入排序之希尔排序
- 图解快速排序
- 腾讯安全部门-大数据挑战赛-learner队伍(进入复赛)