How to create an external Table on ES document uisng Spark SQL(ES-Hadoop 6.7.0 and ES 6.7.0)

[Srinivas2 (Srinivas)]

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

[james.baiera]

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

[Srinivas2 (Srinivas)]

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