You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@hive.apache.org by Raj Hadoop <ha...@yahoo.com> on 2013/07/03 22:22:50 UTC
Issue with Oracle Hive Metastore (SEQUENCE_TABLE)
Hi,
When I installed Hive earlier on my machine I used a oracle hive meta script. Please find attached the script. HIVE worked fine for me on this box with no issues.
I am trying to install Hive on another machine in a different Oracle metastore. I executed the meta script but I am having issues with my hive on second box.
$ hive
WARNING: org.apache.hadoop.metrics.jvm.EventCounter is deprecated. Please use org.apache.hadoop.log.metrics.EventCounter in all the log4j.properties files.
Logging initialized using configuration in jar:file:/software/hadoop/hive/hive-0.9.0/lib/hive-common-0.9.0.jar!/hive-log4j.properties
Hive history file=/tmp/hadoop/hive_job_log_hadoop_201307031616_605717324.txt
hive> show tables;
FAILED: Error in metadata: javax.jdo.JDOException: Couldnt obtain a new sequence (unique id) : ORA-00942: table or view does not exist
NestedThrowables:
java.sql.SQLSyntaxErrorException: ORA-00942: table or view does not exist
FAILED: Execution Error, return code 1 from org.apache.hadoop.hive.ql.exec.DDLTask
I found the difference between the two meta stores and one table is missing in it. The table is SEQUENCE_TABLE. I do not know whether this table will be created automatically by Hive or should it be in the script.I dont remember what I did earlier and I am assuming I used the same script. Can any one had this issue earlier ? Please advise.
Also, Where to get the hive 0.9 oracle meta script?
Thanks,
Raj
Re: One query works the other does notŠany clues ?
Posted by Sanjay Subramanian <Sa...@wizecommerce.com>.
For the time being I have added the create HDFS dir in my hive script…got to keep moving on….cant wait for ideal solution :-) but would love to know the ideal solution !
!hdfs dfs -mkdir /user/beeswax/warehouse/impressions_hive_stats/outpdir_impressions_header/2013-07-01/record_counts
;
INSERT OVERWRITE DIRECTORY '/user/beeswax/warehouse/impressions_hive_stats/outpdir_impressions_header/2013-07-01/record_counts'
SELECT
'outpdir_impressions_header',
'2013-07-01',
'record_counts',
'all_servers',
count(*)
FROM
outpdir_impressions_header
WHERE
header_date_partition='2013-07-01'
;
From: Sanjay Subramanian <sa...@wizecommerce.com>>
Reply-To: "user@hive.apache.org<ma...@hive.apache.org>" <us...@hive.apache.org>>
Date: Wednesday, July 3, 2013 2:40 PM
To: "user@hive.apache.org<ma...@hive.apache.org>" <us...@hive.apache.org>>
Subject: One query works the other does notŠany clues ?
THIS FAILS
=========
INSERT OVERWRITE DIRECTORY '/user/beeswax/warehouse/impressions_hive_stats/outpdir_impressions_header/2013-07-01/record_counts' select 'outpdir_impressions_header', '2013-07-01', 'record_counts', 'all_servers', count(*) from outpdir_impressions_header where header_date_partition='2013-07-01'
13/07/03 14:28:31 ERROR exec.Task: Failed with exception Unable to rename: hdfs://thv-nn1.pv.sv.nextag.com:8020/tmp/hive-nextag/hive_2013-07-03_14-25-39_324_3631745157890476605/-ext-10000 to: /user/beeswax/warehouse/impressions_hive_stats/outpdir_impressions_header/2013-07-01/record_counts
org.apache.hadoop.hive.ql.metadata.HiveException: Unable to rename: hdfs://thv-nn1.pv.sv.nextag.com:8020/tmp/hive-nextag/hive_2013-07-03_14-25-39_324_3631745157890476605/-ext-10000 to: /user/beeswax/warehouse/impressions_hive_stats/outpdir_impressions_header/2013-07-01/record_counts
at org.apache.hadoop.hive.ql.exec.MoveTask.moveFile(MoveTask.java:95)
at org.apache.hadoop.hive.ql.exec.MoveTask.execute(MoveTask.java:159)
at org.apache.hadoop.hive.ql.exec.Task.executeTask(Task.java:153)
at org.apache.hadoop.hive.ql.exec.TaskRunner.runSequential(TaskRunner.java:57)
at org.apache.hadoop.hive.ql.Driver.launchTask(Driver.java:1331)
at org.apache.hadoop.hive.ql.Driver.execute(Driver.java:1117)
at org.apache.hadoop.hive.ql.Driver.run(Driver.java:950)
at org.apache.hadoop.hive.cli.CliDriver.processLocalCmd(CliDriver.java:258)
at org.apache.hadoop.hive.cli.CliDriver.processCmd(CliDriver.java:215)
at org.apache.hadoop.hive.cli.CliDriver.processLine(CliDriver.java:406)
at org.apache.hadoop.hive.cli.CliDriver.processLine(CliDriver.java:341)
at org.apache.hadoop.hive.cli.CliDriver.run(CliDriver.java:695)
at org.apache.hadoop.hive.cli.CliDriver.main(CliDriver.java:607)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
at java.lang.reflect.Method.invoke(Method.java:597)
at org.apache.hadoop.util.RunJar.main(RunJar.java:208)
FAILED: Execution Error, return code 1 from org.apache.hadoop.hive.ql.exec.MoveTask
THIS SUCCEEDS
=============
INSERT OVERWRITE DIRECTORY '/user/beeswax/warehouse/impressions_hive_stats/outpdir_impressions_header/record_counts/2013-07-01' select 'outpdir_impressions_header', '2013-07-01', 'record_counts', 'all_servers', count(*) from outpdir_impressions_header where header_date_partition='2013-07-01'
TABLE DEFINITION
================
CREATE EXTERNAL TABLE IF NOT EXISTS impressions_hive_stats(table_name STRING, aggregation_date STRING , metric_name STRING, metric_key STRING, metric_value BIGINT) PARTITIONED BY (table_name_partition STRING, aggregation_date_partition STRING , metric_name_partition STRING, metric_key_partition STRING) STORED AS INPUTFORMAT \"com.hadoop.mapred.DeprecatedLzoTextInputFormat\" OUTPUTFORMAT \"org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat\" ;
CONFIDENTIALITY NOTICE
======================
This email message and any attachments are for the exclusive use of the intended recipient(s) and may contain confidential and privileged information. Any unauthorized review, use, disclosure or distribution is prohibited. If you are not the intended recipient, please contact the sender by reply email and destroy all copies of the original message along with any attachments, from your computer system. If you are the intended recipient, please be advised that the content of this message is subject to access, review and disclosure by the sender's Email System Administrator.
CONFIDENTIALITY NOTICE
======================
This email message and any attachments are for the exclusive use of the intended recipient(s) and may contain confidential and privileged information. Any unauthorized review, use, disclosure or distribution is prohibited. If you are not the intended recipient, please contact the sender by reply email and destroy all copies of the original message along with any attachments, from your computer system. If you are the intended recipient, please be advised that the content of this message is subject to access, review and disclosure by the sender's Email System Administrator.
One query works the other does notŠany clues ?
Posted by Sanjay Subramanian <Sa...@wizecommerce.com>.
THIS FAILS
=========
INSERT OVERWRITE DIRECTORY '/user/beeswax/warehouse/impressions_hive_stats/outpdir_impressions_header/2013-07-01/record_counts' select 'outpdir_impressions_header', '2013-07-01', 'record_counts', 'all_servers', count(*) from outpdir_impressions_header where header_date_partition='2013-07-01'
13/07/03 14:28:31 ERROR exec.Task: Failed with exception Unable to rename: hdfs://thv-nn1.pv.sv.nextag.com:8020/tmp/hive-nextag/hive_2013-07-03_14-25-39_324_3631745157890476605/-ext-10000 to: /user/beeswax/warehouse/impressions_hive_stats/outpdir_impressions_header/2013-07-01/record_counts
org.apache.hadoop.hive.ql.metadata.HiveException: Unable to rename: hdfs://thv-nn1.pv.sv.nextag.com:8020/tmp/hive-nextag/hive_2013-07-03_14-25-39_324_3631745157890476605/-ext-10000 to: /user/beeswax/warehouse/impressions_hive_stats/outpdir_impressions_header/2013-07-01/record_counts
at org.apache.hadoop.hive.ql.exec.MoveTask.moveFile(MoveTask.java:95)
at org.apache.hadoop.hive.ql.exec.MoveTask.execute(MoveTask.java:159)
at org.apache.hadoop.hive.ql.exec.Task.executeTask(Task.java:153)
at org.apache.hadoop.hive.ql.exec.TaskRunner.runSequential(TaskRunner.java:57)
at org.apache.hadoop.hive.ql.Driver.launchTask(Driver.java:1331)
at org.apache.hadoop.hive.ql.Driver.execute(Driver.java:1117)
at org.apache.hadoop.hive.ql.Driver.run(Driver.java:950)
at org.apache.hadoop.hive.cli.CliDriver.processLocalCmd(CliDriver.java:258)
at org.apache.hadoop.hive.cli.CliDriver.processCmd(CliDriver.java:215)
at org.apache.hadoop.hive.cli.CliDriver.processLine(CliDriver.java:406)
at org.apache.hadoop.hive.cli.CliDriver.processLine(CliDriver.java:341)
at org.apache.hadoop.hive.cli.CliDriver.run(CliDriver.java:695)
at org.apache.hadoop.hive.cli.CliDriver.main(CliDriver.java:607)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
at java.lang.reflect.Method.invoke(Method.java:597)
at org.apache.hadoop.util.RunJar.main(RunJar.java:208)
FAILED: Execution Error, return code 1 from org.apache.hadoop.hive.ql.exec.MoveTask
THIS SUCCEEDS
=============
INSERT OVERWRITE DIRECTORY '/user/beeswax/warehouse/impressions_hive_stats/outpdir_impressions_header/record_counts/2013-07-01' select 'outpdir_impressions_header', '2013-07-01', 'record_counts', 'all_servers', count(*) from outpdir_impressions_header where header_date_partition='2013-07-01'
TABLE DEFINITION
================
CREATE EXTERNAL TABLE IF NOT EXISTS impressions_hive_stats(table_name STRING, aggregation_date STRING , metric_name STRING, metric_key STRING, metric_value BIGINT) PARTITIONED BY (table_name_partition STRING, aggregation_date_partition STRING , metric_name_partition STRING, metric_key_partition STRING) STORED AS INPUTFORMAT \"com.hadoop.mapred.DeprecatedLzoTextInputFormat\" OUTPUTFORMAT \"org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat\" ;
CONFIDENTIALITY NOTICE
======================
This email message and any attachments are for the exclusive use of the intended recipient(s) and may contain confidential and privileged information. Any unauthorized review, use, disclosure or distribution is prohibited. If you are not the intended recipient, please contact the sender by reply email and destroy all copies of the original message along with any attachments, from your computer system. If you are the intended recipient, please be advised that the content of this message is subject to access, review and disclosure by the sender's Email System Administrator.
Re: Issue with Oracle Hive Metastore (SEQUENCE_TABLE)
Posted by Darren Yin <da...@gmail.com>.
you can get the hive 0.9 oracle script here:
https://github.com/apache/hive/blob/trunk/metastore/scripts/upgrade/oracle/hive-schema-0.9.0.oracle.sql
On Wed, Jul 3, 2013 at 1:22 PM, Raj Hadoop <ha...@yahoo.com> wrote:
> Hi,
>
> When I installed Hive earlier on my machine I used a oracle hive meta
> script. Please find attached the script. HIVE worked fine for me on this
> box with no issues.
>
> I am trying to install Hive on another machine in a different Oracle
> metastore. I executed the meta script but I am having issues with my hive
> on second box.
>
> *$ hive
> WARNING: org.apache.hadoop.metrics.jvm.EventCounter is deprecated. Please
> use org.apache.hadoop.log.metrics.EventCounter in all the log4j.properties
> files.
> Logging initialized using configuration in
> jar:file:/software/hadoop/hive/hive-0.9.0/lib/hive-common-0.9.0.jar!/hive-log4j.properties
> Hive history
> file=/tmp/hadoop/hive_job_log_hadoop_201307031616_605717324.txt
> hive> show tables;
> FAILED: Error in metadata: javax.jdo.JDOException: Couldnt obtain a new
> sequence (unique id) : ORA-00942: table or view does not exist*
> *NestedThrowables:
> java.sql.SQLSyntaxErrorException: ORA-00942: table or view does not exist*
> *FAILED: Execution Error, return code 1 from
> org.apache.hadoop.hive.ql.exec.DDLTask*
> I found the difference between the two meta stores and one table is
> missing in it. The table is SEQUENCE_TABLE. I do not know whether this
> table will be created automatically by Hive or should it be in the script.I
> dont remember what I did earlier and I am assuming I used the same script.
> Can any one had this issue earlier ? Please advise.
>
> Also, Where to get the hive 0.9 oracle meta script?
>
> Thanks,
> Raj
>