Publisher
cloudquery
Repository
github.comLatest version
v2.3.5
Type
Policy
Published
Category
Cloud Infrastructure
Loading addon documentation
Publisher
cloudquery
Repository
github.comLatest version
v2.3.5
Type
Policy
Published
Category
Cloud Infrastructure
Loading addon documentation
We use tracking cookies to understand how you use the product and help us improve it. Please accept cookies to help us improve. You can always opt out later via the link in the footer.
select account_id, count(*)
from aws_resources
group by account_id
order by count(*) desc
select account_id, service, count(*)
from aws_resources
group by account_id, service
order by count(*) desc;
select * from aws_resources
where tags is null or tags = '{}';
_cq_id
_cq_source_name
_cq_sync_time
account_id
request_account_id
type
arn
region
tags
partition
service
_cq_table
dbt
:pip install dbt-postgres
mkdir -p ~/.dbt
profiles.yml
file in your profile directory (e.g. ~/.dbt/profiles.yml
):aws_asset_inventory: # This should match the name in your dbt_project.yml
target: dev
outputs:
dev:
type: postgres
host: 127.0.0.1
user: postgres
pass: pass
port: 5432
dbname: postgres
schema: public # default schema where dbt will build the models
threads: 1 # number of threads to use when running in parallel
profiles.yml
, you should test the connection to ensure everything is configured correctly:dbt debug
cloudquery login
in your terminalkind: source
spec:
name: aws # The source type, in this case, AWS.
path: cloudquery/aws # The plugin path for handling AWS sources.
registry: cloudquery # The registry from which the AWS plugin is sourced.
version: "v25.5.3" # The version of the AWS plugin.
tables: ["aws_ec2_instances"] # Include any tables that meet your requirements, separated by commas
destinations: ["postgresql"] # The destination for the data, in this case, PostgreSQL.
spec:
---
kind: destination
spec:
name: "postgresql" # The type of destination, in this case, PostgreSQL.
path: "cloudquery/postgresql" # The plugin path for handling PostgreSQL as a destination.
registry: "cloudquery" # The registry from which the PostgreSQL plugin is sourced.
version: "v8.0.1" # The version of the PostgreSQL plugin.
spec:
connection_string: "${POSTGRESQL_CONNECTION_STRING}" # set the environment variable in a format like
# postgresql://postgres:pass@localhost:5432/postgres?sslmode=disable
# You can also specify the connection string in DSN format, which allows for special characters in the password:
# connection_string: "user=postgres password=pass+0-[word host=localhost port=5432 dbname=postgres"
dbt_project.yml
resides.dbt run
command, it might be useful to check for any potential issues:dbt compile
dbt run
dbt
models and create tables/views in your destination database as defined in your models.dbt-core
and not dbt-cloud-cli
as dbt-core does not require extra authentication.dbt run
commands can be used:dbt run --select +<model_name>
dbt run --models +<model_name>
aws_asset_inventory
to ClickHouse as a destination using dbt.aws_resources
model for this purpose. However, due to ClickHouse's limitations in handling large and complex queries—specifically related to query size and AST (Abstract Syntax Tree) restrictions—you may encounter some challenges. To resolve these, follow the steps outlined below.cloudquery.yml
ClickHouse Configuration File #cloudquery.yml
file in your dbt project directory with the following configuration:<profiles>
<default>
<max_query_size>10000000</max_query_size>
<max_ast_elements>150000</max_ast_elements>
</default>
</profiles>
docker run --platform linux/amd64 --name clickhouse-server --rm -p 8123:8123 -p 9000:9000 \
-e CLICKHOUSE_PASSWORD=test \
-e CLICKHOUSE_USER=cq \
-e CLICKHOUSE_DB=cloudquery \
-e CLICKHOUSE_DEFAULT_ACCESS_MANAGEMENT=1 \
-v ./clickhouse.xml:/etc/clickhouse-server/users.d/cloudquery.xml \
clickhouse/clickhouse-server:22.1.2
dbt
Profile for ClickHouse #dbt
profile to include the ClickHouse destination:aws_asset_inventory:
target: dev
outputs:
dev:
type: clickhouse
schema: cloudquery
host: localhost
port: 9000
user: cq
password: test
dbt
#dbt
with the appropriate profile:dbt run
dbt
models and sync the data to your ClickHouse destination. Make sure everything is properly set up in the dbt profile to connect to the ClickHouse server.