locked
Recommendations to sync data from ElasticSearch to MS SQL DB RRS feed

  • Question

  • Looking for recommendations to sync data from ElasticSearch to MS SQL DB

    -Vital

    Tuesday, December 15, 2015 11:50 AM

Answers

  • Good day NagaVital,

    I do not have lot of experience with ElasticSearch, but as much as I understand this is JSON based database (documents database or NOSQL database), while SQL Server is tables based database. I do not think that there is tool to migrate database's schema and if there is then you should probably not use it. You should redesign the entire database structure from scratch!

    The basic procedure of migrating data from NOSQL into Tabular database and vise versa is using something that called a RIVER. Basically, a river is a library to pull data from an external source into the cluster. You can check this tutorial regarding built-in RIVER in old ElasticSearch servers (it is not supported on new versions): https://www.elastic.co/blog/deprecating-rivers. This RIVER was intend to pull data from other sources into the ES, while you need to opposite action.

    What you can do?!?

    It might be good idea to use SQL Server 2016 (as your RIVER) for the migration, since SQL Server 2016 has addition supporting for JSON documents. Using the new JSONE features it should be very easy to migrate the data from ElasticSearch to SQL Server, one you redesign the entire database structure. In older versions of SQL Server you can use SQLCLR in the SQL Server.

    I actually have a procedure that do this exact same request :-)
    I migrates data from MongoDB, which is JSON based database like ElasticSearch, into SQL Server.

    The basic procedures that you can use:

    1. Import the data as simple NVARCHAR(MAX) in staging tables -> Using SQLCLR we migrate the data after analysis into the final tables in the data warehouse (this better to do in SQL 2016 if you don't have developers to develop your apps/code or you don't have the time and this is one-time-job).

    2. Import the data after analysis as tabular structure (the converting/analysis is done with simple small C# application and JSON CLASS)

    * Even if you want to migrate the data to lower version of SQL Server then 2016, you can still use the SQL Server 2016 feature on developing environment in order to do the job :-). You can migrate the data to SQL Server 2016 first, and when it is ready for the production you can open a linked server and migrate it directly from that server to older server (since both databases will have the same structure).

    * In our case some of the data is store as JSON in the SQL Server as well, for better performance in some apps that need the data as JSON. We (actually I) designed a full indexing option for the JSON data (which I intend to continue using in SQL Server 2016, which do not support direct index on the JSON data).

    * If you do not want to use SQLCLR or SQL Server 2016 for the migration and if this is a one-time-action then you probably better use an external application that read the data and convert JSON document into tabular structure and insert it to the SQL Server database.

    I hope this is useful :-)


    signature   Ronen Ariely
     [Personal Site]    [Blog]    [Facebook]    [Linkedin]

    Tuesday, December 15, 2015 1:34 PM

All replies

  • Good day NagaVital,

    I do not have lot of experience with ElasticSearch, but as much as I understand this is JSON based database (documents database or NOSQL database), while SQL Server is tables based database. I do not think that there is tool to migrate database's schema and if there is then you should probably not use it. You should redesign the entire database structure from scratch!

    The basic procedure of migrating data from NOSQL into Tabular database and vise versa is using something that called a RIVER. Basically, a river is a library to pull data from an external source into the cluster. You can check this tutorial regarding built-in RIVER in old ElasticSearch servers (it is not supported on new versions): https://www.elastic.co/blog/deprecating-rivers. This RIVER was intend to pull data from other sources into the ES, while you need to opposite action.

    What you can do?!?

    It might be good idea to use SQL Server 2016 (as your RIVER) for the migration, since SQL Server 2016 has addition supporting for JSON documents. Using the new JSONE features it should be very easy to migrate the data from ElasticSearch to SQL Server, one you redesign the entire database structure. In older versions of SQL Server you can use SQLCLR in the SQL Server.

    I actually have a procedure that do this exact same request :-)
    I migrates data from MongoDB, which is JSON based database like ElasticSearch, into SQL Server.

    The basic procedures that you can use:

    1. Import the data as simple NVARCHAR(MAX) in staging tables -> Using SQLCLR we migrate the data after analysis into the final tables in the data warehouse (this better to do in SQL 2016 if you don't have developers to develop your apps/code or you don't have the time and this is one-time-job).

    2. Import the data after analysis as tabular structure (the converting/analysis is done with simple small C# application and JSON CLASS)

    * Even if you want to migrate the data to lower version of SQL Server then 2016, you can still use the SQL Server 2016 feature on developing environment in order to do the job :-). You can migrate the data to SQL Server 2016 first, and when it is ready for the production you can open a linked server and migrate it directly from that server to older server (since both databases will have the same structure).

    * In our case some of the data is store as JSON in the SQL Server as well, for better performance in some apps that need the data as JSON. We (actually I) designed a full indexing option for the JSON data (which I intend to continue using in SQL Server 2016, which do not support direct index on the JSON data).

    * If you do not want to use SQLCLR or SQL Server 2016 for the migration and if this is a one-time-action then you probably better use an external application that read the data and convert JSON document into tabular structure and insert it to the SQL Server database.

    I hope this is useful :-)


    signature   Ronen Ariely
     [Personal Site]    [Blog]    [Facebook]    [Linkedin]

    Tuesday, December 15, 2015 1:34 PM
  • Thank you Ronen Ariely for your detailed view. This gave me insights to start with. I will also evaluate LogStash and see if it helps me

    -Vital

    Thursday, December 17, 2015 11:43 AM
  • You are most welcome :-)


    signature   Ronen Ariely
     [Personal Site]    [Blog]    [Facebook]    [Linkedin]

    Thursday, December 17, 2015 7:47 PM