filimonov

eb90336ba886 :) select * from (select number, intDiv(number,5) value from numbers(20)) order by value;

SELECT *
FROM 
(
    SELECT 
        number, 
        intDiv(number, 5) AS value
    FROM numbers(20)
)

filimonov

https://clickhouse.github.io/clickhouse-presentations/meetup30/new_features/#10



select number, rand() as val1, 'xxx'||toString(number) as val3, 'zzz' as const_col  from numbers(10) where number%3 = 1;

SELECT 
    number, 
    rand() AS val1, 
    concat('xxx', toString(number)) AS val3, 

filimonov

#!/usr/bin/env python
# based on https://unix.stackexchange.com/questions/359832/converting-csv-to-tsv 
# usage: zcat file.ssv.gz | csv2tab | clickhouse-client -q 'insert into ... format TSV'

import csv, sys
csv.register_dialect('clickhouse', escapechar="\\", doublequote=0, quotechar='\'',skipinitialspace = 0,delimiter   = '\t', quoting=csv.QUOTE_NONE, lineterminator='\n')

# docs: https://docs.python.org/2.7/library/csv.html 
csv.writer(sys.stdout, dialect='clickhouse').writerows(csv.reader(sys.stdin, delimiter=';'))

filimonov

define perl_stack
    set $end=my_perl->Icurstackinfo->si_cxix
    set $i=0
    while ($i<$end)
        printf "%d:%s\n", my_perl->Icurstackinfo->si_cxstack[$i].cx_u.cx_blk.blku_oldcop->cop_line, my_perl->Icurstackinfo->si_cxstack[$i].cx_u.cx_blk.blku_oldcop->cop_file
        set $i=$i+1
    end
    printf "%d:%s\n",my_perl->Icurcop->cop_line, my_perl->Icurcop->cop_file
end

filimonov

Determining if the pthread_create exist failed with the following output:
Change Dir: /home/centos/clickhouse-rpm/rpmbuild/BUILD/ClickHouse-19.14.6.12-stable/build/CMakeFiles/CMakeTmp

Run Build Command(s):/usr/bin/gmake cmTC_1e6f6/fast 
/usr/bin/gmake -f CMakeFiles/cmTC_1e6f6.dir/build.make CMakeFiles/cmTC_1e6f6.dir/build
gmake[1]: Entering directory `/home/centos/clickhouse-rpm/rpmbuild/BUILD/ClickHouse-19.14.6.12-stable/build/CMakeFiles/CMakeTmp'
Building C object CMakeFiles/cmTC_1e6f6.dir/CheckSymbolExists.c.o
/opt/rh/devtoolset-8/root/usr/bin/gcc   -msse4.1 -msse4.2 -mpopcnt -fno-omit-frame-pointer  -Wall     -o CMakeFiles/cmTC_1e6f6.dir/CheckSymbolExists.c.o   -c /home/centos/clickhouse-rpm/rpmbuild/BUILD/ClickHouse-19.14.6.12-stable/build/CMakeFiles/CMakeTmp/CheckSymbolExists.c
Linking C executable cmTC_1e6f6
/usr/bin/cmake3 -E cmake_link_script CMakeFiles/cmTC_1e6f6.dir/link.txt --verbose=1

filimonov

Thread 1 (Thread 0x7f69772f9700 (LWP 17301)):
#0  0x0000000000000002 in ?? ()
#1  0x0000000005e01948 in DB::ColumnNullable::checkConsistency (this=this@entry=0x7f6903e51840) at /usr/src/debug/ClickHouse-19.11.8.46-stable/dbms/src/Columns/ColumnNullable.cpp:450
#2  0x0000000005bfcf0c in DB::DataTypeNullable::serializeBinaryBulkWithMultipleStreams (this=0x7f69cc61e810, column=..., offset=0, limit=8192, settings=..., state=std::shared_ptr (empty) 0x0) at /usr/src/debug/ClickHouse-19.11.8.46-stable/dbms/src/DataTypes/DataTypeNullable.cpp:88
#3  0x0000000006398029 in writeSingleGranule (write_marks=<optimized out>, number_of_rows=8192, from_row=<optimized out>, serialize_settings=..., serialization_state=std::shared_ptr (empty) 0x0, skip_offsets=<optimized out>, offset_columns=std::set with 0 elements, column=..., type=..., name="username", this=0x7f69772ef7d0) at /usr/src/debug/ClickHouse-19.11.8.46-stable/dbms/src/Storages/MergeTree/IMergedBlockOutputStream.cpp:195
#4  DB::IMergedBlockOutputStream::writeColumn (

filimonov

CREATE TABLE words ENGINE = Join(ANY,LEFT,id) AS SELECT rowNumberInAllBlocks() AS id, * FROM url('http://rawcdn.githack.com/dwyl/english-words/54b470a763d3df98ec33cb049382711972975317/words.txt', 'TSV', 'word String');
select arrayMap( i -> joinGet( 'default.words', 'word', toUInt64( rand(i) % 466551)), range(10));

filimonov

create table delete_test Engine=MergeTree() ORDER BY id AS SELECT number as id from numbers(1000000);

SELECT count() FROM delete_test;
SELECT * FROM delete_test where id<10;

/*  mutations are executed asyncroniously, so you may need to wait a bit... */ 

alter table delete_test delete where id in (1,2,3,5,7);

SELECT count() FROM delete_test;

filimonov

#!/bin/bash
input="test.sql"
curl_config="/tmp/curl_config_file.txt"

echo > $curl_config
while IFS= read -r line
do
    echo "next" >> $curl_config
    echo "url=http://192.168.1.129:8123/?user=test&password=test&default_format=Null" >> $curl_config
    echo "data=\"${line}\""  >> $curl_config

filimonov

<yandex>
    <remote_servers>
        <test_cluster>
            <shard>
                <internal_replication>false</internal_replication>
                <replica>
                    <host>ch1</host>
                    <port>9000</port>
                </replica>
                <replica>