fix(alicloud)!: Add `billing_date` and `instance_id` to `alicloud_bss_bill_details` primary key by easayliu · Pull Request #9639 · cloudquery/cloudquery

easayliu · 2023-04-03T10:32:11Z

Summary

add alicloud daily bill table.

Co-authored-by: candiduslynx <candiduslynx@users.noreply.github.com>

…tch.go Co-authored-by: candiduslynx <candiduslynx@users.noreply.github.com>

…tch.go copy from bill_fetch.go. u are right Co-authored-by: candiduslynx <candiduslynx@users.noreply.github.com>

candiduslynx · 2023-04-03T10:34:02Z

@easayliu you would also need to run make gen-docs in the plugin dir & commit the changed table docs, too

github-actions · 2023-04-04T01:21:36Z

This PR has the following changes to source plugin(s) tables:

⚠️ BREAKING CHANGE: Table alicloud_bss_bill_details: primary key constraint added to column billing_date
⚠️ BREAKING CHANGE: Table alicloud_bss_bill_details: primary key constraint added to column instance_id

candiduslynx · 2023-04-04T06:05:32Z

@easayliu thanks once again for the PR!
We also noticed some similar issues with the following tables:

alicloud_bss_bill
alicloud_bss_bill_overview

Would you mind taking look at them, too? (asking as you clearly have much more experience with AliCloud than us)

🤖 I have created a release *beep* *boop* --- ## [2.0.0](plugins-source-alicloud-v1.2.0...plugins-source-alicloud-v2.0.0) (2023-04-04) ### ⚠ BREAKING CHANGES * **alicloud:** Add `billing_date` and `instance_id` to `alicloud_bss_bill_details` primary key ([#9639](#9639)) ### This Release has the Following Changes to Tables - Table `alicloud_bss_bill_details`: primary key constraint added to column `billing_date` (:warning: breaking) - Table `alicloud_bss_bill_details`: primary key constraint added to column `instance_id` (:warning: breaking) - Table `alicloud_ecs_instances`: column added with name `instance_owner_id` and type `Int` ### Bug Fixes * **alicloud:** Add `billing_date` and `instance_id` to `alicloud_bss_bill_details` primary key ([#9639](#9639)) ([c68cf92](c68cf92)) * **deps:** Update golang.org/x/exp digest to 10a5072 ([#9587](#9587)) ([31f913f](31f913f)) * **deps:** Update module github.com/aliyun/alibaba-cloud-sdk-go to v1.62.266 ([#9601](#9601)) ([f7135db](f7135db)) * **deps:** Update module github.com/aliyun/aliyun-oss-go-sdk to v2.2.7 ([#9602](#9602)) ([81f1cb7](81f1cb7)) * **deps:** Update module github.com/cloudquery/plugin-sdk to v1.44.1 ([#9520](#9520)) ([202c31b](202c31b)) * **deps:** Update module github.com/cloudquery/plugin-sdk to v1.44.2 ([#9661](#9661)) ([a27dc84](a27dc84)) * **deps:** Update module github.com/mattn/go-isatty to v0.0.18 ([#9609](#9609)) ([5b2908e](5b2908e)) --- This PR was generated with [Release Please](https://github.com/googleapis/release-please). See [documentation](https://github.com/googleapis/release-please#release-please).

easayliu · 2023-04-06T05:56:33Z

@easayliu thanks once again for the PR! We also noticed some similar issues with the following tables:

alicloud_bss_bill

alicloud_bss_bill_overview

Would you mind taking look at them, too? (asking as you clearly have much more experience with AliCloud than us)

i will check this .

easayliu · 2023-04-06T06:55:02Z

@easayliu thanks once again for the PR! We also noticed some similar issues with the following tables:

alicloud_bss_bill

alicloud_bss_bill_overview

Would you mind taking look at them, too? (asking as you clearly have much more experience with AliCloud than us)

Replay with my accout, i get the same duplicate primary key.
we will purchase multiple resource packs in one order, so we get duplicate primary key at table alicloud_bss_bill_overview.
in table alicloud_bs_bill,this issue will occur when refunding.
This one is good to resolve, add pk item. but the alicloud_bss_bill_overview is hard to say, this one is get the same value in every key.
Do you have any suggestions？
for example

