kylin-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From 程 万胜 <chivise...@hotmail.com>
Subject 答复: 答复: No result from JDBC with Date filter in prepareStatement
Date Tue, 22 Aug 2017 03:17:22 GMT
yes. I tested kylin2.0 and kylin2.1, that has some problem


kylin.log

2017-08-22 11:12:11,925 DEBUG [http-bio-7070-exec-4] security.KylinAuthenticationProvider:113
: User ADMIN authorities : [ROLE_ADMIN, ROLE_ANALYST, ROLE_MODELER]
2017-08-22 11:12:11,931 DEBUG [http-bio-7070-exec-4] security.KylinAuthenticationProvider:57
: User cache [-108, 112, -63, -32, 41, -87, -81, 81, -32, 61, -35, -111, 7, 56, -29, -59]
is removed due to EXPIRED
2017-08-22 11:12:11,931 DEBUG [http-bio-7070-exec-4] security.KylinAuthenticationProvider:126
: Authenticated user org.springframework.security.authentication.UsernamePasswordAuthenticationToken@c8fb3bba:
Principal: org.springframework.security.core.userdetails.User@3b40b2f: Username: ADMIN; Password:
[PROTECTED]; Enabled: true; AccountNonExpired: true; credentialsNonExpired: true; AccountNonLocked:
true; Granted Authorities: ROLE_ADMIN,ROLE_ANALYST,ROLE_MODELER; Credentials: [PROTECTED];
Authenticated: true; Details: org.springframework.security.web.authentication.WebAuthenticationDetails@0:
RemoteIpAddress: 192.168.93.1; SessionId: null; Granted Authorities: ROLE_ADMIN, ROLE_ANALYST,
ROLE_MODELER
2017-08-22 11:12:11,933 DEBUG [http-bio-7070-exec-4] controller.UserController:55 : User login:
org.springframework.security.core.userdetails.User@3b40b2f: Username: ADMIN; Password: [PROTECTED];
Enabled: true; AccountNonExpired: true; credentialsNonExpired: true; AccountNonLocked: true;
Granted Authorities: ROLE_ADMIN,ROLE_ANALYST,ROLE_MODELER
2017-08-22 11:12:11,996 INFO  [Query cc783405-8d75-4f3c-9fdb-411427111448-92] service.QueryService:366
: Using project: learn_kylin
2017-08-22 11:12:11,997 INFO  [Query cc783405-8d75-4f3c-9fdb-411427111448-92] service.QueryService:367
: The original query:  select * from KYLIN_SALES where part_dt = ?
2017-08-22 11:12:11,998 INFO  [Query cc783405-8d75-4f3c-9fdb-411427111448-92] schema.OLAPSchemaFactory:116
: Schema json:{
    "version": "1.0",
    "defaultSchema": "DEFAULT",
    "schemas": [
        {
            "type": "custom",
            "name": "DEFAULT",
            "factory": "org.apache.kylin.query.schema.OLAPSchemaFactory",
            "operand": {
                "project": "LEARN_KYLIN"
            },
            "functions": [
               {
                   name: 'PERCENTILE',
                   className: 'org.apache.kylin.measure.percentile.PercentileAggFunc'
               },
               {
                   name: 'INTERSECT_COUNT',
                   className: 'org.apache.kylin.measure.bitmap.BitmapIntersectDistinctCountAggFunc'
               },
               {
                   name: 'MASSIN',
                   className: 'org.apache.kylin.query.udf.MassInUDF'
               },
               {
                   name: 'CONCAT',
                   className: 'org.apache.kylin.query.udf.ConcatUDF'
               },
               {
                   name: 'VERSION',
                   className: 'org.apache.kylin.query.udf.VersionUDF'
               }
            ]
        }
    ]
}
2017-08-22 11:12:12,008 INFO  [Query cc783405-8d75-4f3c-9fdb-411427111448-92] service.QueryService:868
: Processed rows for each storageContext:
2017-08-22 11:12:12,008 INFO  [Query cc783405-8d75-4f3c-9fdb-411427111448-92] service.QueryService:393
: Stats of SQL response: isException: false, duration: 11, total scan count 0
2017-08-22 11:12:12,009 DEBUG [Query cc783405-8d75-4f3c-9fdb-411427111448-92] util.CheckUtil:35
: query is too lightweight with duration: 11 (threshold 2000), scan count: 0 (threshold 10240),
scan bytes: 0 (threshold 1048576)
2017-08-22 11:12:12,009 INFO  [Query cc783405-8d75-4f3c-9fdb-411427111448-92] service.QueryService:284
:
==========================[QUERY]===============================
Query Id: cc783405-8d75-4f3c-9fdb-411427111448
SQL: select * from KYLIN_SALES where part_dt = ?
User: ADMIN
Success: true
Duration: 0.011
Project: learn_kylin
Realization Names: []
Cuboid Ids: []
Total scan count: 0
Total scan bytes: 0
Result row count: 0
Accept Partial: false
Is Partial Result: false
Hit Exception Cache: false
Storage cache used: false
Is Query Push-Down: false
Message: null


