How to query CSV file stored in AWS S3 using Athena?
Tags: aws, athena, s3, python
Published: 2023-02-11 19:26:57

**CSV (Comma Separated Values)** can consider as a database, and it stores data in a table format. As far as I know, CSV has been used everywhere; from reports to machine learning to store the dataset and else.

Believe it or not, most companies still use CSV as a 'db.' Then, when we talk about data and you're a passionate Data Analyst, we must analyze it using SQL (in BI tools) so, if you're working in AWS environment do follow this steps.

---

![AWS Logo](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/5toatxj8lk1m8s99l9s9.png)

### 1- Prepare a CSV File
Skip if you already have CSV file on your local.

1. As we will use Python, do install Python on your local
2. Install **faker** library to get dummy data
3. Prepare the CSV with `'first_name', 'last_name', 'age'` as headers
4. Now, open the folder and `dummy-data.csv` should created inside it

```py
import csv
from faker import Faker

fake = Faker()
number_of_records = 8000

with open('dummy-data.csv', mode='w+') as file:
    file_writer = csv.writer(file, delimiter=',', quotechar='"', quoting=csv.QUOTE_MINIMAL)
    file_writer.writerow(['first_name', 'last_name', 'age'])
    
    for _ in range(number_of_records):
        file_writer.writerow([fake.first_name(), fake.last_name(), fake.numerify("@#")])
```

### 2- Create a bucket in Amazon S3
Skip if you already have a bucket and use the existing one


1. Go to AWS Console -> search `S3` -> click `Create Bucket` ![create bucket](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/dscf3go0nr37openxtna.png)
2. Fill up the necessary fields -> Create bucket 
![fill up fields](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/18zkfdo35mowlluqb84t.png)
3. New bucket is created. Now you can decide whether to create a new folder inside the bucket. For me, I want to have a separate folder for dummy data; hence I created `dummy_data` folder
4. Click `Create folder` 
![Create folder](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/1w1p23fe47fydnkj9iu0.png)
5. Fill up the necessary fields -> Create folder
6. New folder is created in the bucket
7. Now, upload the .csv file by drag to the page 
![upload file](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/ff8u6zip8b4e46fxuggu.png)
8. You can refresh or go back to the bucket folder to see the imported CSV file 
![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/5fo4aofjiiw9q3atti8i.png)
9. Please don't forget to copy the S3 URI of the folder to create Athena table

### 3- Create a table in AWS Athena
Note: If you want to create another database, can use below query
`CREATE DATABASE myDataBase`

1. Create table in Athena; you need to define the structure of the table so that Athena table able to know the data structure in CSV file. 
    1. AWS Management Console
    2. AWS CLI
2. For this post, I am using AWS Management Console. Like the S3 service, you can open your AWS Console and search Athena.
3. Populate the table using below query

```sql
CREATE EXTERNAL TABLE dummy_person (
  first_name string,
  last_name string,
  age string
)
ROW FORMAT SERDE 'org.apache.hadoop.hive.serde2.OpenCSVSerde'
WITH SERDEPROPERTIES (
  'separatorChar' = ',',
  'quoteChar' = '"',
  'escapeChar' = '\\'
)
LOCATION '[S3 URI]'
TBLPROPERTIES ("skip.header.line.count"="1");

```
Table is created.
![table](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/yst6kwie87xf28en0pfr.png)

Run SQL queries to check the data has been populated

```sql
select * from dummy_person 
```

At this moment, you can already see the data

![data](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/zl5ht07cmeqrnal7tef5.png)

---
Next, you can connect Athena to your BI tools and start to develop the dashboard 😊