Search code examples
hivehiveql

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 ?

Solution

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

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

                SELECT * FROM staging