zerodha/dungbeetle

A highly opinionated, distributed job-queue built specifically for queuing and executing heavy SQL read jobs asynchronously. Supports MySQL, Postgres, ClickHouse.

dungbeetle's Issues

Improve the read/insert process in some way...

The code inside cmd/jobber.go

func writeResults(jobID string, task *Task, ttl time.Duration, rows *sql.Rows, jobber *Jobber) (int64, error) {

for rows.Next() {
	if err := rows.Scan(resPointers...); err != nil {
		return numRows, err
	}
	if err := w.WriteRow(resCols); err != nil {
		return numRows, fmt.Errorf("error writing row to result backend: %v", err)
	}

	numRows++
	fmt.Println("Writing row to result backend: %d", numRows)
}

it's generic for all drivers thus database/sql dont support some kind of bulks... but if target backend support multiples rows in insert could be a simple aproach to improve velocity and could be configurable... #23

Adding new drivers

Hi, i'm playing with sql-jobber ... and asking if could be interesting adding new drivers

https://github.com/sijms/go-ora
_ "github.com/sijms/go-ora/v2" // sql.Open("oracle", "oracle://OT:yourpassword@localhost/XE")

https://github.com/mithrandie/csvq-driver
_ "github.com/mithrandie/csvq-driver" // sql.Open("csvq", "/path/to/data/directory")

And even.. https://pkg.go.dev/cloud.google.com/go/bigquery

I played successfully with the 2 first (i consider use sql-jobber + csvq as option to generate csv files from queries... a prof of concept :) but this leadme to change sqldb.go (backend) in some place to make it csvq dialect compatible with the actual code ... adding new drivers may lead to spread ifs like this

// This will be filled by the driver. if w.backend.opt.DBType == dbTypePostgres { // Postgres placeholders are $1, $2 ... colValHolder[i] = fmt.Sprintf("$%d", i+1) } else { colValHolder[i] = "?" }
or maybe refactor in some way, maybe adding new backend for "non standard sql databases"...

what do you think about it?... if consider interesting i can submit a PR.

Use Unlogged Tables in PostgreSQL

Unlogged Tables was first introduced at PostgreSQL 9.1. From release notes:

• Support unlogged tables using the UNLOGGED option in CREATE TABLE (Robert Haas)

Such tables provide better update performance than regular tables, but are not crash-safe: their contents are automatically cleared in case of a server crash. Their contents do not propagate to replication slaves, either.

Wonder if can can add this statement here https://github.com/knadh/sql-jobber/blob/master/backends/sqldb.go#L282 ? And perhaps add a new config option unlogged = true that works just for dbType=postgres

Add the option for reload sql in runtime

I think could be interesting use something like fsnotify to watch changes in sql file (when debugging or running outside the build... i need to try all the options) but the idea (later) is to have way to include new sql e.g. via API ... without restarting or in production...

Ideas for an architectural change to add new plugins (db)

As comented in this issue here we can discuss options to implements new drivers (or plugins) in a fancy way that could select and reduce (in the build) dependencies, mantaining clear an easy compile/build the code for the standard cases.

I think those are the principal points for this: maintain easy (build and code) but reduce the size (if that it's important in some scenarios)

I read something like this in some place under the machinery proyect (maybe the unexplore for me now to the v2 experiment https://github.com/RichardKnop/machinery#v2-experiment) but backing to this proyect... i would like to explore two options:

1. The standard way in golang (i thought)
2. The wasm aproach e.g this one... that seems an aproach that some project took https://github.com/proxy-wasm/spec/blob/master/docs/WebAssembly-in-Envoy.md

Let's talk

Fix circle-ci build step

https://circleci.com/gh/knadh/sql-jobber/4?utm_campaign=workflow-failed&utm_medium=email&utm_source=notification

@joeirimpan Could you please take a look?