Clickhouse JSON vs Map

inspired by ClickHouse/ClickHouse#100960

CREATE TABLE test_JSON_max_dynamic_paths0                    
(                                                                    
    `timestamp` DateTime64(3, 'Etc/UTC') CODEC(DoubleDelta, ZSTD(1)),
    `host` LowCardinality(String) CODEC(ZSTD(1)),                    
    `program` LowCardinality(String) CODEC(ZSTD(1)),                 
    `platform` LowCardinality(String) CODEC(ZSTD(1)),                
    `environment` LowCardinality(String) CODEC(ZSTD(1)),             
    `level` LowCardinality(String) CODEC(ZSTD(1)),                   
    `owner` LowCardinality(String) CODEC(ZSTD(1)),                   
    `location` LowCardinality(String) CODEC(ZSTD(1)),                
    `filename` String CODEC(ZSTD(1)),                                
    `message` String CODEC(ZSTD(1)),                                 
    document JSON(max_dynamic_paths=0)                               
)                                                                    
ENGINE = MergeTree
PARTITION BY toDate(timestamp)                                       
PRIMARY KEY (location, program, toDateTime(timestamp))               
ORDER BY (location, program, toDateTime(timestamp), timestamp);


insert into test_JSON_max_dynamic_paths0(timestamp, document) 
select toDateTime('2020-01-01')+number/100 t, arrayReduce('sumMap', [arrayMap( x -> tuple('key'||cityHash64(x*(number+1))%2000, cityHash64(number*x+1)%25555::Int64), range(50))::Map(String,Int64)])::JSON
from numbers(1e5);

100000 rows in set. Elapsed: 46.933 sec.

optimize table test_JSON_max_dynamic_paths0 final;



CREATE TABLE test_JSON_max_dynamic_paths              
(                                                                    
    `timestamp` DateTime64(3, 'Etc/UTC') CODEC(DoubleDelta, ZSTD(1)),
    `host` LowCardinality(String) CODEC(ZSTD(1)),                    
    `program` LowCardinality(String) CODEC(ZSTD(1)),                 
    `platform` LowCardinality(String) CODEC(ZSTD(1)),                
    `environment` LowCardinality(String) CODEC(ZSTD(1)),             
    `level` LowCardinality(String) CODEC(ZSTD(1)),                   
    `owner` LowCardinality(String) CODEC(ZSTD(1)),                   
    `location` LowCardinality(String) CODEC(ZSTD(1)),                
    `filename` String CODEC(ZSTD(1)),                                
    `message` String CODEC(ZSTD(1)),                                 
    document JSON                           
)                                                                    
ENGINE = MergeTree
PARTITION BY toDate(timestamp)                                       
PRIMARY KEY (location, program, toDateTime(timestamp))               
ORDER BY (location, program, toDateTime(timestamp), timestamp);

insert into test_JSON_max_dynamic_paths(timestamp, document) 
select toDateTime('2020-01-01')+number/100 t, arrayReduce('sumMap', [arrayMap( x -> tuple('key'||cityHash64(x*(number+1))%2000, cityHash64(number*x+1)%25555::Int64), range(50))::Map(String,Int64)])::JSON
from numbers(1e5);

100000 rows in set. Elapsed: 18.241 sec.

optimize table test_JSON_max_dynamic_paths final;

CREATE TABLE test_JSON_max_dynamic_paths_min_bytes_for_wide_part            
(                                                                    
    `timestamp` DateTime64(3, 'Etc/UTC') CODEC(DoubleDelta, ZSTD(1)),
    `host` LowCardinality(String) CODEC(ZSTD(1)),                    
    `program` LowCardinality(String) CODEC(ZSTD(1)),                 
    `platform` LowCardinality(String) CODEC(ZSTD(1)),                
    `environment` LowCardinality(String) CODEC(ZSTD(1)),             
    `level` LowCardinality(String) CODEC(ZSTD(1)),                   
    `owner` LowCardinality(String) CODEC(ZSTD(1)),                   
    `location` LowCardinality(String) CODEC(ZSTD(1)),                
    `filename` String CODEC(ZSTD(1)),                                
    `message` String CODEC(ZSTD(1)),                                 
    document JSON                      
)                                                                    
ENGINE = MergeTree
PARTITION BY toDate(timestamp)                                       
PRIMARY KEY (location, program, toDateTime(timestamp))               
ORDER BY (location, program, toDateTime(timestamp), timestamp)
SETTINGS min_bytes_for_wide_part = 2004857600;

