How to skip headers when reading a CSV file in S3 and creating a table in AWS Athena

0 votes

I am trying to read csv file from s3 bucket and create a table in AWS Athena. My table when created is unable to skip the header information of my CSV file.

Query Example :

CREATE EXTERNAL TABLE IF NOT EXISTS table_name (   `event_type_id`
     string,   `customer_id` string,   `date` string,   `email` string )
     ROW FORMAT SERDE  'org.apache.hadoop.hive.serde2.OpenCSVSerde' 
     WITH
     SERDEPROPERTIES (   "separatorChar" = "|",   "quoteChar"     = "\"" )
     LOCATION 's3://location/' 
     TBLPROPERTIES ("skip.header.line.count"="1");
This doesn't seem to work. Is there any other way that I could get through this?
Sep 4, 2018 in AWS by datageek
• 2,530 points
11,450 views

2 answers to this question.

0 votes

This is a known deficiency. The best method I've seen was tweeted by Eric Hammond:

...WHERE date NOT LIKE '#%'

This appears to skip header lines during a Query. I'm not sure how it works, but it might be a method for skipping NULLs.

To know more about Migrating to AWS, join our AWS Cloud Migration Training. Also, If you wish to master the principles in AWS and work on step-by-step tasks that have significant relevance for the world of business, industry professionals developed the AWS syllabus.

answered Sep 4, 2018 by Archana
• 4,170 points
+2 votes
Thanks for the answer.

This should be clear & prominent in the aws doco, but unfortunately is not !
answered Aug 14, 2019 by athenauserz

Related Questions In AWS

0 votes
1 answer

How to download the latest file in a S3 bucket using AWS CLI?

You can use the below command $ aws ...READ MORE

answered Sep 6, 2018 in AWS by Archana
• 4,170 points
19,412 views
+1 vote
2 answers

How to read a csv file stored in Amazon S3 using csv.DictReader

The code would be something like this: import ...READ MORE

answered Oct 25, 2018 in AWS by Archana
• 5,640 points
54,008 views
0 votes
0 answers

How to upload a file in to aws s3 by using programmatically??

Sep 13, 2019 in AWS by anonymous

closed Sep 16, 2019 by Kalgi 3,754 views
0 votes
1 answer

Unziiping a tar.gz file in aws s3 bucket and upload it back to s3 using lambda

Hi@khyati, You can do your task using lambda. ...READ MORE

answered Dec 3, 2020 in AWS by MD
• 95,440 points
18,258 views
0 votes
1 answer

How to upload a file from S3 in Lambda?

Yes there is an option where you ...READ MORE

answered Jan 24, 2019 in AWS by Priyaj
• 58,090 points
4,919 views
0 votes
1 answer

How to create a S3 bucket in AWS Console?

To create a S3 bucket in AWS, ...READ MORE

answered Feb 13, 2019 in AWS by Priyaj
• 58,090 points
1,015 views
webinar REGISTER FOR FREE WEBINAR X
REGISTER NOW
webinar_success Thank you for registering Join Edureka Meetup community for 100+ Free Webinars each month JOIN MEETUP GROUP