ODBC Driver roundrtip

Dockerfile

# docker build -t yandex/clickhouse-server-with-odbc .
# docker start --name ch-odbc-roundtrip -d yandex/clickhouse-server-with-odbc
# docker exec -it ch-odbc-roundtrip clickhouse-client -mn

FROM yandex/clickhouse-server

ARG odbc_driver_url="https://github.com/ClickHouse/clickhouse-odbc/releases/download/v1.1.3.20200115/clickhouse-odbc-1.1.3-Linux.tar.gz"

RUN apt-get update \
    && apt-get install --yes --no-install-recommends unixodbc \
    && mkdir clickhouse-odbc \
    && cd clickhouse-odbc \
    && wget ${odbc_driver_url} -O clickhouse-odbc.tar.gz \
    && tar --strip-components=1 -xvf clickhouse-odbc.tar.gz \
    && cp lib64/*.so /usr/local/lib/ \
    && odbcinst -i -d -f share/doc/clickhouse-odbc/config/odbcinst.ini.sample \
    && odbcinst -i -s -l -f share/doc/clickhouse-odbc/config/odbc.ini.sample \
    && cd .. \
    && rm -rf \
        *.deb \
        /var/lib/apt/lists/* \
        /var/cache/debconf \
        /tmp/* \
    && apt-get clean

test.sql

DROP TABLE IF EXISTS test_decimal_types;
CREATE TABLE test_decimal_types (
    number UInt64,

    col01 Decimal(2,1)   DEFAULT CAST( 1 + rand(1) / 0xFFFFFFFF , 'Decimal(2,1)'),
    col02 Decimal(10,2)  DEFAULT CAST( 10000000 + rand(2) / 0xFFFFFFFF, 'Decimal(10,2)'),
    col03 Decimal(19,3)  DEFAULT CAST( 1000000000000000 + rand(3) / 0xFFFFFFFF, 'Decimal(19,3)'),
    col04 Decimal(33,4)  DEFAULT CAST( 1000000000000000000000000000000 + rand(4) / 0xFFFFFFFF, 'Decimal(33,4)'),
    col05 Decimal32(1)   DEFAULT CAST( 10 + rand(5) / 0xFFFFFFFF, 'Decimal32(1)'),
    col06 Decimal32(7)   DEFAULT CAST( 1  + rand(6) / 0xFFFFFFFF, 'Decimal32(7)'),
    col07 Decimal64(1)   DEFAULT CAST( 10000000000000 + rand(7) / 0xFFFFFFFF, 'Decimal64(1)'),
    col08 Decimal64(15)  DEFAULT CAST( 1 + rand(8) / 0xFFFFFFFF, 'Decimal64(15)'),
    col09 Decimal128(1)  DEFAULT CAST( 1000000000000000000000000000000 + rand(9) / 0xFFFFFFFF, 'Decimal128(1)'),
    col10 Decimal128(30) DEFAULT CAST( 10000000 + rand(10) / 0xFFFFFFFF, 'Decimal128(30)'),

    col11 Nullable(Decimal(2,1))   DEFAULT rand(11) % 100 > 50 ? col01 : NULL,
    col12 Nullable(Decimal(10,2))  DEFAULT rand(12) % 100 > 50 ? col02 : NULL,
    col13 Nullable(Decimal(19,3))  DEFAULT rand(13) % 100 > 50 ? col03 : NULL,
    col14 Nullable(Decimal(33,4))  DEFAULT rand(14) % 100 > 50 ? col04 : NULL,
    col15 Nullable(Decimal32(1))   DEFAULT rand(15) % 100 > 50 ? col05 : NULL,
    col16 Nullable(Decimal32(7))   DEFAULT rand(16) % 100 > 50 ? col06 : NULL,
    col17 Nullable(Decimal64(1))   DEFAULT rand(17) % 100 > 50 ? col07 : NULL,
    col18 Nullable(Decimal64(15))  DEFAULT rand(18) % 100 > 50 ? col08 : NULL,
    col19 Nullable(Decimal128(1))  DEFAULT rand(19) % 100 > 50 ? col09 : NULL,
    col20 Nullable(Decimal128(30)) DEFAULT rand(20) % 100 > 50 ? col10 : NULL,

    col21 Array(Decimal(2,1))   DEFAULT arrayWithConstant(rand(21) % 10, col01),
    col22 Array(Decimal(10,2))  DEFAULT arrayWithConstant(rand(22) % 10, col02),
    col23 Array(Decimal(19,3))  DEFAULT arrayWithConstant(rand(23) % 10, col03),
    col24 Array(Decimal(33,4))  DEFAULT arrayWithConstant(rand(24) % 10, col04),
    col25 Array(Decimal32(1))   DEFAULT arrayWithConstant(rand(25) % 10, col05),
    col26 Array(Decimal32(7))   DEFAULT arrayWithConstant(rand(26) % 10, col06),
    col27 Array(Decimal64(1))   DEFAULT arrayWithConstant(rand(27) % 10, col07),
    col28 Array(Decimal64(15))  DEFAULT arrayWithConstant(rand(28) % 10, col08),
    col29 Array(Decimal128(1))  DEFAULT arrayWithConstant(rand(29) % 10, col09),
    col30 Array(Decimal128(30)) DEFAULT arrayWithConstant(rand(30) % 10, col10),

    col31 Array(Nullable(Decimal(2,1)))   DEFAULT arrayWithConstant(rand(31) % 10, col11),
    col32 Array(Nullable(Decimal(10,2)))  DEFAULT arrayWithConstant(rand(32) % 10, col12),
    col33 Array(Nullable(Decimal(19,3)))  DEFAULT arrayWithConstant(rand(33) % 10, col13),
    col34 Array(Nullable(Decimal(33,4)))  DEFAULT arrayWithConstant(rand(34) % 10, col14),
    col35 Array(Nullable(Decimal32(1)))   DEFAULT arrayWithConstant(rand(35) % 10, col15),
    col36 Array(Nullable(Decimal32(7)))   DEFAULT arrayWithConstant(rand(36) % 10, col16),
    col37 Array(Nullable(Decimal64(1)))   DEFAULT arrayWithConstant(rand(37) % 10, col17),
    col38 Array(Nullable(Decimal64(15)))  DEFAULT arrayWithConstant(rand(38) % 10, col18),
    col39 Array(Nullable(Decimal128(1)))  DEFAULT arrayWithConstant(rand(39) % 10, col19),
    col40 Array(Nullable(Decimal128(30))) DEFAULT arrayWithConstant(rand(40) % 10, col20)

) Engine=MergeTree ORDER BY tuple();


# https://github.com/minimaxir/big-list-of-naughty-strings/blob/master/blns.txt

DROP TABLE IF exists test_string_types;
CREATE TABLE test_string_types (
    number UInt64,
    col01 String DEFAULT transform( number % 16, range(16), ['','NULL','\\','\n','\0','ヽ༼ຈل͜ຈ༽ﾉ ヽ༼ຈل͜ຈ༽ﾉ','0','1','999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999','田中さんにあげて下さい','❤️ 💔 💌 💕 💞 💓 💗 💖 💘 💝 💟 💜 💛 💚 💙','Ṱ̺̺̕o͞ ̷i̲̬͇̪͙n̝̗͕v̟̜̘̦͟o̶̙̰̠kè͚̮̺̪̹̱̤ ̖t̝͕̳̣̻̪͞h̼͓̲̦̳̘̲e͇̣̰̦̬͎ ̢̼̻̱̘h͚͎͙̜̣̲ͅi̦̲̣̰̤v̻͍e̺̭̳̪̰-m̢iͅn̖̺̞̲̯̰d̵̼̟͙̩̼̘̳ ̞̥̱̳̭r̛̗̘e͙p͠r̼̞̻̭̗e̺̠̣͟s̘͇̳͍̝͉e͉̥̯̞̲͚̬͜ǹ̬͎͎̟̖͇̤t͍̬̤͓̼̭͘ͅi̪̱n͠g̴͉ ͏͉ͅc̬̟h͡a̫̻̯͘o̫̟̖͍̙̝͉s̗̦̲.̨̹͈̣', '𝐓𝐡𝐞 𝐪𝐮𝐢𝐜𝐤 𝐛𝐫𝐨𝐰𝐧 𝐟𝐨𝐱 𝐣𝐮𝐦𝐩𝐬 𝐨𝐯𝐞𝐫 𝐭𝐡𝐞 𝐥𝐚𝐳𝐲 𝐝𝐨𝐠','../../../../../../../../../../../etc/hosts','Roses are red, violets are blue. Hope you enjoy terminal hue',repeat('x',4096+1)], ''),
    col02 FixedString(4) DEFAULT substring(col01,1,4),
    col03 FixedString(129) DEFAULT substring(col01,1,129),
    col04 LowCardinality(String) DEFAULT col01,
    col05 LowCardinality(FixedString(4)) DEFAULT col02,
    col06 LowCardinality(FixedString(129)) DEFAULT col03,

    col11 Nullable(String)                           DEFAULT rand(11) % 100 > 50 ? col01 : NULL,
    col12 Nullable(FixedString(4))                   DEFAULT rand(12) % 100 > 50 ? col02 : NULL,
    col13 Nullable(FixedString(129))                 DEFAULT rand(13) % 100 > 50 ? col03 : NULL,
    col14 LowCardinality(Nullable(String))           DEFAULT rand(14) % 100 > 50 ? col04 : NULL,
    col15 LowCardinality(Nullable(FixedString(4)))   DEFAULT rand(15) % 100 > 50 ? col05 : NULL,
    col16 LowCardinality(Nullable(FixedString(129))) DEFAULT rand(16) % 100 > 50 ? col06 : NULL,

    col21 Array(String)                           DEFAULT arrayWithConstant(rand(21) % 10, col01),
    col22 Array(FixedString(4))                   DEFAULT arrayWithConstant(rand(22) % 10, col02),
    col23 Array(FixedString(129))                 DEFAULT arrayWithConstant(rand(23) % 10, col03),
    col24 Array(LowCardinality(String))           DEFAULT arrayWithConstant(rand(24) % 10, col04),
    col25 Array(LowCardinality(FixedString(4)))   DEFAULT arrayWithConstant(rand(25) % 10, col05),
    col26 Array(LowCardinality(FixedString(129))) DEFAULT arrayWithConstant(rand(26) % 10, col06),

    col31 Array(Nullable(String))                           DEFAULT arrayWithConstant(rand(31) % 10, col11),
    col32 Array(Nullable(FixedString(4)))                   DEFAULT arrayWithConstant(rand(32) % 10, col12),
    col33 Array(Nullable(FixedString(129)))                 DEFAULT arrayWithConstant(rand(33) % 10, col13),
    col34 Array(LowCardinality(Nullable(String)))           DEFAULT arrayWithConstant(rand(34) % 10, col14),
    col35 Array(LowCardinality(Nullable(FixedString(4))))   DEFAULT arrayWithConstant(rand(35) % 10, col15),
    col36 Array(LowCardinality(Nullable(FixedString(129)))) DEFAULT arrayWithConstant(rand(36) % 10, col16)

) Engine=MergeTree ORDER BY tuple();


DROP TABLE IF EXISTS test_int_types;
CREATE TABLE test_int_types (
    number UInt64,
    col01 UInt8 DEFAULT  toUInt8(rand(1)),
    col02 UInt16 DEFAULT toUInt16(rand(2)),
    col03 UInt32 DEFAULT rand(3),
    col04 UInt64 DEFAULT rand64(4),
    col05 Int8 DEFAULT   toInt8(rand(5)),
    col06 Int16 DEFAULT  toInt16(rand(6)),
    col07 Int32 DEFAULT  toInt32(rand(7)),
    col08 Int64 DEFAULT  rand64(8),

    col11 Nullable(UInt8)  DEFAULT rand(11) % 100 > 50 ? col01 : NULL,
    col12 Nullable(UInt16) DEFAULT rand(12) % 100 > 50 ? col02 : NULL,
    col13 Nullable(UInt32) DEFAULT rand(13) % 100 > 50 ? col03 : NULL,
    col14 Nullable(UInt64) DEFAULT rand(14) % 100 > 50 ? col04 : NULL,
    col15 Nullable(Int8)  DEFAULT  rand(15) % 100 > 50 ? col05 : NULL,
    col16 Nullable(Int16) DEFAULT  rand(16) % 100 > 50 ? col06 : NULL,
    col17 Nullable(Int32) DEFAULT  rand(17) % 100 > 50 ? col07 : NULL,
    col18 Nullable(Int64) DEFAULT  rand(18) % 100 > 50 ? col08 : NULL,

    col21 Array(UInt8)  DEFAULT arrayWithConstant(rand(21) % 10, col01),
    col22 Array(UInt16) DEFAULT arrayWithConstant(rand(22) % 10, col02),
    col23 Array(UInt32) DEFAULT arrayWithConstant(rand(23) % 10, col03),
    col24 Array(UInt64) DEFAULT arrayWithConstant(rand(24) % 10, col04),
    col25 Array(Int8)   DEFAULT arrayWithConstant(rand(25) % 10, col05),
    col26 Array(Int16)  DEFAULT arrayWithConstant(rand(26) % 10, col06),
    col27 Array(Int32)  DEFAULT arrayWithConstant(rand(27) % 10, col07),
    col28 Array(Int64)  DEFAULT arrayWithConstant(rand(28) % 10, col08),

    col21 Array(Nullable(UInt8))  DEFAULT arrayWithConstant(rand(21) % 10, col11),
    col22 Array(Nullable(UInt16)) DEFAULT arrayWithConstant(rand(22) % 10, col12),
    col23 Array(Nullable(UInt32)) DEFAULT arrayWithConstant(rand(23) % 10, col13),
    col24 Array(Nullable(UInt64)) DEFAULT arrayWithConstant(rand(24) % 10, col14),
    col25 Array(Nullable(Int8))   DEFAULT arrayWithConstant(rand(25) % 10, col15),
    col26 Array(Nullable(Int16))  DEFAULT arrayWithConstant(rand(26) % 10, col16),
    col27 Array(Nullable(Int32))  DEFAULT arrayWithConstant(rand(27) % 10, col17),
    col28 Array(Nullable(Int64))  DEFAULT arrayWithConstant(rand(28) % 10, col18)
) Engine=MergeTree ORDER BY tuple();


insert into test_decimal_types(number) SELECT * from numbers(1000000);
INSERT INTO test_string_types(number) SELECT * FROM numbers(1000000);
INSERT INTO test_int_types(number) SELECT * FROM numbers(1000000);


# 1) we expect all simple types properly matched to corresponding clickhouse types
# 2) nulls should be preserved.
# 3) arrays (?) - is there some option in ODBC to pass arrays?
# 4) all 'strange' types (like UUID / IPv4 / IPv6 / Tuple / Array(Array( ... )) may be passed somehow (string is rather ok), should not be broken
DESC odbc('DSN={ClickHouse DSN (Unicode)}','default','test_int_types'); 

## we can compare roundtrip row-by-row using join
SELECT * FROM default.test_string_types LEFT JOIN ( select toUInt64(number) as number, col1, col2, col3, col4 from odbc('DSN={ClickHouse DSN (ANSI)}','default','test_string_types') ) x
USING (number) WHERE col1 <> x.col1 OR col2 <> x.col2 OR col3 <> x.col3 OR col4 <> x.col4;



## You can measure the benchmark / find bottlenecks by executing queries like those:
SELECT count(*) FROM ( select col03 from odbc('DSN={ClickHouse DSN (ANSI)}','default','test_decimal_types') );
SELECT count(*) FROM ( select col03 from odbc('DSN={ClickHouse DSN (Unicode)}','default','test_decimal_types') );
SELECT count(*) FROM ( select col03 from default.test_decimal_types );