Job fails when trying to insert large number of rows to table with large number of columns. “A commit request is outside of a reservation size. A row is too big.”

[nagendra22]

I am quite new to Azure Data Lake and U-SQL and have worked with SQL earlier. I am facing the following issue while trying to migrate a SQL application to cloud.

I have ADLA table with 73 columns. I need to populate it with data in 3 columns and null in other columns. The data comes after processing 2 other tables. From error message, I understood that one particular row size is too big and checked the data by outputting in CSV. Each row is 3 columns (long?, int and int?) and nulls in 70 other columns. The data is inside the long and int range so there is no data type/size issue.

Observations:
• When I tried to insert large number of rows in a table with large number of column, It failed. (approx. 200,000 rows and table with 73 columns) error message: “A commit request is outside of a reservation size. A row is too big.”(figure 1)
• When I tried to insert the same data in a separate table with only 3 columns I am interested in, it passes.
• When I tried to insert the same rows in batches (I tried sorting data and inserting half in one ADLA job and other half in another ADLA job), it succeeded. (I think that there is no data size restriction in ADLA table but there is some restriction while processing it)(figure 2)
• When I tried to output the processed rows directly to CSV in stead of inserting into table, it succeeds without any restriction. I can output all the data at once. (I think there is no problem with data types or fetching/joining logic)(figure 3)
• I think there is limitation in processing large amount of data if it is to be inserted into ADLA Table with large number of columns and this limitation does not exist while creating files instead of inserting to tables.

Is there something I have missed taking into consideration? How can I resolve the issue?

I recently found in forums that size of string is 128KB and read here that each row can be upto 4 MB.
I have table with 48 string columns which makes it 6144 KB = 6MB. Is this the problem I am facing? If so, is there any plans to increase data size per row? Is it generally a bad practice to have such large columns?

[nagendra22]

Found out from usql team that it is a known bug when there are many null values being inserted into table. The workarounds are

• separate the sparsely populated fields(having most nulls) into a separate table
• reserve some non null values to represent null values.
  (Fixed this issue temporarily by replacing null strings with blank strings.)
  Hoping the bug will be fixed soon.

[MKadaner]

Please note that for the first bullet, splitting columns between the "main" table and the "secondary" sparse table only makes sense if you can split so that the secondary table has much less rows, assuming that the rows with all null values are not stored at all. After such split you can LEFT OUTER JOIN the tables on a unique key (which you may need to introduce).

[nagendra22]

Thanks Michael for the immediate resolution we can apply. Please keep us posted when the bug gets fixed if possible :)

[bnepal]

Hi Michael

Has this bug been fixed, as I am getting similar error, I have around 300+ columns and have replaced all NULL values with empty string too but still the same issue, the scenario is I have 10 files and every time I run it fails on different file after few files has been finished processing successfully. So it doesn't always fail on the same file.

E_RUNTIME_USER_RESERVATION_TOO_BIG: Commit request exceeds reservation size of 29360128 bytes. A row is too big.

Component
RUNTIME
Message
Commit request exceeds reservation size of 29360128 bytes. A row is too big.

Any input would be appreciated.

Regards
Binod

[MikeRys]

Let me check what the status is. From: bnepal <[email protected]> Sent: Tuesday, December 4, 2018 4:30 PM To: Azure/usql <[email protected]> Cc: Subscribed <[email protected]> Subject: Re: [Azure/usql] Job fails when trying to insert large number of rows to table with large number of columns. “A commit request is outside of a reservation size. A row is too big.” (#123) Hi Michael Has this bug been fixed, as I am getting similar error, I have around 300+ columns and have replaced all NULL values with empty string too but still the same issue, the scenario is I have 10 files and every time I run it fails on different file after few files has been finished processing successfully. So it doesn't always fail on the same file. E_RUNTIME_USER_RESERVATION_TOO_BIG: Commit request exceeds reservation size of 29360128 bytes. A row is too big. Component RUNTIME Message Commit request exceeds reservation size of 29360128 bytes. A row is too big. Any input would be appreciated. Regards Binod — You are receiving this because you are subscribed to this thread. Reply to this email directly, view it on GitHub<https://na01.safelinks.protection.outlook.com/?url=https%3A%2F%2Fgithub.com%2FAzure%2Fusql%2Fissues%2F123%23issuecomment-444311876&data=02%7C01%7Cmrys%40microsoft.com%7Cab7fb7f678614ab41c4d08d65a48c103%7C72f988bf86f141af91ab2d7cd011db47%7C1%7C0%7C636795665861500675&sdata=6CpJJdiGsjFIGzpdGxkQw2DroMzpzqIMuQULN5VjXSo%3D&reserved=0>, or mute the thread<https://na01.safelinks.protection.outlook.com/?url=https%3A%2F%2Fgithub.com%2Fnotifications%2Funsubscribe-auth%2FALNzJBermICRIufkT2t7PNrGxoJCpoa_ks5u1xN3gaJpZM4SZohg&data=02%7C01%7Cmrys%40microsoft.com%7Cab7fb7f678614ab41c4d08d65a48c103%7C72f988bf86f141af91ab2d7cd011db47%7C1%7C0%7C636795665861500675&sdata=7zCIDC2X%2BhDNE1g%2BKzpDkzzhA%2B2vkfbel86NpYjVcY4%3D&reserved=0>.

[bnepal]

Thanks Michael, it would be nice to know as I am struggling to get our files processed.

[MikeRys]

Sorry for the delay. We have a work item filed to fix it and it is assigned to an engineer. But it is still open. We are working now on getting an ETA. Our CSS team is now engaged, so I suggest to use that channel for updates. Thanks again for reaching out!