dbatcher

module
v0.0.0-...-2ecc45d Latest Latest
Warning

This package is not in the latest version of its module.

Go to latest
Published: Jan 10, 2023 License: MIT

README

dbatcher

Build Status Go Report Card codecov

NOT YET READY

A server for batching single inserts to databases. Gather many single or just small inserts to your DMBS and send them in batches with dbatcher. That could lower DBMS' load, make less latency and is viable for ClickHouse. The goal of this project is low latency and simplicity to use: you need only HTTP client and JSON libraries.

Supports
  • ClickHouse
  • MySQL

Instalation and setup

  1. Install go
  2. go get github.com/edwvee/dbatcher/cmd/dbatcher
  3. go build github.com/edwvee/dbatcher/cmd/dbatcher
  4. Write config and place it in config.toml
  5. ./dbatcher config.toml
Config example
#address for pprof http (https://pkg.go.dev/runtime/pprof)
#remove if you won't profile
pprof_http_bind = "localhost:6034"

#log for insert errors (not for sync=1 requests)
#format: {"timestamp":..., "timestamp_string":..., "error": ..., "table":..., "fields":..., "rows": ...}\n
#remove or leave empty path if not needed
[insert_error_logger]
    path = "error.log"
    pretty_print = true

[receivers]

    [receivers.first-http]
        type = "http"
        #bind address
        bind = ":8124"

[inserters]

    #first-clickhouse is a name
    [inserters.first-clickhouse]
        #use this type for clickhouse
        type = "clickhouse"
        #connection string (look here https://github.com/ClickHouse/clickhouse-go#dsn)
        #use native tcp interface, not http or mysql
        dsn = "tcp://localhost:9000?user=default"
        #maximum simultaneous connections (treat like maximum simultaneous queries)
        max_connections = 2
        insert_timeout_ms = 30000

    [inserters.second-mysql]
        #use this type for mysql
        type = "mysql"
        #connection string (look here https://github.com/go-sql-driver/mysql#dsn-data-source-name)
        dsn = "user:password@tcp(hostname)/db_name?charset=utf8mb4,utf8"
        #maximum simultaneous connections (treat like maximum simultaneous queries)
        max_connections = 2
        insert_timeout_ms = 30000

    [inserters.third-dummy]
        #dummy inserter only reports about inserts
        type = "dummy"

HTTP interface

Type: POST

URL: /

Query parameters:

  • table (string) - table name. Could be with database. Use backticks (`) here if database or table name should be encoded. Database could be infered from DSN (db connection string). Examples : my_table, database.my_table, `database`.`my_table`
  • fields (string) - comma separated column names that match columns in rows to pass. Spaces are ignored. Use backticks if column name should be escaped. Example: field1,field2,`table`, field4
  • sync (0 or 1) - insert rows right away. Mostly debug feature. Parameters bellow are ignored if sync is set to 1
  • timeout_ms (uint > 0) - timeout before data insertion in milliseconds. Updates for table inside dbatcher after insertion
  • max_rows (uint > 0) - maximum rows number before insert

Body: rows in JSON format. Should be array of arrays. Column order should match fields. For correct type representation see the tables below.

Response: success - code 200, empty body; fail - non 200 code, body with an error message as a plain text.

Insertion to database happens when sync is 1 (only for requests data), after timeout is came or after row count for table reached max_rows (not in request time, async).

Example:

table: db.`table`

fields: string_field,int_field

timeout_ms: 10000

max_rows: 10000

Full URL: http://127.0.0.1:8124/?table=db.%60table%60&fields=string_field%2Cint_field&timeout_ms=10000&max_rows=10000

Body: [[\"foo\",123],[\"bar\",321]]

ClickHouse - JSON types compatibility

string number int/uint as string
UInt8/16/32/64 + +
Int8/16/32/64 + +
Float32/64 +
String/FixedString +
Date yyyy-mm-dd unix time (seconds) unix time (seconds)
DateTime yyyy-mm-dd H:i:s unix time (seconds) unix time (seconds)
DateTime64 yyyy-mm-dd H:i:s.XXX
Enum8/16 + +

MySQL - JSON types compatibility

string number int/uint/float as string
TINYINT/SMALLINT/INT/BIGINT UNSIGNED - + +
TINYINT/SMALLINT/INT/BIGINT + +
FLOAT/DOUBLE + +
CHAR/BINARY/VARCHAR/TEXT +
DATE yyyy-mm-dd
DATETIME/TIMESTAMP yyyy-mm-dd H:i:s
ENUM + +
  1. For MySQL dbatcher uses INSERT IGNORE.
  2. Those types and formats are tested. Others could work too (see MySQL documentation). Open an issue to add official support.

Directories

Path Synopsis
cmd
internal
pkg

Jump to

Keyboard shortcuts

? : This menu
/ : Search site
f or F : Jump to
y or Y : Canonical URL