hive-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Elliot West <tea...@gmail.com>
Subject Re: Attempt to do update or delete using transaction manager that does not support these operations. (state=42000,code=10294)
Date Tue, 22 Dec 2015 17:52:39 GMT
What concurrency properties do you have set? Should look something like
those described here:

https://cwiki.apache.org/confluence/display/Hive/Hive+Transactions#HiveTransactions-Configuration



On Tuesday, 22 December 2015, Mich Talebzadeh <mich@peridale.co.uk> wrote:

> Dropped and created table tt as follows:
>
>
>
> drop table if exists tt;
>
> create table tt (
>
> owner                   varchar(30)
>
> ,object_name             varchar(30)
>
> ,subobject_name          varchar(30)
>
> ,object_id               bigint
>
> ,data_object_id          bigint
>
> ,object_type             varchar(19)
>
> ,created                 timestamp
>
> ,last_ddl_time           timestamp
>
> ,timestamp2               varchar(19)
>
> ,status                  varchar(7)
>
> ,temporary2              varchar(1)
>
> ,generated               varchar(1)
>
> ,secondary               varchar(1)
>
> ,namespace               bigint
>
> ,edition_name            varchar(30)
>
> ,padding1                varchar(4000)
>
> ,padding2                varchar(3500)
>
> ,attribute               varchar(32)
>
> ,op_type                 int
>
> ,op_time                 timestamp
>
> )
>
> CLUSTERED BY (object_id) INTO 256 BUCKETS
>
> STORED AS ORC
>
> TBLPROPERTIES ( "orc.compress"="SNAPPY",
>
> "transactional"="true",
>
> "orc.create.index"="true",
>
> "orc.bloom.filter.columns"="object_id",
>
> "orc.bloom.filter.fpp"="0.05",
>
> "orc.stripe.size"="268435456",
>
> "orc.row.index.stride"="10000" )
>
> ;
>
> INSERT INTO TABLE tt
>
> SELECT
>
>           owner
>
>         , object_name
>
>         , subobject_name
>
>         , object_id
>
>         , data_object_id
>
>         , object_type
>
>         , cast(created AS timestamp)
>
>         , cast(last_ddl_time AS timestamp)
>
>         , timestamp2
>
>         , status
>
>         , temporary2
>
>         , generated
>
>         , secondary
>
>         , namespace
>
>         , edition_name
>
>         , padding1
>
>         , padding2
>
>         , attribute
>
>         , op_type
>
>         , op_time
>
> FROM t
>
> ;
>
> exit;
>
>
>
>
>
> And tried delete again and got the same error!
>
>
>
> delete from tt where exists(select 1 from t where tt.object_id =
> t.object_id);
>
> Error: Error while compiling statement: FAILED: SemanticException [Error
> 10294]: Attempt to do update or delete using transaction manager that does
> not support these operations. (state=42000,code=10294)
>
>
>
>
>
>
>
> Mich Talebzadeh
>
>
>
> *Sybase ASE 15 Gold Medal Award 2008*
>
> A Winning Strategy: Running the most Critical Financial Data on ASE 15
>
>
> http://login.sybase.com/files/Product_Overviews/ASE-Winning-Strategy-091908.pdf
>
> Author of the books* "A Practitioner’s Guide to Upgrading to Sybase ASE
> 15", ISBN 978-0-9563693-0-7*.
>
> co-author *"Sybase Transact SQL Guidelines Best Practices", ISBN
> 978-0-9759693-0-4*
>
> *Publications due shortly:*
>
> *Complex Event Processing in Heterogeneous Environments*, ISBN:
> 978-0-9563693-3-8
>
> *Oracle and Sybase, Concepts and Contrasts*, ISBN: 978-0-9563693-1-4, volume
> one out shortly
>
>
>
> http://talebzadehmich.wordpress.com
>
>
>
> NOTE: The information in this email is proprietary and confidential. This
> message is for the designated recipient only, if you are not the intended
> recipient, you should destroy it immediately. Any information in this
> message shall not be understood as given or endorsed by Peridale Technology
> Ltd, its subsidiaries or their employees, unless expressly so stated. It is
> the responsibility of the recipient to ensure that this email is virus
> free, therefore neither Peridale Ltd, its subsidiaries nor their employees
> accept any responsibility.
>
>
>
> *From:* Mich Talebzadeh [mailto:mich@peridale.co.uk
> <javascript:_e(%7B%7D,'cvml','mich@peridale.co.uk');>]
> *Sent:* 22 December 2015 17:14
> *To:* user@hive.apache.org
> <javascript:_e(%7B%7D,'cvml','user@hive.apache.org');>
> *Subject:* RE: Attempt to do update or delete using transaction manager
> that does not support these operations. (state=42000,code=10294)
>
>
>
> Thanks Elliot,
>
>
>
> Sounds like that table was created as create table tt as select * from t.
> Although the original table t was created as transactional shown below, the
> table tt is not!
>
>
>
> 0: jdbc:hive2://rhes564:10010/default> show create table t;
>
> +-------------------------------------------------------------+--+
>
> |                       createtab_stmt                        |
>
> +-------------------------------------------------------------+--+
>
> | CREATE TABLE `t`(                                           |
>
> |   `owner` varchar(30),                                      |
>
> |   `object_name` varchar(30),                                |
>
> |   `subobject_name` varchar(30),                             |
>
> |   `object_id` bigint,                                       |
>
> |   `data_object_id` bigint,                                  |
>
> |   `object_type` varchar(19),                                |
>
> |   `created` timestamp,                                      |
>
> |   `last_ddl_time` timestamp,                                |
>
> |   `timestamp2` varchar(19),                                 |
>
> |   `status` varchar(7),                                      |
>
> |   `temporary2` varchar(1),                                  |
>
> |   `generated` varchar(1),                                   |
>
> |   `secondary` varchar(1),                                   |
>
> |   `namespace` bigint,                                       |
>
> |   `edition_name` varchar(30),                               |
>
> |   `padding1` varchar(4000),                                 |
>
> |   `padding2` varchar(3500),                                 |
>
> |   `attribute` varchar(32),                                  |
>
> |   `op_type` int,                                            |
>
> |   `op_time` timestamp,                                      |
>
> |   `new_col` varchar(30))                                    |
>
> | CLUSTERED BY (                                              |
>
> |   object_id)                                                |
>
> | INTO 256 BUCKETS                                            |
>
> | 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/asehadoop.db/t'  |
>
> | TBLPROPERTIES (                                             |
>
> |   'COLUMN_STATS_ACCURATE'='false',                          |
>
> |   'last_modified_by'='hduser',                              |
>
> |   'last_modified_time'='1449831076',                        |
>
> |   'numFiles'='17',                                          |
>
> |   'numRows'='-1',                                           |
>
> |   'orc.bloom.filter.columns'='object_id',                   |
>
> |   'orc.bloom.filter.fpp'='0.05',                            |
>
> |   'orc.compress'='SNAPPY',                                  |
>
> |   'orc.create.index'='true',                                |
>
> |   'orc.row.index.stride'='10000',                           |
>
> |   'orc.stripe.size'='268435456',                            |
>
> |   'rawDataSize'='-1',                                       |
>
> |   'totalSize'='64438212',                                   |
>
> |   'transactional'='true',                                   |
>
> |   'transient_lastDdlTime'='1449831076')                     |
>
> +-------------------------------------------------------------+--+
>
> 49 rows selected (0.06 seconds)
>
>
>
>
>
> Mich Talebzadeh
>
>
>
> *Sybase ASE 15 Gold Medal Award 2008*
>
> A Winning Strategy: Running the most Critical Financial Data on ASE 15
>
>
> http://login.sybase.com/files/Product_Overviews/ASE-Winning-Strategy-091908.pdf
>
> Author of the books* "A Practitioner’s Guide to Upgrading to Sybase ASE
> 15", ISBN 978-0-9563693-0-7*.
>
> co-author *"Sybase Transact SQL Guidelines Best Practices", ISBN
> 978-0-9759693-0-4*
>
> *Publications due shortly:*
>
> *Complex Event Processing in Heterogeneous Environments*, ISBN:
> 978-0-9563693-3-8
>
> *Oracle and Sybase, Concepts and Contrasts*, ISBN: 978-0-9563693-1-4, volume
> one out shortly
>
>
>
> http://talebzadehmich.wordpress.com
>
>
>
> NOTE: The information in this email is proprietary and confidential. This
> message is for the designated recipient only, if you are not the intended
> recipient, you should destroy it immediately. Any information in this
> message shall not be understood as given or endorsed by Peridale Technology
> Ltd, its subsidiaries or their employees, unless expressly so stated. It is
> the responsibility of the recipient to ensure that this email is virus
> free, therefore neither Peridale Ltd, its subsidiaries nor their employees
> accept any responsibility.
>
>
>
> *From:* Elliot West [mailto:teabot@gmail.com
> <javascript:_e(%7B%7D,'cvml','teabot@gmail.com');>]
> *Sent:* 22 December 2015 16:57
> *To:* user@hive.apache.org
> <javascript:_e(%7B%7D,'cvml','user@hive.apache.org');>
> *Subject:* Re: Attempt to do update or delete using transaction manager
> that does not support these operations. (state=42000,code=10294)
>
>
>
> Hi,
>
>
>
> The input/output formats do not appear to be ORC, have you tried 'stored
> as orc'? Additionally you'll need to set the property 'transactional=true'
> on the table. Do you have the original create table statement?
>
>
>
> Cheers - Elliot.
>
> On Tuesday, 22 December 2015, Mich Talebzadeh <mich@peridale.co.uk
> <javascript:_e(%7B%7D,'cvml','mich@peridale.co.uk');>> wrote:
>
> Hi,
>
>
>
> I am trying this code on table tt defined as an ORC table
>
>
>
> 0: jdbc:hive2://rhes564:10010/default> show create table tt;
>
> +-----------------------------------------------------------------+--+
>
> |                         createtab_stmt                          |
>
> +-----------------------------------------------------------------+--+
>
> | CREATE TABLE `tt`(                                              |
>
> |   `owner` varchar(30),                                          |
>
> |   `object_name` varchar(30),                                    |
>
> |   `subobject_name` varchar(30),                                 |
>
> |   `object_id` bigint,                                           |
>
> |   `data_object_id` bigint,                                      |
>
> |   `object_type` varchar(19),                                    |
>
> |   `created` timestamp,                                          |
>
> |   `last_ddl_time` timestamp,                                    |
>
> |   `timestamp2` varchar(19),                                     |
>
> |   `status` varchar(7),                                          |
>
> |   `temporary2` varchar(1),                                      |
>
> |   `generated` varchar(1),                                       |
>
> |   `secondary` varchar(1),                                       |
>
> |   `namespace` bigint,                                           |
>
> |   `edition_name` varchar(30),                                   |
>
> |   `padding1` varchar(4000),                                     |
>
> |   `padding2` varchar(3500),                                     |
>
> |   `attribute` varchar(32),                                      |
>
> |   `op_type` int,                                                |
>
> |   `op_time` timestamp,                                          |
>
> |   `new_col` varchar(30))                                        |
>
> | ROW FORMAT SERDE                                                |
>
> |   'org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe'          |
>
> | STORED AS INPUTFORMAT                                           |
>
> |   'org.apache.hadoop.mapred.TextInputFormat'                    |
>
> | OUTPUTFORMAT                                                    |
>
> |   'org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat'  |
>
> | LOCATION                                                        |
>
> |   'hdfs://rhes564:9000/user/hive/warehouse/asehadoop.db/tt'     |
>
> | TBLPROPERTIES (                                                 |
>
> |   'COLUMN_STATS_ACCURATE'='true',                               |
>
> |   'numFiles'='546',                                             |
>
> |   'numRows'='8299588',                                          |
>
> |   'rawDataSize'='18611443520',                                  |
>
> |   'totalSize'='18619743108',                                    |
>
> |   'transient_lastDdlTime'='1450337155')                         |
>
> +-----------------------------------------------------------------+--+
>
>
>
> The following delete statement:
>
>
>
> delete from tt where exists(select 1 from t where tt.object_id =
> t.object_id);
>
> Error: Error while compiling statement: FAILED: SemanticException [Error
> 10294]: Attempt to do update or delete using transaction manager that does
> not support these operations. (state=42000,code=10294)
>
>
>
>
>
>
>
> Mich Talebzadeh
>
>
>
> *Sybase ASE 15 Gold Medal Award 2008*
>
> A Winning Strategy: Running the most Critical Financial Data on ASE 15
>
>
> http://login.sybase.com/files/Product_Overviews/ASE-Winning-Strategy-091908.pdf
>
> Author of the books* "A Practitioner’s Guide to Upgrading to Sybase ASE
> 15", ISBN 978-0-9563693-0-7*.
>
> co-author *"Sybase Transact SQL Guidelines Best Practices", ISBN
> 978-0-9759693-0-4*
>
> *Publications due shortly:*
>
> *Complex Event Processing in Heterogeneous Environments*, ISBN:
> 978-0-9563693-3-8
>
> *Oracle and Sybase, Concepts and Contrasts*, ISBN: 978-0-9563693-1-4, volume
> one out shortly
>
>
>
> http://talebzadehmich.wordpress.com
>
>
>
> NOTE: The information in this email is proprietary and confidential. This
> message is for the designated recipient only, if you are not the intended
> recipient, you should destroy it immediately. Any information in this
> message shall not be understood as given or endorsed by Peridale Technology
> Ltd, its subsidiaries or their employees, unless expressly so stated. It is
> the responsibility of the recipient to ensure that this email is virus
> free, therefore neither Peridale Ltd, its subsidiaries nor their employees
> accept any responsibility.
>
>
>
>

Mime
View raw message