如何在AWS Athena中实现基于当前MMYY格式日期的动态表查询

Got it, let's solve this problem where you need to dynamically query a table in Athena using the current month and year in MMYY format. Since Athena runs on Presto SQL, we can use built-in functions to generate the table name on the fly.

Here's how to do it step by step:

• First, confirm the MMYY format: Use DATE_FORMAT to get the current month (two digits with leading zero) and year (two digits) combined. Run this test query to verify the output matches your table name pattern:

  SELECT DATE_FORMAT(current_date, '%m%y') AS current_mmyy;
  

  For example, this will return 0822 for August 2022, exactly what you need for the table suffix.

• Build and execute the dynamic query: Use the format function to inject the MMYY value into your table name, then run the generated SQL with EXECUTE:

  EXECUTE format(
    'SELECT tvd_data_hora FROM "mydb"."i_public_%s" limit 10;',
    DATE_FORMAT(current_date, '%m%y')
  );
  

Key Details to Keep in Mind: #

• The %s in the format string acts as a placeholder that gets replaced by the MMYY value from DATE_FORMAT.
• current_date uses Athena's default timezone (UTC by default). If you need to use a specific local timezone, adjust it like this (replace with your target timezone):

  DATE_FORMAT(current_date AT TIME ZONE 'America/Sao_Paulo', '%m%y')
  

• This approach works because Athena supports Presto's EXECUTE statement for running dynamically generated SQL.

内容的提问来源于stack exchange，提问作者Danillo Campelo