Support Questions
Find answers, ask questions, and share your expertise
Announcements
Alert: Welcome to the Unified Cloudera Community. Former HCC members be sure to read and learn how to activate your account here.

Put MongoDB Data into SQL Server

Highlighted

Put MongoDB Data into SQL Server

Expert Contributor

Hello All,

I am completely new to nifi. But as i found it is very interesting to learn.

I am trying to migrate the MongoDB Data into sql server using NIFI.

Is it possible.

If yes, please suggest me what are the steps that i need to follow.

Thanks,

Mohan V

5 REPLIES 5

Re: Put MongoDB Data into SQL Server

Super Guru

@Mohan V

Yes, it's possible.

Use GetMongo processor to read the data from the mongo collection and Use PutDatabaseRecord processor to store the data into SQL server.

In PutDatabaseRecord processor Configure the RecordReader controller service to read the incoming Mongo data then the processor will store the data into SQL server.

Refer to this and this links for usage/Configuring of PutDatabaseRecord processor.

Flow:

1.GetMongo 
2.PutDatabaseRecord

-

If the Answer helped to resolve your issue, Click on Accept button below to accept the answer, That would be great help to Community users to find solution quickly for these kind of issues.

Re: Put MongoDB Data into SQL Server

Expert Contributor

Wow..That was quick and clear..

Thanks for the reply @Shu.

Will get back to you once i try this.

Re: Put MongoDB Data into SQL Server

Expert Contributor

Thanks for the reply @Shu.

I tried what you have suggested but i got struck.

I ll give you the screen shots of what i have done..

Please correct me where i was doing mistake.

GetMongo Processor:-

88522-untitled.png

PutDataBaseRecord Processor:-

I have mentioend

Record Reader -- avroreader

statement type - INSERT

Database connection pooling service - DBCPCONNECTIONPOOL

schema name - dbo

Table Name - Locations

Rest is as they are

then

AvroReader:-

88525-untitled3.png

AvroShemaRegistry:-

88526-untitled4.png

DBCPConnectionPool:-

88528-untitled5.png

And then i created upstream connections and started.

But i am authentication error which is something weird for me as the same user id and credentials i am using in other applications to get the data from sql also.

Error as

Can not create pooling connection factory (login failed for user pbiuser).

Can you please help me with this.

I have tried for every possibility with that userid and password.but it didnt worked.

My sql server, and nifi server both are in same machine.


untitled2.png

Re: Put MongoDB Data into SQL Server

Super Guru

@Mohan V

NiFi not able to create connection pool as it is failing to login with the username/password.

Please check is there any firewalll blocking and make sure all the connection details and db drivers are configured correctly.

Re: Put MongoDB Data into SQL Server

Expert Contributor

@Shu Im still having the same issue.

I checked the firewalls and they are off.

And i have downloded the jdbc drivers for sql and put it onto C:\Program Files\Java\jre1.8.0_171\lib\ext this directory

In PutDatabaserecord:-

Url:- jdbc:sqlserver://hjcorpsql-04:1433;databaseName=Test1;user=MyorganizationName\pbiuser;password=Secure@99;

ClassName:- com.microsoft.sqlserver.jdbc.SQLServerDriver

Driver Location:- E:/Software/sqljdbc_6.0/enu/jre8/sqljdbc42.jar

untitled5.png

92384-untitled5.png

Im running my nifi onto production system.

And the sql server is SQL Server Enterprise Edition 2016 and Windows Server 2012 R2.

Please help me.

I have been struck here from last one week.