[HIVE-19694] Create Materialized View statement should check for MV name conflicts before running MV's SQL statement. - ASF JIRA

XML

Word

Printable

JSON

Details

Type: Bug
Status: Closed
Priority: Major
Resolution: Fixed
Affects Version/s: 3.0.0
Fix Version/s: 4.0.0-alpha-1
Component/s: Hive
Labels:
None

Description

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.

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

Attachments

- Sort By Name
- Sort By Date
- Ascending
- Descending

HIVE-19694.01.patch
27/Jul/18 05:02
15 kB
Miklos Gergely

Activity

People

Assignee:: Miklos Gergely

Reporter:: Nita Dembla

Votes:: 0 Vote for this issue

Watchers:: 3 Start watching this issue

Dates

Created:: 24/May/18 14:10

Updated:: 17/Nov/22 08:55

Resolved:: 31/Jul/18 20:25