________________________________
发件人: Billy Liu <billyliu@apache.org>
发送时间: 2017年8月21日 17:04
收件人: user
主题: Re: 答复: No result from JDBC with Date filter in prepareStatement

Could you try the latest Kylin 2.1?

The Date parameter issue has been fixed during Kylin 2.0.

2017-08-21 13:45 GMT+08:00 程 万胜 <chivise.cn@hotmail.com<mailto:chivise.cn@hotmail.com>>:

kylin.log


2017-08-21 13:40:58,606 DEBUG [http-bio-7070-exec-9] controller.UserController:64 : authentication.getPrincipal()
is org.springframework.security.core.userdetails.User@3b40b2f: Username: ADMIN; Password:
[PROTECTED]; Enabled: true; AccountNonExpired: true; credentialsNo
nExpired: true; AccountNonLocked: true; Granted Authorities: ROLE_ADMIN,ROLE_ANALYST,ROLE_MODELER
2017-08-21 13:40:58,679 INFO  [Query eabab58a-47aa-454c-a0ff-e5815a490d22-200] service.QueryService:337
: Using project: learn_kylin
2017-08-21 13:40:58,679 INFO  [Query eabab58a-47aa-454c-a0ff-e5815a490d22-200] service.QueryService:338
: The original query:  select part_dt, sum(price) as total_selled, count(distinct seller_id)
as sellers from kylin_sales where part_dt <= ? group by part_dt order by
part_dt limit 100
2017-08-21 13:40:58,692 INFO  [Query eabab58a-47aa-454c-a0ff-e5815a490d22-200] routing.QueryRouter:56
: Find candidates by table TMP_KYLIN.KYLIN_SALES and project=LEARN_KYLIN : CUBE[name=kylin_sales_cube]
2017-08-21 13:40:58,692 INFO  [Query eabab58a-47aa-454c-a0ff-e5815a490d22-200] routing.QueryRouter:51
: Applying rule: class org.apache.kylin.query.routing.rules.RemoveBlackoutRealizationsRule,
realizations before: [kylin_sales_cube(CUBE)], realizations after: [kylin_sa
les_cube(CUBE)]
2017-08-21 13:40:58,692 INFO  [Query eabab58a-47aa-454c-a0ff-e5815a490d22-200] routing.QueryRouter:51
: Applying rule: class org.apache.kylin.query.routing.rules.RemoveUncapableRealizationsRule,
realizations before: [kylin_sales_cube(CUBE)], realizations after: [kylin_s
ales_cube(CUBE)]
2017-08-21 13:40:58,692 INFO  [Query eabab58a-47aa-454c-a0ff-e5815a490d22-200] rules.RealizationSortRule:40
: CUBE[name=kylin_sales_cube] priority 1 cost 836.
2017-08-21 13:40:58,693 INFO  [Query eabab58a-47aa-454c-a0ff-e5815a490d22-200] routing.QueryRouter:51
: Applying rule: class org.apache.kylin.query.routing.rules.RealizationSortRule, realizations
before: [kylin_sales_cube(CUBE)], realizations after: [kylin_sales_cube(CU
BE)]
2017-08-21 13:40:58,693 INFO  [Query eabab58a-47aa-454c-a0ff-e5815a490d22-200] routing.QueryRouter:68
: The realizations remaining: [kylin_sales_cube(CUBE)] And the final chosen one is the first
one
2017-08-21 13:40:58,714 INFO  [Query eabab58a-47aa-454c-a0ff-e5815a490d22-200] service.QueryService:528
: Setting current statement's max rows to 0
2017-08-21 13:40:58,718 DEBUG [Query eabab58a-47aa-454c-a0ff-e5815a490d22-200] enumerator.OLAPEnumerator:109
: query storage...
2017-08-21 13:40:58,718 DEBUG [Query eabab58a-47aa-454c-a0ff-e5815a490d22-200] gtrecord.GTCubeStorageQueryBase:253
: Does not need storage aggregation
2017-08-21 13:40:58,718 DEBUG [Query eabab58a-47aa-454c-a0ff-e5815a490d22-200] gtrecord.GTCubeStorageQueryBase:345
: Storage limit push down is impossible because the query has order by
2017-08-21 13:40:58,719 DEBUG [Query eabab58a-47aa-454c-a0ff-e5815a490d22-200] gtrecord.GTCubeStorageQueryBase:392
: Aggregate partition results is not beneficial because no storage aggregation
2017-08-21 13:40:58,719 INFO  [Query eabab58a-47aa-454c-a0ff-e5815a490d22-200] gtrecord.GTCubeStorageQueryBase:151
: Cuboid identified: cube=kylin_sales_cube, cuboidId=16384, groupsD=[TMP_KYLIN.KYLIN_SALES.PART_DT],
filterD=[TMP_KYLIN.KYLIN_SALES.PART_DT], limitPushdown
=2147483647<tel:021%204748%203647>, storageAggr=false
2017-08-21 13:40:58,719 INFO  [Query eabab58a-47aa-454c-a0ff-e5815a490d22-200] gtrecord.CubeSegmentScanner:56
: Init CubeSegmentScanner for segment 20120101000000_20170801000000
2017-08-21 13:40:58,719 DEBUG [Query eabab58a-47aa-454c-a0ff-e5815a490d22-200] dimension.DimensionEncodingFactory:57
: Encoding Name : date, args : [], version 1
2017-08-21 13:40:58,721 DEBUG [Query eabab58a-47aa-454c-a0ff-e5815a490d22-200] gtrecord.CubeScanRangePlanner:213
: Pre-check partition col filter failed, partitionColRef UNKNOWN_MODEL:NULL.GT_MOCKUP_TABLE.0,
segment start \x0B\x36\x96, segment end \x0B\x3E\x8D, range be
gin null, range end \x0A\xFA\xAA
2017-08-21 13:40:58,721 INFO  [Query eabab58a-47aa-454c-a0ff-e5815a490d22-200] gtrecord.ScannerWorker:44
: Segment kylin_sales_cube[20120101000000_20170801000000] will be skipped
2017-08-21 13:40:58,744 INFO  [Query eabab58a-47aa-454c-a0ff-e5815a490d22-200] gtrecord.SequentialCubeTupleIterator:71
: Using Iterators.concat to merge segment results
2017-08-21 13:40:58,745 DEBUG [Query eabab58a-47aa-454c-a0ff-e5815a490d22-200] enumerator.OLAPEnumerator:122
: return TupleIterator...
2017-08-21 13:40:58,746 INFO  [Query eabab58a-47aa-454c-a0ff-e5815a490d22-200] service.QueryService:598
: Processed rows for each storageContext: 0
2017-08-21 13:40:58,746 INFO  [Query eabab58a-47aa-454c-a0ff-e5815a490d22-200] service.QueryService:363
: Stats of SQL response: isException: false, duration: 67, total scan count 0
2017-08-21 13:40:58,746 DEBUG [Query eabab58a-47aa-454c-a0ff-e5815a490d22-200] util.CheckUtil:29
: query is too lightweight with duration: 67 (threshold 2000), scan count: 0 (threshold 10240),
scan bytes: 0 (threshold 1048576)
2017-08-21 13:40:58,747 INFO  [Query eabab58a-47aa-454c-a0ff-e5815a490d22-200] service.QueryService:286
:
==========================[QUERY]===============================
Query Id: eabab58a-47aa-454c-a0ff-e5815a490d22
SQL: select part_dt, sum(price) as total_selled, count(distinct seller_id) as sellers from
kylin_sales where part_dt <= ? group by part_dt order by part_dt limit 100
User: ADMIN
Success: true
Duration: 0.067
Project: learn_kylin
Realization Names: [CUBE[name=kylin_sales_cube]]
Cuboid Ids: [16384]
Total scan count: 0
Total scan bytes: 0
Result row count: 0
Accept Partial: false
Is Partial Result: false
Hit Exception Cache: false
Storage cache used: false
Message: null
==========================[QUERY]===============================



