Hive Action Failing in Oozie

classic Classic list List threaded Threaded
4 messages Options
Reply | Threaded
Open this post in threaded view
|

Hive Action Failing in Oozie

Dave Cardwell
Hello there,

I have an Oozie workflow that is failing on a Hive action with the following error:

FAILED: SemanticException [Error 10001]: Table not found attempted_calls_import_raw_logs_named_route_name

If I run the query file from the command line (as described in the map task log), it works fine:

hive --hivevar WORKING_DIR=/user/lpadm/working/attempted_calls --hivevar TABLE_NAME=attempted_calls_import_raw_logs_named_route_name -f hive-named_route_name.q

Below are the contents of hive-named_route_name.q and the full output of the failing map task log can be found at https://gist.github.com/fc665aacb3aa9c663f04 — any ideas?

DROP TABLE IF EXISTS ${TABLE_NAME};

CREATE EXTERNAL TABLE ${TABLE_NAME} (
    named_route_name_id bigint,
    prefix string,
    handle string,
    name string,
    iso2 string,
    country_handle string,
    country_name string,
    dial_code string,
    category string
)
    ROW FORMAT DELIMITED
        FIELDS TERMINATED BY '\001'
        COLLECTION ITEMS TERMINATED BY '\002'
        MAP KEYS TERMINATED BY '\003'
    STORED AS SequenceFile
    LOCATION '${WORKING_DIR}/${TABLE_NAME}'
;

SET hive.exec.compress.output=true;
SET io.seqfile.compression.type=BLOCK;

INSERT INTO TABLE ${TABLE_NAME}
    SELECT nrn.named_route_name_id,
           nrn.prefix,
           nrn.handle,
           nrn.name,
           nrn.iso2,
           nrn.country_handle,
           nrn.country_name,
           nrn.dial_code,
           nrn.category
      FROM named_route_name nrn
  ORDER BY nrn.prefix ASC
;

-- 
Best wishes,
Dave Cardwell.


Reply | Threaded
Open this post in threaded view
|

Re: Hive Action Failing in Oozie

Harsh J-2
A 'table not found' would usually mean that the Hive Action's config
is not properly pointing to the right metastore you usually use for
Hive.

P.s. Both Hive and Oozie have separate user mailing lists. I encourage
you to post specific questions to the components' own lists for
better/faster answers and keeping content relevant :)

On Thu, Dec 13, 2012 at 3:57 AM, Dave Cardwell <[hidden email]> wrote:

> Hello there,
>
> I have an Oozie workflow that is failing on a Hive action with the following
> error:
>
>> FAILED: SemanticException [Error 10001]: Table not found
>> attempted_calls_import_raw_logs_named_route_name
>
>
> If I run the query file from the command line (as described in the map task
> log), it works fine:
>
> hive --hivevar WORKING_DIR=/user/lpadm/working/attempted_calls --hivevar
> TABLE_NAME=attempted_calls_import_raw_logs_named_route_name -f
> hive-named_route_name.q
>
> Below are the contents of hive-named_route_name.q and the full output of the
> failing map task log can be found at
> https://gist.github.com/fc665aacb3aa9c663f04 — any ideas?
>
> DROP TABLE IF EXISTS ${TABLE_NAME};
>
> CREATE EXTERNAL TABLE ${TABLE_NAME} (
>     named_route_name_id bigint,
>     prefix string,
>     handle string,
>     name string,
>     iso2 string,
>     country_handle string,
>     country_name string,
>     dial_code string,
>     category string
> )
>     ROW FORMAT DELIMITED
>         FIELDS TERMINATED BY '\001'
>         COLLECTION ITEMS TERMINATED BY '\002'
>         MAP KEYS TERMINATED BY '\003'
>     STORED AS SequenceFile
>     LOCATION '${WORKING_DIR}/${TABLE_NAME}'
> ;
>
> SET hive.exec.compress.output=true;
> SET io.seqfile.compression.type=BLOCK;
>
> INSERT INTO TABLE ${TABLE_NAME}
>     SELECT nrn.named_route_name_id,
>            nrn.prefix,
>            nrn.handle,
>            nrn.name,
>            nrn.iso2,
>            nrn.country_handle,
>            nrn.country_name,
>            nrn.dial_code,
>            nrn.category
>       FROM named_route_name nrn
>   ORDER BY nrn.prefix ASC
> ;
>
> --
> Best wishes,
> Dave Cardwell.
>
> http://davecardwell.co.uk/
>



