Skip to content
Permalink
Branch: master
Find file Copy path
Find file Copy path
Fetching contributors…
Cannot retrieve contributors at this time
92 lines (76 sloc) 2.26 KB

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.