Limit on number of keys for Flex table input
We are dealing with JSON files with a very large number of keys. Is the maximum limit on number of keys in input JSON the same as the limit on number of columns in a table, e.g. about 1600? I was hoping it could be higher for Flex Tables.
0
Comments
There is no hard limit on the number of columns in a Flex table. The only limit is the size of the Flex field -- you can only store so much total data per row. (The documentation discusses how to adjust this limit; note that bigger records require more system resources to process.)
Adam
Thanks for the reply. But once I execute command build_flextable_view('my_table'), I am getting this warning:
WARNING 5917: Ignored some keys since the total key count exceeds the view column limit
That is correct.
You don't need the view; just query from the table directly.
That said, while Flex tables do not have a limit on the number of columns (and we have users who rely on them for that reason), views in Vertica (including views generated for Flex, which are just regular views) do have the 1600-column limit.
Many existing applications can work with the tables directly, either automatically or with a little setup work. Our recommendation is to use the Flex table directly when possible, rather than necessarily creating and relying on the view. Views are a backwards-compatibility mechanism for applications that have trouble working with flex tables directly; they emulate a traditional table, and as such are constrained by various things such as the 1600-column limit.
If you really can't use the flex table directly, first, I would be curious to hear more about your use case; also, an alternative, if your table (for example) contains records from many applications, would be to create a view per application with just the columns that pertain to that application.
Some applications have trouble with straight-up flex tables, but work better with hybrid flex tables. (They introspect using conventional mechanisms, in which case they won't see flex column information but will see the materialized columns.) I don't know your use case, but this can be useful in some scenarios.
Adam
I guess for our users it would be more comfortable to work with "conventionally looking" views or tables.
So, in order to create multiple views based on a single flex table, I would need to create multiple custom key tables and populate them by using "create .. as select.." or "insert...select" from main "_keys" table, then create each view by passing each custom table in "select build_flextable_view.." statement? Is that it?
Sorry for the delay -- I'm not personally a Flex expert; hopefully someone who knows more than I can comment on the details here.
If you're doing something fancy with multiple views, you could try to use the Flex view-machinery. It also might make more sense for you to just create views manually.
To continue the example of multiple applications loading into the same table, you might get a new application and more-or-less know the schema for that application (because it tends to have relatively consistent fields). When you observe this, you could add a view for this application. Flex doesn't currently automate this process, but it probably wouldn't be difficult to script; and it would potentially be a reasonable way to use Flex to store flexible data while providing various more-traditional rigid views into that data.
Again, though, I'm guessing at your exact use case. (Also throwing ideas out there, should anyone else read this.) Once the data is in a Flex table, there's a lot you can do with it; I guess I'd just put some thought into what process would be most comfortable for your users.
Adam
1. Create your own keys table with the same columns as in the default {MyFlexTable}_keys table, for example via CREATE TABLE . . . AS SELECT from an existing {MyFlexTable}_keys table.
2. Modify the keys in this table to taste.
3. Run build_flextable_view() ala:
SELECT build_flextable_view('{MyFlexTable}', '{NameOfDesiredTargetView}', '{NameOfYourKeysTableFrom#1}');
4. Repeat for however many VIEWs you wish to create.
Sorry for the slow reply, I hope that helps!