AWS Athena csv metadata delimiter changed after first query use

1
votes

I want to query s3 csv files to athena. Source csv file desc: (separator '|')

system information
val1|val2|val3|val4|val5|

Base on that I create table in athena:

Create external table dbname.fromcsv
(
col1 string,
col2 string,
col3 string,
col4 string,
col5 string
)
 ROW FORMAT SERDE  'org.apache.hadoop.hive.serde2.OpenCSVSerde'
WITH SERDEPROPERTIES (
  "separatorChar" = '|')
LOCATION
's3://mybucketloc/folder/'
TBLPROPERTIES ("skip.header.line.count"='1');

After creating table and querying athena everything looks fine, data are well separated etc.

First query result:

col1 col2  col3   col4 col5
val1 val2  val3   val4 val5

Next query against the same table is returning values with ',' separator stored in one column...

col1                     col2  col3   col4  col5
val1,val1,val,val4,val5

And then In my s3 bucket additional files are added .csv.metadata

How to handle such case? I dont want to delete every single time .csv.metadata file ? Is there are way to keep the original create table definition? I've tried to change create table to ROW FORMAT DELIMITED FIELDS TERMINATED BY '|' and many others...

2
amazon-web-servicescsvamazon-s3amazon-athena
Your question is confusing. What do you mean by "After creating table everything looks fine"? This was before you queried with Athena, so how do you know it looks fine? Also, what do you mean by "In my s3 loc additional files are added .csv.metadata files"? Are you saying that Athena created some files in an Amazon S3 bucket? Please edit your question to better explain what you are doing at each step and what you are observing. Also, can you supply a sample of the input data that is |-separated? - John Rotenstein
The point is that athena is creating some metadata files that are used after first query execution as a "source" of table ive checked it with $path. - dzabba

2 Answers

1
votes

I believe your s3://mybucketloc/folder/ is the same as athena query results bucket. This bucket is created by Athena for storing files such as .csv.metadata etc. and is meant for its internal use, hence you have .csv.metadata files present in the same bucket as that of your csv files.

Easiest solution could be, have your csv files in a different s3 bucket and not in the athena-query-results bucket.

0
votes

Change this:

 'serialization.format' = ';',                                                     
  'field.delim' = ';'

for:

 'serialization.format' = ',',                                                     
  'field.delim' = ','