[jira] [Commented] (HIVE-26778) Pushdown Date data type to metastore via direct sql / JDO

["katty he (Jira)" <ji...@apache.org>]

    [ https://issues.apache.org/jira/browse/HIVE-26778?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17838444#comment-17838444 ] 

katty he commented on HIVE-26778:
---------------------------------

it seems like, this patch will cause spark runs error , for example, we create a table 

{panel:title=My title}
  CREATE EXTERNAL TABLE `sample.tmts_grey_device_log_bak`(
  `name` string COMMENT '', 
  
  `authcookie` string COMMENT '')
PARTITIONED BY ( 
  `date` string, 
  `hour` string)
{panel}

and then query with spark , 

{panel:title=My title}
select
  *
from
  sample.tmts_grey_device_log_bak
where
  `date` = '2022-01-01' 
  limit 5;
{panel}

the error is 
Caused by: MetaException(message:Error parsing partition filter; lexer error: null; exception NoViableAltException(17@[]))
	at org.apache.hadoop.hive.metastore.api.ThriftHiveMetastore$get_partitions_by_filter_result$get_partitions_by_filter_resultStandardScheme.read(ThriftHiveMetastore.java)
	at org.apache.hadoop.hive.metastore.api.ThriftHiveMetastore$get_partitions_by_filter_result$get_partitions_by_filter_resultStandardScheme.read(ThriftHiveMetastore.java)
	at org.apache.hadoop.hive.metastore.api.ThriftHiveMetastore$get_partitions_by_filter_result.read(ThriftHiveMetastore.java)
	at org.apache.thrift.TServiceClient.receiveBase(TServiceClient.java:86)
	at org.apache.hadoop.hive.metastore.api.ThriftHiveMetastore$Client.recv_get_partitions_by_filter(ThriftHiveMetastore.java:3212)
	at org.apache.hadoop.hive.metastore.api.ThriftHiveMetastore$Client.get_partitions_by_filter(ThriftHiveMetastore.java:3196)
	at org.apache.hadoop.hive.metastore.HiveMetaStoreClient.listPartitionsByFilter(HiveMetaStoreClient.java:1470)
	at org.apache.hadoop.hive.metastore.HiveMetaStoreClient.listPartitionsByFilter(HiveMetaStoreClient.java:1464)
	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.apache.hadoop.hive.metastore.RetryingMetaStoreClient.invoke(RetryingMetaStoreClient.java:212)
	at com.sun.proxy.$Proxy49.listPartitionsByFilter(Unknown Source)
	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.apache.hadoop.hive.metastore.HiveMetaStoreClient$SynchronizedHandler.invoke(HiveMetaStoreClient.java:2799)
	at com.sun.proxy.$Proxy49.listPartitionsByFilter(Unknown Source)
	at org.apache.hadoop.hive.ql.metadata.Hive.getPartitionsByFilter(Hive.java:3143)
	... 98 more

when i do not use this patch, it runs successfully.

> Pushdown Date data type to metastore via direct sql / JDO
> ---------------------------------------------------------
>
>                 Key: HIVE-26778
>                 URL: https://issues.apache.org/jira/browse/HIVE-26778
>             Project: Hive
>          Issue Type: Bug
>          Components: Standalone Metastore
>    Affects Versions: 4.0.0
>            Reporter: Syed Shameerur Rahman
>            Assignee: Syed Shameerur Rahman
>            Priority: Major
>              Labels: pull-request-available
>          Time Spent: 2h 50m
>  Remaining Estimate: 0h
>
> The original feature to push down date data type while doing partition pruning via direct sql/JDO was added as part of the jira : https://issues.apache.org/jira/browse/HIVE-5679
> Since the behavior of Hive has changed with CBO, Now when CBO is turned on, The date data types are not pushed down to metastore due to CBO adding extra keyword 'DATE' with the original filter since the filter parser is not handled to parse this extra keyword it fails and hence the date data type is not pushed down to the metastore.
> {code:java}
> select * from test_table where date_col = '2022-01-01';
> {code}
> When CBO is turned on, The filter predicate generated is date_col=DATE'2022-01-01' which the filter parser fails to recognize.
>  
> *Steps to reproduce*
> The following query will generate "{color:#6a8759}Error parsing partition filter; lexer error" [https://github.com/apache/hive/blob/master/standalone-metastore/metastore-server/src/main/java/org/apache/hadoop/hive/metastore/PartFilterExprUtil.java#L128]{color}
>  
> {color:#172b4d}When CBO is turned off (set hive.cbo.enable=false) we don't see this error message.{color}
> {code:java}
> create table part_time(a int) partitioned by(date_c date);
> insert into part_time partition(date_c='2000-01-01') values (1);
> insert into part_time partition(date_c='2000-02-01') values (1);
> insert into part_time partition(date_c='2000-03-01') values (1); 
> select * from part_time where date_c = '2000-03-01';
> {code}
>  
> *Performance Improvement*
> In my testing setup of table having *10k partitions* in the table. When we do a select query on one of the partitions without the change it was *300 ms* and after the change it was {*}14 ms{*}.



--
This message was sent by Atlassian Jira
(v8.20.10#820010)