data type guess in flex tables

Vadim_Levin

We are trying to evaluate Flex tables for using in our Vertica data pipeline for game analytics. We deal with JSON with bunch of keys that have mainly Integer values. When trying to load this JSON in a Flex table and compute keys, I've noticed that "data_type_guess" is resolved as varchar(20). Similarly, values like "2014-03-18 20:42:41.820000" get resolved as varchar, not timestamp.
Why do not at least Integer values get resolved as Integer types? Do you plan to do this in future releases of Flex table fuctionality?

[Deleted User]

Hi Vadim,

So, "correct" is difficult in this case:  Yes, your data might be all integers today; but you could easily insert something that's not an integer tomorrow.  Maybe even as simple as adding a decimal onto a number (JSON doesn't natively distinguish between integer and floating-point types), or growing some value past what will fit into a signed 64-bit integer.

If we leave the type as VARCHAR, then many operators will automatically try to cast to an appropriate data type (int, arbitrary-precision numeric, etc); most SQL queries will continue to work, and some will intelligently use a different type if needed.

But as soon as a type is specified, then SQL semantics constrain us to strictly enforce exactly that type.  By default, this means that, if you add (or already have) a single value in the table that doesn't match the type, all of your queries will start erroring out with a cast failure.  If the types had been less tightly specified, we might have had the leeway to use a bigger intermediate type and give you your result anyway.

That said, there are certainly cases where this approach would be useful.  (Some use cases would rather have the precise definition and risk erroring out, for example.)  You correctly note that this functionality doesn't exist right now.  I can't comment on future plans here; I can say that we know this is currently a limitation.

Adam