hive 求月访问记录，累计访问记录

最新推荐文章于 2023-05-08 07:33:13 发布
最新推荐文章于 2023-05-08 07:33:13 发布
阅读量544 收藏
点赞数
分类专栏： 【Hadoop】 文章标签： hive
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/ma969070578/article/details/82999467
版权

求出当月记录和累计记录，如2月累计记录=1月+2月记录，3月累计记录=1月+2月+3月记录

A,2015-01,1
A,2015-01,11
A,2015-01,12
A,2015-01,13
B,2015-01,10
B,2015-01,20
B,2015-01,30
B,2015-01,40
A,2015-01,1
A,2015-01,11
A,2015-01,12
A,2015-02,13
B,2015-01,10
B,2015-02,20
B,2015-02,30
B,2015-01,40

 

 

[root@hdp-nn-01 apps]# beeline
Beeline version 1.2.2 by Apache Hive
beeline>  !connect jdbc:hive2://hdp-nn-01:10000
Connecting to jdbc:hive2://hdp-nn-01:10000
Enter username for jdbc:hive2://hdp-nn-01:10000: root
Enter password for jdbc:hive2://hdp-nn-01:10000:
Connected to: Apache Hive (version 1.2.2)
Driver: Hive JDBC (version 1.2.2)
Transaction isolation: TRANSACTION_REPEATABLE_READ
0: jdbc:hive2://hdp-nn-01:10000> select * from t_access_time;
+-------------------------+----------------------+-----------------------+--+
| t_access_time.username  | t_access_time.month  | t_access_time.salary  |
+-------------------------+----------------------+-----------------------+--+
| A                       | 2015-01              | 1                     |
| A                       | 2015-01              | 11                    |
| A                       | 2015-01              | 12                    |
| A                       | 2015-01              | 13                    |
| B                       | 2015-01              | 10                    |
| B                       | 2015-01              | 20                    |
| B                       | 2015-01              | 30                    |
| B                       | 2015-01              | 40                    |
+-------------------------+----------------------+-----------------------+--+
8 rows selected (1.512 seconds)
0: jdbc:hive2://hdp-nn-01:10000> [root@hdp-nn-01 apps]#
[root@hdp-nn-01 apps]#
[root@hdp-nn-01 apps]#
[root@hdp-nn-01 apps]#
[root@hdp-nn-01 apps]#
[root@hdp-nn-01 apps]#
[root@hdp-nn-01 apps]#
[root@hdp-nn-01 apps]#
[root@hdp-nn-01 apps]#
[root@hdp-nn-01 apps]#
[root@hdp-nn-01 apps]#
[root@hdp-nn-01 apps]#
[root@hdp-nn-01 apps]#
[root@hdp-nn-01 apps]# beeline                      
Beeline version 1.2.2 by Apache Hive
beeline>  !connect jdbc:hive2://hdp-nn-01:10000
Connecting to jdbc:hive2://hdp-nn-01:10000
Enter username for jdbc:hive2://hdp-nn-01:10000: root
Enter password for jdbc:hive2://hdp-nn-01:10000:
Connected to: Apache Hive (version 1.2.2)
Driver: Hive JDBC (version 1.2.2)
Transaction isolation: TRANSACTION_REPEATABLE_READ
0: jdbc:hive2://hdp-nn-01:10000>
0: jdbc:hive2://hdp-nn-01:10000>
0: jdbc:hive2://hdp-nn-01:10000>
0: jdbc:hive2://hdp-nn-01:10000>
0: jdbc:hive2://hdp-nn-01:10000> select username ,month ,sum(salary) salary from t_access_time group by username,month;
INFO  : Number of reduce tasks not specified. Estimated from input data size: 1
INFO  : In order to change the average load for a reducer (in bytes):
INFO  :   set hive.exec.reducers.bytes.per.reducer=<number>
INFO  : In order to limit the maximum number of reducers:
INFO  :   set hive.exec.reducers.max=<number>
INFO  : In order to set a constant number of reducers:
INFO  :   set mapreduce.job.reduces=<number>
INFO  : number of splits:1
INFO  : Submitting tokens for job: job_1539182373092_0001
INFO  : The url to track the job: http://hdp-nn-01:8088/proxy/application_1539182373092_0001/
INFO  : Starting Job = job_1539182373092_0001, Tracking URL = http://hdp-nn-01:8088/proxy/application_1539182373092_0001/
INFO  : Kill Command = /root/apps/hadoop-2.6.5/bin/hadoop job  -kill job_1539182373092_0001
INFO  : Hadoop job information for Stage-1: number of mappers: 1; number of reducers: 1
INFO  : 2018-10-10 23:45:45,273 Stage-1 map = 0%,  reduce = 0%
INFO  : 2018-10-10 23:45:56,000 Stage-1 map = 100%,  reduce = 0%, Cumulative CPU 5.34 sec
INFO  : 2018-10-10 23:46:10,379 Stage-1 map = 100%,  reduce = 100%, Cumulative CPU 10.51 sec
INFO  : MapReduce Total cumulative CPU time: 10 seconds 510 msec
INFO  : Ended Job = job_1539182373092_0001
+-----------+----------+---------+--+
| username  |  month   | salary  |
+-----------+----------+---------+--+
| A         | 2015-01  | 37      |
| B         | 2015-01  | 100     |
+-----------+----------+---------+--+
2 rows selected (46.304 seconds)
0: jdbc:hive2://hdp-nn-01:10000> delete from t_accces_time;
Error: Error while compiling statement: FAILED: SemanticException [Error 10294]: Attempt to do update or delete using transaction manager that does not support these operations. (state=42000,code=10294)
0: jdbc:hive2://hdp-nn-01:10000>
0: jdbc:hive2://hdp-nn-01:10000>
0: jdbc:hive2://hdp-nn-01:10000> select * from t_access_time;
+-------------------------+----------------------+-----------------------+--+
| t_access_time.username  | t_access_time.month  | t_access_time.salary  |
+-------------------------+----------------------+-----------------------+--+
| A                       | 2015-01              | 1                     |
| A                       | 2015-01              | 11                    |
| A                       | 2015-01              | 12                    |
| A                       | 2015-01              | 13                    |
| B                       | 2015-01              | 10                    |
| B                       | 2015-01              | 20                    |
| B                       | 2015-01              | 30                    |
| B                       | 2015-01              | 40                    |
+-------------------------+----------------------+-----------------------+--+
8 rows selected (0.187 seconds)
0: jdbc:hive2://hdp-nn-01:10000> load data local inpath '/root/apps/hive1.txt'  into table t_access_time;
INFO  : Loading data to table default.t_access_time from file:/root/apps/hive1.txt
INFO  : Table default.t_access_time stats: [numFiles=2, totalSize=206]
No rows affected (0.741 seconds)
0: jdbc:hive2://hdp-nn-01:10000>
0: jdbc:hive2://hdp-nn-01:10000>
0: jdbc:hive2://hdp-nn-01:10000>
0: jdbc:hive2://hdp-nn-01:10000> select * from t_access_time;
+-------------------------+----------------------+-----------------------+--+
| t_access_time.username  | t_access_time.month  | t_access_time.salary  |
+-------------------------+----------------------+-----------------------+--+
| A                       | 2015-01              | 1                     |
| A                       | 2015-01              | 11                    |
| A                       | 2015-01              | 12                    |
| A                       | 2015-01              | 13                    |
| B                       | 2015-01              | 10                    |
| B                       | 2015-01              | 20                    |
| B                       | 2015-01              | 30                    |
| B                       | 2015-01              | 40                    |
| A                       | 2015-01              | 1                     |
| A                       | 2015-01              | 11                    |
| A                       | 2015-02              | 12                    |
| A                       | 2015-01              | 13                    |
| B                       | 2015-01              | 10                    |
| B                       | 2015-01              | 20                    |
| B                       | 2015-02              | 30                    |
| B                       | 2015-01              | 40                    |
+-------------------------+----------------------+-----------------------+--+
16 rows selected (0.173 seconds)
0: jdbc:hive2://hdp-nn-01:10000> select username ,month ,sum(salary) salary from t_access_time group by username,month;
INFO  : Number of reduce tasks not specified. Estimated from input data size: 1
INFO  : In order to change the average load for a reducer (in bytes):
INFO  :   set hive.exec.reducers.bytes.per.reducer=<number>
INFO  : In order to limit the maximum number of reducers:
INFO  :   set hive.exec.reducers.max=<number>
INFO  : In order to set a constant number of reducers:
INFO  :   set mapreduce.job.reduces=<number>
INFO  : number of splits:1
INFO  : Submitting tokens for job: job_1539182373092_0002
INFO  : The url to track the job: http://hdp-nn-01:8088/proxy/application_1539182373092_0002/
INFO  : Starting Job = job_1539182373092_0002, Tracking URL = http://hdp-nn-01:8088/proxy/application_1539182373092_0002/
INFO  : Kill Command = /root/apps/hadoop-2.6.5/bin/hadoop job  -kill job_1539182373092_0002
INFO  : Hadoop job information for Stage-1: number of mappers: 1; number of reducers: 1
INFO  : 2018-10-10 23:48:31,221 Stage-1 map = 0%,  reduce = 0%
INFO  : 2018-10-10 23:48:36,391 Stage-1 map = 100%,  reduce = 0%, Cumulative CPU 2.95 sec
INFO  : 2018-10-10 23:48:41,516 Stage-1 map = 100%,  reduce = 100%, Cumulative CPU 5.15 sec
INFO  : MapReduce Total cumulative CPU time: 5 seconds 150 msec
INFO  : Ended Job = job_1539182373092_0002
+-----------+----------+---------+--+
| username  |  month   | salary  |
+-----------+----------+---------+--+
| A         | 2015-01  | 62      |
| A         | 2015-02  | 12      |
| B         | 2015-01  | 170     |
| B         | 2015-02  | 30      |
+-----------+----------+---------+--+
4 rows selected (16.206 seconds)
[root@hdp-nn-01 apps]# beeline
Beeline version 1.2.2 by Apache Hive
beeline>  !connect jdbc:hive2://hdp-nn-01:10000
Connecting to jdbc:hive2://hdp-nn-01:10000
Enter username for jdbc:hive2://hdp-nn-01:10000: root
Enter password for jdbc:hive2://hdp-nn-01:10000:
Connected to: Apache Hive (version 1.2.2)
Driver: Hive JDBC (version 1.2.2)
Transaction isolation: TRANSACTION_REPEATABLE_READ
0: jdbc:hive2://hdp-nn-01:10000>
0: jdbc:hive2://hdp-nn-01:10000>
0: jdbc:hive2://hdp-nn-01:10000>
0: jdbc:hive2://hdp-nn-01:10000> set hive.exec.mode.local.auto=true;
No rows affected (0.057 seconds)
0: jdbc:hive2://hdp-nn-01:10000> create table t_temp1 as select username ,month ,sum(salary) salary from t_access_time group by username,month;
INFO  : Number of reduce tasks not specified. Estimated from input data size: 1
INFO  : In order to change the average load for a reducer (in bytes):
INFO  :   set hive.exec.reducers.bytes.per.reducer=<number>
INFO  : In order to limit the maximum number of reducers:
INFO  :   set hive.exec.reducers.max=<number>
INFO  : In order to set a constant number of reducers:
INFO  :   set mapreduce.job.reduces=<number>
INFO  : number of splits:1
INFO  : Submitting tokens for job: job_local164879396_0001
INFO  : The url to track the job: http://localhost:8080/
INFO  : Job running in-process (local Hadoop)
INFO  : 2018-10-10 23:52:25,322 Stage-1 map = 100%,  reduce = 100%
INFO  : Ended Job = job_local164879396_0001
INFO  : Moving data to: hdfs://hdp-nn-01:9000/user/hive/warehouse/t_temp1 from hdfs://hdp-nn-01:9000/user/hive/warehouse/.hive-staging_hive_2018-10-10_23-52-23_803_4672116048929793587-5/-ext-10001
INFO  : Table default.t_temp1 stats: [numFiles=1, numRows=4, totalSize=53, rawDataSize=49]
No rows affected (2.014 seconds)
0: jdbc:hive2://hdp-nn-01:10000> select * from t_temp1;
+-------------------+----------------+-----------------+--+
| t_temp1.username  | t_temp1.month  | t_temp1.salary  |
+-------------------+----------------+-----------------+--+
| A                 | 2015-01        | 62              |
| A                 | 2015-02        | 12              |
| B                 | 2015-01        | 170             |
| B                 | 2015-02        | 30              |
+-------------------+----------------+-----------------+--+
4 rows selected (0.179 seconds)
0: jdbc:hive2://hdp-nn-01:10000> [root@hdp-nn-01 apps]# beeline
Beeline version 1.2.2 by Apache Hive
beeline>  !connect jdbc:hive2://hdp-nn-01:10000
Connecting to jdbc:hive2://hdp-nn-01:10000
Enter username for jdbc:hive2://hdp-nn-01:10000: root
Enter password for jdbc:hive2://hdp-nn-01:10000:
Connected to: Apache Hive (version 1.2.2)
Driver: Hive JDBC (version 1.2.2)
Transaction isolation: TRANSACTION_REPEATABLE_READ
0: jdbc:hive2://hdp-nn-01:10000>
0: jdbc:hive2://hdp-nn-01:10000>
0: jdbc:hive2://hdp-nn-01:10000>
0: jdbc:hive2://hdp-nn-01:10000>
0: jdbc:hive2://hdp-nn-01:10000>
0: jdbc:hive2://hdp-nn-01:10000>
0: jdbc:hive2://hdp-nn-01:10000> select * from t_temp1 a  join t_temp1 b  on a.username=b.username;
INFO  : Execution completed successfully
INFO  : MapredLocal task succeeded
INFO  : Number of reduce tasks is set to 0 since there's no reduce operator
INFO  : number of splits:1
INFO  : Submitting tokens for job: job_1539182373092_0003
INFO  : The url to track the job: http://hdp-nn-01:8088/proxy/application_1539182373092_0003/
INFO  : Starting Job = job_1539182373092_0003, Tracking URL = http://hdp-nn-01:8088/proxy/application_1539182373092_0003/
INFO  : Kill Command = /root/apps/hadoop-2.6.5/bin/hadoop job  -kill job_1539182373092_0003
INFO  : Hadoop job information for Stage-3: number of mappers: 1; number of reducers: 0
INFO  : 2018-10-10 23:56:53,180 Stage-3 map = 0%,  reduce = 0%
INFO  : 2018-10-10 23:56:58,333 Stage-3 map = 100%,  reduce = 0%, Cumulative CPU 1.17 sec
INFO  : MapReduce Total cumulative CPU time: 1 seconds 170 msec
INFO  : Ended Job = job_1539182373092_0003
+-------------+----------+-----------+-------------+----------+-----------+--+
| a.username  | a.month  | a.salary  | b.username  | b.month  | b.salary  |
+-------------+----------+-----------+-------------+----------+-----------+--+
| A           | 2015-01  | 62        | A           | 2015-01  | 62        |
| A           | 2015-02  | 12        | A           | 2015-01  | 62        |
| A           | 2015-01  | 62        | A           | 2015-02  | 12        |
| A           | 2015-02  | 12        | A           | 2015-02  | 12        |
| B           | 2015-01  | 170       | B           | 2015-01  | 170       |
| B           | 2015-02  | 30        | B           | 2015-01  | 170       |
| B           | 2015-01  | 170       | B           | 2015-02  | 30        |
| B           | 2015-02  | 30        | B           | 2015-02  | 30        |
+-------------+----------+-----------+-------------+----------+-----------+--+
8 rows selected (14.906 seconds)
0: jdbc:hive2://hdp-nn-01:10000> select * from t_temp1 a  join t_temp1 b  on a.username=b.username where a.month <=b.month ;
INFO  : Execution completed successfully
INFO  : MapredLocal task succeeded
INFO  : Number of reduce tasks is set to 0 since there's no reduce operator
INFO  : number of splits:1
INFO  : Submitting tokens for job: job_1539182373092_0004
INFO  : The url to track the job: http://hdp-nn-01:8088/proxy/application_1539182373092_0004/
INFO  : Starting Job = job_1539182373092_0004, Tracking URL = http://hdp-nn-01:8088/proxy/application_1539182373092_0004/
INFO  : Kill Command = /root/apps/hadoop-2.6.5/bin/hadoop job  -kill job_1539182373092_0004
INFO  : Hadoop job information for Stage-3: number of mappers: 1; number of reducers: 0
INFO  : 2018-10-11 00:01:42,367 Stage-3 map = 0%,  reduce = 0%
INFO  : 2018-10-11 00:01:46,472 Stage-3 map = 100%,  reduce = 0%, Cumulative CPU 1.24 sec
INFO  : MapReduce Total cumulative CPU time: 1 seconds 240 msec
INFO  : Ended Job = job_1539182373092_0004
+-------------+----------+-----------+-------------+----------+-----------+--+
| a.username  | a.month  | a.salary  | b.username  | b.month  | b.salary  |
+-------------+----------+-----------+-------------+----------+-----------+--+
| A           | 2015-01  | 62        | A           | 2015-01  | 62        |
| A           | 2015-01  | 62        | A           | 2015-02  | 12        |
| A           | 2015-02  | 12        | A           | 2015-02  | 12        |
| B           | 2015-01  | 170       | B           | 2015-01  | 170       |
| B           | 2015-01  | 170       | B           | 2015-02  | 30        |
| B           | 2015-02  | 30        | B           | 2015-02  | 30        |
+-------------+----------+-----------+-------------+----------+-----------+--+
6 rows selected (13.334 seconds)
0: jdbc:hive2://hdp-nn-01:10000> create table t_temp2 as
0: jdbc:hive2://hdp-nn-01:10000> select  
0: jdbc:hive2://hdp-nn-01:10000> a.username as aname,
0: jdbc:hive2://hdp-nn-01:10000> a.month as amonth,
0: jdbc:hive2://hdp-nn-01:10000> a.salary as asalary,
0: jdbc:hive2://hdp-nn-01:10000> b.username as bname,
0: jdbc:hive2://hdp-nn-01:10000> b.month as bmonth,
0: jdbc:hive2://hdp-nn-01:10000> b.salary as bsalary
0: jdbc:hive2://hdp-nn-01:10000>
0: jdbc:hive2://hdp-nn-01:10000> from t_temp1 a  join t_temp1 b  on a.username=b.username where a.month <=b.month ;
INFO  : Execution completed successfully
INFO  : MapredLocal task succeeded
INFO  : Number of reduce tasks is set to 0 since there's no reduce operator
INFO  : number of splits:1
INFO  : Submitting tokens for job: job_1539182373092_0005
INFO  : The url to track the job: http://hdp-nn-01:8088/proxy/application_1539182373092_0005/
INFO  : Starting Job = job_1539182373092_0005, Tracking URL = http://hdp-nn-01:8088/proxy/application_1539182373092_0005/
INFO  : Kill Command = /root/apps/hadoop-2.6.5/bin/hadoop job  -kill job_1539182373092_0005
INFO  : Hadoop job information for Stage-4: number of mappers: 1; number of reducers: 0
INFO  : 2018-10-11 00:06:07,100 Stage-4 map = 0%,  reduce = 0%
INFO  : 2018-10-11 00:06:12,213 Stage-4 map = 100%,  reduce = 0%, Cumulative CPU 1.24 sec
INFO  : MapReduce Total cumulative CPU time: 1 seconds 240 msec
INFO  : Ended Job = job_1539182373092_0005
INFO  : Moving data to: hdfs://hdp-nn-01:9000/user/hive/warehouse/t_temp2 from hdfs://hdp-nn-01:9000/user/hive/warehouse/.hive-staging_hive_2018-10-11_00-06-00_196_2056446029980412768-7/-ext-10001
INFO  : Table default.t_temp2 stats: [numFiles=1, numRows=6, totalSize=159, rawDataSize=153]
No rows affected (13.422 seconds)
0: jdbc:hive2://hdp-nn-01:10000> select * from t_temp2;
+----------------+-----------------+------------------+----------------+-----------------+------------------+--+
| t_temp2.aname  | t_temp2.amonth  | t_temp2.asalary  | t_temp2.bname  | t_temp2.bmonth  | t_temp2.bsalary  |
+----------------+-----------------+------------------+----------------+-----------------+------------------+--+
| A              | 2015-01         | 62               | A              | 2015-01         | 62               |
| A              | 2015-01         | 62               | A              | 2015-02         | 12               |
| A              | 2015-02         | 12               | A              | 2015-02         | 12               |
| B              | 2015-01         | 170              | B              | 2015-01         | 170              |
| B              | 2015-01         | 170              | B              | 2015-02         | 30               |
| B              | 2015-02         | 30               | B              | 2015-02         | 30               |
+----------------+-----------------+------------------+----------------+-----------------+------------------+--+
6 rows selected (0.103 seconds)
0: jdbc:hive2://hdp-nn-01:10000> select bname,bmonth,sum(asalary) from t_temp2 group by bname,bmonth, bsalary;
INFO  : Number of reduce tasks not specified. Estimated from input data size: 1
INFO  : In order to change the average load for a reducer (in bytes):
INFO  :   set hive.exec.reducers.bytes.per.reducer=<number>
INFO  : In order to limit the maximum number of reducers:
INFO  :   set hive.exec.reducers.max=<number>
INFO  : In order to set a constant number of reducers:
INFO  :   set mapreduce.job.reduces=<number>
INFO  : number of splits:1
INFO  : Submitting tokens for job: job_1539182373092_0006
INFO  : The url to track the job: http://hdp-nn-01:8088/proxy/application_1539182373092_0006/
INFO  : Starting Job = job_1539182373092_0006, Tracking URL = http://hdp-nn-01:8088/proxy/application_1539182373092_0006/
INFO  : Kill Command = /root/apps/hadoop-2.6.5/bin/hadoop job  -kill job_1539182373092_0006
INFO  : Hadoop job information for Stage-1: number of mappers: 1; number of reducers: 1
INFO  : 2018-10-11 00:11:48,163 Stage-1 map = 0%,  reduce = 0%
INFO  : 2018-10-11 00:11:53,351 Stage-1 map = 100%,  reduce = 0%, Cumulative CPU 2.38 sec
INFO  : 2018-10-11 00:11:58,484 Stage-1 map = 100%,  reduce = 100%, Cumulative CPU 3.44 sec
INFO  : MapReduce Total cumulative CPU time: 3 seconds 440 msec
INFO  : Ended Job = job_1539182373092_0006
+--------+----------+------+--+
| bname  |  bmonth  | _c2  |
+--------+----------+------+--+
| A      | 2015-01  | 62   |
| A      | 2015-02  | 74   |
| B      | 2015-01  | 170  |
| B      | 2015-02  | 200  |
+--------+----------+------+--+
4 rows selected (15.245 seconds)
0: jdbc:hive2://hdp-nn-01:10000> select bname,bmonth,bsalary,sum(asalary) as tsalary  from t_temp2 group by bname,bmonth, bsalary;
INFO  : Number of reduce tasks not specified. Estimated from input data size: 1
INFO  : In order to change the average load for a reducer (in bytes):
INFO  :   set hive.exec.reducers.bytes.per.reducer=<number>
INFO  : In order to limit the maximum number of reducers:
INFO  :   set hive.exec.reducers.max=<number>
INFO  : In order to set a constant number of reducers:
INFO  :   set mapreduce.job.reduces=<number>
INFO  : number of splits:1
INFO  : Submitting tokens for job: job_1539182373092_0007
INFO  : The url to track the job: http://hdp-nn-01:8088/proxy/application_1539182373092_0007/
INFO  : Starting Job = job_1539182373092_0007, Tracking URL = http://hdp-nn-01:8088/proxy/application_1539182373092_0007/
INFO  : Kill Command = /root/apps/hadoop-2.6.5/bin/hadoop job  -kill job_1539182373092_0007
INFO  : Hadoop job information for Stage-1: number of mappers: 1; number of reducers: 1
INFO  : 2018-10-11 00:12:50,334 Stage-1 map = 0%,  reduce = 0%
INFO  : 2018-10-11 00:12:55,495 Stage-1 map = 100%,  reduce = 0%, Cumulative CPU 2.2 sec
INFO  : 2018-10-11 00:12:59,609 Stage-1 map = 100%,  reduce = 100%, Cumulative CPU 3.22 sec
INFO  : MapReduce Total cumulative CPU time: 3 seconds 220 msec
INFO  : Ended Job = job_1539182373092_0007
+--------+----------+----------+----------+--+
| bname  |  bmonth  | bsalary  | tsalary  |
+--------+----------+----------+----------+--+
| A      | 2015-01  | 62       | 62       |
| A      | 2015-02  | 12       | 74       |
| B      | 2015-01  | 170      | 170      |
| B      | 2015-02  | 30       | 200      |
+--------+----------+----------+----------+--+
4 rows selected (13.917 seconds)
0: jdbc:hive2://hdp-nn-01:10000> create table t_final as select bname,bmonth,bsalary,sum(asalary) as tsalary  from t_temp2 group by bname,bmonth, bsalary;
INFO  : Number of reduce tasks not specified. Estimated from input data size: 1
INFO  : In order to change the average load for a reducer (in bytes):
INFO  :   set hive.exec.reducers.bytes.per.reducer=<number>
INFO  : In order to limit the maximum number of reducers:
INFO  :   set hive.exec.reducers.max=<number>
INFO  : In order to set a constant number of reducers:
INFO  :   set mapreduce.job.reduces=<number>
INFO  : number of splits:1
INFO  : Submitting tokens for job: job_1539182373092_0008
INFO  : The url to track the job: http://hdp-nn-01:8088/proxy/application_1539182373092_0008/
INFO  : Starting Job = job_1539182373092_0008, Tracking URL = http://hdp-nn-01:8088/proxy/application_1539182373092_0008/
INFO  : Kill Command = /root/apps/hadoop-2.6.5/bin/hadoop job  -kill job_1539182373092_0008
INFO  : Hadoop job information for Stage-1: number of mappers: 1; number of reducers: 1
INFO  : 2018-10-11 00:20:12,409 Stage-1 map = 0%,  reduce = 0%
INFO  : 2018-10-11 00:20:17,539 Stage-1 map = 100%,  reduce = 0%, Cumulative CPU 2.68 sec
INFO  : 2018-10-11 00:20:23,692 Stage-1 map = 100%,  reduce = 100%, Cumulative CPU 3.75 sec
INFO  : MapReduce Total cumulative CPU time: 3 seconds 750 msec
INFO  : Ended Job = job_1539182373092_0008
INFO  : Moving data to: hdfs://hdp-nn-01:9000/user/hive/warehouse/t_final from hdfs://hdp-nn-01:9000/user/hive/warehouse/.hive-staging_hive_2018-10-11_00-20-08_776_6311242436066492297-7/-ext-10001
INFO  : Table default.t_final stats: [numFiles=1, numRows=4, totalSize=67, rawDataSize=63]
No rows affected (16.612 seconds)
0: jdbc:hive2://hdp-nn-01:10000> select * from t_final;
+----------------+-----------------+------------------+------------------+--+
| t_final.bname  | t_final.bmonth  | t_final.bsalary  | t_final.tsalary  |
+----------------+-----------------+------------------+------------------+--+
| A              | 2015-01         | 62               | 62               |
| A              | 2015-02         | 12               | 74               |
| B              | 2015-01         | 170              | 170              |
| B              | 2015-02         | 30               | 200              |
+----------------+-----------------+------------------+------------------+--+
4 rows selected (0.138 seconds)
0: jdbc:hive2://hdp-nn-01:10000>

 

 

 

 

 

 

 

 

 

