Flex Table Limits

This article describes the limits that are applied to flex tables. To learn more about flex tables, see Logical Schema Design - Tables and Views.

Table Limits

Flex tables always include a _raw_ column. This column is used to store unstructured or semi-structured data in Vertica map (VMap) data format. The maximum size of the _raw_ column is set to 2,000,000 B. This means that the maximum size of one object (for example, a JSON structure) to load to a flex table is 2,000,000 B.

To load a file bigger than 2 MB, structure your file so that records are split into separate objects (for example, an array of JSONs) and can be loaded into separate rows.

For example, the following JSON structure will be loaded as one object and may easily hit the limit:

{
   "1":{"key1":"value1", ... "keyN":"valueN"},
   ...
   "N":{"key1":"value1", ... "keyN":"valueN"}
}

To load this JSON successfully, rewrite it to contain N simpler JSON structures:

{ "id":"1", "key1":"value1", ... "keyN":"valueN"}
...
{ "id":"N", "key1":"value1", ... "keyN":"valueN"}

Override the Limit

If you know that the incoming JSON structures are going to be bigger than 2 MB or significantly smaller than 2 MB, you can override the default limit of 2,000,000 B for the _raw_ column before creating flex tables. To do so, set the FlexTableRawSize parameter to the required column size between 1 B and 32,000,000 B. The parameter is set for a session.

For example, to set the size to 1 KB, use the following command:

ALTER SESSION SET FlexTableRawSize = 1024;

Flex Function Limit

Some flex functions have a limit on the size of VMap data they are capable to handle or produce. For more information, see Vertica’s online documentation.

For example, the function MAPJSONEXTRACTOR, which takes a JSON structure as input and converts it to VMap, has a limit of 65,000 B for VMap output. This function is designed to be applied to many small JSON structures.

If you receive the following error message, it means that you hit the object size limit:

ERROR 5861: Error calling processBlock() in User Function MapJSONExtractor

This is a known issue by a third-party software provider.