rtrivedi I have modified the script to add the following line of code: TBLPROPERTIES ("skip.header.line.count" = "1"); The script now looks like the following: DROP TABLE IF EXISTS HiveSampleIn; CREATE EXTERNAL TABLE HiveSampleIn ( 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 ',' LINES TERMINATED BY '10' STORED AS TEXTFILE LOCATION 'wasb://adfgetstarted@geogstoreacct.blob.core.windows.net/samplein/'; TBLPROPERTIES ("skip.header.line.count" = "1"); DROP TABLE IF EXISTS HiveSampleOut; CREATE EXTERNAL TABLE HiveSampleOut ( acorn_category int, acorn_categorycount int ) ROW FORMAT DELIMITED FIELDS TERMINATED BY ',' LINES TERMINATED BY '10' STORED AS TEXTFILE LOCATION 'wasb://adfgetstarted@geogstoreacct.blob.core.windows.net/sampleout/'; INSERT OVERWRITE TABLE HiveSampleOut Select acorn_category, count(*) as acorn_categorycount FROM HiveSampleIn Group by acorn_category; However, I keep on getting the following error: FAILED: ParseException line 2:0 cannot recognize input near 'TBLPROPERTIES' '(' '"skip.header.line.count"' 18/01/31 11:28:20 [main]: ERROR ql.Driver: FAILED: ParseException line 2:0 cannot recognize input near 'TBLPROPERTIES' '(' '"skip.header.line.count"' org.apache.hadoop.hive.ql.parse.ParseException: line 2:0 cannot recognize input near 'TBLPROPERTIES' '(' '"skip.header.line.count"' Your help with this last error will complete all that I need to know to fix this issue. Much appreciated ... View more

rtrivedi That worked perfectly thanks so much.. ... View more

Hi Naresh, Thanks for getting in touch. Can you think of any reason why tblproperties ("skip.header.line.count"="2"); is throwing the error? And any suggestions on how to fix it? Much appreciated. Carlton ... View more

Hi rtrivedi Thanks for getting in touch. Im very new to Hive, tbh, I took the script from the following link https://bigdata.ukdataservice.ac.uk/media/604332/hiveql-queries.pdf#page5 In the link, the author suggests using the following line of code to fix the problem TBLPROPERTIES ("skip.header.line.count" = "1"); But I can't get the code to work with the Hive script. Therefore, if you could show me how implement the code or let me know how to define a table with 2 fields as you suggested that would be very much appreciated. Cheers Carlton ... View more

Hello Community, While waiting for some assistance, I have created the the following HQL script, but I'm still have the problem: DROP TABLE IF EXISTS HiveSampleIn; CREATE EXTERNAL TABLE HiveSampleIn ( 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/samplein/'; tblproperties ("skip.header.line.count"="2"); DROP TABLE IF EXISTS HiveSampleOut; CREATE EXTERNAL TABLE HiveSampleOut ( acorn_category int ) ROW FORMAT DELIMITED FIELDS TERMINATED BY ',' STORED AS TEXTFILE LOCATION 'wasb://adfgetstarted@geogstoreacct.blob.core.windows.net/sampleout/'; INSERT OVERWRITE TABLE HiveSampleOut Select acorn_category FROM HiveSampleIn Group by acorn_category; I'm getting the following error: Time taken: 2.663 seconds FAILED: ParseException line 21:44 missing EOF at ',' near ')' 18/01/29 21:37:15 [main]: ERROR ql.Driver: FAILED: ParseException line 21:44 missing EOF at ',' near ')' org.apache.hadoop.hive.ql.parse.ParseException: line 21:44 missing EOF at ',' near ')' Any help with this will be greatly appreciated. Cheers Carlton ... View more

