clickhouse-server:20.5.4.40 修改用户配置允许替换null值为默认值

定义表格:

CREATE TABLE test
(
    `created_at` DateTime DEFAULT now(),
    `created_by` Nullable(Int64)
)ENGINE=TinyLog; 

插入数据:

insert into test ( created_at,created_by) values(null,null) 

在 clickhouse-server:21.9.4.35 版本，上述语句可以正常执行，插入数据：

03f1f51b76a9 :) select * from test;

SELECT *
FROM test

Query id: 4726bec5-4443-4b1f-bacf-bd60f69dde60

┌──────────created_at─┬─created_by─┐
│ 2021-11-03 02:18:34 │       ᴺᵁᴸᴸ │
└─────────────────────┴────────────┘

1 rows in set. Elapsed: 0.006 sec.

而在 clickhouse-server:20.5.4.40 执行则报错，没有插入默认值:

dd4f1dff9dd0 :) insert into test ( created_at,created_by) values(null,null)

INSERT INTO test (created_at, created_by) VALUES


Exception on client:
Code: 53. DB::Exception: Cannot insert NULL value into a column of type 'DateTime' at: null,null)

Connecting to localhost:9000 as user default.
Connected to ClickHouse server version 20.5.4 revision 54435.

原因是  用户属性 input_format_null_as_default 是自 Release 19.14.3.3, 2019-09-10 新建的属性，默认未启用，直到Release 21.1.7.1 2021-03-24 才设置默认启用。

对于Release 20.5.4.40，需要手工配置一下，生成newuser.xml文件，放到目录 /etc/clickhouse-server/users.d/ ：

<?xml version="1.0"?>
<yandex>
    <!-- Profiles of settings. -->
    <profiles>
        <!-- Default settings. -->
        <newuser>
            <!-- Maximum memory usage for processing single query, in bytes. -->
            <max_memory_usage>10000000000</max_memory_usage>
            <!-- Use cache of uncompressed blocks of data. Meaningful only for processing many of very short queries. -->
            <use_uncompressed_cache>0</use_uncompressed_cache>
            <load_balancing>random</load_balancing>
            <!--  Added by Henry 2021/11/03  -->
            <input_format_null_as_default>1</input_format_null_as_default>
        </newuser>
    </profiles>
    <!-- Users and ACL. -->
    <users>
        <newuser>
            <password>newuser567890</password>
            <networks incl="networks" replace="replace">
                <ip>::/0</ip>
            </networks>
            <!-- Settings profile for user. -->
            <profile>newuser</profile>
            <!-- Quota for user. -->
            <quota>default</quota>
        </newuser>
    </users>
</yandex>

clickhouse会监视此目录的，改动即时生效，可以检查文件：

/etc/clickhouse-server/preprocessed/users.xml

也可以用sql检查用户配置:

select * from system.settings where name = 'input_format_null_as_default'

dd4f1dff9dd0 :) select * from system.settings where name = 'input_format_null_as_default'

SELECT *
FROM system.settings
WHERE name = 'input_format_null_as_default'

┌─name─────────────────────────┬─value─┬─changed─┬─description──────────────────────────────────────────────────────────────────────────────────────────────────┬─min──┬─max──┬─readonly─┬─type────────┐
│ input_format_null_as_default │ 1     │       1 │ For text input formats initialize null fields with default values if data type of this field is not nullable │ ᴺᵁᴸᴸ │ ᴺᵁᴸᴸ │        0 │ SettingBool │
└──────────────────────────────┴───────┴─────────┴──────────────────────────────────────────────────────────────────────────────────────────────────────────────┴──────┴──────┴──────────┴─────────────┘

1 rows in set. Elapsed: 0.012 sec.

更简单的一点办法是临时用 clickhouse-client 的启动参数指定：

clickhouse-client --input_format_null_as_default=1