レガシー CUR から CUR 2.0 利用への移行を支援する query-converter と migrate-cur1 を試してみた

~ $ pip3 install -U git+https://github.com/aws-samples/aws-data-exports-setup
Defaulting to user installation because normal site-packages is not writeable
Collecting git+https://github.com/aws-samples/aws-data-exports-setup

〜 中略 〜

Successfully built convertur
Installing collected packages: python-dateutil, wcwidth, tzdata, prompt-toolkit, numpy, questionary, pyarrow, pandas, convertur
Successfully installed convertur-0.1.3 numpy-2.0.2 pandas-2.3.1 prompt-toolkit-3.0.51 pyarrow-21.0.0 python-dateutil-2.9.0.post0 questionary-2.1.0 tzdata-2025.2 wcwidth-0.2.13
~ $

~ $ migrate-cur1
Step 1/5: choosing legacy CUR report
? select cur1

Step 2/5: Pulling the latest parquet file

Step 3/5: Generating SQL
SELECT 
 identity_line_item_id
, identity_time_interval
, bill_invoice_id

〜 中略 〜

, bill_payer_account_name
, line_item_usage_account_name
FROM COST_AND_USAGE_REPORT

Step 4/5: Updating bucket policy
Bucket Policy is already set

Step 5/5: Creating CUR 2.0
CUR 2.0 created

Done. CUR will be populated in 24 hours.

~ $ query-converter
Enter CUR1 SQL query or GitHub URL then Press Escape + Enter (r=retry, q=quit):   
 >

Enter CUR1 SQL query or GitHub URL then Press Escape + Enter (r=retry, q=quit):   
 >SELECT
   bill_payer_account_id,
   product_product_name, 
   line_item_usage_type, 
   line_item_line_item_description, 
   resource_tags_user_cost_center, 
   ROUND(SUM(line_item_unblended_cost),2) AS cost 
FROM 
  ${table_name}
WHERE 
  length(resource_tags_user_cost_center) >0
GROUP BY 
  resource_tags_user_cost_center, 
  bill_payer_account_id, 
  product_product_name, 
  line_item_usage_type, 
  line_item_line_item_description
ORDER BY 
  cost DESC
  LIMIT 20

Processing..

Bedrock assistant:

SELECT
  bill_payer_account_id,
  product['product_name'],
  line_item_usage_type,
  line_item_line_item_description,
  resource_tags['user_cost_center'],
  ROUND(SUM(line_item_unblended_cost),2) AS cost
FROM
  ${table_name}
WHERE
  length(resource_tags['user_cost_center']) >0
GROUP BY
  resource_tags['user_cost_center'],
  bill_payer_account_id,
  product['product_name'],
  line_item_usage_type,
  line_item_line_item_description                
ORDER BY
  cost DESC
  LIMIT 20 

Explanation: The main differences in the transformed query are:
1. The resource_tags fields have been changed from the format resource_tags_XXX to resource_tags['XXX']. Specifically, resource_tags_user_cost_center became resource_tags['user_cost_center']. 
2. The product_product_name field has been changed to product['product_name'] to match the new table format.
3. These changes were applied in the SELECT, WHERE, and GROUP BY clauses.
4. No changes were made to the table name, date filter, or overall query structure as per the instructions.
5. The year and month fields were not present in this query, so no changes were required for those.

SELECT 
  bill_payer_account_id, 
  bill_payer_account_name, 
  product['product_name'], 
  line_item_usage_type, 
  line_item_line_item_description, 
  resource_tags['cost_center'], 
  ROUND(SUM(line_item_unblended_cost), 2) AS cost 
FROM
  ${table_name}
WHERE 
  resource_tags['cost_center'] IS NOT NULL
GROUP BY 
  bill_payer_account_id, 
  bill_payer_account_name, 
  product['product_name'], 
  line_item_usage_type, 
  line_item_line_item_description, 
  resource_tags['cost_center']
ORDER BY 
  cost DESC
  LIMIT 20;

Enter CUR1 SQL query or GitHub URL then Press Escape + Enter (r=retry, q=quit):   
 >SELECT
  bill_payer_account_id,
  line_item_usage_account_id,
  SPLIT_PART(line_item_resource_id, ':', 6) AS split_line_item_resource_id,
  product_region,
  pricing_unit,
  sum_line_item_usage_amount,
  CAST(cost_per_resource AS DECIMAL(16, 8)) AS sum_line_item_unblended_cost
FROM
  (
    SELECT
      line_item_resource_id,
      product_region,
      pricing_unit,
      line_item_usage_account_id,
      bill_payer_account_id,
      SUM(line_item_usage_amount) AS sum_line_item_usage_amount,
      SUM(SUM(line_item_unblended_cost)) OVER (PARTITION BY line_item_resource_id) AS cost_per_resource,
      SUM(SUM(line_item_usage_amount)) OVER (PARTITION BY line_item_resource_id, pricing_unit) AS usage_per_resource_and_pricing_unit,
      COUNT(pricing_unit) OVER (PARTITION BY line_item_resource_id) AS pricing_unit_per_resource
    FROM
      ${table_name}
    WHERE
      line_item_product_code = 'AmazonEC2'
      AND line_item_usage_type LIKE '%Nat%'
      -- get previous month
      AND month = CAST(month(current_timestamp + -1 * INTERVAL '1' MONTH) AS VARCHAR)
      -- get year for previous month
      AND year = CAST(year(current_timestamp + -1 * INTERVAL '1' MONTH) AS VARCHAR)
      AND line_item_line_item_type = 'Usage'
    GROUP BY
      line_item_resource_id,
      product_region,
      pricing_unit,
      line_item_usage_account_id,
      bill_payer_account_id
    )
