mayankshriv commented on issue #3626: timezone problem URL: https://github.com/apache/incubator-pinot/issues/3626#issuecomment-450568138 Thanks for sharing the information. From the second result it seems the query selects 1.2B records, which means too much computation needs to happen. Do you really need to store the data in milliseconds granularity? If not, preaggregating offline data after with larger granularity will reduce data size, and hence the the num records to process. May I ask which company/organization is this use case for? Thanks Mayank Get Outlook for iOS<https://aka.ms/o0ukef> ________________________________ From: Sun-Li <[email protected]> Sent: Sunday, December 30, 2018 1:20 AM To: apache/incubator-pinot Cc: Mayank Shrivastava; Comment Subject: Re: [apache/incubator-pinot] timezone problem (#3626) The second statement has EPOCH (which is UTC). Statement 1 is the only way to specify time zone. Seems like you don't have a filter in your query, which means you are essentially applying the transform on all the records. How many records do are there in this table? What is the query latency you are seeing? Also, is this a test query or a real query that you plan to run in production (ie without any filters)? yes，For a real query running in a production environment, such as this table video_market-video_download_performance_full_day, about 1.5g of data per day, querying 30 days of data, a total of 1237838944 rows of data, executing pq1 (server query timeout) and pql2 takes the following results pq1: select sum(total_count) from video_market-video_download_performance_full_day where fdate >= 1543593600000 and fdate < 1546271999000 group by dateTimeConvert(fdate, '1:MILLISECONDS:EPOCH','1:MILLISECONDS:SIMPLE_DATE_FORMAT:yyyy-MM-dd HH:mm:ss tz(Asia/Shanghai)', '1:DAYS') { "exceptions":[ { "errorCode":200, "message":"QueryExecutionError: java.util.concurrent.TimeoutException: Timed out while combining group-by results after 14999ms at com.linkedin.pinot.core.operator.CombineGroupByOperator.getNextBlock(CombineGroupByOperator.java:168) at com.linkedin.pinot.core.operator.CombineGroupByOperator.getNextBlock(CombineGroupByOperator.java:50) at com.linkedin.pinot.core.operator.BaseOperator.nextBlock(BaseOperator.java:42) at com.linkedin.pinot.core.operator.InstanceResponseOperator.getNextBlock(InstanceResponseOperator.java:34) at com.linkedin.pinot.core.operator.InstanceResponseOperator.getNextBlock(InstanceResponseOperator.java:23) at com.linkedin.pinot.core.operator.BaseOperator.nextBlock(BaseOperator.java:42) at com.linkedin.pinot.core.plan.GlobalPlanImplV0.execute(GlobalPlanImplV0.java:45) at com.linkedin.pinot.core.query.executor.ServerQueryExecutorV1Impl.processQuery(ServerQueryExecutorV1Impl.java:159) at com.linkedin.pinot.core.query.scheduler.QueryScheduler.processQueryAndSerialize(QueryScheduler.java:127) at com.linkedin.pinot.core.query.scheduler.QueryScheduler.lambda$createQueryFutureTask$0(QueryScheduler.java:112) at java.util.concurrent.FutureTask.run(FutureTask.java:266) at java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:511) at com.google.common.util.concurrent.TrustedListenableFutureTask$TrustedFutureInterruptibleTask.runInterruptibly(TrustedListenableFutureTask.java:108) at com.google.common.util.concurrent.InterruptibleTask.run(InterruptibleTask.java:41)" }, { "errorCode":200, "message":"QueryExecutionError: java.util.concurrent.TimeoutException: Timed out while combining group-by results after 15000ms at com.linkedin.pinot.core.operator.CombineGroupByOperator.getNextBlock(CombineGroupByOperator.java:168) at com.linkedin.pinot.core.operator.CombineGroupByOperator.getNextBlock(CombineGroupByOperator.java:50) at com.linkedin.pinot.core.operator.BaseOperator.nextBlock(BaseOperator.java:42) at com.linkedin.pinot.core.operator.InstanceResponseOperator.getNextBlock(InstanceResponseOperator.java:34) at com.linkedin.pinot.core.operator.InstanceResponseOperator.getNextBlock(InstanceResponseOperator.java:23) at com.linkedin.pinot.core.operator.BaseOperator.nextBlock(BaseOperator.java:42) at com.linkedin.pinot.core.plan.GlobalPlanImplV0.execute(GlobalPlanImplV0.java:45) at com.linkedin.pinot.core.query.executor.ServerQueryExecutorV1Impl.processQuery(ServerQueryExecutorV1Impl.java:159) at com.linkedin.pinot.core.query.scheduler.QueryScheduler.processQueryAndSerialize(QueryScheduler.java:127) at com.linkedin.pinot.core.query.scheduler.QueryScheduler.lambda$createQueryFutureTask$0(QueryScheduler.java:112) at java.util.concurrent.FutureTask.run(FutureTask.java:266) at java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:511) at com.google.common.util.concurrent.TrustedListenableFutureTask$TrustedFutureInterruptibleTask.runInterruptibly(TrustedListenableFutureTask.java:108) at com.google.common.util.concurrent.InterruptibleTask.run(InterruptibleTask.java:41)" }, { "errorCode":200, "message":"QueryExecutionError: java.util.concurrent.TimeoutException: Timed out while combining group-by results after 14999ms at com.linkedin.pinot.core.operator.CombineGroupByOperator.getNextBlock(CombineGroupByOperator.java:168) at com.linkedin.pinot.core.operator.CombineGroupByOperator.getNextBlock(CombineGroupByOperator.java:50) at com.linkedin.pinot.core.operator.BaseOperator.nextBlock(BaseOperator.java:42) at com.linkedin.pinot.core.operator.InstanceResponseOperator.getNextBlock(InstanceResponseOperator.java:34) at com.linkedin.pinot.core.operator.InstanceResponseOperator.getNextBlock(InstanceResponseOperator.java:23) at com.linkedin.pinot.core.operator.BaseOperator.nextBlock(BaseOperator.java:42) at com.linkedin.pinot.core.plan.GlobalPlanImplV0.execute(GlobalPlanImplV0.java:45) at com.linkedin.pinot.core.query.executor.ServerQueryExecutorV1Impl.processQuery(ServerQueryExecutorV1Impl.java:159) at com.linkedin.pinot.core.query.scheduler.QueryScheduler.processQueryAndSerialize(QueryScheduler.java:127) at com.linkedin.pinot.core.query.scheduler.QueryScheduler.lambda$createQueryFutureTask$0(QueryScheduler.java:112) at java.util.concurrent.FutureTask.run(FutureTask.java:266) at java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:511) at com.google.common.util.concurrent.TrustedListenableFutureTask$TrustedFutureInterruptibleTask.runInterruptibly(TrustedListenableFutureTask.java:108) at com.google.common.util.concurrent.InterruptibleTask.run(InterruptibleTask.java:41)" }, { "errorCode":200, "message":"QueryExecutionError: java.util.concurrent.TimeoutException: Timed out while combining group-by results after 15000ms at com.linkedin.pinot.core.operator.CombineGroupByOperator.getNextBlock(CombineGroupByOperator.java:168) at com.linkedin.pinot.core.operator.CombineGroupByOperator.getNextBlock(CombineGroupByOperator.java:50) at com.linkedin.pinot.core.operator.BaseOperator.nextBlock(BaseOperator.java:42) at com.linkedin.pinot.core.operator.InstanceResponseOperator.getNextBlock(InstanceResponseOperator.java:34) at com.linkedin.pinot.core.operator.InstanceResponseOperator.getNextBlock(InstanceResponseOperator.java:23) at com.linkedin.pinot.core.operator.BaseOperator.nextBlock(BaseOperator.java:42) at com.linkedin.pinot.core.plan.GlobalPlanImplV0.execute(GlobalPlanImplV0.java:45) at com.linkedin.pinot.core.query.executor.ServerQueryExecutorV1Impl.processQuery(ServerQueryExecutorV1Impl.java:159) at com.linkedin.pinot.core.query.scheduler.QueryScheduler.processQueryAndSerialize(QueryScheduler.java:127) at com.linkedin.pinot.core.query.scheduler.QueryScheduler.lambda$createQueryFutureTask$0(QueryScheduler.java:112) at java.util.concurrent.FutureTask.run(FutureTask.java:266) at java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:511) at com.google.common.util.concurrent.TrustedListenableFutureTask$TrustedFutureInterruptibleTask.runInterruptibly(TrustedListenableFutureTask.java:108) at com.google.common.util.concurrent.InterruptibleTask.run(InterruptibleTask.java:41)" }, { "errorCode":200, "message":"QueryExecutionError: java.util.concurrent.TimeoutException: Timed out while combining group-by results after 15000ms at com.linkedin.pinot.core.operator.CombineGroupByOperator.getNextBlock(CombineGroupByOperator.java:168) at com.linkedin.pinot.core.operator.CombineGroupByOperator.getNextBlock(CombineGroupByOperator.java:50) at com.linkedin.pinot.core.operator.BaseOperator.nextBlock(BaseOperator.java:42) at com.linkedin.pinot.core.operator.InstanceResponseOperator.getNextBlock(InstanceResponseOperator.java:34) at com.linkedin.pinot.core.operator.InstanceResponseOperator.getNextBlock(InstanceResponseOperator.java:23) at com.linkedin.pinot.core.operator.BaseOperator.nextBlock(BaseOperator.java:42) at com.linkedin.pinot.core.plan.GlobalPlanImplV0.execute(GlobalPlanImplV0.java:45) at com.linkedin.pinot.core.query.executor.ServerQueryExecutorV1Impl.processQuery(ServerQueryExecutorV1Impl.java:159) at com.linkedin.pinot.core.query.scheduler.QueryScheduler.processQueryAndSerialize(QueryScheduler.java:127) at com.linkedin.pinot.core.query.scheduler.QueryScheduler.lambda$createQueryFutureTask$0(QueryScheduler.java:112) at java.util.concurrent.FutureTask.run(FutureTask.java:266) at java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:511) at com.google.common.util.concurrent.TrustedListenableFutureTask$TrustedFutureInterruptibleTask.runInterruptibly(TrustedListenableFutureTask.java:108) at com.google.common.util.concurrent.InterruptibleTask.run(InterruptibleTask.java:41)" } ], "numServersQueried":6, "numServersResponded":6, "numDocsScanned":170287136, "numEntriesScannedInFilter":340574272, "numEntriesScannedPostFilter":340574272, "totalDocs":1237838944, "numGroupsLimitReached":false, "timeUsedMs":35737, "segmentStatistics":[ ], "traceInfo":{ } } pq2: select sum(total_count) from video_market-video_download_performance_full_day where fdate >= 1543593600000 and fdate < 1546271999000 group by dateTimeConvert(fdate, '1:MILLISECONDS:EPOCH','1:MILLISECONDS:EPOCH', '1:DAYS') { "aggregationResults": [ { "groupByResult": [ { "value": "5214728856.00000", "group": ["1543622400000"] }, { "value": "5146017730.00000", "group": ["1544227200000"] }, { "value": "4950169463.00000", "group": ["1545868800000"] }, { "value": "4912167135.00000", "group": ["1545696000000"] }, { "value": "4894003737.00000", "group": ["1545436800000"] }, { "value": "4882993768.00000", "group": ["1545782400000"] }, { "value": "4612845413.00000", "group": ["1544140800000"] }, { "value": "4612000264.00000", "group": ["1543536000000"] }, { "value": "4581314177.00000", "group": ["1544832000000"] }, { "value": "4510667574.00000", "group": ["1545609600000"] }], "function": "sum_total_count", "groupByColumns": ["datetimeconvert(fdate,'1:MILLISECONDS:EPOCH','1:MILLISECONDS:EPOCH','1:DAYS')"] }], "exceptions": [], "numServersQueried": 6, "numServersResponded": 6, "numDocsScanned": 1074252463, "numEntriesScannedInFilter": 2148504926, "numEntriesScannedPostFilter": 2148504926, "totalDocs": 1237838944, "numGroupsLimitReached": false, "timeUsedMs": 5750, "segmentStatistics": [], "traceInfo": {} } — You are receiving this because you commented. Reply to this email directly, view it on GitHub<https://nam06.safelinks.protection.outlook.com/?url=https%3A%2F%2Fgithub.com%2Fapache%2Fincubator-pinot%2Fissues%2F3626%23issuecomment-450548401&data=02%7C01%7Cmshrivas%40linkedin.com%7Cd84ece31ced44fc0a14108d66e381ae0%7C72f988bf86f141af91ab2d7cd011db47%7C1%7C0%7C636817584591197552&sdata=U3yDoAGb8hcKmfFWqUs2Fetu0QmwXOjPo6VJ2W2SL0g%3D&reserved=0>, or mute the thread<https://nam06.safelinks.protection.outlook.com/?url=https%3A%2F%2Fgithub.com%2Fnotifications%2Funsubscribe-auth%2FAMCAzaxbJsB3Ndv3tCzTMp_66FAL1r8Rks5u-IVwgaJpZM4Zkbf0&data=02%7C01%7Cmshrivas%40linkedin.com%7Cd84ece31ced44fc0a14108d66e381ae0%7C72f988bf86f141af91ab2d7cd011db47%7C1%7C0%7C636817584591207557&sdata=sAj468xEB0RFyKR6L39zyIVRZATnqjyJflAWLJroXoo%3D&reserved=0>.
---------------------------------------------------------------- This is an automated message from the Apache Git Service. To respond to the message, please log on GitHub and use the URL above to go to the specific comment. For queries about this service, please contact Infrastructure at: [email protected] With regards, Apache Git Services --------------------------------------------------------------------- To unsubscribe, e-mail: [email protected] For additional commands, e-mail: [email protected]
