Yes. You are right that INSERT INTO is not yet supported for bucketed tables. For your use case where you wanted to specify the number of buckets/file sizes, using Athena bucketing would be appropriate but, with the downfall of not being able to use INSERT INTO to insert new incoming data.
But, I can recommend of using S3distcp utility on AWS EMR to merge small files into ~128MB size to solve your small file problem. You can use it to combine smaller files into larger objects. You can also use S3DistCP to move large amounts of data in an optimized fashion from HDFS to Amazon S3, Amazon S3 to Amazon S3, and Amazon S3 to HDFS.
How to control how result of Glue job is split into files?Accepted Answerasked 4 years ago
Redshift UNLOAD parquet file sizeAccepted Answerasked 3 years ago
Does the file gateway cache need to be at least as large as the largest file being uploaded?Accepted Answerasked 3 years ago
How can I explicitly specify the size of the files to be split or the number of files?Accepted Answerasked 5 months ago
Storage Gateway for files, SMB Share Size vs Size on Diskasked 3 years ago
Is it optimal to keep one lengthy Glue job script, or split it into sub-modules/multiple files?Accepted Answerasked 4 years ago
How to view the files in Glacier vault using CLIasked a month ago
How do we export activity of multiple files in one go?asked 2 months ago
How can we set s3 lifecycle rule to delete the files form S3 bucket!asked 5 months ago
Cannot increase the number of open files limitasked a year ago