Reply
New Contributor
Posts: 2
Registered: ‎01-26-2016

Syntax error while running the query in Hue-impala editor

 

Tutorial Exercise 2

 

Error while running the following query.

 

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';

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';

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

INSERT OVERWRITE TABLE tokenized_access_logs SELECT * FROM intermediate_access_logs;

 

AnalysisException: Syntax error in line 11: ROW FORMAT SERDE 'org.apache.hadoop.hiv... ^ Encountered: IDENTIFIER Expected: DELIMITED CAUSED BY: Exception: Syntax error.

 

Any ideas to resolve the syntax error.

New Contributor
Posts: 1
Registered: ‎02-19-2016

Re: Syntax error while running the query in Hue-impala editor

Use Hive Query Editor. It should work fine.

New Contributor
Posts: 2
Registered: ‎03-13-2016

Re: Syntax error while running the query in Hue-impala editor

I have experienced the same issue when tried to pass the cloudera tutorials.

By the way, I used the Live VM that cloudera provides and I suppose that tutorials (http://www.cloudera.com/developers/get-started-with-hadoop-tutorial/exercise-2.html) were tested using the same environments.

 

It would be nice if somebody will explain the root cause and also provide a workaround.

Thank you in advance

Cloudera Employee
Posts: 435
Registered: ‎07-12-2013

Re: Syntax error while running the query in Hue-impala editor

As the previous reply said, you need to use the Hive Query Editor. The
error shows up if you use the Impala Query Editor because you're using a
library written for Hive.
New Contributor
Posts: 2
Registered: ‎03-13-2016

Re: Syntax error while running the query in Hue-impala editor

Ok, if I select "Query Editors->Hive" and run the queries from that editor
it looks much better. I think the problem is that your assumption is that
everybody who starts this tutorial have some basic level of familiarity
with the tools. I believe that newbies like me will fall into this trap
unless you add a little bit more context or screenshots to this great
tutorial.

Thanks and kudos.
New Contributor
Posts: 3
Registered: ‎09-22-2017

Re: Syntax error while running the query in Hue-impala editor

I was able to create the table with Impala in Hue using this syntax:

 

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)
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';

 

I was using the backquote (backward apostrophe) ` on line 3

Also I have removed the row format clause since it is not needed in Impala (check the doc: https://www.cloudera.com/documentation/enterprise/5-5-x/topics/impala_create_table.html#create_table).

New Contributor
Posts: 3
Registered: ‎09-22-2017

Re: Syntax error while running the query in Hue-impala editor

[ Edited ]

We should use Hive Query Editor since some of the Examples syntax is not compatible with Impala and the tables are not created correctly.

 

i.e. https://community.cloudera.com/t5/Hadoop-101-Training-Quickstart/Unable-to-execute-add-jar-command-a...

 

Announcements