search

alibaba / DataX

DataX是阿里云DataWorks数据集成的开源版本。
Other
15.54k stars 5.35k forks source link

mysql导入es nested数据 中文乱码 #2131

Open weilongWrong opened 1 month ago

weilongWrong commented 1 month ago

mysql 一张主表 一张子表 上传到ES一个索引 mysql查询时将子表数据转成json为es索引的一个字段 结果里面的中文全乱码了

SELECT p.guid, JSON_ARRAYAGG( JSON_OBJECT( 'guid', pn.guid, 'colorid', pn.colorid, 'sizeid', pn.sizeid, 'barcode', pn.barcode, 'num', pn.num, 'salesnum', pn.salesnum, 'no', pn.NO)) AS sku, p.uid, p.fid, p.DIRECTORY, p.brand, p.bh, p.NAME, p.factory, p.factory_name AS 'factoryName', p.fprice, p.cost_amount AS 'costAmount', p.price, p.discount, p.discount_price AS 'discountPrice', p.has_directory_discount AS 'hasDirectoryDiscount', p.member_discount AS 'memberDiscount', p.member_price AS 'memberPrice', p.dat, p.modify_date AS 'modifyDate', p.nw, p.unit, p.pdesc, p.image222, p.imgs, p.remark, p.ONLINE, p.opensales, p.opensales_time AS 'opensalesTime', p.stopsales_time AS 'stopsalesTime', p.opensales_user AS 'opensalesUser', p.num, p.salesnum, p.material, p.quality, p.safety_grade AS 'safetyGrade', p.exe_standard AS 'exeStandard', p.washing_method AS 'washingMethod', p.has_open_discount AS 'hasOpenDiscount', p.has_give_points AS 'hasGivePoints', p.has_online AS 'hasOnline', p.video_url AS 'videoUrl', p.web_category_id AS 'webCategoryId', p.web_category_name AS 'webCategoryName', p.good_appraise_nums AS 'goodAppraiseNums', p.total_appraise_grade AS 'totalAppraiseGrade', p.default_sort AS 'defaultSort', p.has_open_member_discount AS hasOpenMemberDiscount FROM t_product p LEFT JOIN t_product_num pn ON p.guid = pn.guid GROUP BY pn.guid

SQL语句 是能正常运行的 关键是JSON_ARRAYAGG( JSON_OBJECT( 'guid', pn.guid, 'colorid', pn.colorid, 'sizeid', pn.sizeid, 'barcode', pn.barcode, 'num', pn.num, 'salesnum', pn.salesnum, 'no', pn.NO)) AS sku

转为nested上传上去后 就会乱码