{
  "Message": "Successful!",
  "RequestId": "AFBE3713-8CF3-5001-BD01-CF8372761D0C",
  "Data": {
    "BillingCycle": "2023-03",
    "TotalCount": 3,
    "AccountID": "XX",
    "PageNum": 1,
    "PageSize": 20,
    "Items": {
      "Item": [
        {
          "ProductName": "消息队列 MQ",
          "SubOrderId": "XX",
          "DeductedByCashCoupons": 0,
          "PaymentTime": "2023-03-14 18:20:47",
          "PaymentAmount": 20088.68,
          "DeductedByPrepaidCard": 0,
          "InvoiceDiscount": 3600,
          "UsageEndTime": "",
          "Item": "SubscriptionOrder",
          "SubscriptionType": "Subscription",
          "PretaxGrossAmount": 24000,
          "Currency": "CNY",
          "CommodityCode": "onsbag",
          "UsageStartTime": "",
          "AdjustAmount": 0,
          "Status": "PayFinish",
          "DeductedByCoupons": 311.32,
          "RoundDownDiscount": 0,
          "ProductDetail": "RocketMQ 4.0 标准版专用资源包（容量递减）",
          "ProductCode": "ons",
          "ProductType": "",
          "OutstandingAmount": 0,
          "PipCode": "ons",
          "PretaxAmount": 20088.68,
          "OwnerID": "XX",
          "RecordID": "XX",
          "CashAmount": 20088.68
        },
        {
          "ProductName": "消息队列 MQ",
          "SubOrderId": "XX",
          "DeductedByCashCoupons": 0,
          "PaymentTime": "2023-03-14 18:20:47",
          "PaymentAmount": 20088.68,
          "DeductedByPrepaidCard": 0,
          "InvoiceDiscount": 3600,
          "UsageEndTime": "",
          "Item": "SubscriptionOrder",
          "SubscriptionType": "Subscription",
          "PretaxGrossAmount": 24000,
          "Currency": "CNY",
          "CommodityCode": "onsbag",
          "UsageStartTime": "",
          "AdjustAmount": 0,
          "Status": "PayFinish",
          "DeductedByCoupons": 311.32,
          "RoundDownDiscount": 0,
          "ProductDetail": "RocketMQ 4.0 标准版专用资源包（容量递减）",
          "ProductCode": "ons",
          "ProductType": "",
          "OutstandingAmount": 0,
          "PipCode": "ons",
          "PretaxAmount": 20088.68,
          "OwnerID": "XX",
          "RecordID": "XX",
          "CashAmount": 20088.68
        },
        {
          "ProductName": "消息队列 MQ",
          "SubOrderId": "XX",
          "DeductedByCashCoupons": 0,
          "PaymentTime": "2023-03-14 18:20:47",
          "PaymentAmount": 12580.54,
          "DeductedByPrepaidCard": 0,
          "InvoiceDiscount": 2254.5,
          "UsageEndTime": "",
          "Item": "SubscriptionOrder",
          "SubscriptionType": "Subscription",
          "PretaxGrossAmount": 15030,
          "Currency": "CNY",
          "CommodityCode": "onsbag",
          "UsageStartTime": "",
          "AdjustAmount": 0,
          "Status": "PayFinish",
          "DeductedByCoupons": 194.96,
          "RoundDownDiscount": 0,
          "ProductDetail": "RocketMQ 4.0 标准版专用资源包（容量递减）",
          "ProductCode": "ons",
          "ProductType": "",
          "OutstandingAmount": 0,
          "PipCode": "ons",
          "PretaxAmount": 12580.54,
          "OwnerID": "XX",
          "RecordID": "XX",
          "CashAmount": 12580.54
        }
      ]
    },
    "AccountName": "XX"
  },
  "Code": "Success",
  "Success": true
}

candiduslynx · 2023-04-08T06:44:49Z

@easayliu thanks for looking into the possible PK issues!

Would you mind creating a PR for alicloud_bs_bill with item added to the primary key? (that way we can keep the acknowledgement that it was your valuable input on the issue)

For the alicloud_bss_bill_overview issue:
We now have (billing_cycle,account_id,subscription_type,commodity_code,product_code,product_type,pip_code,record_id) as the PK.
I see that there are owner_id and sub_order_id fields that might also provide additional uniqueness to the bill overview.

Would you mind testing with jq?
Something like:

cat output.json | jq -S -c '.Data.Items.Item[] | { SubscriptionType, CommodityCode, ProductCode, ProductType, PipCode, RecordID, OwnerID, SubOrderId }' | wc -l
cat output.json | jq -S -c '.Data.Items.Item[] | { SubscriptionType, CommodityCode, ProductCode, ProductType, PipCode, RecordID, OwnerID, SubOrderId }' | sort | uniq | wc -l

If the numbers are the same, it means that the uniqueness key is enough.

easayliu · 2023-05-04T01:54:42Z

@easayliu thanks for looking into the possible PK issues!

Would you mind creating a PR for alicloud_bs_bill with item added to the primary key? (that way we can keep the acknowledgement that it was your valuable input on the issue)

For the alicloud_bss_bill_overview issue: We now have (billing_cycle,account_id,subscription_type,commodity_code,product_code,product_type,pip_code,record_id) as the PK. I see that there are owner_id and sub_order_id fields that might also provide additional uniqueness to the bill overview.

Would you mind testing with jq? Something like:
cat output.json | jq -S -c '.Data.Items.Item[] | { SubscriptionType, CommodityCode, ProductCode, ProductType, PipCode, RecordID, OwnerID, SubOrderId }' | wc -l
cat output.json | jq -S -c '.Data.Items.Item[] | { SubscriptionType, CommodityCode, ProductCode, ProductType, PipCode, RecordID, OwnerID, SubOrderId }' | sort | uniq | wc -l
If the numbers are the same, it means that the uniqueness key is enough.

