Parse Json from Vertica VARCHAR column
Posted: Wed Mar 29, 2017 1:33 pm
Hi,
We have the case that Json is pushed from outer system to our Vertica database into tables with VARCHAR(65000) columns. We do not have any possibility to change this process currently.
So to parse the Json-s we load those to our ETL tool, do the parsing there and then write back to the same database. This is quite time consuming.
We thought that maybe we could utilize the possibility to let Vertica do the parsing. But as we understand this can only be done with Flex tables, we do not have the possibility to change our regular columnar tables to flex tables.
So, now we tried to do this:
a. Create temporary flex table.
b. CONNECT TO VERTICA from and to the same instance.
c. COPY from the VARCHAR(65000) table to the temp flex table.
d. parse the Json and load the results to where needed.
But with this we hit the problem, that the CONNECT TO VERTICA seems to be possible to be done only with the dbadmin user of the connect-to database.
So we have come quite far from the initial problem with additional problems
Any good ideas how to get the Json in VARCHAR(65000) parsed as optimal as possible. Maybe using the ETL tool is still the best option in the end?
Br,
pj
We have the case that Json is pushed from outer system to our Vertica database into tables with VARCHAR(65000) columns. We do not have any possibility to change this process currently.
So to parse the Json-s we load those to our ETL tool, do the parsing there and then write back to the same database. This is quite time consuming.
We thought that maybe we could utilize the possibility to let Vertica do the parsing. But as we understand this can only be done with Flex tables, we do not have the possibility to change our regular columnar tables to flex tables.
So, now we tried to do this:
a. Create temporary flex table.
b. CONNECT TO VERTICA from and to the same instance.
c. COPY from the VARCHAR(65000) table to the temp flex table.
d. parse the Json and load the results to where needed.
But with this we hit the problem, that the CONNECT TO VERTICA seems to be possible to be done only with the dbadmin user of the connect-to database.
So we have come quite far from the initial problem with additional problems
Any good ideas how to get the Json in VARCHAR(65000) parsed as optimal as possible. Maybe using the ETL tool is still the best option in the end?
Br,
pj