当我使用 Amazon Athena 查询成本和使用情况报告时,为什么无法查看最新的账单数据?

上次更新时间:2021 年 4 月 20 日

在使用 Amazon Athena 查询成本和使用情况报告时,我无法查看最新的账单数据。

解决方法

导致此问题的最常见原因是成本和使用情况报告表中缺少分区。要解决此问题,请选择以下一种或多种解决方案。

确保为手动创建的成本和使用情况报告表加载表分区

如果成本和使用情况报告表是手动创建的,则应通过在 Amazon Athena 控制台中运行 MCSK REPAIR TABLE 命令来加载表分区您也可以从 Amazon Athena 控制台上传分区

如果您已设置 AWS Glue 爬网程序来定期向表中添加分区,则请确保将该爬网程序安排在生成报告之前运行。否则,您可能无法在查询结果中看到最新的账单数据。

请务必检查 Amazon Simple Storage Service (Amazon S3)和 AWS Glue 爬网程序

您可以使用 AWS CloudFormation 模板将成本和使用情况报告与 Athena 集成。此模板包括 AWS Glue 爬网程序、AWS Glue 数据库和 AWS Lambda 事件。当新的成本和使用情况报告文件提交到 Amazon S3 路径时,Lambda 函数将启动 AWS Glue 爬网程序。然后,爬网程序会根据成本和使用情况报告文件中的数据自动将分区添加到表中。如果在查询表时无法查看最新的账单数据,请检查您的 S3 路径。确保成本和使用情况报告文件已提交。此外,检查您的 AWS Glue 爬网程序,确保它未处于运行或失败状态。

使用分区投影自动填充分区

您可以使用分区投影创建成本和使用情况报告表格。在分区投影中,分区值和位置是动态计算的,而不是实际存储在 AWS Glue 数据目录中。因此,您无需手动添加分区即可查看最新数据。报告文件提交到 Amazon S3 存储桶后,您可以立即查询。

要使用分区投影创建成本和使用情况报告表,请执行以下操作:

注意:请确保在创建成本和使用情况报告选择了使用 Athena 启用报告数据集成。通过选择使用 Athena 与报告集成,报告文件将以分区的 Parquet 格式提交给目标账户。报告文件提交到目标文件最多可能需要 24 小时。文件路径类似于以下格式:

s3://example-report-prefix/example-report-name/example-report-name/year=2021/month=1
s3://example-report-prefix/example-report-name/example-report-name/year=2021/month=2

1.    打开 Amazon Athena 控制台

2.    在 Query editor(查询编辑器)选项卡上,输入类似以下内容的命令:

CREATE EXTERNAL TABLE test_cur_partitionprojection(
  `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,
  `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_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_blended_rate` string,
  `line_item_blended_cost` double,
  `line_item_line_item_description` string,
  `line_item_tax_type` string,
  `line_item_legal_entity` string,
  `product_product_name` string,
  `product_alarm_type` string,
  `product_availability` string,
  `product_bundle` string,
  `product_bundle_description` string,
  `product_bundle_group` string,
  `product_capacitystatus` string,
  `product_clock_speed` string,
  `product_component` string,
  `product_compute_type` string,
  `product_content_type` string,
  `product_cputype` string,
  `product_current_generation` string,
  `product_database_engine` string,
  `product_dedicated_ebs_throughput` string,
  `product_deployment_option` string,
  `product_description` string,
  `product_durability` string,
  `product_ecu` string,
  `product_edition` string,
  `product_engine_code` string,
  `product_enhanced_networking_supported` string,
  `product_event_type` string,
  `product_free_query_types` string,
  `product_free_trial` string,
  `product_from_location` string,
  `product_from_location_type` string,
  `product_gpu` string,
  `product_gpu_memory` string,
  `product_group` string,
  `product_group_description` string,
  `product_instance_family` string,
  `product_instance_name` string,
  `product_instance_type` string,
  `product_instance_type_family` string,
  `product_intel_avx2_available` string,
  `product_intel_avx_available` string,
  `product_intel_turbo_available` string,
  `product_license` string,
  `product_license_model` string,
  `product_location` string,
  `product_location_type` string,
  `product_logs_destination` string,
  `product_max_iops_burst_performance` string,
  `product_max_iopsvolume` string,
  `product_max_throughputvolume` string,
  `product_max_volume_size` string,
  `product_maximum_extended_storage` string,
  `product_maximum_storage_volume` string,
  `product_memory` string,
  `product_memorytype` string,
  `product_message_delivery_frequency` string,
  `product_message_delivery_order` string,
  `product_min_volume_size` string,
  `product_minimum_storage_volume` string,
  `product_network_performance` string,
  `product_normalization_size_factor` string,
  `product_operating_system` string,
  `product_operation` string,
  `product_origin` string,
  `product_physical_cpu` string,
  `product_physical_gpu` string,
  `product_physical_processor` string,
  `product_pre_installed_sw` string,
  `product_processor_architecture` string,
  `product_processor_features` string,
  `product_product_family` string,
  `product_queue_type` string,
  `product_recipient` string,
  `product_region` 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_sku` string,
  `product_software_included` string,
  `product_software_type` string,
  `product_standard_storage_retention_included` string,
  `product_storage` string,
  `product_storage_class` string,
  `product_storage_media` string,
  `product_storage_type` string,
  `product_subscription_type` string,
  `product_tenancy` string,
  `product_to_location` string,
  `product_to_location_type` string,
  `product_transfer_type` string,
  `product_usagetype` string,
  `product_uservolume` string,
  `product_vcpu` string,
  `product_version` string,
  `product_volume_api_name` string,
  `product_volume_type` string,
  `pricing_rate_id` string,
  `pricing_currency` 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_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_start_time` string,
  `reservation_subscription_id` 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,
  `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)
PARTITIONED BY (
  `year` int,
  `month` int)
ROW FORMAT SERDE
  'org.apache.hadoop.hive.ql.io.parquet.serde.ParquetHiveSerDe'
STORED AS INPUTFORMAT
  'org.apache.hadoop.mapred.TextInputFormat'
OUTPUTFORMAT
  'org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat'
LOCATION
  's3://example-report-prefix/example-report-name/example-report-name'
TBLPROPERTIES (
  'projection.enabled'='true',
  'projection.month.range'='1,12',
  'projection.month.type'='integer',
  'projection.year.range'='1900,2050',
  'projection.year.type'='integer',
  'storage.location.template'='s3://example-report-prefix/example-report-name/example-report-name/year=${year}/month=${month}')

3.    选择 Save as(另存为)保存查询。

4.    选择 Run query(运行查询)以创建表 test_cur_partitionprojection

您可以在创建表后立即查询表。有关更多信息,请参阅使用 Amazon Athena 查询成本和使用情况报告


这篇文章对您有帮助吗?


您是否需要账单或技术支持?