1. REST API for Big Data Spatial and Graph
  2. Tasks
  3. Vector Spark API

Submit a SQL query and return results.

post

/spatialviewer/api/v1/spark/sqlQuery

Submit a SQL query to Spark and return results.

Request

Supported Media Types
Query Parameters
  • if true all the output columns except the geometry will be double quoted
    Default Value: false
  • The name of the analysis. This parameter is required when outputAsAnalysis is set to true
  • if true the operation is runned asynchronously.
    Default Value: false
  • if false and asynchronously is false then the response contains the location of the results in HDFS and not the actual results.
    Default Value: true
  • if true the index will be added to the analysis results of the SpatialViewer UI
    Default Value: false
  • if true the service returns the response as GeoJSON that can be used as data source for the Map API to display the results on map. The geometry has to be returned in a field named geometry. Example: SELECT geometry, friends_count...
    Default Value: false
Body ()
SQL query details
Root Schema : SQLQueryInfo
Type: object
Show Source
  • jar name with the user-defined classes like custom RecordInfoProvider or InputFormat. If the InputFormat class or the RecordInfoProvider class is not in the API, or in the hadoop API classes, then a jar with the user-defined classes must be provided. To be able to use this jar the user must add it in the /opt/oracle/oracle-spatial-graph/spatial/web-server/spatialviewer/WEB-INF/lib directory and restart the server. Optional property that can be set if useIndex is false.
    Example: myjar.jar
  • The job output path. Specify this property if asynchronous is true.
    Example: /user/oracle/spatialviewer/SQLQueryResult
  • The SQL query.
    Example: SELECT geometry, friends_count, location, followers_count FROM tweets WHERE ST_Contains( ST_Polygon('{"type": "Polygon","coordinates": [[[-106, 25], [-106, 30], [-104, 30], [-104, 25], [-106, 25]]]}', 8307) , ST_Point(geometry, 8307) , 0.05) and followers_count > 50
  • Index of the output columns that will be double quoted. The index are comma separated. The first index is 0.
    Example: 2,3
  • tables
Nested Schema : tables
Type: array
Show Source
Nested Schema : Table
Type: object
Show Source
Nested Schema : columnNames
Type: array
The extra fields of the RecordInfoProvider to include in the columns of the table. Note that the column geometry will always be the first column and will contain the geometry in GeoJSON format.
Show Source
  • Example: friends_count location followers_count
Example:
[
    "friends_count",
    "location",
    "followers_count"
]
Back to Top

Response

Supported Media Types

200 Response

successful request. If fullResult is false then the response contains the location of the results in HDFS.
Body ()
Root Schema : SparkSQLRecordsInfo
Type: object
Show Source
Nested Schema : items
Type: array
Show Source
Nested Schema : SparkSQLRecordExample
Type: object
Show Source
Nested Schema : geometry
Type: object
Example:
{
    "type":"Point",
    "coordinates":[
        -3.6853,
        40.40197
    ]
}

202 Response

accepted asynchronous request
Body ()
Root Schema : AsyncSparkSQLJobResponse
Type: object
Show Source
Back to Top

Examples

The following example shows how to execute a Spark SQL Query by submitting a POST request on the REST resource using cURL.

curl -v -X POST "http://localhost:8045/spatialviewer/api/v1/spark/sqlQuery?fullResult=true" -H  "accept: application/json" -H  "content-type: application/json" -d "{  \"query\": \"SELECT geometry, friends_count, location, followers_count FROM tweets WHERE ST_Contains( ST_Polygon('{\\\"type\\\": \\\"Polygon\\\",\\\"coordinates\\\": [[[-106, 25], [-106, 30], [-104, 30], [-104, 25], [-106, 25]]]}', 8307) , ST_Point(geometry, 8307) , 0.05) and followers_count > 50\",  \"stringTypeColumns\": \"2,3\",  \"tables\": [    {      \"name\": \"tweets\",      \"columnNames\": [        \"friends_count\",        \"location\",        \"followers_count\"      ],      \"srid\": 8307,      \"dataPath\": \"/user/oracle/tweets.json\",      \"inputFormatClass\": \"oracle.spatial.hadoop.vector.geojson.mapred.GeoJsonInputFormat\",      \"keyClass\": \"org.apache.hadoop.io.LongWritable\",      \"valueClass\": \"org.apache.hadoop.io.Text\",      \"recordInfoProviderClass\": \"oracle.spatial.spark.vector.recordinfoprovider.GeoJsonRecordInfoProvider\"    }  ]}"

Example of Response Header

The following shows an example of the response header.

HTTP/1.1 200 OK Date: Wed, 15 Nov 2017 17:50:38 GMT Access-Control-Allow-Origin: * Access-Control-Allow-Credentials: true Access-Control-Allow-Methods: GET, POST, DELETE, PUT Access-Control-Allow-Headers: Content-Type, Accept Transfer-Encoding: chunked  Content-Type: application/json

Example of Response Body

The following example shows the contents of the response body in JSON format, including one record:

{"items":[{"geometry":{"type":"Point","coordinates":[-104.41002,29.5404]},"friends_count":72,"location":"Chihuahua","followers_count":"94"}]}
Back to Top