HOW DO I COMBINE MULTIPLE CSVS INTO ONE

Question

I HAVE MULTIPLE CSVS ABOUT A SINGLE PATIENT AND I WOULD LIKE TO KNOW HOW DO I COMBINE ALL THE CSVS BECAUSE ALL THE COLUMNS INSIDE THE CSVS MAKE UP AN ALL THE INFORMATION FOR ONE PATIENT. THE CSV'S ARE STORED IN S3 BUCKET AND INDIFFERENT FOLDERS. i HAVE TRIED USING JOIN BUT BECAUSE WE HAVE MANY PATIENTS THE JOB IS TAKING FOREVER.TIA

Answer

Hello,

You can create an athena table for taking the input locations as all the s3 prefix. Something like this, refer [create table in athena](https://docs.aws.amazon.com/athena/latest/ug/create-table.html)

```
CREATE EXTERNAL TABLE `test_table`(
...
)
ROW FORMAT ...
STORED AS INPUTFORMAT ...
OUTPUTFORMAT ...
LOCATION s3://bucketname/folder/
```
Once create the table, use CTAS to create another table to consolidate all the csv as single table output location like below, refer [here for CTAS](https://docs.aws.amazon.com/athena/latest/ug/ctas-examples.html#ctas-example-bucketed)

```
CREATE TABLE ctas_csv_unpartitioned 
WITH (
     format = 'CSV', 
     external_location = 's3://xxxxxxxxxxxx/ctas_csv_unpartitioned/') 
AS SELECT key1, name1, comment1
FROM test_table;
```

HOW DO I COMBINE MULTIPLE CSVS INTO ONE

Relevant content