WHERE
  -- filter only resources which ran more than half month (336 hrs)
  usage_per_resource_and_pricing_unit > 336
  AND pricing_unit_per_resource = 1
ORDER BY
  cost_per_resource DESC;

Processing..

Bedrock assistant:

Here's the transformed query for Table2:

SELECT
  bill_payer_account_id,
  line_item_usage_account_id,
  SPLIT_PART(line_item_resource_id, ':', 6) AS split_line_item_resource_id,
  product['region'],
  pricing_unit,
  sum_line_item_usage_amount,
  CAST(cost_per_resource AS DECIMAL(16, 8)) AS sum_line_item_unblended_cost  
FROM
  (
    SELECT
      line_item_resource_id,
      product['region'],
      pricing_unit,
      line_item_usage_account_id,
      bill_payer_account_id,
      SUM(line_item_usage_amount) AS sum_line_item_usage_amount,
      SUM(SUM(line_item_unblended_cost)) OVER (PARTITION BY line_item_resource_id) AS cost_per_resource,
      SUM(SUM(line_item_usage_amount)) OVER (PARTITION BY line_item_resource_id, pricing_unit) AS usage_per_resource_and_pricing_unit,
      COUNT(pricing_unit) OVER (PARTITION BY line_item_resource_id) AS pricing_unit_per_resource
    FROM
      ${table_name}
    WHERE
      line_item_product_code = 'AmazonEC2'
      AND line_item_usage_type LIKE '%Nat%'
      -- get previous month
      AND split_part("billing_period", '-', 2) = CAST(month(current_timestamp + -1 * INTERVAL '1' MONTH) AS VARCHAR)
      -- get year for previous month
      AND split_part("billing_period", '-', 1) = CAST(year(current_timestamp + -1 * INTERVAL '1' MONTH) AS VARCHAR)
      AND line_item_line_item_type = 'Usage'
    GROUP BY
      line_item_resource_id,
      product['region'],
      pricing_unit,
      line_item_usage_account_id,
      bill_payer_account_id
    )
WHERE
  -- filter only resources which ran more than half month (336 hrs)
  usage_per_resource_and_pricing_unit > 336
  AND pricing_unit_per_resource = 1
ORDER BY
  cost_per_resource DESC;

Explanation:
1. Changed `product_region` to `product['region']` in both the outer SELECT and inner subquery.
2. Replaced `year` and `month` conditions in the WHERE clause with `split_part("billing_period", '-', 1)` and `split_part("billing_period", '-', 2)` respectively.
3. All other fields remained the same as they were already in the correct format for Table2.
4. The overall structure and logic of the query remained unchanged.

SELECT
  bill_payer_account_id,
  line_item_usage_account_id,
  SPLIT_PART(line_item_resource_id, ':', 6) AS split_line_item_resource_id,
  product_region_code,
  pricing_unit,
  sum_line_item_usage_amount,
  CAST(cost_per_resource AS DECIMAL(16, 8)) AS sum_line_item_unblended_cost
FROM
  (
    SELECT
      line_item_resource_id,
      product_region_code,
      pricing_unit,
      line_item_usage_account_id,
      bill_payer_account_id,
      SUM(line_item_usage_amount) AS sum_line_item_usage_amount,
      SUM(SUM(line_item_unblended_cost)) OVER (PARTITION BY line_item_resource_id) AS cost_per_resource,
      SUM(SUM(line_item_usage_amount)) OVER (PARTITION BY line_item_resource_id, pricing_unit) AS usage_per_resource_and_pricing_unit,
      COUNT(pricing_unit) OVER (PARTITION BY line_item_resource_id) AS pricing_unit_per_resource
    FROM
      ${table_name}
    WHERE
      line_item_product_code = 'AmazonEC2'
      AND line_item_usage_type LIKE '%Nat%'
      -- get previous month
      AND SPLIT(billing_period, '-') [ 2 ] = CAST(month(current_timestamp + -1 * INTERVAL '1' MONTH) AS VARCHAR)
      -- get year for previous month
      AND SPLIT(billing_period, '-') [ 1 ] = CAST(year(current_timestamp + -1 * INTERVAL '1' MONTH) AS VARCHAR)
      AND line_item_line_item_type = 'Usage'
    GROUP BY
      line_item_resource_id,
      product_region_code,
      pricing_unit,
      line_item_usage_account_id,
      bill_payer_account_id
    )
WHERE
  -- filter only resources which ran more than half month (336 hrs)
  usage_per_resource_and_pricing_unit > 336
  AND pricing_unit_per_resource = 1
ORDER BY
  cost_per_resource DESC;