當我使用 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 爬蟲程式，確定該程式並非處於「RUNNING」或「FAILED」狀態。

使用分區投影自動填入分區

您可以使用資料分割投影建立「成本和用量報告」資料表。在分區投影中，分區值和位置是動態計算的，而非實際存放在 AWS Glue Data Catalog 中。因此，您無需手動新增分區即可查看最新資料。您可以在報告檔案傳送到 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.    在查詢編輯器索引標籤上，輸入類似於以下內容的命令：

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。

資料表建立好之後隨即可查詢。如需詳細資訊，請參閱使用 Amazon Athena 查詢成本和用量報告。

---