Init aws_resources view

George_Tsopouridis · January 13, 2025, 11:41am

plugins/source/aws/views/resources.sql

66d259516

DROP VIEW IF EXISTS aws_resources;

DO $$
DECLARE
    tbl TEXT;
    strSQL TEXT = '';
BEGIN
-- iterate over every table in our information_schema that has an `arn` column available
FOR tbl IN
    SELECT DISTINCT table_name
    FROM information_schema.columns
    WHERE table_name LIKE 'aws_%s' and COLUMN_NAME IN ('account_id', 'request_account_id')
    INTERSECT
    SELECT table_name
    FROM information_schema.columns
    WHERE table_name LIKE 'aws_%s' and COLUMN_NAME = 'arn'
LOOP
    -- UNION each table query to create one view
 	IF NOT (strSQL = ''::TEXT) THEN
		strSQL = strSQL || ' UNION ALL ';

This file has been truncated. show original

How to get the latest sql for generating aws_resources view? I am following the documentation here https://hub.cloudquery.io/addons/transformation/cloudquery/aws-asset-inventory/latest/docs but it not clear to me. Please note that I am syncing the AWS resources using ECS containers and I just want to create once the aws_resources view from my local env.

erez · January 13, 2025, 2:37pm

Hi @George_Tsopouridis to create the view you would need to download the dbt project via the download link

then follow the instructions to run it AWS Asset Inventory Transformation documentation

George_Tsopouridis · January 13, 2025, 2:56pm

Thanks @erez You are right. In the meantime, I’ve managed to apply the process and it worked for me.

Topic		Replies	Views
Issue with aws_asset_inventory transformation always showing region as unavailable CloudQuery Plugins	1	5	July 29, 2024
Join tables from two CloudQuery databases for account metadata and AWS resources CloudQuery Plugins	3	2	February 22, 2024
AWS Plugin - aws_rds_instance_resource_metrics CloudQuery Plugins	9	63	December 11, 2024
CloudQuery AWS source plugin v23 update missing column information CloudQuery Plugins	6	2	May 14, 2024
CloudQuery issue with missing read-replica in MySQL database CloudQuery Plugins	3	1	January 24, 2024

Init aws_resources view

Related topics