CODEWITHSUNDEEP
sqlhadoophive
Neethu Lalitha
Neethu Lalitha

Reputation: 3071

HIVE : Schema of both sides of union should match: Column _c0

I am trying to perform an update operation in hive which means insert overwrite for hive 0.13. Below is my query

    INSERT OVERWRITE TABLE TABLE_A 
    SELECT 
    A.ADDRESS1=B.ADDR_LINE_1,
    A.ADDRESS2,
    A.ADDRESS3=CONCAT(B.CITY, ', ', B.STATE, ' ', B.POSTAL_CDE) ,
    A.STORETYPE
    FROM
    P.STUDENT A
    JOIN
    `default.CUSTOMER` B ON A.REGION = B.SHIP_TO_LOCATION_NUM
    AND A.STORENUMBER = B.SHIP 
    UNION ALL 
    SELECT 
    A.ADDRESS1,
    A.ADDRESS2,
    A.ADDRESS3,
    A.STORETYPE=cast(B1.id AS string)
    FROM
    `P.STUDENT` A
    JOIN
    R.RTL B1 ON A.REGION = B1.SHIP_TO_LOCATION_NUM
    AND A.STORENUMBER = B1.SHIP ;

After the execution i receive an error saying:
FAILED: SemanticException 36:0 Schema of both sides of union should match: Column _c0 is of type boolean on first table and type string on second table. Error encountered near token 'P.STUDENT'.

I find all datatypes in the join are string . And for B1.id , i am casting as string cast(B1.id AS string). Can anyone tell me the issue with this query. i am using hive 0.13.

Thanks

Upvotes: 0

Views: 9912

Answers (1)

Neethu Lalitha
Neethu Lalitha

Reputation: 3071

Below query works, Seems like the A.ADDRESS1=B.ADDR_LINE_1, statement in the above query returns a boolen . 

  INSERT OVERWRITE TABLE TABLE_A 
        SELECT 
        B.ADDR_LINE_1,
        A.ADDRESS2,
        CONCAT(B.CITY, ', ', B.STATE, ' ', B.POSTAL_CDE) ,
        A.STORETYPE
        FROM
        P.STUDENT A
        JOIN
        `default.CUSTOMER` B ON A.REGION = B.SHIP_TO_LOCATION_NUM
        AND A.STORENUMBER = B.SHIP 
        UNION ALL 
        SELECT 
        A.ADDRESS1,
        A.ADDRESS2,
        A.ADDRESS3,
        cast(B1.id AS string)
        FROM
        `P.STUDENT` A
        JOIN
        R.RTL B1 ON A.REGION = B1.SHIP_TO_LOCATION_NUM
        AND A.STORENUMBER = B1.SHIP ;

Upvotes: 1

Related Questions