[
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)
