roistat / go-clickhouse Goto Github PK

View Code? Open in Web Editor NEW

189.0 10.0 29.0 53 KB

Golang ClickHouse connector

License: MIT License

Go 100.00%

clickhouse golang adapter go connector client

go-clickhouse's Introduction

go-clickhouse

Golang Yandex ClickHouse connector

ClickHouse manages extremely large volumes of data in a stable and sustainable manner. It currently powers Yandex.Metrica, world’s second largest web analytics platform, with over 13 trillion database records and over 20 billion events a day, generating customized reports on-the-fly, directly from non-aggregated data. This system was successfully implemented at CERN’s LHCb experiment to store and process metadata on 10bn events with over 1000 attributes per event registered in 2011.

Examples

Query rows

conn := clickhouse.NewConn("localhost:8123", clickhouse.NewHttpTransport())
query := clickhouse.NewQuery("SELECT name, date FROM clicks")
iter := query.Iter(conn)
var (
    name string
    date string
)
for iter.Scan(&name, &date) {
    //
}
if iter.Error() != nil {
    log.Panicln(iter.Error())
}

Single insert

conn := clickhouse.NewConn("localhost:8123", clickhouse.NewHttpTransport())
query, err := clickhouse.BuildInsert("clicks",
    clickhouse.Columns{"name", "date", "sourceip"},
    clickhouse.Row{"Test name", "2016-01-01 21:01:01", clickhouse.Func{"IPv4StringToNum", "192.0.2.192"}},
)
if err == nil {
    err = query.Exec(conn)
    if err == nil {
        //
    }
}

External data for query processing

See documentation for details

conn := clickhouse.NewConn("localhost:8123", clickhouse.NewHttpTransport())
query := clickhouse.NewQuery("SELECT Num, Name FROM extdata")
query.AddExternal("extdata", "Num UInt32, Name String", []byte("1	first\n2	second")) // tab separated


iter := query.Iter(conn)
var (
    num  int
    name string
)
for iter.Scan(&num, &name) {
    //
}
if iter.Error() != nil {
    log.Panicln(iter.Error())
}

Cluster

Cluster is useful if you have several servers with same Distributed table (master). In this case you can send requests to random master to balance load.

cluster.Check() pings all connections and filters active ones
cluster.ActiveConn() returns random active connection
cluster.OnCheckError() is called when any connection fails

Important: You should call method Check() at least once after initialization, but we recommend to call it continuously, so ActiveConn() will always return filtered active connection.

http := clickhouse.NewHttpTransport()
conn1 := clickhouse.NewConn("host1", http)
conn2 := clickhouse.NewConn("host2", http)

cluster := clickhouse.NewCluster(conn1, conn2)
cluster.OnCheckError(func (c *clickhouse.Conn) {
    log.Fatalf("Clickhouse connection failed %s", c.Host)
})
// Ping connections every second
go func() {
    for {
        cluster.Check()
        time.Sleep(time.Second)
    }
}()

Transport options

Timeout

t := clickhouse.NewHttpTransport()
t.Timeout = time.Second * 5

conn := clickhouse.NewConn("host", t)

go-clickhouse's People

Contributors

Stargazers

Watchers

go-clickhouse's Issues

The build doesn't run tests

Furthermore, tests fail when run locally with clickhouse installed

--- FAIL: TestPrepareExecPostRequest (0.00s)
        Error Trace:    transport_test.go:69
        Error:          Not equal: 
                        expected: <nil>(<nil>)
                        actual  : *url.Error(&url.Error{Op:"parse", URL:"127.0.0.0:8123", Err:(*errors.errorString)(0xc420112600)})
        Test:           TestPrepareExecPostRequest
panic: runtime error: invalid memory address or nil pointer dereference [recovered]
        panic: runtime error: invalid memory address or nil pointer dereference
[signal SIGSEGV: segmentation violation code=0x1 addr=0x40 pc=0x68e622]

goroutine 63 [running]:
testing.tRunner.func1(0xc4201244e0)
        /usr/lib/go-1.8/src/testing/testing.go:622 +0x29d
panic(0x6da460, 0x899fc0)
        /usr/lib/go-1.8/src/runtime/panic.go:489 +0x2cf
github.com/roistat/go-clickhouse.TestPrepareExecPostRequest(0xc4201244e0)
        /home/dude/go/src/github.com/roistat/go-clickhouse/transport_test.go:70 +0x132
testing.tRunner(0xc4201244e0, 0x744670)
        /usr/lib/go-1.8/src/testing/testing.go:657 +0x96
created by testing.(*T).Run
        /usr/lib/go-1.8/src/testing/testing.go:697 +0x2ca
FAIL    github.com/roistat/go-clickhouse        0.027s

format csv insertition

I want to use insert statement with format csv. For example

INSERT INTO events FORMAT CSV 8cd3e02c-83b1-41f0-8e29-c1681f03feb0,1a9cbce7-0788-430e-b3e7-15bbbea2ac27,3,0,0,0.000010,0.200000,,2017-02-27,1488203857,4,3,3,4,0,0,4,0,,,Unknown,Linux Unknown,0,0,[],,,0,0,0,,127.0.0.1,0,0,0,,0,,0,0,0,,,,,,,,,,,,,[]

