Set number of records in in a file while unloading in athena to S3

user_466521 0
Viewed 475

I am using CTAS command and I was wondering if there is a way to set number of records in a file in S3. All I have got now is to set the size in this link:

https://aws.amazon.com/premiumsupport/knowledge-center/set-file-number-size-ctas-athena/

However, I will not know the size beforehand.

I will be using this command

CREATE TABLE "historic_climate_gz_20_files"
WITH (
      external_location = 's3://awsexamplebucket/historic_climate_gz_20_files/',
      format = 'TEXTFILE',
      bucket_count=20,
      bucketed_by = ARRAY['yearmonthday']
       ) as
select * from historic_climate_gz

I dont see any option to set number of records.

How can I do this ?

Thanks in advance

presto amazon-athena bucket hive sql
1 Answers

Nobody knows beforehand how many buckets exactly should be created to get the desired size or the number of rows per file.

This is why (as described in the link you provided) they are measuring total size, divide it on bucket size required to get the number of buckets. And you cannot specify the size, only the number of buckets when you creating the table. After you calculated the number of buckets, create new bucketed table and reload the data from initial table.

So, if you want your files to contain the desired number of rows each instead of being of desired size, you need to calculate the number of buckets using the same approach.

  1. Calculate the total row number in the initial dataset:

select count(*) as cnt from historic_climate_gz

  1. For example your table contains 1000000 (1M) rows. And you want to have buckets with 10K rows. Calculate bucket_count = 1000000/10000 = 100.

Create new bucketed table with 100 buckets and reload the data, each file in it will approximately contain 10K rows (if the bucket key is evenly distributed and have enough cardinaity).

CREATE TABLE "historic_climate_gz_20_files"
WITH (
      external_location = 's3://awsexamplebucket/historic_climate_gz_20_files/',
      format = 'TEXTFILE',
      bucket_count=100,   ---100 buckets
      bucketed_by = ARRAY['yearmonthday']
       ) as
select * from historic_climate_gz

You see, in case of bucketed table you can only control the number_of_buckets and the bucket key. Size or number of rows is an approximation (expectation) and is not accurate. For not evenly distributed bucket key of course you will get different size of buckets. Some of them bigger, some of them smaller. For evenly distributed key you will have approximately the same number of rows.

Which row comes to which bucket is decided by this function:

`hash(bucket_key) MOD number_of_buckets` where hash is integer.

MOD generates integer bucket numbers in a range [0, number_of_buckets-1]. Number of buckets and bucket key is what you can specify before load.

Rows with the same values will be written in the same bucket. If you have skew in bucket key distribution, then you will have buckets with size and number of rows different (skewed accordingly).

user_27003440
Related