delete and update hive

[#1]

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??

[#2]

@Roberto Sancho

To start with please see this https://community.hortonworks.com/content/kbentry/4321/hive-acid-current-state.html

[#3]

@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.

[#4]

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??

[#5]

@Roberto Sancho Can you share your ddl?

[#6]

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.

[#7]

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

[#8]

ACID is not yet integrated with HCatalog

[#9]

@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.

[#10]

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??