Dump data of table to files

kfruchtmankfruchtman Vertica Customer ✭
in General Discussion

Hi Experts!
Is there a way to dump the data of the projections into a file?
I cannot use the EXPORT-IMPORT option for many reasons so I was wondering if I can dump the data to a file and import it on another vertica db.
Thanks in advance for all your help
Keren

Answers

  • moshegmosheg Vertica Employee Administrator

    You can use the EXPORT TO PARQUET statement to export a table (or part of one) as Parquet data.
    You can export data to HDFS, AWS S3, Google Cloud Storage (GCS), or the local file system.
    See: https://www.vertica.com/docs/10.1.x/HTML/Content/Authoring/DataExport/Parquet/ExportingDataParquet.htm

  • kfruchtmankfruchtman Vertica Customer ✭

    Thanks for your help,
    I was actually not looking for an external table.I needed to dump a txt file of the table data.Well I did it with this command:
    vsql -U dbadmin -F $'\t' -At -o dumpfile_operators.txt -c "SELECT * FROM operators;"
    I have a 80GB file of txt now.. not sure I can load this huge file to my table in my new db.
    Do you know if that possible? Does Vertica know how to handle a huge file like this?

    Thanks again
    Keren

  • moshegmosheg Vertica Employee Administrator
    edited March 2021

    Vertica can handle if there are enough available resources.
    For load efficiency use the Linux SPLIT command to generate a set of 8 files, each at the size of ~10GB
    Try something like: split -b 10G dumpfile_operators.txt
    Then use one COPY command with '*' to load all those files into one table.

  • kfruchtmankfruchtman Vertica Customer ✭

    great thanks! I am working on it and I will update how it went.

Leave a Comment

BoldItalicStrikethroughOrdered listUnordered list
Format
SpoilerCodeQuote
Heading 2Heading 1
Emoji
:):D:(;):/:o:s:p:'(:|B):#:*<3o:)>:)
Url
Image
Align leftAlign centerAlign rightToggle HTML viewToggle full pageToggle lights
Drop image/file
Home • General DiscussionComment As ...