Support Questions
Find answers, ask questions, and share your expertise
Announcements
Alert: Welcome to the Unified Cloudera Community. Former HCC members be sure to read and learn how to activate your account here.

Unable to Store Results from Hive Query

Highlighted

Unable to Store Results from Hive Query

New Contributor

Hello Experts,


I have created the following Hadoop Hive Script.

The script is attempting to store the results of a query into the following location:

LOCATION 'wasb://adfgetstarted@geogstoreacct.blob.core.windows.net/sampleout/';

However, I keep on getting the following error:

FAILED: ParseException line 9:0 Failed to recognize predicate 'ROW'. Failed rule: 'identifier' in table or column identifier
18/01/30 16:08:06 [main]: ERROR ql.Driver: FAILED: ParseException line 9:0 Failed to recognize predicate 'ROW'. Failed rule: 'identifier' in table or column identifier
org.apache.hadoop.hive.ql.parse.ParseException: line 9:0 Failed to recognize predicate 'ROW'. Failed rule: 'identifier' in table or column identifier

The Hive script is as follows:

DROP TABLE IF EXISTS geography;
CREATE EXTERNAL TABLE geography
(
 anonid INT,
 eprofileclass INT,
 fueltypes STRING,
 acorn_category INT,
 acorn_group STRING,
 acorn_type INT,
 nuts4 STRING,
 lacode STRING,
 nuts1 STRING,
 gspgroup STRING,
 ldz STRING,
 gas_elec STRING,
 gas_tout STRING
)
ROW FORMAT DELIMITED
 FIELDS TERMINATED BY ','
STORED AS TEXTFILE
LOCATION 'wasb://adfgetstarted@geogstoreacct.blob.core.windows.net/sampleout/'
TBLPROPERTIES ("skip.header.line.count" = "1");
Create table acorn_category_frequency
 as
select acorn_category,
 count(*) as acorn_categorycount
from geography
group by acorn_category,
ROW FORMAT DELIMITED
 FIELDS TERMINATED BY ','
STORED AS TEXTFILE
LOCATION 'wasb://adfgetstarted@geogstoreacct.blob.core.windows.net/sampleout/';

Any help in figuring out how to store the results from the query would be much appreciated.

Cheers

Carlton

4 REPLIES 4

Re: Unable to Store Results from Hive Query

@Carlton Patterson

The problem may exist here

group by acorn_category,
ROW FORMAT DELIMITED

I don't believe you should have a comma before ROW there.

Re: Unable to Store Results from Hive Query

New Contributor

Michael, thanks for responding. I will remove comma when I get back to my desk and see. I will let you know.

Thanks

Re: Unable to Store Results from Hive Query

New Contributor

Hi Michael,

I tried your suggestion, but it didn't work

I received the following error message:

Time taken: 3.473 seconds
FAILED: ParseException line 9:0 missing EOF at 'ROW' near 'acorn_category'
18/01/30 18:42:11 [main]: ERROR ql.Driver: FAILED: ParseException line 9:0 missing EOF at 'ROW' near 'acorn_category'
org.apache.hadoop.hive.ql.parse.ParseException: line 9:0 missing EOF at 'ROW' near 'acorn_category'
at org.apache.hadoop.hive.ql.parse.ParseDriver.parse(ParseDriver.java:213)

I made the change as suggested below

DROP TABLE IF EXISTS geography;
CREATE EXTERNAL TABLE geography
(
 anonid INT,
 eprofileclass INT,
 fueltypes STRING,
 acorn_category INT,
 acorn_group STRING,
 acorn_type INT,
 nuts4 STRING,
 lacode STRING,
 nuts1 STRING,
 gspgroup STRING,
 ldz STRING,
 gas_elec STRING,
 gas_tout STRING
)
ROW FORMAT DELIMITED
 FIELDS TERMINATED BY ','
STORED AS TEXTFILE
LOCATION 'wasb://adfgetstarted@geogstoreacct.blob.core.windows.net/sampleout/'
TBLPROPERTIES ("skip.header.line.count" = "1");
Create table acorn_category_frequency
 as
select acorn_category,
 count(*) as acorn_categorycount
from geography
group by acorn_category
ROW FORMAT DELIMITED
 FIELDS TERMINATED BY ','
STORED AS TEXTFILE
LOCATION 'wasb://adfgetstarted@geogstoreacct.blob.core.windows.net/sampleout/';

Any other suggestions

Re: Unable to Store Results from Hive Query

Expert Contributor

@Carlton Patterson

pls try the below create statement for acorn_category_frequency table creation

Create table acorn_category_frequency
ROW FORMAT DELIMITED
 FIELDS TERMINATED BY ','
STORED AS TEXTFILE
LOCATION 'wasb://adfgetstarted@geogstoreacct.blob.core.windows.net/sampleout/'
as
select acorn_category,
 count(*) as acorn_categorycount
from geography
group by acorn_category<br>