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,390 points
476 views

1 answer to this question.

Your answer

Your name to display (optional):
Privacy: Your email address will only be used for sending these notifications.
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.

answered Sep 4, 2018 by Archana
• 3,770 points

Related Questions In AWS

0 votes
1 answer

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,280 points
3,391 views
0 votes
1 answer
0 votes
1 answer
0 votes
1 answer

How to create a DynamoDB table in AWS?

Creating a DynamoDB table is made very ...READ MORE

answered Feb 22 in AWS by Priyaj
• 56,120 points
50 views
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
• 3,770 points
1,525 views
0 votes
1 answer

How to upload a file to Amazon S3 without passing it my server?

This article pretty much explains the entire ...READ MORE

answered Aug 14, 2018 in AWS by Archana
• 3,770 points
34 views

© 2018 Brain4ce Education Solutions Pvt. Ltd. All rights Reserved.
"PMP®","PMI®", "PMI-ACP®" and "PMBOK®" are registered marks of the Project Management Institute, Inc. MongoDB®, Mongo and the leaf logo are the registered trademarks of MongoDB, Inc.