Importing data from SQL Server to Mongo

matiasdesousa — Sat, 16 Nov 2024 00:01:49 GMT

Hi.

I have a simple job to import data from SQL Server to MongoDB and i noticed something strange when inserting data into Mongo.

Some fields that are empty in SQL server are being filled with data in Mongo when it should be null.

Example:

If i execute this query:

select SUBSTRING(UF204,1,14) as imei_14, uf204 as imei, uf102 as trafego, uf201 as Origem, uf211 as Destino, uf301 as Data, uf300 as hora, uf400 as Duração, uf202 as IMSI, uf203 as LAC_CI

from dbo.History20210605 where uf101=1 and ProcID like 'MCR_09_PRA_CSR4_%' and uf301 = 20210605 and uf201='5885732'"

The result are 4 lines and it's comming with null fields and being inserted as it should in Mongo.

But if i change the query to (removed last part - and uf201='5885732'😞

select SUBSTRING(UF204,1,14) as imei_14, uf204 as imei, uf102 as trafego, uf201 as Origem, uf211 as Destino, uf301 as Data, uf300 as hora, uf400 as Duração, uf202 as IMSI, uf203 as LAC_CI

from dbo.History"+context.table_date +" where uf101=1 and ProcID like 'MCR_09_PRA_CSR4_%' and uf301 = 20210605"

The result of this query are 2 million lines that are inserted into Mongo. If i search for the {uf201: 5885732}

the results are the same 4 lines from first query but the fields that should be null are not, which is causing data to be incorrect.

I have a java code(image) that converts the string from SQL to number before inserting to Mongo but don't think that causes any issue.

Any idea on what can be the cause of this? Let me know if you need any more info.

Edit:

The problem seems to be in the conversion to Long. I made that conversion so the search would be faster since the fields are mostly numbers but they are commng as strings from SQL.

If i put them as strings in mongo it seems to work fine but that would slow dow the search. Any suggestions?

Re: Importing data from SQL Server to Mongo

Anonymous — Thu, 24 Jun 2021 07:58:00 GMT

Hello,

Could you please clarify in which Talend version/edition you are?

Is the schema column number type correctly mapped to mongodb collection field type with tMongoDBOutput component? What's Mongodb version are you using? Mongodb 3.6?

Best regards

Sabrina

Re: Importing data from SQL Server to Mongo

matiasdesousa — Wed, 07 Jul 2021 12:44:49 GMT

Hi.

I'm usin Talend Open Studio for Big Data Version: 7.1.1.

The schema is correctly mapped or otherwise it will throw an error.

It's Mongo v4.0.10-rc1

Re: Importing data from SQL Server to Mongo

gjeremy1617088143 — Wed, 07 Jul 2021 13:14:31 GMT

Hi, you can put a tlogrow just after tDBInput to see the values you get from the query .

So you'll see if the value are null or not when you get it for uf201='5885732'"

Send me Love and kudos

topic Re: Importing data from SQL Server to Mongo in Talend Studio

Importing data from SQL Server to Mongo

Re: Importing data from SQL Server to Mongo

Re: Importing data from SQL Server to Mongo

Re: Importing data from SQL Server to Mongo