GHC Cheat Sheet
- Open AWS Workshop Studio at https://catalog.us-east-1.prod.workshops.aws/join?access-code=7a77-0eb03a-4e
- Choose your preferred sign-in method. For AWS guided events, choose Email OTP.
- Enter the code provided by the event organizer into the text box. You will usually find this code on a slide that is being shown, or a paper printout at your table. You can skip this step if the link under #1 already contains the access code.
- Read and agree to the Terms and Conditions by selecting I agree with the terms and conditions and choose Join Event.
- Access the console of your AWS account for the event by clicking the link in the sidebar.
- Go to the Legend GitHub Website in a new browser tab and click on the "Launch Stack" at the bottom of the page.
You will be taken to the Quick create stack page.
- Acknowledge and click Create stack.
This takes a few minutes to complete.
After the CloudFormation stack completes, you are now ready to launch the Legend Workbench URL.
- Select outputs tab and click on Studio Url
- Choose the LegendStarterModel Legend project by clicking on the drop down.
- Hit the "+" symbol to create a workspace to start working in Legend. You can give the workspace any name you want.
You are all set!
First, let's create a new table in our relational schema so that we can bring in the new surface temperature dataset.
Now that the table schema for surface temperature is defined, we need to join it with the country dataset. We will add a join for the two tables into the relationship schema.
Let's create a new SurfaceTemperature class.
- Make sure that the model package is highlighted/selected.
- Click on the "+" icon and select Class.
- Name the new Class SurfaceTemperature. All in one word, camelcase. Click "create" when done.
- Add 3 attributes to your class by hitting the "+" sign: country, year, temperatureChange. Please see screenshot for datatype and cardinality.
- Hit F9 or click on the Hammer icon at the bottom right to compile your new code.
Now, we will map our new _SurfaceTemperature _ Class to our data.
- Navigate to the SustainabilityStatisticsMapping in the mapping package.
- Drag and drop the SurfaceTemperature Class in the Mapping Editor (where also the other Classes are listed). Hit "Create" in the dialog that pops up.
- Navigate to the store package, and select the SustainabilityStatisticsDatabase. Drag the store into the source Editor on the right-hand side. Select the surface_temperature_change table from the drop down that appears.
- Drag and drop the columns of the surface temperature datasets (on the right) to the respective attributes (on the left). See screenshot for details.
- Hit F9 or click on the Hammer icon at the bottom right to compile your new code.
- Use the Country Class to build the relationship between ProductionBasedCarbonDioxideEmissions Class and the SurfaceTemperature Class. Navigate to the Country Class and add the surfaceTemperature attribute. See screenshot for details.
- Now update your Country Class Mapping. Navigate to the SustainabilityStatisticsMapping, select the Country Class and map the country_surface_temperature_join to the surfaceTemperature attribute.
- Navigate to the "EmissionsModelDiagram" under the model package.
- You can now drag and drop the SurfaceTemperature class onto the diagram. You can see that the relationship to the Country class was added for you based on the association that we modeled earlier. Tip: You can move the class box and the relationship lines around to make the diagram pretty.
To create a query, right-click on any class.
Add attributes you want to query by dragging & dropping them into the "fetch structure panel".
Apply operations on your data, by clicking on the "sigma" symbol. Options include:
- Sum: Adds up all values
- Average: Returns the average
- Count: Counts values
- Distinct count: Returns distinct count value
Add a filter to your query by dragging & dropping an attribute to the "filter" panel. Click on the drop down to see available filter options. Alternatively, you can add a filter by right-clicking on the table values. Note: the filter specs will not be persisted in the query.
To sort query results, navigate to the "result set modifier" and add filter conditions. Alternatively, you can sort the results directly in the table by clicking on the column headers. Note: the filter specs will not be persisted in the query.
To remove duplicate values to create a unique set of values, click on "result set modifier" and check the "Eliminate duplicate rows" box.
To specify in your query how many rows you'd like to have returned go to the "result set modifier" and enter the desired value under "Limit results".
Query 1: Get the number of distinct countries that have production based carbon dioxide emission statistics.
Query 2: Get the list of distinct countries that have production-based carbon dioxide emission statistics.
Get the top 10 countries with highest carbon dioxide emissions in 2021.
Query 3: Get top 10 countries by average emissions across all years available in the data set.
Last Resort in case you get stuck during the exercises or your Legend instances crashes. Please refer to this document to see the entire data model code (including data queries). You can copy/paste this in your Studio project when hitting F8 to enter text mode and then F9 to compile. Make sure that the any other code is deleted before doing this.
- Word file GHCModelCode.docx
- Text file grace_hopper_code.txt