# Spark SQL thriftserver DDL?

**URL:** https://discuss.elastic.co/t/spark-sql-thriftserver-ddl/42450
**Category:** Elasticsearch
**Tags:** es-hadoop
**Created:** [February 23, 2016, 2:52am UTC](https://discuss.elastic.co/t/spark-sql-thriftserver-ddl/42450 "2016-02-23T02:52:52Z")
**Posts on this page:** 4
**Page:** 1

<div class="post-metadata">

### Author: ![andrewwwooster](https://avatars.discourse-cdn.com/v4/letter/a/da6949/32.png) [@andrewwwooster](https://discuss.elastic.co/u/andrewwwooster)
#### Post date: [February 23, 2016, 2:52am UTC](https://discuss.elastic.co/t/spark-sql-thriftserver-ddl/42450/1 "2016-02-23T02:52:52Z")

</div>

I have a working example using the Elasticsearch/Hive SQL connector. It allows me to use Tableau to query Elasticsearch. Here is my working Hive SQL DDL:

```auto
CREATE EXTERNAL TABLE biblio_raw (
    url STRING,
   content_text_array ARRAY<STRING>
)
STORED BY 'org.elasticsearch.hadoop.hive.EsStorageHandler'
TBLPROPERTIES(
  'es.port' = '11100', 
  'es.resource' = 'my_index/doc', 
  'es.mapping.names' = 'content_text_array:contentText'
);

```

The Hive queries are painfully slow. I now want to create a similar view using Spark so that I can take advantage of the elasticsearch / Spark push-down capabilities.

I start the Spark thrift server as follows:

```auto
$SPARK_HOME/sbin/start-thriftserver.sh --master spark://xxx.com:11407 --hiveconf hive.server2.thrift.port=11410 --jars ~/workspace/lib/elasticsearch-spark_2.10-2.2.0-rc1.jar

```

It is not clear to me what the equivalent Spark SQL DDL to create the view above. I tried the following without success:

```auto
CREATE EXTERNAL TABLE biblio_raw (
    url STRING,
    content_text_array ARRAY<STRING>
)
STORED BY 'org.elasticsearch.spark.sql'
TBLPROPERTIES(
  'es.port' = '11100', 
  'es.resource' = 'my_index/doc', 
  'es.mapping.names' = 'content_text_array:contentText'
);

```

It failed with the error: `Cannot find class 'org.elasticsearch.spark.sql'`

---

<div class="post-metadata">

### Author: ![andrewwwooster](https://avatars.discourse-cdn.com/v4/letter/a/da6949/32.png) [@andrewwwooster](https://discuss.elastic.co/u/andrewwwooster)
#### Post date: [February 24, 2016, 5:27am UTC](https://discuss.elastic.co/t/spark-sql-thriftserver-ddl/42450/2 "2016-02-24T05:27:13Z")

</div>

I've figured out the corresponding Spark SQL syntax. The following temporary table works:

```auto
CREATE TEMPORARY TABLE biblio_raw (
   url STRING,
  content_text_array ARRAY<STRING>
)
USING org.elasticsearch.spark.sql 
OPTIONS (
  path 'my_index/doc', 
  query '?q=mouse',
  es.port '11100',
  es.mapping.names 'content_text_array:contentText'
);

```

HOWEVER, the es.mapping.names do NOT work. The above DDL does not map content\_text\_array SQL field to the contentText ES field. How do I express mappings with Elasticsearch/Spark SQL?

---

<div class="post-metadata">

### 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: [March 3, 2016, 9:31am UTC](https://discuss.elastic.co/t/spark-sql-thriftserver-ddl/42450/3 "2016-03-03T09:31:22Z")

</div>

`es.mapping.names` is not a global configuration - it is available in Hive (mainly because Hive has various issues including being case insensitive) while Spark does not. Use the upper case variable and you should be all set.

P.S. Note that the `query` param makes sense when reading; you can use it for writing however many find it confusing when they can't read back data that they write (since it does not match the query bound to the table).

---

<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: [July 6, 2017, 1:26pm UTC](https://discuss.elastic.co/t/spark-sql-thriftserver-ddl/42450/4 "2017-07-06T13:26:02Z")

</div>