我使用的是OBIEE 12c的版本, 对应的datadirectodbc是8.0.2版本
使用datadirectodbc连接Clickhouse如果加了string类型就返回无结果,fixedstring没问题,
注意isql没问题.orz
我先用jdbc绕过去了,但是问题还要解决阿.
打开日志文件开关
有2个日志 datadirectodbc和clickhouse的
DataDirectODBC 和 ClickHouse的ODBC
$obiee_path/user_projects/domains/bidomain/config/fmwconfig/bienv/core/odbc.ini
history
需要如下配置打开
[ODBC]
Trace=1
TraceFile=$yourpath/odbc_detail.log
TraceDll=$obiee_path/bi/modules/oracle.bi.datadirect.odbc/8.0.2/lib/ARtrc28.so
InstallDir=$obiee_path/bi/modules/oracle.bi.datadirect.odbc/8.0.2
[CLICKHOUSE]
Driver=$yourpath/clickhouse-odbc-1.1.6-Linux/lib64/libclickhouseodbcw.so
DriverLog=on
DriverLogFile=/home/oracle/obieelogs/clicklogs/ck_detail2.log
DriverUnicodeType=1
IANAAppCodePage=106
检查日志
看DataDirectODBC的日志看到string的返回长度是16777215,fixedstring是1024
EXIT SQLPrepareW with return code 0 (SQL_SUCCESS)
HSTMT 0x007f4b70131f70
WCHAR * 0x007f4af3aca180 [4294967293] "select distinct T371481."stringcolumn" as c1 from "talbe01" T371481"
SDWORD -3
ENTER SQLNumResultCols
HSTMT 0x007f4b70131f70
SWORD * 0x007f4bb37f62c6
EXIT SQLNumResultCols with return code 0 (SQL_SUCCESS)
HSTMT 0x007f4b70131f70
SWORD * 0x007f4bb37f62c6 (1)
ENTER SQLDescribeColW
HSTMT 0x007f4b70131f70
UWORD 1
WCHAR * 0x007f4b21cd6760
SWORD 64
SWORD * 0x007f4bb37f62ce
SWORD * 0x007f4bb37f62c8
UDWORD * 0x007f4bb37f62d8
SWORD * 0x007f4bb37f62ca
SWORD * 0x007f4bb37f62cc
EXIT SQLDescribeColW with return code 0 (SQL_SUCCESS)
HSTMT 0x007f4b70131f70
UWORD 1
WCHAR * 0x007f4b21cd6760 [ 2] "c1"
SWORD 64
SWORD * 0x007f4bb37f62ce (2)
SWORD * 0x007f4bb37f62c8 (12)
UDWORD * 0x007f4bb37f62d8 (16777215)
SWORD * 0x007f4bb37f62ca (0)
SWORD * 0x007f4bb37f62cc (0)
再往下看,发现SQLExecute后因该有的SQLFetch语句没有
ENTER SQLExecute
HSTMT 0x007f51d80cff30
EXIT SQLExecute with return code 0 (SQL_SUCCESS)
HSTMT 0x007f51d80cff30
ENTER SQLCloseCursor
SQLHSTMT 0x007f51d80cff30
EXIT SQLCloseCursor with return code 0 (SQL_SUCCESS)
SQLHSTMT 0x007f51d80cff30
查看clickhouse的日志,没看到相应信息
1587611532171536 [21255:139860436293376] /pythontest/glibc/clickhouse-odbc-master/driver/api/odbc.cpp:502 in SQLExecute: SQLExecute
1587611544598959 [21255:139860436293376] /pythontest/glibc/clickhouse-odbc-master/driver/api/odbc.cpp:1264 in SQLCloseCursor: SQLCloseCursor
在odbc.cpp中SQLFetch和SQLFetchScroll部分增加相应日志和fixedstring比较发现确实没有执行
LOG(__FUNCTION__ << " ... debug odbcSQLFetch start ");
SQLRETURN SQL_API EXPORTED_FUNCTION(SQLFetch)(HSTMT statement_handle) {
auto func = [&] (Statement & statement) {
return impl::FetchScroll(statement, SQL_FETCH_NEXT, 0);
};
return CALL_WITH_TYPED_HANDLE(SQL_HANDLE_STMT, statement_handle, func);
}
SQLRETURN SQL_API EXPORTED_FUNCTION(SQLFetchScroll)(HSTMT statement_handle, SQLSMALLINT orientation, SQLLEN offset) {
auto func = [&] (Statement & statement) {
return impl::FetchScroll(statement, orientation, offset);
};
return CALL_WITH_TYPED_HANDLE(SQL_HANDLE_STMT, statement_handle, func);
}
修改涉及string的语句制定长度
猜测是string的字段长度太长了,odbc.cpp中涉及string长度可能2个地方
SQL_API EXPORTED_FUNCTION(SQLBindCol)
SQL_API EXPORTED_FUNCTION_MAYBE_W(SQLDescribeCol)
验证后修改SQLBindCol既可以(数据中控制长度,如果超了还没做测试),修改后obiee就能用了
BindingInfo binding;
binding.c_type = target_type;
binding.value = out_value;
binding.value_max_size = out_value_max_size;
binding.value_size = out_value_size_or_indicator;
binding.indicator = out_value_size_or_indicator;
//seng modify for string type return no data ************************************
if (out_value_max_size == 131080 && target_type == -8)
binding.value_max_size = 10240; //131080 1024 2048 4096 ok
LOG(__FUNCTION__ << " ...rong modify odbc SQLBindCol for string size out_value_max_size:" << binding.value_max_size
<< " out_value: " << out_value << " value_size: " << out_value_size_or_indicator << " target_type: " << target_type);
//seng modify end
statement.bindings[column_number] = binding;
相关总结:
过程中由于对odbc的调用走了不少弯路,如果对流程熟悉会更快.
过程中是这个issue提醒去看fetch流程,然后猜到bindcol的
https://github.com/ClickHouse/clickhouse-odbc/issues/242
Currently, we aim at 3 relatively independent parts that contribute to the overall performance:
Retrieving data from the server: the wire protocol (this issue.) This contributes to a latency associated with extracting data (converting string to double, integers etc.) from the wire.
Added cost of each ODBC API call. Currently application either
2.1. binds a single-row buffer for each column, and calls SQLFetch to get next row (addressing this with #235 and then implementing SQLFetchScroll/SQLExtendedFetch)
2.2. or even worse, call SQLGetData for each column for each row (after a SQLFetch). So there are lots of api calls involved to read a row. To address the second scenario (and partially the first one), we plan to reduce the added cost of a single api call: the issue #234
odbc的函数说明可以参考
DB2
https://www.ibm.com/support/knowledgecenter/en/SSEPGG_10.5.0/com.ibm.db2.luw.apdv.cli.doc/doc/r0000553.html
MS ODBC
https://docs.microsoft.com/en-us/sql/odbc/reference/odbc-programmer-s-reference?view=sql-server-ver15
