ParthaI
commented
1 month ago Hello, @aidansteele,
I apologize for any inconvenience caused.
The issue you're experiencing with the aws_ec2_ami_shared table may be due to the lack of pagination in the API call (pagination wasn't supported earlier). According to the API documentation, it is strongly recommended to use only paginated requests. Unpaginated requests are susceptible to throttling and timeouts..
To address this, I have raised a PR that includes the following enhancements:
- Implemented pagination for the API call.
- Optimized query timing and the number of API calls by supporting batch operations, allowing multiple image IDs or owner IDs in a single API call.
- Added support for
owner_idsandimage_idsto facilitate batch operations. - The query format to perform batch operations:
SELECT * FROM aws_ec2_ami_shared WHERE owner_ids = '["123456789089", "938475869606"]'SELECT * FROM aws_ec2_ami_shared WHERE image_ids = '["ami-003a0d7f585604444", "ami-0038c1e63dd57df14"]'
Please note that the table design has not yet been finalized. In our environment, we have limited resources to test the performance of these changes. It would be greatly appreciated if you could test the PR branch (issue-2259) and share your observations.
Official blog post query(It should run more smoothly than before):
WITH instances AS (
SELECT
instance_id,
instance_type,
account_id,
tags ->> 'Name' AS instance_name,
_ctx ->> 'connection_name' AS account_name,
instance_state,
region,
image_id
FROM
aws_ec2_instance
)
SELECT DISTINCT
aws_ec2_ami_shared.image_id as image_id,
aws_ec2_ami_shared.owner_id as image_owner_id,
aws_ec2_ami_shared.image_owner_alias as image_owner_name,
instances.instance_name,
instances.account_name,
instances.region,
aws_ec2_ami_shared.name as image_name
FROM
instances
LEFT JOIN aws_ec2_ami_shared ON aws_ec2_ami_shared.image_id=instances.image_id
WHERE aws_ec2_ami_shared.image_owner_alias != 'amazon'
AND aws_ec2_ami_shared.image_owner_alias != 'self'Taking advantage of batch operation:
with instances as (
select
instance_id,
instance_type,
account_id,
tags ->> 'Name' as instance_name,
_ctx ->> 'connection_name' as account_name,
instance_state,
region,
image_id
from
aws_ec2_instance
),
all_image_ids as (
select
json_agg(image_id)::jsonb as image_ids -- Cast to jsonb
from
instances
),
shared_ami as (
select
s.*
from
aws_ec2_ami_shared as s,
all_image_ids
where s.image_ids = all_image_ids.image_ids
)
select distinct
shared_ami.image_id as image_id,
shared_ami.owner_id as image_owner_id,
shared_ami.image_owner_alias as image_owner_name,
instances.instance_name,
instances.account_name,
instances.region,
shared_ami.name as image_name
from
instances
left join shared_ami on shared_ami.image_id=instances.image_id
where shared_ami.image_owner_alias != 'amazon'
and shared_ami.image_owner_alias != 'self';Thank you for your understanding and assistance!
aidansteele
Describe the bug The Steampipe AWS plugin appears to be making excessive duplicate API calls when running well-formed SQL queries. This is causing queries to run much slower than they should.
Steampipe version (
steampipe -v) Steampipe v0.23.3Plugin version (
steampipe plugin list) hub.steampipe.io/plugins/turbot/aws 0.116.0To reproduce I ran the Ensuring AMIs are from trusted sources sample query from this official blog post. In case the blog post gets updated, this is the exact SQL that I ran:
The query ultimately failed (for reasons that I suspect are not Steampipe's fault: it got a
RequestExpired: Request has expiredresponse from the EC2 API, indicating a clock skew issue), but it made at least 53,899 calls toec2:DescribeImagesas per CloudTrail. A lot of these were duplicates, e.g. see these screenshot of duplicate AMI lookupsExpected behavior I would have expected Steampipe to deduplicate/cache these API calls and (presumably) for the query to be executed much faster.
Additional context This query was executed against an aggregation of 225 AWS accounts and two AWS regions. I am not an SQL expert and I assume that the SQL query in the Steampipe blog is optimal.