HIVE-14579: Add support for date extract (Jesus Camacho Rodriguez, reviewed by Ashutosh Chauhan)
Project: http://git-wip-us.apache.org/repos/asf/hive/repo Commit: http://git-wip-us.apache.org/repos/asf/hive/commit/da376eba Tree: http://git-wip-us.apache.org/repos/asf/hive/tree/da376eba Diff: http://git-wip-us.apache.org/repos/asf/hive/diff/da376eba Branch: refs/heads/hive-14535 Commit: da376eba44ab090d693c2f9eb3f94ade04942b52 Parents: e532549 Author: Jesus Camacho Rodriguez <jcama...@apache.org> Authored: Fri Sep 23 10:28:45 2016 +0100 Committer: Jesus Camacho Rodriguez <jcama...@apache.org> Committed: Fri Sep 23 10:28:45 2016 +0100 ---------------------------------------------------------------------- .../org/apache/hadoop/hive/ql/parse/HiveLexer.g | 1 + .../hadoop/hive/ql/parse/IdentifiersParser.g | 23 +++ ql/src/test/queries/clientpositive/extract.q | 43 ++++ .../test/results/clientpositive/extract.q.out | 200 +++++++++++++++++++ 4 files changed, 267 insertions(+) ---------------------------------------------------------------------- http://git-wip-us.apache.org/repos/asf/hive/blob/da376eba/ql/src/java/org/apache/hadoop/hive/ql/parse/HiveLexer.g ---------------------------------------------------------------------- diff --git a/ql/src/java/org/apache/hadoop/hive/ql/parse/HiveLexer.g b/ql/src/java/org/apache/hadoop/hive/ql/parse/HiveLexer.g index af659ad..b623187 100644 --- a/ql/src/java/org/apache/hadoop/hive/ql/parse/HiveLexer.g +++ b/ql/src/java/org/apache/hadoop/hive/ql/parse/HiveLexer.g @@ -331,6 +331,7 @@ KW_RELY: 'RELY'; KW_NORELY: 'NORELY'; KW_KEY: 'KEY'; KW_ABORT: 'ABORT'; +KW_EXTRACT: 'EXTRACT'; KW_FLOOR: 'FLOOR'; // Operators http://git-wip-us.apache.org/repos/asf/hive/blob/da376eba/ql/src/java/org/apache/hadoop/hive/ql/parse/IdentifiersParser.g ---------------------------------------------------------------------- diff --git a/ql/src/java/org/apache/hadoop/hive/ql/parse/IdentifiersParser.g b/ql/src/java/org/apache/hadoop/hive/ql/parse/IdentifiersParser.g index 7842d50..e6b70a0 100644 --- a/ql/src/java/org/apache/hadoop/hive/ql/parse/IdentifiersParser.g +++ b/ql/src/java/org/apache/hadoop/hive/ql/parse/IdentifiersParser.g @@ -265,6 +265,28 @@ floorDateQualifiers | KW_SECOND -> Identifier["floor_second"] ; +extractExpression + : + KW_EXTRACT + LPAREN + (timeUnit=timeQualifiers) + KW_FROM + expression + RPAREN -> ^(TOK_FUNCTION $timeUnit expression) + ; + +timeQualifiers + : + KW_YEAR -> Identifier["year"] + | KW_QUARTER -> Identifier["quarter"] + | KW_MONTH -> Identifier["month"] + | KW_WEEK -> Identifier["weekofyear"] + | KW_DAY -> Identifier["day"] + | KW_HOUR -> Identifier["hour"] + | KW_MINUTE -> Identifier["minute"] + | KW_SECOND -> Identifier["second"] + ; + constant @init { gParent.pushMsg("constant", state); } @after { gParent.popMsg(state); } @@ -347,6 +369,7 @@ atomExpression (KW_NULL) => KW_NULL -> TOK_NULL | (constant) => constant | castExpression + | extractExpression | floorExpression | caseExpression | whenExpression http://git-wip-us.apache.org/repos/asf/hive/blob/da376eba/ql/src/test/queries/clientpositive/extract.q ---------------------------------------------------------------------- diff --git a/ql/src/test/queries/clientpositive/extract.q b/ql/src/test/queries/clientpositive/extract.q new file mode 100644 index 0000000..c09574b --- /dev/null +++ b/ql/src/test/queries/clientpositive/extract.q @@ -0,0 +1,43 @@ +drop table extract_udf; + +create table extract_udf (t timestamp); +from (select * from src tablesample (1 rows)) s + insert overwrite table extract_udf + select '2011-05-06 07:08:09.1234567'; + +explain +select day(t) +from extract_udf; + +select day(t) +from extract_udf; + +-- new syntax +explain +select extract(day from t) +from extract_udf; + +select extract(day from t) +from extract_udf; + + +select extract(second from t) +from extract_udf; + +select extract(minute from t) +from extract_udf; + +select extract(hour from t) +from extract_udf; + +select extract(week from t) +from extract_udf; + +select extract(month from t) +from extract_udf; + +select extract(quarter from t) +from extract_udf; + +select extract(year from t) +from extract_udf; http://git-wip-us.apache.org/repos/asf/hive/blob/da376eba/ql/src/test/results/clientpositive/extract.q.out ---------------------------------------------------------------------- diff --git a/ql/src/test/results/clientpositive/extract.q.out b/ql/src/test/results/clientpositive/extract.q.out new file mode 100644 index 0000000..73c9bc6 --- /dev/null +++ b/ql/src/test/results/clientpositive/extract.q.out @@ -0,0 +1,200 @@ +PREHOOK: query: drop table extract_udf +PREHOOK: type: DROPTABLE +POSTHOOK: query: drop table extract_udf +POSTHOOK: type: DROPTABLE +PREHOOK: query: create table extract_udf (t timestamp) +PREHOOK: type: CREATETABLE +PREHOOK: Output: database:default +PREHOOK: Output: default@extract_udf +POSTHOOK: query: create table extract_udf (t timestamp) +POSTHOOK: type: CREATETABLE +POSTHOOK: Output: database:default +POSTHOOK: Output: default@extract_udf +PREHOOK: query: from (select * from src tablesample (1 rows)) s + insert overwrite table extract_udf + select '2011-05-06 07:08:09.1234567' +PREHOOK: type: QUERY +PREHOOK: Input: default@src +PREHOOK: Output: default@extract_udf +POSTHOOK: query: from (select * from src tablesample (1 rows)) s + insert overwrite table extract_udf + select '2011-05-06 07:08:09.1234567' +POSTHOOK: type: QUERY +POSTHOOK: Input: default@src +POSTHOOK: Output: default@extract_udf +POSTHOOK: Lineage: extract_udf.t EXPRESSION [] +PREHOOK: query: explain +select day(t) +from extract_udf +PREHOOK: type: QUERY +POSTHOOK: query: explain +select day(t) +from extract_udf +POSTHOOK: type: QUERY +STAGE DEPENDENCIES: + Stage-1 is a root stage + Stage-0 depends on stages: Stage-1 + +STAGE PLANS: + Stage: Stage-1 + Map Reduce + Map Operator Tree: + TableScan + alias: extract_udf + Statistics: Num rows: 1 Data size: 27 Basic stats: COMPLETE Column stats: NONE + Select Operator + expressions: day(t) (type: int) + outputColumnNames: _col0 + Statistics: Num rows: 1 Data size: 27 Basic stats: COMPLETE Column stats: NONE + File Output Operator + compressed: false + Statistics: Num rows: 1 Data size: 27 Basic stats: COMPLETE Column stats: NONE + table: + input format: org.apache.hadoop.mapred.SequenceFileInputFormat + output format: org.apache.hadoop.hive.ql.io.HiveSequenceFileOutputFormat + serde: org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe + + Stage: Stage-0 + Fetch Operator + limit: -1 + Processor Tree: + ListSink + +PREHOOK: query: select day(t) +from extract_udf +PREHOOK: type: QUERY +PREHOOK: Input: default@extract_udf +#### A masked pattern was here #### +POSTHOOK: query: select day(t) +from extract_udf +POSTHOOK: type: QUERY +POSTHOOK: Input: default@extract_udf +#### A masked pattern was here #### +6 +PREHOOK: query: -- new syntax +explain +select extract(day from t) +from extract_udf +PREHOOK: type: QUERY +POSTHOOK: query: -- new syntax +explain +select extract(day from t) +from extract_udf +POSTHOOK: type: QUERY +STAGE DEPENDENCIES: + Stage-1 is a root stage + Stage-0 depends on stages: Stage-1 + +STAGE PLANS: + Stage: Stage-1 + Map Reduce + Map Operator Tree: + TableScan + alias: extract_udf + Statistics: Num rows: 1 Data size: 27 Basic stats: COMPLETE Column stats: NONE + Select Operator + expressions: day(t) (type: int) + outputColumnNames: _col0 + Statistics: Num rows: 1 Data size: 27 Basic stats: COMPLETE Column stats: NONE + File Output Operator + compressed: false + Statistics: Num rows: 1 Data size: 27 Basic stats: COMPLETE Column stats: NONE + table: + input format: org.apache.hadoop.mapred.SequenceFileInputFormat + output format: org.apache.hadoop.hive.ql.io.HiveSequenceFileOutputFormat + serde: org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe + + Stage: Stage-0 + Fetch Operator + limit: -1 + Processor Tree: + ListSink + +PREHOOK: query: select extract(day from t) +from extract_udf +PREHOOK: type: QUERY +PREHOOK: Input: default@extract_udf +#### A masked pattern was here #### +POSTHOOK: query: select extract(day from t) +from extract_udf +POSTHOOK: type: QUERY +POSTHOOK: Input: default@extract_udf +#### A masked pattern was here #### +6 +PREHOOK: query: select extract(second from t) +from extract_udf +PREHOOK: type: QUERY +PREHOOK: Input: default@extract_udf +#### A masked pattern was here #### +POSTHOOK: query: select extract(second from t) +from extract_udf +POSTHOOK: type: QUERY +POSTHOOK: Input: default@extract_udf +#### A masked pattern was here #### +9 +PREHOOK: query: select extract(minute from t) +from extract_udf +PREHOOK: type: QUERY +PREHOOK: Input: default@extract_udf +#### A masked pattern was here #### +POSTHOOK: query: select extract(minute from t) +from extract_udf +POSTHOOK: type: QUERY +POSTHOOK: Input: default@extract_udf +#### A masked pattern was here #### +8 +PREHOOK: query: select extract(hour from t) +from extract_udf +PREHOOK: type: QUERY +PREHOOK: Input: default@extract_udf +#### A masked pattern was here #### +POSTHOOK: query: select extract(hour from t) +from extract_udf +POSTHOOK: type: QUERY +POSTHOOK: Input: default@extract_udf +#### A masked pattern was here #### +7 +PREHOOK: query: select extract(week from t) +from extract_udf +PREHOOK: type: QUERY +PREHOOK: Input: default@extract_udf +#### A masked pattern was here #### +POSTHOOK: query: select extract(week from t) +from extract_udf +POSTHOOK: type: QUERY +POSTHOOK: Input: default@extract_udf +#### A masked pattern was here #### +18 +PREHOOK: query: select extract(month from t) +from extract_udf +PREHOOK: type: QUERY +PREHOOK: Input: default@extract_udf +#### A masked pattern was here #### +POSTHOOK: query: select extract(month from t) +from extract_udf +POSTHOOK: type: QUERY +POSTHOOK: Input: default@extract_udf +#### A masked pattern was here #### +5 +PREHOOK: query: select extract(quarter from t) +from extract_udf +PREHOOK: type: QUERY +PREHOOK: Input: default@extract_udf +#### A masked pattern was here #### +POSTHOOK: query: select extract(quarter from t) +from extract_udf +POSTHOOK: type: QUERY +POSTHOOK: Input: default@extract_udf +#### A masked pattern was here #### +2 +PREHOOK: query: select extract(year from t) +from extract_udf +PREHOOK: type: QUERY +PREHOOK: Input: default@extract_udf +#### A masked pattern was here #### +POSTHOOK: query: select extract(year from t) +from extract_udf +POSTHOOK: type: QUERY +POSTHOOK: Input: default@extract_udf +#### A masked pattern was here #### +2011
