I have to create 20 table in a Athena data base at the same time. Can I do it with a single execution.
example :
CREATE EXTERNAL TABLE IF NOT EXISTS database_1.A
;
CREATE EXTERNAL TABLE IF NOT EXISTS database_1.B
;
CREATE EXTERNAL TABLE IF NOT EXISTS database_1.C
I have used aws cli for such problems. create a list of sqls.
sql_list.txt
CREATE EXTERNAL TABLE IF NOT EXISTS database_1.A;
CREATE EXTERNAL TABLE IF NOT EXISTS database_1.B;
CREATE EXTERNAL TABLE IF NOT EXISTS database_1.C;
----------
exec_sqls.sh
input_file=$1
while IFS= read -r sql
do
echo "$line"
aws athena start-query-execution --query-string "$sql" --result-configuration S3LocationForOutput=s3://<bucket>
done < "$input_file"
-----------
sh -x exec_sqls.sh sql_list.txt
You can submit multiple requests simultaneously to Amazon Athena (eg via different threads in your application), but each Amazon Athena command can only execute a single SQL query/command.
I have a similar solution but using Redshift eternal tables and Dbeaver. Changes made to external tables will be reflected automatically on Athena.
By using Dbeaver I'm able to run several DDLs on a single execution. Minor changes are required: