Join tables from two CloudQuery databases for account metadata and AWS resources

CloudQuery Plugins
1

Hi Team,

We have 2 databases in CloudQuery. We want all the tables from DB1 to be able to join with all the tables in DB2. We have our organization and account metadata information in DB1 and all our AWS resources inventory in DB2; in my case, it is a CloudQuery-generated AWS/Azure tables. We need to connect account metadata with AWS resources for reference.

  1. If not, then should we rather replicate and do real-time sync of DB1 tables in DB2? If yes, how do we do it?
  2. Can we create a custom table in DB2? How do we create custom tables in this case?
  3. If we can create a custom table, will it get affected when we rerun CloudQuery inventory for AWS/Azure resources?
2

Hi @patient-vulture :wave:

DB1 has data that was not created by CloudQuery and DB2 has data that was created by CloudQuery. Which database are you using? If you’re using Postgres, we have a source plugin to do real-time sync to a Postgres destination. See PostgreSQL Source Plugin Documentation.

3

Hi @erez,

Thank you for the response. Yes, DB1 has data that was not created by CloudQuery and DB2 has data that was created by CloudQuery. Both are PostgreSQL.

Can you please address questions 2 and 3 as well?

4

For 2. and 3. you can create a custom table by running an SQL query on the DB. CloudQuery won’t touch the custom tables as long as their names don’t conflict with the tables of the source plugin (for example AWS Source Plugin Tables).

Might be better to create a separate schema for any custom tables though for clear separation.