# Insert into externe table Hive

**URL:** <https://discuss.elastic.co/t/insert-into-externe-table-hive/163482>\
**Category:** Elasticsearch\
**Tags:** es-hadoop\
**Created:** [January 9, 2019, 9:06am UTC](https://discuss.elastic.co/t/insert-into-externe-table-hive/163482 "2019-01-09T09:06:31Z")\
**Posts on this page:** 2\
**Page:** 1

<div class="post-metadata">

**Author:** ![anass](https://avatars.discourse-cdn.com/v4/letter/a/d07c76/32.png) [@anass](https://discuss.elastic.co/u/anass)\
**Post date:** [January 9, 2019, 9:06am UTC](https://discuss.elastic.co/t/insert-into-externe-table-hive/163482/1 "2019-01-09T09:06:31Z")

</div>

Hi,  
i want to insert into externe table Hive but i have this error.

Error: java.lang.NoClassDefFoundError: org/elasticsearch/hadoop/mr/EsOutputFormat$EsOutputCommitter

### 

ADD JAR hdfs:/user/lib/elastic/commons-httpclient-3.1.jar;  
ADD JAR hdfs:/user/lib/elastic/elasticsearch-hadoop-hive-2.3.3.jar;

CREATE EXTERNAL TABLE IF NOT EXISTS test (  
id\_indicateur string ,  
date\_indicateur timestamp,  
DQ\_cons\_abo\_date\_reference date ,  
DQ\_cons\_type\_consentement string,  
DQ\_cons\_source string ,  
DQ\_cons\_etat string ,  
DQ\_abo\_kafka\_statut string ,  
DQ\_abo\_code\_retour string ,  
DQ\_abo\_interpretation\_etat string ,  
DQ\_cons\_abo\_nb\_bp\_prm bigint ,  
DQ\_cons\_abo\_nb\_bp\_prm\_cumul bigint  
)  
COMMENT 'Table externe pour export vers ELK'  
STORED BY 'org.elasticsearch.hadoop.hive.EsStorageHandler'  
TBLPROPERTIES('es.resource'='{ELASTIC\_INDEX\_NAME\_VALUE}/{TYPE\_ABO\_VALUE}','es.index.auto.create'='false','es.nodes'='${NODES\_ELASTICSEARCH\_VALUE}','es.nodes.discovery'='false','es.nodes.client.only'='fa  
lse','es.nodes.wan.only'='true');

INSERT INTO TABLE test  
SELECT  
'A' AS id\_indicateur,  
CURRENT\_TIMESTAMP as date\_indicateur,  
date\_sub(CURRENT\_DATE(),1) AS DQ\_cons\_abo\_date\_reference,  
B AS dq\_cons\_type\_consentement,  
t\_iddemandeur AS DQ\_cons\_source,  
'ACCEPTE' AS DQ\_cons\_etat,  
C AS abo\_kfk\_statut,  
D AS abo\_dem\_code\_retour,  
'ABO\_ACCEPTE' AS DQ\_abo\_interpretation\_etat,  
count(distinct (case when TO\_DATE(date\_de\_modification) = date\_sub(CURRENT\_DATE(),1) then CONCAT(bp,pdl) else null end)) as DQ\_cons\_abo\_nb\_bp\_prm ,  
count(distinct CONCAT(bp,pdl)) AS DQ\_cons\_abo\_nb\_bp\_prm\_cumul  
FROM test2

GROUP BY codeobjetconsentement, t\_iddemandeur, abo\_kfk\_statut, abo\_dem\_code\_retour  
;

---

<div class="post-metadata">

**Author:** ![system](https://us1.discourse-cdn.com/elastic/original/3X/1/a/1ac57faf039f6b580b3f104ef42a2a89e41014de.png) [@system](https://discuss.elastic.co/u/system)\
**Post date:** [February 6, 2019, 9:06am UTC](https://discuss.elastic.co/t/insert-into-externe-table-hive/163482/2 "2019-02-06T09:06:32Z")

</div>

This topic was automatically closed 28 days after the last reply. New replies are no longer allowed.