Can I export data using Vertica to JSON file/format?

alalmeidaalalmeida Registered User

Can I export data using Vertica to JSON file/format?

Answers

  • aurorakaurorak Employee, Registered User

    You could if you use something like DBeaver. http://dbeaver.jkiss.org/docs/features/. Ctrl-F JSON.

  • Ariel_CaryAriel_Cary Employee, Registered User, VerticaExpert

    A similar question was discussed here.
    https://forum.vertica.com/discussion/comment/239123

  • sKwasKwa Registered User

    Hi!

    Can you describe your requirements(how do you see it)?

    For example:

    $ vsql2json "<some query>" > output.json
    

    or how?

  • sKwasKwa Registered User

    Hi!

    Yes, you can do it easily, but its not a out-of-box functionality.

    Depends on your requirements:

    • You can write a script that reads data from pipe and converts it on the fly.
    • You can write Python/JDBC/ODBC client that exports data to JSON.
    • You can write an UDx on C++/Java/Python if you want it as built-in functionality.

    Don't afraid, all options very easy to implement(I can help, if you don't know how to do it).

  • sKwasKwa Registered User

    Hi!

    Yes, you can do it easily, but its not a out-of-box functionality.

    Depends on your requirements:

    • You can write a script that reads data from pipe and converts it on the fly.
    • You can write Python/JDBC/ODBC client that exports data to JSON.
    • You can write an UDx on C++/Java/Python if you want it as built-in functionality.

    Don't afraid, all options very easy to implement(I can help, if you don't know how to do it).

  • alalmeidaalalmeida Registered User

    I think, for my requirements, Python client will be the best choice. Do you have any example of this?

    I will create a lot of files to use in my chart tool, I start with:

    select * from tabela order by campo1

    And any time who change "campo1" I create a new file campo1.json

  • alalmeidaalalmeida Registered User

    Hi Ariel_Cary

    When i click on this link I get this message:

    Permission Problem
    You don't have permission to do that.

  • sKwasKwa Registered User

    Hi!

    Checkout this gist(its just a concept): https://gist.github.com/sKwa/b7af8753f598c9666ad4cfdc05904346

    Since we cannot enclose fields in quotes so I did a trick:

    • fields delimiter is '\x02'
    • rows separator is '\x01\n'

    example of usage:

    $ vsql -Udbadmin -wletmein -F$'\002' -R$'\001\n' -P footer=off -Ac "select * from T" \
    | python csv2json.py > output.json
    

    example of output:

    [
    {"date": "2000-01-01", "id": "1", "val": "rus"},
    {"date": "2000-01-01", "id": "1", "val": "usa"},
    {"date": "2000-01-02", "id": "1", "val": "usa"},
    {"date": "2000-01-03", "id": "1", "val": "eng"},
    {"date": "2000-01-01", "id": "2", "val": "afg"},
    {"date": "2000-01-02", "id": "2", "val": "eng"},
    {"date": null, "id": "4", "val": null},
    ]
    

    see example in the end of gist.
    This snippet converts empty string to null and probably will fail on binary data.

    PS
    Latter I will post a robust script.

  • sKwasKwa Registered User
    edited July 12

    Hi alalmeida!

    I created a new script. This script is much more better.
    I'm still working on it, but even now you can use it.
    I tested script on Verica 8.0.1 (you didn't post your version).

    Feel free to fork/modify it:
    https://github.com/sKwa/vertica/tree/master/vertex

    Changes:
    * empty string is empty string
    * null is null
    * no more vsql
    * command line arguments
    * pure python

    Limitations:
    * so far it is compatible only with Linux systems(later I will add an Windows support)

    Requirements:
    * python 2.7
    * hp-vertica-client (https://my.vertica.com/docs/8.0.x/HTML/Content/python_client/index.html)

    Supported data types:

    • Boolean
    • Char
    • Date
    • Float
    • Integer
    • Interval Day
    • Interval Day to Hour
    • Interval Day to Minute
    • Interval Day to Second
    • Interval Hour
    • Interval Hour to Minute
    • Interval Hour to Second
    • Interval Minute
    • Interval Minute to Second
    • Interval Month
    • Interval Second
    • Interval Year
    • Interval Year to Month
    • Long Varchar
    • Numeric
    • Time
    • TimeTz
    • Timestamp
    • TimestampTz
    • Varchar

    Help

    $ ./vertex.py --help
    usage: vertex.py [--help] [-d DATABASE] [-U USER] -w PASSWORD [-h HOST]
                     [-p PORT] -c COMMAND [-F {csv,json,xml}] [-o FILENAME] [-V]
    
    vertex.py - convert your SQL table or query to JSON/CSV/XML format.
    
    optional arguments:
      --help                show this help message and exit
      -d DATABASE, --dbname DATABASE
                            the name of your HP Vertica database. (default: None)
      -U USER, --username USER
                            your HP Vertica user name. (default: dbadmin)
      -w PASSWORD           the password for the user's account. (default: None)
      -h HOST, --host HOST  the name of the host. (default: localhost)
      -p PORT, --port PORT  the port number on which HP Vertica listens. (default:
                            5433)
      -c COMMAND, --command COMMAND
                            query to exeport. (default: None)
      -F {csv,json,xml}, --format {csv,json,xml}
                            output format for query. (default: json)
      -o FILENAME, --output FILENAME
                            writes all query output into file filename. (default:
                            None)
      -V, --version         show program's version number and exit
    

    Example

    ./vertex.py -U "${DBUSER}" -w "${DBPWD}" -c "select * from T" 
    [{"date": "2000-01-01", "id": 1, "val": "rus"},
    {"date": "2000-01-01", "id": 1, "val": "usa"},
    {"date": "2000-01-02", "id": 1, "val": "usa"},
    {"date": "2000-01-03", "id": 1, "val": "eng"},
    {"date": "2000-01-01", "id": 2, "val": "afg"},
    {"date": "2000-01-02", "id": 2, "val": "eng"},
    {"date": null, "id": 4, "val": null}]
    
  • PravinGPravinG Registered User

    How to remove last records extra comma?

    Individual records looks good, and if we consider the whole JSON file then after last record it is having an extra comma making the json invalid.

  • RameshRamesh Employee, Registered User

    PravinG - I am sure you can find a way to remove the last comma using sed or awk.

  • RameshRamesh Employee, Registered User

    PravinG - I am sure you can find a way to remove the last comma using sed or awk.

Leave a Comment

BoldItalicStrikethroughOrdered listUnordered list
Emoji
Image
Align leftAlign centerAlign rightToggle HTML viewToggle full pageToggle lights
Drop image/file