Create a view with Filter parameter

user_5031891 0
Viewed 688

I'm creating a view in hive which unions two tables and has a lot of data. Is there a way to pass a filter paramter to view in hive so that it is applied to the table as well. I have 

CREATE VIEW abc 
AS
SELECT * FROM 
(SELECT * FROM table_a
UNION 
SELECT * table_b) temp;

If I run something like SELECT * FROM abc WHERE day='2018-10-22' It should return the union on the selected date only like

SELECT * FROM table _a WHERE day='2018-10-22' UNION
SELECT * FROM table _b WHERE day='2018-10-22'

How do I create a view to do this.

hiveql hive sql
1 Answers

There is no need to add filter explicitly for optimization purposes. Query optimizer can push down the predicate. Take a look to this

CREATE TABLE `t5`(`a` string);
CREATE TABLE `t6`(`a` string);


CREATE VIEW v1 
AS
SELECT * FROM 
(
SELECT * FROM t5
UNION ALL
SELECT * from t6
) temp;

This is the explain of the query select * from v1 where a = "b", as you can see there is 2 independent table scan and for each the predicate is applied. It would be really disappointing if Hive at this point pull all the data and filter at the end :)

Explain
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: t5
            filterExpr: (a = 'b') (type: boolean)
            Statistics: Num rows: 1 Data size: 0 Basic stats: PARTIAL Column stats: NONE
            Filter Operator
              predicate: (a = 'b') (type: boolean)
              Statistics: Num rows: 1 Data size: 0 Basic stats: PARTIAL Column stats: NONE
              Select Operator
                Statistics: Num rows: 1 Data size: 0 Basic stats: PARTIAL Column stats: NONE
                Union
                  Statistics: Num rows: 2 Data size: 0 Basic stats: PARTIAL Column stats: NONE
                  Select Operator
                    expressions: 'b' (type: string)
                    outputColumnNames: _col0
                    Statistics: Num rows: 2 Data size: 0 Basic stats: PARTIAL Column stats: NONE
                    File Output Operator
                      compressed: false
                      Statistics: Num rows: 2 Data size: 0 Basic stats: PARTIAL Column stats: NONE
                      table:
                          input format: org.apache.hadoop.mapred.TextInputFormat
                          output format: org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat
                          serde: org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe
          TableScan
            alias: t6
            filterExpr: (a = 'b') (type: boolean)
            Statistics: Num rows: 1 Data size: 0 Basic stats: PARTIAL Column stats: NONE
            Filter Operator
              predicate: (a = 'b') (type: boolean)
              Statistics: Num rows: 1 Data size: 0 Basic stats: PARTIAL Column stats: NONE
              Select Operator
                Statistics: Num rows: 1 Data size: 0 Basic stats: PARTIAL Column stats: NONE
                Union
                  Statistics: Num rows: 2 Data size: 0 Basic stats: PARTIAL Column stats: NONE
                  Select Operator
                    expressions: 'b' (type: string)
                    outputColumnNames: _col0
                    Statistics: Num rows: 2 Data size: 0 Basic stats: PARTIAL Column stats: NONE
                    File Output Operator
                      compressed: false
                      Statistics: Num rows: 2 Data size: 0 Basic stats: PARTIAL Column stats: NONE
                      table:
                          input format: org.apache.hadoop.mapred.TextInputFormat
                          output format: org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat
                          serde: org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe

  Stage: Stage-0
    Fetch Operator
      limit: -1
      Processor Tree:
        ListSink
user_38294260
Related