# How to create an external Table on ES document uisng Spark SQL(ES-Hadoop 6.7.0 and ES 6.7.0)
**URL:** https://discuss.elastic.co/t/how-to-create-an-external-table-on-es-document-uisng-spark-sql-es-hadoop-6-7-0-and-es-6-7-0/195228
**Category:** Elasticsearch
**Tags:** es-hadoop
**Created:** [August 14, 2019, 3:22pm UTC](https://discuss.elastic.co/t/how-to-create-an-external-table-on-es-document-uisng-spark-sql-es-hadoop-6-7-0-and-es-6-7-0/195228 "2019-08-14T15:22:31Z")
**Posts on this page:** 4
**Page:** 1
### Author:  [@Srinivas2](https://discuss.elastic.co/u/Srinivas2)
#### Post date: [August 14, 2019, 3:22pm UTC](https://discuss.elastic.co/t/how-to-create-an-external-table-on-es-document-uisng-spark-sql-es-hadoop-6-7-0-and-es-6-7-0/195228/1 "2019-08-14T15:22:31Z")
Hi Team,
Requesting you help. Is it possible to create a table using spark-sql like mentioned here (`https://docs.databricks.com/spark/latest/data-sources/elasticsearch.html` [Use SQL to access Elasticsearch index])
I am able to create external table successfully on top of ES document using ES-hive using ESStorage Handler , however I am unable to create a table using spark-sql synstax ,and using below command.
```
create table elasticsearch_poc
using org.elasticsearch.spark.sql
options('resource'='indexname',
'nodes'= 'node name',
'es.nodes.wan.only'='true',
'es.port'='portnumber'
);
```
Below command is using to create table(successfully) using HIVE.
```
create external table elasticserach_test(col1 STRING,col2 STRING)
STORED BY 'org.elasticsearch.hadoop.hive.EsStorageHandler'
TBLPROPERTIES(
'es.nodes'='nodeaddress',
'es.port'='portnumber',
'es.resource'='resourcenamet' ,
'es.nodes.wan.only' = 'true',
'es.nodes.discover'='true',
'es.mapping.names'='col1:col1,col2:col2'
)
```
Regards,
Srini
---
### Author:  [@james.baiera](https://discuss.elastic.co/u/james.baiera)
#### Post date: [August 14, 2019, 7:27pm UTC](https://discuss.elastic.co/t/how-to-create-an-external-table-on-es-document-uisng-spark-sql-es-hadoop-6-7-0-and-es-6-7-0/195228/2 "2019-08-14T19:27:32Z")
I would take a look at the documentation for setting up tables in SparkSQL using ES-Hadoop: [https://www.elastic.co/guide/en/elasticsearch/hadoop/current/spark.html#spark-data-sources-12](https://www.elastic.co/guide/en/elasticsearch/hadoop/current/spark.html#spark-data-sources-12)
---
### Author:  [@Srinivas2](https://discuss.elastic.co/u/Srinivas2)
#### Post date: [August 15, 2019, 6:39pm UTC](https://discuss.elastic.co/t/how-to-create-an-external-table-on-es-document-uisng-spark-sql-es-hadoop-6-7-0-and-es-6-7-0/195228/3 "2019-08-15T18:39:41Z")
Thank You James for looking into the issue.
Just to reiterate . Using ESHandler(elasticsearch-hive) I am able to create a table and able to create a temporary table using (ES-Spark) .However unable to create permanent table using ES-Spark (spark-sql syntax) .
---
### Author:  [@system](https://discuss.elastic.co/u/system)
#### Post date: [September 12, 2019, 6:39pm UTC](https://discuss.elastic.co/t/how-to-create-an-external-table-on-es-document-uisng-spark-sql-es-hadoop-6-7-0-and-es-6-7-0/195228/4 "2019-09-12T18:39:46Z")
This topic was automatically closed 28 days after the last reply. New replies are no longer allowed.