メンバーズ組織CURとAthenaでアカウント横断的に月間AWS料金を集計する

CREATE DATABASE `members-cur`;

CREATE EXTERNAL TABLE `cur` (
  `identity_line_item_id` string, 
  `identity_time_interval` string, 
  `bill_invoice_id` string, 
  `bill_billing_entity` string, 
  `bill_bill_type` string, 
  `bill_payer_account_id` string, 
  `bill_billing_period_start_date` timestamp, 
  `bill_billing_period_end_date` timestamp, 
  `bill_invoicing_entity` string, 
  `line_item_usage_account_id` string, 
  `line_item_line_item_type` string, 
  `line_item_usage_start_date` timestamp, 
  `line_item_usage_end_date` timestamp, 
  `line_item_product_code` string, 
  `line_item_usage_type` string, 
  `line_item_operation` string, 
  `line_item_availability_zone` string, 
  `line_item_resource_id` string, 
  `line_item_usage_amount` double, 
  `line_item_normalization_factor` double, 
  `line_item_normalized_usage_amount` double, 
  `line_item_currency_code` string, 
  `line_item_unblended_rate` string, 
  `line_item_unblended_cost` double, 
  `line_item_line_item_description` string, 
  `line_item_tax_type` string, 
  `line_item_legal_entity` string, 
  `product_product_name` string, 
  `product_accelerator_size` string, 
  `product_accelerator_type` string, 
  `product_access_type` string, 
  `product_activity_type` string, 
  `product_addon_feature` string, 
  `product_alarm_type` string, 
  `product_api_type` string, 
  `product_attachment_type` string, 
  `product_availability` string, 
  `product_availability_zone` string, 
  `product_bit_rate` string, 
  `product_broker_engine` string, 
  `product_bundle` string, 
  `product_cache_engine` string, 
  `product_cache_memory_size_gb` string, 
  `product_calling_type` string, 
  `product_capacitystatus` string, 
  `product_category` string, 
  `product_client_location` string, 
  `product_clock_speed` string, 
  `product_cloud_search_version` string, 
  `product_codec` string, 
  `product_compute_family` string, 
  `product_compute_type` string, 
  `product_concurrencyscalingfreeusage` string, 
  `product_content_type` string, 
  `product_country` string, 
  `product_counts_against_quota` string, 
  `product_cputype` string, 
  `product_current_generation` string, 
  `product_data` string, 
  `product_data_transfer` string, 
  `product_data_transfer_quota` string, 
  `product_database_edition` string, 
  `product_database_engine` string, 
  `product_datatransferout` string, 
  `product_dedicated_ebs_throughput` string, 
  `product_deployment_location` string, 
  `product_deployment_option` string, 
  `product_describes` string, 
  `product_description` string, 
  `product_device` string, 
  `product_device_type` string, 
  `product_direct_connect_location` string, 
  `product_directory_size` string, 
  `product_directory_type` string, 
  `product_directory_type_description` string, 
  `product_dominantnondominant` string, 
  `product_durability` string, 
  `product_ebs_optimized` string, 
  `product_ecu` string, 
  `product_edition` string, 
  `product_elastic_graphics_type` string, 
  `product_endpoint` string, 
  `product_endpoint_type` string, 
  `product_engine` string, 
  `product_engine_code` string, 
  `product_enhanced_networking_support` string, 
  `product_enhanced_networking_supported` string, 
  `product_entity_type` string, 
  `product_event_type` string, 
  `product_execution_frequency` string, 
  `product_execution_location` string, 
  `product_fee_code` string, 
  `product_fee_description` string, 
  `product_file_system_type` string, 
  `product_frame_rate` string, 
  `product_free_overage` string, 
  `product_free_query_types` string, 
  `product_free_tier` string, 
  `product_free_trial` string, 
  `product_free_usage_included` string, 
  `product_frequency_mode` string, 
  `product_from_location` string, 
  `product_from_location_type` string, 
  `product_georegioncode` string, 
  `product_gets` string, 
  `product_gpu` string, 
  `product_gpu_memory` string, 
  `product_graphqloperation` string, 
  `product_group` string, 
  `product_group_description` string, 
  `product_high_availability` string, 
  `product_indexing_source` string, 
  `product_ingest_type` string, 
  `product_input` string, 
  `product_input_mode` string, 
  `product_instance` string, 
  `product_instance_capacity10xlarge` string, 
  `product_instance_capacity12xlarge` string, 
  `product_instance_capacity24xlarge` string, 
  `product_instance_capacity2xlarge` string, 
  `product_instance_capacity4xlarge` string, 
  `product_instance_capacity8xlarge` string, 
  `product_instance_capacity_large` string, 
  `product_instance_capacity_xlarge` string, 
  `product_instance_family` string, 
  `product_instance_function` string, 
  `product_instance_type` string, 
  `product_instance_type_family` string, 
  `product_instances` string, 
  `product_instancesku` string, 
  `product_intel_avx2_available` string, 
  `product_intel_avx_available` string, 
  `product_intel_turbo_available` string, 
  `product_io` string, 
  `product_license` string, 
  `product_license_model` string, 
  `product_license_type` string, 
  `product_line_type` string, 
  `product_location` string, 
  `product_location_type` string, 
  `product_logs_source` string, 
  `product_logs_type` string, 
  `product_machine_learning_process` string, 
  `product_mailbox_storage` string, 
  `product_max_iops_burst_performance` string, 
  `product_max_iopsvolume` string, 
  `product_max_throughputvolume` string, 
  `product_max_volume_size` string, 
  `product_maximum_capacity` string, 
  `product_maximum_extended_storage` string, 
  `product_maximum_storage_volume` string, 
  `product_memory` string, 
  `product_memory_gib` string, 
  `product_memorytype` string, 
  `product_message_delivery_frequency` string, 
  `product_message_delivery_order` string, 
  `product_metering_type` string, 
  `product_min_volume_size` string, 
  `product_minimum_storage_volume` string, 
  `product_network_performance` string, 
  `product_newcode` string, 
  `product_normalization_size_factor` string, 
  `product_offer` string, 
  `product_operating_system` string, 
  `product_operation` string, 
  `product_operation_type` string, 
  `product_ops_items` string, 
  `product_origin` string, 
  `product_os_license_model` string, 
  `product_output` string, 
  `product_output_mode` string, 
  `product_overage_type` string, 
  `product_parameter_type` string, 
  `product_physical_cores` string, 
  `product_physical_cpu` string, 
  `product_physical_gpu` string, 
  `product_physical_processor` string, 
  `product_pipeline` string, 
  `product_port_speed` string, 
  `product_pre_installed_sw` string, 
  `product_processor_architecture` string, 
  `product_processor_features` string, 
  `product_product_family` string, 
  `product_protocol` string, 
  `product_provisioned` string, 
  `product_queue_type` string, 
  `product_readtype` string, 
  `product_realtimeoperation` string, 
  `product_recipient` string, 
  `product_region` string, 
  `product_request_description` string, 
  `product_request_type` string, 
  `product_resolution` string, 
  `product_resource_endpoint` string, 
  `product_resource_type` string, 
  `product_rootvolume` string, 
  `product_routing_target` string, 
  `product_routing_type` string, 
  `product_running_mode` string, 
  `product_servicecode` string, 
  `product_servicename` string, 
  `product_single_or_dual_pass` string, 
  `product_sku` string, 
  `product_software_included` string, 
  `product_software_type` string, 
  `product_standard_storage_retention_included` string, 
  `product_steps` string, 
  `product_storage` string, 
  `product_storage_class` string, 
  `product_storage_description` string, 
  `product_storage_media` string, 
  `product_storage_type` string, 
  `product_subscription_type` string, 
  `product_supported_modes` string, 
  `product_tenancy` string, 
  `product_tenancy_support` string, 
  `product_throughput` string, 
  `product_throughput_class` string, 
  `product_tier` string, 
  `product_tiertype` string, 
  `product_to_location` string, 
  `product_to_location_type` string, 
  `product_traffic_direction` string, 
  `product_transcoding_result` string, 
  `product_transfer_type` string, 
  `product_type` string, 
  `product_updates` string, 
  `product_usage_family` string, 
  `product_usagetype` string, 
  `product_uservolume` string, 
  `product_vcpu` string, 
  `product_version` string, 
  `product_video_codec` string, 
  `product_video_frame_rate` string, 
  `product_video_memory_gib` string, 
  `product_video_quality` string, 
  `product_video_quality_setting` string, 
  `product_video_resolution` string, 
  `product_virtual_interface_type` string, 
  `product_volume_api_name` string, 
  `product_volume_type` string, 
  `product_vq_setting` string, 
  `pricing_lease_contract_length` string, 
  `pricing_offering_class` string, 
  `pricing_purchase_option` string, 
  `pricing_public_on_demand_cost` double, 
  `pricing_public_on_demand_rate` string, 
  `pricing_term` string, 
  `pricing_unit` string, 
  `reservation_amortized_upfront_cost_for_usage` double, 
  `reservation_amortized_upfront_fee_for_billing_period` double, 
  `reservation_availability_zone` string, 
  `reservation_effective_cost` double, 
  `reservation_end_time` string, 
  `reservation_modification_status` string, 
  `reservation_normalized_units_per_reservation` string, 
  `reservation_number_of_reservations` string, 
  `reservation_recurring_fee_for_usage` double, 
  `reservation_reservation_a_r_n` string, 
  `reservation_start_time` string, 
  `reservation_total_reserved_normalized_units` string, 
  `reservation_total_reserved_units` string, 
  `reservation_units_per_reservation` string, 
  `reservation_unused_amortized_upfront_fee_for_billing_period` double, 
  `reservation_unused_normalized_unit_quantity` double, 
  `reservation_unused_quantity` double, 
  `reservation_unused_recurring_fee` double, 
  `reservation_upfront_value` double, 
  `resource_tags_user_cm_billing_group` string, 
  `savings_plan_total_commitment_to_date` double, 
  `savings_plan_savings_plan_a_r_n` string, 
  `savings_plan_savings_plan_rate` double, 
  `savings_plan_used_commitment` double, 
  `savings_plan_savings_plan_effective_cost` double, 
  `savings_plan_amortized_upfront_commitment_for_billing_period` double, 
  `savings_plan_recurring_commitment_for_billing_period` double, 
  `savings_plan_region` string, 
  `savings_plan_payment_option` string, 
  `savings_plan_end_time` string, 
  `savings_plan_instance_type_family` string, 
  `savings_plan_purchase_term` string, 
  `savings_plan_offering_type` string, 
  `savings_plan_start_time` string
)
PARTITIONED BY (
  `year` string, 
  `month` string
)
ROW FORMAT SERDE 
  'org.apache.hadoop.hive.ql.io.parquet.serde.ParquetHiveSerDe' 
