tencent cloud

Tencent Cloud TCHouse-D

Importing Strict Mode

PDF
Modo Foco
Tamanho da Fonte
Última atualização: 2024-06-27 11:06:00
The strict mode (strict_mode) is configured as a parameter in the import operation. This parameter will affect certain import behavior of certain values and the final imported data. This document mainly explains how to configure the strict pattern and the impact of the strict pattern.

How to Set

The strict mode is set to False by default, namely, OFF. Different import methods set strict mode in different ways.
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"
)
curl --location-trusted -u user:passwd \\
-H "strict_mode: true" \\
-T 1.txt \\
http://host:port/api/example_db/my_table/_stream_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 ...;

The Role of Strict Pattern

Restricting the filtering of column type conversion during the import. The strict filtering strategy is as follows:
For column type conversion, if the strict mode is enabled, incorrect data will be filtered. The erroneous data here refer to: the original data is not null, but result is null after the column type conversion.
The column type conversionreferred to here does not include the null value calculated by the function.
For the imported column type that contains range restrictions, if the original data can pass the type conversion normally, but cannot pass the range limit, the strict mode will not effect it. For example, if the type is decimal(1,0) and the original data is 10, it belongs to the range that can be converted by type but is not within the scope of the column declaration. This kind of data strict has no effect on it.
Take the column type as TinyInt for example:
Original data type
Examples of original data
Converted Value to TinyInt
Strict pattern
Result
Null value
\\N
NULL
Turn On or Off
NULL
Non-null Value
"abc" or 2000
NULL
Enabled
Illegal Value (Filtered)
Non-null Value
"abc"
NULL
Off
NULL
Non-null Value
1
1
Turn On or Off
Correct Import
Note
Columns in the table allow importing of null values.
abc and 2000 will become NULL after being converted to TinyInt due to type or precision issues. When strict mode is on, this data will be filtered. And if it is closed, null will be imported.
Take the column type of Decimal(1,0) as an example:
Original data type
Examples of original data
Value after converting to Decimal
Strict pattern
Result
Null value
\\N
null
Turn On or Off
NULL
Non-null Value
aaa
NULL
Enabled
Illegal Value (Filtered)
Non-null Value
aaa
NULL
Off
NULL
Non-null Value
1 or 10
1 or 10
Turn On or Off
Correct Import
Note
Columns in the table allow importing of null values.
abc will turn into NULL due to type issues after converting to Decimal. When strict pattern is enabled, this type of data will be filtered. Whereas if it is disabled, it will import null.
Even though 10 is a value that exceeds the range, since its type meets the requirements of decimal, the strict mode does not affect it. 10 will eventually be filtered in other import processing procedures. However, it won't be filtered by the strict mode.

Ajuda e Suporte

Esta página foi útil?

comentários