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