{ "job": { "setting": { "speed": { "channel": 5 }, "errorLimit": { "record": 0, "percentage": 0.02 } }, "content": [{ "reader": { "encoding":"UTF-8", "name": "mysqlreader", "parameter": { "username": "password": "",

                "connection": [{
                    "jdbcUrl": [
                        ""
                    ],
                    "querySql": [
                        "SELECT p.guid, JSON_ARRAYAGG( JSON_OBJECT( 'guid', pn.guid, 'colorid', pn.colorid, 'sizeid', pn.sizeid, 'barcode', pn.barcode, 'num', pn.num, 'salesnum', pn.salesnum, 'no', pn.NO)) AS sku, p.uid, p.fid, p.DIRECTORY, p.brand, p.bh, p.NAME, p.factory, p.factory_name AS 'factoryName', p.fprice, p.cost_amount AS 'costAmount', p.price, p.discount, p.discount_price AS 'discountPrice', p.has_directory_discount AS 'hasDirectoryDiscount', p.member_discount AS 'memberDiscount', p.member_price AS 'memberPrice', p.dat, p.modify_date AS 'modifyDate', p.nw, p.unit, p.pdesc, p.image222, p.imgs, p.remark, p.ONLINE, p.opensales, p.opensales_time AS 'opensalesTime', p.stopsales_time AS 'stopsalesTime', p.opensales_user AS 'opensalesUser', p.num, p.salesnum, p.material, p.quality, p.safety_grade AS 'safetyGrade', p.exe_standard AS 'exeStandard', p.washing_method AS 'washingMethod', p.has_open_discount AS 'hasOpenDiscount', p.has_give_points AS 'hasGivePoints', p.has_online AS 'hasOnline', p.video_url AS 'videoUrl', p.web_category_id AS 'webCategoryId', p.web_category_name AS 'webCategoryName', p.good_appraise_nums AS 'goodAppraiseNums', p.total_appraise_grade AS 'totalAppraiseGrade', p.default_sort AS 'defaultSort', p.has_open_member_discount AS hasOpenMemberDiscount FROM t_product p LEFT JOIN t_product_num pn ON p.guid = pn.guid GROUP BY pn.guid"
                    ]
                }]
            }
        },

        "writer": {
            "name": "elasticsearchwriter",
            "parameter": {
                "endpoint": "",
                "accessId": "",
                "accessKey": "",
                "index": "fz_test_product",
                "cleanup": false,
                "discovery": false,
                "column": [{
                        "name": "guid",
                        "type": "keyword"
                    },
                    {
                        "name": "sku",
                        "type": "nested"
                    },
                    {
                        "name": "uid",
                        "type": "keyword"
                    },
                    {
                        "name": "fid",
                        "type": "keyword"
                    },
                    {
                        "name": "directory",
                        "type": "text",
                        "analyzer": "standard"
                    },
                    {
                        "name": "brand",
                        "type": "text",
                        "analyzer": "standard"
                    },
                    {
                        "name": "bh",
                        "type": "keyword"
                    },
                    {
                        "name": "name",
                        "type": "text",
                        "analyzer": "standard"
                    },
                    {
                        "name": "factory",
                        "type": "keyword"
                    },
                    {
                        "name": "factoryName",
                        "type": "text",
                        "analyzer": "standard"
                    },
                    {
                        "name": "fprice",
                        "type": "double"
                    },
                    {
                        "name": "costAmount",
                        "type": "double"
                    },
                    {
                        "name": "price",
                        "type": "double"
                    },
                    {
                        "name": "discount",
                        "type": "double"
                    },
                    {
                        "name": "discountPrice",
                        "type": "double"
                    },
                    {
                        "name": "hasDirectoryDiscount",
                        "type": "integer"
                    },
                    {
                        "name": "memberDiscount",
                        "type": "double"
                    },
                    {
                        "name": "memberPrice",
                        "type": "double"
                    },
                    {
                        "name": "dat",
                        "type": "date"
                    },
                    {
                        "name": "modifyDate",
                        "type": "date",
                        "format": "yyyy-MM-dd HH:mm:ss"
                    },
                    {
                        "name": "nw",
                        "type": "double"
                    },
                    {
                        "name": "unit",
                        "type": "keyword"
                    },
                    {
                        "name": "pdesc",
                        "type": "text",
                        "analyzer": "standard"
                    },
                    {
                        "name": "image222",
                        "type": "keyword"
                    },
                    {
                        "name": "imgs",
                        "type": "keyword"
                    },
                    {
                        "name": "remark",
                        "type": "text",
                        "analyzer": "standard"
                    },
                    {
                        "name": "online",
                        "type": "integer"
                    },
                    {
                        "name": "opensales",
                        "type": "integer"
                    },
                    {
                        "name": "opensalesTime",
                        "type": "long"
                    },
                    {
                        "name": "stopsalesTime",
                        "type": "long"
                    },
                    {
                        "name": "opensalesUser",
                        "type": "keyword"
                    },
                    {
                        "name": "num",
                        "type": "integer"
                    },
                    {
                        "name": "salesnum",
                        "type": "integer"
                    },
                    {
                        "name": "material",
                        "type": "keyword"
                    },
                    {
                        "name": "quality",
                        "type": "keyword"
                    },
                    {
                        "name": "safetyGrade",
                        "type": "keyword"
                    },
                    {
                        "name": "exeStandard",
                        "type": "keyword"
                    },
                    {
                        "name": "washingMethod",
                        "type": "keyword"
                    },
                    {
                        "name": "hasOpenDiscount",
                        "type": "integer"
                    },
                    {
                        "name": "hasGivePoints",
                        "type": "integer"
                    },
                    {
                        "name": "hasOnline",
                        "type": "integer"
                    },
                    {
                        "name": "videoUrl",
                        "type": "keyword"
                    },
                    {
                        "name": "webCategoryId",
                        "type": "keyword"
                    },
                    {
                        "name": "webCategoryName",
                        "type": "keyword"
                    },
                    {
                        "name": "goodAppraiseNums",
                        "type": "keyword"
                    },
                    {
                        "name": "totalAppraiseGrade",
                        "type": "double"
                    },
                    {
                        "name": "defaultSort",
                        "type": "long"
                    },
                    {
                        "name": "hasOpenMemberDiscount",
                        "type": "integer"
                    }
                ]
            }
        }

    }]
}

}

weilongWrong commented 1 month ago

![Uploading 企业微信截图_17174797877459.png…]()