Visualize Data with Amazon Timestream & Grafana

Time series is a set of values of any data that are ordered according to time. It’s a very common data format to observe how things are changing over time. Duration of processes, exchange rates, weather forecasts, heart rate measurements, these are all examples of the time series. Most common applications are metrics from IoT sensors, performance of a system or process, application performance monitoring, clickstream and so on.

Although time series data can be collected very quickly, traditional databases have difficulties in scaling these data since they often remain insufficient with the very large datasets. Because of this disadvantage of traditional relational databases, most people choose to use time series databases (TSDB).

AWS offers a scalable TSDB service for the time series data, called Amazon Timestream. Let’s check its core features, how it works and continue with building our case.

An Overview of Amazon Timestream

Two years ago, AWS launched Amazon Timestream in Preview and in late September 2020, AWS Timestream was announced to be generally available for the users. Amazon Timestream is a serverless, scalable time series database solution and it is up to 1000x faster in query performance compared to the relational databases. The service also makes it easier to handle trillions of time series data with high performance while charging almost for a tenth of the cost of a traditional relational database.

Let’s start with the convenience of transferring and accessing the data with Amazon Timestream. You do not have to do anything to transfer your data from memory to magnetic store, Amazon Timestream carries out the transfer process for you. Also when it comes to data queries, you don’t have to use separate tools to access your data anymore since you don’t need to specify the data location such as memory or magnetic store.

One of the additional benefits of Amazon Timestream is the built-in security. The service ensures that time series data is always encrypted. Last but not least, you only pay for the write, store and query operations while using Amazon Timestream.

In this tutorial, we are going to build an application that saves the weather data for the last five days to our timestream database and visualizes it with the Grafana dashboard. We are going to use One Call API of OpenWeatherMap as our data source and save the data in Amazon Timestream database. You can check out the project's GitHub repo for the full source code.

Creating a Database on Amazon Timestream

Now, we are going to create a database from Amazon Timestream. From the Amazon Timestream console, we selected the standard database and named it weatherDB.

In the Encryption tab, we will use the default master key. However, you can also provide a master key through AWS KMS as well.

Following these two steps, our database is created. Now, we are going to create a database table, let’s call it weathertable.

In the Data Retention tab, you will see two retention periods. The most recent recordings, “hot data” are stored in memory. On the other hand, magnetic store keeps cold data and the cold data is permanently erased when the magnetic store period expires. Here, memory store retention describes how long the data will be stored in memory before being moved to the magnetic store.

In our case, our memory store retention period should be at least 5-days since we can not save records older than the retention period of the memory store. Let’s select the memory store retention as 7-days and magnetic store retention period as 1-month.

Now, our timestream database is up to go. Let’s start coding.

Loading Sample Data with Python and Boto3

First things first, we need to create a client to be able to send time series data to Amazon Timestream. We create our client with boto3 using the credentials of our IAM user with the necessary permissions.

   client = boto3.client(
        'timestream-write',
        aws_access_key_id=constants.AWS_ACCESS_KEY_ID,
        aws_secret_access_key=constants.AWS_SECRET_ACCESS_KEY,
        region_name=constants.AWS_DEFAULT_REGION
    )

Or if you run this code on an EC2 instance or AWS Lambda, then you can grant permissions to instance profile role or Lambda role and use following code to create boto3 client. You can check for more information about using IAM roles on boto3 from here.

   client = boto3.client(
        'timestream-write',
        region_name=constants.AWS_DEFAULT_REGION
    )

After we have created our client, we are ready to get daily weather data from the API. Creating a 'list of dictionaries' with the prepare_data() method:

def prepare_data(self, data):
    records = []
    for hourly in data['hourly']:
        records.append(self.prepare_record(hourly['dt'] * 1000, self.city, 'temp', hourly['temp']))
        records.append(self.prepare_record(hourly['dt'] * 1000, self.city, 'feels_like', hourly['feels_like']))
        records.append(self.prepare_record(hourly['dt'] * 1000, self.city, 'humidity', hourly['humidity']))
    return records

We need to define these keys in our dictionary and add value with prepare_record() method:

• Time: Epoch timestamp in milliseconds.
• Dimensions: Additional columns for our database.
• MeasureName: Name of the metric data such as temperature, air pressure etc.
• MeasureValue: Value of the metric data.
• MeasureValueType: Type of the value of metric data such as DOUBLE, INTEGER etc.

In our application, Dimensions field(s) are common for each city’s analysis. It can be customized by adding other columns such as hostname, country name, timezone etc.

    def prepare_record(self, timestamp, city, measure_name, measure_value):
        #  Prepare record to be able to send Amazon Timestream
        return {
            'Time': str(timestamp),
            'Dimensions': [{'Name': "city", 'Value': city}],
            'MeasureName': measure_name,
            'MeasureValue': str(measure_value),
            'MeasureValueType': 'DOUBLE'
        }

PS: Sending epoch timestamp in seconds will result in a RejectedRecord exception as the timestamp will lie outside of the memory store retention.

Finally, we have reached the last point. All we need to do is send the records to the Amazon Timestream and that’s what our write_records() method does. We will use temperature, feels-like temperature and humidity values from the API response. When the creation of the list is done for each day, we will send it to Amazon Timestream.

  def write_records(self):
        for epoch in self.get_five_past_days():
            data = self.get_weather_data(epoch)
            records = self.prepare_data(data)
            try:
                # Send data to Amazon Timestream
                response = self.client.write_records(
                    DatabaseName=constants.DATABASE_NAME,
                    TableName=constants.TABLE_NAME,
                    Records=records
                )
                print(f"WriteRecords Status: {response['ResponseMetadata']['HTTPStatusCode']}")
            except self.client.exceptions.ResourceNotFoundException:
                raise Exception("Table doesn't exists.")
            except Exception as exc:
                raise Exception(f"Unexpected error: {exc}")

Run the script with a city that has predefined latitude and longitude information;

python main.py --city Istanbul

Here is the generated data in Amazon Timestream;

You can check the full source code example from:

Visualizing Our Historical Weather Data Using Grafana

Now, it is time to visualize our 5-days weather data using Grafana. By following the instructions here, you can install and set up Grafana on your machine. When the setup is completed, run the following command to install the Timestream plugin of Grafana from the command line: grafana-cli plugins install grafana-timestream-datasource

At the "Add Data Sources" tab, search for Amazon Timestream and select it.

Next, provide the credentials file and select your preferred region. After that, hit the “Save & Test” button to see if the connection is successful. Lastly, select your database, table and measure values.

Done! In the Grafana Dashboard, we selected a line graph to visualize temperature values. Here is an example query and its result.

You can also enhance your graphs and dashboards such as adding average value for a specified time period, a secondary measurement for your graph or a secondary location to compare your time series data values as in below:

Interested in leveraging cloud-native database services for your applications? Book an Appointment now to increase your application performance with custom software architecture and services!