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

alalmeida

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

aurorak

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

Ariel_Cary

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

sKwa

Hi!

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

For example:

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

or how?

sKwa

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).

sKwa

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).

alalmeida

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

alalmeida

Hi Ariel_Cary

When i click on this link I get this message:

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

sKwa

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.

sKwa

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}]

PravinG

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.

Ramesh

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

Ramesh

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