36 views

create table in Athena using CSV file

Today, I will discuss about “How to create table using csv file in Athena”.Please follow the below steps for the same.
* Upload or transfer the csv file to required S3 location.


* Create table using below syntax.

create external table emp_details
(EMPID int,
EMPNAME string )
ROW FORMAT SERDE ‘org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe’
WITH SERDEPROPERTIES (
‘serialization.format’ = ‘,’,
field.delim‘ = ‘,’
)
location ‘s3://techie-1/emp/’
TBLPROPERTIES (
“skip.header.line.count”=”1”)

* Important to note here that if you have a file which has header , then you need to skip the header .For this, we need to add Table properties.
* If file doesn’t have header , then the above mentioned property can be excluded from the table creation syntax.
* Location defines the path where the input file is present.
* As file is CSV format, that means , it is comma separated . That point is mentioned in the Serde properties.
* See the “select Query” on the same.


Related posts