How can I explicitly specify the size of the files to be split or the number of files?
Situation: If only specify the partition clause, it will be divided into multiple files. The size of one file is less than 1MB (~ 40 files).
What I am thinking of: I want to explicitly specify the size of the files to be split or the number of files when registering data with CTAS or INSERT INTO.
I have read this article: https://aws.amazon.com/premiumsupport/knowledge-center/set-file-number-size-ctas-athena/
Problem: Using bucketing method (like said in above article ) can help me specify the number of file or file size. However, it also said that "Note: The INSERT INTO statement isn't supported on bucketed tables". I would like to register data daily with Athena's INSERT INTO.
Question: what is the best way to build a partitioned data mart without compromising search efficiency? Is it best to register the data with Glue and save it as one file?
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.
S3 sync limitations on index size?asked 3 months ago
EMR Spark output file consolidation in S3Accepted Answerasked 4 years ago
How does sagmaker batch inference processes individual files?asked 2 months ago
Redshift UNLOAD parquet file sizeAccepted AnswerMODERATORasked 2 years ago
How to control how result of Glue job is split into files?Accepted Answerasked 4 years ago
Glue slow on JSON to Parquetasked 3 months ago
Does the file gateway cache need to be at least as large as the largest file being uploaded?Accepted Answerasked 2 years ago
How can I explicitly specify the size of the files to be split or the number of files?Accepted Answerasked a month ago
AWS glue combining multiple input into a single output csvasked 3 months ago
Is it optimal to keep one lengthy Glue job script, or split it into sub-modules/multiple files?Accepted Answerasked 4 years ago