I`m sorry too late to reply this.
In that example, i remove number to protect.
OwnerID and RecordID is same number, It is not be able to be a PK.
Because this is order by same owner in same record.

liuyixun and others added 22 commits March 23, 2023 10:15

fix clickhouse migrate_mode: forced error,"table is already exist".

8e2336d

add alicloud daily bill table.

add alicloud daily bill table.

2cfa74a

fix

1698dff

Merge branch 'cloudquery:main' into main

abc536f

Update plugins/source/alicloud/resources/services/bss/bill_des_fetch.go

ef97fb1

Co-authored-by: candiduslynx <candiduslynx@users.noreply.github.com>

Update plugins/source/alicloud/resources/services/bss/bill_des_fetch.go

65bfbb3

Co-authored-by: candiduslynx <candiduslynx@users.noreply.github.com>

fix: Suggested change

12f4aee

change name

85bb865

rm log

902e085

Update plugins/source/alicloud/resources/services/bss/bill_details_fe…

4e538d8

…tch.go Co-authored-by: candiduslynx <candiduslynx@users.noreply.github.com>

Update plugins/source/alicloud/resources/services/bss/bill_details_fe…

b1e5bff

…tch.go copy from bill_fetch.go. u are right Co-authored-by: candiduslynx <candiduslynx@users.noreply.github.com>

feat(alicloud): add documentation

37f8519

feat(alicloud): add documentation

e4ebac5

Update title and description

e5aa6b3

Update docs

f281a83

Merge branch 'main' into main

bcba212

Merge branch 'main' into main

5153a2c

the bill update for 5th of each month.

2603033

Merge branch 'main' into main

701fe1f

Merge branch 'main' into main

b62190c

Merge branch 'cloudquery:main' into main

ce52a41

fix(alicloud): duplicate primary key error

f14ea5f

easayliu requested review from a team and candiduslynx and removed request for a team April 3, 2023 10:32

cq-bot added the alicloud label Apr 3, 2023

candiduslynx changed the title ~~fix(alicloud): error duplicate primary key.~~ fix(alicloud)!: Fix duplicate primary key in alicloud_bss_bill_details Apr 3, 2023

fix(alicloud)!: Fix duplicate primary key in alicloud_bss_bill_details

ef4a96c

cq-bot added the website label Apr 4, 2023

easayliu closed this Apr 4, 2023

easayliu reopened this Apr 4, 2023

vercel Bot deployed to Preview April 4, 2023 01:24 View deployment

candiduslynx changed the title ~~fix(alicloud)!: Fix duplicate primary key in alicloud_bss_bill_details~~ fix(alicloud)!: Add billing_date and instance_id to alicloud_bss_bill_details primary key Apr 4, 2023

candiduslynx approved these changes Apr 4, 2023

View reviewed changes

candiduslynx added the automerge Automatically merge once required checks pass label Apr 4, 2023

Merge branch 'main' into main

360e993

vercel Bot deployed to Preview April 4, 2023 06:03 View deployment

kodiakhq Bot merged commit c68cf92 into cloudquery:main Apr 4, 2023

cq-bot mentioned this pull request Apr 4, 2023

chore(main): Release plugins-source-alicloud v2.0.0 #9543

Merged

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

fix(alicloud)!: Add `billing_date` and `instance_id` to `alicloud_bss_bill_details` primary key#9639

fix(alicloud)!: Add `billing_date` and `instance_id` to `alicloud_bss_bill_details` primary key#9639
kodiakhq[bot] merged 24 commits into
cloudquery:mainfrom
easayliu:main

easayliu commented Apr 3, 2023

Uh oh!

candiduslynx commented Apr 3, 2023

Uh oh!

github-actions Bot commented Apr 4, 2023

Uh oh!

candiduslynx commented Apr 4, 2023

Uh oh!

easayliu commented Apr 6, 2023

Uh oh!

easayliu commented Apr 6, 2023

Uh oh!

candiduslynx commented Apr 8, 2023

Uh oh!

easayliu commented May 4, 2023

Uh oh!

Reviewers

Assignees

Labels

Projects

Milestone

Development

Uh oh!

5 participants

Conversation

easayliu commented Apr 3, 2023

Summary

Uh oh!

candiduslynx commented Apr 3, 2023

Uh oh!

github-actions Bot commented Apr 4, 2023

This PR has the following changes to source plugin(s) tables:

Uh oh!

candiduslynx commented Apr 4, 2023

Uh oh!

easayliu commented Apr 6, 2023

Uh oh!

easayliu commented Apr 6, 2023

Uh oh!

candiduslynx commented Apr 8, 2023

Uh oh!

easayliu commented May 4, 2023

Uh oh!

Reviewers

Assignees

Labels

Projects

Milestone

Development

Uh oh!

5 participants