Support Questions
Find answers, ask questions, and share your expertise

Unable to Store Results from Hive Query

Highlighted

Unable to Store Results from Hive Query

Explorer

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.

Highlighted

Re: Unable to Store Results from Hive Query

Explorer

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

Thanks

Highlighted

Re: Unable to Store Results from Hive Query

Explorer

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

Highlighted

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>