# Cloudquery tables lack foreign key relationships for improved data flexibility

**URL:** <https://community.cloudquery.io/t/cloudquery-tables-lack-foreign-key-relationships-for-improved-data-flexibility/328>\
**Category:** CloudQuery Plugins\
**Created:** [March 5, 2024, 9:56am UTC](https://community.cloudquery.io/t/cloudquery-tables-lack-foreign-key-relationships-for-improved-data-flexibility/328 "2024-03-05T09:56:08Z")\
**Posts on this page:** 20\
**Page:** 2

<div class="post-metadata">

**Author:** ![concise-oryx](https://avatars.discourse-cdn.com/v4/letter/c/67e7ee/32.png) [@concise-oryx](https://community.cloudquery.io/u/concise-oryx)\
**Post date:** [March 5, 2024, 3:47pm UTC](https://community.cloudquery.io/t/cloudquery-tables-lack-foreign-key-relationships-for-improved-data-flexibility/328/21 "2024-03-05T15:47:12Z")

</div>

Then how to join these tables without having foreign keys?

---

<div class="post-metadata">

**Author:** ![erez](https://sea1.discourse-cdn.com/flex001/user_avatar/community.cloudquery.io/erez/32/40_2.png) [@erez](https://community.cloudquery.io/u/erez)\
**Post date:** [March 5, 2024, 3:47pm UTC](https://community.cloudquery.io/t/cloudquery-tables-lack-foreign-key-relationships-for-improved-data-flexibility/328/22 "2024-03-05T15:47:13Z")

</div>

So you could try using that. You can still join without foreign keys; the join will still work. Generally, it’s better to rely on the information returned from AWS to logically link different resources (like `load_balancer_arns`) instead of using `_cq_id` and `_cq_parent_id`.

Once you find which columns work for you to link the data, you’d create a view from the query.

Please let me know if that makes sense.

---

<div class="post-metadata">

**Author:** ![concise-oryx](https://avatars.discourse-cdn.com/v4/letter/c/67e7ee/32.png) [@concise-oryx](https://community.cloudquery.io/u/concise-oryx)\
**Post date:** [March 5, 2024, 4:05pm UTC](https://community.cloudquery.io/t/cloudquery-tables-lack-foreign-key-relationships-for-improved-data-flexibility/328/23 "2024-03-05T16:05:01Z")

</div>

Hi @erez,

One more question. You said we can use `_cq_parent_id` and `_cq_id` to relate the parent and the child table. But I think there are no relationships externally defined in the tables in the database, right? Should I need to create foreign keys explicitly? Also, is there any way in the future where you can try to add the relation for these tables?

Hey @erez, also another thing. When I am trying to sync my tables locally for one account, I am getting some errors.

For example:

```auto
2024-03-06T05:07:56Z ERR table resolver finished with error error="operation error API Gateway: GetUsagePlans, https response error StatusCode: 403, RequestID: a427e56b-a886-43aa-bb40-1a6589b2fghd92, api error AccessDeniedException: User: arn:aws:sts:❌xxxxxxxxx-assumed-role/sso-devops-iam-role/xxxxxxxxxxxxxx@xxxxx.com is not authorized to perform: apigateway:GET on resource: arn:aws:apigateway:ap-northeast-3::/usageplans with an explicit deny in a service control policy" client=xxxxxxxxxxxx:ap-northeast-3 module=aws-src table=aws_apigateway_usage_plans

```

Can you also specify on this?

---

<div class="post-metadata">

**Author:** ![erez](https://sea1.discourse-cdn.com/flex001/user_avatar/community.cloudquery.io/erez/32/40_2.png) [@erez](https://community.cloudquery.io/u/erez)\
**Post date:** [March 6, 2024, 9:04am UTC](https://community.cloudquery.io/t/cloudquery-tables-lack-foreign-key-relationships-for-improved-data-flexibility/328/24 "2024-03-06T09:04:56Z")

</div>

Hi @concise-oryx, you don’t need to create foreign keys. You can find the relations between the tables using the tables command I shared:

```auto
cloudquery tables

```

That command generates a JSON file with information on the relation. I don’t think we’ll add foreign keys for users as:

1. It makes it harder to migrate the schema.
2. It’s only relevant for a few destinations.

Regarding the error, it looks like you’re missing some permissions to perform that API call.

---

<div class="post-metadata">

**Author:** ![concise-oryx](https://avatars.discourse-cdn.com/v4/letter/c/67e7ee/32.png) [@concise-oryx](https://community.cloudquery.io/u/concise-oryx)\
**Post date:** [March 6, 2024, 9:05am UTC](https://community.cloudquery.io/t/cloudquery-tables-lack-foreign-key-relationships-for-improved-data-flexibility/328/25 "2024-03-06T09:05:49Z")

</div>

Hi @erez,

I gave the `cloudquery tables` command but I’m running into a different issue:

```auto
cloudquery tables C:/Users/gkaturi

```

Loading spec(s) from `C:/Users/gkaturi`

```auto
Error: failed to load spec(s) from C:/Users/gkaturi. Error: expecting at least one source

```

---

<div class="post-metadata">

**Author:** ![erez](https://sea1.discourse-cdn.com/flex001/user_avatar/community.cloudquery.io/erez/32/40_2.png) [@erez](https://community.cloudquery.io/u/erez)\
**Post date:** [March 6, 2024, 9:18am UTC](https://community.cloudquery.io/t/cloudquery-tables-lack-foreign-key-relationships-for-improved-data-flexibility/328/26 "2024-03-06T09:18:53Z")

</div>

What is the content of `C:/Users/gkaturi`? You should have at least one source configuration in that directory to generate source tables.

---

<div class="post-metadata">

**Author:** ![concise-oryx](https://avatars.discourse-cdn.com/v4/letter/c/67e7ee/32.png) [@concise-oryx](https://community.cloudquery.io/u/concise-oryx)\
**Post date:** [March 6, 2024, 9:29am UTC](https://community.cloudquery.io/t/cloudquery-tables-lack-foreign-key-relationships-for-improved-data-flexibility/328/27 "2024-03-06T09:29:03Z")

</div>

This is my local directory. What should I place there after `cloudquery tables` in that command?

---

<div class="post-metadata">

**Author:** ![erez](https://sea1.discourse-cdn.com/flex001/user_avatar/community.cloudquery.io/erez/32/40_2.png) [@erez](https://community.cloudquery.io/u/erez)\
**Post date:** [March 6, 2024, 10:11am UTC](https://community.cloudquery.io/t/cloudquery-tables-lack-foreign-key-relationships-for-improved-data-flexibility/328/28 "2024-03-06T10:11:23Z")

</div>

You should point to the same path you use to run `cloudquery sync`.

---

<div class="post-metadata">

**Author:** ![concise-oryx](https://avatars.discourse-cdn.com/v4/letter/c/67e7ee/32.png) [@concise-oryx](https://community.cloudquery.io/u/concise-oryx)\
**Post date:** [March 6, 2024, 1:02pm UTC](https://community.cloudquery.io/t/cloudquery-tables-lack-foreign-key-relationships-for-improved-data-flexibility/328/29 "2024-03-06T13:02:55Z")

</div>

Hi @erez,

I have tried syncing the CloudQuery tables, but I am running into an issue now:

```auto
failed to sync records: failed to sync unmanaged client: your configuration references the following premium tables: "aws_accessanalyzer_analyzer_findings_v2,aws_autoscaling_warm_pools,aws_backupgateway_gateways,aws_budgets_budgets,aws_budgets_actions,aws_cloudwatch_metrics,aws_cloudwatch_metric_statistics,aws_cod...". Please run `cloudquery login` or use a valid API Key which can be generated via https://cloud.cloudquery.io to allow the sync to succeed

```

My versions:

- cli = “v5.8.1”
- db = “v7.0.0”
- aws = “v23.1.0”
- azure = “v11.0.0”

Should I need to downgrade or upgrade anything here to avoid this error?

---

<div class="post-metadata">

**Author:** ![erez](https://sea1.discourse-cdn.com/flex001/user_avatar/community.cloudquery.io/erez/32/40_2.png) [@erez](https://community.cloudquery.io/u/erez)\
**Post date:** [March 6, 2024, 1:03pm UTC](https://community.cloudquery.io/t/cloudquery-tables-lack-foreign-key-relationships-for-improved-data-flexibility/328/30 "2024-03-06T13:03:49Z")

</div>

Hi @concise-oryx,

You’d need to run `cloudquery login` since you’re referencing paid tables.

---

<div class="post-metadata">

**Author:** ![concise-oryx](https://avatars.discourse-cdn.com/v4/letter/c/67e7ee/32.png) [@concise-oryx](https://community.cloudquery.io/u/concise-oryx)\
**Post date:** [March 6, 2024, 2:38pm UTC](https://community.cloudquery.io/t/cloudquery-tables-lack-foreign-key-relationships-for-improved-data-flexibility/328/31 "2024-03-06T14:38:04Z")

</div>

How can I not refer to these paid tables? Can you please specify how I would clear this error without having a login? I should not be able to sync the tables because I am not a premium member.

---

<div class="post-metadata">

**Author:** ![erez](https://sea1.discourse-cdn.com/flex001/user_avatar/community.cloudquery.io/erez/32/40_2.png) [@erez](https://community.cloudquery.io/u/erez)\
**Post date:** [March 6, 2024, 3:30pm UTC](https://community.cloudquery.io/t/cloudquery-tables-lack-foreign-key-relationships-for-improved-data-flexibility/328/32 "2024-03-06T15:30:48Z")

</div>

You can use `skip_tables:` in the configuration.

---

<div class="post-metadata">

**Author:** ![concise-oryx](https://avatars.discourse-cdn.com/v4/letter/c/67e7ee/32.png) [@concise-oryx](https://community.cloudquery.io/u/concise-oryx)\
**Post date:** [March 6, 2024, 4:09pm UTC](https://community.cloudquery.io/t/cloudquery-tables-lack-foreign-key-relationships-for-improved-data-flexibility/328/33 "2024-03-06T16:09:59Z")

</div>

Okay… Should I skip all of them? Which are the premium tables?

---

<div class="post-metadata">

**Author:** ![erez](https://sea1.discourse-cdn.com/flex001/user_avatar/community.cloudquery.io/erez/32/40_2.png) [@erez](https://community.cloudquery.io/u/erez)\
**Post date:** [March 6, 2024, 4:15pm UTC](https://community.cloudquery.io/t/cloudquery-tables-lack-foreign-key-relationships-for-improved-data-flexibility/328/34 "2024-03-06T16:15:28Z")

</div>

You can use this list as a reference: [CloudQuery AWS Premium Tables](https://hub.cloudquery.io/plugins/source/cloudquery/aws/v23.2.0/docs#premium-tables).

Please note that for new versions of the plugin, you’d still need to log in regardless of whether you use paid tables or not.

---

<div class="post-metadata">

**Author:** ![concise-oryx](https://avatars.discourse-cdn.com/v4/letter/c/67e7ee/32.png) [@concise-oryx](https://community.cloudquery.io/u/concise-oryx)\
**Post date:** [March 6, 2024, 4:17pm UTC](https://community.cloudquery.io/t/cloudquery-tables-lack-foreign-key-relationships-for-improved-data-flexibility/328/35 "2024-03-06T16:17:23Z")

</div>

From which versions onwards for AWS, Azure, PostgreSQL, and CLI should we compulsorily log in?

---

<div class="post-metadata">

**Author:** ![erez](https://sea1.discourse-cdn.com/flex001/user_avatar/community.cloudquery.io/erez/32/40_2.png) [@erez](https://community.cloudquery.io/u/erez)\
**Post date:** [March 6, 2024, 5:08pm UTC](https://community.cloudquery.io/t/cloudquery-tables-lack-foreign-key-relationships-for-improved-data-flexibility/328/36 "2024-03-06T17:08:48Z")

</div>

All plugin versions released starting from 22 February 2024.

Is there something preventing you from logging in? You can still use some tables for free with the login and get the latest updates. Happy to learn more about your use case.

---

<div class="post-metadata">

**Author:** ![concise-oryx](https://avatars.discourse-cdn.com/v4/letter/c/67e7ee/32.png) [@concise-oryx](https://community.cloudquery.io/u/concise-oryx)\
**Post date:** [March 7, 2024, 4:52am UTC](https://community.cloudquery.io/t/cloudquery-tables-lack-foreign-key-relationships-for-improved-data-flexibility/328/37 "2024-03-07T04:52:56Z")

</div>

Sure… thanks @Erez for your valuable information.

Hi @erez, the **aws\_inspector\_findings** table in CloudQuery is taking more time to sync for all the accounts in our DB and then failing for some accounts randomly. It is almost running for 7 to 8 hours and failing for some random accounts.

What should we do for this table?

Also, one thing which we observed is, when we skip the **aws\_inspector\_findings** table, this sync is working fine.

---

<div class="post-metadata">

**Author:** ![erez](https://sea1.discourse-cdn.com/flex001/user_avatar/community.cloudquery.io/erez/32/40_2.png) [@erez](https://community.cloudquery.io/u/erez)\
**Post date:** [March 12, 2024, 11:15am UTC](https://community.cloudquery.io/t/cloudquery-tables-lack-foreign-key-relationships-for-improved-data-flexibility/328/38 "2024-03-12T11:15:09Z")

</div>

Hi @concise-oryx, what you’d usually do is create 2 separate sync jobs, one with `aws_inspector_findings` included and another with `aws_inspector_findings` skipped. Then you’d run those on separate schedules.

---

<div class="post-metadata">

**Author:** ![concise-oryx](https://avatars.discourse-cdn.com/v4/letter/c/67e7ee/32.png) [@concise-oryx](https://community.cloudquery.io/u/concise-oryx)\
**Post date:** [March 12, 2024, 11:15am UTC](https://community.cloudquery.io/t/cloudquery-tables-lack-foreign-key-relationships-for-improved-data-flexibility/328/39 "2024-03-12T11:15:53Z")

</div>

How to do 2 sync jobs?  
Also, in one account we have more than one lakh inspector findings, and there may be a case this might be for multiple accounts too. How would I handle this case then?  
@erez can you please suggest or help me out here?

---

<div class="post-metadata">

**Author:** ![erez](https://sea1.discourse-cdn.com/flex001/user_avatar/community.cloudquery.io/erez/32/40_2.png) [@erez](https://community.cloudquery.io/u/erez)\
**Post date:** [March 12, 2024, 11:40am UTC](https://community.cloudquery.io/t/cloudquery-tables-lack-foreign-key-relationships-for-improved-data-flexibility/328/40 "2024-03-12T11:40:10Z")

</div>

To do 2 sync jobs, you’ll create 2 different sets of configurations, e.g.

```yaml
filename=config-1.yml
kind: source
spec:
  name: aws-1
  path: cloudquery/aws
  tables: [...]
  skip_tables: ["aws_inspector_findings"]
---
kind: destination
...

```

and

```yaml
filename=config-2.yml
kind: source
spec:
  name: aws-2
  path: cloudquery/aws
  tables: ["aws_inspector_findings"]
---
kind: destination
...

```

Then run `cloudquery sync config-1.yml` and `cloudquery sync config-2.yml` separately. You can split to more configurations that way to keep individual jobs from running for a long time, then run each job on a different schedule. You can specify to only sync a single account [here](https://hub.cloudquery.io/plugins/source/cloudquery/aws/latest/docs).

[Previous page](https://community.cloudquery.io/t/cloudquery-tables-lack-foreign-key-relationships-for-improved-data-flexibility/328.md?page=1)

[Next page](https://community.cloudquery.io/t/cloudquery-tables-lack-foreign-key-relationships-for-improved-data-flexibility/328.md?page=3)
