doris数据导入-严格模式

转载自：https://zhuanlan.zhihu.com/p/621836873

1、严格模式

对导入中的数据列类型转换进行严格过滤。

列类型转换后为 null 的数据将被过滤。

不含函数计算为 null 的数据。

（1）设置方式

代理加载 Broker Load 通过属性 "strict_mode"="true" 设置：

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"
)

流式加载 Stream Load 通过头 -H "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

插入数据通过会话变量设置：

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

（2）关闭严格模式

建表

[root@vm30 ~]# mysql -uroot -p123456 -P9030 -hvm30 demo -e '
> CREATE TABLE `example_strict_1` (
>   `k1` int     COMMENT "第一列",
>   `k2` TinyInt COMMENT "第二列",
>   `k3` double  COMMENT "第三列"
> )
> DISTRIBUTED BY HASH(k1) BUCKETS 2
> PROPERTIES (
>     "replication_num" = "1"
> );'
mysql: [Warning] Using a password on the command line interface can be insecure.
[root@vm30 ~]#
[root@vm30 ~]# mysql -uroot -p123456 -P9030 -hvm30 demo -e '
> desc example_strict_1;'
mysql: [Warning] Using a password on the command line interface can be insecure.
+-------+---------+------+-------+---------+-------+
| Field | Type    | Null | Key   | Default | Extra |
+-------+---------+------+-------+---------+-------+
| k1    | INT     | Yes  | true  | NULL    |       |
| k2    | TINYINT | Yes  | true  | NULL    |       |
| k3    | DOUBLE  | Yes  | false | NULL    | NONE  |
+-------+---------+------+-------+---------+-------+
[root@vm30 ~]#

造数

[root@vm30 ~]# cat > example_strict_1 <<EOL
> 1,\\N,1.1
> 2,abc,1.2
> 3,2000,1.3
> 4,1,1.4
> EOL
[root@vm30 ~]# cat example_strict_1
1,\N,1.1
2,abc,1.2
3,2000,1.3
4,1,1.4
[root@vm30 ~]#

流式加载 - 默认关闭严格模式

[root@vm30 ~]# curl --location-trusted      \
> -u root:123456               \
> -H "column_separator:,"      \
> -T example_strict_1          \
> http://vm30:8030/api/demo/example_strict_1/_stream_load
{
    "TxnId": 2024,
    "Label": "5bd983e8-0bcf-4fa7-a835-60fd8ec1d4e2",
    "TwoPhaseCommit": "false",
    "Status": "Success",
    "Message": "OK",
    "NumberTotalRows": 4,
    "NumberLoadedRows": 4,
    "NumberFilteredRows": 0,
    "NumberUnselectedRows": 0,
    "LoadBytes": 38,
    "LoadTimeMs": 147,
    "BeginTxnTimeMs": 3,
    "StreamLoadPutTimeMs": 12,
    "ReadDataTimeMs": 0,
    "WriteDataTimeMs": 59,
    "CommitAndPublishTimeMs": 69
}
[root@vm30 ~]#

查询

关闭严格模式下 abc 和 2000 都会转换为空值 null。

[root@vm30 ~]# mysql -uroot -p123456 -P9030 -hvm30 demo -e '
> select * from example_strict_1;'
mysql: [Warning] Using a password on the command line interface can be insecure.
+------+------+------+
| k1   | k2   | k3   |
+------+------+------+
|    3 | NULL |  1.3 |
|    1 | NULL |  1.1 |
|    2 | NULL |  1.2 |
|    4 |    1 |  1.4 |
+------+------+------+
[root@vm30 ~]# cat example_strict_1
1,\N,1.1
2,abc,1.2
3,2000,1.3
4,1,1.4
[root@vm30 ~]#

总结一下：
表结构中，k1字段类型是 int，k2字段类型是 TinyInt，k3字段类型是 double
但是构造的数据中，

# cat example_strict_1
1,\N,1.1
2,abc,1.2
3,2000,1.3
4,1,1.4

第一行的第二列也就是k2字段的值是\N,不符合TinyInt类型
第二行的第二列也就是k2字段的值是abc, 不符合TinyInt类型
第三行的第二列也就是k2字段的值是2000, 不符合TinyInt类型

