Querying data with Athena

To query asset data with Athena

1. Navigate to the Athena console.

   If the Getting started page appears, choose Get Started.

2. If you're using Athena for the first time, complete the following steps to configure an S3 bucket for query results. Athena stores the results of your queries in this bucket.

   Important

   Use a different bucket than your asset data bucket, so the crawler that you created earlier doesn't crawl query results. We recommend that you create a bucket to use only for Athena query results. For more information, see How do I create an S3 bucket? in the Amazon Simple Storage Service User Guide.

   1. Choose Settings.

   2. In Query result location, enter the S3 bucket for Athena query results. The bucket must end with /.

      

   3. Choose Save.

3. The left panel contains the data source to query. Do the following:

   1. For Data source, choose AwsDataCatalog to use the Amazon Glue Data Catalog.

   2. For Database, choose the Amazon Glue database that you created with the crawler.

      

   You should see two tables: asset_metadata and asset_property_updates.

4. To create a view from the combination of asset property data and metadata, enter the following query, and then choose Run query.

   CREATE
           OR REPLACE VIEW iot_sitewise_asset_data AS
   SELECT "from_unixtime"("time_in_seconds" + ("offset_in_nanos" / 1000000000)) "timestamp",
            "metadata"."asset_name",
            "metadata"."asset_property_name",
            "data"."asset_property_value",
            "metadata"."asset_property_unit",
            "metadata"."asset_property_alias"
   FROM ( "iot_sitewise_asset_database".asset_property_updates data
   INNER JOIN "iot_sitewise_asset_database".asset_metadata metadata
       ON ( ("data"."asset_id" = "metadata"."asset_id")
           AND ("data"."asset_property_id" = "metadata"."asset_property_id") ) );

   This query joins the asset property data and metadata tables on asset ID and property ID to create a view. You can run this query multiple times because it replaces the existing view if the view already exists.

5. To add a new query, choose the + icon.

6. To view a sample of asset data, enter the following query, and then choose Run query. Replace the timestamps with an interval for which your bucket has data.

   SELECT *
   FROM "iot_sitewise_asset_database"."iot_sitewise_asset_data"
   WHERE "timestamp"
       BETWEEN TIMESTAMP '2020-05-14 12:00:00.000'
           AND TIMESTAMP '2020-05-14 13:00:00.000'
   ORDER BY  "timestamp" DESC LIMIT 50;

   This query outputs up to 50 data points between two timestamps, with the most recent entries shown first.

   Your query output might look similar to the following results.