增加对外部表sqlserver中nvarchar类型数据的支持

[lahok]

Search before asking

• I had searched in the issues and found no similar issues.

Description

外部表sqlserver中nvarchar类型数据现在有乱码现像，希望增加字符集处理类功能，比如函数等。
或都增加JDBC外部表，用JDBC的方式处理nvarchar类型的转码问题。

Use case

No response

Related issues

No response

Are you willing to submit PR?

• Yes I am willing to submit a PR!

Code of Conduct

• I agree to follow this project's Code of Conduct

[StephenLau007]

how did you solve it？

[blysin]

请问你sqlserver外部表是怎么创建的，我这边一直创建失败，可以指导一下吗 @lahok

[lahok]

提示符就是成功。","marks":[]}]}]},{"type":"block","id":"2kwC-1645432781013","name":"code-line","data":{},"nodes":[{"type":"text","id":"kL1M-1645432781013","leaves":[{"text":"","marks":[]}]}]},{"type":"block","id":"ZgQt-1645431254029","name":"code-line","data":{},"nodes":[{"type":"text","id":"ntVm-1645431254029","leaves":[{"text":"#","marks":[]},{"text":"向unixODBC注册tdsodbc驱动","marks":[{"type":"fontSize","value":14}]}]}]},{"type":"block","id":"ttXY-1645431255546","name":"code-line","data":{},"nodes":[{"type":"text","id":"Lr1O-1645431255545","leaves":[{"text":"vi /etc/odbcinst.ini","marks":[]}]}]},{"type":"block","id":"aK0U-1645431256032","name":"code-line","data":{},"nodes":[{"type":"text","id":"YE42-1645431256031","leaves":[{"text":"# 修改[FreeTDS]段驱动程序位置：Driver = /usr/local/freetds/lib/libtdsodbc.so","marks":[{"type":"fontSize","value":14}]}]}]},{"type":"block","id":"txJn-1645495994977","name":"code-line","data":{},"nodes":[{"type":"text","id":"jsvS-1645495994976","leaves":[{"text":"#（这个段名“FreeTDS”后面/etc/odbc.ini中Driver要用）","marks":[{"type":"fontSize","value":14}]}]}]},{"type":"block","id":"Ii6O-1645433619540","name":"code-line","data":{},"nodes":[{"type":"text","id":"1YWT-1645433619539","leaves":[{"text":"vi ","marks":[]},{"text":"/etc/odbc.ini","marks":[{"type":"fontSize","value":14}]}]}]},{"type":"block","id":"AeJi-1645496257777","name":"code-line","data":{},"nodes":[{"type":"text","id":"zd1A-1645496257776","leaves":[{"text":"# （记住这里配置的名称“mssql”，后面要用）","marks":[{"type":"fontSize","value":14}]}]}]},{"type":"block","id":"M0nR-1645433691631","name":"code-line","data":{},"nodes":[{"type":"text","id":"NIVa-1645433691630","leaves":[{"text":"[ODBC Data Sources]\r","marks":[{"type":"fontSize","value":14}]}]}]},{"type":"block","id":"IfNe-1645495695786","name":"code-line","data":{},"nodes":[{"type":"text","id":"hOxR-1645495695786","leaves":[{"text":"mssql = MSSQL Server\r","marks":[{"type":"fontSize","value":14}]}]}]},{"type":"block","id":"sted-1645495695786","name":"code-line","data":{},"nodes":[{"type":"text","id":"NDzj-1645495695786","leaves":[{"text":"\r","marks":[{"type":"fontSize","value":14}]}]}]},{"type":"block","id":"FhgB-1645495695786","name":"code-line","data":{},"nodes":[{"type":"text","id":"xsmT-1645495695786","leaves":[{"text":"[Default]\r","marks":[{"type":"fontSize","value":14}]}]}]},{"type":"block","id":"Xuj5-1645495695786","name":"code-line","data":{},"nodes":[{"type":"text","id":"DzSv-1645495695786","leaves":[{"text":"Driver = /usr/local/freetds/lib/libtdsodbc.so\r","marks":[{"type":"fontSize","value":14}]}]}]},{"type":"block","id":"eKsy-1645495695786","name":"code-line","data":{},"nodes":[{"type":"text","id":"drKC-1645495695786","leaves":[{"text":"\r","marks":[{"type":"fontSize","value":14}]}]}]},{"type":"block","id":"Z8Yi-1645495695786","name":"code-line","data":{},"nodes":[{"type":"text","id":"pTDw-1645495695786","leaves":[{"text":"[mssql]\r","marks":[{"type":"fontSize","value":14}]}]}]},{"type":"block","id":"uzyH-1645495695786","name":"code-line","data":{},"nodes":[{"type":"text","id":"ByTn-1645495695786","leaves":[{"text":"Driver         = FreeTDS\r","marks":[{"type":"fontSize","value":14}]}]}]},{"type":"block","id":"bs02-1645495695786","name":"code-line","data":{},"nodes":[{"type":"text","id":"aQDX-1645495695786","leaves":[{"text":"Description     = ODBC connection via FreeTDS\r","marks":[{"type":"fontSize","value":14}]}]}]},{"type":"block","id":"yz5W-1645495695786","name":"code-line","data":{},"nodes":[{"type":"text","id":"oTKK-1645495695786","leaves":[{"text":"Trace           = No\r","marks":[{"type":"fontSize","value":14}]}]}]},{"type":"block","id":"bVsq-1645495695786","name":"code-line","data":{},"nodes":[{"type":"text","id":"tOK6-1645495695786","leaves":[{"text":"Servername = MY_MS_SQL\r","marks":[{"type":"fontSize","value":14}]}]}]},{"type":"block","id":"qanZ-1645495695786","name":"code-line","data":{},"nodes":[{"type":"text","id":"ZsQC-1645495695786","leaves":[{"text":"Database        = YHYL_PFS_ZSJ\r","marks":[{"type":"fontSize","value":14}]}]}]},{"type":"block","id":"mAhV-1645495695786","name":"code-line","data":{},"nodes":[{"type":"text","id":"w6gq-1645495695786","leaves":[{"text":"","marks":[]}]}]},{"type":"block","id":"QPKt-1645431837641","name":"code-line","data":{},"nodes":[{"type":"text","id":"YkmL-1645431837640","leaves":[{"text":"#测试ODBC链接","marks":[]}]}]},{"type":"block","id":"2Vcq-1645431659448","name":"code-line","data":{},"nodes":[{"type":"text","id":"0BRN-1645431659448","leaves":[{"text":"isql","marks":[]},{"text":" -v mssql sa lanqin","marks":[{"type":"fontSize","value":14}]}]}]},{"type":"block","id":"e63a-1645435974121","name":"code-line","data":{},"nodes":[{"type":"text","id":"eJbc-1645435974120","leaves":[{"text":"","marks":[{"type":"fontSize","value":14}]}]}]},{"type":"block","id":"h21L-1645494959727","name":"code-line","data":{},"nodes":[{"type":"text","id":"EKtF-1645494959726","leaves":[{"text":"#填坑记：","marks":[{"type":"fontSize","value":14}]}]}]},{"type":"block","id":"2vHG-1645494971015","name":"code-line","data":{},"nodes":[{"type":"text","id":"SaOM-1645494971015","leaves":[{"text":"#通过执行odbcinst -j命令可以查看当前配置信息情况，以确认相关配置信息的路径是否正确。\r","marks":[]}]}]},{"type":"block","id":"rmYT-1645435974655","name":"code-line","data":{},"nodes":[{"type":"text","id":"5iuM-1645435974655","leaves":[{"text":"","marks":[{"type":"fontSize","value":14}]}]}]}],"state":{}},{"type":"block","id":"Wxkn-1645422695327","name":"list-item","data":{"listId":"MOJr-1645422476550","listType":"unordered","listLevel":2},"nodes":[{"type":"text","id":"aiGm-1645422695317","leaves":[{"text":"Doris上操作：","marks":[{"type":"fontSize","value":14}]}]}],"state":{"index":2}},{"type":"block","id":"CWda-1645496451826","name":"list-item","data":{"listId":"MOJr-1645422476550","listType":"unordered","listLevel":3},"nodes":[{"type":"text","id":"Gune-1645496451825","leaves":[{"text":"编辑","marks":[{"type":"fontSize","value":14}]},{"text":"BE节点的conf/odbcinst.ini，在末尾添加（这里的段名“","marks":[{"type":"color","value":"#4d4d4d"},{"type":"backgroundColor","value":"rgb(255, 255, 255)"},{"type":"fontFamily","value":"Arial"},{"type":"fontSize","value":14}]},{"text":"SQL Server”，在SQL:CREATE EXTERNAL RESOURCE的“driver”部分要用到，注意要一致","marks":[{"type":"fontSize","value":14}]},{"text":"）：","marks":[{"type":"color","value":"#4d4d4d"},{"type":"backgroundColor","value":"rgb(255, 255, 255)"},{"type":"fontFamily","value":"Arial"},{"type":"fontSize","value":14}]}]}],"state":{"index":1}},{"type":"block","id":"lpK9-1645496485371","name":"code","data":{"wrap":false,"language":"","theme":"default"},"nodes":[{"type":"block","id":"hSHi-1645496485371","name":"code-line","data":{},"nodes":[{"type":"text","id":"CkJs-1645496479745","leaves":[{"text":"[","marks":[]},{"text":"SQL Server","marks":[{"type":"fontSize","value":14}]},{"text":"]","marks":[]}]}]},{"type":"block","id":"S8YW-1645496719494","name":"code-line","data":{},"nodes":[{"type":"text","id":"g9eN-1645496719494","leaves":[{"text":"Description = ODBC for SQL Server","marks":[]}]}]},{"type":"block","id":"0jr1-1645496719494","name":"code-line","data":{},"nodes":[{"type":"text","id":"oEr7-1645496719494","leaves":[{"text":"Driver = ","marks":[]},{"text":"/usr/local/freetds/lib/libtdsodbc.so","marks":[{"type":"fontSize","value":14}]}]}]},{"type":"block","id":"zHw8-1645496719494","name":"code-line","data":{},"nodes":[{"type":"text","id":"J99v-1645496719494","leaves":[{"text":"FileUsage = 1 ","marks":[]}]}]}],"state":{}},{"type":"block","id":"z4E9-1645496485371","name":"list-item","data":{"version":1,"listId":"MOJr-1645422476550","listType":"unordered","listLevel":2},"nodes":[{"type":"text","id":"5oFS-1645496485371","leaves":[{"text":"Doris上SQL操作：","marks":[{"type":"fontSize","value":14}]}]}],"state":{"index":3}},{"type":"block","id":"V05h-1645423467309","name":"code","data":{"wrap":false,"language":"","theme":"default"},"nodes":[{"type":"block","id":"oPAA-1645423467309","name":"code-line","data":{},"nodes":[{"type":"text","id":"xABC-1645423463751","leaves":[{"text":"CREATE EXTERNAL RESOURCE `mssql_odbc`","marks":[]}]}]},{"type":"block","id":"J7wP-1645423476542","name":"code-line","data":{},"nodes":[{"type":"text","id":"0So9-1645423476542","leaves":[{"text":"PROPERTIES (","marks":[]}]}]},{"type":"block","id":"knAX-1645423476542","name":"code-line","data":{},"nodes":[{"type":"text","id":"truF-1645423476542","leaves":[{"text":"\"type\" = \"odbc_catalog\",","marks":[]}]}]},{"type":"block","id":"UxDz-1645423476542","name":"code-line","data":{},"nodes":[{"type":"text","id":"DkJp-1645423476542","leaves":[{"text":"\"host\" = \"","marks":[]},{"text":"192.168.211.96","marks":[{"type":"fontSize","value":14}]},{"text":"\",","marks":[]}]}]},{"type":"block","id":"iGmN-1645423476542","name":"code-line","data":{},"nodes":[{"type":"text","id":"Kv7V-1645423476542","leaves":[{"text":"\"port\" = \"1433\",","marks":[]}]}]},{"type":"block","id":"GJqw-1645423476542","name":"code-line","data":{},"nodes":[{"type":"text","id":"c59J-1645423476542","leaves":[{"text":"\"user\" = \"sa\",","marks":[]}]}]},{"type":"block","id":"Anch-1645423476542","name":"code-line","data":{},"nodes":[{"type":"text","id":"GmrQ-1645423476542","leaves":[{"text":"\"password\" = \"lanqin\",","marks":[]}]}]},{"type":"block","id":"FING-1645423476542","name":"code-line","data":{},"nodes":[{"type":"text","id":"FOqc-1645423476542","leaves":[{"text":"\"database\" = \"","marks":[]},{"text":"YHYL_PFS_ZSJ","marks":[{"type":"fontSize","value":14}]},{"text":"\",","marks":[]}]}]},{"type":"block","id":"zvdx-1645423476542","name":"code-line","data":{},"nodes":[{"type":"text","id":"ZF7J-1645423476542","leaves":[{"text":"\"odbc_type\" = \"sqlserver\",","marks":[]}]}]},{"type":"block","id":"CrZ2-1645423476542","name":"code-line","data":{},"nodes":[{"type":"text","id":"IluP-1645423476542","leaves":[{"text":"\"driver\" = \"SQL Server\"","marks":[]}]}]},{"type":"block","id":"6p1S-1645423476542","name":"code-line","data":{},"nodes":[{"type":"text","id":"Mfvc-1645423476542","leaves":[{"text":");","marks":[]}]}]},{"type":"block","id":"C8Kz-1645423476542","name":"code-line","data":{},"nodes":[{"type":"text","id":"uThZ-1645423476542","leaves":[{"text":" ","marks":[]}]}]},{"type":"block","id":"8J0t-1645423476542","name":"code-line","data":{},"nodes":[{"type":"text","id":"af9D-1645423476542","leaves":[{"text":"CREATE EXTERNAL TABLE YWYXX (\r","marks":[{"type":"fontSize","value":14}]}]}]},{"type":"block","id":"cIXs-1645425537166","name":"code-line","data":{},"nodes":[{"type":"text","id":"yqBO-1645425537166","leaves":[{"text":"\tIYWYLSH varchar(20) NOT NULL,\r","marks":[{"type":"fontSize","value":14}]}]}]},{"type":"block","id":"a40V-1645425537166","name":"code-line","data":{},"nodes":[{"type":"text","id":"rFgX-1645425537166","leaves":[{"text":"\tCYWYBM varchar(20) NOT NULL,\r","marks":[{"type":"fontSize","value":14}]}]}]},{"type":"block","id":"VVgh-1645425537166","name":"code-line","data":{},"nodes":[{"type":"text","id":"upHe-1645425537166","leaves":[{"text":"\tCYWYMC varchar(50) NOT NULL,\r","marks":[{"type":"fontSize","value":14}]}]}]},{"type":"block","id":"IDtR-1645425537166","name":"code-line","data":{},"nodes":[{"type":"text","id":"EvgQ-1645425537166","leaves":[{"text":"\tCBSFLX varchar(100) NULL,\r","marks":[{"type":"fontSize","value":14}]}]}]},{"type":"block","id":"eJbo-1645425537166","name":"code-line","data":{},"nodes":[{"type":"text","id":"HtzC-1645425537166","leaves":[{"text":"\tCPYM varchar(50) NULL,\r","marks":[{"type":"fontSize","value":14}]}]}]},{"type":"block","id":"y4bU-1645425537166","name":"code-line","data":{},"nodes":[{"type":"text","id":"JPEb-1645425537166","leaves":[{"text":"\tBKYX SMALLINT NOT NULL,\r","marks":[{"type":"fontSize","value":14}]}]}]},{"type":"block","id":"bSsv-1645425537166","name":"code-line","data":{},"nodes":[{"type":"text","id":"f3Nn-1645425537166","leaves":[{"text":"\tCMM varchar(20) NULL,\r","marks":[{"type":"fontSize","value":14}]}]}]},{"type":"block","id":"g8jk-1645425537166","name":"code-line","data":{},"nodes":[{"type":"text","id":"yltb-1645425537166","leaves":[{"text":"\tCYLJGID varchar(30) NULL,\r","marks":[{"type":"fontSize","value":14}]}]}]},{"type":"block","id":"vJMJ-1645425537166","name":"code-line","data":{},"nodes":[{"type":"text","id":"GK3p-1645425537166","leaves":[{"text":"\tBMMKYX SMALLINT NULL\r","marks":[{"type":"fontSize","value":14}]}]}]},{"type":"block","id":"80BV-1645425537166","name":"code-line","data":{},"nodes":[{"type":"text","id":"IWpW-1645425537166","leaves":[{"text":") ENGINE=ODBC\r","marks":[{"type":"fontSize","value":14}]}]}]},{"type":"block","id":"clSA-1645425537166","name":"code-line","data":{},"nodes":[{"type":"text","id":"obMI-1645425537166","leaves":[{"text":"COMMENT \"ODBC\"\r","marks":[{"type":"fontSize","value":14}]}]}]},{"type":"block","id":"2GQg-1645425537166","name":"code-line","data":{},"nodes":[{"type":"text","id":"KaQf-1645425537166","leaves":[{"text":"PROPERTIES (\r","marks":[{"type":"fontSize","value":14}]}]}]},{"type":"block","id":"v29C-1645425537166","name":"code-line","data":{},"nodes":[{"type":"text","id":"cFia-1645425537166","leaves":[{"text":"\"odbc_catalog_resource\" = \"mssql_odbc\",\r","marks":[{"type":"fontSize","value":14}]}]}]},{"type":"block","id":"lW0P-1645425537166","name":"code-line","data":{},"nodes":[{"type":"text","id":"mdeX-1645425537166","leaves":[{"text":"\"database\" = \"YHYL_PFS_ZSJ\",\r","marks":[{"type":"fontSize","value":14}]}]}]},{"type":"block","id":"m02t-1645425537166","name":"code-line","data":{},"nodes":[{"type":"text","id":"kuqz-1645425537166","leaves":[{"text":"\"table\" = \"TB_YWYXX\"\r","marks":[{"type":"fontSize","value":14}]}]}]},{"type":"block","id":"HrJD-1645425537166","name":"code-line","data":{},"nodes":[{"type":"text","id":"zeLY-1645425537166","leaves":[{"text":");","marks":[{"type":"fontSize","value":14}]}]}]},{"type":"block","id":"RPcD-1645522421032","name":"code-line","data":{},"nodes":[{"type":"text","id":"8wvR-1645522421029","leaves":[{"text":"","marks":[]}]}]},{"type":"block","id":"Xupy-1645522421163","name":"code-line","data":{},"nodes":[{"type":"text","id":"z4XF-1645522421162","leaves":[{"text":"#测试写入数据：","marks":[]}]}]},{"type":"block","id":"ExsO-1645522427642","name":"code-line","data":{},"nodes":[{"type":"text","id":"bt9W-1645522427642","leaves":[{"text":"insert into YWYXX(IYWYLSH,CYWYBM,CYWYMC,BKYX) values (" a',="" 'b',="" 'c',="" 2);","marks":[]}]}]},{"type":"block","id":"ouwh-1645522469722","name":"code-line","data":{},"nodes":[{"type":"text","id":"9lh9-1645522469721","leaves":[{"text":"insert="" into="" ywyxx(iywylsh,cywybm,cywymc,bkyx)="" values="" ('东方不典籍',="" 2);","marks":[]}]}]}],"state":{}},{"type":"block","id":"pezd-1645422452779","name":"paragraph","data":{"style":{"indent":56}},"nodes":[{"type":"text","id":"wy4b-1645422452779","leaves":[{"text":"doris查询成功。（n","marks":[]},{"text":"varchar类型数据乱码问题，","marks":[{"type":"fontsize","value":14}]},{"text":"todo","marks":[{"type":"fontsize","value":14},{"type":"color","value":"#f33232"}]}]}],"state":{}}]'=""> 建立到MSSQL 的ODBC外表： 配置SQL Server ODBC: (参考：https://www.cnblogs.com/daner1257/p/11807123.html) cd /var/lib/doris/be/mydata # 遇到YUM问题 参考： https://blog.csdn.net/tongkongyu/article/details/122824763 yum install -y unixODBC unixODBC-devel # 查看是否安装成功 isql --version #安装数据库驱动程序FreeTDS curl -O ftp://ftp.freetds.org/pub/freetds/stable/freetds-1.3.9.tar.gz tar zxvf freetds-1.3.9.tar.gz cd freetds-1.3.9/ # 编译安装，注意 tdsver 版本（可以用./configure --help,输出后可以看到这样的一行：--with-tdsver=VERSION TDS protocol version (5.0/7.1/7.2/7.3/7.4/auto)） ./configure --prefix=/usr/local/freetds --enable-msdblib --with-tdsver=7.4 make && make install #检查FreeTDS安装情况 /usr/local/freetds/bin/tsql -H 192.168.211.96 -p 1433 -U sa -P lanqin #配置freeTDS vi /usr/local/freetds/etc/freetds.conf #编辑配置文件，增加一个数据库连接段落：（这里的段名“MY_MS_SQL”后面/etc/odbc.ini中Servername要用） [MY_MS_SQL] host = 192.168.211.96 port = 1433 tds version = 7.4 #使用tsql测试，/usr/local/freetds/bin/tsql -S MY_MS_SQL -U sa -P lanqin，看到1>提示符就是成功。 #向unixODBC注册tdsodbc驱动 vi /etc/odbcinst.ini # 修改[FreeTDS]段驱动程序位置：Driver = /usr/local/freetds/lib/libtdsodbc.so #（这个段名“FreeTDS”后面/etc/odbc.ini中Driver要用） vi /etc/odbc.ini # （记住这里配置的名称“mssql”，后面要用） [ODBC Data Sources] mssql = MSSQL Server [Default] Driver = /usr/local/freetds/lib/libtdsodbc.so [mssql] Driver         = FreeTDS Description     = ODBC connection via FreeTDS Trace           = No Servername = MY_MS_SQL Database        = YHYL_PFS_ZSJ #测试ODBC链接 isql -v mssql sa lanqin #填坑记： #通过执行odbcinst -j命令可以查看当前配置信息情况，以确认相关配置信息的路径是否正确。 Doris上操作： 编辑BE节点的conf/odbcinst.ini，在末尾添加（这里的段名“SQL Server”，在SQL:CREATE EXTERNAL RESOURCE的“driver”部分要用到，注意要一致）： [SQL Server] Description = ODBC for SQL Server Driver = /usr/local/freetds/lib/libtdsodbc.so FileUsage = 1 Doris上SQL操作： CREATE EXTERNAL RESOURCE `mssql_odbc` PROPERTIES ( "type" = "odbc_catalog", "host" = "192.168.211.96", "port" = "1433", "user" = "sa", "password" = "lanqin", "database" = "YHYL_PFS_ZSJ", "odbc_type" = "sqlserver", "driver" = "SQL Server" ); CREATE EXTERNAL TABLE YWYXX ( IYWYLSH varchar(20) NOT NULL, CYWYBM varchar(20) NOT NULL, CYWYMC varchar(50) NOT NULL, CBSFLX varchar(100) NULL, CPYM varchar(50) NULL, BKYX SMALLINT NOT NULL, CMM varchar(20) NULL, CYLJGID varchar(30) NULL, BMMKYX SMALLINT NULL ) ENGINE=ODBC COMMENT "ODBC" PROPERTIES ( "odbc_catalog_resource" = "mssql_odbc", "database" = "YHYL_PFS_ZSJ", "table" = "TB_YWYXX" ); #测试写入数据： insert into YWYXX(IYWYLSH,CYWYBM,CYWYMC,BKYX) values ('a', 'b', 'c', 2); insert into YWYXX(IYWYLSH,CYWYBM,CYWYMC,BKYX) values ('东方不典籍', 'b', 'c', 2); Doris查询成功。（Nvarchar类型数据乱码问题，todo） Message ID: ***@***.***>

[lahok]

下面这段是我曾经测试它时的笔记， 你参考一下：

…

------------------ 原始邮件 ------------------ 发件人: ***@***.***>; 发送时间: 2022年7月7日(星期四) 下午3:36 收件人: ***@***.***>; 抄送: ***@***.***>; ***@***.***>; 主题: Re: [apache/doris] 增加对外部表sqlserver中nvarchar类型数据的支持 (Issue #8182) 请问你sqlserver外部表是怎么创建的，我这边一直创建失败，可以指导一下吗 @lahok — Reply to this email directly, view it on GitHub, or unsubscribe. You are receiving this because you were mentioned.Message ID: ***@***.***>