Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

Sign up for GitHub

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Jump to bottom

can't figure out "Nested arrays are not supported" error #322

Closed
pinkerltm opened this issue Jun 12, 2020 · 4 comments
Closed

can't figure out "Nested arrays are not supported" error #322

pinkerltm opened this issue Jun 12, 2020 · 4 comments
Assignees
@c0c0n3
Labels
question

Comments

@pinkerltm
Copy link

Our orion-context broker gets Weather and Weather Forecast data following more or less the smart-data-model specification (its in a state between NGSIv2 and NGSI-LD).

Our WeatherObserved Entities look like this, for example

[{
	"id": "urn:ngsi-ld:WeatherObserved:AT:851e064ffffffff",
	"type": "WeatherObserved",
	"atmosphericPressure": {
		"type": "Number",
		"value": 1008,
		"metadata": {}
	},
	"dataProvider": {
		"type": "Text",
		"value": "https://openweathermap.org/api",
		"metadata": {}
	},
	"dateObserved": {
		"type": "DateTime",
		"value": "2020-06-12T12:26:01.00Z",
		"metadata": {}
	},
	"location": {
		"type": "GeoProperty",
		"value": {
			"coordinates": [
				[
					[16.172720169, 48.358809607],
					[16.175378292, 48.273274371],
					[16.294542264, 48.234683438],
					[16.411233965, 48.281589408],
					[16.408841665, 48.367146676],
					[16.289491704, 48.405776114],
					[16.172720169, 48.358809607]
				]
			],
			"type": "Polygon"
		},
		"metadata": {}
	},
	"location_index": {
		"type": "Text",
		"value": "851e064ffffffff",
		"metadata": {}
	},
	"precipitation": {
		"type": "Number",
		"value": 0,
		"metadata": {
			"volume_timespan": {
				"type": "String",
				"value": "n/a"
			}
		}
	},
	"relativeHumidity": {
		"type": "Number",
		"value": 51,
		"metadata": {}
	},
	"source": {
		"type": "Text",
		"value": "openweathermap/weather",
		"metadata": {}
	},
	"stationName": {
		"type": "Text",
		"value": "Klosterneuburg",
		"metadata": {}
	},
	"temperature": {
		"type": "Number",
		"value": 26.08,
		"metadata": {}
	},
	"windDirection": {
		"type": "Number",
		"value": 20,
		"metadata": {}
	},
	"windSpeed": {
		"type": "Number",
		"value": 4.1,
		"metadata": {}
	}
}, {
	"id": "urn:ngsi-ld:WeatherObserved:AT:851e1533fffffff",
	"type": "WeatherObserved",
	"atmosphericPressure": {
		"type": "Number",
		"value": 1009,
		"metadata": {}
	},
	"dataProvider": {
		"type": "Text",
		"value": "https://openweathermap.org/api",
		"metadata": {}
	},
	"dateObserved": {
		"type": "DateTime",
		"value": "2020-06-12T12:26:01.00Z",
		"metadata": {}
	},
	"location": {
		"type": "GeoProperty",
		"value": {
			"coordinates": [
				[
					[16.188579312, 47.844202604],
					[16.191201738, 47.758110779],
					[16.309557945, 47.719174592],
					[16.42547494, 47.766292387],
					[16.423114785, 47.852406597],
					[16.304575231, 47.891380796],
					[16.188579312, 47.844202604]
				]
			],
			"type": "Polygon"
		},
		"metadata": {}
	},
	"location_index": {
		"type": "Text",
		"value": "851e1533fffffff",
		"metadata": {}
	},
	"precipitation": {
		"type": "Number",
		"value": 0,
		"metadata": {
			"volume_timespan": {
				"type": "String",
				"value": "n/a"
			}
		}
	},
	"relativeHumidity": {
		"type": "Number",
		"value": 53,
		"metadata": {}
	},
	"source": {
		"type": "Text",
		"value": "openweathermap/weather",
		"metadata": {}
	},
	"stationName": {
		"type": "Text",
		"value": "Lichtenwörth",
		"metadata": {}
	},
	"temperature": {
		"type": "Number",
		"value": 26.66,
		"metadata": {}
	},
	"windDirection": {
		"type": "Number",
		"value": 30,
		"metadata": {}
	},
	"windSpeed": {
		"type": "Number",
		"value": 3.6,
		"metadata": {}
	}
}, {
	"id": "urn:ngsi-ld:WeatherObserved:AT:851e15a3fffffff",
	"type": "WeatherObserved",
	"atmosphericPressure": {
		"type": "Number",
		"value": 1007,
		"metadata": {}
	},
	"dataProvider": {
		"type": "Text",
		"value": "https://openweathermap.org/api",
		"metadata": {}
	},
	"dateObserved": {
		"type": "DateTime",
		"value": "2020-06-12T12:26:02.00Z",
		"metadata": {}
	},
	"location": {
		"type": "GeoProperty",
		"value": {
			"coordinates": [
				[
					[16.180676559, 48.101924082],
					[16.183316742, 48.016109493],
					[16.302075461, 47.977345534],
					[16.418378521, 48.024358082],
					[16.416002375, 48.110194879],
					[16.297058996, 48.14899709],
					[16.180676559, 48.101924082]
				]
			],
			"type": "Polygon"
		},
		"metadata": {}
	},
	"location_index": {
		"type": "Text",
		"value": "851e15a3fffffff",
		"metadata": {}
	},
	"precipitation": {
		"type": "Number",
		"value": 0,
		"metadata": {
			"volume_timespan": {
				"type": "String",
				"value": "n/a"
			}
		}
	},
	"relativeHumidity": {
		"type": "Number",
		"value": 52,
		"metadata": {}
	},
	"source": {
		"type": "Text",
		"value": "openweathermap/weather",
		"metadata": {}
	},
	"stationName": {
		"type": "Text",
		"value": "Neu-Guntramsdorf",
		"metadata": {}
	},
	"temperature": {
		"type": "Number",
		"value": 26.49,
		"metadata": {}
	},
	"windDirection": {
		"type": "Number",
		"value": 25,
		"metadata": {}
	},
	"windSpeed": {
		"type": "Number",
		"value": 3.13,
		"metadata": {}
	}
}, {
	"id": "urn:ngsi-ld:WeatherObserved:AT:851e3303fffffff",
	"type": "WeatherObserved",
	"atmosphericPressure": {
		"type": "Number",
		"value": 1010,
		"metadata": {}
	},
	"dataProvider": {
		"type": "Text",
		"value": "https://openweathermap.org/api",
		"metadata": {}
	},
	"dateObserved": {
		"type": "DateTime",
		"value": "2020-06-12T12:26:03.00Z",
		"metadata": {}
	},
	"location": {
		"type": "GeoProperty",
		"value": {
			"coordinates": [
				[
					[14.85921182, 48.68756989],
					[14.86336362, 48.602593357],
					[14.983266675, 48.565646044],
					[15.099217245, 48.613640499],
					[15.095330637, 48.698643747],
					[14.975228091, 48.735625993],
					[14.85921182, 48.68756989]
				]
			],
			"type": "Polygon"
		},
		"metadata": {}
	},
	"location_index": {
		"type": "Text",
		"value": "851e3303fffffff",
		"metadata": {}
	},
	"precipitation": {
		"type": "Number",
		"value": 0,
		"metadata": {
			"volume_timespan": {
				"type": "String",
				"value": "n/a"
			}
		}
	},
	"relativeHumidity": {
		"type": "Number",
		"value": 50,
		"metadata": {}
	},
	"source": {
		"type": "Text",
		"value": "openweathermap/weather",
		"metadata": {}
	},
	"stationName": {
		"type": "Text",
		"value": "Schweiggers",
		"metadata": {}
	},
	"temperature": {
		"type": "Number",
		"value": 24.51,
		"metadata": {}
	},
	"windDirection": {
		"type": "Number",
		"value": 90,
		"metadata": {}
	},
	"windSpeed": {
		"type": "Number",
		"value": 3.58,
		"metadata": {}
	}
}]