For this purpose my code looks like (simplified)

...
query := clickhouse.NewQuery("INSERT INTO events FORMAT CSV https://github.com, https://vk.com")
query.Exec(c.chCluster.ActiveConn())
...

Everething works ok untill there is no "?" character in my query statement. When I`m doing something like this

query := clickhouse.NewQuery("INSERT INTO events FORMAT CSV https://github.com/roistat/go-clickhouse/issues?q=asdasda, https://vk.com/im?sel=12892913")

I have panic

panic: runtime error: index out of range

goroutine 1 [running]:
github.com/roistat/go-clickhouse.prepareHttp(0xc4570e6000, 0xd59f82b, 0x0, 0x0, 0x0, 0xc4200dc228, 0xc4212515c0)
        /home/dmitry/golang/src/github.com/roistat/go-clickhouse/transport.go:100 +0x270
github.com/roistat/go-clickhouse.HttpTransport.Exec(0xc42000c440, 0xc4570e6000, 0xd59f82b, 0x0, 0x0, 0x0, 0x0, 0x0, 0x0, 0xc420000100, ...)
        /home/dmitry/golang/src/github.com/roistat/go-clickhouse/transport.go:23 +0xbf
github.com/roistat/go-clickhouse.(*HttpTransport).Exec(0x82b278, 0xc42000c440, 0xc4570e6000, 0xd59f82b, 0x0, 0x0, 0x0, 0x0, 0x0, 0x0, ...)
        <autogenerated>:4 +0x84
github.com/roistat/go-clickhouse.Query.Exec(0xc4570e6000, 0xd59f82b, 0x0, 0x0, 0x0, 0x0, 0x0, 0x0, 0xc42000c440, 0x0, ...)
        /home/dmitry/golang/src/github.com/roistat/go-clickhouse/query.go:45 +0x7c
main.(*ClickhouseCluster).ExecQuery(0xc4200dc100, 0xc4570e6000, 0xd59f82b, 0x0, 0x0, 0x0, 0xd59f82b, 0x0)
        /home/dmitry/golang/src/main/clickhouse_cluster.go:39 +0x162
main.main()
        /home/dmitry/golang/src/main/main.go:132 +0x15b8

This panic cause your prepareHttp function in transport.go file, which tries to replace every "?" character with some arg, but args slice is empty.

func prepareHttp(stmt string, args []interface{}) string {
	var res []byte
	buf := []byte(stmt)
	res = make([]byte, 0)
	k := 0
	for _, ch := range buf {
		if ch == '?' {
			res = append(res, []byte(marshal(args[k]))...)
			k++
		} else {
			res = append(res, ch)
		}
	}

	return string(res)
}

Please, can you fix the problem or tell me how I can do such queries in another way.

Back slash broke parsing url

When i call query.Exec() and calls prepareHttp() return string with incorrect query. Back slash not escaped and i get some error from click house server:

panic: [error code=27 message="DB::Exception: Cannot parse input: expected , before: 53.0.2785.143','Windows 8.1',0,1,0,0,1366,768,1366,728,'GA1.2.106696037.1475139446','1475139447154558963',3009207953,'<(60.7076,28.7528),20>',0): (at row 10)"]

Original query

INSERT INTO event (id,event_type_id,created_date,created_at,ip,user_id,url_host_id,url_path,url_query,browser_id,browser_version,operation_system,device_type_id,country_id,test_version,test_segment,screen_width,screen_height,view_port_width,view_port_height,google_uid,yandex_uid,page_type_id,location,session_id) 
VALUES 
(2038956206,17,'2016-10-22','2016-10-22 00:27:19','176.125.161.207/32','UlWlI7UcD6f5q0xz',4063345517,'/search/p4/','f=null&q=%D0%BF%D0%B0%D0%BA%D0%B5%D1%82%20%D0%BF%D0%BE%D0%BB%D0%B8%D1%8D%D1%82%D0%B8%D0%BB%D0%B5%D0%BD%D0%BE%D0%B2%D1%8B%D0%B9\',4269441498,'53.0.2785.143','Windows 8.1',0,1,0,0,1366,768,1366,728,'GA1.2.106696037.1475139446','1475139447154558963',3009207953,'<(60.7076,28.7528),20>',0)

String ends back slash, when i remove him server accept request 'f=null&q=%D0%BF%D0%B0%D0%BA%D0%B5%D1%82%20%D0%BF%D0%BE%D0%BB%D0%B8%D1%8D%D1%82%D0%B8%D0%BB%D0%B5%D0%BD%D0%BE%D0%B2%D1%8B%D0%B9\'

Extremes and WITH TOTALS support

Currently Iter.Scan() breaks on the first empty line and misses extremes and WITH TOTALS rows which separated from the main data by an empty line. https://clickhouse.yandex/reference_en.html#TabSeparated
I see 2 ways:

Include additional data by default
Add flag Iter.SetAdditionalData(bool)

About changing the API struct, e.g: muti-return with err.

	iter := query.Iter(conn)
	if iter.Error() != nil {
		log.Panicln(iter.Error())
	}

I think it would be better that iter use muti-return val, which is golang API usually do.

	iter, err := query.Iter(conn)
	if err != nil {
		log.Panicln(err)
	}