--
Harsh J
Reply | Threaded
Open this post in threaded view
|

Re: Hive Action Failing in Oozie

Dave Cardwell
Thank you for the suggestion.

From the log output javax.jdo.option.ConnectionDriverName appears to be set to com.mysql.jdbc.Driver, with the correct IP in javax.jdo.option.ConnectionURL. I have copied hive-site.xml from the local machine into Hadoop and instructed Oozie to use that, which it appears to be.

The first step is to drop the table it’s complaining about (if it doesn’t exist), and then to create it, and finally to select from a “named_route_name” table into it. If it were using the wrong metastore wouldn’t the first two steps pass, but fail and complain about the missing “named_route_name” table in the third step?

I will try my luck on the Oozie mailing list as well, thanks.

-- 
Best wishes,
Dave Cardwell.




On 12 December 2012 17:38, Harsh J <[hidden email]> wrote:
A 'table not found' would usually mean that the Hive Action's config
is not properly pointing to the right metastore you usually use for
Hive.

P.s. Both Hive and Oozie have separate user mailing lists. I encourage
you to post specific questions to the components' own lists for
better/faster answers and keeping content relevant :)

On Thu, Dec 13, 2012 at 3:57 AM, Dave Cardwell <[hidden email]> wrote:
> Hello there,
>
> I have an Oozie workflow that is failing on a Hive action with the following
> error:
>
>> FAILED: SemanticException [Error 10001]: Table not found
>> attempted_calls_import_raw_logs_named_route_name
>
>
> If I run the query file from the command line (as described in the map task
> log), it works fine:
>
> hive --hivevar WORKING_DIR=/user/lpadm/working/attempted_calls --hivevar
> TABLE_NAME=attempted_calls_import_raw_logs_named_route_name -f
> hive-named_route_name.q
>
> Below are the contents of hive-named_route_name.q and the full output of the
> failing map task log can be found at
> https://gist.github.com/fc665aacb3aa9c663f04 — any ideas?
>
> DROP TABLE IF EXISTS ${TABLE_NAME};
>
> CREATE EXTERNAL TABLE ${TABLE_NAME} (
>     named_route_name_id bigint,
>     prefix string,
>     handle string,
>     name string,
>     iso2 string,
>     country_handle string,
>     country_name string,
>     dial_code string,
>     category string
> )
>     ROW FORMAT DELIMITED
>         FIELDS TERMINATED BY '\001'
>         COLLECTION ITEMS TERMINATED BY '\002'
>         MAP KEYS TERMINATED BY '\003'
>     STORED AS SequenceFile
>     LOCATION '${WORKING_DIR}/${TABLE_NAME}'
> ;
>
> SET hive.exec.compress.output=true;
> SET io.seqfile.compression.type=BLOCK;
>
> INSERT INTO TABLE ${TABLE_NAME}
>     SELECT nrn.named_route_name_id,
>            nrn.prefix,
>            nrn.handle,
>            nrn.name,
>            nrn.iso2,
>            nrn.country_handle,
>            nrn.country_name,
>            nrn.dial_code,
>            nrn.category
>       FROM named_route_name nrn
>   ORDER BY nrn.prefix ASC
> ;
>
> --
> Best wishes,
> Dave Cardwell.
>
> http://davecardwell.co.uk/
>



--
Harsh J

Reply | Threaded
Open this post in threaded view
|

Re: Hive Action Failing in Oozie

Dave Cardwell
I spent a couple of hours digging into this with a colleague today, and in the end we’ve discovered that the issue is due to the MySQL-Connector JAR not being available.