STORED AS INPUTFORMAT 
  'org.apache.hadoop.hive.ql.io.parquet.MapredParquetInputFormat' 
OUTPUTFORMAT 
  'org.apache.hadoop.hive.ql.io.parquet.MapredParquetOutputFormat'
LOCATION
  's3://${BUCKET_NAME}/cur/'
TBLPROPERTIES (
  'partition_filtering.enabled'='true',
  'projection.enabled'='true',
  'projection.year.type'='date',
  'projection.year.range'='2025,NOW+9HOURS',
  'projection.year.format'='yyyy',
  'projection.year.interval'='1',
  'projection.year.interval.unit'='YEARS',
  'projection.month.type'='integer',
  'projection.month.range'='1,12',
  'projection.month.format'='%d',
  'storage.location.template'='s3://${BUCKET_NAME}/cur/classmethod/org-mcur-parquet/org-mcur-parquet/year=${year}/month=${month}'
);

SELECT 
    line_item_usage_account_id AS aws_account_id,
    product_product_name AS product_name,
    line_item_usage_type AS usage_type,
    line_item_line_item_description AS item_description,
    SUM(line_item_usage_amount) AS usage_quantity,
    SUM(line_item_unblended_cost) AS cost

FROM "members-cur"."cur"
WHERE
    year = '2025'
    AND month = '10'
    AND line_item_line_item_type != 'Fee'
    AND line_item_line_item_type NOT IN ('RIFee', 'SavingsPlanRecurringFee')
    AND bill_billing_entity != 'AWS Marketplace'
    AND NOT (
        bill_billing_entity = 'CM' 
        AND product_product_name = 'Classmethod Members Discount'
    )
GROUP BY 
    line_item_usage_account_id,
    product_product_name,
    line_item_usage_type,
    line_item_line_item_description
ORDER BY
    cost DESC;