0.0.5 • Published 5 years ago
sql-s3 v0.0.5
sql-s3
Streams arbitrary SQL objects (tables or views) to S3 as delimited files.
Installation
First add the library:
npm install sql-s3 --save
yarn add sql-s3
Then add your database driver:
npm install pg pg-query-stream
npm install sqlite3
npm install mysql
npm install mysql2
npm install oracle
npm install mssql
Usage
Here is an example using environment variables to configure the available options:
const start = Date.now();
const { stream } = require('./index');
const main = async () => {
try {
const s = await stream({
SQL_DIALECT: process.env.SQL_DIALECT, SQL_USER: process.env.SQL_USER, SQL_PASSWORD: process.env.SQL_PASSWORD,
SQL_HOST: process.env.SQL_HOST, SQL_DATABASE: process.env.SQL_DATABASE, DEBUG: process.env.DEBUG,
AWS_ACCESS_KEY: process.env.AWS_ACCESS_KEY, AWS_SECRET_KEY: process.env.AWS_SECRET_KEY,
AWS_REGION: process.env.AWS_REGION, SQL_TABLE: process.env.SQL_TABLE, SQL_SCHEMA: process.env.SQL_SCHEMA,
COMPRESS: process.env.COMPRESS, BUCKET: process.env.BUCKET, KEY_PREFIX: process.env.KEY_PREFIX
});
console.log(s);
} catch(ex) {
console.error(ex);
} finally {
console.log(`Finished ${process.env.SQL_DATABASE}.${process.env.SQL_SCHEMA}.${process.env.SQL_TABLE} in ${Date.now() - start} ms`);
process.exit(0);
}
};
main();
A few notes:
KEY_PREFIX
can be used to override the database name in the S3 object name. Otherwise the file will be called${SQL_DATABASE}/${SQL_SCHEMA}/${opts.SQL_TABLE}.csv
DEBUG
option will log raw queries, row counts and byte flow to stdoutCOMPRESS
option will pass the stream through a Node.js Gzip stream, compressing the resultsDROP_COLUMNS
variable can be used to specify columns to drop (otherwise returnsSELECT *
). Use a comma-separated list to specify multiple columns.