Strict mode (strict_mode) is a parameter configuration in load operation. This parameter will affect the load behavior of some values and the final loaded data.

This document mainly describes how to set strict mode and the impact of strict mode.

How to set

Default setting of strict mode is False, which means OFF status.

Different load methods have different ways to set strict mode.

1. BROKER LOAD

   LOAD LABEL example_db.label1
   (
       DATA INFILE("bos://my_bucket/input/file.txt")
       INTO TABLE `my_table`
       COLUMNS TERMINATED BY ","
   )
   WITH BROKER bos
   (
       "bos_endpoint" = "http://bj.bcebos.com",
       "bos_accesskey" = "xxxxxxxxxxxxxxxxxxxxxxxxxx",
       "bos_secret_accesskey"="yyyyyyyyyyyyyyyyyyyyyyyyyy"
   )
   PROPERTIES
   (
       "strict_mode" = "true"
   )

2. STREAM LOAD

   curl --location-trusted -u user:passwd \
   -H "strict_mode: true" \
   -T 1.txt \
   http://host:port/api/example_db/my_table/_stream_load

3. ROUTINE LOAD

   CREATE ROUTINE LOAD example_db.test_job ON my_table
   PROPERTIES
   (
       "strict_mode" = "true"
   ) 
   FROM KAFKA
   (
       "kafka_broker_list" = "broker1:9092,broker2:9092,broker3:9092",
       "kafka_topic" = "my_topic"
   );

4. INSERT

   Set through Session variables:

   SET enable_insert_strict = true;
   INSERT INTO my_table ...;

Functions of strict mode

Strict mode means to carry out strict filtering for column type converting during load.

The strategy of strict filtering is as follows:

For column type converting, the error data will be filtered if strict mode is started. The error data here refer to the kind of data whose original data are not null but whose result are null after column type converting.

"Column type converting" here does not include null value calculated by functions.

If the loaded column type contains range restriction and if the original data can pass the type converting normally but cannot pass the range restriction, the strict mode will not affect it. For example, if the type is decimal(1,0), and the original data is 10, then it belongs to the range that can pass the type converting but not to the column statement. This data can not be affect by strict mode.

1. Take column type Tinyint as an example:

   Original data type	original data example	Value converted to Tinyint	Strict mode	Results
   Null	\N	NULL	On or off	NULL
   Non-null	"abc" or 2000	NULL	On	Invalid value（filtered）
   Non-null	"abc"	NULL	Off	NULL
   Non-null	1	1	On or off	Correct load

   Notes:

   1. Null values are allowed to be loaded in table columns.
   2. Afterabc and 2000 are converted to TinyInt, they will become Null due to type or precision. These data will be filtered when strict mode is on. And null will be loaded if strict mode is off.

2. Take column type Decimal(1,0) as an example

   Original data type	original data example	Value converted to Decimal	Strict mode	Results
   Null	\N	null	On or off	NULL
   Non-null	aaa	NULL	On	Invalid value（filtered）
   Non-null	aaa	NULL	Off	NULL
   Non-null	1 or 10	1 or 10	On or off	Correct load

   Notes:

   1. Null values are allowed to be loaded in table columns.
   2. Afterabc is converted to Decimal, it will become Null due to the type. These data will be filtered when strict mode is on. And nullwill be loaded if strict mode is off.
   3. Although 10 is a value beyond the range, strict mode has no effect on it because its type meets the requirements of decimal, and 10 will be finally filtered in other load processes, but it will not filtered by strict mode.