messing_around.sql

7b701b64404c :) create table test_null_storage (pkey Int64, key Int64, floatColumn Float64, floatColumnNull Nullable(Float64), arrayColumn Array(Int64)) ENGINE = MergeTree() ORDER BY key PARTITION BY pkey;

CREATE TABLE test_null_storage
(
    `pkey` Int64, 
    `key` Int64, 
    `floatColumn` Float64, 
    `floatColumnNull` Nullable(Float64), 
    `arrayColumn` Array(Int64)
)
ENGINE = MergeTree()
PARTITION BY pkey
ORDER BY key

Ok.

0 rows in set. Elapsed: 0.120 sec.

7b701b64404c :) insert into test_null_storage select 1, number, toFloat64(number), NULL, [] from system.numbers limit 100000

INSERT INTO test_null_storage SELECT 
    1, 
    number, 
    toFloat64(number), 
    NULL, 
    []
FROM system.numbers
LIMIT 100000

↙ Progress: 131.07 thousand rows, 1.05 MB (4.13 million rows/s., 33.07 MB/s.) Ok.

0 rows in set. Elapsed: 0.032 sec. Processed 131.07 thousand rows, 1.05 MB (4.12 million rows/s., 32.99 MB/s.) 

# Distinct values in floatColumn

7b701b64404c :) select table, name, data_uncompressed_bytes, data_compressed_bytes from system.columns where table = 'test_null_storage'

SELECT 
    table, 
    name, 
    data_uncompressed_bytes, 
    data_compressed_bytes
FROM system.columns
WHERE table = 'test_null_storage'

┌─table─────────────┬─name────────────┬─data_uncompressed_bytes─┬─data_compressed_bytes─┐
│ test_null_storage │ pkey            │                  800000 │                  3645 │
│ test_null_storage │ key             │                  800000 │                400518 │
│ test_null_storage │ floatColumn     │                  800000 │                400580 │
│ test_null_storage │ floatColumnNull │                  900000 │                  4056 │
│ test_null_storage │ arrayColumn     │                  800000 │                  3593 │
└───────────────────┴─────────────────┴─────────────────────────┴───────────────────────┘

5 rows in set. Elapsed: 0.013 sec.

7b701b64404c :) truncate table test_null_storage;

TRUNCATE TABLE test_null_storage

Ok.

# All 0.0 in floatColumn

7b701b64404c :) select table, name, data_uncompressed_bytes, data_compressed_bytes from system.columns where table = 'test_null_storage'

SELECT 
    table, 
    name, 
    data_uncompressed_bytes, 
    data_compressed_bytes
FROM system.columns
WHERE table = 'test_null_storage'

┌─table─────────────┬─name────────────┬─data_uncompressed_bytes─┬─data_compressed_bytes─┐
│ test_null_storage │ pkey            │                  800000 │                  3645 │
│ test_null_storage │ key             │                  800000 │                400518 │
│ test_null_storage │ floatColumn     │                  800000 │                  3593 │
│ test_null_storage │ floatColumnNull │                  900000 │                  4056 │
│ test_null_storage │ arrayColumn     │                  800000 │                  3593 │
└───────────────────┴─────────────────┴─────────────────────────┴───────────────────────┘


# 10M rows 
7b701b64404c :) insert into test_null_storage select 1, number, 0.0, NULL, [] from system.numbers limit 10000000;

INSERT INTO test_null_storage SELECT 
    1, 
    number, 
    0., 
    NULL, 
    []
FROM system.numbers
LIMIT 10000000

↘ Progress: 10.03 million rows, 80.22 MB (10.17 million rows/s., 81.35 MB/s.) Ok.

0 rows in set. Elapsed: 0.986 sec. Processed 10.03 million rows, 80.22 MB (10.17 million rows/s., 81.35 MB/s.) 

7b701b64404c :) select table, name, formatReadableSize(data_uncompressed_bytes), formatReadableSize(data_compressed_bytes) from system.columns where table = 'test_null_storage'

SELECT 
    table, 
    name, 
    formatReadableSize(data_uncompressed_bytes), 
    formatReadableSize(data_compressed_bytes)
FROM system.columns
WHERE table = 'test_null_storage'

┌─table─────────────┬─name────────────┬─formatReadableSize(data_uncompressed_bytes)─┬─formatReadableSize(data_compressed_bytes)─┐
│ test_null_storage │ pkey            │ 83.92 MiB                                   │ 388.19 KiB                                │
│ test_null_storage │ key             │ 83.92 MiB                                   │ 41.05 MiB                                 │
│ test_null_storage │ floatColumn     │ 83.92 MiB                                   │ 382.94 KiB                                │
│ test_null_storage │ floatColumnNull │ 94.41 MiB                                   │ 430.84 KiB                                │
│ test_null_storage │ arrayColumn     │ 83.92 MiB                                   │ 382.94 KiB                                │
└───────────────────┴─────────────────┴─────────────────────────────────────────────┴───────────────────────────────────────────┘

5 rows in set. Elapsed: 0.010 sec.