Enable Analysis of AWS Metrics with Extended Metrics Points `aws_cloudwatch_metric_statistic_data_point`

[kaushikkishore]

Table name aws_cloudwatch_metric_statistic_data_point

New column name ExtendedStatistics - like p95 or p99

Description This feature request proposes extending the listCloudWatchMetricStatisticDataPoints function in Steampipe to support retrieving extended statistics like p95 and p99 for AWS metrics.

Current limitation The current implementation only retrieves the basic statistics for AWS metrics.

Proposed Solution Introduce a new optional argument ExtendedStatistics of type []string to the listCloudWatchMetricStatisticDataPoints function. This argument will allow users to specify a list of extended statistics (e.g., "p99", "p95") to be retrieved along with the basic statistics. Update the function to include the ExtendedStatistics argument in the request to the CloudWatch API. Add new columns to the output schema to accommodate the retrieved extended statistics values.

Additionally, I suggest augmenting the function to include support for reading and processing these extended metric results. I have already implemented these modifications locally and verified their functionality through thorough testing.

Furthermore, I aim to generalise the codebase to facilitate future extensibility. By implementing a flexible mechanism, users will have the capability to specify any desired extended metric, such as "p65", in their select queries and retrieve the corresponding data points. This approach enhances the versatility of the codebase and accommodates potential future requirements effortlessly.

Alternatives Considered I have contemplated alternative approaches, including generating all possible combinations of extended metrics points ranging from p1 to p99. However, this approach is deemed impractical as it would result in excessive data retrieval, potentially burdening the system with unnecessary processing overhead. Therefore, I am seeking guidance from the team to explore the most effective approach to address this requirement.

[bigdatasourav]

The ExtendedStatistics parameter in GetMetricStatisticsInput is of type []string and is used for specifying percentile statistics between p0.0 and p100. In the GetMetricStatistics API call, you must choose either Statistics or ExtendedStatistics, but not both. For example, you can specify ExtendedStatistics as []string{"p95", "p100"}. The response for these parameters will be in the format map[string]float64, such as {"p100": 0.500000000000303, "p95": 0.49568533302992535}.

However, when attempting to query this data, matching the extended_statistics directly in a WHERE clause can be problematic. This is because the WHERE condition expects a string representation of the array, which does not directly correspond to the map structure returned in the response.

select
  *
from
  aws_cloudwatch_metric_statistic_data_point
where
  namespace = 'AWS/EC2'
  and metric_name = 'CPUUtilization'
  and extended_statistics = '["p100"]'
  and dimensions = '[
    {"Name": "InstanceId", "Value": "i-04a9bf67bc23432432"}
    ]' 
order by
  timestamp;

To address this, consider modifying the schema by introducing two new columns: extended_statistics_labels and extended_statistics. The extended_statistics_labels should be a JSON column to store the labels (e.g., ["p100", "p95"]) as an optional parameter. The extended_statistics column should be a JSON type to hold the actual numerical values (e.g., {"p100": 0.500000000000303, "p95": 0.49568533302992535}).

@cbruno10 @misraved @kaushikkishore please share your thoughts.

[github-actions[bot]]

This issue is stale because it has been open 60 days with no activity. Remove stale label or comment or this will be closed in 30 days.