AWS Timestream csv-export

By scrt_admSecValBlog

Export your time-data to csv

What is Amazon Timestream?

Amazon Timestream is a fast, scalable, and serverless time-series
database service that makes it easier to store and analyze trillions of
events per day up to 1,000 times faster. Amazon Timestream automatically
scales up or down to adjust capacity and performance, so that you don’t
have to manage the underlying infrastructure.  You can read more about timestream here: https://aws.amazon.com/timestream/

Why export data to CSV?

Especially in corporate business it is sometimes necessary to provide data in CSV format. What would the world be without Excel people… Furthermore, for evaluations and exchange with other systems, it is sometimes inevitable to exchange data in CSV format.

AWS Timestream (unfortunately?) does not provide a standard function to perform a CSV export for a defined query.

Therefore, we use a trick and push the query data, directly with the query definition, as a compressed CSV into an AWS S3 bucket.

Amazon Simple Storage Service (Amazon S3) is an object storage service offering industry-leading scalability, data availability, security, and performance. Customers of all sizes and industries can store and protect any amount of data for virtually any use case, such as data lakes, cloud-native applications, and mobile apps. With cost-effective storage classes and easy-to-use management features, you can optimize costs, organize data, and configure fine-tuned access controls to meet specific business, organizational, and compliance requirements. You can read more about AWS S3 here: https://aws.amazon.com/s3/

 

 

"The trick"

The first step is to create a new S3 bucket and remember its name. In this example, my S3 bucket is called “measure-secval-export-csv”.

Then switch to the AWS Timestream view and select “Query Editor” from the left menu.

In my query example, the timestream database is “secval-measures” and the table is “iot-measurements”. Our query then looks like this:

UNLOAD(SELECT * FROM “secval-measures”.”iot-measurements” WHERE device-id=’my-device-id’ and time between ago(24h) and now()) TO ‘s3://measure-secval-export-csv/my-device-export/’ WITH ( format=’CSV’, compression=’GZIP’)

If we take the query apart, we perform the following steps:

– From the table “iot-measurements” from the database “secval-measures”.
– Load all entries from the time period now and 24 hours back in time
– For the device with the device-id “my-device-id”.
– Move the result of the query to the S3 bucket named “s3://measure-secval-export-csv”.
– Create the folder named “my-device-export” in the S3 bucket beforehand
– Set the destination format to CSV
– Compress the result with GZIP

We run the query and then switch back to S3 Bucket overview. We run the query and then switch back to S3 Bucket overview. There we open our created bucket. There should be a folder “results” containing our exports.

There you can easily download the results. The process can be perfectly solved programmatically with an AWS Lambda function, for example.

Leave a Reply

Your email address will not be published. Required fields are marked *