Skip to main content

Flex tables backup -export and import

  • June 3, 2015
  • 3 replies
  • 24 views
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?

 

    3 replies

    DerrickR
    Forum|alt.badge.img
    • DerrickR
    • Participating Frequently
    • June 4, 2015

    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
    • Sreedhar1
    • Author
    • New Participant
    • June 4, 2015

    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
    Forum|alt.badge.img
    • DerrickR
    • Participating Frequently
    • June 4, 2015

    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');

    Still scrolling?
    Ask a question
    Terms & ConditionsAccessibility statement