Formatting Data as BSON

<< Click to Display Table of Contents >>

Navigation:  Integrating SQLstream Blaze with Other Systems > Writing Data Out of s-Server > Writing to Other Destinations > Output Formats for Writing >

Formatting Data as BSON

Previous pageReturn to chapter overviewNext page

5_2_indicator Version 5.2 Feature

The ECDA adapter writes batches of data to BSON tuples. BSON, or Binary Javascript Object Notation extends the JSON model to provide additional data types and ordered fields. It is used primarily for MongoDB. See Writing to MongoDB fpr more details. To configure how the adapter writes tuples to BSON, you use foreign stream options. These options are listed below.

Here is an example of the SQL used to define a foreign stream for the BSON ECDA adapter. This code uses the file system for output. To write data over other input/output systems, such as IBM MQ or AMQP, you would need to specify options for these formats. See Writing to Other Destinations for more details.

CREATE OR REPLACE SERVER "FileWriterServer" TYPE 'FILE'

FOREIGN DATA WRAPPER ECDA;

 

CREATE OR REPLACE FOREIGN STREAM "BSON_OutputStream"

  ("id" DOUBLE,

  "reported_at" VARCHAR(4096),

  "shift_no" DOUBLE,

  "trip_no" DOUBLE,

  "route_variant_id" VARCHAR(4096),

  "waypoint_id" DOUBLE,

  "last_known_location_state" VARCHAR(4096)

   )

   SERVER "FileWriterServer"

    --note that this uses the server defined above

   OPTIONS

   (

    --file directory where BSON file will be written.

    directory '/tmp/BSON_test/'

    formatter 'BSON',

    character_encoding 'UTF-8',

    filename_prefix 'test',

    filename_suffix '.BSON',

    formatter_include_rowtime 'false',

    MAX_TIME_DELTA_PER_FILE '60000'

    );

 

To actually write to a file in /tmp/BSON_test/, you need to write a pump containing an INSERT statement along the following lines:

CREATE OR REPLACE PUMP "writerPump" STARTED AS

INSERT INTO "BSON_OutputStream" (

  "id",

  "reported_at",

  "shift_no",

  "trip_no",

  "route_variant_id",

  "waypoint_id",

  "last_known_location_state"

)

   SELECT STREAM

  "id",

  "reported_at",

  "shift_no",

  "trip_no",

  "route_variant_id",

  "waypoint_id",

  "last_known_location_state"

   from "buses_stream";

    --this assumes that a stream called "buses_stream" exists in the same schema

 

Output

 

[{"id":"5.0115809712E10",

"reported_at":"2014-07-23 20:52:04.527000000",

"shift_no":"NULL",

"trip_no":"653.0",

"route_variant_id":"L38 7",

"waypoint_id":"NULL",

"last_known_location_state":"NULL"},

{"id":"5.0115854098E10",

"reported_at":"2014-07-23 20:52:05.443000000",

"shift_no":"NULL",

"trip_no":"NULL",

"route_variant_id":"310 7",

"waypoint_id":"NULL",

"last_known_location_state":"NULL"},

{"id":"3.46866848031E11",

"reported_at":"2014-07-23 20:52:07.713000000",

"shift_no":"1016.0",

"trip_no":"NULL",

"route_variant_id":"806 160",

"waypoint_id":"1.5588646E7",

"last_known_location_state":"NULL"}]

 

Sample Properties File to Use ECD Agent to Write BSON Files

The following code uses the file system for output. To write data over other input/output systems, such as IBM MQ or AMQP, you would need to specify options for these formats. See Writing to Other Destinations for more details.

#Column types for the source stream

ROWTYPE=RECORDTYPE(VARCHAR(2040) id, VARCHAR(2040) reported_at, VARCHAR(2040) shift_no, VARCHAR(2040) trip_no, VARCHAR(2040) route_variant_id)

DIRECTORY=/tmp/BSON_test/

FORMATTER=BSON

FILENAME_DATE_FORMAT=YYYY-MM-DD-HH:MM:SS

FILENAME_PREFIX=TEST

FILENAME_SUFFIX=.BSON

CHARACTER_ENCODING=US-ASCII

FORMATTER_INCLUDE_ROWTIME=FALSE

MAX_TIME_DELTA_PER_FILE=60000

 

See the topic Writing to Files with the ECD Adapter and Agent for a general overview of how to write to files with the ECD Adapter or Agent.