Skip to content

Instantly share code, notes, and snippets.

View filimonov's full-sized avatar

filimonov filimonov

74 followers · 4 following
View GitHub Profile
@filimonov
filimonov / clickhouse-client-sample.xml
Last active March 26, 2018 16:37
<!-- 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
filimonov / geoip2_bench.pl
Created April 4, 2018 15:48
perl geoip benchmark of particular parts
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
filimonov / geoip2_bench2.pl
Created April 11, 2018 12:33
GeoIP2::Database::Reader vs MaxMind::DB::Reader benchmark or why shouldn't you use GeoIP2::Database::Reader.
## 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
filimonov / sample.sql
Last active May 9, 2018 10:09
clickhouse partition key in system.parts : tuple vs simple type
-- 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
filimonov / prewhere.sql
Created May 14, 2018 12:58
Clickhouse prewhere demonstration
SELECT version()
┌─version()─┐
│ 1.1.54380 │
└───────────┘
SELECT *
FROM system.settings
WHERE name LIKE '%prewh%';
┌─name──────────────────────┬─value─┬─changed─┬─description───────────────────────────────────────────────────────────────────────┐
@filimonov
filimonov / csv2tsv.py
Last active July 10, 2018 13:24
Reformat CSV to clickhouse-compatible TSV
#!/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
filimonov / matview_block_size.sql
Last active September 20, 2018 11:16
clickhouse matview vs max_block_size
CREATE TABLE mat_vew_src
(
id UInt64,
number UInt64
)
ENGINE = MergeTree
PARTITION BY tuple()
ORDER BY id
-- Ok.
@filimonov
filimonov / gist:cd9c41f8eff1f0900312d90e51fbb081
Last active December 1, 2018 20:39
Allow creating symlinks in Windows 10 Home for users
1. Download https://gallery.technet.microsoft.com/scriptcenter/Grant-Revoke-Query-user-26e259b0#content
2. powershell as admin
3.
Import-Module .\UserRights.psm1
Grant-UserRight -Account "BUILTIN\Users" -Right SeCreateSymbolicLink
Get-AccountsWithUserRight -Right SeCreateSymbolicLink
Account Right SID
------- ----- ---
@filimonov
filimonov / list_partitions.sql
Created January 11, 2019 13:17
Create queries to detach all the partitions in clickhouse
select 'ALTER TABLE '||database||'.'||table||' DETACH PARTITION '||partition||';' from system.parts group by database,table,partition ORDER BY database, table, partition FORMAT TSVRaw;
@filimonov
filimonov / check_different_presets.sh
Last active April 15, 2019 12:02
Lua script for formatting output of wrk to csv
URL='http://127.0.0.1:8123/ping'
EXTRA=''
echo 'query,extra,concurrency,threads,time_started,min_req,max_req,mean_req,req_stdev,min_latency,max_latency,mean_latency,latency_stdev,50th,75th,90th,99th,99.999th,duration,requests,bytes,connect_errors,read_errors,write_errors,status_errors,timeouts' > result.csv
for CONCURRENCY in 1 2 4 8 16 32 64 128 256 512 768 1024 1280 1536 1792 2048
do
if (( $CONCURRENCY < 8 )); then
THREADS=$CONCURRENCY