Created 01-30-2018 04:19 PM
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
Created 01-30-2018 04:34 PM
The problem may exist here
group by acorn_category, ROW FORMAT DELIMITED
I don't believe you should have a comma before ROW there.
Created 01-30-2018 05:01 PM
Michael, thanks for responding. I will remove comma when I get back to my desk and see. I will let you know.
Thanks
Created 01-30-2018 06:52 PM
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
Created 01-31-2018 09:09 AM
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>