Querying data with Amazon Athena

Now that you have made your data discoverable, you can query your data for exactly what you want to analyze.

  • Go to AWS Management Console, click Services, then select Athena or click this quick link.

  • On the left hand side under Database, select the Glue Data Catalog you just created. In the case of the lab, it is the peculiar-data-catalog.

  • Expand the peculiar-wizards-data-lake (Partitioned) table located on the left-hand navigation pane to see the data fields.

  • Click the three dots next to the table name and Preview table to see a sample query return a preview of the data in the table.

  • This will run the following query:

SELECT * FROM "peculiar-data-catalog"."peculiar_wizards_data_lake" limit 10;

Keep in mind, for all of the queries in this section, you might need to change the database and table name to what yours if they are different than what is shown in this lab.

This will show you a preview of your data in S3.

You might need to set up an S3 bucket destination where the output of your queries get saved. If “Run Query” is greyed out, there will be a link at the top of the screen prompting you to specify an S3 destination. You can also edit your S3 destination by clicking “Settings”.

  • Create a new query in the middle pane of the Athena Dashboard and Run query for the following:
SELECT count(DISTINCT event_id) as event_count 
                FROM "peculiar-data-catalog"."peculiar_wizards_data_lake"

This shows you how many unique events you have had in total.

  • Open a new tab, create a new query and Run query for the following:
SELECT *, from_unixtime(event_timestamp, 'America/New_York') as event_timestamp_america_new_york
                FROM "peculiar-data-catalog"."peculiar_wizards_data_lake"
                ORDER BY event_timestamp_america_new_york DESC
                LIMIT 10;

This query shows you the latest events by the timestamp.

The latest events query and the unique events query shown above are sample queries that come out of the box with the Game Analytics Pipeline solution. If you deploy this solution in development mode, it will come with other sample queries as well. These include things like level completion rate, new users last month, total plays by level, and more.

Congratulations! You have finished querying your data using Amazon Athena. See what other sample queries you can run to find insights.