Ok, When table is created as ORC but with no transactional property INSERT/SELECT works
CREATE TABLE sales3 ( PROD_ID bigint , CUST_ID bigint , TIME_ID timestamp , CHANNEL_ID bigint , PROMO_ID bigint , QUANTITY_SOLD decimal(10) , AMOUNT_SOLD decimal(10) ) STORED AS ORC TBLPROPERTIES ( "orc.compress"="SNAPPY" ) hive> insert into sales3 select * from smallsales; Query ID = hduser_20160308085645_9fb4e880-d802-4a52-b30b-aa56ba1dedbd Total jobs = 1 If you create table with transactional=true and assuming you have set up lock manager etc in hive-site.xml then the only way INSERT will work is if table is bucketed CREATE TABLE sales3 ( PROD_ID bigint , CUST_ID bigint , TIME_ID timestamp , CHANNEL_ID bigint , PROMO_ID bigint , QUANTITY_SOLD decimal(10) , AMOUNT_SOLD decimal(10) ) CLUSTERED BY (PROD_ID,CUST_ID,TIME_ID,CHANNEL_ID,PROMO_ID) INTO 256 BUCKETS STORED AS ORC TBLPROPERTIES ( "orc.compress"="SNAPPY" , "transactional"="true" ) ; Updates will work if the column(s) updated are not part of bucketing which makes sense. I gather I have always created tables with bucketing so this was never an issue. Thanks Dr Mich Talebzadeh LinkedIn * https://www.linkedin.com/profile/view?id=AAEAAAAWh2gBxianrbJd6zP6AcPCCdOABUrV8Pw <https://www.linkedin.com/profile/view?id=AAEAAAAWh2gBxianrbJd6zP6AcPCCdOABUrV8Pw>* http://talebzadehmich.wordpress.com On 8 March 2016 at 01:03, Marcin Tustin <mtus...@handybook.com> wrote: > I believe updates and deletes have always had this constraint. It's at > least hinted at by: > https://cwiki.apache.org/confluence/display/Hive/Hive+Transactions#HiveTransactions-ConfigurationValuestoSetforINSERT,UPDATE,DELETE > > On Mon, Mar 7, 2016 at 7:46 PM, Mich Talebzadeh <mich.talebza...@gmail.com > > wrote: > >> Hi, >> >> I noticed this one in Hive2. >> >> insert into sales3 select * from smallsales; >> FAILED: SemanticException [Error 10297]: Attempt to do update or delete >> on table sales3 that does not use an AcidOutputFormat or is not bucketed >> >> Is this something new in Hive 2 as I don't recall having this issue >> before? >> >> Table sales3 has been created as follows: >> >> +---------------------------------------------------------------------+--+ >> | createtab_stmt | >> +---------------------------------------------------------------------+--+ >> | CREATE TABLE `sales3`( | >> | `prod_id` bigint, | >> | `cust_id` bigint, | >> | `time_id` timestamp, | >> | `channel_id` bigint, | >> | `promo_id` bigint, | >> | `quantity_sold` decimal(10,0), | >> | `amount_sold` decimal(10,0)) | >> | ROW FORMAT SERDE | >> | 'org.apache.hadoop.hive.ql.io.orc.OrcSerde' | >> | STORED AS INPUTFORMAT | >> | 'org.apache.hadoop.hive.ql.io.orc.OrcInputFormat' | >> | OUTPUTFORMAT | >> | 'org.apache.hadoop.hive.ql.io.orc.OrcOutputFormat' | >> | LOCATION | >> | 'hdfs://rhes564:9000/user/hive/warehouse/oraclehadoop.db/sales3' | >> | TBLPROPERTIES ( | >> | 'orc.compress'='SNAPPY', | >> | 'transactional'='true', | >> | 'transient_lastDdlTime'='1457396808') | >> +---------------------------------------------------------------------+--+ >> >> >> Dr Mich Talebzadeh >> >> >> >> LinkedIn * >> https://www.linkedin.com/profile/view?id=AAEAAAAWh2gBxianrbJd6zP6AcPCCdOABUrV8Pw >> <https://www.linkedin.com/profile/view?id=AAEAAAAWh2gBxianrbJd6zP6AcPCCdOABUrV8Pw>* >> >> >> >> http://talebzadehmich.wordpress.com >> >> >> > > > Want to work at Handy? Check out our culture deck and open roles > <http://www.handy.com/careers> > Latest news <http://www.handy.com/press> at Handy > Handy just raised $50m > <http://venturebeat.com/2015/11/02/on-demand-home-service-handy-raises-50m-in-round-led-by-fidelity/> > led > by Fidelity > >