You are viewing a plain text version of this content. The canonical link for it is here.
Posted to issues@drill.apache.org by "Rymar Maksym (Jira)" <ji...@apache.org> on 2020/07/03 10:35:00 UTC
[jira] [Issue Comment Deleted] (DRILL-7752) Query fails on hive
table with date column
[ https://issues.apache.org/jira/browse/DRILL-7752?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Rymar Maksym updated DRILL-7752:
--------------------------------
Comment: was deleted
(was: Source parquet file has incorrect data that are wrong processed by hive itself. Hive can't correctly processed data of this column as date. Since hive expect date in ANSI SQL format that is yyyy-mm-dd.
[https://cwiki.apache.org/confluence/display/Hive/LanguageManual+Types#LanguageManualTypes-date]
To proof this behavior need to make simple comparing of dates:
{code:java}
hive> select c_orders.o_orderdate[0] < '2020-01-01' from customer_complex limit 1;
OK
false
{code}
or cast to timestamp:
{code:java}
hive> select cast(c_orders.o_orderdate[0] as timestamp) from customer_complex limit 1;
OK
15361-09-06 00:00:000.0{code}
while cast to date returns:
{code:java}
hive> select cast(c_orders.o_orderdate[0] as date) from customer_complex limit 1;
OK
361-09-06
{code}
Drill in turn, expect exactly ANSI date format, so when he tries to parse that date, he throws exception, since in "361-09-06", year has format yyy. This behaivor respond design.
)
> Query fails on hive table with date column
> ------------------------------------------
>
> Key: DRILL-7752
> URL: https://issues.apache.org/jira/browse/DRILL-7752
> Project: Apache Drill
> Issue Type: Bug
> Components: Storage - Hive
> Affects Versions: 1.17.0
> Reporter: Rymar Maksym
> Assignee: Rymar Maksym
> Priority: Minor
> Attachments: customer_complex.zip
>
>
> *Steps to reproduce:*
> *1.* Create hive table:
> {code:java}
> create external table if not exists customer_complex (
> c_custkey int,
> c_name string,
> c_address string,
> c_nation struct<
> n_name:string,
> n_comment:string,
> n_region:map<string,string>>,
> c_phone string,
> c_acctbal double,
> c_mktsegment string,
> c_comment string,
> c_orders array<struct<
> o_orderstatus:string,
> o_totalprice:double,
> o_orderdate:date,
> o_orderpriority:string,
> o_clerk:string,
> o_shippriority:int,
> o_comment:string,
> o_lineitems:array<struct<
> l_part:struct<
> p_name:string,
> p_mfgr:string,
> p_brand:string,
> p_type:string,
> p_size:int,
> p_container:string,
> p_retailprice:double,
> p_comment:string>,
> l_supplier:struct<
> s_name:string,
> s_address:string,
> s_nationkey:int,
> s_phone:string,
> s_acctbal:double,
> s_comment:string>,
> l_linenumber:int,
> l_quantity:double,
> l_extendedprice:double,
> l_discount:double,
> l_tax:double,
> l_returnflag:string,
> l_linestatus:string,
> l_shipdate:date,
> l_commitdate:date,
> l_receiptdate:date,
> l_shipinstruct:string,
> l_shipmode:string,
> l_comment:string>>>>
> )
> STORED AS parquet
> LOCATION '/drill/customer_complex';
> {code}
> *2.* Add folder customer_complex. ZIP attached.
> *3.* Query hive table using Drill:
> {code:java}
> SELECT c_orders FROM hive.customer_complex;{code}
> Query fails with error:
> {code:java}
> org.apache.drill.common.exceptions.UserRemoteException: INTERNAL_ERROR ERROR: For input string: "361"
> Fragment: 0:0
> {code}
> Stack trace:
> {code:java}
> 2020-06-25 08:30:51,116 [210b3cd3-8532-fa13-9853-50f22d16be5f:foreman] INFO o.a.drill.exec.work.foreman.Foreman - Query text for query with id 210b3cd3-8532-fa13-9853-50f22d16be5f issued by anonymous: SELECT c_orders FROM hive.customer_complex
> 2020-06-25 08:30:51,444 [210b3cd3-8532-fa13-9853-50f22d16be5f:foreman] INFO o.a.drill.exec.store.PluginHandle - Creating storage plugin for hive
> 2020-06-25 08:30:52,669 [210b3cd3-8532-fa13-9853-50f22d16be5f:foreman] INFO o.a.drill.exec.store.PluginHandle - Creating storage plugin for dfs
> 2020-06-25 08:30:52,768 [210b3cd3-8532-fa13-9853-50f22d16be5f:foreman] INFO o.a.d.c.s.persistence.ScanResult - loading 18 classes for org.apache.drill.exec.store.dfs.FormatPlugin took 48ms
> 2020-06-25 08:30:52,854 [210b3cd3-8532-fa13-9853-50f22d16be5f:foreman] INFO o.a.d.c.s.persistence.ScanResult - loading 19 classes for org.apache.drill.common.logical.FormatPluginConfig took 0ms
> 2020-06-25 08:30:52,855 [210b3cd3-8532-fa13-9853-50f22d16be5f:foreman] INFO o.a.d.c.s.persistence.ScanResult - loading 19 classes for org.apache.drill.common.logical.FormatPluginConfig took 0ms
> 2020-06-25 08:30:52,856 [210b3cd3-8532-fa13-9853-50f22d16be5f:foreman] INFO o.a.d.c.s.persistence.ScanResult - loading 19 classes for org.apache.drill.common.logical.FormatPluginConfig took 0ms
> 2020-06-25 08:30:52,856 [210b3cd3-8532-fa13-9853-50f22d16be5f:foreman] INFO o.a.d.c.s.persistence.ScanResult - loading 19 classes for org.apache.drill.common.logical.FormatPluginConfig took 0ms
> 2020-06-25 08:30:52,856 [210b3cd3-8532-fa13-9853-50f22d16be5f:foreman] INFO o.a.d.c.s.persistence.ScanResult - loading 19 classes for org.apache.drill.common.logical.FormatPluginConfig took 0ms
> 2020-06-25 08:30:52,857 [210b3cd3-8532-fa13-9853-50f22d16be5f:foreman] INFO o.a.d.c.s.persistence.ScanResult - loading 19 classes for org.apache.drill.common.logical.FormatPluginConfig took 0ms
> 2020-06-25 08:30:52,857 [210b3cd3-8532-fa13-9853-50f22d16be5f:foreman] INFO o.a.drill.exec.store.PluginHandle - Creating storage plugin for maprdb
> 2020-06-25 08:30:53,888 [210b3cd3-8532-fa13-9853-50f22d16be5f:frag:0:0] INFO o.a.d.e.w.fragment.FragmentExecutor - 210b3cd3-8532-fa13-9853-50f22d16be5f:0:0: State change requested AWAITING_ALLOCATION --> RUNNING
> 2020-06-25 08:30:53,889 [210b3cd3-8532-fa13-9853-50f22d16be5f:frag:0:0] INFO o.a.d.e.w.f.FragmentStatusReporter - 210b3cd3-8532-fa13-9853-50f22d16be5f:0:0: State to report: RUNNING
> 2020-06-25 08:30:54,761 [210b3cd3-8532-fa13-9853-50f22d16be5f:frag:0:0] INFO o.a.d.exec.physical.impl.ScanBatch - User Error Occurred: For input string: "361" (For input string: "361")
> org.apache.drill.common.exceptions.UserException: INTERNAL_ERROR ERROR: For input string: "361"
> Please, refer to logs for more information.[Error Id: d6b8f2ba-f111-4205-8229-24d1739cd024 ]
> at org.apache.drill.common.exceptions.UserException$Builder.build(UserException.java:657)
> at org.apache.drill.exec.physical.impl.ScanBatch.next(ScanBatch.java:305)
> at org.apache.drill.exec.record.AbstractRecordBatch.next(AbstractRecordBatch.java:119)
> at org.apache.drill.exec.record.AbstractRecordBatch.next(AbstractRecordBatch.java:111)
> at org.apache.drill.exec.record.AbstractUnaryRecordBatch.innerNext(AbstractUnaryRecordBatch.java:59)
> at org.apache.drill.exec.physical.impl.project.ProjectRecordBatch.innerNext(ProjectRecordBatch.java:85)
> at org.apache.drill.exec.record.AbstractRecordBatch.next(AbstractRecordBatch.java:170)
> at org.apache.drill.exec.physical.impl.BaseRootExec.next(BaseRootExec.java:103)
> at org.apache.drill.exec.physical.impl.ScreenCreator$ScreenRoot.innerNext(ScreenCreator.java:81)
> at org.apache.drill.exec.physical.impl.BaseRootExec.next(BaseRootExec.java:93)
> at org.apache.drill.exec.work.fragment.FragmentExecutor$1.run(FragmentExecutor.java:323)
> at org.apache.drill.exec.work.fragment.FragmentExecutor$1.run(FragmentExecutor.java:310)
> at java.security.AccessController.doPrivileged(Native Method)
> at javax.security.auth.Subject.doAs(Subject.java:422)
> at org.apache.hadoop.security.UserGroupInformation.doAs(UserGroupInformation.java:1669)
> at org.apache.drill.exec.work.fragment.FragmentExecutor.run(FragmentExecutor.java:310)
> at org.apache.drill.common.SelfCleaningRunnable.run(SelfCleaningRunnable.java:38)
> at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149)
> at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624)
> at java.lang.Thread.run(Thread.java:748)
> Caused by: java.lang.NumberFormatException: For input string: "361"
> at java.lang.NumberFormatException.forInputString(NumberFormatException.java:65)
> at java.lang.Integer.parseInt(Integer.java:569)
> at java.lang.Integer.parseInt(Integer.java:615)
> at java.sql.Date.valueOf(Date.java:133)
> at org.apache.drill.exec.store.hive.writers.primitive.HiveDateWriter.write(HiveDateWriter.java:37)
> at org.apache.drill.exec.store.hive.writers.complex.HiveStructWriter.write(HiveStructWriter.java:50)
> at org.apache.drill.exec.store.hive.writers.complex.HiveListWriter.write(HiveListWriter.java:52)
> at org.apache.drill.exec.store.hive.readers.HiveDefaultRecordReader.readHiveRecordAndInsertIntoRecordBatch(HiveDefaultRecordReader.java:413)
> at org.apache.drill.exec.store.hive.readers.HiveDefaultRecordReader.next(HiveDefaultRecordReader.java:399)
> at org.apache.drill.exec.physical.impl.ScanBatch.internalNext(ScanBatch.java:234)
> at org.apache.drill.exec.physical.impl.ScanBatch.next(ScanBatch.java:298)
> ... 18 common frames omitted
> 2020-06-25 08:30:54,762 [210b3cd3-8532-fa13-9853-50f22d16be5f:frag:0:0] ERROR o.a.d.e.physical.impl.BaseRootExec - Batch dump started: dumping last 2 failed batches
> 2020-06-25 08:30:54,762 [210b3cd3-8532-fa13-9853-50f22d16be5f:frag:0:0] ERROR o.a.d.exec.physical.impl.ScanBatch - ScanBatch[container=org.apache.drill.exec.record.VectorContainer@143d73f6[recordCount = 0, schemaChanged = true, schema = null, wrappers = [org.apache.drill.exec.vector.complex.RepeatedMapVector@5578670e], ...], currentReader=org.apache.drill.exec.store.hive.readers.HiveDefaultRecordReader@6ce3b5e4[columns = [`c_orders`], isStarQuery = false, isSkipQuery = false], schema=null]
> 2020-06-25 08:30:54,762 [210b3cd3-8532-fa13-9853-50f22d16be5f:frag:0:0] ERROR o.a.d.e.p.i.p.ProjectRecordBatch - ProjectRecordBatch[projector=null, hasRemainder=false, remainderIndex=0, recordCount=0, container=org.apache.drill.exec.record.VectorContainer@793aa3d5[recordCount = 0, schemaChanged = true, schema = null, wrappers = [], ...]]
> 2020-06-25 08:30:54,762 [210b3cd3-8532-fa13-9853-50f22d16be5f:frag:0:0] ERROR o.a.d.e.physical.impl.BaseRootExec - Batch dump completed.
> 2020-06-25 08:30:54,762 [210b3cd3-8532-fa13-9853-50f22d16be5f:frag:0:0] INFO o.a.d.e.w.fragment.FragmentExecutor - 210b3cd3-8532-fa13-9853-50f22d16be5f:0:0: State change requested RUNNING --> FAILED
> 2020-06-25 08:30:54,765 [210b3cd3-8532-fa13-9853-50f22d16be5f:frag:0:0] INFO o.a.d.e.w.fragment.FragmentExecutor - 210b3cd3-8532-fa13-9853-50f22d16be5f:0:0: State change requested FAILED --> FINISHED
> 2020-06-25 08:30:54,780 [210b3cd3-8532-fa13-9853-50f22d16be5f:frag:0:0] WARN o.a.d.exec.rpc.control.WorkEventBus - Fragment 210b3cd3-8532-fa13-9853-50f22d16be5f:0:0 manager is not found in the work bus.
> 2020-06-25 08:30:54,814 [qtp1007076085-61] ERROR o.a.d.e.server.rest.QueryResources - Query from Web UI Failed: {}
> org.apache.drill.common.exceptions.UserRemoteException: INTERNAL_ERROR ERROR: For input string: "361"Fragment: 0:0Please, refer to logs for more information.[Error Id: d6b8f2ba-f111-4205-8229-24d1739cd024 on maprdemo:31010]
> at org.apache.drill.exec.server.rest.RestQueryRunner.submitQuery(RestQueryRunner.java:181)
> at org.apache.drill.exec.server.rest.RestQueryRunner.run(RestQueryRunner.java:70)
> at org.apache.drill.exec.server.rest.QueryResources.submitQueryJSON(QueryResources.java:96)
> at org.apache.drill.exec.server.rest.QueryResources.submitQuery(QueryResources.java:114)
> at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
> at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
> at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
> at java.lang.reflect.Method.invoke(Method.java:498)
> at org.glassfish.jersey.server.model.internal.ResourceMethodInvocationHandlerFactory$1.invoke(ResourceMethodInvocationHandlerFactory.java:81)
> at org.glassfish.jersey.server.model.internal.AbstractJavaResourceMethodDispatcher$1.run(AbstractJavaResourceMethodDispatcher.java:144)
> at org.glassfish.jersey.server.model.internal.AbstractJavaResourceMethodDispatcher.invoke(AbstractJavaResourceMethodDispatcher.java:161)
> at org.glassfish.jersey.server.model.internal.JavaResourceMethodDispatcherProvider$TypeOutInvoker.doDispatch(JavaResourceMethodDispatcherProvider.java:205)
> at org.glassfish.jersey.server.model.internal.AbstractJavaResourceMethodDispatcher.dispatch(AbstractJavaResourceMethodDispatcher.java:99)
> at org.glassfish.jersey.server.model.ResourceMethodInvoker.invoke(ResourceMethodInvoker.java:389)
> at org.glassfish.jersey.server.model.ResourceMethodInvoker.apply(ResourceMethodInvoker.java:347)
> at org.glassfish.jersey.server.model.ResourceMethodInvoker.apply(ResourceMethodInvoker.java:102)
> at org.glassfish.jersey.server.ServerRuntime$2.run(ServerRuntime.java:326)
> at org.glassfish.jersey.internal.Errors$1.call(Errors.java:271)
> at org.glassfish.jersey.internal.Errors$1.call(Errors.java:267)
> at org.glassfish.jersey.internal.Errors.process(Errors.java:315)
> at org.glassfish.jersey.internal.Errors.process(Errors.java:297)
> at org.glassfish.jersey.internal.Errors.process(Errors.java:267)
> at org.glassfish.jersey.process.internal.RequestScope.runInScope(RequestScope.java:317)
> at org.glassfish.jersey.server.ServerRuntime.process(ServerRuntime.java:305)
> at org.glassfish.jersey.server.ApplicationHandler.handle(ApplicationHandler.java:1154)
> at org.glassfish.jersey.servlet.WebComponent.serviceImpl(WebComponent.java:473)
> at org.glassfish.jersey.servlet.WebComponent.service(WebComponent.java:427)
> at org.glassfish.jersey.servlet.ServletContainer.service(ServletContainer.java:388)
> at org.glassfish.jersey.servlet.ServletContainer.service(ServletContainer.java:341)
> at org.glassfish.jersey.servlet.ServletContainer.service(ServletContainer.java:228)
> at org.eclipse.jetty.servlet.ServletHolder.handle(ServletHolder.java:848)
> at org.eclipse.jetty.servlet.ServletHandler$CachedChain.doFilter(ServletHandler.java:1780)
> at org.apache.drill.exec.server.rest.header.ResponseHeadersSettingFilter.doFilter(ResponseHeadersSettingFilter.java:71)
> at org.eclipse.jetty.servlet.ServletHandler$CachedChain.doFilter(ServletHandler.java:1767)
> at org.apache.drill.exec.server.rest.CsrfTokenValidateFilter.doFilter(CsrfTokenValidateFilter.java:55)
> at org.eclipse.jetty.servlet.ServletHandler$CachedChain.doFilter(ServletHandler.java:1767)
> at org.apache.drill.exec.server.rest.CsrfTokenInjectFilter.doFilter(CsrfTokenInjectFilter.java:54)
> at org.eclipse.jetty.servlet.ServletHandler$CachedChain.doFilter(ServletHandler.java:1767)
> at org.eclipse.jetty.servlet.ServletHandler.doHandle(ServletHandler.java:583)
> at org.eclipse.jetty.server.session.SessionHandler.doHandle(SessionHandler.java:224)
> at org.eclipse.jetty.server.handler.ContextHandler.doHandle(ContextHandler.java:1180)
> at org.eclipse.jetty.servlet.ServletHandler.doScope(ServletHandler.java:513)
> at org.eclipse.jetty.server.session.SessionHandler.doScope(SessionHandler.java:185)
> at org.eclipse.jetty.server.handler.ContextHandler.doScope(ContextHandler.java:1112)
> at org.eclipse.jetty.server.handler.ScopedHandler.handle(ScopedHandler.java:141)
> at org.eclipse.jetty.server.handler.HandlerWrapper.handle(HandlerWrapper.java:134)
> at org.eclipse.jetty.server.Server.handle(Server.java:539)
> at org.eclipse.jetty.server.HttpChannel.handle(HttpChannel.java:333)
> at org.eclipse.jetty.server.HttpConnection.onFillable(HttpConnection.java:251)
> at org.eclipse.jetty.io.AbstractConnection$ReadCallback.succeeded(AbstractConnection.java:283)
> at org.eclipse.jetty.io.FillInterest.fillable(FillInterest.java:108)
> at org.eclipse.jetty.io.SelectChannelEndPoint$2.run(SelectChannelEndPoint.java:93)
> at org.eclipse.jetty.util.thread.strategy.ExecuteProduceConsume.executeProduceConsume(ExecuteProduceConsume.java:303)
> at org.eclipse.jetty.util.thread.strategy.ExecuteProduceConsume.produceConsume(ExecuteProduceConsume.java:148)
> at org.eclipse.jetty.util.thread.strategy.ExecuteProduceConsume.run(ExecuteProduceConsume.java:136)
> at org.eclipse.jetty.util.thread.QueuedThreadPool.runJob(QueuedThreadPool.java:671)
> at org.eclipse.jetty.util.thread.QueuedThreadPool$2.run(QueuedThreadPool.java:589)
> at java.lang.Thread.run(Thread.java:748)
> Caused by: java.lang.NumberFormatException: For input string: "361"
> at .......(:0)
> at org.apache.drill.exec.store.hive.writers.primitive.HiveDateWriter.write(HiveDateWriter.java:37)
> at org.apache.drill.exec.store.hive.writers.complex.HiveStructWriter.write(HiveStructWriter.java:50)
> at org.apache.drill.exec.store.hive.writers.complex.HiveListWriter.write(HiveListWriter.java:52)
> at org.apache.drill.exec.store.hive.readers.HiveDefaultRecordReader.readHiveRecordAndInsertIntoRecordBatch(HiveDefaultRecordReader.java:413)
> at org.apache.drill.exec.store.hive.readers.HiveDefaultRecordReader.next(HiveDefaultRecordReader.java:399)
> at org.apache.drill.exec.physical.impl.ScanBatch.internalNext(ScanBatch.java:234)
> at org.apache.drill.exec.physical.impl.ScanBatch.next(ScanBatch.java:298)
> at org.apache.drill.exec.record.AbstractRecordBatch.next(AbstractRecordBatch.java:119)
> at org.apache.drill.exec.record.AbstractRecordBatch.next(AbstractRecordBatch.java:111)
> at org.apache.drill.exec.record.AbstractUnaryRecordBatch.innerNext(AbstractUnaryRecordBatch.java:59)
> at org.apache.drill.exec.physical.impl.project.ProjectRecordBatch.innerNext(ProjectRecordBatch.java:85)
> at org.apache.drill.exec.record.AbstractRecordBatch.next(AbstractRecordBatch.java:170)
> at org.apache.drill.exec.physical.impl.BaseRootExec.next(BaseRootExec.java:103)
> at org.apache.drill.exec.physical.impl.ScreenCreator$ScreenRoot.innerNext(ScreenCreator.java:81)
> at org.apache.drill.exec.physical.impl.BaseRootExec.next(BaseRootExec.java:93)
> at org.apache.drill.exec.work.fragment.FragmentExecutor$1.run(FragmentExecutor.java:323)
> at org.apache.drill.exec.work.fragment.FragmentExecutor$1.run(FragmentExecutor.java:310)
> at .......(:0)
> at org.apache.hadoop.security.UserGroupInformation.doAs(UserGroupInformation.java:1669)
> at org.apache.drill.exec.work.fragment.FragmentExecutor.run(FragmentExecutor.java:310)
> at org.apache.drill.common.SelfCleaningRunnable.run(SelfCleaningRunnable.java:38)
> at .......(:0)
> 2020-06-25 08:30:54,946 [210b3cd3-8532-fa13-9853-50f22d16be5f:frag:0:0] WARN o.a.d.e.w.f.QueryStateProcessor - Dropping request to move to COMPLETED state as query is already at FAILED state (which is terminal).{code}
--
This message was sent by Atlassian Jira
(v8.3.4#803005)