Uploaded image for project: 'Hive'
  1. Hive
  2. HIVE-19295

Some multiple inserts do not work on MR engine

    XMLWordPrintableJSON

    Details

    • Type: Bug
    • Status: Resolved
    • Priority: Blocker
    • Resolution: Won't Fix
    • Affects Version/s: 2.3.3
    • Fix Version/s: None
    • Component/s: None
    • Labels:
      None

      Description

      General Info

      Hive version : 2.3.3

      commit 3f7dde31aed44b5440563d3f9d8a8887beccf0be
      Author: Daniel Dai <daijy@hortonworks.com>
      Date:   Wed Mar 28 16:46:29 2018 -0700
      
          Preparing for 2.3.3 release
      
      

      Hadoop version: 2.7.2.

      Engine

      hive> set hive.execution.engine;
      hive.execution.engine=mr
      

      STEP 1. Create test data

      DROP TABLE IF EXISTS customer_target;
      DROP TABLE IF EXISTS customer_source;
      
      CREATE TABLE customer_target (id STRING, first_name STRING, last_name STRING, age INT); 
      
      insert into customer_target values ('001', 'John', 'Smith', 45), ('002', 'Michael', 'Watson', 27), ('003', 'Den', 'Brown', 33);
      SELECT id, first_name, last_name, age  FROM customer_target;
      
      +------+-------------+------------+------+
      |  id  | first_name  | last_name  | age  |
      +------+-------------+------------+------+
      | 002  | Michael     | Watson     | 27   |
      | 001  | John        | Smith      | 45   |
      | 003  | Den         | Brown      | 33   |
      +------+-------------+------------+------+
      
      CREATE TABLE customer_source (id STRING, first_name STRING, last_name STRING, age INT);
      
      insert into customer_source values ('001', 'Dorothi', 'Hogward', 77), ('007', 'Alex', 'Bowee', 1), ('088', 'Robert', 'Dowson', 25);
      SELECT id, first_name, last_name, age  FROM customer_source;
      
      +------+-------------+------------+------+
      |  id  | first_name  | last_name  | age  |
      +------+-------------+------------+------+
      | 088  | Robert      | Dowson     | 25   |
      | 001  | Dorothi     | Hogward    | 77   |
      | 007  | Alex        | Bowee      | 1    |
      +------+-------------+------------+------+
      

      STEP 2. Do multiple insert

      FROM
        `default`.`customer_target` `trg`
        RIGHT OUTER JOIN
        `default`.`customer_source` `src`
        ON `src`.`id` = `trg`.`id`
      INSERT INTO `default`.`customer_target`    -- update clause
       select  `trg`.`id`, `src`.`first_name`, `src`.`last_name`, `trg`.`age`
         WHERE `src`.`id` = `trg`.`id`
       sort by `trg`.id 
      INSERT INTO `default`.`customer_target`    -- insert clause
        select `src`.`id`, `src`.`first_name`, `src`.`last_name`, `src`.`age`
         WHERE `trg`.`id` IS NULL   
      

      ACTUAL RESULT

      2018-04-25T05:31:50,407 ERROR [e502bb46-e832-4156-860f-aba875bbb10b main] ql.Driver: FAILED: SemanticException [Error 10087]: The same output cannot be present multiple times:  customer_target
      org.apache.hadoop.hive.ql.parse.SemanticException: The same output cannot be present multiple times:  customer_target
              at org.apache.hadoop.hive.ql.parse.SemanticAnalyzer.genFileSinkPlan(SemanticAnalyzer.java:6899)
              at org.apache.hadoop.hive.ql.parse.SemanticAnalyzer.genPostGroupByBodyPlan(SemanticAnalyzer.java:9772)
              at org.apache.hadoop.hive.ql.parse.SemanticAnalyzer.genBodyPlan(SemanticAnalyzer.java:9644)
              at org.apache.hadoop.hive.ql.parse.SemanticAnalyzer.genPlan(SemanticAnalyzer.java:10549)
              at org.apache.hadoop.hive.ql.parse.SemanticAnalyzer.genPlan(SemanticAnalyzer.java:10427)
              at org.apache.hadoop.hive.ql.parse.SemanticAnalyzer.genOPTree(SemanticAnalyzer.java:11125)
              at org.apache.hadoop.hive.ql.parse.CalcitePlanner.genOPTree(CalcitePlanner.java:481)
              at org.apache.hadoop.hive.ql.parse.SemanticAnalyzer.analyzeInternal(SemanticAnalyzer.java:11138)
              at org.apache.hadoop.hive.ql.parse.CalcitePlanner.analyzeInternal(CalcitePlanner.java:286)
              at org.apache.hadoop.hive.ql.parse.BaseSemanticAnalyzer.analyze(BaseSemanticAnalyzer.java:258)
              at org.apache.hadoop.hive.ql.Driver.compile(Driver.java:512)
              at org.apache.hadoop.hive.ql.Driver.compileInternal(Driver.java:1317)
              at org.apache.hadoop.hive.ql.Driver.runInternal(Driver.java:1457)
              at org.apache.hadoop.hive.ql.Driver.run(Driver.java:1237)
              at org.apache.hadoop.hive.ql.Driver.run(Driver.java:1227)
              at org.apache.hadoop.hive.cli.CliDriver.processLocalCmd(CliDriver.java:233)
              at org.apache.hadoop.hive.cli.CliDriver.processCmd(CliDriver.java:184)
              at org.apache.hadoop.hive.cli.CliDriver.processLine(CliDriver.java:403)
              at org.apache.hadoop.hive.cli.CliDriver.executeDriver(CliDriver.java:821)
              at org.apache.hadoop.hive.cli.CliDriver.run(CliDriver.java:759)
              at org.apache.hadoop.hive.cli.CliDriver.main(CliDriver.java:686)
              at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
              at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
              at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
              at java.lang.reflect.Method.invoke(Method.java:498)
              at org.apache.hadoop.util.RunJar.run(RunJar.java:221)
              at org.apache.hadoop.util.RunJar.main(RunJar.java:136)
      

        Attachments

          Issue Links

            Activity

              People

              • Assignee:
                osayankin Oleksiy Sayankin
                Reporter:
                osayankin Oleksiy Sayankin
              • Votes:
                0 Vote for this issue
                Watchers:
                2 Start watching this issue

                Dates

                • Created:
                  Updated:
                  Resolved: