Fail to write to a DFS table due to the error The column used for value-partitioning cannot contain NULL values

Question

How to solve the following error occurred when writing to a database:

Exception in writeCandlestick: 127.0.0.1:8503 Server response: 'The column used for value-partitioning cannot contain NULL values. RefId:S01001' script: 'tableInsert{loadTable('dfs://candle_db', 'candles')}'.

Answer 1

When creating a value-partitioned database, NULL values are not allowed in the partitioning column as the system cannot determine the partition to which NULL should be assigned. To solve this issue, you can write the data of the partitioning column to an in-memory table and examine whether it contains NULL values. Here is an example script:

t=table(7 8 9 as id, 1.1 1.2 NULL as val)
select * from t where val is null
// output
id val
-- ---
9