1. 数据准备
# 本地数据准备
[yun@mini01 hive]$ pwd
/app/software/hive
[yun@mini01 hive]$ ll /app/software/hive/t_access_times.dat
-rw-rw-r-- yun yun Jul : /app/software/hive/t_access_times.dat
[yun@mini01 hive]$ cat /app/software/hive/t_access_times.dat
A,--,
A,--,
B,--,
A,--,
B,--,
A,--,
A,--,
A,--,
B,--,
B,--,
A,--,
B,--,
A,--,
B,--,
A,--,
B,--,
A,--,
B,--,
A,--,
# hive 建表
hive (test_db)> create table t_access_times(username string,month string,salary int)
> row format delimited fields terminated by ',';
OK
Time taken: 0.16 seconds
# 数据上传 从desc formatted t_access_times; 可获取Location信息
: jdbc:hive2://mini01:10000> load data local inpath '/app/software/hive/t_access_times.dat' [overwrite] into table t_access_times; # 上传
INFO : Loading data to table test_db.t_access_times from file:/app/software/hive/t_access_times.dat
INFO : Table test_db.t_access_times stats: [numFiles=, totalSize=]
No rows affected (0.764 seconds)
: jdbc:hive2://mini01:10000> select * from t_access_times; # 查询数据
+--------------------------+-----------------------+------------------------+--+
| t_access_times.username | t_access_times.month | t_access_times.salary |
+--------------------------+-----------------------+------------------------+--+
| A | -- | |
| A | -- | |
| B | -- | |
| A | -- | |
| B | -- | |
| A | -- | |
| A | -- | |
| A | -- | |
| B | -- | |
| B | -- | |
| A | -- | |
| B | -- | |
| A | -- | |
| B | -- | |
| A | -- | |
| B | -- | |
| A | -- | |
| B | -- | |
| A | -- | |
+--------------------------+-----------------------+------------------------+--+
rows selected (0.102 seconds)
# 根据月份查询 去掉月份字段中的天信息
: jdbc:hive2://mini01:10000> select a.username, substr(a.month,1,7) month, a.salary from t_access_times a;
+-------------+----------+-----------+--+
| a.username | month | a.salary |
+-------------+----------+-----------+--+
| A | - | |
| A | - | |
| B | - | |
| A | - | |
| B | - | |
| A | - | |
| A | - | |
| A | - | |
| B | - | |
| B | - | |
| A | - | |
| B | - | |
| A | - | |
| B | - | |
| A | - | |
| B | - | |
| A | - | |
| B | - | |
| A | - | |
+-------------+----------+-----------+--+
rows selected (0.078 seconds)
2. 用户一个月总金额
# 或者使用 select x.username, x.month, sum(x.salary) from (select a.username, substr(a.month,,) month, a.salary from t_access_times a) x group by x.username, x.month;
: jdbc:hive2://mini01:10000> select a.username, substr(a.month,1,7) month, sum(a.salary) salary from t_access_times a group by a.username, substr(a.month,1,7);
INFO : Number of reduce tasks not specified. Estimated from input data size:
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:
INFO : Submitting tokens for job: job_1531893043061_0002
INFO : The url to track the job: http://mini02:8088/proxy/application_1531893043061_0002/
INFO : Starting Job = job_1531893043061_0002, Tracking URL = http://mini02:8088/proxy/application_1531893043061_0002/
INFO : Kill Command = /app/hadoop/bin/hadoop job -kill job_1531893043061_0002
INFO : Hadoop job information for Stage-: number of mappers: ; number of reducers:
INFO : -- ::, Stage- map = %, reduce = %
INFO : -- ::, Stage- map = %, reduce = %, Cumulative CPU 3.08 sec
INFO : -- ::, Stage- map = %, reduce = %, Cumulative CPU 5.53 sec
INFO : MapReduce Total cumulative CPU time: seconds msec
INFO : Ended Job = job_1531893043061_0002
+-------------+----------+------+--+
| a.username | month | _c2 |
+-------------+----------+------+--+
| A | - | |
| A | - | |
| A | - | |
| B | - | |
| B | - | |
| B | - | |
+-------------+----------+------+--+
rows selected (18.755 seconds)
3. 将月总金额表 自己连接 自己连接
: jdbc:hive2://mini01:10000> select * from
: jdbc:hive2://mini01:10000> (select a.username, substr(a.month,1,7) month, sum(a.salary) salary from t_access_times a group by a.username, substr(a.month,1,7)) A
: jdbc:hive2://mini01:10000> inner join
: jdbc:hive2://mini01:10000> (select a.username, substr(a.month,1,7) month, sum(a.salary) salary from t_access_times a group by a.username, substr(a.month,1,7)) B
: jdbc:hive2://mini01:10000> on A.username = B.username
: jdbc:hive2://mini01:10000> ORDER BY A.username, A.`month`, B.`month`;
INFO : Number of reduce tasks not specified. Estimated from input data size:
…………………………
INFO : Ended Job = job_1531893043061_0029
+-------------+----------+-----------+-------------+----------+-----------+--+
| a.username | a.month | a.salary | b.username | b.month | b.salary |
+-------------+----------+-----------+-------------+----------+-----------+--+
| A | - | | A | - | |
| A | - | | A | - | |
| A | - | | A | - | |
| A | - | | A | - | |
| A | - | | A | - | |
| A | - | | A | - | |
| A | - | | A | - | |
| A | - | | A | - | |
| A | - | | A | - | |
| B | - | | B | - | |
| B | - | | B | - | |
| B | - | | B | - | |
| B | - | | B | - | |
| B | - | | B | - | |
| B | - | | B | - | |
| B | - | | B | - | |
| B | - | | B | - | |
| B | - | | B | - | |
+-------------+----------+-----------+-------------+----------+-----------+--+
rows selected (85.593 seconds)
######################################################
# 查询后排序
: jdbc:hive2://mini01:10000> select * from
: jdbc:hive2://mini01:10000> (select a.username, substr(a.month,1,7) month, sum(a.salary) salary from t_access_times a group by a.username, substr(a.month,1,7)) A
: jdbc:hive2://mini01:10000> inner join
: jdbc:hive2://mini01:10000> (select a.username, substr(a.month,1,7) month, sum(a.salary) salary from t_access_times a group by a.username, substr(a.month,1,7)) B
: jdbc:hive2://mini01:10000> on A.username = B.username
: jdbc:hive2://mini01:10000> where A.month >= B.month
: jdbc:hive2://mini01:10000> ORDER BY A.username, A.month, B.month;
INFO : Number of reduce tasks not specified. Estimated from input data size:
…………………………
INFO : Ended Job = job_1531893043061_0016
+-------------+----------+--------+-------------+----------+--------+--+
| a.username | a.month | a._c2 | b.username | b.month | b._c2 |
+-------------+----------+--------+-------------+----------+--------+--+
| A | - | | A | - | |
| A | - | | A | - | |
| A | - | | A | - | |
| A | - | | A | - | |
| A | - | | A | - | |
| A | - | | A | - | |
| B | - | | B | - | |
| B | - | | B | - | |
| B | - | | B | - | |
| B | - | | B | - | |
| B | - | | B | - | |
| B | - | | B | - | |
+-------------+----------+--------+-------------+----------+--------+--+
rows selected (83.385 seconds)
4. 累计报表
4.1. 类似数据在MySQL数据库查询
# 使用这个SQL语句就可了，但是在HIVE中运行不了
select A.username, A.month, A.salary , sum(B.salary) countSala from
(select a.username, substr(a.month,1,7) month, sum(a.salary) salary from t_access_times a group by a.username, substr(a.month,1,7)) A
inner join
(select a.username, substr(a.month,1,7) month, sum(a.salary) salary from t_access_times a group by a.username, substr(a.month,1,7)) B
on A.username = B.username
where A.month >= B.month
group by A.username, A.month
ORDER BY A.username, A.month, B.month;
![Hive-1.2.1_06_累计报表查询-LMLPHP]( "Hive-1.2.1_06_累计报表查询-LMLPHP")
4.2. Hive中运行
# 上面的SQL不能运行 所以查询列表改为了max(A.salary) salary ； order by 中去掉了 B.month 。
0: jdbc:hive2://mini01:10000> select A.username, A.month, max(A.salary) salary, sum(B.salary) countSala from
0: jdbc:hive2://mini01:10000> (select a.username, substr(a.month,1,7) month, sum(a.salary) salary from t_access_times a group by a.username, substr(a.month,1,7)) A
0: jdbc:hive2://mini01:10000> inner join
0: jdbc:hive2://mini01:10000> (select a.username, substr(a.month,1,7) month, sum(a.salary) salary from t_access_times a group by a.username, substr(a.month,1,7)) B
0: jdbc:hive2://mini01:10000> on A.username = B.username
0: jdbc:hive2://mini01:10000> where A.month >= B.month
0: jdbc:hive2://mini01:10000> group by A.username, A.month
0: jdbc:hive2://mini01:10000> ORDER BY A.username, A.month;
INFO : Number of reduce tasks not specified. Estimated from input data size: 1
………………
INFO : Ended Job = job_1531893043061_0052
+-------------+----------+---------+------------+--+
| a.username | a.month | salary | countsala |
+-------------+----------+---------+------------+--+
| A | 2015-01 | 33 | 33 |
| A | 2015-02 | 10 | 43 |
| A | 2015-03 | 11 | 54 |
| B | 2015-01 | 30 | 30 |
| B | 2015-02 | 15 | 45 |
| B | 2015-03 | 20 | 65 |
+-------------+----------+---------+------------+--+
6 rows selected (106.718 seconds)