- Subscribe to RSS Feed
- Mark Question as New
- Mark Question as Read
- Float this Question for Current User
- Bookmark
- Subscribe
- Mute
- Printer Friendly Page
delete and update hive
- Labels:
-
Apache Hive
Created ‎03-07-2016 10:55 AM
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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??
Created ‎03-07-2016 10:57 AM
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
To start with please see this https://community.hortonworks.com/content/kbentry/4321/hive-acid-current-state.html
Created ‎03-07-2016 11:05 AM
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
@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.
Created ‎03-07-2016 11:33 AM
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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??
Created ‎03-07-2016 11:59 AM
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
@Roberto Sancho Can you share your ddl?
Created ‎03-07-2016 02:21 PM
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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.
Created ‎03-09-2016 03:49 PM
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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
Created ‎03-12-2016 02:31 AM
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
ACID is not yet integrated with HCatalog
Created ‎03-12-2016 05:29 AM
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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.
Created ‎03-26-2016 08:13 AM
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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??