Hello Community, I have updated the script as follows: DROP TABLE IF EXISTS HiveSampleIn; CREATE EXTERNAL TABLE HiveSampleIn ( 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 ',' LINES TERMINATED BY '10' STORED AS TEXTFILE LOCATION 'wasb://adfgetstarted@geogstoreacct.blob.core.windows.net/samplein/' TBLPROPERTIES ("skip.header.line.count" = "1"); DROP TABLE IF EXISTS HiveSampleOut; CREATE EXTERNAL TABLE HiveSampleOut ( acorn_category int ) ROW FORMAT DELIMITED FIELDS TERMINATED BY ',' LINES TERMINATED BY '10' STORED AS TEXTFILE LOCATION 'wasb://adfgetstarted@geogstoreacct.blob.core.windows.net/sampleout/' INSERT OVERWRITE TABLE HiveSampleOut Select acorn_category FROM HiveSampleIn Group by acorn_category TBLPROPERTIES ("skip.header.line.count" = "1"); However, I'm getting the following error message: Time taken: 0.499 seconds FAILED: ParseException line 7:0 missing EOF at 'INSERT' near ''wasb://adfgetstarted@geogstoreacct.blob.core.windows.net/sampleout/'' 18/01/29 15:34:09 [main]: ERROR ql.Driver: FAILED: ParseException line 7:0 missing EOF at 'INSERT' near ''wasb://adfgetstarted@geogstoreacct.blob.core.windows.net/sampleout/'' org.apache.hadoop.hive.ql.parse.ParseException: line 7:0 missing EOF at 'INSERT' near ''wasb://adfgetstarted@geogstoreacct.blob.core.windows.net/sampleout/'' Any help will be greatly appreciated. Cheers ... View more

Hi Aditya, I tried your suggestion, but now I'm getting the following error: FAILED: IllegalArgumentException java.net.URISyntaxException: Illegal character in scheme name at index 0: wasb://adfgetstarted@geogstoreacct.blob.core.windows.net/samplein 18/01/29 12:18:20 [main]: ERROR ql.Driver: FAILED: IllegalArgumentException java.net.URISyntaxException: Illegal character in scheme name at index 0: wasb://adfgetstarted@geogstoreacct.blob.core.windows.net/samplein java.lang.IllegalArgumentException: java.net.URISyntaxException: Illegal character in scheme name at index 0: wasb://adfgetstarted@geogstoreacct.blob.core.windows.net/samplein The new code looks like the following: DROP TABLE IF EXISTS HiveSampleIn; CREATE EXTERNAL TABLE HiveSampleIn ( 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 ',' LINES TERMINATED BY '10' STORED AS TEXTFILE LOCATION ' wasb://adfgetstarted@geogstoreacct.blob.core.windows.net/samplein/' TBLPROPERTIES ("skip.header.line.count" = "1"); DROP TABLE IF EXISTS HiveSampleOut; CREATE EXTERNAL TABLE HiveSampleOut ( acorn_category int ) ROW FORMAT DELIMITED FIELDS TERMINATED BY ',' LINES TERMINATED BY '10' STORED AS TEXTFILE LOCATION ' wasb://adfgetstarted@geogstoreacct.blob.core.windows.net/sampleout/' TBLPROPERTIES ("skip.header.line.count" = "1"); INSERT OVERWRITE TABLE HiveSampleOut Select acorn_category, count(*) as acorn_categorycount FROM HiveSampleIn Group by acorn_category ... View more

Aditya, Thanks for getting in touch, I change script to the following: DROP TABLE IF EXISTS HiveSampleIn; CREATE EXTERNAL TABLE HiveSampleIn ( 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 ',' LINES TERMINATED BY '10' STORED AS TEXTFILE LOCATION ' wasb://adfgetstarted@geogstoreacct.blob.core.windows.net/samplein/'; TBLPROPERTIES ("skip.header.line.count" = "1"); DROP TABLE IF EXISTS HiveSampleOut; CREATE EXTERNAL TABLE HiveSampleOut ( acorn_category int ) ROW FORMAT DELIMITED FIELDS TERMINATED BY ',' LINES TERMINATED BY '10' STORED AS TEXTFILE LOCATION ' wasb://adfgetstarted@geogstoreacct.blob.core.windows.net/sampleout/'; TBLPROPERTIES ("skip.header.line.count" = "1"); INSERT OVERWRITE TABLE HiveSampleOut Select acorn_category, count(*) as acorn_categorycount FROM HiveSampleIn Group by acorn_category But I'm now getting the following errors: Time taken: 2.684 seconds FAILED: ParseException line 18:46 character '<EOF>' not supported here 18/01/29 11:37:24 [main]: ERROR ql.Driver: FAILED: ParseException line 18:46 character '<EOF>' not supported here org.apache.hadoop.hive.ql.parse.ParseException: line 18:46 character '<EOF>' not supported here ... View more