Flex tables backup -export and import

Sreedhar1

I am trying to migrate data in flex tabels from one vertica server to another but its not working.

1) create flex table using DDL from existing table

2)used thsi query to create dump vsql -h '' -U <.> -w <> -At -c "SELECT * FROM  temp_flex " | gzip -c > '/home/test/temp_fl.gz';

3) load usong COPY temp_flex FROM  local '/home/test_1.gz' GZIP 

 

I am able to load the data but the view /keys dont get built and hence the loaded data is useless.

 

Is there any other way to export/import flex tables?

 

DerrickR

Hello Sreedhar1,

 

You can export your data by serializing it into Json and then copying it back in with the fjsonparser.

 

To serialize, try:

 

public.MapToString(__raw__).

 

See the documentation for MapToString. You may need to do some string manipulation to get your output file to be a valid input file for the fjsonparser.

 

I think something like this might work to generate an output file. You'd run this statement with headers and alignment off, exported to a file.

 

select '[' as data

union all

select public.MapToString(__raw__) || ',' as data from mytable

union all

select '{} ]' as data;

Sreedhar1

thanks . Do i create the table as create flex table temp (); and then insert and compute the keys so that the columns get auto created?

DerrickR

The export process would create a json file that you'd load the same as if it came from anywhere else.

 

So I believe the answer you are looking for is "yes".

 

CREATE FLEX TABLE data();

COPY data FROM ... PARSER fjsonparser();

select computer_flextable_keys_and_build_view('data');