Exercise 2 : Long time running + error from intermediate_access_logs table creation query

[#1]

There are a few other post on the same topic however none of the suggested fixes have worked. This query (which I am using HUE Hive) just hangs. I also started the Zookeeper as suggested in another post, but it did not help. I did let it run over night and it produced the following error:

Error while processing statement: FAILED: Error in acquiring locks: Locks on the underlying objects cannot be acquired. retry after some time

Anyone know a solution?

CREATE EXTERNAL TABLE intermediate_access_logs (
ip STRING,
date STRING,
method STRING,
url STRING,
http_version STRING,
code1 STRING,
code2 STRING,
dash STRING,
user_agent STRING)
ROW FORMAT SERDE 'org.apache.hadoop.hive.contrib.serde2.RegexSerDe'
WITH SERDEPROPERTIES (
'input.regex' = '([^ ]*) - - \\[([^\\]]*)\\] "([^\ ]*) ([^\ ]*) ([^\ ]*)" (\\d*) (\\d*) "([^"]*)" "([^"]*)"',
'output.format.string' = "%1$$s %2$$s %3$$s %4$$s %5$$s %6$$s %7$$s %8$$s %9$$s")
LOCATION '/user/hive/warehouse/original_access_logs';

[#2]

Hi jbatspv,

Have all instances of HiveServer2 been restarted already?

[#3]

In the Cloudera Management Service Actions under the CDH 5 Packages dashboard I restarted the service. This fixed the issue and I was able to complete this step successfully as well, but get another error described below.

CREATE EXTERNAL TABLE tokenized_access_logs (
ip STRING,
date STRING,
method STRING,
url STRING,
http_version STRING,
code1 STRING,
code2 STRING,
dash STRING,
user_agent STRING)
ROW FORMAT DELIMITED FIELDS TERMINATED BY ','
LOCATION '/user/hive/warehouse/tokenized_access_logs';

I did however get the following error when I hit this line of code. Any ideas?

ADD JAR {{lib_dir}}/hive/lib/hive-contrib.jar;

Error while processing statement: java.net.URISyntaxException: Illegal character in path at index 0: {{lib_dir}}/hive/lib/hive-contrib.jar

[#4]

Based on another article I have tried the following combinations but all issue and error that the hive-contrib.jar does not exist.

ADD JAR /hive/lib/hive-contrib.jar;

ADD JAR /home/cloudera/lib/hive-contrib.jar;

ADD JAR /opt/cloudera/parcels/CDH/lib/hive/hive-contrib.jar;

[#5]

Hi jbatspv,

Which code/script is running?

Does hive-contrib.jar exist under /lib/hive/lib?

[#6]

Which code/script is running? How can I tell?

I ran this but got similar error.

ADD JAR /lib/hive/lib/hive-contrib.jar;

Error while processing statement: /lib/hive/lib/hive-contrib.jar does not exist

Really appreciate you work with me on this!!

[#7]

Sure, glad to help.

Does hive-contrib.jar exist at /opt/cloudera/parcels/CDH/lib/hive/lib/hive-contrib.jar? You can navigate to this directory to verify.

Was the ADD JAR command run with Beeline?

What's the full error output?

[#8]

I am running this in HUE Hive ADD JAR /opt/cloudera/parcels/CDH/lib/hive/lib/hive-contrib.jar     Error while processing statement: /opt/cloudera/parcels/CDH/lib/hive/lib/hive-contrib.jar does not exist Hopefully the screen shot will come though, but when I do ls from terminal window I see the folder "parcels" but it is highlighted green. If I try ls /parcels its says No Such File or Directory

[#9 · Accepted solution]

I don't have time at the moment to check the QuickStart VM but let's check the /usr/lib/ hive/lib location (rather than ~/parcels or ~/lib). Also, the ADD JAR command has to be run from within Beeline.

[#10]

I found the file hive-contrib.jar the command ls /usr/lib/have/lib via the terminal window. I ran ADD JAR /usr/lib/hive/lib/hive-contrib.jar; from the HUE Hive and it worked! I also was able to run the last statement successfully INSERT OVERWRITE TABLE tokenized_access_logs SELECT * FROM intermediate_access_logs;

I have now been able to complete exercise 2!! 

Big thanks for the help!

[#11]

Absolutely! You could also mark the solution for anyone else that comes across this issue in the future.

Cheers

[#12]

I added the jar file and still my query is running for the last 8 minutes.

CREATE EXTERNAL TABLE intermediate_access_logs (
ip STRING,
date STRING,
method STRING,
url STRING,
http_version STRING,
code1 STRING,
code2 STRING,
dash STRING,
user_agent STRING)
ROW FORMAT SERDE 'org.apache.hadoop.hive.contrib.serde2.RegexSerDe'
WITH SERDEPROPERTIES (
'input.regex' = '([^ ]*) - - \\[([^\\]]*)\\] "([^\ ]*) ([^\ ]*) ([^\ ]*)" (\\d*) (\\d*) "([^"]*)" "([^"]*)"',
'output.format.string' = "%1$$s %2$$s %3$$s %4$$s %5$$s %6$$s %7$$s %8$$s %9$$s")
LOCATION '/user/hive/warehouse/original_access_logs';

Any other setting change needs to be done?

[#13]

I had the same problem - create external table intermediate_access_logs hanging.

The solution which helped in my case was increasing memory in my virtual machine (VirtualBox). I've set VM memory to 20GB. I did not experiment, I don't know what is the working minimum. What I can tell is that 20GB works.