因为流式加载是默认关闭严格模式的，因此关闭严格模式下 abc 和 2000 都会转换为空值 null。

abc 及 2000 在转换为 TinyInt 后，会因类型或精度问题变为 NULL。在严格模式开启的情况下，这类数据将会被过滤。而如果是关闭状态，则会导入 null。

（3）开启严格模式

建表

[root@vm30 ~]# mysql -uroot -p123456 -P9030 -hvm30 demo -e '
> CREATE TABLE `example_strict_2` (
>   `k1` int     COMMENT "第一列",
>   `k2` TinyInt COMMENT "第二列",
>   `k3` double  COMMENT "第三列"
> )
> DISTRIBUTED BY HASH(k1) BUCKETS 2
> PROPERTIES (
>     "replication_num" = "1"
> );'
mysql: [Warning] Using a password on the command line interface can be insecure.
[root@vm30 ~]#
[root@vm30 ~]# mysql -uroot -p123456 -P9030 -hvm30 demo -e '
> desc example_strict_2;'
mysql: [Warning] Using a password on the command line interface can be insecure.
+-------+---------+------+-------+---------+-------+
| Field | Type    | Null | Key   | Default | Extra |
+-------+---------+------+-------+---------+-------+
| k1    | INT     | Yes  | true  | NULL    |       |
| k2    | TINYINT | Yes  | true  | NULL    |       |
| k3    | DOUBLE  | Yes  | false | NULL    | NONE  |
+-------+---------+------+-------+---------+-------+
[root@vm30 ~]#

流式加载 - 开启严格模式

[root@vm30 ~]# curl --location-trusted      \
> -u root:123456               \
> -H "column_separator:,"      \
> -H "strict_mode: true"       \
> -T example_strict_1          \
> http://vm30:8030/api/demo/example_strict_2/_stream_load
{
    "TxnId": 2025,
    "Label": "6e86c67f-6496-46d2-a09a-ead19b5ea23c",
    "TwoPhaseCommit": "false",
    "Status": "Fail",
    "Message": "[INTERNAL_ERROR]too many filtered rows",
    "NumberTotalRows": 4,
    "NumberLoadedRows": 2,
    "NumberFilteredRows": 2,
    "NumberUnselectedRows": 0,
    "LoadBytes": 38,
    "LoadTimeMs": 64,
    "BeginTxnTimeMs": 2,
    "StreamLoadPutTimeMs": 8,
    "ReadDataTimeMs": 0,
    "WriteDataTimeMs": 50,
    "CommitAndPublishTimeMs": 0,
    "ErrorURL": "http://10.10.1.31:8040/api/_load_error_log?file=__shard_5/error_log_insert_stmt_f2411f67383594aa-ee11744d7d3da2a3_f2411f67383594aa_ee11744d7d3da2a3"
}
[root@vm30 ~]#

访问错误地址页面显示：

Reason: column(k2) value is incorrect while strict mode is true, src value is 1.2. src line [2 abc 1.2]; 
Reason: column(k2) value is incorrect while strict mode is true, src value is 1.3. src line [3 2000 1.3]; 

查询

表未加载任何数据。

[root@vm30 ~]# mysql -uroot -p123456 -P9030 -hvm30 demo -e '
> select * from example_strict_2;'
mysql: [Warning] Using a password on the command line interface can be insecure.
[root@vm30 ~]#

多造点数据

[root@vm30 ~]# cat > example_strict_2 <<EOL
> 1,\\N,1.1
> 2,abc,1.2
> 3,2000,1.3
> 4,1,1.4
> 5,2,1.5
> 6,3,1.6
> 7,4,1.7
> EOL
[root@vm30 ~]# cat example_strict_2
1,\N,1.1
2,abc,1.2
3,2000,1.3
4,1,1.4
5,2,1.5
6,3,1.6
7,4,1.7
[root@vm30 ~]#

流式加载

