I am using azure sql server database which contains my application data which is accumulated over the time and it has grown too big in size. Therefore it is getting harder to query even though there is proper indexing in the sql server. In addition, if i want to add column with indexing to the already very big data would be a problem as it could slow down the sql server.
I would like to migrate these data to the elasticsearch as they support near real time for better searching and scaling purpose. I also could do basic analytic with Kibana with the data as well. I have set up the elasticsearch in the Azure with 3 data nodes.
I understand that elastic has officially deprecated __river where it is a plugin to import from sql to elasticsearch. Although there are still JCBC driver importer but I am not sure about the consistency of migrating the data over to the elasticsearch as my data which consists of multiple table link together. Yes, there could be few left join to match them together. I could think of run through the data and save it into a .json file and then do curl by importing those file into elasticsearch.
I am not sure about this method as my data of the table could easily go up to Gigabyte of data. I could have run through the data in the sql server by grouping it day by day and then index it into the elasticsearch. The concern i have for this is the speed of migrating into elastic as i need to loop over by query each day of the data and convert it into json file and load into elasticsearch. Will this cause any issue during migrating? Since the table of the sql server is quite big, I also have a concern this could run very slow and there are approx of 1 year of data to run through.
What could have been the better way to import these data?
Thank you in advance.
