Redshift limits say you can run a 15 mb query. I try to pass a 10mb file with aws redshift-data execute-query —-sql file://.\file.txt throws An error occurred (ValidationException) when calling the ExecuteStatement operation: Query string size exceeds 100 kB. Query editor v2 won’t run anything over 300k characters. How do I actually run it?
You need to have a bench / connection tool that supports queries of this length. As you are seeing Redshift Data API does not support this length of statement. Neither does their web based query editor. In general queries of this size of anti-patterns as all the query statement needs to be processed through the query compiler which is very costly.
If you have data literals in your query I highly recommend that you use S3 to move data into Redshift. If you just have a really long query that doesn't fit in the 100KB limit (101KB) then you just need to use a tool that can issue this size of query.
As I said the limitation you are running into are coming from the bench, not the database. Many benches support issuing commands longer than 100KB - SQL Workbench/J, Aginity Pro, DBeaver should all work based on my quick search.
It looks like you may want a way to do this from a command line which can also be done. The Postgres CLI tool (psql) can issue large commands that are stored in a file and direct command output to another file. You can also write some code and use a standard SDK for the connection. The Amazon Redshift JDBC drive - https://docs.aws.amazon.com/redshift/latest/mgmt/jdbc20-download-driver.html - can do this for Python.
Now a quick note on network configuration for doing this. Redshift requires an MTU of 1500 bytes or less (max network packet size). Most network equipment that is in play when connecting to Redshift from outside of AWS will also enforce a max MTU of 1500 so this isn't (often) an issue. However for network efficiency AWS allows higher MTUs on their networks. So if you are connecting to Redshift from an EC2 you will likely need to configure a max MTU of 1500 bytes to run such large statements. See https://docs.aws.amazon.com/AWSEC2/latest/UserGuide/ec2-instance-mtu.html