# Truncate or update Elastic Hive tables **URL:** \ **Category:** Elasticsearch\ **Tags:** es-hadoop\ **Created:** [April 27, 2016, 1:03am UTC](https://discuss.elastic.co/t/truncate-or-update-elastic-hive-tables/48493 "2016-04-27T01:03:31Z")\ **Posts on this page:** 6\ **Page:** 1
**Author:** ![yuan0122](https://avatars.discourse-cdn.com/v4/letter/y/b9bd4f/32.png) [@yuan0122](https://discuss.elastic.co/u/yuan0122)\ **Post date:** [April 27, 2016, 1:03am UTC](https://discuss.elastic.co/t/truncate-or-update-elastic-hive-tables/48493/1 "2016-04-27T01:03:31Z")
Hi, all. I am using Elasticsearch Hive integration, so that I can query from Hadoop tables, sending alerts when data is bad (with ElastAlert), as well as display on Kibana. This is how I created the Elastic table: > CREATE EXTERNAL TABLE my\_elastic\_table ( > `timestamp` BIGINT, > count BIGINT > ) > STORED BY 'org.elasticsearch.hadoop.hive.EsStorageHandler' > TBLPROPERTIES('es.resource' = 'my\_index/my\_type); And I inserted into Elastic Hive table with: > INSERT OVERWRITE TABLE my\_elastic\_table > SELECT {something} FROM my\_hadoop\_table; However, it didn't really \> OVERWRITE elastic\_table, it actually appended to elastic\_table. So I tried to TRUNCATE elastic\_table, and it gave me the following error: > FAILED: SemanticException [Error 10146]: Cannot truncate non-managed table elastic\_table. So I am asking if anyone know how to truncate, update or overwrite Elastic Hive tables. Or is there any better way to deal with this kind of problem. Thank you! ---
**Author:** ![costin](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/costin/32/44950_2.png) [@costin](https://discuss.elastic.co/u/costin)\ **Post date:** [April 30, 2016, 2:10pm UTC](https://discuss.elastic.co/t/truncate-or-update-elastic-hive-tables/48493/2 "2016-04-30T14:10:40Z")
If you want to delete data, it's best to do it directly in Elasticsearch. `TRUNCATE` and other delete operations are not supported in Hive since manly they assume files on HDFS (or the so-called `managed` tables`). Which is not the case with ES. Deleting content is ES is quite easy - it can be a simple curl call for a given index. ---
**Author:** ![yuan0122](https://avatars.discourse-cdn.com/v4/letter/y/b9bd4f/32.png) [@yuan0122](https://discuss.elastic.co/u/yuan0122)\ **Post date:** [May 2, 2016, 6:12pm UTC](https://discuss.elastic.co/t/truncate-or-update-elastic-hive-tables/48493/3 "2016-05-02T18:12:11Z")
Hi Costin, thanks for your reply. Instead of delete, is `UPDATE` supported? From [Apache Hive integration Documentation](https://www.elastic.co/guide/en/elasticsearch/hadoop/current/hive.html). I see `insert overwrite` hive tables. However, as I tested a lot, it didn't really `OVERWRITE` the tables. Can I ask the reason for using `insert overwrite` in the documentation? Thanks a lot! ---
**Author:** ![costin](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/costin/32/44950_2.png) [@costin](https://discuss.elastic.co/u/costin)\ **Post date:** [May 10, 2016, 7:59am UTC](https://discuss.elastic.co/t/truncate-or-update-elastic-hive-tables/48493/4 "2016-05-10T07:59:24Z")
`INSERT OVERWRITE` is a bit of a misnomer. Due to the way Hive works `OVERWRITE` simply tells it to not pay attention to the table metadata (if its exists). Internally however the data is not updated; further more as a Hive adaptor, ES-Hadoop is unaware of whether `INSERT` or `INSERT OVERWRITE` was being used; Hive infrastructure doesn't provide any information on this front. As a comparison Spark SQL for example does support different update modes (`SaveMode`s as they are called). Due to API limitations, one is best in accessing ES directly when doing data management and use Hive for reads and writes. Hope this helps, ---
**Author:** ![costin](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/costin/32/44950_2.png) [@costin](https://discuss.elastic.co/u/costin)\ **Post date:** [May 10, 2016, 8:19am UTC](https://discuss.elastic.co/t/truncate-or-update-elastic-hive-tables/48493/5 "2016-05-10T08:19:10Z")
By the way, if you want to do updates see `es.write.operation` parameter which instructs ES-Hadoop to `update` data in ES instead of just indexing it as mentioned [here](https://www.elastic.co/guide/en/elasticsearch/hadoop/master/configuration.html#_operation). ---
**Author:** ![system](https://us1.discourse-cdn.com/elastic/original/3X/1/a/1ac57faf039f6b580b3f104ef42a2a89e41014de.png) [@system](https://discuss.elastic.co/u/system)\ **Post date:** [July 6, 2017, 1:24pm UTC](https://discuss.elastic.co/t/truncate-or-update-elastic-hive-tables/48493/6 "2017-07-06T13:24:45Z")