https://community.cloudera.com/t5/Support-Questions/Hive-Dynamic-partition-issue/m-p/189642#M151735 <A rel="user" href="https://community.cloudera.com/users/48176/voxjungle.html" nodeid="48176">@Team Spark</A><P><A rel="user" href="https://community.cloudera.com/users/48176/voxjungle.html" nodeid="48176"></A><A rel="user" href="https://community.cloudera.com/users/48176/voxjungle.html" nodeid="48176"></A></P><BR /><P>Your TEMP_tab table having 3 columns and your insert query having 4 columns(* means 3 columns from temp_tab and substr(mytime,0,10) means extra 1 column)</P><P>use the below query will work for your case</P><PRE>FROM TEMP_TAB INSERT OVERWRITE TABLE main_TAB PARTITION (mytime) SELECT id,age,substr(mytime,0,10) as mytime;</PRE><P>* *in addition in the above insert statement you are going to <STRONG>miss mytime column value</STRONG> as you are doing sub string that means <STRONG>source data is going to miss</STRONG> from<STRONG> temp_tab table</STRONG> to <STRONG>main_tab table</STRONG>.</P><P><STRONG><U>Ex:-</U></STRONG></P><P>temp_tab having 2017-10-12 12:20:23 but main_tab will have 2017-10-12, here we are going to miss 12:20:23 time from temp_tab to main _tab.</P><P><STRONG>In case if you dont want to miss the data then</STRONG></P><P>create main tab table with<B> 4 columns</B> in with <B>dt as partition column</B></P><PRE>CREATE TABLE IF NOT EXISTS main_TAB(id int,mytime STRING,age int) PARTITIONED BY (dt string) STORED AS ORC tblproperties ("orc.compress"="ZLIB");</PRE><P>then do insert statement as below</P><PRE>FROM TEMP_TAB INSERT OVERWRITE TABLE main_TAB PARTITION (mytime) SELECT *,substr(mytime,0,10) as mytime;</PRE><P></P><P>in this case <STRONG>partition column would be dt </STRONG>and you are <STRONG>not missing temp_tab</STRONG> data at all. </P> Mon, 06 Nov 2017 21:35:01 GMT Shu_ashu 2017-11-06T21:35:01Z