[root@vm30 ~]# curl --location-trusted      \
> -u root:123456               \
> -H "column_separator:,"      \
> -H "strict_mode: true"       \
> -T example_strict_2          \
> http://vm30:8030/api/demo/example_strict_2/_stream_load
{
    "TxnId": 2026,
    "Label": "8aac4480-b126-4469-8304-5302f087ef28",
    "TwoPhaseCommit": "false",
    "Status": "Fail",
    "Message": "[INTERNAL_ERROR]too many filtered rows",
    "NumberTotalRows": 7,
    "NumberLoadedRows": 5,
    "NumberFilteredRows": 2,
    "NumberUnselectedRows": 0,
    "LoadBytes": 62,
    "LoadTimeMs": 86,
    "BeginTxnTimeMs": 5,
    "StreamLoadPutTimeMs": 16,
    "ReadDataTimeMs": 0,
    "WriteDataTimeMs": 61,
    "CommitAndPublishTimeMs": 0,
    "ErrorURL": "http://10.10.1.31:8040/api/_load_error_log?file=__shard_6/error_log_insert_stmt_324199914f5445c5-56e83207cb1f8099_324199914f5445c5_56e83207cb1f8099"
}
[root@vm30 ~]#

（4）数据质量 - 过滤阈值

NumberFilteredRows - 过滤掉的数据行数

因数据质量不合格而被过滤掉的数据。
01.类型错误 - type error - 上例中 abc
02.精度错误 - precision error - 上例中 2000
03.字符串长度超长 - long string length
04.文件列数不匹配 - mismatched file column number - 前几篇中的 (k1, k2, k3) "Message": "[INTERNAL_ERROR]too many filtered rows",

NumberLoadedRows - 正确导入数据行数

NumberUnselectedRows - 前置过滤 Preceding Filter 和过滤 where 掉的数据行数

max_filter_ratio - 最大错误率

max_filter_ratio = NumberFilteredRows / (NumberFilteredRows + NumberLoadedRows)

Doris's import task allows the user to set a maximum error rate (max_filter_ratio). If the error rate of the imported data is below the threshold, those erroneous rows will be ignored and other correct data will be imported.
多丽丝允许用户设置最大错误率 max_filter_ratio。如果导入数据的错误率在这个阈值之下，会忽略这些错误行，并导入其他正确的数据。

章节（3）中的错误率是

[root@vm30 ~]# echo "2 7" | awk '{printf "%.2f\n", $1/$2}'
0.29
[root@vm30 ~]#

流式加载 - 指定错误率阈值 0.3，这次状态 Status 为成功 Success 了。

[root@vm30 ~]# curl --location-trusted      \
> -u root:123456               \
> -H "column_separator:,"      \
> -H "strict_mode: true"       \
> -H "max_filter_ratio:0.3"    \
> -T example_strict_2          \
> http://vm30:8030/api/demo/example_strict_2/_stream_load
{
    "TxnId": 2027,
    "Label": "2c432ba9-c980-4f19-9ffa-b42b95df4018",
    "TwoPhaseCommit": "false",
    "Status": "Success",
    "Message": "OK",
    "NumberTotalRows": 7,
    "NumberLoadedRows": 5,
    "NumberFilteredRows": 2,
    "NumberUnselectedRows": 0,
    "LoadBytes": 62,
    "LoadTimeMs": 188,
    "BeginTxnTimeMs": 4,
    "StreamLoadPutTimeMs": 15,
    "ReadDataTimeMs": 0,
    "WriteDataTimeMs": 58,
    "CommitAndPublishTimeMs": 106,
    "ErrorURL": "http://10.10.1.31:8040/api/_load_error_log?file=__shard_7/error_log_insert_stmt_c74923d5781e29b5-33c0f24c617770a0_c74923d5781e29b5_33c0f24c617770a0"
}
[root@vm30 ~]#

查询数据

仔细查看，会发现少了两条数据，这两条数据不符合要求

[root@vm30 ~]# mysql -uroot -p123456 -P9030 -hvm30 demo -e '
> select * from example_strict_2;'
mysql: [Warning] Using a password on the command line interface can be insecure.
+------+------+------+
| k1   | k2   | k3   |
+------+------+------+
|    5 |    2 |  1.5 |
|    6 |    3 |  1.6 |
|    1 | NULL |  1.1 |
|    4 |    1 |  1.4 |
|    7 |    4 |  1.7 |
+------+------+------+
[root@vm30 ~]# cat example_strict_2
1,\N,1.1
2,abc,1.2
3,2000,1.3
4,1,1.4
5,2,1.5
6,3,1.6
7,4,1.7
[root@vm30 ~]#