Hi CQ team,
Would it be possible to add columns origin_type
and origin_domain
to the table aws_cloudfront_distributions
?
Hi CQ team,
Would it be possible to add columns origin_type
and origin_domain
to the table aws_cloudfront_distributions
?
Can you link to the API docs of how to get those fields?
origin_domain you are already providing but in the distribution_config. However, as it’s a jsonb, it’s not possible to join on this field.
Even if selecting like:
jsonb_array_elements(distribution_config->'Origins'->'Items')->>'DomainName'
Because a distribution can have multiple origins, how would you want that represented?
Hm, maybe I’ll explain my use case.
I’d like to select distributions and their origins where origin_type = s3
and then join this information to the aws_s3_buckets
on bucket_name
.
That is a very interesting use case! I am going to look into it by spinning up a CloudFront distribution with S3 origin and get back to you…
this is my SQL query
SELECT id, jsonb_array_elements(distribution_config->'Origins'->'Items')->>'DomainName' AS origin_name, domain_name
FROM aws_cloudfront_distributions
Here is a query that is able to join S3 origins to an S3 bucket:
SELECT ORIGIN_ID,
ORIGIN_NAME,
DOMAIN_NAME,
AWS_S3_BUCKETS.*
FROM
(SELECT ID as ORIGIN_ID,
JSONB_ARRAY_ELEMENTS(DISTRIBUTION_CONFIG -> 'Origins' -> 'Items') ->> 'DomainName' AS ORIGIN_NAME,
DOMAIN_NAME
FROM AWS_CLOUDFRONT_DISTRIBUTIONS) AS CLOUDFRONT
LEFT JOIN AWS_S3_BUCKETS ON CLOUDFRONT.ORIGIN_NAME = AWS_S3_BUCKETS.NAME || '.s3.' || AWS_S3_BUCKETS.REGION || '.amazonaws.com'