sql - How to row format a column with tsv file with sqlite in athena

Question

So I want to add these to a table in athena from a tsv file which I can do except for the last column genres. I mean I can add it but I want it to be like for example ["Comedy", "Mystery"] but it comes out as [Comedy,Mystery] which makes it impossible to access them in any way

tconst      genres 

tt0081313   Action

tt0081315   Comedy,Mystery

tt0081349   Comedy,Crime

This is what I did:

CREATE EXTERNAL TABLE `title_basics`(
  `tconst` string, 
  `genres` Array<string>)

ROW FORMAT SERDE 'org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe'
WITH SERDEPROPERTIES (

  'field.delim' = ''  # This is for separating them by tab which is right but how can I also
                        # add the genres the way I want them to the table

)

STORED AS INPUTFORMAT 
  'org.apache.hadoop.mapred.TextInputFormat' 
OUTPUTFORMAT 
  'org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat'
LOCATION
  'mylocation'
TBLPROPERTIES (
  'has_encrypted_data'='false', 
  'transient_lastDdlTime'='-----')

question from:https://stackoverflow.com/questions/66069048/how-to-row-format-a-column-with-tsv-file-with-sqlite-in-athena

Answer 1

The column genre is being interpreted as a string. There are two possible solutions:

select split(genre,',') -- this will give you an array of genres

Or directly create the column as an array by adding a , as a collection separator.