Redshift Data API query statement size limited to 100 KB

Question

Issue: Our Redshift Java SDK queries return ValidationException: software.amazon.awssdk.services.redshiftdata.model.ValidationException: Cannot process query string larger than 100kB

Details:

• Our system may generate many SQL SELECTs connected together via SQL UNIONs with sorting rules on UNIONs level - that's reason why we have reached 100kB query size limit.
• We understand that having SQL query over 100 KB smells by poor queries design and we are going to reduce those queries size.
• We need to understand better this Redshift infrastructure limit before applying changes and constraints in business logic.
• I have not met any similar error while executing the same SQL query via standard SQL client connected to Redshift engine.
• We prefer keep using SDK (instead of JDBC, CLI) due to various reasons.

Question: Is query size 100 KB soft limit constraint configurable within our cluster/SDK or is it hard limit set on Amazon side?

Answer 1

Finally there is constraint regarding Redshift Data API - The maximum query statement size is 100 KB.

Source: https://docs.aws.amazon.com/redshift/latest/mgmt/data-api.html#data-api-calling-considerations

Answer 2

Redshift's query size limit is 16MB - https://docs.aws.amazon.com/redshift/latest/dg/c_redshift-sql.html

I think you need to focus on the SDK.