CODEWITHSUNDEEP
clickhouseclickhouse-clientclickhouse-go
Hemanth Kumar N R
Hemanth Kumar N R

Reputation: 11

Clickhouse VALUE_IS_OUT_OF_RANGE_OF_DATA_TYPE for Nullable(Date) having null values for date columns

A column having the null dates in the ORC file, while trying to fetch using S3 engine, encountering with an exception regarding the retrieving the null value from the s3 ORC file.

When the same ORC file is converted to CSV file and tried to fetch the data, with CSV file it worked, that means the ORC file retrieving the having the issue.

Null for DateTime datatype is working - only for Date columns, it is not working.

On selecting the date columns having null from select null_date_column from s3('s3url','ORC') is having the issue.

Error trace:

Code: 321. DB::Exception: Received from localhost:9000. DB::Exception: Input value 1834119856 of a column "end_date" is out of allowed Date32 range, which is \[-25567, 120530\]: (in file/uri s3url1-617e75a8f3f0.c000.snappy.orc): While executing ORCBlockInputFormat: While executing S3(\_table_function.s3)Source. (VALUE_IS_OUT_OF_RANGE_OF_DATA_TYPE)`

I've tried by setting the below clickhouse configuration:

input_format_skip_unknown_fields = 1, input_format_null_as_default = 1, input_format_defaults_for_omitted_fields = 1,  input_format_allow_errors_num = 1000
input_format_allow_errors_ratio = 1 (100%);

Upvotes: 0

Views: 24

Answers (1)

Hemanth Kumar N R
Hemanth Kumar N R

Reputation: 11

Set the below configuration to retrieve the null values:

SET date_time_overflow_behavior = 'saturate'

Reference in Clickhouse docs - date_time_overflow_behavior

Upvotes: 1

Related Questions