We fixed this by copying it from the Sqoop libs folder into the Oozie shared libs in HDFS:

/usr/bin/sudo -u hdfs hadoop fs -copyFromLocal /usr/lib/sqoop/lib/mysql-connector-java-5.1.21-bin.jar /user/oozie/share/lib/hive/
/usr/bin/sudo -u hdfs hadoop fs -chown oozie:oozie /user/oozie/share/lib/hive/mysql-connector-java-5.1.21-bin.jar

I found the error message (“FAILED: SemanticException [Error 10001]: Table not found attempted_calls_import_raw_logs_named_route_name”) was not at all intuitive for figuring this out, but an informed hunch from my coworker got us there in the end.

Thank you for taking a look for me.

-- 
Best wishes,
Dave Cardwell.




On 12 December 2012 18:12, Dave Cardwell <[hidden email]> wrote:
Thank you for the suggestion.

From the log output javax.jdo.option.ConnectionDriverName appears to be set to com.mysql.jdbc.Driver, with the correct IP in javax.jdo.option.ConnectionURL. I have copied hive-site.xml from the local machine into Hadoop and instructed Oozie to use that, which it appears to be.

The first step is to drop the table it’s complaining about (if it doesn’t exist), and then to create it, and finally to select from a “named_route_name” table into it. If it were using the wrong metastore wouldn’t the first two steps pass, but fail and complain about the missing “named_route_name” table in the third step?

I will try my luck on the Oozie mailing list as well, thanks.


-- 
Best wishes,
Dave Cardwell.




On 12 December 2012 17:38, Harsh J <[hidden email]> wrote:
A 'table not found' would usually mean that the Hive Action's config
is not properly pointing to the right metastore you usually use for
Hive.

P.s. Both Hive and Oozie have separate user mailing lists. I encourage
you to post specific questions to the components' own lists for
better/faster answers and keeping content relevant :)

On Thu, Dec 13, 2012 at 3:57 AM, Dave Cardwell <[hidden email]> wrote:
> Hello there,
>
> I have an Oozie workflow that is failing on a Hive action with the following
> error:
>
>> FAILED: SemanticException [Error 10001]: Table not found
>> attempted_calls_import_raw_logs_named_route_name
>
>
> If I run the query file from the command line (as described in the map task
> log), it works fine:
>
> hive --hivevar WORKING_DIR=/user/lpadm/working/attempted_calls --hivevar
> TABLE_NAME=attempted_calls_import_raw_logs_named_route_name -f
> hive-named_route_name.q
>
> Below are the contents of hive-named_route_name.q and the full output of the
> failing map task log can be found at
> https://gist.github.com/fc665aacb3aa9c663f04 — any ideas?
>
> DROP TABLE IF EXISTS ${TABLE_NAME};
>
> CREATE EXTERNAL TABLE ${TABLE_NAME} (
>     named_route_name_id bigint,
>     prefix string,
>     handle string,
>     name string,
>     iso2 string,
>     country_handle string,
>     country_name string,
>     dial_code string,
>     category string
> )
>     ROW FORMAT DELIMITED
>         FIELDS TERMINATED BY '\001'
>         COLLECTION ITEMS TERMINATED BY '\002'
>         MAP KEYS TERMINATED BY '\003'
>     STORED AS SequenceFile
>     LOCATION '${WORKING_DIR}/${TABLE_NAME}'
> ;
>
> SET hive.exec.compress.output=true;
> SET io.seqfile.compression.type=BLOCK;
>
> INSERT INTO TABLE ${TABLE_NAME}
>     SELECT nrn.named_route_name_id,
>            nrn.prefix,
>            nrn.handle,
>            nrn.name,
>            nrn.iso2,
>            nrn.country_handle,
>            nrn.country_name,
>            nrn.dial_code,
>            nrn.category
>       FROM named_route_name nrn
>   ORDER BY nrn.prefix ASC
> ;
>
> --
> Best wishes,
> Dave Cardwell.
>
> http://davecardwell.co.uk/
>



--
Harsh J