Wanted to understand on hive.max-partitions-per-writers property

[Akanksha-kedia]

With default value of hive.max-partitions-per-writers=100 and i m trying to insert into a partitioned table with 2 partition s column i have changed the page chunk with positionCount to be 100(In code 4096) i m getting error"Exceeded limit of 100 open writers for partitions/buckets. I see its using MultiChannelGroupByHash and PagePartitioner but whats the use of this hash table and why differnt writerIndexes is getting created as we shpuld close old ones and start a new writers list accordingly.

@mosabua @wendigo any idea abt this.

Also in case pf huge dataset with large unique partitions, to wat extend we can increase the value of parameter hive.max-partitions-per-writers can we set this value based on unique partitions being set?

Do let me know any more information needed.

[Akanksha-kedia]

Want to get clarity of how each page is getting processed as per code each page of postion 4096 is being processed and if that chunk has unique partitions based on that writeraindexes is returned with partitionsPage() api and indexPage() api which in turn GroupByHashPageIndexer class which is using MultiChannelGroupByHash which is having inner class GetNonDictionaryGroupIdsWork for process and other group add. pagePartitioner.getMaxIndex() can have wat max value ?

[Akanksha-kedia]

@martint @electrum @wendigo please help to understand these

[sug-ghosh]

scenario : trino:sg10> use sg11;
->
-> CREATE TABLE employee_data (
-> data VARCHAR,
-> employee_id INT,
-> record_hour TIMESTAMP
-> ) WITH (
-> partitioned_by = ARRAY['employee_id', 'record_hour']
-> );
-> INSERT INTO employee_data
-> SELECT
-> CAST(employee_id AS VARCHAR) AS employee_id,
-> 1 AS some_integer_column,
-> (TIMESTAMP '2022-01-01 00:00:00' + INTERVAL '1' HOUR * numbers) AS record_hour
-> FROM
-> unnest(sequence(1, 10)) AS t(employee_id),
-> unnest(sequence(0, 24*365-1)) AS t2(numbers);
USE
CREATE TABLE
Query 20240430_043424_00013_f8qvk, FAILED, 1 node
Splits: 18 total, 5 done (27.78%)
0.96 [0 rows, 0B] [0 rows/s, 0B/s]
Query 20240430_043424_00013_f8qvk failed: Exceeded limit of 100 open writers for partitions/buckets

Let's consider the example above where we have a table employee_data that's partitioned by employee_id and record_hour. When we run the INSERT query, Trino creates a separate writer for each unique combination of employee_id and record_hour.
In INSERT query, generating a sequence of 10 employee_ids, and a sequence of record_hours that spans a year. This results in 10 * 24 * 365 = 87,600 unique combinations of employee_id and record_hour, and thus Trino will attempt to create 87,600 writers.the process fails with the error: "Exceeded limit of 100 open writers for partitions/buckets". each open writer consumes resources, and having a large number of small files.To prevent these issues, Trino has a limit on the number of open writers, which is set by the hive.max-partitions-per-writers property. If a query would require more writers than this limit, Trino does not execute the query and instead gives the error message "Exceeded limit of 100 open writers for partitions/buckets".