[ https://issues.apache.org/jira/browse/KYLIN-4514?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17126461#comment-17126461 ]
dongwanfu edited comment on KYLIN-4514 at 6/5/20, 7:17 AM: ----------------------------------------------------------- 终于解决kylin连接phoenix部分日期转换错误的问题了,就是强制使用字符串格式: 1.kylin.properties中配置 {code:java} #### JDBC Data Source kylin.source.default=8 kylin.source.jdbc.connection-url=jdbc:phoenix:zk01,zk02,zk03:2181 kylin.source.jdbc.driver=org.apache.phoenix.jdbc.PhoenixDriver kylin.source.jdbc.dialect=default kylin.source.jdbc.user=admin kylin.source.jdbc.pass=admin123456 kylin.source.jdbc.sqoop-home=/home/install/sqoop kylin.source.jdbc.filed-delimiter=| ## 要加上一下两行解决`字符(ESC下面那个)的问题 kylin.source.hive.quote-enabled=false kylin.source.quote.character= {code} 2.自己编译sqoop,添加数据库连接参数 {code:java} isNamespaceMappingEnabled: true {code} 修改编译好的sqoop-1.4.7.jar,见附件[^sqoop-1.4.7.jar] 3.修改kylin源码编译 1)org.apache.kylin.source.jdbc.JdbcExplorer {code:java} private ColumnDesc[] extractColumnFromMeta(ResultSet meta) throws SQLException { List<ColumnDesc> columns = new ArrayList<>(); while (meta.next()) { String cname = meta.getString("COLUMN_NAME"); int type = meta.getInt("DATA_TYPE"); int csize = meta.getInt("COLUMN_SIZE"); int digits = meta.getInt("DECIMAL_DIGITS"); int pos = meta.getInt("ORDINAL_POSITION"); String remarks = meta.getString("REMARKS"); ColumnDesc cdesc = new ColumnDesc(); cdesc.setName(cname.toUpperCase(Locale.ROOT)); String kylinType = SqlUtil.jdbcTypeToKylinDataType(type); int precision = (SqlUtil.isPrecisionApplicable(kylinType) && csize > 0) ? csize : -1; int scale = (SqlUtil.isScaleApplicable(kylinType) && digits > 0) ? digits : -1; if(Types.DATE==type||Types.TIME==type||Types.TIMESTAMP==type){//添加的代码start kylinType = "varchar"; scale = -1; precision=20; }//添加的代码end cdesc.setDatatype(new DataType(kylinType, precision, scale).toString()); cdesc.setId(String.valueOf(pos)); cdesc.setComment(remarks); columns.add(cdesc); } return columns.toArray(new ColumnDesc[columns.size()]); }{code} 添加了以下 {code:java} if(Types.DATE==type||Types.TIME==type||Types.TIMESTAMP==type){ kylinType = "varchar"; scale = -1; precision=20; } {code} 2)org.apache.kylin.source.jdbc.extensible.JdbcExplorer:loadTableMetadata方法 {code:java} int precision = (SqlUtil.isPrecisionApplicable(kylinType) && csize > 0) ? csize : -1; int scale = (SqlUtil.isScaleApplicable(kylinType) && digits > 0) ? digits : -1; if(Types.DATE==type||Types.TIME==type||Types.TIMESTAMP==type){//添加的代码start kylinType = "varchar"; scale = -1; precision = 20; }//添加的代码end cdesc.setDatatype(new DataType(kylinType, precision, scale).toString()); cdesc.setId(String.valueOf(pos)); cdesc.setComment(remarks); columnDescs.add(cdesc);{code} 编译成功后用KYLIN_SOURCE_PATH\source-jdbc\target\classes\org\apache\kylin\source\jdbc下的class文件把KYLIN_HOME/tomcat/webapps/kylin.war/WEB-INF/lib中的kylin-source-jdbc-3.*.*.jar里面的相对应两个JdbcExplorer.class文件替换掉,然后重启kylin查看日期是否正确 对比一下修改前后的查询结果: 修改前: !DataInKylin1.jpg! !DataInKylin2.jpg! 修改后: !222222.jpg! !20200605150934.jpg! 在此记录一下,有同道中人遇到也可以提供个参考 was (Author: lxty): 终于解决kylin连接phoenix部分日期转换错误的问题了,就是强制使用字符串格式: 1.kylin.properties中配置 {code:java} #### JDBC Data Source kylin.source.default=8 kylin.source.jdbc.connection-url=jdbc:phoenix:zk01,zk02,zk03:2181 kylin.source.jdbc.driver=org.apache.phoenix.jdbc.PhoenixDriver kylin.source.jdbc.dialect=default kylin.source.jdbc.user=admin kylin.source.jdbc.pass=admin123456 kylin.source.jdbc.sqoop-home=/home/install/sqoop kylin.source.jdbc.filed-delimiter=| ## 要加上一下两行解决`字符(ESC下面那个)的问题 kylin.source.hive.quote-enabled=false kylin.source.quote.character= {code} 2.自己编译sqoop,添加数据库连接参数 {code:java} isNamespaceMappingEnabled: true {code} 修改编译好的sqoop-1.4.7.jar,见附件[^sqoop-1.4.7.jar] 3.修改kylin源码编译 1)org.apache.kylin.source.jdbc.JdbcExplorer {code:java} private ColumnDesc[] extractColumnFromMeta(ResultSet meta) throws SQLException { List<ColumnDesc> columns = new ArrayList<>(); while (meta.next()) { String cname = meta.getString("COLUMN_NAME"); int type = meta.getInt("DATA_TYPE"); int csize = meta.getInt("COLUMN_SIZE"); int digits = meta.getInt("DECIMAL_DIGITS"); int pos = meta.getInt("ORDINAL_POSITION"); String remarks = meta.getString("REMARKS"); ColumnDesc cdesc = new ColumnDesc(); cdesc.setName(cname.toUpperCase(Locale.ROOT)); String kylinType = SqlUtil.jdbcTypeToKylinDataType(type); int precision = (SqlUtil.isPrecisionApplicable(kylinType) && csize > 0) ? csize : -1; int scale = (SqlUtil.isScaleApplicable(kylinType) && digits > 0) ? digits : -1; if(Types.DATE==type||Types.TIME==type||Types.TIMESTAMP==type){//添加的代码start kylinType = "varchar"; scale = -1; precision=20; }//添加的代码end cdesc.setDatatype(new DataType(kylinType, precision, scale).toString()); cdesc.setId(String.valueOf(pos)); cdesc.setComment(remarks); columns.add(cdesc); } return columns.toArray(new ColumnDesc[columns.size()]); }{code} 添加了以下 {code:java} if(Types.DATE==type||Types.TIME==type||Types.TIMESTAMP==type){ kylinType = "varchar"; scale = -1; precision=20; } {code} 2)org.apache.kylin.source.jdbc.extensible.JdbcExplorer:loadTableMetadata方法 {code:java} int precision = (SqlUtil.isPrecisionApplicable(kylinType) && csize > 0) ? csize : -1; int scale = (SqlUtil.isScaleApplicable(kylinType) && digits > 0) ? digits : -1; if(Types.DATE==type||Types.TIME==type||Types.TIMESTAMP==type){//添加的代码start kylinType = "varchar"; scale = -1; precision = 20; }//添加的代码end cdesc.setDatatype(new DataType(kylinType, precision, scale).toString()); cdesc.setId(String.valueOf(pos)); cdesc.setComment(remarks); columnDescs.add(cdesc);{code} 编译成功后用KYLIN_SOURCE_PATH\source-jdbc\target\classes\org\apache\kylin\source\jdbc下的class文件把KYLIN_HOME/tomcat/webapps/kylin.war/WEB-INF/lib中的kylin-source-jdbc-3.*.*.jar里面的相对应两个JdbcExplorer.class文件替换掉,然后重启kylin查看日期是否正确 对比一下修改前后的查询结果: 修改前: !DataInKylin1.jpg! !DataInKylin2.jpg! 修改后: !222222.jpg! !20200605150934.jpg! > kylin通过jdbc连接apache phoenix,CUBE中的部分日期转换错误 > ------------------------------------------ > > Key: KYLIN-4514 > URL: https://issues.apache.org/jira/browse/KYLIN-4514 > Project: Kylin > Issue Type: Bug > Components: Driver - JDBC > Affects Versions: v3.0.1 > Environment: hadoop-2.6.5 hbase-1.3.1 jdk1.8.0_181 sqoop-1.4.7 > phoenix-4.14.3-HBase-1.3 > Reporter: dongwanfu > Priority: Major > Labels: date, jdbc, pohoenix, timestamp > Fix For: v3.0.1 > > Attachments: 20200602102111.jpg, 20200602102146.jpg, > 20200605150934.jpg, 222222.jpg, DataInKylin1.jpg, DataInKylin2.jpg, > DataInPhoenix.jpg, sqoop-1.4.7.jar > > > kylin通过jdbc连接Phoenix,kylin创建的CUBE中的日期出现了错误时间,我的phoenix中导入了10000条数据,其中有个字段birthtime(phoenix > > timestamp),时间范围在1970-2000年之间,kylin创建的Cube中的年份出现了很大的年份如9532年,也有很小的年份如1002年,我不知道怎么解决这个问题,只能提交这个issue。 > -- This message was sent by Atlassian Jira (v8.3.4#803005)