Writing UDF for Clickhouse using Golang

Today we're going to create an UDF (User-defined Function) in Golang that can be run inside Clickhouse query, this function will parse uuid v1 and return timestamp of it since Clickhouse doesn't have this function for now. Inspired from the python version with TabSeparated delimiter (since it's easiest to parse), UDF in Clickhouse will read line by line (each row is each line, and each text separated with tab is each column/cell value):

package main
import (
    "bufio"
    "encoding/binary"
    "encoding/hex"
    "fmt"
    "os"
    "strings"
    "time"
)
func main() {
    scanner := bufio.NewScanner(os.Stdin)
    scanner.Split(bufio.ScanLines)
    for scanner.Scan() {
        id, _ := FromString(scanner.Text())
        fmt.Println(id.Time())
    }
}
func (me UUID) Nanoseconds() int64 {
    time_low := int64(binary.BigEndian.Uint32(me[0:4]))
    time_mid := int64(binary.BigEndian.Uint16(me[4:6]))
    time_hi := int64((binary.BigEndian.Uint16(me[6:8]) & 0x0fff))
    return int64((((time_low) + (time_mid << 32) + (time_hi << 48)) - epochStart) * 100)
}
func (me UUID) Time() time.Time {
    nsec := me.Nanoseconds()
    return time.Unix(nsec/1e9, nsec%1e9).UTC()
}
// code below Copyright (C) 2013 by Maxim Bublis <b@codemonkey.ru>
// see https://github.com/satori/go.uuid
// Difference in 100-nanosecond intervals between
// UUID epoch (October 15, 1582) and Unix epoch (January 1, 1970).
const epochStart = 122192928000000000
// UUID representation compliant with specification
// described in RFC 4122.
type UUID [16]byte
// FromString returns UUID parsed from string input.
// Following formats are supported:
// "6ba7b810-9dad-11d1-80b4-00c04fd430c8",
// "{6ba7b810-9dad-11d1-80b4-00c04fd430c8}",
// "urn:uuid:6ba7b810-9dad-11d1-80b4-00c04fd430c8"
func FromString(input string) (u UUID, err error) {
    s := strings.Replace(input, "-", "", -1)
    if len(s) == 41 && s[:9] == "urn:uuid:" {
        s = s[9:]
    } else if len(s) == 34 && s[0] == '{' && s[33] == '}' {
        s = s[1:33]
    }
    if len(s) != 32 {
        err = fmt.Errorf("uuid: invalid UUID string: %s", input)
        return
    }
    b := []byte(s)
    _, err = hex.Decode(u[:], b)
    return
}
// Returns canonical string representation of UUID:
// xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx.
func (u UUID) String() string {
    return fmt.Sprintf("%x-%x-%x-%x-%x",
        u[:4], u[4:6], u[6:8], u[8:10], u[10:])
}

Compile and put it with proper owner and permission on /var/lib/clickhouse/user_scripts/uuid2timestr and create /etc/clickhouse-server/uuid2timestr_function.xml (must be have proper suffix) containing:

<functions>
    <function>
        <type>executable</type>
        <name>uuid2timestr</name>
        <return_type>String</return_type>
        <argument>
            <type>String</type>
        </argument>
        <format>TabSeparated</format>
        <command>uuid2timestr</command>
        <lifetime>0</lifetime>
    </function>
</functions>

after that you can restart Clickhouse (sudo systemctl restart clickhouse-server or sudo clickhouse restart) depends on how you install it (apt or binary setup).

Usage

to make sure it's loaded, you can just find this line on the log:

<Trace> ExternalUserDefinedExecutableFunctionsLoader: Loading config file '/etc/clickhouse-server/uuid2timestr_function.xml

then just run a query using that function:

SELECT uuid2timestr('51038948-97ea-11ee-b7e0-52de156a77d8')

┌─uuid2timestr('51038948-97ea-11ee-b7e0-52de156a77d8')─┐
│ 2023-12-11 05:58:33.2391752 +0000 UTC                │
└──────────────────────────────────────────────────────┘

mTLS using Golang Fiber

In this demo we're going to create mTLS using Go and Fiber. To create certificates that can be used for mutual authentication, what you need to have is just an OpenSSL program (or simplecert, or mkcert like in previous natsmtls1 example), create a CA (certificate authority), server certs, and client certs, something like this:

# generate CA Root
openssl req -newkey rsa:2048 -new -nodes -x509 -days 3650 -out ca.crt -keyout ca.key -subj "/C=SO/ST=Earth/L=MyLocation/O=MyOrganiz/OU=MyOrgUnit/CN=localhost"

# generate Server Certs
openssl genrsa -out server.key 2048
# generate server Cert Signing request
openssl req -new -key server.key -days 3650 -out server.csr -subj "/C=SO/ST=Earth/L=MyLocation/O=MyOrganiz/OU=MyOrgUnit/CN=localhost"
# sign with CA Root
openssl x509  -req -in server.csr -extfile <(printf "subjectAltName=DNS:localhost") -CA ca.crt -CAkey ca.key -days 3650 -sha256 -CAcreateserial -out server.crt

# generate Client Certs
openssl genrsa -out client.key 2048
# generate client Cert Signing request
openssl req -new -key client.key -days 3650 -out client.csr -subj "/C=SO/ST=Earth/L=MyLocation/O=$O/OU=$OU/CN=localhost"
# sign with CA Root
openssl x509  -req -in client.csr -extfile <(printf "subjectAltName=DNS:localhost") -CA ca.crt -CAkey ca.key -out client.crt -days 3650 -sha256 -CAcreateserial

You will get at least 2 files related to CA, 3 files related to server, and 3 files related to client, but what you really need is just CA public key, server private and public key (key pairs), and client private and public key (key pairs). If you need to generate another client or rollover server keys, you will still need CA's private key so don't erase it.

Next, now that you already have those 5 keys, you will need to load CA public key, and server key pair and use it on fiber, something like this:

caCertFile, _ := os.ReadFile(in.CaCrt)
caCertPool := x509.NewCertPool()
caCertPool.AppendCertsFromPEM(caCertFile)

serverCerts, _ := tls.LoadX509KeyPair(in.ServerCrt, in.ServerKey)

tlsConfig := &tls.Config{
    ClientCAs:        caCertPool,
    ClientAuth:       tls.RequireAndVerifyClientCert,
    MinVersion:       tls.VersionTLS12,
    CurvePreferences: []tls.CurveID{tls.CurveP521, tls.CurveP384, tls.CurveP256},
        CipherSuites: []uint16{
        tls.TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384,
        tls.TLS_ECDHE_RSA_WITH_AES_256_CBC_SHA,
        tls.TLS_RSA_WITH_AES_256_GCM_SHA384,
        tls.TLS_RSA_WITH_AES_256_CBC_SHA,
         tls.TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256,
        tls.TLS_ECDHE_ECDSA_WITH_AES_128_GCM_SHA256,
     },
     Certificates: []tls.Certificate{serverCerts},
}

// attach the certs to TCP socket, and start Fiber server
app := fiber.New(fiber.Config{
    Immutable: true,
})
app.Get("/", func(c *fiber.Ctx) error {
    return c.String(`secured string`)
})
ln, _ := tls.Listen("tcp", `:1443`, tlsConfig)
app.Listener(ln)

next on the client side, you just need to load CA public key, client key pairs, something like this:

caCertFile, _ := os.ReadFile(in.CaCrt)
caCertPool := x509.NewCertPool()
caCertPool.AppendCertsFromPEM(caCertFile)
certificate, _ := tls.LoadX509KeyPair(in.ClientCrt, in.ClientKey)

httpClient := &http.Client{
    Timeout: time.Minute * 3,
    Transport: &http.Transport{
        TLSClientConfig: &tls.Config{
            RootCAs:      caCertPool,
            Certificates: []tls.Certificate{certificate},
        },
    },
}

r, _ := httpClient.Get(`https://localhost:1443`)

that's it, that's how you secure client-server communication between Go client and server with mTLS, this code can be found here.

Simple Websocket Echo Benchmark

Today we're gonna benchmark nodejs/bin+uwebsocket with golang+nbio. The code is here, both taken from their own example. The benchmark plan is create 10k client/connection, send both text/binary string and receive back the from server and sleep for 1s,100ms,10ms or 1ms, the result is as expected:

go 1.20.5 nbio 1.3.16
rps: 19157.16 avg/max latency = 1.66ms/319.88ms elapsed 10.2s
102 MB 0.6 core usage
rps: 187728.05 avg/max latency = 0.76ms/167.76ms elapsed 10.2s
104 MB 5 core usage
rps: 501232.80 avg/max latency = 12.48ms/395.01ms elapsed 10.1s
rps: 498869.28 avg/max latency = 12.67ms/425.04ms elapsed 10.1s
134 MB 15 core usage

bun 0.6.9
rps: 17420.17 avg/max latency = 5.57ms/257.61ms elapsed 10.1s
48 MB 0.2 core usage
rps: 95992.29 avg/max latency = 29.93ms/242.74ms elapsed 10.4s
rps: 123589.91 avg/max latency = 40.67ms/366.15ms elapsed 10.2s
rps: 123171.42 avg/max latency = 62.74ms/293.29ms elapsed 10.1s
55 MB 1 core usage

node 18.16.0
rps: 18946.51 avg/max latency = 6.64ms/229.28ms elapsed 10.3s
59 MB 0.2 core usage
rps: 97032.08 avg/max latency = 44.06ms/196.41ms elapsed 11.1s
rps: 114449.91 avg/max latency = 72.62ms/295.33ms elapsed 10.3s
rps: 109512.05 avg/max latency = 79.27ms/226.03ms elapsed 10.2s
59 MB 1 core usage

First line until 4th line are with 1s, 100ms, 10ms, 1ms delay before next request. Since Golang/nbio is by default can utilize multi-core so can handle ~50 rps per client, while Bun/Nodejs 11-12 rps per client. If you found a bug, or want to contribute another language (or create better client, just create a pull request on the github link above.

Dockerfile vs Nixpacks vs ko

Dockerfile is quite simple, first we need to pick the base image for build phase (only if you want to build inside docker, if you already have CI/CD that build it outside, you just need to copy the executable binary directly), put command of build steps, choose runtime image for run stage (popular one like ubuntu/debian have bunch of debugging tools, alpine/busybox for stripped one), copy the binary to that layer and done. 

 
FROM golang:1.20 as build1
WORKDIR /app1
# if you don't use go mod vendor
#COPY go.mod .
#COPY go.sum .
#RUN go mod download
COPY . .
RUN CGO_ENABLED=0 GOOS=linux go build -o app1.exe

FROM busybox:latest
WORKDIR /
COPY --from=build1 /etc/ssl/certs /etc/ssl/certs
COPY --from=build1 /app1/app1.exe .
CMD ./app1.exe 

then run the docker build and docker run command:

# build
docker build . -t app0
[+] Building 76.2s (15/15) FINISHED -- first time, without vendor
[+] Building 9.5s (12/12) FINISHED -- changing code, rebuild, with go mod vendor

# run
docker run -it app0

with nixpacks you just need to run this without having to create Dockerfile (as long there's main.go file):

# install nixpack
curl -sSL https://nixpacks.com/install.sh | bash

# build
nixpacks build . --name app1
[+] Building 315.7s (19/19) FINISHED -- first time build
[+] Building 37.2s (19/19) FINISHED -- changing code, rebuild

# run
docker run -it app1

With ko, 

# install ko
go install github.com/google/ko@latest

# build
time ko build -L -t app2
CPU: 0.84s      Real: 5.05s     RAM: 151040KB

# run (have to do this since the image name is hashed)
docker run -it `docker image ls | grep app2 | cut -d ' ' -f 1`

How about container image size? Dockerfile with busybox only use 14.5MB, with ubuntu 82.4MB, debian 133MB, alpine 15.2MB, with nixpack it uses 99.2MB, and with ko it only took 11.5MB but it only support Go (and you cannot debug inside it, eg. for testing connectivity to 3rd party dependency using shell inside the container). So is it better to use nixpacks? I don't think so, both build speed and image size for this case is inferior compared to normal Dockerfile with busybox or ko.

CockroachDB Benchmark on Different Disk Types

Today we're going to benchmark CockroachDB one of database that I use this year to create embedded application. I use CockroachDB because I don't want to use SqLite or any other embedded database that lack of tooling or cannot be accessed by multiple program at the same time. With CockroachDB I only need to distribute my application binary, cockroachdb binary, and that's it, the offline backup also quite simple, just need to rsync the directory, or do manual rows export like other PostgreSQL-like database. Scaling out also quite simple.

Here's the result:

Disk Type	Ins Dur (s)	Upd Dur (s)	Sel Dur (s)	Many Dur (s)	Insert Q/s	Update Q/s	Select1 Q/s	SelMany Row/s	SelMany Q/s
TMPFS (RAM)	1.3	2.1	4.9	1.5	31419	19275	81274	8194872	20487
NVME DA 1TB	2.7	3.7	5.0	1.5	15072	10698	80558	8019435	20048
NVMe Team 1TB	3.8	3.7	4.9	1.5	10569	10678	81820	8209889	20524
SSD GALAX 250GB	8.0	7.1	5.0	1.5	4980	5655	79877	7926162	19815
HDD WD 8TB	32.1	31.7	4.9	3.9	1244	1262	81561	3075780	7689

From the table we can see that TMPFS (RAM, obviously) is the fastest in all case especially insert and update benchmark, NVMe faster than SSD, and standard magnetic HDD is the slowest. but the query-part doesn't really have much effect probably because the dataset too small that all can fit in the cache.

The test done with 100 goroutines, 400 records insert/update per goroutines, the record is only integer and string. Queries done 10x for select, and 300x for select-many, sending small query is shown there reaching the limit  of 80K rps, inserts can reach 31K rps and multirow-query/updates can reach ~20K rps.

The repository is here if you want to run the benchmark on your own machine.

Map to Struct and Struct to Map Golang Benchmark 2022 Edition

Sometimes we want to convert from map to struct or struct to map (dictionary in other language), or even struct to struct. There's some library that can help us doing this, for example structs, mapstructure, copier, or smapping. We could also utilize serialization and deserialization libraries to do this. With caveats, that some serialization format (eg. JSON) doesn't allow integer larger than 2^53 for example.

Here's benchmark that I run this morning:

map to struct	total	ns/op	B/op	allocs/op
M2S_GoccyGoJson_MarshalUnmarshal-32	6,661,932	517	80	3
M2S_JsonIteratorGo_MarshalUnmarshal-32	4,892,611	724	196	8
M2S_VmihailencoMspackV5_MarhsalUnmarshal-32	4,572,597	741	188	5
M2S_FxamackerCbor_MarshalUnmarshal-32	4,418,558	799	120	8
M2S_SurrealdbCork_EncodeDecode-32	3,080,282	1,080	1,217	6
M2S_GopkgInMgoV2Bson_MarshalUnmarshal-32	3,227,905	1,092	232	13
M2S_ShamatonMsgpackV2_MarshalUnmarshal-32	3,062,677	1,161	956	15
M2S_MitchellhMapstructure_Decode-32	2,487,428	1,395	720	18
M2S_MongoDriverBson_MarshalUnmarshal-32	2,477,983	1,459	414	14
M2S_KokizzuJson5b_MarshalUnmarshal-32	1,987,240	1,711	632	16
M2S_EncodingJson_MarshalUnmarshal-32	2,056,944	1,780	600	16
M2S_EtNikBinngo_MarshalUnmarshal-32	1,985,595	1,857	425	39
M2S_PquernaFfjson_MarshalUnmarshal-32	1,739,968	1,986	609	16
M2S_UngorjiGocodec_BincEncodeDecode-32	1,401,453	2,582	4,340	23
M2S_UngorjiGoCodec_CborEncodeDecode-32	1,304,828	2,636	4,340	23
M2S_PelletierGoTomlV2_MarshalUnmarshal-32	1,284,037	2,787	1,600	27
M2S_UngorjiGocodec_SimpleEncodeDecode-32	1,295,926	2,810	4,340	23
M2S_UngorjiGocodec_JsonEncodeDecode-32	1,000,000	3,028	4,956	25
M2S_IchibanTnetstrings_MarshalUnmarshal-32	749,947	5,056	9,329	48
M2S_BurntSushiToml_EncodeUnmarshal-32	425,335	8,065	7,958	71
M2S_HjsonHjsonGoV4_MarshalUnmarshal-32	355,784	10,870	3,936	78
M2S_GopkgInYamlV3_MarshalUnmarshal-32	271,190	13,524	14,112	80
M2S_DONUTSLz4Msgpack_MarshalUnmarshal-32	240,619	15,498	1,264	16
M2S_GoccyGoYaml_MarshalUnmarshal-32	214,776	16,192	7,821	214
M2S_GhodssYaml_MarshalUnmarshal-32	156,412	23,347	21,378	161
M2S_NaoinaToml_MarshalUnmarshal-32	57,607	58,331	398,544	77
struct to map	total	ns/op	B/op	allocs/op
S2M_MitchellhMapstructure_Decode-32	5,055,402	716	536	12
S2M_GoccyGoJson_MarshalUnmarshal-32	4,660,224	747	522	12
S2M_JsonIteratorGo_MarshalUnmarshal-32	4,283,262	835	505	14
S2M_VmihailencoMspackV5_MarhsalUnmarshal-32	4,009,863	908	607	12
S2M_FxamackerCbor_MarshalUnmarshal-32	3,562,352	1,023	452	11
S2M_ShamatonMsgpackV2_MarshalUnmarshal-32	3,180,010	1,089	556	15
S2M_GopkgInMgoV2Bson_MarshalUnmarshal-32	3,047,396	1,145	528	15
S2M_SurrealdbCork_EncodeDecode-32	2,976,328	1,196	1,611	12
S2M_EncodingJson_MarshalUnmarshal-32	1,914,165	1,782	688	18
S2M_PquernaFfjson_MarshalUnmarshal-32	1,911,950	1,845	697	18
S2M_EtNikBinngo_MarshalUnmarshal-32	1,948,802	1,859	768	45
S2M_KokizzuJson5b_MarshalUnmarshal-32	1,888,774	1,884	960	20
S2M_MongoDriverBson_MarshalUnmarshal-32	1,857,649	1,995	759	18
S2M_PelletierGoTomlV2_MarshalUnmarshal-32	1,244,012	2,864	1,800	31
S2M_UngorjiGocodec_BincEncodeDecode-32	1,000,000	3,234	4,888	34
S2M_UngorjiGoCodec_CborEncodeDecode-32	989,671	3,358	4,888	34
S2M_UngorjiGocodec_SimpleEncodeDecode-32	1,000,000	3,400	4,888	34
S2M_UngorjiGocodec_JsonEncodeDecode-32	912,512	3,639	5,504	36
S2M_IchibanTnetstrings_MarshalUnmarshal-32	776,796	4,744	9,561	46
S2M_BurntSushiToml_EncodeUnmarshal-32	447,216	8,538	8,231	73
S2M_HjsonHjsonGoV4_MarshalUnmarshal-32	389,476	9,416	3,868	66
S2M_GopkgInYamlV3_MarshalUnmarshal-32	315,939	13,338	14,400	81
S2M_DONUTSLz4Msgpack_MarshalUnmarshal-32	242,330	14,298	744	16
S2M_GoccyGoYaml_MarshalUnmarshal-32	230,042	14,919	7,580	202
S2M_GhodssYaml_MarshalUnmarshal-32	151,023	22,682	21,441	161
S2M_NaoinaToml_MarshalUnmarshal-32	60,916	52,047	398,112	80
struct to struct	total	ns/op	B/op	allocs/op
S2S_GoccyGoJson_MarshalUnmarshal-32	12,046,497	317	112	4
S2S_ShamatonMsgpackV2_MarshalUnmarshal-32	7,897,488	458	148	6
S2S_JsonIteratorGo_MarshalUnmarshal-32	7,853,592	494	92	6
S2S_FxamackerCbor_MarshalUnmarshal-32	7,038,808	511	80	5
S2S_GopkgInMgoV2Bson_MarshalUnmarshal-32	5,105,343	715	144	9
S2S_VmihailencoMspackV5_MarhsalUnmarshal-32	4,549,700	818	213	6
S2S_MongoDriverBson_MarshalUnmarshal-32	3,560,946	1,019	321	8
S2S_EncodingJson_MarshalUnmarshal-32	2,731,051	1,313	304	9
S2S_PquernaFfjson_MarshalUnmarshal-32	2,734,357	1,330	304	9
S2S_KokizzuJson5b_MarshalUnmarshal-32	2,594,728	1,343	504	9
S2S_SurrealdbCork_EncodeDecode-32	2,555,745	1,397	1,241	7
S2S_EtNikBinngo_MarshalUnmarshal-32	1,995,468	1,840	400	41
S2S_PelletierGoTomlV2_MarshalUnmarshal-32	1,460,683	2,459	1,440	23
S2S_UngorjiGocodec_SimpleEncodeDecode-32	1,205,648	2,919	4,364	24
S2S_UngorjiGoCodec_CborEncodeDecode-32	1,290,734	2,920	4,364	24
S2S_UngorjiGocodec_BincEncodeDecode-32	1,207,327	3,007	4,364	24
S2S_UngorjiGocodec_JsonEncodeDecode-32	1,000,000	3,223	4,980	26
S2S_IchibanTnetstrings_MarshalUnmarshal-32	722,493	4,950	9,289	47
S2S_BurntSushiToml_EncodeUnmarshal-32	398,366	8,458	7,918	72
S2S_HjsonHjsonGoV4_MarshalUnmarshal-32	304,369	11,189	4,578	79
S2S_DONUTSLz4Msgpack_MarshalUnmarshal-32	282,505	12,215	237	7
S2S_GopkgInYamlV3_MarshalUnmarshal-32	279,332	12,541	14,016	76
S2S_GoccyGoYaml_MarshalUnmarshal-32	211,952	15,542	7,982	208
S2S_GhodssYaml_MarshalUnmarshal-32	160,660	23,148	21,073	154
S2S_NaoinaToml_MarshalUnmarshal-32	64,468	59,672	399,065	83

The repository and the always updated result is here, feel free to add your own serialization/deserialization library. As we can see, goccy-gojson is the fastest among all, too bad if you store int64 larger than 2^53 it give wrong result. So it's better to use second best and all rounder vmihailenco-msgpack, or for specific use case struct to map/struct is mapstructure.

Here's the top ranking:

Ser/Deser	M2S	S2M	S2S
GoccyGoJson	1	2	2
JsonIteratorGo	2	3	4
MitchellhMapstructure	8	1	1
VmihailencoMspackV5	3	4	7
FxamackerCbor	4	5	5
ShamatonMsgpackV2	7	6	3
SurrealdbCork	5	8	12