CODEWITHSUNDEEP
hivehiveql
sharath
sharath

Reputation: 3616

Hive : Add partition column data in subquery

I have 2 hive tables which have the exact same schema, except for a date column. One of them has the date column, which is what its partitioned by and the other does not have a date column and is not partitioned by anything.

the 2 tables are:

staging (no date column and not partitioned)  
main (date column present and is partitioned by date)

I want to copy over data from staging to main. I am trying this query

INSERT OVERWRITE TABLE main
        PARTITION (dt='2019-04-30')
                SELECT col_a,
                col_b,
                col_c,
                col_d,
                col_e,
                '2019-04-30' FROM staging

Both staging and main tables have col_a, col_b, col_c, col_d and col_e. dt is the field which only the main table has. But this throws this error:

main requires that the data to be inserted have the same number of columns as the target table: target table has 6 column(s) but the inserted data has 7 column(s), including 1 partition column(s) having constant value(s).;'

Any idea how I can fix this ?

Upvotes: 1

Views: 926

Answers (2)

JackJouns
JackJouns

Reputation: 1

Look this,

target table has 6 column(s) but the inserted data has 7 column(s), 
including 1 partition column(s) having constant value(s).;'

It said, you your table has 6 columns but insert has 7 columns, please check your target table , show create table staging , if or not has the correct column numbers.

This often happends after the table structure is not successfuly modified.

Upvotes: 0

sharath
sharath

Reputation: 3616

Well, turns out all I had to do was this - 

INSERT OVERWRITE TABLE main
        PARTITION (dt='2019-04-30')

                SELECT * FROM staging

Upvotes: 2

Related Questions