insert into test_JSON_max_dynamic_paths_min_bytes_for_wide_part(timestamp, document) 
select toDateTime('2020-01-01')+number/100 t, arrayReduce('sumMap', [arrayMap( x -> tuple('key'||cityHash64(x*(number+1))%2000, cityHash64(number*x+1)%25555::Int64), range(50))::Map(String,Int64)])::JSON
from numbers(1e5);

100000 rows in set. Elapsed: 14.822 sec.

optimize table test_JSON_max_dynamic_paths_min_bytes_for_wide_part final;

CREATE TABLE test_map            
(                                                                    
    `timestamp` DateTime64(3, 'Etc/UTC') CODEC(DoubleDelta, ZSTD(1)),
    `host` LowCardinality(String) CODEC(ZSTD(1)),                    
    `program` LowCardinality(String) CODEC(ZSTD(1)),                 
    `platform` LowCardinality(String) CODEC(ZSTD(1)),                
    `environment` LowCardinality(String) CODEC(ZSTD(1)),             
    `level` LowCardinality(String) CODEC(ZSTD(1)),                   
    `owner` LowCardinality(String) CODEC(ZSTD(1)),                   
    `location` LowCardinality(String) CODEC(ZSTD(1)),                
    `filename` String CODEC(ZSTD(1)),                                
    `message` String CODEC(ZSTD(1)),                                 
    document Map(String,Int64)                   
)                                                                    
ENGINE = MergeTree
PARTITION BY toDate(timestamp)                                       
PRIMARY KEY (location, program, toDateTime(timestamp))               
ORDER BY (location, program, toDateTime(timestamp), timestamp);

insert into test_map(timestamp, document) 
select toDateTime('2020-01-01')+number/100 t, arrayReduce('sumMap', [arrayMap( x -> tuple('key'||cityHash64(x*(number+1))%2000, cityHash64(number*x+1)%25555::Int64), range(50))::Map(String,Int64)])
from numbers(1e5);

100000 rows in set. Elapsed: 2.253 sec

optimize table test_map final;


CREATE TABLE test_map_with_buckets            
(                                                                    
    `timestamp` DateTime64(3, 'Etc/UTC') CODEC(DoubleDelta, ZSTD(1)),
    `host` LowCardinality(String) CODEC(ZSTD(1)),                    
    `program` LowCardinality(String) CODEC(ZSTD(1)),                 
    `platform` LowCardinality(String) CODEC(ZSTD(1)),                
    `environment` LowCardinality(String) CODEC(ZSTD(1)),             
    `level` LowCardinality(String) CODEC(ZSTD(1)),                   
    `owner` LowCardinality(String) CODEC(ZSTD(1)),                   
    `location` LowCardinality(String) CODEC(ZSTD(1)),                
    `filename` String CODEC(ZSTD(1)),                                
    `message` String CODEC(ZSTD(1)),                                 
    document Map(String,Int64)                   
)                                                                    
ENGINE = MergeTree
PARTITION BY toDate(timestamp)                                       
PRIMARY KEY (location, program, toDateTime(timestamp))               
ORDER BY (location, program, toDateTime(timestamp), timestamp)
SETTINGS map_serialization_version = 'with_buckets',
    max_buckets_in_map = 64,
    map_buckets_strategy = 'sqrt';


insert into test_map_with_buckets(timestamp, document) 
select toDateTime('2020-01-01')+number/100 t, arrayReduce('sumMap', [arrayMap( x -> tuple('key'||cityHash64(x*(number+1))%2000, cityHash64(number*x+1)%25555::Int64), range(50))::Map(String,Int64)])
from numbers(1e5);

100000 rows in set. Elapsed: 2.235 sec.

optimize table test_map_with_buckets final;


