Question & Answer
Question
I am getting this table while select using Fluid Query Connector: DB.ADMIN(ADMIN)=> select * from table with final (fqread ('','','select * from tab1')) limit 10; ERROR: Record size 86082 exceeds internal limit of 65535 bytes
Cause
The table 'tab1' you are selecting from Hadoop table using the Fluid Query Connector.
First you should check the table; how many columns with 'STRING' data-type.
By default, using Fluid Query Connector; NPS is casting each of the STRING column into VARCHAR(1000); because we do not know what is the length of each STRING column.
That also means anything more than 65 STRING columns in that table, you will hit this error. Because our internal limit is 65535 bytes.
Answer
To work-around this, consider :
- Changing the "varchar-size" properties in the properties files (normally located in /nz/export/ae/products/fluidquery/*.properties); depending on how many columns; if you set to "varchar-size=500"; then in theory, 130 STRING columns are allowed.
- Changing your Hadoop size table schema, to use varchar; instead of STRING. (better solution) **
- Specific only the columns you need in the query; instead of "select *" which means include all columns.
**Refer to this URL, that suggest the use of VARCHAR over STRING.
https://developer.ibm.com/hadoop/blog/2015/06/01/top-7-big-sql-4-0-performance-tips/
"7. Use appropriate data types for the values being stored
In Big SQL 4.0 the use of STRING data types is strongly discouraged."
If this table is created by Data Movement; it was designed to convert varchar to STRING to support earlier hiver version like Cloudera 4.7 (Hive 0.10). Now that the later version of Hive support Varchar data type, the enhancement might be required to improve performance, and to avoid hitting system limits issue like this.
Internal Enhancement#107075 - For Data Movement to avoid changing varchar data type to STRING.
Was this topic helpful?
Document Information
Modified date:
17 October 2019
UID
swg21992388