Subscribing QuantumLeap to this created the tables etweatherobserved and etweatherforecast but does not append rows. The QuanumLeap logs say

INFO:werkzeug:10.240.0.23 - - [12/Jun/2020 12:32:56] "POST /v2/notify HTTP/1.1" 500 -
ERROR:app:Exception on /v2/notify [POST]
Traceback (most recent call last):
  File "/usr/local/lib/python3.6/site-packages/flask/app.py", line 2446, in wsgi_app
    response = self.full_dispatch_request()
  File "/usr/local/lib/python3.6/site-packages/flask/app.py", line 1951, in full_dispatch_request
    rv = self.handle_user_exception(e)
  File "/usr/local/lib/python3.6/site-packages/flask/app.py", line 1820, in handle_user_exception
    reraise(exc_type, exc_value, tb)
  File "/usr/local/lib/python3.6/site-packages/flask/_compat.py", line 39, in reraise
    raise value
  File "/usr/local/lib/python3.6/site-packages/flask/app.py", line 1949, in full_dispatch_request
    rv = self.dispatch_request()
  File "/usr/local/lib/python3.6/site-packages/flask/app.py", line 1935, in dispatch_request
    return self.view_functions[rule.endpoint](**req.view_args)
  File "/usr/local/lib/python3.6/site-packages/connexion/decorators/decorator.py", line 48, in wrapper
    response = function(request)
  File "/usr/local/lib/python3.6/site-packages/connexion/decorators/uri_parsing.py", line 143, in wrapper
    response = function(request)
  File "/usr/local/lib/python3.6/site-packages/connexion/decorators/validation.py", line 172, in wrapper
    response = function(request)
  File "/usr/local/lib/python3.6/site-packages/connexion/decorators/validation.py", line 347, in wrapper
    return function(request)
  File "/usr/local/lib/python3.6/site-packages/connexion/decorators/parameter.py", line 126, in wrapper
    return function(**kwargs)
  File "/src/ngsi-timeseries-api/src/reporter/reporter.py", line 189, in notify
    trans.insert(payload, fiware_s, fiware_sp)
  File "/src/ngsi-timeseries-api/src/translators/crate.py", line 189, in insert
    fiware_servicepath)
  File "/src/ngsi-timeseries-api/src/translators/crate.py", line 297, in _insert_entities_of_type
    self.cursor.executemany(stmt, entries)
  File "/usr/local/lib/python3.6/site-packages/crate/client/cursor.py", line 67, in executemany
    self.execute(sql, bulk_parameters=seq_of_parameters)
  File "/usr/local/lib/python3.6/site-packages/crate/client/cursor.py", line 54, in execute
    bulk_parameters)
  File "/usr/local/lib/python3.6/site-packages/crate/client/http.py", line 328, in sql
    content = self._json_request('POST', self.path, data=data)
  File "/usr/local/lib/python3.6/site-packages/crate/client/http.py", line 448, in _json_request
    _raise_for_status(response)
  File "/usr/local/lib/python3.6/site-packages/crate/client/http.py", line 187, in _raise_for_status
    error_trace=error_trace)
