We are using insert into $table select ... in AWS Athena to write data to S3. It ended up with 30 small s3 files. We want to write the results into one large S3 file. On presto, we can set task.writer-count to limit # of writers. Is there anyway that we can do this on Athena?
https://prestodb.io/docs/current/admin/properties.html#task-writer-count
You can use bucketed_by and bucket_count. Here's an example from Examples of CTAS Queries - Amazon Athena:
CREATE TABLE ctas_avro_bucketed
WITH (
format = 'AVRO',
external_location = 's3://my_athena_results/ctas_avro_bucketed/',
partitioned_by = ARRAY['nationkey'],
bucketed_by = ARRAY['mktsegment'],
bucket_count = 3)
AS SELECT key1, name1, address1, phone1, acctbal, mktsegment, comment1, nationkey
FROM table1;