You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@hive.apache.org by Mich Talebzadeh <mi...@gmail.com> on 2016/03/08 01:46:44 UTC
Hive 2 insert error
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
Re: Hive 2 insert error
Posted by Gopal Vijayaraghavan <go...@apache.org>.
> Is this something new in Hive 2 as I don't recall having this issue
>before?
No.
> | CREATE TABLE `sales3`( |
> | `prod_id` bigint, |
> | STORED AS INPUTFORMAT |
> | 'transactional'='true', |
Does look like you're overriding the inputformat for a transactional table.
Cheers,
Gopal
Re: Hive 2 insert error
Posted by Mich Talebzadeh <mi...@gmail.com>.
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 <mt...@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
>
>
Re: Hive 2 insert error
Posted by Marcin Tustin <mt...@handybook.com>.
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 <mi...@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