sql-load

README

sql-load

A generic worker to load a newline delimited json (ndj) into a sql database

Initially only postgresql will be supported

info query params:

• origin : (required) file, or folder path (all files) to parse and insert into table
• table : (required), the name of the table to be inserted into i.e., schema.table_name
• delete : will build and run a delete statement based on provided params
  • this is executed before the insert statements
  • provide a list of delete key:values to be used in the delete statement
  • "?delete=date:2020-07-01|id:7"
• truncate: will truncate (delete all) from the table before the insert
• fields : allows mapping different json key values to different database column names
  • provide a list of field name mapping {json key name}:{DB column name} to be mapped
  • ?fields=jsonKeyName:dbColumnName
• cached_insert : will create a temp table for insert
  • this temp table data will then be inserted into the [table_name]
• batch_size : number of rows to insert at once (default: 1000)

Example tasks:

// These will use the sql batch loader
{"type":"sql_load","info":"gs://bucket/path/to/file.json?table=schema.table_name&delete=date:2020-07-01|id:7"}

{"type":"sql_load","info":"gs://bucket/path/of/files/to/load/?table=schema.table_name"}

{"type":"sql_load","info":"gs://bucket/path/to/file.json?table=schema.table_name&delete=date:2020-07-01|id:7&fields=jsonKeyValue:dbColumnName"}

// These examples will use the cached_insert 
// this creates a temp table to insert the data, 
// and inserts into the main table from the temp table to improve insert loading time
{"type":"sql_load","info":"gs://bucket/path/to/file.json?cached_insert&table=schema.table_name&delete=date:2020-07-01|id:7&fields=jsonKeyValue:dbColumnName"}

{"type":"sql_load","info":"gs://bucket/path/to/file.json?cached_insert&table=schema.table_name&truncate&fields=jsonKeyValue:dbColumnName"}

Technical:

data flow through channels