Why can't I view my latest billing data when I query my Cost and Usage Reports that uses Amazon Athena?

5 minute read
0

I can't view my latest billing data when I query my Cost and Usage Reports that uses Amazon Athena.

Resolution

The most common reason for this issue is missing partitions in the Cost and Usage Reports table. To resolve this issue, choose one or more of the following solutions.

Load table partitions for manually created Cost and Usage Reports table

If you manually created the Cost and Usage Reports table, then run the MSCK REPAIR TABLE command in the Amazon Athena console. This command loads table partitions. Or, upload the partitions from the Amazon Athena console.

If you set up an AWS Glue crawler to periodically add partitions to your table, confirm that the crawler is scheduled to run. Otherwise, when you generate the report, you might not get the latest billing data.

Check the Amazon Simple Storage Service (Amazon S3) path and AWS Glue crawler

You can use an AWS CloudFormation template to integrate your Cost and Usage Reports with Athena. This template includes an AWS Glue crawler, an AWS Glue database, and an AWS Lambda event. The Lambda function initiates an AWS Glue crawler when new Cost and Usage Reports files are delivered to the Amazon S3 path. Then, the crawler automatically adds partitions to the table based on the data in the Cost and Usage Reports files.

If you can't view the latest billing data when you query the table, then check your S3 path. Make sure that the Cost and Usage Reports files are delivered. Also, check your AWS Glue crawler to be sure that it's not in RUNNING or FAILED status.

Use partition projection to populate the partitions automatically

You can create the Cost and Usage Reports table that uses partition projection. In partition projection, partition values and locations are calculated dynamically instead of being physically stored in the AWS Glue Data Catalog. You don't need to manually add the partitions to view the latest data. You can query the report files immediately after they are delivered to your Amazon S3 bucket.

Note: Be sure that you select Athena for Enable report data integration for when you create the Cost and Usage Reports. Then, the report files are delivered to the target account in partitioned parquet format. It might take up to 24 hours for the report files to be delivered to the target files. The file paths look similar to the following:

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

To create a Cost and Usage Reports table that uses partition projection, do the following:

  1. Open the Amazon Athena console.

  2. On the Query editor tab, enter a command similar to the following:

    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. Choose Save as.

  4. Choose Run query to create the table test_cur_partitionprojection.

You can query the table immediately after the table is created. For more information, see Querying Cost and Usage Reports using Amazon Athena.

AWS OFFICIAL
AWS OFFICIALUpdated 3 months ago