Querying Data in S3 with Amazon Athena
Tags: serverless, aws, awscommunitybuilderprogram, athena
Published: 2022-05-08 20:13:18

**<u>_Architecture Diagram_</u>**

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/d112lhjy1div4x9q0d9g.png)

**<u>_Task Details_</u>**

 1. Log into AWS Management Console.

2. Setup workgroup

3. Create a database in Glue.

4. Query table in Athena

**<u>Task 1:-</u>**
i. Login to **<u>AWS Console</u>**

**<u>Task 2:- </u>**
   i. Make sure you are in the **<u>N.Virginia</u>** Region.
   ii. Navigate to menu in the top, then click on **<u>Athena</u>**. 
   iii. Click on the **<u>Get started</u>** button.

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/43s46udasy0h39du63ad.png)
  iv. In the menu bar, click on **<u>Workgroup:primary</u>**.

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/ejxh1fmi8oeufawkzzcb.png)

  v. To create a workgroup, Click on the **<u>Create workgroup</u>** button.

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/cewaym2e0qne7ick8y66.png)
   vi. Provide details to create a workgroup:
        - Workgroup Name: Enter **<u>WhizWorkgroup</u>**
        - Description: Enter **<u>Workgroup for Athena lab</u>**
        - Query result location: **<u>Select the S3 bucket</u>**, whose name is starting with **<u>whizlabs</u>**...

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/29iijiykeoojxvayf34g.png)
       - Leave other settings as default.
       - Click on the **<u>Create Workgroup</u>** button.

vii. Now it will list all the Workgroups

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/1w1j998e6ryx8ljobchx.png)
viii. Select **<u>WhizWorkgroup</u>** by checking on the option and click on the **<u>Switch workgroup</u>** button.


![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/3pjm7oymz2kl1aamzri4.png)
 ix. Now it will show the opening page, click on Get started

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/b2dt7lyegkdaqm6daa0w.png)

x. Now you can verify your workspace.

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/g5c96ypznsjk0kie46w4.png)

**<u>Task 3: Create a database in Glue</u>**

     1.	Make sure you are in the **<u>N.Virginia</u>** Region.
     2.	Navigate to   menu at the top, then click on **<u>AWS Glue</u>** in 
        the   section.
     3.	By default, you will be able to see the **<u>tables </u>**present. In the left sidebar, Under Data catalog, Click on **<u>Databases</u>**.
     4. Click on the **<u>ADD DATABASE</u>**
     5.	In the pop-up menu, enter the database name as 
       **<u>whizgluedatabase </u>**and click on the **<u>Create </u>**button.

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/80pbpxjntqc6m8pz9fcv.png)
    6.	The database is now created.

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/6hc443bbg6zji0xbgk59.png)
**<u>Task 4: Create a table in Glue</u>**
1.	In the left sidebar, Under **<u>Data catalog</u>**, Click on **<u>Tables</u>**.
2.	To create a table, click on the **<u>Add tables</u>** button and select **<u>Add table manually</u>**.

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/e9o9uxmi5uboz8wgq69g.png)
3.	In the **<u>Set up your table's properties</u>** section, do the following:
•	Enter the Table name as **<u>whiz-sample-table </u>**
•	select the Database, select **<u>whizgluedatabase</u>**
•	Click on the Next **<u>button</u>**.

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/6aah61gtanknyi7z4agi.png)
4.	In the Add a data store section, do the following:
•	**<u>Select the type of source</u>**: S3 (default)
•	**<u>Data is located in</u>**: Specified path in my account (default)
•	**<u>Include path</u>**: Select the S3 bucket name starting with **<u>whizlabs</u>**...
•	Click on the **<u>Next </u>**button below to proceed further...

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/xvab1rvmgys049favb4b.jpg)
5.	In the **<u>Choose a data format</u>** section, do the following:
•	Select Classification as **<u>CSV</u>**
•	Choose delimiter as **<u>Comma: ,</u>**
•	Click on the **<u>Next </u>**button.

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/tro8rwb5m7fvx6psvsvt.png)
6.	In the Define a schema section, we will add 2 columns.
•	Click on the **<u>ADD Column</u>** button.
•	Column name: Enter **<u>Expense_Type</u>** and Column Type: Select **<u>string</u>**
•	Click on the **<u>Add</u>** button below.

•	Click on the **<u>ADD Column</u>** button again.
•	Column name: Enter **<u>Expense_Category</u>** and Column Type: Select **<u>string</u>**
•	Click on the **<u>Add</u>** button below.
•	After adding both the columns, click on the **<u>Next </u>**button.

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/ypz7bprp9ukx8l70cfgt.png)
7.	Add partition indices: Leave everything as default and click on **<u>Next </u>**button.
8.	Review the configuration of the table and click on the **<u>Finish </u>**button.

**<u>The table is now created.</u>**

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/7zs9kw3jdcagbwtgjx0r.png)

**<u>Task 5: Query table in Athena</u>**
1.	Make sure you are in the **<u>N.Virginia</u>** Region.
2.	Navigate to   menu in the top, then click on **<u>Athena </u>**in the   section.
3.	Click on the **<u>Get started</u>** button, if asked.
4.	In the left sidebar, under data source, Select the database as **<u>whizgluedatabase</u>**.
5.	Then you will see our table, **<u>whiz-sample-table</u>**.

6.	To preview the data of **<u>whiz-sample-table table</u>**, select the **<u>Preview table</u>**.

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/fmy3dw3r93sbhddr1bau.png)
7.	Query editor will automatically generate the SQL statement for querying the first 10 columns.

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/4ac606p2lt0bommkvz06.png)
8.	The result of the query is shown below.

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/diw4b713m5aen26q7gjf.png)
9.	To get the results of all expenses types under **<u>expense_category</u>** of food, paste the following SQL statement into the query editor.
•	**<u>SELECT * FROM "whizgluedatabase"."whiz-sample-table" where expense_category = 'Food';</u>**
**<u>Note</u>**: To execute the queries through the keyboard directly use the shortcut **<u>Ctrl + Enter (For windows) or Tab + Enter (For Mac)</u>**
1.	You can play around with some of the queries like:
2.	Getting a total number of rows present by running the following SQL statement in the query editor.
•	**<u>SELECT count(*) FROM "whizgluedatabase"."whiz-sample-table";</u>**

Once you execute this command you will get the result.

Thank you.