crate.client.exceptions.ProgrammingError: SQLActionException[ElasticsearchParseException: nested arrays are not supported]

I can't figure out on which part of the JSON this message is refering to, as we also utilize the Device smart-data-model where I really use a List/Array as Value which works perfectly like a charm.
@chicco785
Copy link
Contributor

chicco785 commented Jun 12, 2020 •
edited
Loading 

"type": "GeoProperty",
		"value": {
			"coordinates": [
				[
					[16.180676559, 48.101924082],
					[16.183316742, 48.016109493],
					[16.302075461, 47.977345534],
					[16.418378521, 48.024358082],
					[16.416002375, 48.110194879],
					[16.297058996, 48.14899709],
					[16.180676559, 48.101924082]
				]
			],
			"type": "Polygon"
		},
		"metadata": {}

this is not recognised as geo:json.

rename GeoProperty to geo:json and it should work.

consider that ql supports ngsi-v2, but no ngsi-ld, so, when the message arrives, the value:

				[
					[16.180676559, 48.101924082],
					[16.183316742, 48.016109493],
					[16.302075461, 47.977345534],
					[16.418378521, 48.024358082],
					[16.416002375, 48.110194879],
					[16.297058996, 48.14899709],
					[16.180676559, 48.101924082]
				]
			],

is processed as an array (not as geo:json). but being a nested array, which is not supported by CrateDB, things go badly

this is a guess though :) i may be wrong.

@pinkerltm
Copy link
Author

Thanks to pointing out that detail, I took the NGSI-LD Sample and adopted it to more fit NGSIv2 but missed the "GeoProperty"

You are perfectly right, reformated my Context and recreated the tables, now it works like a charm again.

Thank you very much!

@chicco785
Copy link
Contributor

Thanks to pointing out that detail, I took the NGSI-LD Sample and adopted it to more fit NGSIv2 but missed the "GeoProperty"

You are perfectly right, reformated my Context and recreated the tables, now it works like a charm again.

Thank you very much!

happy to have helped :)

@c0c0n3 c0c0n3 mentioned this issue Jul 29, 2020
Closed
@c0c0n3
Copy link
Member

c0c0n3 commented Jul 29, 2020

For the sake of completeness, this issue is related to #343. In fact, the location attribute in the original example has a type of GeoProperty which eventually causes QL to store it in an object column, but the insert fails because Crate doesn't support nested arrays in a JSON document:

"coordinates": [ 
                 [
                   [14.85921182, 48.68756989],
                 ...

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Assignees

@c0c0n3 c0c0n3

Labels
question
Projects
None yet
Milestone
No milestone
Development

No branches or pull requests
3 participants
@chicco785 @pinkerltm @c0c0n3