Skip to content
Straighfoward tooling to move data from a SQL instance to and ElasticSearch instance
Branch: master
Clone or download
Fetching latest commit…
Cannot retrieve the latest commit at this time.
Permalink
Type Name Latest commit message Commit time
Failed to load latest commit information.
.vscode
.eslintrc.json
.gitattributes
.gitignore
LICENSE
README.md
_config.yml
app.js
elastic-connection.js
index.js
mssql-connection.js
package.json
yarn.lock

README.md

sql-to-elastic

Installation

$ npm i -g sql-to-elastic

or

$ yarn global add sql-to-elastic

Usage

$ sql-to-elastic [options]

Options:
  -V, --version            output the version number
  -e, --elastic <type>     Elastic config location (default: "./config/elastic.json")
  -m, --mssql <type>       MSSQL config location (default: "./config/elastic.json")
  -q, --query <type>       Location of query to retrieve data from SQL Server (default: "./query.sql")
  -t, --totalQuery <type>  Location of query to retrieve total rows from sql server or a total row count (default: "./total.sql")
  -r, --totalRows <type>   Total rows to process (default: 100000)
  -h, --help               output usage information

Prerequisites

  1. Add a file at ./config/mssql.json containing private connection options for MSSQL in the following format:
    {
    	"user": "...",
    	"password": "...",
    	"server": "localhost",
    	"database": "...",
    
    	"options": {
    		"encrypt": true
    	}
    }
  2. Add a file at ./config/elastic.json containing private connection options for ElasticSearch in the following format:
    {
    	"client": {
    		"host": "localhost:9200",
    		"log": "trace"
    	},
    	"bulk": {
    		"_index": "index",
    		"_type": "type"
    	}
    }
  3. Add a query to select the total number of rows to transfer at ./total.sql. For example:
    SELECT COUNT(*) FROM Products
    or for large tables to avoid a full scan:
    SELECT SUM (row_count) as total
    FROM sys.dm_db_partition_stats
    WHERE object_id=OBJECT_ID('Products')   
    AND (index_id=0 or index_id=1);
  4. Add a query to select data to transfer data at ./query.sql, including @Offset and @Limit parameters to allow for paging. For example:
    ;WITH pg AS (
    	SELECT ProductId
    	FROM Products
    	ORDER BY ProductId
    	OFFSET @Offset ROWS
    	FETCH NEXT @Limit ROWS only
    )
    SELECT * FROM Products p
    JOIN pg ON p.ProductId = pg.ProductId

Development

  1. Run yarn install or npm install to install dependencies
  2. Run the utility with yarn start/npm start or node index.js

Issues

  • Paging is very tightly coupled to named SQL parameters right now. This should be more dynamic
  • Errors are not handled elegantly yet
You can’t perform that action at this time.