使用StreamLoad导入数据 数据库保留字字段问题

💬 StarRocks 用户问答
#1

【详述】
利用StarRock dataX工具从MySQL 导入数据到 StarRock，由于MySQL中有一个字段名字为Role，导致数据导入流程不成功。

StarRock建表DDL：

CREATE TABLE try_activity
(
    `id`                    INT NOT NULL,
    `type`                  TINYINT,
    `role`                  TINYINT,
    `show_time_type`        TINYINT,
    `show_start_ts`         INT,
    `show_end_ts`           INT,
    `activity_time_type`    INT
)
 ENGINE=olap
PRIMARY KEY(`id`)
DISTRIBUTED BY HASH(`id`)
BUCKETS 10
PROPERTIES("replication_num" = "1")

DataX config:

{
	"job":{
		"content":[
			{
				"reader":{
					"parameter":{
						"password":"@@@@@@",
						"connection":[
							{
								"querySql":[
									"SELECT `id`,`type`,`role`,`show_time_type`,`show_start_ts`,`show_end_ts`,`activity_time_type`, FROM try_activity "
								],
								"jdbcUrl":[
									"jdbc:mysql://xxxx:3306/activity?useUnicode=yes&useCursorFetch=true&useSSL=false&serverTimezone=Asia%2FShanghai&useCompression=true&characterEncoding=utf8"
								]
							}
						],
						"dataxName":"test",
						"username":"@@@@@@"
					},
					"name":"mysqlreader"
				},
				"writer":{
					"parameter":{
						"password":"",
						"database":"test",
						"loadProps":{
							"row_delimiter":"\\x02",
							"column_separator":"\\x01"
						},
						"maxBatchSize":104857600,
						"column":[
							"id",
							"type",
							"role",
							"show_time_type",
							"show_start_ts",
							"show_end_ts",
							"activity_time_type",
						],
						"jdbcUrl":"jdbc:mysql://172.18.0.155:9030/test",
						"loadUrl":[
							"172.18.0.155:8030"
						],
						"table":"try_activity",
						"dataxName":"test",
						"username":"root"
					}
				}
			}
		],
		"setting":{
			.........
		}
	}
}

【背景】做过哪些操作？
【业务影响】
StarRock dataX 执行不能成功,执行过程中报告以下错误：

    java.io.IOException: Failed to flush data to doris table:test.try_activity.
{"Status":"Fail","BeginTxnTimeMs":0
,"Message":"Syntax error in line 1:
COLUMNS (id,type,role,show_time_type,show_sta...\n                
Encountered: ROLE
Expected: ROLE is keyword, maybe `ROLE`"
,"NumberUnselectedRows":0
,"CommitAndPublishTimeMs":0
,"Label":"ccf0ca8b-15c0-4c84-a1ad-c192e1ddcb24"
,"LoadBytes":0,"StreamLoadPutTimeMs":1
,"NumberTotalRows":0
,"WriteDataTimeMs":0,"TxnId":885843
,"LoadTimeMs":0,"ReadDataTimeMs":0
,"NumberLoadedRows":0,"NumberFilteredRows":0}

查询了StreamLoad的配置参数 并没有发现如何处理数据库保留字符的相关配置,

后面 ，按照错误提示：**Expected: ROLE is keyword, maybe `ROLE` ** 在DataX配置中将 role的列明加上 ``, 重新执行之后还是报相同的错误

"column":[
"id",
"type",
"`role`",
"show_time_type",
"show_start_ts",
"show_end_ts",
"activity_time_type",
],
#2

可以使用最新的starrocks writer试下，这个问题已经修复过了。
https://github.com/StarRocks/DataX/releases

#3

兄弟 ，太谢谢啦

京ICP备2022026421号-1