|
Determine HDFS Output Format |
Scroll |
SQData supports a variety of target formats for HDFS output file(s), including JSON, AVRO, XML and delimited. JSON is the preferred format for streaming changed data (CDC), as it is self-described and is not as heavy payload-wise as XML.
With JSON output, you have the option of replicating an entire source record/row, including the before and after image for updates, without any mapping. This is performed via the REPLICATE function. An example of this is provided later in this document.
You also have the option of customizing your output with the source to target mapping and dynamic construction of a JSON (or another format) message using the PRINTF function. Please note that the scope of this document is not intended to discuss custom JSON. Please visit Precisely https://www.precisely.com/support for assistance with this type of processing.
Syntax
DATASTORE hdfs://%(HHOST):%(HPORT)/DUMMY
OF JSON -- specify JSON format
AS TARGET
DESCRIBED BY GROUP DB2_SOURCE -- use source for REPLICATE
STAGING SIZE 4G -- file size
STAGING DELAY 60 -- rotate every 60 minutes
;
Sample JSON output
{“object_name”:SQDATA.EMPLOYEE,
"stck":"00d16918eb93309dc0",
"timestamp":"2018-06-01-13.18.41.780264",
"change_op":"U",
"after_image":
{
"empno":“000010”,
"firstnme":“SANDRA”,
”midinit":“i”,
“lastname”:“HAAS”,
“workdept”:“A00”,
“phoneno":"3978",
"hiredate":"1978-09-15",
"job":"PRES",
"edlevel":14,
"sex":"f",
"birthdate":"1954-03-31",
"salary":59250.00,
"bonus":6600.00,
"comm":2340.00
},
"before_image":
{
"empno":“000010”,
"firstnme":“CHRISTINE”,
”midinit":“I”,
“lastname”:“HAAS”,
“workdept”:“A00”,
“phoneno":"3978",
"hiredate":"1978-09-15",
"job":"PRES",
"edlevel":14,
"sex":"f",
"birthdate":"1954-03-31",
"salary":59250.00,
"bonus":6600.00,
"comm":2340.00
},
}
Note: Both JSON and AVRO formatted records are always in Unicode (UTF-8) with the translation from the source codepage/CCSID (i.e. EBCIDC/1047) performed automatically by the SQData Apply engine.
