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

3 分钟阅读
0

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

解决方法

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

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

如果您手动创建了成本和使用情况报告表,请在 Amazon Athena 控制台中运行 MSCK 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 Data Catalog 中。您无需手动添加分区即可查看最新数据。在报告文件传输到您的 Amazon S3 存储桶后,您可以立即对其进行查询。

注意:创建成本和使用情况报告时,请务必将为以下项启用报告数据集成选为 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. 查询编辑器选项卡上,输入类似于以下内容的命令:

    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. 选择另存为

  4. 选择运行查询,创建表 test_cur_partitionprojection

创建表后,您可以立即查询该表。有关更多信息,请参阅 Querying Cost and Usage Reports using Amazon Athena

AWS 官方
AWS 官方已更新 1 年前