database synchronization between traditional RDMS and NoSQL

marrowyung
marrowyung used Ask the Experts™
on
hi,

any  tools/idea you all use to synchronization between traditional RDMS and NoSQL in MS SQL and maria DB and noSQL?
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
David FavorFractional CTO
Distinguished Expert 2018

Commented:
This almost always requires custom code, as each combination will have complex indexing (SQL side) which must be normalize going to simple indexing (no-SQL side).
marrowyungSenior Technical architecture (Data)

Author

Commented:
how about ETL and replication server like oracle goldengate, sybase replication serer ?
David FavorFractional CTO
Distinguished Expert 2018

Commented:
Same answer. You're moving from complex SQL indexing to simple non-SQL indexing, hashed lookups.

Each of these types of moves tend to be unique in their design.
Acronis in Gartner 2019 MQ for datacenter backup

It is an honor to be featured in Gartner 2019 Magic Quadrant for Datacenter Backup and Recovery Solutions. Gartner’s MQ sets a high standard and earning a place on their grid is a great affirmation that Acronis is delivering on our mission to protect all data, apps, and systems.

marrowyungSenior Technical architecture (Data)

Author

Commented:
so can't do much on this ! no tools for it, right?
David FavorFractional CTO
Distinguished Expert 2018

Commented:
No tools for this. You'd write custom tools designed like this.

1) Open connections to database + no-SQL store.

2) Generate your custom SQL SELECT calls.

3) Convert retrieved data into one or more no-SQL writes.

4) Do all your writes.

5) Rinse + repeat for all SQL data tables.

6) Close all your connections.

When I have a client requesting this type of work, I just use PERL to quickly whip up a one-time, throw-away script.
marrowyungSenior Technical architecture (Data)

Author

Commented:
you are PERL programmer ?
David FavorFractional CTO
Distinguished Expert 2018

Commented:
I write PERL code pretty much every day, for projects + command line tools I use with my private hosting clients.

I've been using PERL since... 1994, so I'm guessing I qualify as a PERL programmer.

I only work on code for sites I host though + I'm very expensive.

Likely your best bet is to open an EE Gig + someone can assist you with your project.
marrowyungSenior Technical architecture (Data)

Author

Commented:
"I only work on code for sites I host though + I'm very expensive."

ahahha, a lot of people here say the same thing.

other than PERL,  c# can do that too ?
David FavorFractional CTO
Distinguished Expert 2018

Commented:
All languages are just languages. So any language will work.

That said, if I had a client suggest c# I'd try to contain my laughter... probably couldn't...

I'd figure 10x to 100x the time for c# instead of PERL.

Maybe longer, as your first step will be getting c# to actually work under Linux... as I'm hoping you're using Linux for your project.

If you're imagining Microsoft as your target OS... whew... add 10x to every minor daily task.

You're going to require a massive budget for your project is you're using non-Linux based solution.

https://LaunchSpeedHosting.com covers the LAMP Stack code versions my team currently uses for projects.

I always use Ubuntu for host + container OS, then run LXD at the machine level, where sites/projects all live in their own LXD containers.

You may find this helpful also, a you can clone a production container into a dev container in a few seconds.

This also allows you to deploy a dev site as a production site in roughly 30 seconds.
marrowyungSenior Technical architecture (Data)

Author

Commented:
"Maybe longer, as your first step will be getting c# to actually work under Linux... as I'm hoping you're using Linux for your project."

excellent  !

but I heard that Tungsten replicator 3.0 can replicate from MySQL / oracle to NoSQL like Hadoop, right? it seems nearly real time, right?
Fractional CTO
Distinguished Expert 2018
Commented:
It's not an issue of speed.

The issue is converting complex indexes (SQL) to simple/single/hashed indexes (no-SQL).

Once you start this process once, you'll understand the hurdles involved.

Just pick a database + table + start your conversion process. Pretty simple + sometimes time consuming to complete.

Many consideration is code accessing your non-SQL data will have to be able to lookup data + use it as you envision.

One other option, I'm doing this for a client now, is keeping complex SQL + being able to do Google-ish type searches on various data.

To implement this, I'm using a very simple row structure, basically no-SQL, with a primary key + FTS (Full Text Search) type indexing on top of a column of data tags - ad-hoc keywords + keyphrases which can be anything.

vhttps://mariadb.com/kb/en/library/full-text-index-overview/ covers details of how this works.

Since you haven't mentioned how your no-SQL application will work, difficult to guess anymore about how to assist with your conversion.

Based on your questions, likely best if you hire someone to assist you with your project.

A good designer may look at your end goal + say... "Geez, you can use no-SQL + you'll end up writing far more code + have a barely maintainable system or you can just add an FTS index to your existing SQL database + write code to do FTS lookups."
marrowyungSenior Technical architecture (Data)

Author

Commented:
"
vhttps://mariadb.com/kb/en/library/full-text-index-overview/ covers details of how this works.
"
oh, by using MariaDB ?
you mean Maria DB can do a lot in this area?


"Based on your questions, likely best if you hire someone to assist you with your project."
you mean Database developer ?
marrowyungSenior Technical architecture (Data)

Author

Commented:
tks.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial