Simple timeseries query with Comstock_oedi schema exceeds Athena string length quota

[lixiliu]

Issue overview

The query statements generated by build stock-query can be quite large that sometimes they can exceed the Athena query string length limit.

Error encountered:
...WHERE CAST(baseline.applicability AS VARCHAR) = 'true' GROUP BY 1, 2 ORDER BY 1, 2' at 'queryString' failed to satisfy constraint: Member must have length less than or equal to 262144

Per https://docs.aws.amazon.com/athena/latest/ug/troubleshooting-athena.html

queryString failed to satisfy constraint: Member must have length less than or equal to 262144

The maximum query string length in Athena (262,144 bytes) is not an adjustable quota. AWS Support can't increase the quota for you, but you can work around the issue by splitting long queries into smaller ones. For more information, see How can I increase the maximum query string length in Athena? in the AWS Knowledge Center.

Current Behavior

Expected Behavior

Steps to Reproduce

1. Run comstock_oedi version of https://github.com/NREL/buildstock-query/blob/370f27ccf6ae35cbd8eea957fcb6332906e416de/example_usage/basic_usage_oedi.ipynb#L8

ComStock datasets = (
    "comstock_2024_amy2018_release_2_metadata_state_vu",
    "comstock_2024_amy2018_release_2_by_state_vu",
    "comstock_2024_amy2018_release_2_metadata_state_vu",
)

Possible Solution

Refactor large queries into subqueries with DDL, intermediate views or tables
https://repost.aws/knowledge-center/athena-query-string-length

Details

Environment

Some additional details about your environment for this issue (if relevant):

• Platform (Operating system, version):
• Version of OpenStudio (if using an intermediate build, include SHA):

Context