3

How to write multiple CREATE TABLE statement to execute multiple SQL statements via ATHENA CLI command - aws athena start-query-execution

I have tried this but just with single SQL statement and it works fine. But I need to run multiple SQL statements

( select count(*) from elb_logs; create external table tbl_nm; ) via CLI Athena command.

aws athena start-query-execution \
   --query-string "select count(*) from elb_logs;" \
   --query-execution-context Database=default \
   --result-configuration OutputLocation=s3://aws-athena-query-results-xxxxxxxxxx-us-east-1/ \
   --region us-east-1 \
   --output text

Should be able to execute multiple SQL statements on AWS Athena via CLI command.

John Rotenstein
  • 241,921
  • 22
  • 380
  • 470
Deep
  • 99
  • 4
  • 5

1 Answers1

0

I think you can't run several queries in one CLI command, i just tried and got this error:

    An error occurred (InvalidRequestException) when calling the StartQueryExecution operation: 
    Only one sql statement is allowed.

Run one by one.

Yossi Vainshtein
  • 3,845
  • 4
  • 23
  • 39
  • 2
    I have read on AWS documentation that it provisions capability of running multiple SQL statements but with a limit of 20 SQL statements which can be run via CLI but I am just stuck with proper syntax of writing multiple statements. aws athena start-query-execution \ --query-string "select count(*) from elb_logs;" \ – Deep Jul 02 '19 at 09:23
  • 1
    @Deep can you add a link to this documentation? – Yossi Vainshtein Jul 02 '19 at 09:26
  • 1
    If you read AWS documentation for https://docs.aws.amazon.com/cli/latest/reference/athena/start-query-execution.html you can find in description it say "Runs the SQL query statements contained in the Query". There is default limit of 20 SQL statements which can be run https://docs.aws.amazon.com/athena/latest/ug/service-limits.html – Deep Jul 02 '19 at 17:07
  • 2
    That limit means that you can run 20 concurrent executions (queries running simultaneously), not that you can pass 20 statements in the same requests. Each StartQueryExecution call can only have one statement. – Theo Jul 04 '19 at 09:43
  • 2
    I got confirmation from AWS support team that we can only submit one query in Athena and there is no way to run multiple DDL statements in parallel. This is not to be confused with the Athena service limits [1] which specify 20 calls for the StartQueryExecution as well as a default of 20 DML and 20 DDL queries can be made at the same time. This just means that 20 concurrent calls that can be made but only one query can be contained in each call. So you may have multiple systems making Athena queries but the number of queries made by all together at the same time is limited to 20. – Deep Jul 06 '19 at 18:37
  • dam @Deep aws disapoint me – Cristián Vargas Acevedo Jun 09 '22 at 17:22