Support Questions

Find answers, ask questions, and share your expertise
Announcements
Celebrating as our community reaches 100,000 members! Thank you!

delete and update hive

avatar
Master Collaborator

Hi:

Please how can i delete some row in hive from beeline or hue or sqldeveloper?

CREATE EXTERNAL TABLE roberto_delete( 
  WORD string
  ) 
ROW FORMAT DELIMITED 
FIELDS TERMINATED BY ','
LINES TERMINATED BY '\n'
stored as ORC
LOCATION '/RSI/tables/logs/roberto_delete'
TBLPROPERTIES ("immutable"="false");

I set this atributes:

0: jdbc:hive2://lnxbig05.cajarural.gcr:10000> delete from roberto_delete where word='1';
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)


after activate ACID y see this error:

Error: Error while compiling statement: FAILED: SemanticException [Error 10297]: Attempt to do update or delete on table default.roberto_delete that does not use an AcidOutputFormat or is not bucketed (state=42000,code=10297)


Please how can i delete one row??

9 REPLIES 9

avatar
Master Mentor

avatar
Master Mentor

@Roberto Sancho See this https://cwiki.apache.org/confluence/display/Hive/Hive+Transactions#HiveTransactions-Limitations

  • Tables must be bucketed to make use of these features. Tables in the same system not using transactions and ACID do not need to be bucketed.

avatar
Master Collaborator

Hi:

After create a table with partition and try to delete i receive this error:

set hive.support.concurrency=true
set hive.enforce.bucketing=true
set hive.exec.dynamic.partition.mode=nonstrict
set hive.txn.manager=org.apache.hadoop.hive.ql.lockmgr.DbTxnManager
set hive.compactor.initiator.on = true
set hive.compactor.worker.threads = 1
delete from wordcount_delete where word='amazon';




0: jdbc:hive2://lnxbig05.cajarural.gcr:10000> delete from roberto_delete where word="amazon";
Error: Error while compiling statement: FAILED: SemanticException [Error 10001]: Table not found default.roberto_delete (state=42S02,code=10001)


the table need to be with kust with bucket??

avatar
Master Mentor

@Roberto Sancho Can you share your ddl?

avatar
Super Guru

As mentioned in apache hive doc about ACID limitation, for ACID support table should be in ORC format plus non sorted and bucket enabled.

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

Hope that will help.

avatar
Master Collaborator

HI:

i an trying tio creatte ORC, buckered table and insert and delete from hcatalog with pig,

i create the table like this:

CREATE EXTERNAL TABLE canal_v4( 
  CODTF string, 
  CODNRBEENF string, 
  FECHAOPRCNF date,
  CANAL string,
  FRECUENCIA int,
  year string,
  month string)
 COMMENT 'A bucketed canal table'
CLUSTERED BY (codnrbeenf) INTO 25 BUCKETS
ROW FORMAT DELIMITED 
FIELDS TERMINATED BY ','
LINES TERMINATED BY '\n'
stored as ORC
LOCATION '/RSI/tables/logs/canal_v4'
TBLPROPERTIES ("immutable"="false");

how can i Insert with Pig??? i need somenthing??

is good to use partitioned with bucked??

Thanks

avatar
Super Collaborator

ACID is not yet integrated with HCatalog

avatar
Contributor

@Jitendra Yadav

Similar to Partitioned tables we cannot directly load bucketed tables rather we need to use INSERT OVERWRITE TABLE... SELECT.. FROM clause from another table to populate the bucketed table. Also hive.enforce.bucketing should be set to TRUE, so that number of reducers need not be specified exclusively. Kindly let me know if you find this useful.

avatar
Master Collaborator

finally I insert with bucker like this:

CREATE EXTERNAL TABLE IF NOT EXISTS journey_importe_v2(
  FECHAOPRCNF date,
  codnrbeenf string,
  codnrbeenf2 string,
  CODTXF string,
  FREQ BIGINT,
  IMPORTE DECIMAL(9, 2)
  )
CLUSTERED BY (codnrbeenf) INTO 25 BUCKETS
ROW FORMAT DELIMITED
FIELDS TERMINATED BY ','
LINES TERMINATED BY '\n'
stored as ORC
LOCATION '/RSI/tables/logs/importe_v2'
TBLPROPERTIES ("immutable"="false","transactional"="true");

create table IF NOT EXISTS  temp_journey_importe_v2 (importe STRING);

LOAD DATA INPATH '/RSI/staging/output/journey_importe/${date}' OVERWRITE INTO TABLE temp_journey_importe_v2;

set hive.enforce.bucketing = true;
INSERT INTO TABLE journey_importe_v2
SELECT  
  regexp_extract(importe, '^(?:([^,]*)\,?){1}', 1) FECHAOPRCNF,
  regexp_extract(importe, '^(?:([^,]*)\,?){2}', 1) codnrbeenf,
  regexp_extract(importe, '^(?:([^,]*)\,?){3}', 1) codnrbeenf2,
  regexp_extract(importe, '^(?:([^,]*)\,?){4}', 1) CODTXF,
  regexp_extract(importe, '^(?:([^,]*)\,?){5}', 1) FREQ,
  regexp_extract(importe, '^(?:([^,]*)\,?){6}', 1) IMPORTE
from temp_journey_importe_v2;

there is a better way?? how many buckets recomend me use??