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

Thu, 07 Jul 2022 00:42:49 -0700 


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-unsubscr...@doris.apache.org

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


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

Reply via email to