Parser Example: JSON Parser
The JSON Parser consumes a stream of JSON objects. Each object must be well formed and on a single line in the input. Use line breaks to delimit the objects. The parser uses the field names as keys in a map, which become column names in the table. You can find the code for this example in /opt/vertica/packages/flextable/examples. This directory also contains an example data file.
This example uses the setRowFromMap()
method to write data.
Loading and Using the Example
- Load the library and define the JSON parser, using the third-party library (gson-2.2.4.jar). See the comments in JsonParser.java for a download URL.
=> CREATE LIBRARY json -> AS '/opt/vertica/packages/flextable/examples/java/output/json.jar' -> DEPENDS '/opt/vertica/bin/gson-2.2.4.jar' language 'java'; CREATE LIBRARY => CREATE PARSER JsonParser AS LANGUAGE 'java' -> NAME 'com.vertica.flex.JsonParserFactory' LIBRARY json; CREATE PARSER FUNCTION
- Define a table, and then use the JSON parser to load data into that table.
=> CREATE TABLE mountains(name varchar(64), type varchar(32), height integer); CREATE TABLE => COPY mountains FROM '/opt/vertica/packages/flextable/examples/mountains.json' -> WITH PARSER JsonParser(); -[ RECORD 1 ]-- Rows Loaded | 2 => SELECT * from mountains; -[ RECORD 1 ]-------- name | Everest type | mountain height | 29029 -[ RECORD 2 ]-------- name | Mt St Helens type | volcano height |
The data file contains a value (hike_safety) that was not loaded because the table definition did not include that column. The data file follows:
{ "name": "Everest", "type":"mountain", "height": 29029, "hike_safety": 34.1 } { "name": "Mt St Helens", "type": "volcano", "hike_safety": 15.4 }
Implementation
The following code shows the process()
method from JsonParser.java. The parser attempts to read the input into a Map.
If the read is successful, the JSON Parser calls setRowFromMap()
:
@Override public StreamState process(ServerInterface srvInterface, DataBuffer input, InputState inputState) throws UdfException, DestroyInvocation { clearReject(); StreamWriter output = getStreamWriter(); while (input.offset < input.buf.length) { ByteBuffer lineBytes = consumeNextLine(input, inputState); if (lineBytes == null) { return StreamState.INPUT_NEEDED; } String lineString = StringUtils.newString(lineBytes); try { Map<String,Object> map = gson.fromJson(lineString, parseType); if (map == null) { continue; } output.setRowFromMap(map); // No overrides needed, so just call next() here. output.next(); } catch (Exception ex) { setReject(lineString, ex); return StreamState.REJECT; } }
The factory, JsonParserFactory.java, instantiates and returns a parser in the prepare()
method. No additional setup is required.