REXGEN Air: Grafana dashboards with Parquet, Glue and Athena

Turn the Parquet files from the conversion function into a Grafana dashboard. A scheduled AWS Glue job catalogues them, Athena queries them and Grafana charts them.

Before you start

• Loggers that upload to S3. See REXGEN Air: uploading logs to Amazon S3.
• The conversion function, running with the Parquet output enabled. See REXGEN Air: converting logs automatically with AWS Lambda.
• A Grafana instance, either Grafana Cloud from grafana.com or your own server, with permission to install plugins.

Create a Glue database

1. Open AWS Glue. Use the same region as your bucket.
2. Create a database. In the AWS Glue Data Catalog, select Databases and then Add database. Enter a name and select Create database. Note the name, because the script and Grafana both need it.

Create a role for Glue

1. Create a role. In IAM, select Roles and then Create role.
2. Choose the trusted entity. Select AWS service, select Glue as the use case and select Next.
3. Add the permissions. Attach AWSGlueServiceRole, plus a policy for your bucket. The script lists the Parquet files, reads them and tags each one it has processed. The role therefore needs list, read and object tagging access to the bucket.
4. Name the role. Add a description if you want one, then select Create role.

Create the Glue job

Download the Influx Technology script first: Download the Glue script (PY, 13 KB).

1. Open the script editor. In AWS Glue, open ETL jobs and select the Script editor.
2. Upload the script. Select Spark as the engine and select Upload script. Select glue_script_v2.py and create the job.
3. Edit the two names in the script. Near the top, the script sets bucket_output and databasename to fixed example values. Replace them with the name of your logger bucket and the name of your Glue database. Do not skip this step. With the example values, the job reads the wrong bucket and writes to the wrong database.
4. Fill in the Job details tab. Name the job and select the role you created. These settings work:
   • Type Spark
   • Glue 4.0
   • Python 3
   • Worker type G.1X
   • 10 requested workers
   • Job bookmarks disabled
   • Timeout of 300 minutes
   To control the cost, adjust the number of workers.
5. Save the job.

On each run, the script looks through every logger folder and every date folder in the bucket for Parquet files it has not yet processed. It adds them to a table for that logger, partitioned by logger and date. It then tags each file as processed, so the next run skips it.

Schedule the job

1. Create a schedule. On the job's Schedules tab, select Create schedule.
2. Set the frequency. Select a frequency or enter a cron expression, then select Create schedule. For the syntax, see the AWS guide to cron expression syntax.
3. Run the job once. If Parquet files are already in the bucket, select Run job. Otherwise, wait for the first scheduled run.
4. Check the result. The Runs tab shows the status of each run. When a run has finished, the Glue database lists one table per logger, named after its serial number. Open a table to see its columns.

Give Grafana access to Athena

1. Create an IAM user for Grafana. In IAM, select Users and then Create user. Enter the user details and select Next.
2. Attach the permissions. Select Attach policies directly. Give the user Athena query access and read access to the AWS Glue Data Catalog and the logger bucket. Add read and write access to a folder for query results, such as queries/ in your bucket. Then select Create user.
3. Create an access key. Open the user and create an access key. Select Other, then select Create access key.
4. Store both keys securely. Store the access key and the secret access key, then select Done. AWS shows the secret only once.

Do not attach the full-access policies for Athena and S3 to this user. They work, but they reach every bucket in the account.

Connect Grafana to Athena

1. Install the plugin. In Grafana, open Connections > Data sources, select Add new data source, find Amazon Athena and install the plugin.
2. Choose the authentication. Set Authentication Provider to Access & secret key and paste the Grafana user's access key ID and secret access key.
3. Set the region. Set Default Region to the region of your bucket.
4. Fill in the Athena details. Set Data source to AwsDataCatalog, Database to your Glue database and Workgroup to primary.
5. Set the output location. Set Output Location to a folder in your S3 bucket, for example s3://your-bucket/queries/.
6. Test the data source. Select Save & test. Grafana confirms when the data source works.

Build a dashboard

1. Create the dashboard. Select Dashboards > New > New dashboard, then Add visualization.
2. Select the Athena data source.
3. Choose what to plot. In the query editor, select the table for the logger and the column for the signal, for example altitude. Set the format to time series.
4. Run the query. Enter the query and select Run query. The example query after these steps averages the chosen column over Grafana's time interval.
5. Save the dashboard.

SELECT
    $__timeGroup(time, $__interval) as Time,
    AVG("$__column") as Alt
FROM
    "$__table"
WHERE
    $__timeFilter(time)
GROUP BY
    $__timeGroup(time, $__interval)
ORDER BY
    Time ASC

To switch between loggers from one dashboard, add a dashboard variable for the logger table.

This is the last guide in the REXGEN Air cloud series. For an alternative to S3, see REXGEN Air: uploading logs to an FTPS server.