hive-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Mich Talebzadeh <mich.talebza...@gmail.com>
Subject Re: Hive 2 insert error
Date Tue, 08 Mar 2016 09:26:44 GMT
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 <mtustin@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.talebzadeh@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
>
>

Mime
View raw message