Question & Answer
Question
Why does Big SQL fails with SQL0670N 'row size of the resulting table would have exceeded the row size limit' ?
Answer
Big SQL inherits its limit from DB2 and this limit is 1048319 bytes.
The STRING data type is mapped directly to Hive STRING. The native BigSQL data type is VARCHAR(max). This has several implications: If a value in the underlying storage exceeds the maximum VARCHAR length,that value is silently truncated when converted to the Big SQL native data type. If the underlying data does not require the maximum VARCHAR length for storage (for example, if the column never exceeds 100 characters), then Big SQL allocates unnecessary resources for the handling of that column. You should use an explicit VARCHAR instead of a STRING to improve performance. The default behavior for the STRING data type is to map the type to the SQL data type of VARCHAR(32762). As suggested above, the default behavior can lead to performance issues. You can use the configuration property, 'bigsql.string.size' to change the size of the VARCHAR that isused to represent the STRING type. For example, set the following property before you create a Hadoop table that contains a STRING data type:
SET HADOOP PROPERTY 'bigsql.string.size'=4096 and retry the failed statement.
Also, if you are using String in your table DDL, the recommendation is to change references to the STRING data type to explicit VARCHAR(n) types that most appropriately fit the data size. If 4K(4096) results in the data being truncated, the size can be increased again to another value. The goal being to find a size that is as small as possible to avoid the 32K+ default and not truncate the data.
Related Information
Was this topic helpful?
Document Information
Modified date:
18 July 2020
UID
swg21994153