- Product Introduction
- Purchase Guide
- Getting Started
- Operation Guide
- Development Guide
- Design of Data Table
- Importing Data
- Import Overview
- Import Methods
- Synchronizing Data with JDBC
- Synchronizing Data Through External Tables
- Real-time or Batch Writing of MySQL Data
- Importing Data from Kafka
- Importing with DataX
- Importing Doris from Logstash
- Data Transformation, Column Mapping and Filtering
- Importing Strict Mode
- Importing Data in JSON Format
- Exporting Data
- Basic Feature
- Query Optimization
- Ecological Expansion Feature
- API Documentation
- History
- Introduction
- API Category
- Making API Requests
- Cluster Operation APIs
- ModifyClusterConfigs
- ModifyInstanceKeyValConfigs
- ModifyNodeStatus
- RestartClusterForConfigs
- RestartClusterForNode
- CreateInstanceNew
- DestroyInstance
- ModifySecurityGroups
- ReduceInstance
- ResizeDisk
- ScaleOutInstance
- ScaleUpInstance
- DescribeClusterConfigsHistory
- ModifyInstance
- DescribeCreateTablesDDL
- DescribeInstanceOperationHistory
- Database and Table APIs
- Cluster Information Viewing APIs
- Hot-Cold Data Layering APIs
- Database and Operation Audit APIs
- CancelBackupJob
- CreateBackUpSchedule
- DeleteBackUpData
- DescribeBackUpJob
- DescribeBackUpJobDetail
- DescribeBackUpTables
- DescribeBackUpTaskDetail
- DescribeRestoreTaskDetail
- DescribeBackUpSchedules
- RecoverBackUpJob
- DescribeInstanceOperations
- DescribeDatabaseAuditDownload
- DescribeDatabaseAuditRecords
- DescribeSlowQueryRecords
- DescribeSlowQueryRecordsDownload
- DescribeQueryAnalyse
- User and Permission APIs
- Resource Group Management APIs
- Data Types
- Error Codes
- Cloud Ecosystem
- Practical Tutorial
- Performance Testing
- FAQs
- Contact Us
- Glossary
- Product Policy
- Product Introduction
- Purchase Guide
- Getting Started
- Operation Guide
- Development Guide
- Design of Data Table
- Importing Data
- Import Overview
- Import Methods
- Synchronizing Data with JDBC
- Synchronizing Data Through External Tables
- Real-time or Batch Writing of MySQL Data
- Importing Data from Kafka
- Importing with DataX
- Importing Doris from Logstash
- Data Transformation, Column Mapping and Filtering
- Importing Strict Mode
- Importing Data in JSON Format
- Exporting Data
- Basic Feature
- Query Optimization
- Ecological Expansion Feature
- API Documentation
- History
- Introduction
- API Category
- Making API Requests
- Cluster Operation APIs
- ModifyClusterConfigs
- ModifyInstanceKeyValConfigs
- ModifyNodeStatus
- RestartClusterForConfigs
- RestartClusterForNode
- CreateInstanceNew
- DestroyInstance
- ModifySecurityGroups
- ReduceInstance
- ResizeDisk
- ScaleOutInstance
- ScaleUpInstance
- DescribeClusterConfigsHistory
- ModifyInstance
- DescribeCreateTablesDDL
- DescribeInstanceOperationHistory
- Database and Table APIs
- Cluster Information Viewing APIs
- Hot-Cold Data Layering APIs
- Database and Operation Audit APIs
- CancelBackupJob
- CreateBackUpSchedule
- DeleteBackUpData
- DescribeBackUpJob
- DescribeBackUpJobDetail
- DescribeBackUpTables
- DescribeBackUpTaskDetail
- DescribeRestoreTaskDetail
- DescribeBackUpSchedules
- RecoverBackUpJob
- DescribeInstanceOperations
- DescribeDatabaseAuditDownload
- DescribeDatabaseAuditRecords
- DescribeSlowQueryRecords
- DescribeSlowQueryRecordsDownload
- DescribeQueryAnalyse
- User and Permission APIs
- Resource Group Management APIs
- Data Types
- Error Codes
- Cloud Ecosystem
- Practical Tutorial
- Performance Testing
- FAQs
- Contact Us
- Glossary
- Product Policy
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.
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_tablePROPERTIES("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.
Yes
No
Was this page helpful?