Scenario summary

In this lab, you are a data analyst who works for an international development agency. The agency focuses on drought relief. You are asked to look for weather patterns since 1950.

Fortunately for you, global weather data is already stored in Amazon S3. The National Centers for Environmental Information (NCEI), which is in the US, maintains a dataset of climate data. This dataset includes observations from weather stations around the globe. The Global Historical Climatology Network Daily (GHCN-D) contains daily weather summaries from ground-based stations. The dataset contains data that goes back to 1763, and it is updated daily.

The most common recorded parameters are daily temperatures, rainfall, and snowfall. These parameters are useful for assessing risks for drought, flooding, and extreme weather. The data definitions are publicly available on the AWS Open Data Website.

The following diagram illustrates the architecture of the solution you will develop in this lab:

Task 1: Create a crawler for the GHCN-D dataset

As a data analyst, you might not always know the schema of the data that you need to analyze. AWS Glue is designed for this situation. You can direct AWS Glue to your data stored on AWS, and it will discover your data. AWS Glue will then store the associated metadata (for example, the table definition and schema) in the AWS Glue Data Catalog. You accomplish this by creating a crawler that will inspect the data source and infer a schema based on the data. The account that you use to log in when you run AWS Glue must have permissions to access the data source. In this lab, you will work with publicly available data, so there’s no need to create a specific AWS Identity and Access Management (IAM) account. However, this will not always be the case. To read more about how AWS Glue and IAM work together, see Authentication and Access Control for AWS Glue.

The first task is to create a crawler that will discover the schema for the GHCN-D dataset.

  1. On the AWS Management Console, on the Services , choose AWS Glue.

  2. Choose Get started.

    Note: If you do not see the Get started window, then proceed to the next step.

  3. Choose Add tables using a crawler.

  4. For the crawler name, enter Weather.

  5. Choose Next.

  6. On the Specify crawler source type page, choose Data stores.

  7. Choose Next.

  8. Choose Specified path in another account.

  9. For the Include path, enter the following S3 bucket location:

    s3://noaa-ghcn-pds/csv/

  10. Choose Next.

  11. When prompted to add another data store, choose No.

  12. Choose Next.

  13. Choose Choose Create a New role.

  14. Choose Next.

  15. Accept the default frequency of Run on demand.

  16. Choose Next.

  17. Choose Add database.

  18. In the Database name box, enter weatherdata.

  19. Choose Create.

  20. Choose Next .

  21. Review the summary of the crawler and then choose Finish.

Task 1.1: Run the crawler

You can create AWS Glue crawlers to either run on demand, or on a set schedule. To read more about scheduling crawlers, see Scheduling an AWS Glue Crawler. Because you created your crawler to run on demand, you must run the crawler to generate the metadata.

  1. From the window with the message that the weather crawler was created, choose Run it now?

    You will see the status for the crawler change to Starting and then Running crawler. After approximately 1 minute, the status will change to Ready, and the Tables added column will indicate that one table was added.

  2. AWS Glue creates a table to store the metadata about the GHCN-D dataset. Inspect the data that AWS Glue captured about the data source.

Task 1.2: Review the metadata created by AWS Glue

  1. In the navigation pane, choose Databases.
  2. Choose the weatherdata database.
  3. Choose Tables in weatherdata.
  4. Choose the csv table.
  5. Review the metadata that the weather crawler captured. You should see a list of the columns the crawler discovered. The following screenshot illustrates some of the columns:

Notice that the columns are named col0 through col6. In the next step, you will give the columns more descriptive names.

Task 1.3: Edit the schema

  1. In the upper-right corner of the window, choose Edit schema.
  2. Change the column names by selecting them and entering the new names. The following table lists the new column names to use.

  1. Choose Save.

Task 2: Query the table using the AWS Glue Data Catalog

Now that you created the AWS Glue Data Catalog, you can use the metadata that is stored in the AWS Glue Data Catalog to query the data in Amazon Athena.

  1. From the navigation pane, choose Tables.

  2. Select the csv table check box.

  3. From the Action menu, choose view data.

    You will see a warning that Athena is going to open and that you will be charged for Athena usage. The Athena console will open.

  4. Choose Preview data.

  5. Choose Get started.

  6. If the tutorial window opens, close it.

You see the following message at the top of the console:

You must specify an Amazon Simple Storage Service (Amazon S3) bucket to hold the results from any queries that you run.

  1. On the AWS Management Console, on the Services menu, choose S3.
  2. Create / Select the bucket name within the same region as that of Glue catalog.
  3. In the bucket properties window, choose Copy bucket ARN.
  4. Paste the bucket Amazon Resource Name (ARN) into a text editor.
  5. To return to Athena, go back the AWS Management Console and on the Services menu, choose Athena.
  6. Choose setup a query result location in Amazon S3.
  7. In the Query Result Location box, enter the name of the bucket. The name of the bucket is the long string of characters at the end of the bucket ARN. Before the name of the bucket, make sure to specify s3:// and terminate the bucket name with a forward slash (/), as shown in the example.

  1. Choose Save.
  2. From the list of databases, choose the weatherdata database.
  3. Choose the csv table.
  4. Choose the vertical ellipsis (three dots) and then choose Preview table.

You will see the first 10 records of the weather table.

Athena ran a structure query language (SQL) query to get first 10 rows from the table. Data is not yet loaded at this stage, but by using AWS Glue, you inferred and edited the schema to suit your needs. Also, note the resources that were consumed by the Athena query (the run time and the amount of data that was scanned). As you develop more complex applications, minimizing resource consumption will play an important role in optimizing costs.

Task 2.1: Create a table for data after 1950

In this step, you will create an external table that only includes data since 1950. To optimize your use of Athena, you will store data in the Apache Parquet format. Apache Parquet is an open source, columnar data format that is optimized for performance and storage. To read more about Apache Parquet, see Apache Parquet.

  1. Create an S3 bucket to store the external table. The bucket should be in the same Region that your lab is running in. Follow the bucket naming conventions that are described in the Amazon S3 documentation.

  2. Copy the following query and in the Athena command window, paste the query. Remember to replace with the name of the bucket that you created:

    CREATE table weatherdata.late20th
WITH (
 format='PARQUET', external_location='s3:///lab3/'
) AS SELECT date, type, observation  FROM csv
WHERE date/10000 between 1950 and 2015;

  3. Choose Run query.

  4. Preview the query by going to the late20th table, choosing the vertical ellipsis (three dots) next to the table, and then choosing Preview table.

Task 2.2: Run a query from the selected data

Now that you isolated the data that you are interested in, you can write queries for further analysis. Start by creating a view that only includes the maximum temperature reading, or TMAX, value. To create this view:

  1. Copy the following query and in the Athena command window, paste the query:

    CREATE VIEW TMAX AS
SELECT date, observation, type
FROM late20th
WHERE type = 'TMAX'

  2. Choose Run query.

  3. Preview the data in the view by going to tmax, choosing the vertical ellipsis (three dots) next to it, and then choosing Preview.

  4. Copy the following query and in the Athena command window, paste the query:

    SELECT date/10000 as Year, avg(observation)/10 as Max

    FROM tmax

    GROUP BY date/10000 ORDER BY date/10000;

  5. Choose Run query.

You should see a table of data results from 1950–2018, with the average maximum temperature for each year.

Last modified: March 22, 2022