High angle view of Beijing Guomao.
Serge Bonte and Jim Knicely authored this post.
Vertica provides a built-in file parser named FJSONPARSER that parses and loads a JSON file. This file can contain either repeated JSON data objects (including nested maps) or an outer list of JSON elements.
For a flex table, the parser stores the JSON data in a single-value VMap.
For a hybrid or columnar table, the parser loads data directly in any table column with a column name that matches a key in the JSON source data.
Load the JSON Data into Vertica
Let’s look at an example JSON file:
dbadmin=> \! cat /home/dbadmin/some_data.json
{"age": 5, "name": "Tim"}
{"age": 7, "name": "Joe", "zip": 92022}
{"age": 8}
{"name" : "Jim"}
First, you need to create a Vertica table to hold the contents of the JSON data:
dbadmin=> CREATE TABLE some_data1(age INT, name VARCHAR);
CREATE TABLE
Copy the data from the JSON file into the new table:
dbadmin=> COPY some_data1 FROM '/home/dbadmin/some_data.json' PARSER fjsonparser();
Rows Loaded
-------------
4
(1 row)
dbadmin=> SELECT * FROM some_data1;
age | name
—–+——
5 | Tim
8 |
| Jim
7 | Joe
(4 rows)
Skip the JSON Records if Field not Specified
To skip loading records when the value for a field is not specified, add a NOT NULL constraint to the corresponding table column. Vertica does not load that record into the table.
Again, create a Vertica table. This time specify the NOT NULL constraint to the age column:
dbadmin=> CREATE TABLE some_data2(age INT NOT NULL, name VARCHAR);
CREATE TABLE
Copy the JSON data into this table. Note that Vertica does not load the record that does not contain an age value the some_data2.json.
dbadmin=> COPY some_data2 FROM '/home/dbadmin/some_data.json' PARSER fjsonparser();
Rows Loaded
-------------
3
(1 row)
dbadmin=> SELECT * FROM some_data2;
age | name
—–+——
5 | Tim
8 |
7 | Joe
(3 rows)
For More Information
In the Vertica documentation, see:
•
Load JSON Data
•
FJSONPARSER