Managing Time Series on AWS

Cloud Conf, November 5th, 2020

Time series are a very common data format that describes how things change over time. Some of the most common sources are industrial machines and IoT devices, IT infrastructure stacks (such as hardware, software, and networking components), and applications that share their results over time. Managing time series data efficiently is not easy because the data model doesn’t fit general-purpose databases. For this reason, we built Amazon Timestream, a fast, scalable, and serverless time series database service that makes it easy to collect, store, and process trillions of time series events per day up to 1,000 times faster and at as little as to 1/10th the cost of a relational database.

Transcript

1. ©, 2020 Amazon Web Services, Inc. or its Affiliates. All rights reserved. Danilo Poccia, Chief Evangelist (EMEA) @danilop Managing Time-Series on AWS
2. ©, 2020 Amazon Web Services, Inc. or its Affiliates. All rights reserved. Time-series data Time-series data is a sequence of data points recorded over a time interval for measuring events that change over time
3. ©, 2020 Amazon Web Services, Inc. or its Affiliates. All rights reserved. What is time-series data? 85 86 87 88 89 90 91 92 93 94 95 Humidity % WATER VAPOR 91.0 94.0 86.0 93.0 5:28:15 PM 5:28:30 PM 5:28:45 PM 5:29:05 PM
4. ©, 2020 Amazon Web Services, Inc. or its Affiliates. All rights reserved. Time-series use cases IoT Applications Collect motion or temperature data from the device sensors, interpolate to identify the time ranges without motion, or alert consumers to take actions such as turning off the lights to save energy DevOps Analysis Collect and analyze performance and health metrics such as CPU/memory utilization, network data, and IOPS to monitor health and optimize instance usage. App Analytics Easily store and analyze clickstream data at scale to understand the customer journey—the user activity across your applications over a period of time.
5. ©, 2020 Amazon Web Services, Inc. or its Affiliates. All rights reserved. Amazon Timestream Fast, scalable, and serverless time-series database Purpose built for time-series data Built-in analytics using standard SQL with added interpolation and smoothing functions to identify trends, patterns, and anomalies Serverless and easy to use No servers to manage or instances to provision; software patches, indexes, and database optimizations are handled automatically Performance at scale Capable of ingesting trillions of events daily; the adaptive SQL query engine provides rapid point-in-time queries with its in-memory store, and fast analytical queries through its magnetic store Cost effective Reduces costs by simplifying the complex process of data lifecycle management; pay only for what you ingest, store and query Secure from the ground up All data is encrypted inflight, and at rest using AWS Key Management System (KMS) with customer managed keys (CMK)
6. ©, 2020 Amazon Web Services, Inc. or its Affiliates. All rights reserved. Architectural concepts
7. ©, 2020 Amazon Web Services, Inc. or its Affiliates. All rights reserved. 2020 GA Present Day Continuous releases No maintenance or downtime serverless architecture
8. ©, 2020 Amazon Web Services, Inc. or its Affiliates. All rights reserved. Terminology and concepts: Tables Encrypted container that holds records No data definition or columns are specified at creation Time based data retention policies for controlling data lifecycle within storage tiers
9. ©, 2020 Amazon Web Services, Inc. or its Affiliates. All rights reserved. Terminology and concepts: Storage tiers Two storage tiers: in-memory and magnetic In-memory tier • Handles the ingestion of all data • Automatically handles data deduplication • Optimized for latency sensitive point-in-time queries Magnetic disk tier • Optimized for high performance analytical queries • Cost effective long-term storage
10. ©, 2020 Amazon Web Services, Inc. or its Affiliates. All rights reserved. Terminology and concepts: Dimensions Are a set of attributes that uniquely describe a measurement Each table allows up to 128 unique dimensions All dimensions are represented as varchars Dimensions are dynamically added to the table during ingestion
11. ©, 2020 Amazon Web Services, Inc. or its Affiliates. All rights reserved. time region az vpc hostname measure_name measure_value::double measure_value::bigint 2020-06-17 19:00:00.000000000 us-east-1 1d vpc-1a2b3c4d host-24Gju cpu_utilization 35.0 null 2020-06-17 19:00:01.000000000 us-east-1 1d vpc-1a2b3c4d host-24Gju cpu_utilization 38.2 null 2020-06-17 19:00:02.000000000 us-east-1 1d vpc-1a2b3c4d host-24Gju cpu_utilization 45.3 null 2020-06-17 19:00:00.000000000 us-east-1 1d vpc-1a2b3c4d host-24Gju mem_utilization 54.9 null 2020-06-17 19:00:01.000000000 us-east-1 1d vpc-1a2b3c4d host-24Gju mem_utilization 42.6 null 2020-06-17 19:00:02.000000000 us-east-1 1d vpc-1a2b3c4d host-24Gju mem_utilization 33.3 null 2020-06-17 19:00:00.000000000 us-east-1 1d vpc-1a2b3c4d host-24Gju networks_bytes null 30000 2020-06-17 19:00:01.000000000 us-east-1 1d vpc-1a2b3c4d host-24Gju networks_bytes null 15200 2020-06-17 19:00:01.000000000 us-east-1 1d vpc-1a2b3c4d host-24Gju networks_bytes null 15200 time region az vpc hostname measure_name measure_value::double measure_value::bigint 2020-06-17 19:00:00.000000000 us-east-1 1d vpc-1a2b3c4d host-24Gju cpu_utilization 35.0 null 2020-06-17 19:00:01.000000000 us-east-1 1d vpc-1a2b3c4d host-24Gju cpu_utilization 38.2 null 2020-06-17 19:00:02.000000000 us-east-1 1d vpc-1a2b3c4d host-24Gju cpu_utilization 45.3 null 2020-06-17 19:00:00.000000000 us-east-1 1d vpc-1a2b3c4d host-24Gju mem_utilization 54.9 null 2020-06-17 19:00:01.000000000 us-east-1 1d vpc-1a2b3c4d host-24Gju mem_utilization 42.6 null 2020-06-17 19:00:02.000000000 us-east-1 1d vpc-1a2b3c4d host-24Gju mem_utilization 33.3 null 2020-06-17 19:00:00.000000000 us-east-1 1d vpc-1a2b3c4d host-24Gju networks_bytes null 30000 2020-06-17 19:00:01.000000000 us-east-1 1d vpc-1a2b3c4d host-24Gju networks_bytes null 15200 2020-06-17 19:00:01.000000000 us-east-1 1d vpc-1a2b3c4d host-24Gju networks_bytes null 15200 Example: Dimensions in Amazon Timestream
12. ©, 2020 Amazon Web Services, Inc. or its Affiliates. All rights reserved. Terminology and concepts: Measures Each Amazon Timestream record contains a single measurement comprised of a name and value Each table supports up to 1024 unique measure names Measurements support: boolean, bigint, double, and varchar Measures are dynamically added to the table during ingestion
13. ©, 2020 Amazon Web Services, Inc. or its Affiliates. All rights reserved. time region az vpc hostname measure_name measure_value::double measure_value::bigint 2020-06-17 19:00:00.000000000 us-east-1 1d vpc-1a2b3c4d host-24Gju cpu_utilization 35.0 null 2020-06-17 19:00:01.000000000 us-east-1 1d vpc-1a2b3c4d host-24Gju cpu_utilization 38.2 null 2020-06-17 19:00:02.000000000 us-east-1 1d vpc-1a2b3c4d host-24Gju cpu_utilization 45.3 null 2020-06-17 19:00:00.000000000 us-east-1 1d vpc-1a2b3c4d host-24Gju mem_utilization 54.9 null 2020-06-17 19:00:01.000000000 us-east-1 1d vpc-1a2b3c4d host-24Gju mem_utilization 42.6 null 2020-06-17 19:00:02.000000000 us-east-1 1d vpc-1a2b3c4d host-24Gju mem_utilization 33.3 null 2020-06-17 19:00:00.000000000 us-east-1 1d vpc-1a2b3c4d host-24Gju networks_bytes null 30000 2020-06-17 19:00:01.000000000 us-east-1 1d vpc-1a2b3c4d host-24Gju networks_bytes null 15200 2020-06-17 19:00:01.000000000 us-east-1 1d vpc-1a2b3c4d host-24Gju networks_bytes null 15200 time region az vpc hostname measure_name measure_value::double measure_value::bigint 2020-06-17 19:00:00.000000000 us-east-1 1d vpc-1a2b3c4d host-24Gju cpu_utilization 35.0 null 2020-06-17 19:00:01.000000000 us-east-1 1d vpc-1a2b3c4d host-24Gju cpu_utilization 38.2 null 2020-06-17 19:00:02.000000000 us-east-1 1d vpc-1a2b3c4d host-24Gju cpu_utilization 45.3 null 2020-06-17 19:00:00.000000000 us-east-1 1d vpc-1a2b3c4d host-24Gju mem_utilization 54.9 null 2020-06-17 19:00:01.000000000 us-east-1 1d vpc-1a2b3c4d host-24Gju mem_utilization 42.6 null 2020-06-17 19:00:02.000000000 us-east-1 1d vpc-1a2b3c4d host-24Gju mem_utilization 33.3 null 2020-06-17 19:00:00.000000000 us-east-1 1d vpc-1a2b3c4d host-24Gju networks_bytes null 30000 2020-06-17 19:00:01.000000000 us-east-1 1d vpc-1a2b3c4d host-24Gju networks_bytes null 15200 2020-06-17 19:00:01.000000000 us-east-1 1d vpc-1a2b3c4d host-24Gju networks_bytes null 15200 time region az vpc hostname measure_name measure_value::double measure_value::bigint 2020-06-17 19:00:00.000000000 us-east-1 1d vpc-1a2b3c4d host-24Gju cpu_utilization 35.0 null 2020-06-17 19:00:01.000000000 us-east-1 1d vpc-1a2b3c4d host-24Gju cpu_utilization 38.2 null 2020-06-17 19:00:02.000000000 us-east-1 1d vpc-1a2b3c4d host-24Gju cpu_utilization 45.3 null 2020-06-17 19:00:00.000000000 us-east-1 1d vpc-1a2b3c4d host-24Gju mem_utilization 54.9 null 2020-06-17 19:00:01.000000000 us-east-1 1d vpc-1a2b3c4d host-24Gju mem_utilization 42.6 null 2020-06-17 19:00:02.000000000 us-east-1 1d vpc-1a2b3c4d host-24Gju mem_utilization 33.3 null 2020-06-17 19:00:00.000000000 us-east-1 1d vpc-1a2b3c4d host-24Gju networks_bytes null 30000 2020-06-17 19:00:01.000000000 us-east-1 1d vpc-1a2b3c4d host-24Gju networks_bytes null 15200 2020-06-17 19:00:01.000000000 us-east-1 1d vpc-1a2b3c4d host-24Gju networks_bytes null 15200 Example: Measures in Amazon Timestream
14. ©, 2020 Amazon Web Services, Inc. or its Affiliates. All rights reserved. Terminology and concepts: Time-series Sequence of records that are represented as data points over a time interval for given measurement Every record within the series is comprised of a timestamp, at least one dimensions and an associated measure name/value pair A time-series object can be constructed by using built-in time- series functions Missing data points within a time-series object can be filled with interpolation functions such as last-observation-carry-forward
15. ©, 2020 Amazon Web Services, Inc. or its Affiliates. All rights reserved. time region az vpc hostname measure_name measure_value::double measure_value::bigint 2020-06-17 19:00:00.000000000 us-east-1 1d vpc-1a2b3c4d host-24Gju cpu_utilization 35.0 null 2020-06-17 19:00:01.000000000 us-east-1 1d vpc-1a2b3c4d host-24Gju cpu_utilization 38.2 null 2020-06-17 19:00:02.000000000 us-east-1 1d vpc-1a2b3c4d host-24Gju cpu_utilization 45.3 null 2020-06-17 19:00:00.000000000 us-east-1 1d vpc-1a2b3c4d host-24Gju mem_utilization 54.9 null 2020-06-17 19:00:01.000000000 us-east-1 1d vpc-1a2b3c4d host-24Gju mem_utilization 42.6 null 2020-06-17 19:00:02.000000000 us-east-1 1d vpc-1a2b3c4d host-24Gju mem_utilization 33.3 null 2020-06-17 19:00:00.000000000 us-east-1 1d vpc-1a2b3c4d host-24Gju networks_bytes null 30000 2020-06-17 19:00:01.000000000 us-east-1 1d vpc-1a2b3c4d host-24Gju networks_bytes null 15200 2020-06-17 19:00:01.000000000 us-east-1 1d vpc-1a2b3c4d host-24Gju networks_bytes null 15200 Example: Time-series in Amazon Timestream time region az vpc hostname measure_name measure_value::double measure_value::bigint 2020-06-17 19:00:00.000000000 us-east-1 1d vpc-1a2b3c4d host-24Gju cpu_utilization 35.0 null 2020-06-17 19:00:01.000000000 us-east-1 1d vpc-1a2b3c4d host-24Gju cpu_utilization 38.2 null 2020-06-17 19:00:02.000000000 us-east-1 1d vpc-1a2b3c4d host-24Gju cpu_utilization 45.3 null 2020-06-17 19:00:00.000000000 us-east-1 1d vpc-1a2b3c4d host-24Gju mem_utilization 54.9 null 2020-06-17 19:00:01.000000000 us-east-1 1d vpc-1a2b3c4d host-24Gju mem_utilization 42.6 null 2020-06-17 19:00:02.000000000 us-east-1 1d vpc-1a2b3c4d host-24Gju mem_utilization 33.3 null 2020-06-17 19:00:00.000000000 us-east-1 1d vpc-1a2b3c4d host-24Gju networks_bytes null 30000 2020-06-17 19:00:01.000000000 us-east-1 1d vpc-1a2b3c4d host-24Gju networks_bytes null 15200 2020-06-17 19:00:01.000000000 us-east-1 1d vpc-1a2b3c4d host-24Gju networks_bytes null 15200
16. ©, 2020 Amazon Web Services, Inc. or its Affiliates. All rights reserved. Characteristics of Amazon Timestream data • All records require a timestamp, one or more dimensions, a measurement name and measurement value • Records cannot be deleted or updated • Records are only removed when they reach the retention limit within the magnetic tier (indefinite storage is an option) • First writer wins semantics for handling duplicates • Multiple measures are logically represented as multiple individual records (one measure per record) • Automatically scales to handle highspeed real-time data ingestion
17. ©, 2020 Amazon Web Services, Inc. or its Affiliates. All rights reserved. Data Ingestion
18. ©, 2020 Amazon Web Services, Inc. or its Affiliates. All rights reserved. Data is written using the AWS SDK • Java, Python, Golang, Node.js, .NET, etc. • AWS CLI Connectivity: Data ingestion Adapters and plugins • AWS IoT Core • Amazon Kinesis Data Analytics for Apache Flink connector (GitHub) • Telegraf connector (GitHub)
19. ©, 2020 Amazon Web Services, Inc. or its Affiliates. All rights reserved. Example: Amazon Timestream Ingestion (Python) common_attributes = { 'Dimensions': dimensions, 'MeasureValueType': 'DOUBLE', 'Time': current_timestamp } cpu_utilization = { 'MeasureName': 'cpu_utilization', 'MeasureValue': cpu_measurement } memory_utilization = { 'MeasureName': 'memory_utilization', 'MeasureValue': memory_measurement } records = [cpu_utilization, memory_utilization] result = self.client.write_records(DatabaseName=DATABASE_NAME, TableName=TABLE_NAME, Records=records, CommonAttributes=common_attributes)
20. ©, 2020 Amazon Web Services, Inc. or its Affiliates. All rights reserved. Query processing
21. ©, 2020 Amazon Web Services, Inc. or its Affiliates. All rights reserved. Connectivity: Querying (Mostly) ANSI-2003 SQL for querying • Time-series, interpolation and gap filling functions • 250+ scalar, aggregate and windowing functions No proprietary query language to learn Data is queried using the AWS SDK • Java, Python, Golang, Node.js, .NET, etc. AWS CLI JDBC Driver
22. ©, 2020 Amazon Web Services, Inc. or its Affiliates. All rights reserved. Example: Amazon Timestream query SELECT region, az, hostname, bin(time, 15s) AS binned_timestamp, round(avg(measure_value::double), 2) AS avg_cpu_utilization, round(approx_percentile(measure_value::double, 0.9), 2) AS p90_cpu_utilization, round(approx_percentile(measure_value::double, 0.95), 2) AS p95_cpu_utilization, round(approx_percentile(measure_value::double, 0.99), 2) AS p99_cpu_utilization FROM devops.host_metrics WHERE measure_name = 'cpu_utilization' -- Predicate on measure_name AND time > ago(2h) -- Predicate on time AND hostname = 'host-24Gju' -- Optional predicates on other dimensions GROUP BY region, hostname, az, bin(time, 15s) -- bin and GROUP BY time ORDER BY binned_timestamp ASC
23. ©, 2020 Amazon Web Services, Inc. or its Affiliates. All rights reserved. Amazon Timestream integrations
24. ©, 2020 Amazon Web Services, Inc. or its Affiliates. All rights reserved. Supported integrations and drivers Amazon QuickSight AWS IoT Core Grafana (Open Source Edition) Database Tools via JDBC • SQL Workbench/J, DataGrip, DBVisualizer, etc. AWSLabs (GitHub) • Kinesis Data Analytics for Apache Flink connector • Telegraf connector • Amazon SageMaker Notebook example
25. ©, 2020 Amazon Web Services, Inc. or its Affiliates. All rights reserved. Amazon Timestream + JDBC
26. ©, 2020 Amazon Web Services, Inc. or its Affiliates. All rights reserved. Amazon Timestream + Grafana
27. ©, 2020 Amazon Web Services, Inc. or its Affiliates. All rights reserved. Amazon Timestream + Grafana
28. ©, 2020 Amazon Web Services, Inc. or its Affiliates. All rights reserved. Amazon Timestream + Amazon QuickSight
29. ©, 2020 Amazon Web Services, Inc. or its Affiliates. All rights reserved. Analytics with Timestream
30. ©, 2020 Amazon Web Services, Inc. or its Affiliates. All rights reserved. IoT with Timestream
31. ©, 2020 Amazon Web Services, Inc. or its Affiliates. All rights reserved. DevOps with Timestream
32. ©, 2020 Amazon Web Services, Inc. or its Affiliates. All rights reserved. Demo
33. ©, 2020 Amazon Web Services, Inc. or its Affiliates. All rights reserved. Amazon Timestream Server #2 Server #3 Server #1 cpu memory swap disk
34. ©, 2020 Amazon Web Services, Inc. or its Affiliates. All rights reserved. SELECT country, city, hostname, bin(time, 10m) AS binned_time, avg(measure_value::double) AS avg_cpu_utilization FROM MyDatabase.MyTable WHERE measure_name = 'cpu_utilization' AND time > ago(2h) GROUP BY country, city, hostname, bin(time, 10m) ORDER BY country, city, hostname, binned_time
35. ©, 2020 Amazon Web Services, Inc. or its Affiliates. All rights reserved. SELECT country, city, hostname, CREATE_TIME_SERIES(time, measure_value::double) AS cpu_utilization FROM MyDatabase.MyTable WHERE measure_name='cpu_utilization' AND time > ago(1m) GROUP BY country, city, hostname
36. ©, 2020 Amazon Web Services, Inc. or its Affiliates. All rights reserved. WITH binned_timeseries AS ( SELECT country, city, hostname, bin(time, 10m) AS binned_time, avg(measure_value::double) AS avg_cpu_utilization FROM MyDatabase.MyTable WHERE measure_name = 'cpu_utilization' AND time > ago(2h) GROUP BY country, city, hostname, bin(time, 10m) ), interpolated_timeseries AS ( SELECT country, city, hostname, INTERPOLATE_LINEAR( CREATE_TIME_SERIES(binned_time, avg_cpu_utilization), SEQUENCE(min(binned_time), max(binned_time), 1m) ) AS interpolated_avg_cpu_utilization FROM binned_timeseries GROUP BY country, city, hostname ) SELECT * FROM interpolated_timeseries
37. ©, 2020 Amazon Web Services, Inc. or its Affiliates. All rights reserved. WITH binned_timeseries AS ( SELECT country, city, hostname, bin(time, 10m) AS binned_time, avg(measure_value::double) AS avg_cpu_utilization FROM MyDatabase.MyTable WHERE measure_name = 'cpu_utilization' AND time > ago(2h) GROUP BY country, city, hostname, bin(time, 10m) ), interpolated_timeseries AS ( SELECT country, city, hostname, INTERPOLATE_LINEAR( CREATE_TIME_SERIES(binned_time, avg_cpu_utilization), SEQUENCE(min(binned_time), max(binned_time), 1m) ) AS interpolated_avg_cpu_utilization FROM binned_timeseries GROUP BY country, city, hostname ) SELECT country, city, hostname, time, round(avg(value), 2) AS interpolated_cpu FROM interpolated_timeseries CROSS JOIN UNNEST(interpolated_avg_cpu_utilization) GROUP BY country, city, hostname, time
38. ©, 2020 Amazon Web Services, Inc. or its Affiliates. All rights reserved. Additional Resources
39. ©, 2020 Amazon Web Services, Inc. or its Affiliates. All rights reserved. Additional Resources Store and Access Time Series Data at Any Scale with Amazon Timestream https://aws.amazon.com/blogs/aws/store-and-access-time-series-data-at-any-scale-with-amazon-timestream-now-generally-available/ Amazon Timestream Tools and Samples https://github.com/awslabs/amazon-timestream-tools
40. ©, 2020 Amazon Web Services, Inc. or its Affiliates. All rights reserved. Amazon Timestream Tools and Samples Sample Applications (available in various languages): https://github.com/awslabs/amazon-timestream-tools/tree/master/sample_apps Kinesis Data Analytics for Apache Flink connector example: https://github.com/awslabs/amazon-timestream-tools/tree/master/integrations/flink_connector SageMaker example: https://github.com/awslabs/amazon-timestream-tools/tree/master/integrations/sagemaker Telegraf example: https://github.com/awslabs/amazon-timestream-tools/tree/master/integrations/telegraf Continuous data-generator tools: https://github.com/awslabs/amazon-timestream-tools/tree/master/tools/continuous_data_ingestor
41. ©, 2020 Amazon Web Services, Inc. or its Affiliates. All rights reserved. Thank you! Please give your feedback! J @danilop