CREATE TABLE test_map_with_buckets_constant            
(                                                                    
    `timestamp` DateTime64(3, 'Etc/UTC') CODEC(DoubleDelta, ZSTD(1)),
    `host` LowCardinality(String) CODEC(ZSTD(1)),                    
    `program` LowCardinality(String) CODEC(ZSTD(1)),                 
    `platform` LowCardinality(String) CODEC(ZSTD(1)),                
    `environment` LowCardinality(String) CODEC(ZSTD(1)),             
    `level` LowCardinality(String) CODEC(ZSTD(1)),                   
    `owner` LowCardinality(String) CODEC(ZSTD(1)),                   
    `location` LowCardinality(String) CODEC(ZSTD(1)),                
    `filename` String CODEC(ZSTD(1)),                                
    `message` String CODEC(ZSTD(1)),                                 
    document Map(String,Int64)                   
)                                                                    
ENGINE = MergeTree
PARTITION BY toDate(timestamp)                                       
PRIMARY KEY (location, program, toDateTime(timestamp))               
ORDER BY (location, program, toDateTime(timestamp), timestamp)
SETTINGS map_serialization_version = 'with_buckets',
    max_buckets_in_map = 64,
    map_buckets_strategy = 'constant';

insert into test_map_with_buckets_constant(timestamp, document) 
select toDateTime('2020-01-01')+number/100 t, arrayReduce('sumMap', [arrayMap( x -> tuple('key'||cityHash64(x*(number+1))%2000, cityHash64(number*x+1)%25555::Int64), range(50))::Map(String,Int64)])
from numbers(1e5);
100000 rows in set. Elapsed: 2.278 sec.

optimize table test_map_with_buckets_constant final;

select table, part_type, rows, files, formatReadableSize(bytes_on_disk) size,
   formatReadableSize(data_uncompressed_bytes) uncomp_size 
from system.parts 
where table like 'test%' and database = 'default' and active 
order by files;
┌─table───────────────────────────────────────────────┬─part_type─┬───rows─┬─files─┬─size──────┬─uncomp_size─┐
│ test_JSON_max_dynamic_paths_min_bytes_for_wide_part │ Compact   │ 100000 │     7 │ 60.05 MiB │ 288.51 MiB  │
│ test_map                                            │ Wide      │ 100000 │    55 │ 37.82 MiB │ 107.98 MiB  │
│ test_map_with_buckets                               │ Wide      │ 100000 │   105 │ 41.79 MiB │ 112.56 MiB  │
│ test_JSON_max_dynamic_paths0                        │ Wide      │ 100000 │   241 │ 39.77 MiB │ 108.10 MiB  │
│ test_map_with_buckets_constant                      │ Wide      │ 100000 │   561 │ 51.50 MiB │ 156.05 MiB  │
│ test_JSON_max_dynamic_paths                         │ Wide      │ 100000 │ 10229 │ 60.90 MiB │ 290.63 MiB  │
└─────────────────────────────────────────────────────┴───────────┴────────┴───────┴───────────┴─────────────┘


select sum(document['key555']) from test_map_with_buckets_constant;
--
32083846
1 row in set. Elapsed: 0.008 sec. Processed 100.00 thousand rows, 800.00 KB (12.97 million rows/s., 103.79 MB/s.)
Peak memory usage: 1.55 MiB.

select sum(document['key555']) from test_map_with_buckets;
--
32083846
1 row in set. Elapsed: 0.024 sec. Processed 100.00 thousand rows, 800.00 KB (4.10 million rows/s., 32.80 MB/s.)
Peak memory usage: 4.25 MiB.

select sum(document['key555']) from test_map;
--
32083846
1 row in set. Elapsed: 0.117 sec. Processed 100.00 thousand rows, 800.00 KB (858.13 thousand rows/s., 6.87 MB/s.)
Peak memory usage: 18.80 MiB.

select sum(document.key555.:Int64) from test_JSON_max_dynamic_paths0;
--
32083846
1 row in set. Elapsed: 0.012 sec. Processed 100.00 thousand rows, 900.00 KB (8.57 million rows/s., 77.10 MB/s.)
Peak memory usage: 1.22 MiB.

select sum(document.key555.:Int64) from test_JSON_max_dynamic_paths;
--
32083846
1 row in set. Elapsed: 0.005 sec. Processed 100.00 thousand rows, 900.00 KB (21.16 million rows/s., 190.42 MB/s.)
Peak memory usage: 1.14 MiB.

select sum(document.key555.:Int64) from test_JSON_max_dynamic_paths_min_bytes_for_wide_part;
--
32083846
1 row in set. Elapsed: 0.040 sec. Processed 100.00 thousand rows, 900.00 KB (2.48 million rows/s., 22.32 MB/s.)
Peak memory usage: 5.17 MiB.

den-crane/Clickhouse_JSONvsMap.md

Select an option

No results found

Select an option

No results found