The MongoDB ECDA adapter writes batches of data to a MongoDB collection. Currently, the MongoDB ECDA adapter works with BSON only. In order to write to a MongoDB collection, you must first define a server object for a MongoDB server, as well as a foreign stream that defines the specific collection into which you are writing. The minimum credentials required to write to a MongoDB are a fully qualified URL and the name of the COLLECTION. Next, you set up and performing an INSERT into a foreign stream in order to write data to a MongoDB collection.
For more information on MongoDB URLs, see https://docs.mongodb.com/manual/reference/connection-string/.
To write to MongoDB, you need to create a foreign stream in SQL that references a prebuilt server object called MongoDB_SERVER. In the foreign stream's options, you configure how s-Server connects to MongoDB. For more information on creating foreign streams, see the topic CREATE FOREIGN STREAM in the Streaming SQL Reference Guide.
You will also need to specify a formatter for the foreign stream. Specifying "formatter" as a foreign stream option tells s-Server that this foreign stream writes data. See Output Formats for Writing in this guide for more details.
Streams, like most SQL objects (but unlike data wrappers and servers), must be created within a schema. The following code first creates a schema in which to run the rest of the sample code below, then creates a foreign stream named MongoDBWriterSchema.MongoDBWriterStream.
CREATE OR REPLACE SCHEMA MongoDBWriterSchema; CREATE OR REPLACE FOREIGN STREAM MongoDBWriterSchema.MongoDBWriterStream ( "clientOS" VARCHAR(16), "playerVersion" VARCHAR(32), "sessionId" VARCHAR(64), "clientIPAddress" VARCHAR(32), "KPI" INTEGER ) SERVER MONGODB_SERVER OPTIONS ( "FORMATTER" 'BSON', "URL" 'mongodb://user:password@localhost:27017/db', "COLLECTION" 'stream', "DATE_FORMAT" 'mm/dd/yyyy', "TRANSACTION_ROWTIME_LIMIT" '0', "TRANSACTION_ROW_LIMIT" '70000');
To begin writing data to MongoDB, you INSERT into MongoDBWriterSchema.MongoDBWriterStream. When MongoDBWriterSchema.MongoDBWriterStream receives rows, s-Server writes data to the MongoDB you have configured in the foreign stream options.
In most cases, you will want to set up a pump that writes data to MongoDBWriterSchema.MongoDBWriterStream. Pumps are INSERT macros that continually pass data from one point in a streaming pipeline to the other. See the topic CREATE PUMP in the s-Server Streaming SQL Reference Guide for more details.
You do so with code along the following lines:
CREATE OR REPLACE SCHEMA "Pumps"; SET SCHEMA '"Pumps"'; CREATE OR REPLACE PUMP "writerPump" STOPPED AS --We recommend creating pumps as stopped --then using ALTER PUMP "Pumps"."writerPump" START to start it INSERT INTO MongoDBWriterSchema.MongoDBWriterStream SELECT STREAM * FROM "MyStream"; --where "MyStream" is a currently existing stream
To start writing data, use the following code:
ALTER PUMP "Pumps"."writerPump" START;
|URL||Fully qualified URL starting with mongodb:// and including, at minimum, a host name (or IP address or UNIX domain socket). URL can also include a username and password (these are passed to the MongoDB instance) and a port number. Seehttps://docs.mongodb.com/manual/reference/connection-string/ for more information.|
|COLLECTION||MongoDB collection to which data will be written.|
|OPTIONS_QUERY||Optional. Lets you query a table to update adapter options at runtime. You can use this, for example, to set the STARTING_OFFSET option from a table that contains the last offset, as inselect lastOffset as STARTING_OFFSET from TEST.committedOffset.For more details, see the topic, Using Options Query in Integrating Kafka at https://docs.sqlstream.com/integrating-sqlstream/kafka/.|