# All data from Elasticspark sql queries returns as null
**URL:** https://discuss.elastic.co/t/all-data-from-elasticspark-sql-queries-returns-as-null/173560
**Category:** Elasticsearch
**Tags:** es-hadoop
**Created:** [March 22, 2019, 9:12pm UTC](https://discuss.elastic.co/t/all-data-from-elasticspark-sql-queries-returns-as-null/173560 "2019-03-22T21:12:49Z")
**Posts on this page:** 4
**Page:** 1
### Author:  [@bytemedwb](https://discuss.elastic.co/u/bytemedwb)
#### Post date: [March 22, 2019, 9:12pm UTC](https://discuss.elastic.co/t/all-data-from-elasticspark-sql-queries-returns-as-null/173560/1 "2019-03-22T21:12:49Z")
So having gotten further by doing a build of the 8.0.0-SNAPSHOT from source, I now have a new issue.
I am able to create data frames and print schemas. However, every query returns null for every field. Here is my code and the schemas:
```
val dsParams = Map(
"es.nodes.wan.only" -> "true",
"es.port" -> "9201",
"es.net.ssl" -> "true",
"es.net.ssl.cert.allow.self.signed" -> "true",
"es.net.ssl.truststore.location" -> "file:///etc/pki/java/truststore.jks",
"es.net.ssl.truststore.type" -> "JKS",
"es.net.ssl.truststore.pass"-> "changeit",
"es.net.ssl.keystore.location" -> "file:///etc/pki/java/keystore.jks",
"es.net.ssl.keystore.type" -> "jks",
"es.net.ssl.keystore.pass"-> "changeit",
"es.clustername" -> "BDP_oedl.dev.oedl.tradoc.army.mil",
"es.nodes" -> "oedevnode06.dev.oedl.tradoc.army.mil"
)
// Create DataFrame using the "es" format
val docFrame = spark.read.format("es").options(dsParams).load("coalesce-oedocument")
docFrame.createOrReplaceTempView("esdocview")
// Create DataFrame using the "es" format
val eventFrame = spark.read.format("es").options(dsParams).load("coalesce-oeevent")
eventFrame.createOrReplaceTempView("eseventview")
docFrame.printSchema()
eventFrame.printSchema()
val esdocs = spark.sql("select recordset.coalesceentity.objectkey from esdocview limit 20")
esdocs.show()
val esevents = spark.sql("select recordset.coalesceentity.objectkey from eseventview limit 20")
esdocs.show()
```
> root
> |-- recordset: struct (nullable = true)
> | |-- coalesceentity: struct (nullable = true)
> | | |-- datecreated: timestamp (nullable = true)
> | | |-- entityid: string (nullable = true)
> | | |-- entityidtype: string (nullable = true)
> | | |-- lastmodified: timestamp (nullable = true)
> | | |-- name: string (nullable = true)
> | | |-- objectkey: string (nullable = true)
> | | |-- source: string (nullable = true)
> | | |-- status: string (nullable = true)
> | | |-- title: string (nullable = true)
> | | |-- version: string (nullable = true)
> | |-- oedocumentrecordset: struct (nullable = true)
> | | |-- categories: string (nullable = true)
> | | |-- content: string (nullable = true)
> | | |-- contentlength: long (nullable = true)
> | | |-- datasource: string (nullable = true)
> | | |-- dateingested: timestamp (nullable = true)
> | | |-- documentdate: timestamp (nullable = true)
> | | |-- documentlastmodifieddate: timestamp (nullable = true)
> | | |-- documentsource: string (nullable = true)
> | | |-- documenttitle: string (nullable = true)
> | | |-- documenttype: string (nullable = true)
> | | |-- issimulation: boolean (nullable = true)
> | | |-- md5sum: string (nullable = true)
> | | |-- ner\_date: string (nullable = true)
> | | |-- ner\_location: string (nullable = true)
> | | |-- ner\_money: string (nullable = true)
> | | |-- ner\_organization: string (nullable = true)
> | | |-- ner\_percent: string (nullable = true)
> | | |-- ner\_person: string (nullable = true)
> | | |-- ner\_time: string (nullable = true)
> | | |-- ontologyreference: string (nullable = true)
> | | |-- pmesiipteconomic: float (nullable = true)
> | | |-- pmesiiptinformation: float (nullable = true)
> | | |-- pmesiiptinfrastructure: float (nullable = true)
> | | |-- pmesiiptmilitary: float (nullable = true)
> | | |-- pmesiiptphysicalenvironment: float (nullable = true)
> | | |-- pmesiiptpolitical: float (nullable = true)
> | | |-- pmesiiptsocial: float (nullable = true)
> | | |-- pmesiipttime: float (nullable = true)
> | | |-- sourceuri: string (nullable = true)
> | | |-- tags: string (nullable = true)
> | | |-- wordcount: long (nullable = true)
>
> root
> |-- recordset: struct (nullable = true)
> | |-- coalesceentity: struct (nullable = true)
> | | |-- datecreated: timestamp (nullable = true)
> | | |-- entityid: string (nullable = true)
> | | |-- entityidtype: string (nullable = true)
> | | |-- lastmodified: timestamp (nullable = true)
> | | |-- name: string (nullable = true)
> | | |-- objectkey: string (nullable = true)
> | | |-- source: string (nullable = true)
> | | |-- status: string (nullable = true)
> | | |-- title: string (nullable = true)
> | | |-- version: string (nullable = true)
> | |-- eventrecordset: struct (nullable = true)
> | | |-- actiongeoadm1code: string (nullable = true)
> | | |-- actiongeoadm2code: string (nullable = true)
> | | |-- actiongeocountrycode: string (nullable = true)
> | | |-- actiongeofeatureid: string (nullable = true)
> | | |-- actiongeofullname: string (nullable = true)
> | | |-- actiongeolocation: struct (nullable = true)
> | | | |-- lat: double (nullable = true)
> | | | |-- lon: double (nullable = true)
> | | |-- actiongeotype: integer (nullable = true)
> | | |-- avgtone: float (nullable = true)
> | | |-- datasource: string (nullable = true)
> | | |-- dateadded: integer (nullable = true)
> | | |-- datetime: timestamp (nullable = true)
> | | |-- day: integer (nullable = true)
> | | |-- eventbasecode: string (nullable = true)
> | | |-- eventcode: string (nullable = true)
> | | |-- eventrootcode: string (nullable = true)
> | | |-- fractiondate: float (nullable = true)
> | | |-- globaleventid: integer (nullable = true)
> | | |-- goldsteinscale: float (nullable = true)
> | | |-- isrootevent: integer (nullable = true)
> | | |-- issimulation: boolean (nullable = true)
> | | |-- monthyear: integer (nullable = true)
> | | |-- numarticles: integer (nullable = true)
> | | |-- nummentions: integer (nullable = true)
> | | |-- numsources: integer (nullable = true)
> | | |-- ontologyreference: string (nullable = true)
> | | |-- pmesiipteconomic: float (nullable = true)
> | | |-- pmesiiptinformation: float (nullable = true)
> | | |-- pmesiiptinfrastructure: float (nullable = true)
> | | |-- pmesiiptmilitary: float (nullable = true)
> | | |-- pmesiiptphysicalenvironment: float (nullable = true)
> | | |-- pmesiiptpolitical: float (nullable = true)
> | | |-- pmesiiptsocial: float (nullable = true)
> | | |-- pmesiipttime: float (nullable = true)
> | | |-- quadclass: integer (nullable = true)
> | | |-- sourceurl: string (nullable = true)
> | | |-- tags: string (nullable = true)
> | | |-- year: integer (nullable = true)
Both queries return nulls.
I am using Elastic 5.4.3 . All works fine in Kibana and java.
---
### Author:  [@Alishan\_Merchant](https://discuss.elastic.co/u/Alishan_Merchant)
#### Post date: [April 2, 2019, 9:11am UTC](https://discuss.elastic.co/t/all-data-from-elasticspark-sql-queries-returns-as-null/173560/2 "2019-04-02T09:11:59Z")
I am having a similar problem, did you get to fix it ?
Anyone who could help us out here ...
Following are my configuration:
Hadoop 2.7.3
ES 2.3.3 / Lucene 5.5.0
spark 2.2.0
I can access the data using Kibana and ealstic-head , unable to access it with eshadoop 6.0
---
### Author:  [@bytemedwb](https://discuss.elastic.co/u/bytemedwb)
#### Post date: [April 2, 2019, 11:33am UTC](https://discuss.elastic.co/t/all-data-from-elasticspark-sql-queries-returns-as-null/173560/3 "2019-04-02T11:33:23Z")
Nothing so far. Trying to dig down in the code but I am no Scala expert.
---
### Author:  [@system](https://discuss.elastic.co/u/system)
#### Post date: [April 30, 2019, 11:33am UTC](https://discuss.elastic.co/t/all-data-from-elasticspark-sql-queries-returns-as-null/173560/4 "2019-04-30T11:33:30Z")
This topic was automatically closed 28 days after the last reply. New replies are no longer allowed.