Running .sql file in AWS Athena

Question

I have to create a public schema. My sql script is in .sql file, a part of which is shown below for the reference.

DROP MATERIALIZED VIEW IF EXISTS ELIXHAUSER_QUAN CASCADE;
CREATE MATERIALIZED VIEW ELIXHAUSER_QUAN AS
with icd as
(
  select hadm_id, seq_num, icd9_code
  from diagnoses_icd
  where seq_num != 1 -- we do not include the primary icd-9 code
)................................................................

Is there anyway I can run .sql file in AWS Athena instead of running queries one by one.

When I run the following SQL query in AWS Athena, it pops an error.

DROP MATERIALIZED VIEW IF EXISTS ELIXHAUSER_QUAN CASCADE;
CREATE MATERIALIZED VIEW ELIXHAUSER_QUAN AS
with icd as
(
  select hadm_id, seq_num, icd9_code
  from diagnoses_icd
  where seq_num != 1 -- we do not include the primary icd-9 code
)

Your query has the following error(s):

Only one sql statement is allowed. Got: DROP MATERIALIZED VIEW IF EXISTS ELIXHAUSER_QUAN CASCADE; CREATE MATERIALIZED VIEW ELIXHAUSER_QUAN AS with icd as ( select hadm_id, seq_num, icd9_code from diagnoses_icd where seq_num != 1 -- we do not include the primary icd-9 code ) (Service: AmazonAthena; Status Code: 400; Error Code: InvalidRequestException; Request ID: 821def0f-4d38-4e15-b2d7-4d315472461d)

Ilya Kisil Ilya Kisil · Accepted Answer · 2020-02-03T00:07:15

Nope, you can't run .sql scripts directly on AWS Athena. Moreover, you can't execute multiple statements within the same query request so you have to submit them one by one. What you can is to parse your .sql script and split its content by ;. Then submit each of those statements sequentially with, for example, boto3, i.e. Python SDK for AWS. Also note:

AWS Athena natively supports an optional OR REPLACE clause which lets you to update the existing view by replacing it.
CREATE MATERIALIZED VIEW is not supported by AWS Athena. The full list of allowed DDL statements is outlined here.

Running .sql file in AWS Athena

1 Answers