Update flex table

Naveen_Kumar

Is it possible to update a flex table, or alter a column value with such conditions as
"update flex_table set purchase_flag = case when ("ti" like '%Order%Confirmation%') then 1 else 0 end";??

Daniel_Leybovic

Hi!


read a docs and learn tool that you use (or pay me and I will do it for you).

https://my.vertica.com/docs/7.0.x/HTML/index.htm#Authoring/FlexTables/QueryingFlexTables.htm%3FTocPa...

Unsupported DDL and DML Commands for Flex Tables

You cannot use the following DDL commands with flex table and doing so results in an error:

CREATE TABLE flex_table AS...CREATE TABLE flex_table LIKE... SELECT INTO UPDATE MERGE INSERT INTO

[Deleted User]

Daniel's observation, if a bit curt, is correct -- per our docs, Flex Tables don't currently support UPDATE.

(If we can make our docs clearer or more useful, suggestions are always welcome.)

You can add columns:  'ALTER TABLE flex_table ADD COLUMN purchase_flag BOOL DEFAULT case when ("ti" like '%Order%Confirmation%') then 1 else 0 end;'.

You can also materialize your flex data into a regular table (via INSERT .. SELECT); then you can update the regular table.

Fully-general UPDATE support for Flex Tables turns out to be a little funny.  (What does it mean to update a column that may or may not exist on every row?)  We would like to better understand what would be useful to people.  If you have specific ideas or use cases that you would like, feel free to post them here, or (better) add a post to the Ideas section of the site.

Daniel_Leybovic

Hi!

I will speak for myself.
>> What does it mean to update a column that may or may not exist on every row?
UPDATE: fail or noop (better to add some parameter/hint/setting => abort_on_fail = True).
MERGE: ON EXISTS(match) UPDATE and INSERT ON NON EXISTS.
You can take a practice of document oriented DBMS like MongoDB or Redis:
http://docs.mongodb.org/manual/reference/method/db.collection.update/#db.collection.update


>> We would like to better understand what would be useful to people.
Depends. I would like to understand what is a main goal of FlexTables - is it ETL or is it complex analytics on un/semistructured data without ETL?
* If is ETL probably I will not ask for UPDATE (obviously?)
* If it is an analytics - so I will ask for UPDATE.

[feedback]
I tested a Flex and so far see an advantage in complex ETL, where multitasking/threading required (while Vertica can do it "natively") or ETL development is very complex(due complex algorithms and/or its not easy to parallelize).

But I also see a big limitation in FlexTables: no public API for parsers and limited parsers. Where is a XML parser for example? YAML? Parser for native Vertica format? 
Does it on roadmap - public API? I think it must be in public API.
IMO: you can't create parser that "fit all sizes".

[Deleted User]

Thanks for the feedback; certainly appreciated.

Our main goal with FlexTables is, I think, to build something useful :-)  What is it most useful for?  We have various goals; but at the end of the day, it's a silly goal if no one will use it that way.  So we're talking with and asking for feedback from users about what they like to do and what they most wish they could do.  "Do you want to do analytics?  Do you want to do ETL(/ELT)?"  We can't implement every request, of course; but we're certainly taking them into account.

For parsers:  I can't commit to anything here; but I can assure you that we know that's a big limitation in the initial release, and that many people have asked for it.  A workaround for now (admittedly not an optimal workaround, but it works and can be done entirely in Vertica) is to use our UDFilter API and write a COPY UDFilter that reads in XML or YAML or whatever other format and writes out JSON.  We can then load the JSON.