配置单元变量用法_大数据知识库

我正在cloudera-training-vm-4.1.1.c上使用hive0.9.0版本。在中使用hive变量时遇到了问题 SELECT 查询。请看一下，让我知道哪里出了问题。
1）这是我的table rushi_target ```
hive> desc rushi_target;
OK
load_ts timestamp
id int
name string
loc string
data_dt string
Time taken: 0.154 seconds

2） 这是数据

hive> select * from rushi_target;
OK
2015-12-01 00:02:34 1 rushi pune 2015-12-01
2015-12-02 04:02:34 2 komal pune 2015-12-02
2015-12-03 00:03:34 3 bhanu bangalore 2015-12-03
2015-12-04 00:03:34 4 sachin pune 2015-12-04
Time taken: 0.258 seconds

3） 设置配置单元变量

hive> set maxtimevar= select max(load_ts) from rushi_target;

4） 显示配置单元变量的值

hive> ${hiveconf:maxtimevar};
Total MapReduce jobs = 1
Launching Job 1 out of 1
Number of reduce tasks determined at compile time: 1
In order to change the average load for a reducer (in bytes):
set hive.exec.reducers.bytes.per.reducer=
In order to limit the maximum number of reducers:
set hive.exec.reducers.max=
In order to set a constant number of reducers:
set mapred.reduce.tasks=
Starting Job = job_201512211113_0011, Tracking URL = http://0.0.0.0:50030/jobdetails.jsp?jobid=job_201512211113_0011
Kill Command = /usr/lib/hadoop/bin/hadoop job -Dmapred.job.tracker=0.0.0.0:8021 -kill job_201512211113_0011
Hadoop job information for Stage-1: number of mappers: 1; number of reducers: 1
2015-12-22 08:51:35,401 Stage-1 map = 0%, reduce = 0%
2015-12-22 08:51:37,409 Stage-1 map = 100%, reduce = 0%, Cumulative CPU 0.83 sec
2015-12-22 08:51:38,416 Stage-1 map = 100%, reduce = 0%, Cumulative CPU 0.83 sec
2015-12-22 08:51:39,423 Stage-1 map = 100%, reduce = 100%, Cumulative CPU 1.43 sec
2015-12-22 08:51:40,429 Stage-1 map = 100%, reduce = 100%, Cumulative CPU 1.43 sec
2015-12-22 08:51:41,439 Stage-1 map = 100%, reduce = 100%, Cumulative CPU 1.43 sec
MapReduce Total cumulative CPU time: 1 seconds 430 msec
Ended Job = job_201512211113_0011
MapReduce Jobs Launched:
Job 0: Map: 1 Reduce: 1 Cumulative CPU: 1.43 sec HDFS Read: 0 HDFS Write: 0 SUCCESS
Total MapReduce CPU Time Spent: 1 seconds 430 msec
OK
2015-12-04 00:03:34
Time taken: 8.632 seconds

5） 为什么这样不行？

hive> select * from rushi_target where load_ts= ${hiveconf:maxtimevar};
FAILED: ParseException line 1:42 cannot recognize input near 'select' 'max' '(' in expression specification

6） 这不会引发任何错误，但也不会给出任何输出：

hive> select * from rushi_target where load_ts = '${hiveconf:maxtimevar}';
Total MapReduce jobs = 1
Launching Job 1 out of 1
Number of reduce tasks is set to 0 since there's no reduce operator
Starting Job = job_201512211113_0025, Tracking URL = http://0.0.0.0:50030/jobdetails.jsp?jobid=job_201512211113_0025
Kill Command = /usr/lib/hadoop/bin/hadoop job -Dmapred.job.tracker=0.0.0.0:8021 -kill job_201512211113_0025
Hadoop job information for Stage-1: number of mappers: 1; number of reducers: 0
2015-12-22 10:16:56,666 Stage-1 map = 0%, reduce = 0%
2015-12-22 10:16:58,675 Stage-1 map = 100%, reduce = 0%, Cumulative CPU 0.39 sec
2015-12-22 10:16:59,686 Stage-1 map = 100%, reduce = 0%, Cumulative CPU 0.39 sec
2015-12-22 10:17:00,696 Stage-1 map = 100%, reduce = 100%, Cumulative CPU 0.39 sec
MapReduce Total cumulative CPU time: 390 msec
Ended Job = job_201512211113_0025
MapReduce Jobs Launched:
Job 0: Map: 1 Cumulative CPU: 0.39 sec HDFS Read: 0 HDFS Write: 0 SUCCESS
Total MapReduce CPU Time Spent: 390 msec
OK
Time taken: 6.568 seconds
hive>

我想使用hive变量 `maxtimevar` 在 `SELECT` 查询。请建议如何使用。

配置单元变量的用法是传递值而不是查询。
你的问题 select * from rushi_target where load_ts = '${hiveconf:maxtimevar}'; 正在评估 select * from rushi_target where load_ts = 'select max(load_ts) from rushi_target' . 它正在寻找带有load\ts的数据作为查询，显然不会有带有这些值的数据。
这张是5号的， select * from rushi_target where load_ts= ${hiveconf:maxtimevar}; 将被评估为 select * from rushi_target where load_ts= select max(load_ts) from rushi_target; . 这是无效的查询，因此由于语法错误而失败。
您需要这样编写查询。

select rt.* from rushi_target rt 
join 
(select max(load_ts) max_load_ts from rushi_target) rt1 
on 1=1 
where rt.load_ts = rt1.max_load_ts;

配置单元变量用法

1条答案

相关问题

热门标签

最新问答