sql-to-elastic

Straighfoward tooling to move data from a SQL instance to and ElasticSearch instance

View the Project on GitHub kylekarpack/sql-to-elastic

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