hive-issues mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Miklos Gergely (JIRA)" <j...@apache.org>
Subject [jira] [Updated] (HIVE-19694) Create Materialized View statement should check for MV name conflicts before running MV's SQL statement.
Date Fri, 27 Jul 2018 05:02:00 GMT

     [ https://issues.apache.org/jira/browse/HIVE-19694?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]

Miklos Gergely updated HIVE-19694:
----------------------------------
    Attachment: HIVE-19694.patch

> Create Materialized View statement should check for MV name conflicts before running
MV's SQL statement. 
> ---------------------------------------------------------------------------------------------------------
>
>                 Key: HIVE-19694
>                 URL: https://issues.apache.org/jira/browse/HIVE-19694
>             Project: Hive
>          Issue Type: Bug
>          Components: Hive
>    Affects Versions: 3.0.0
>            Reporter: Nita Dembla
>            Assignee: Miklos Gergely
>            Priority: Major
>             Fix For: 3.0.1
>
>
> If the CREATE MATERIALIZE VIEW statement refers to a mv name that already exists, the
statement runs the SQL on cluster and Move task returns an error at the very end.
> This unnecessarily uses up cluster resources and user time.
>  
> {code:java}
> 0: jdbc:hive2://localhost:10007/tpcds_bin_par> CREATE MATERIALIZED VIEW mv_store_sales_item_store
> . . . . . . . . . . . . . . . . . . . . . . .> ENABLE REWRITE AS (
> . . . . . . . . . . . . . . . . . . . . . . .>  select ss_item_sk,
> . . . . . . . . . . . . . . . . . . . . . . .>  ss_store_sk,
> . . . . . . . . . . . . . . . . . . . . . . .>  sum(ss_quantity) as ss_quantity,
> . . . . . . . . . . . . . . . . . . . . . . .>  sum(ss_ext_wholesale_cost) as ss_ext_wholesale_cost,
> . . . . . . . . . . . . . . . . . . . . . . .>  sum(ss_net_paid) as ss_net_paid,
> . . . . . . . . . . . . . . . . . . . . . . .>  sum(ss_net_profit) as ss_net_profit
> . . . . . . . . . . . . . . . . . . . . . . .>  from store_sales
> . . . . . . . . . . . . . . . . . . . . . . .>  group by ss_item_sk,ss_store_sk
> . . . . . . . . . . . . . . . . . . . . . . .>  );
> INFO  : Compiling command(queryId=root_20180524034330_21fca7f6-ed5a-492c-88e9-913d4120b037):
CREATE MATERIALIZED VIEW mv_store_sales_item_store
> ENABLE REWRITE AS (
> select ss_item_sk,
> |   `ss_store_sk` bigint,                            |
> |   `ss_quantity` bigint,                            |
> |   `ss_ext_wholesale_cost` double,                  |
> |   `ss_net_paid` double,                            |
> |   `ss_net_profit` double)                          |
> . . . . . . . . . . . . . . . . . . . . . . .>  from store_sales
> . . . . . . . . . . . . . . . . . . . . . . .>  group by ss_item_sk,ss_store_sk
> . . . . . . . . . . . . . . . . . . . . . . .>  );
> INFO  : Compiling command(queryId=root_20180524034330_21fca7f6-ed5a-492c-88e9-913d4120b037):
CREATE MATERIALIZED VIEW mv_store_sales_item_store
> ENABLE REWRITE AS (
> select ss_item_sk,
> ss_store_sk,
> sum(ss_quantity) as ss_quantity,
> sum(ss_ext_wholesale_cost) as ss_ext_wholesale_cost,
> sum(ss_net_paid) as ss_net_paid,
> sum(ss_net_profit) as ss_net_profit
> from store_sales
> group by ss_item_sk,ss_store_sk
> )
> INFO  : Semantic Analysis Completed
> INFO  : Returning Hive schema: Schema(fieldSchemas:[FieldSchema(name:ss_item_sk, type:bigint,
comment:null), FieldSchema(name:ss_store_sk, type:bigint, comment:null), FieldSchema(name:ss_quantity,
type:bigint, comment:null), FieldSchema(name:ss_ext_wholesale_cost, type:double, comment:null),
FieldSchema(name:ss_net_paid, type:double, comment:null), FieldSchema(name:ss_net_profit,
type:double, comment:null)], properties:null)
> INFO  : Completed compiling command(queryId=root_20180524034330_21fca7f6-ed5a-492c-88e9-913d4120b037);
Time taken: 3.652 seconds
> INFO  : Executing command(queryId=root_20180524034330_21fca7f6-ed5a-492c-88e9-913d4120b037):
CREATE MATERIALIZED VIEW mv_store_sales_item_store
> ENABLE REWRITE AS (
> select ss_item_sk,
> ss_store_sk,
> sum(ss_quantity) as ss_quantity,
> sum(ss_ext_wholesale_cost) as ss_ext_wholesale_cost,
> sum(ss_net_paid) as ss_net_paid,
> sum(ss_net_profit) as ss_net_profit
> from store_sales
> group by ss_item_sk,ss_store_sk
> )
> INFO  : Query ID = root_20180524034330_21fca7f6-ed5a-492c-88e9-913d4120b037
> INFO  : Total jobs = 1
> INFO  : Launching Job 1 out of 1
> INFO  : Starting task [Stage-1:MAPRED] in serial mode
> INFO  : Subscribed to counters: [] for queryId: root_20180524034330_21fca7f6-ed5a-492c-88e9-913d4120b037
> INFO  : Session is already open
> INFO  : Dag name: CREATE MATERIALIZED V...tem_sk,ss_store_sk
> ) (Stage-1)
> INFO  : Status: Running (Executing on YARN cluster with App id application_1525123931791_0151)
> ----------------------------------------------------------------------------------------------
>         VERTICES      MODE        STATUS  TOTAL  COMPLETED  RUNNING 
PENDING  FAILED  KILLED
> ----------------------------------------------------------------------------------------------
> Map 1 ..........      llap     SUCCEEDED   1682       1682       
0        0       0       0
> Reducer 2 ......      llap     SUCCEEDED   1009       1009       
0        0       0       7
> ----------------------------------------------------------------------------------------------
> VERTICES: 02/02  [==========================>>] 100%  ELAPSED TIME: 1734.00 s
> ----------------------------------------------------------------------------------------------
> INFO  : Status: DAG finished successfully in 1731.89 seconds
> INFO  :
> INFO  : Query Execution Summary
> INFO  : ----------------------------------------------------------------------------------------------
> INFO  : OPERATION                            DURATION
> INFO  : ----------------------------------------------------------------------------------------------
> INFO  : Compile Query                           3.65s
> INFO  : Prepare Plan                            0.45s
> INFO  : Get Query Coordinator (AM)              0.00s
> INFO  : Submit Plan                             0.17s
> INFO  : Start DAG                               0.60s
> INFO  : Run DAG                              1731.89s
> INFO  : ----------------------------------------------------------------------------------------------
> INFO  :
> INFO  : Task Execution Summary
> INFO  : ----------------------------------------------------------------------------------------------
> INFO  :   VERTICES      DURATION(ms)   CPU_TIME(ms)    GC_TIME(ms)   INPUT_RECORDS  
OUTPUT_RECORDS
> INFO  : ----------------------------------------------------------------------------------------------
> INFO  :      Map 1         928170.00              0             
0  28,800,426,268   28,760,206,232
> INFO  :  Reducer 2        1099992.00              0             
0  28,760,206,232                0
> INFO  : ----------------------------------------------------------------------------------------------
> INFO  :
> INFO  : LLAP IO Summary
> INFO  : ----------------------------------------------------------------------------------------------
> INFO  :   VERTICES ROWGROUPS  META_HIT  META_MISS  DATA_HIT  DATA_MISS  ALLOCATION    
USED  TOTAL_IO
> INFO  : ----------------------------------------------------------------------------------------------
> INFO  :      Map 1   2890797     33830       1888   40.57GB   401.64GB   
832.29GB 777.79GB 117756.65s
> INFO  : ----------------------------------------------------------------------------------------------
> INFO  :
> INFO  : FileSystem Counters Summary
> INFO  :
> INFO  : Scheme: HDFS
> INFO  : ----------------------------------------------------------------------------------------------
> INFO  :   VERTICES      BYTES_READ      READ_OPS     LARGE_READ_OPS     
BYTES_WRITTEN     WRITE_OPS
> INFO  : ----------------------------------------------------------------------------------------------
> INFO  :      Map 1        401.65GB         29867                 
0                 0B             0
> INFO  :  Reducer 2              0B          4036                 
0             5.95GB          3027
> INFO  : ----------------------------------------------------------------------------------------------
> INFO  :
> INFO  : Scheme: FILE
> INFO  : ----------------------------------------------------------------------------------------------
> INFO  :   VERTICES      BYTES_READ      READ_OPS     LARGE_READ_OPS     
BYTES_WRITTEN     WRITE_OPS
> INFO  : ----------------------------------------------------------------------------------------------
> INFO  :      Map 1              0B             0                 
0           691.51GB             0
> INFO  :  Reducer 2        490.46GB             0                 
0                 0B             0
> INFO  : ----------------------------------------------------------------------------------------------
> INFO  :
> INFO  : org.apache.tez.common.counters.DAGCounter:
> INFO  :    NUM_KILLED_TASKS: 7
> INFO  :    NUM_SUCCEEDED_TASKS: 2691
> INFO  :    TOTAL_LAUNCHED_TASKS: 2698
> INFO  :    AM_CPU_MILLISECONDS: 884400
> INFO  :    AM_GC_TIME_MILLIS: 1052
> INFO  : File System Counters:
> INFO  :    FILE_BYTES_READ: 490462333002
> INFO  :    FILE_BYTES_WRITTEN: 691512269321
> INFO  :    FILE_READ_OPS: 0
> INFO  :    FILE_LARGE_READ_OPS: 0
> INFO  :    FILE_WRITE_OPS: 0
> INFO  :    HDFS_BYTES_READ: 401649861525
> INFO  :    HDFS_BYTES_WRITTEN: 5953929405
> INFO  :    HDFS_READ_OPS: 33903
> INFO  :    HDFS_LARGE_READ_OPS: 0
> INFO  :    HDFS_WRITE_OPS: 3027
> INFO  : org.apache.tez.common.counters.TaskCounter:
> INFO  :    REDUCE_INPUT_GROUPS: 301902000
> INFO  :    REDUCE_INPUT_RECORDS: 28760206232
> INFO  :    COMBINE_INPUT_RECORDS: 0
> INFO  :    SPILLED_RECORDS: 57520412464
> INFO  :    NUM_SHUFFLED_INPUTS: 1697138
> INFO  :    NUM_SKIPPED_INPUTS: 0
> INFO  :    NUM_FAILED_SHUFFLE_INPUTS: 0
> INFO  :    MERGED_MAP_OUTPUTS: 1697138
> INFO  :    INPUT_RECORDS_PROCESSED: 28802064
> INFO  :    INPUT_SPLIT_LENGTH_BYTES: 1423169426915
> INFO  :    OUTPUT_RECORDS: 28760206232
> INFO  :    OUTPUT_LARGE_RECORDS: 0
> INFO  :    OUTPUT_BYTES: 1251477312279
> INFO  :    OUTPUT_BYTES_WITH_OVERHEAD: 1249819249865
> INFO  :    OUTPUT_BYTES_PHYSICAL: 691471524553
> INFO  :    ADDITIONAL_SPILLS_BYTES_WRITTEN: 490687645770
> INFO  :    ADDITIONAL_SPILLS_BYTES_READ: 490687645770
> INFO  :    ADDITIONAL_SPILL_COUNT: 0
> INFO  :    SHUFFLE_CHUNK_COUNT: 1682
> INFO  :    SHUFFLE_BYTES: 691471524553
> INFO  :    SHUFFLE_BYTES_DECOMPRESSED: 1249819249865
> INFO  :    SHUFFLE_BYTES_TO_MEM: 691471524553
> INFO  :    SHUFFLE_BYTES_TO_DISK: 0
> INFO  :    SHUFFLE_BYTES_DISK_DIRECT: 0
> INFO  :    NUM_MEM_TO_DISK_MERGES: 0
> INFO  :    NUM_DISK_TO_DISK_MERGES: 0
> INFO  :    SHUFFLE_PHASE_TIME: 208623266
> INFO  :    MERGE_PHASE_TIME: 248665618
> INFO  :    FIRST_EVENT_RECEIVED: 6992
> INFO  :    LAST_EVENT_RECEIVED: 66502682
> INFO  : HIVE:
> INFO  :    CREATED_FILES: 1009
> INFO  :    DESERIALIZE_ERRORS: 0
> INFO  :    RECORDS_IN_Map_1: 28800426268
> INFO  :    RECORDS_OUT_1_tpcds_bin_partitioned_orc_10000.mv_store_sales_it: 301902000
> INFO  :    RECORDS_OUT_INTERMEDIATE_Map_1: 28760206232
> INFO  :    RECORDS_OUT_INTERMEDIATE_Reducer_2: 0
> INFO  :    RECORDS_OUT_OPERATOR_FS_11: 301902000
> INFO  :    RECORDS_OUT_OPERATOR_GBY_10: 301902000
> INFO  :    RECORDS_OUT_OPERATOR_GBY_8: 28760206232
> INFO  :    RECORDS_OUT_OPERATOR_MAP_0: 0
> INFO  :    RECORDS_OUT_OPERATOR_RS_9: 28760206232
> INFO  :    RECORDS_OUT_OPERATOR_SEL_7: 28800426268
> INFO  :    RECORDS_OUT_OPERATOR_TS_0: 28800426268
> INFO  : Shuffle Errors:
> INFO  :    BAD_ID: 0
> INFO  :    CONNECTION: 0
> INFO  :    IO_ERROR: 0
> INFO  :    WRONG_LENGTH: 0
> INFO  :    WRONG_MAP: 0
> INFO  :    WRONG_REDUCE: 0
> INFO  : Shuffle Errors_Reducer_2_INPUT_Map_1:
> INFO  :    BAD_ID: 0
> INFO  :    CONNECTION: 0
> INFO  :    IO_ERROR: 0
> INFO  :    WRONG_LENGTH: 0
> INFO  :    WRONG_MAP: 0
> INFO  :    WRONG_REDUCE: 0
> INFO  : TaskCounter_Map_1_INPUT_store_sales:
> INFO  :    INPUT_RECORDS_PROCESSED: 28802064
> INFO  :    INPUT_SPLIT_LENGTH_BYTES: 1423169426915
> INFO  : TaskCounter_Map_1_OUTPUT_Reducer_2:
> INFO  :    ADDITIONAL_SPILLS_BYTES_READ: 0
> INFO  :    ADDITIONAL_SPILLS_BYTES_WRITTEN: 0
> INFO  :    ADDITIONAL_SPILL_COUNT: 0
> INFO  :    OUTPUT_BYTES: 1251477312279
> INFO  :    OUTPUT_BYTES_PHYSICAL: 691471524553
> INFO  :    OUTPUT_BYTES_WITH_OVERHEAD: 1249819249865
> INFO  :    OUTPUT_LARGE_RECORDS: 0
> INFO  :    OUTPUT_RECORDS: 28760206232
> INFO  :    SHUFFLE_CHUNK_COUNT: 1682
> INFO  :    SPILLED_RECORDS: 28760206232
> INFO  : TaskCounter_Reducer_2_INPUT_Map_1:
> INFO  :    ADDITIONAL_SPILLS_BYTES_READ: 490687645770
> INFO  :    ADDITIONAL_SPILLS_BYTES_WRITTEN: 490687645770
> INFO  :    COMBINE_INPUT_RECORDS: 0
> INFO  :    FIRST_EVENT_RECEIVED: 6992
> INFO  :    LAST_EVENT_RECEIVED: 66502682
> INFO  :    MERGED_MAP_OUTPUTS: 1697138
> INFO  :    MERGE_PHASE_TIME: 248665618
> INFO  :    NUM_DISK_TO_DISK_MERGES: 0
> INFO  :    NUM_FAILED_SHUFFLE_INPUTS: 0
> INFO  :    NUM_MEM_TO_DISK_MERGES: 0
> INFO  :    NUM_SHUFFLED_INPUTS: 1697138
> INFO  :    NUM_SKIPPED_INPUTS: 0
> INFO  :    REDUCE_INPUT_GROUPS: 301902000
> INFO  :    REDUCE_INPUT_RECORDS: 28760206232
> INFO  :    SHUFFLE_BYTES: 691471524553
> INFO  :    SHUFFLE_BYTES_DECOMPRESSED: 1249819249865
> INFO  :    SHUFFLE_BYTES_DISK_DIRECT: 0
> INFO  :    SHUFFLE_BYTES_TO_DISK: 0
> INFO  :    SHUFFLE_BYTES_TO_MEM: 691471524553
> INFO  :    SHUFFLE_PHASE_TIME: 208623266
> INFO  :    SPILLED_RECORDS: 28760206232
> INFO  : TaskCounter_Reducer_2_OUTPUT_out_Reducer_2:
> INFO  :    OUTPUT_RECORDS: 0
> INFO  : org.apache.hadoop.hive.llap.counters.LlapIOCounters:
> INFO  :    ALLOCATED_BYTES: 832288587776
> INFO  :    ALLOCATED_USED_BYTES: 777785688450
> INFO  :    CACHE_HIT_BYTES: 40573598684
> INFO  :    CACHE_MISS_BYTES: 401640241544
> INFO  :    CONSUMER_TIME_NS: 103780723121700
> INFO  :    DECODE_TIME_NS: 67854956903872
> INFO  :    HDFS_TIME_NS: 40407374232025
> INFO  :    METADATA_CACHE_HIT: 33830
> INFO  :    METADATA_CACHE_MISS: 1888
> INFO  :    NUM_DECODED_BATCHES: 2890797
> INFO  :    NUM_VECTOR_BATCHES: 28802069
> INFO  :    ROWS_EMITTED: 28800426268
> INFO  :    SELECTED_ROWGROUPS: 2890797
> INFO  :    TOTAL_IO_TIME_NS: 117756651175367
> INFO  : org.apache.hadoop.hive.llap.counters.LlapWmCounters:
> INFO  :    GUARANTEED_QUEUED_NS: 0
> INFO  :    GUARANTEED_RUNNING_NS: 0
> INFO  :    SPECULATIVE_QUEUED_NS: 127227283691687
> INFO  :    SPECULATIVE_RUNNING_NS: 475493970727392
> INFO  : org.apache.hadoop.hive.ql.exec.tez.HiveInputCounters:
> INFO  :    GROUPED_INPUT_SPLITS_Map_1: 1682
> INFO  :    INPUT_DIRECTORIES_Map_1: 1824
> INFO  :    INPUT_FILES_Map_1: 4323
> INFO  :    RAW_INPUT_SPLITS_Map_1: 8292
> INFO  : Starting task [Stage-2:DEPENDENCY_COLLECTION] in serial mode
> INFO  : Starting task [Stage-0:MOVE] in serial mode
> INFO  : Moving data to directory hdfs://ctr-e138-1518143905142-92974-01-000002.hwx.site:8020/apps/hive/warehouse/tpcds_bin_partitioned_orc_10000.db/mv_store_sales_item_store
from hdfs://ctr-e138-1518143905142-92974-01-000002.hwx.site:8020/apps/hive/warehouse/tpcds_bin_partitioned_orc_10000.db/.hive-staging_hive_2018-05-24_03-43-30_960_8962535148229486995-408/-ext-10002
> INFO  : Starting task [Stage-4:DDL] in serial mode
> ERROR : FAILED: Execution Error, return code 1 from org.apache.hadoop.hive.ql.exec.DDLTask.
Table already exists: tpcds_bin_partitioned_orc_10000.mv_store_sales_item_store
> INFO  : Completed executing command(queryId=root_20180524034330_21fca7f6-ed5a-492c-88e9-913d4120b037);
Time taken: 1734.952 seconds
> Error: Error while processing statement: FAILED: Execution Error, return code 1 from
org.apache.hadoop.hive.ql.exec.DDLTask. Table already exists: tpcds_bin_partitioned_orc_10000.mv_store_sales_item_store
(state=08S01,code=1{code}
>  



--
This message was sent by Atlassian JIRA
(v7.6.3#76005)

Mime
View raw message