filimonov

# Tested on Centos 6.9 and Centos 7.4. ClickHouse vesion 1.1.54236
# Official build instructions:
# https://github.com/yandex/ClickHouse/blob/master/doc/build.md
# See also https://github.com/redsoftbiz/clickhouse-rpm
#      and https://github.com/Altinity/clickhouse-rpm

export THREADS=$(grep -c ^processor /proc/cpuinfo)

# Determine RHEL major version
RHEL_VERSION=`rpm -qa --queryformat '%{VERSION}\n' '(redhat|sl|slf|centos|oraclelinux|goslinux)-release(|-server|-workstation|-client|-computenode)'`

filimonov

<!-- Put it to ~/.clickhouse-client/config.xml -->
<yandex>
    <config-file></config-file>
    <stacktrace>false<stacktrace>
    <format>Vertical</format>
    <format_max_block_size>10000</format_max_block_size>
    <insert_format_max_block_size>100000</insert_format_max_block_size>
    <progress>false</progress>
    <echo>false</echo>
    <use_client_time_zone>false</use_client_time_zone>

filimonov

set -o errexit
TABLE_NAME="${1:?You must pass a TABLE_NAME as first argument}"
STARTID="${2:?You must pass a STARTID as 2nd argument}"
ENDID="${3:?You must pass a ENDID as 3rd argument}"
[[ -z "$4" ]] && LIMIT="" || LIMIT="LIMIT $4"

. logins.sh

INSERT_COMMAND="INSERT INTO clickhouse_table(column1,column2,...) FORMAT TSV"

filimonov

use strict;
use warnings;
use Time::HiRes;

use GeoIP2::Database::Reader;

my $reader = GeoIP2::Database::Reader->new(
      file    => '/usr/local/www/maxminddb/GeoIP2-City.mmdb',
      locales => [ 'en' ]
);

filimonov

## GeoIP2::Database::Reader vs MaxMind::DB::Reader benchmark or 
## why you shouldn't use GeoIP2::Database::Reader

use Benchmark (cmpthese);

use strict;
use warnings;

use GeoIP2::Database::Reader;
use MaxMind::DB::Reader;

filimonov

-- adding braces around partition name (even for simple types) allows to do cast safely
:) create table xxx(d Date, a UInt8) Engine = MergeTree() PARTITION by d ORDER BY a;

CREATE TABLE xxx
(
    d Date, 
    a UInt8
)
ENGINE = MergeTree()
PARTITION BY d

filimonov

SELECT version()
┌─version()─┐
│ 1.1.54380 │
└───────────┘

SELECT *
FROM system.settings 
WHERE name LIKE '%prewh%';

┌─name──────────────────────┬─value─┬─changed─┬─description───────────────────────────────────────────────────────────────────────┐

filimonov

DROP TABLE IF EXISTS test;

CREATE TABLE test (sessionid String, timestamp DateTime) ENGINE = Log;

INSERT INTO test SELECT 's1', arrayJoin([now(), now() + 10, now() + 21, now() + 33]);
INSERT INTO test SELECT 's2', arrayJoin([now()+33, now() + 34, now() + 35, now() + 36]);
INSERT INTO test SELECT 's3', arrayJoin([now()+40, now() + 60, now() + 61, now() + 80]);

WITH
  groupArray( timestamp ) as all_timestamps_in_group,

filimonov

#!/usr/bin/env python
import csv, sys
csv.register_dialect('clickhouse', escapechar="\\", doublequote=0, quotechar='\'',skipinitialspace = 0,delimiter   = '\t', quoting=csv.QUOTE_NONE, lineterminator='\n')
csv.writer(sys.stdout, dialect='clickhouse').writerows(csv.reader(sys.stdin))

# install:
#  put in $PATH
#  chmod +x csv2tsv.py

# usage:

filimonov

<?xml version="1.0"?>
<yandex>

    <mysql_config>
        <port>3306</port>
        <user>user</user>
        <password>123</password>
        <replica>
            <host>mysql_host</host>
            <priority>1</priority>