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 <[email protected]> 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 <[email protected]
> > 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
>
>
