[GitHub] [doris] lahok commented on issue #8182: 增加对外部表sqlserver中nvarchar类型数据的支持

[GitBox <gi...@apache.org>]

lahok commented on issue #8182:
URL: https://github.com/apache/doris/issues/8182#issuecomment-1177204525

   提示符就是成功。","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要用）&q
 uot;,"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":&q
 uot;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":"ND
 zj-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&
 quot;:"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",&
 quot;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&quot
 ;:"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-164549
 4971015","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":[{&quot
 ;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",&quo
 t;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&qu
 ot;,"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&qu
 ot;,"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":{},&quot
 ;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",&q
 uot;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",&
 quot;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":"\"o
 dbc_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&qu
 ot;,"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","nam
 e":"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&quot
 ;:"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&q
 uot;,"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&qu
 ot;,"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":&quo
 t;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 in
 to 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: ***@***.***>


-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: commits-unsubscribe@doris.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org


---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@doris.apache.org
For additional commands, e-mail: commits-help@doris.apache.org