tencent cloud

Tencent Cloud TCHouse-D

Product Introduction
Overview
Concepts
Cluster Architecture
Strengths
Scenarios
Purchase Guide
Billing Overview
Renewal Instructions
Overdue Policy
Refund Instructions
Configuration Adjustment Billing Instructions
Getting Started
Using Tencent Cloud TCHouse-D Through the Console
Using Tencent Cloud TCHouse-D Through a Client
Operation Guide
Cluster Operation
Monitoring and Alarm Configuration
Account Privilege Management
Data Management
Query Management
Modify Configurations
Node Management
Log Analysis
SQL Studio
Enabling Resource Isolation
Development Guide
Design of Data Table
Importing Data
Exporting Data
Basic Feature
Query Optimization
Ecological Expansion Feature
API Documentation
History
Introduction
API Category
Making API Requests
Cluster Operation APIs
Database and Table APIs
Cluster Information Viewing APIs
Hot-Cold Data Layering APIs
Database and Operation Audit APIs
User and Permission APIs
Resource Group Management APIs
Data Types
Error Codes
Cloud Ecosystem
Granting CAM Policies to Sub-accounts
Query Acceleration for Tencent Cloud DLC
Practical Tutorial
Basic Feature Usage
Advanced Features Usage
Resource Specification Selection and Optimization Suggestions
Naming Specifications and Limits to the Database and Data Table
Table Design and Data Import
Query Optimization
Suggested Usage to Avoid
Accessing TCHouse-D via JDBC over the Public Network
Performance Testing
TPC-H Performance Testing
SSB Performance Testing
TPC-DS Performance Testing
FAQs
Common Operational Issues
Common Errors
Contact Us
Glossary
Product Policy
Service Level Agreement
Privacy Policy
Data Processing And Security Agreement

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