________________________________
发件人: Billy Liu <billyliu@apache.org<mailto:billyliu@apache.org>>
发送时间: 2017年8月21日 13:14
收件人: user
主题: Re: No result from JDBC with Date filter in prepareStatement

THank you for reporting. Could you check the kylin.log first?

2017-08-21 11:15 GMT+08:00 程 万胜 <chivise.cn@hotmail.com<mailto:chivise.cn@hotmail.com>>:


大家好:

用kylin的JDBC查询,查询条件是date类型的,如果通过prepareStatement、setDate是查询不出数据的,然而直接用SQL可以查出数据

我用的是kylin版本:apache-kylin-1.6.0-cdh5.7-bin,jdbc版本:kylin-jdbc-1.6.0.jar,java版本:java
version "1.8.0_92"


代码1

        Driver driver = (Driver) Class.forName("org.apache.kylin.jdbc.Driver").newInstance();

        Properties info = new Properties();
        info.put("user", "xxx");
        info.put("password", "xxx");
        Connection conn = driver.connect("jdbc:kylin://xxx/learn_kylin", info);
        PreparedStatement state = conn.prepareStatement(
                "select " + "part_dt, sum(price) as total_selled, count(distinct seller_id)
as sellers "
                        + "from kylin_sales where part_dt <= '2013-01-01' " + "group by
part_dt " + "order by part_dt limit 100");

        ResultSet resultSet = state.executeQuery();
        System.out.print("结果:");
        while (resultSet.next()) {
            System.out.print(resultSet.getString(1) + "\n");
        }


运行结果

SLF4J: Failed to load class "org.slf4j.impl.StaticLoggerBinder".
SLF4J: Defaulting to no-operation (NOP) logger implementation
SLF4J: See http://www.slf4j.org/codes.html#StaticLoggerBinder for further details.
结果:2011-12-31
2012-01-01
2012-01-02
2012-01-03
2012-01-04
2012-01-05
2012-01-06
2012-01-07
2012-01-08
2012-01-09


代码2

        Driver driver = (Driver) Class.forName("org.apache.kylin.jdbc.Driver").newInstance();

        Properties info = new Properties();
        info.put("user", "xxx");
        info.put("password", "xxx");
        Connection conn = driver.connect("jdbc:kylin://xxx/learn_kylin", info);
        PreparedStatement state = conn.prepareStatement(
                "select " + "part_dt, sum(price) as total_selled, count(distinct seller_id)
as sellers "
                        + "from kylin_sales where part_dt <= ? " + "group by part_dt "
+ "order by part_dt limit 100");
        SimpleDateFormat simpleTime = new SimpleDateFormat("yyyy-MM-dd");
        java.util.Date passUtilDate = simpleTime.parse("2013-01-01");
        java.sql.Date passSqlDate = new java.sql.Date(passUtilDate.getTime());
        System.out.print("条件是:" + passSqlDate + "\n");
        state.setDate(1, passSqlDate);

        ResultSet resultSet = state.executeQuery();
        System.out.print("结果:");
        while (resultSet.next()) {
            System.out.print(resultSet.getString(1) + "\n");
        }


运行结果

SLF4J: Failed to load class "org.slf4j.impl.StaticLoggerBinder".
SLF4J: Defaulting to no-operation (NOP) logger implementation
SLF4J: See http://www.slf4j.org/codes.html#StaticLoggerBinder for further details.
条件是:2013-01-01
结果:






Mime
View raw message