天海行者
关注
  • 0
    点赞
  • 踩
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
专栏目录
idea中通过spark访问hive
01-07
折腾了两个小时，好久没有弄了，记录一下 一、复制hive-site.xml 复制hive-site.xml到idea工程中的resource目录下 二、在windows中安装hadoop并配置环境变量 1、解压hadoop 2、修改hadoop etc/hadoop中的 hadoop-env...
Hive基本操作之用户访问次数统计
ya0ng的专栏
09-12 1526
# 前言 根据对用户ID、访问日期、访问次数的若干条数据，分析得出每个用户的累计访问次数，涉及字符串替换、时间函数、开窗、求和等技术。 ## 1. 假如有用户访问数据（用户ID，访问日期，访问次数） |userId|visitDate|visitCount| | -- | -- | -- | |u01|2017/1/21|5| |u02|2017/1/23|6| |u03|2017/1/22|8| |u04|2017/1/20|3| |u01|2017/1/23|6| |u01|2017/2/21|8| |
hive统计店铺每个月的营业额以及到当目前的历史累计额
窦永厚
05-23 2970
--------数据 名称，月份，资金 a,01,150 a,01,200 b,01,1000 b,01,800 c,01,250 c,01,220 b,01,6000 a,02,2000 a,02,3000 b,02,1000 b,02,1500 c,02,350 c,02,280 a,03,350 a,03,250 ----建表 create table t_store( name strin...
HIVE SQL ——订单消费天数累加
斧王，不能再斩杀了吗的博客
07-10 909
数据： user：用户，num：下单次数，date消费日期 user num date A 1 20200206 A 2 20200517 A 2 20200530 A 4 20200611 A 6 20201111 B 1 20201113 B 5 20201210 B 3 20210222 需求：按照一个人的订单日期从1开始排序累加，并计算下一笔订单距离上一笔订单间隔
【Hive】一些函数
依旧的博客
04-27 4878
语法: split(string str, string pat) 返回值: array 说明: 按照pat字符串分割str，会返回分割后的字符串数组 举例： 1.基本用法 hive> select split('abcdef', 'c') from test; ["ab", "def"] 2.截取字符串中的某个值 //获取年月日 hive (default)> select...
SQL | 窗口函数 | 累计求和问题
weixin_46023346的博客
05-06 3739
一、每个用户截止到每月为止的最大单月访问次数和累计到该月的总访问次数 1、创建表 CREATE TABLE IF NOT EXISTS visits( userid VARCHAR(10), datetime1 DATETIME, visits INT)ENGINE=InnoDB DEFAULT CHARSET=utf8; 2、插入数据 INSERT INTO visits VALUES('A','2021-01-03 14:59:43',5); INSERT INTO visits VALUES('A'
基于hadoop的web访问记录分析
11-15
本文将深入探讨如何基于Hadoop进行Web访问记录的分析，包括页面访问量统计、独立IP访问统计、每小时PV统计、来源域名统计以及访问设备统计。 1. **页面访问量统计**： 页面访问量（Page Views，简称PV）是衡量网站...
python脚本访问http接口数据写入hive表
最新发布
03-14
python脚本访问http接口数据写入hive表
Hive学习记录
09-15
Hive学习记录，一些关于hive分桶、分区的知识。有需要自己下载。
hive安装与python访问hive
05-19
内容包括hive如何安装与启动，以及如何使用python访问hive，希望对大家有帮助。
hive sql 实战案例-求每个用户按月累积交易金额
jianai858的博客
05-08 810
求每个用户的GMV和按月累积GMV
hive面试题以及解题思路
jin6872115的博客
04-08 413
题目1：现有这么一批数据，现要求出： 每个用户截止到每月为止的最大单月访问次数和累计到该月的总访问次数 三个字段的意思： 用户名，月份，访问次数 A,2015-01,5 A,2015-01,15 B,2015-01,5 A,2015-01,8 B,2015-01,25 A,2015-01,5 A,2015-02,4 A,2015-02,6 B,2015-02,10 B,2015-02,5 A,...
Hive编程题：求每月的累计访问数
chenzhang0485的博客
12-11 876
题目： 每个用户截止到每月为止的最大单月访问次数和累计到该月的总访问次数： 用户名，月份，访问次数 A, 2015-01, 5 A, 2015-01, 15 B, 2015-01, 5 A, 2015-01, 8 B, 2015-...
Hive经典案例：求出每个用户到当月为止的最大当月访问次数和累积到当月的总访问次数
Vency_L的博客
11-06 1061
1、数据准备 有如下数据：用户名，月份，访问次数 A,2015-01,5 A,2015-01,15 B,2015-01,5 A,2015-01,8 B,2015-01,25 A,2015-01,5 A,2015-02,4 A,2015-02,6 B,2015-02,10 B,2015-02,5 A,2015-03,16 A,2015-03,22 B,2015-03,23 B,2015-03,10 B,2015-03,1 2、需求分析 （1）创建表 create table requirement( nam
hive之求累计值
数据小白的进阶之路
10-22 1万+
最近做项目真是忙到昏厥，抽个空写篇博客吧，保持良好的学习习惯~ 今天要写的是工作中经常遇到的求累计值的问题。 从订单表中取出orderid,uid,time,ciiamount，数据格式如下： 现需要计算每个uid每天累计的成交金额。 SQL代码如下： --汇总每个uid每天的订单成交金额 USE edw_htl; DROP TABLE IF EXISTS tmp_linshi_c...
Hive第六天——Hive函数（开窗函数之累计统计）
你怎么回事
09-27 7379
本文部分参考自：https://blog.csdn.net/qq_23897391/article/details/100558433 Hive第六天——Hive函数 自己的话：千里之行，始于足下。 每天都要保持前进，我势必要有强劲的实力，再跟明天的自己问好。 开窗函数：累计统计 这类函数叫法很多，包括分析函数、窗口函数、开窗函数、分析窗口函数，其实说的都是一类函数 一、开窗函数简介 开窗函...
Hive求截止到当前日期累计消费金额或使用次数
FlatTiger的博客
07-09 2477
说明 经常遇到这种类型的问题：求截止到当前日期（或在此日期之前），某用户的累计消费金额或者累计使用次数。 只要遇到截止到当前日期为止，就要想到窗口函数，因为最后求的是累积消费金额，就要使用sum(金额字段) over(partition by 用户 order by 日期字段)。 场景题 下面是力扣的一道SQL题，以此题为例： activity 表: 编写一个 SQL 查询，同时报告每组玩家和日期，以及玩家到目前为止玩了多少游戏。也就是说，在此日期之前玩家所玩的游戏总数。最终结果如下： SQL SELE
Hive窗口函数之累积值、平均值、首尾值的计算学习
BabyFish13
11-16 2万+
Hive窗口函数可以计算一定范围内、一定值域内、或者一段时间内的累积和以及移动平均值等；可以结合聚集函数SUM() 、AVG()等使用；可以结合FIRST_VALUE() 和LAST_VALUE()，返回窗口的第一个和最后一个值。 - 如果只使用partition by子句,未指定order by的话,我们的聚合是分组内的聚合.  - 使用了order by子句,未使用window子句的情况下
数仓工具—Hive实战之累计计算(7)
热门推荐
06-23 5万+
累计计算 对于数仓而言，我们一般都是增量计算，增量数据同步、增量数据计算，然后我们产出每天的指标，大多数情况下都是这样的，但是我们也有很多的指标需要把过去的数据和当前的数据进行累计，从而产出计算结果。 接下来我们分析一下常用的累计计算，然后关注一下如何去从中抽出一个通用的计算模型 数据准备/需求分析 day open_cnt click_cnt +----+---------+--------+ 2021-06-10 100 50 2021-06-11 120 60 2021-06-12 150 50 这
Hive SQL面试题：用户访问次数累计统计
问题要求根据用户ID（userId）和访问日期（visitDate）计算每个用户在每个月的累计访问次数（累积）。提供的数据包括用户ID、访问日期和访问次数。已经给出了数据准备的Hive SQL语句，创建了一个名为test_sql.test1...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值