hive底层可以查询数据,但是插入到目标表时候没有结果

昨夜风魂 发布于 2013/09/06 14:20
阅读 347
收藏 0

linux下。hive 0.11.0版本

select A.key key,
                 A.RECORDTIME RECORDTIME,
                 A.CONTENTID CONTENTID,
                 cast(A.RN_1 as int) RN_1,
                 cast(A.RN_2 as int) RN_2,
                 cast(B.RN_3 as int) RN_3
           from
           (SELECT key,
                 RECORDTIME,
                 CONTENTID,
                 ROW_NUMBER() OVER(PARTITION BY key ORDER BY RECORDTIME ASC) RN_1,
                 ROW_NUMBER() OVER(PARTITION BY key ORDER BY RECORDTIME DESC) RN_2                 
            FROM ODS_IREAD.IREAD_COMMENT
           WHERE RECORD_DAY ='20130812'
             AND SUBSTR(key,1,1) <> '9')A
             join 
             (SELECT CONTENTID,
                 COUNT(DISTINCT key) RN_3
            FROM ODS_IREAD.IREAD_COMMENT
           WHERE RECORD_DAY ='20130812'
             AND SUBSTR(key,1,1) <> '9'
             group by CONTENTID )B
             on (A.CONTENTID=B.CONTENTID)

执行上面的语句是可以查询到数据的。

插入到目标表时候,无报错信息。但是目标表无任何数据。

create table test.tmp_test
as
select A.key key,
                 A.RECORDTIME RECORDTIME,
                 A.CONTENTID CONTENTID,
                 cast(A.RN_1 as int) RN_1,
                 cast(A.RN_2 as int) RN_2,
                 cast(B.RN_3 as int) RN_3
           from
           (SELECT key,
                 RECORDTIME,
                 CONTENTID,
                 ROW_NUMBER() OVER(PARTITION BY key ORDER BY RECORDTIME ASC) RN_1,
                 ROW_NUMBER() OVER(PARTITION BY key ORDER BY RECORDTIME DESC) RN_2                 
            FROM ODS_IREAD.IREAD_COMMENT
           WHERE RECORD_DAY ='20130812'
             AND SUBSTR(key,1,1) <> '9')A
             join 
             (SELECT CONTENTID,
                 COUNT(DISTINCT key) RN_3
            FROM ODS_IREAD.IREAD_COMMENT
           WHERE RECORD_DAY ='20130812'
             AND SUBSTR(key,1,1) <> '9'
             group by CONTENTID )B
             on (A.CONTENTID=B.CONTENTID)
--创建一个表。信息如下:

Ended Job = job_201308241420_1933
Stage-11 is filtered out by condition resolver.
20879 Rows loaded to hdfs://namenode:9000/tmp/hive-hadoop/hive_2013-09-06_10-15-07_521_7704171105143537871/-ext-10000
MapReduce Jobs Launched: 
Job 0: Map: 2  Reduce: 1   Cumulative CPU: 9.3 sec   HDFS Read: 2656543 HDFS Write: 104959 SUCCESS
Job 1: Map: 2  Reduce: 1   Cumulative CPU: 11.79 sec   HDFS Read: 2656543 HDFS Write: 1383576 SUCCESS
Job 2: Map: 1  Reduce: 1   Cumulative CPU: 10.44 sec   HDFS Read: 1384108 HDFS Write: 1410015 SUCCESS
Job 3: Map: 1   Cumulative CPU: 4.64 sec   HDFS Read: 1410547 HDFS Write: 905213 SUCCESS
Total MapReduce CPU Time Spent: 36 seconds 170 msec
OK
Time taken: 131.982 seconds
----再去查询创建的目标表,结果提示没有目标表。请大师没分析是哪里有问题。正常创建表以及使用as创建表都是可以的。


加载中
0
sdzzboy
sdzzboy

分成两步来操作试试:

第一步:create table tmp_test(....);

第二步:

insert overwrite table tmp_test

select * from

(

select A.key key,
                 A.RECORDTIME RECORDTIME,
                 A.CONTENTID CONTENTID,
                 cast(A.RN_1 as int) RN_1,
                 cast(A.RN_2 as int) RN_2,
                 cast(B.RN_3 as int) RN_3
           from
           (SELECT key,
                 RECORDTIME,
                 CONTENTID,
                 ROW_NUMBER() OVER(PARTITION BY key ORDER BY RECORDTIME ASC) RN_1,
                 ROW_NUMBER() OVER(PARTITION BY key ORDER BY RECORDTIME DESC) RN_2                 
            FROM ODS_IREAD.IREAD_COMMENT
           WHERE RECORD_DAY ='20130812'
             AND SUBSTR(key,1,1) <> '9')A
             join 
             (SELECT CONTENTID,
                 COUNT(DISTINCT key) RN_3
            FROM ODS_IREAD.IREAD_COMMENT
           WHERE RECORD_DAY ='20130812'
             AND SUBSTR(key,1,1) <> '9'
             group by CONTENTID )B
             on (A.CONTENTID=B.CONTENTID)

)TEMP;


返回顶部
顶部