This repository contains the steps to perform ETL on a dataset using AWS Glue and querying the data using Athena.

1. Open the AWS Management Console and navigate to AWS Glue.
2. Under Databases, select Tables.
3. Click on Add tables using crawler.
4. Name the crawler "Weather" and keep the default settings for Tags.
5. Choose Add a data source and configure the following:
   • Data source: S3
   • Location of S3 data: In a different account
   • S3 path: s3://noaa-ghcn-pds/csv/by_year/
   • Subsequent crawler runs: Crawl all sub-folders
6. Choose Add an S3 data source.
7. For Existing IAM role, select "gluelab".
8. In the Output configuration section, choose Add database, name it "weatherdata", and create the database.
9. Select the weatherdata database.
10. In the Crawler schedule section, choose Frequency: On demand.
11. Review the crawler configuration and choose Create crawler.
12. Wait for the crawler status to change to Ready.

1. On the Crawlers page, select the Weather crawler.
2. Choose Run and wait for the status to change to Ready.

1. In the navigation pane, select Databases.
2. Choose the weatherdata database.
3. Click on the by_year table and review the metadata.

1. From the Actions menu, choose Edit schema.
2. Change the column names according to the provided table.
3. Choose Update schema.

1. In the AWS Glue console, navigate to Tables under Databases.
2. Click on the by_year table and choose Actions > View data.
3. Proceed to the Athena console and select Settings.
4. Manage the Location of query result and choose the data-science-bucket.
5. Save the settings.

1. Switch to the Athena Editor tab.
2. Set Data source to AwsDataCatalog and Database to weatherdata.
3. Choose the by_year table and select Preview Table to view the data.

1. Retrieve the bucket name containing glue-1950-bucket.
2. In the Athena query editor, execute the provided query replacing with the bucket name.
3. Run the query and preview the results.

1. Create a view for the maximum temperature reading.
2. Run a query to calculate the average maximum temperature for each year.

1. Navigate to the IAM console and select Roles.
2. Locate and copy the ARN for the gluelab role.

1. Open the AWS Cloud9 IDE and create a new file named gluecrawler.cf.yml.
2. Copy and paste the provided CloudFormation template into the file.
3. Replace with the copied IAM role ARN.
4. Save the file and validate the template using the AWS CLI.

1. Run the command to create the CloudFormation stack using the template.
2. Verify the stack creation and check the resources created.

By following these steps, you can perform ETL tasks with AWS Glue, query the data with Athena, and automate the creation of Glue crawlers using CloudFormation.