1.1.3 • Published 4 years ago
sql-to-elastic v1.1.3
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
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 } }
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" } }
- Add a query to select the total number of rows to transfer at
./total.sql
. For example:
or for large tables to avoid a full scan:SELECT COUNT(*) FROM Products
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);
- 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
- Run
yarn install
ornpm install
to install dependencies - Run the utility with
yarn start
/npm start
ornode 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