You can translate the question and the replies:

Base View schema doesn't get updated automatically if the payload changes

Hello, With the JSON data source, if I have a base view created initially; which is created by Denodo based on the JSON response payload it receives at the time of view creation, the schema of the created view is decided at this point. Now in future, if the payload changes, the view won't be able to fetch that information from the changed payload. Since, its very often in any system that some part of the payload can be generated dynamically. For example - A map represented in a JSON payload; Keys and values both can change based on the instance information. But current JSON data source considers the keys in such a map as schema information and if it changes, it won't be able to map to columns in view. { "fixed-key1" : "value1" "fixed-key2" : { "variable-key1" : { "fixed-key1.1" : "value1.1", "fixed-key1.2" : "value1.2" }, "variable-key2" : { "fixed-key2.1" : "value2.1", "fixed-key2.2" : "value2.2" } } } In this payload, if "variable-key2" is changed to "variable-key3" in next response, because merely the input value to endpoint was different. Is it possible to avoid this issue by any means in Denodo setup? If yes, how that can be done?
user
17-02-2015 08:02:33 -0500

4 Answers

Denodo needs a fixed JSON schema in order to generate a base view. However, it does allow parts of the schema to be optional. In your scenario, if the payloads are such that the variable elements can be treated as optional ones, then at design time you can create a base view using an example JSON document that includes all the optional elements, then at run-time any elements that are not present in a JSON response will be left as NULL values in Denodo. When creating the base view using the JSON connector, you can specify a Data Route that can be a local JSON file, which has been edited to include all the optional elements. This local example JSON file would then be introspected by Denodo to create the base view's structure. Once the JSON base view is created, the Data Route can be changed to point to the 'live' JSON end-point e.g. an HTTP URL. In your example if you defined the JSON base view using a simple JSON document as follows in which the variable-key1, 2 and 3 structures are all optional: { "fixed-key1" : "value1" "fixed-key2" : { "variable-key1" : { "fixed-key1.1" : "value1.1", "fixed-key1.2" : "value1.2" }, "variable-key2" : { "fixed-key2.1" : "value2.1", "fixed-key2.2" : "value2.2" } "variable-key3" : { "fixed-key3.1" : "value3.1", "fixed-key3.2" : "value3.2" } } } At run-time, if the JSON response does not include variable-key3 for example, then these elements would just be represented as null values in Denodo. If however, the payload is completely dynamic depending on the request, and returns payloads that represent completely different entities with different structures that can't be represented using optional elements as described above, then Denodo wouldn't be able to be handle this in a single view. In this case I would create a base view for each specific entity.
Denodo Team
19-02-2015 09:26:13 -0500
Does Denodo support dynamic field types by now? We integrate data from a REST endpoint (from an external data source) as JSON data source. Where returned data contains timeseries datapoints which (depending on the asset/aspect queried) have different schemas/field types -> ![](https://i.imgur.com/qTAHEub.png) However, since Denodo already creates a custom JSON wrapper which predefines the respective ARRAY/REGISTER type when creating the base view with certain values for interpolated ones, it fails to properly match different fields when the view later on is queried using different interpolation values.. It manages to properly fetch all results, however the output only contains “_time” values as that’s the only field name that occurs in all cases. Questions: 1. How can one set up a JSON data source that has only a partially fixed schema? 2. Is there a way to somehow dynamically “inject”/join the relevant fields on query time? Neither converting the field to text nor writing our own json wrapper worked for us.. /:
user
01-06-2022 06:08:54 -0400
For some reason the link to the image didn't work properly -> https://i.imgur.com/qTAHEub.png
user
01-06-2022 06:09:38 -0400
Hi, We believe that you are facing is that the API returns different schemas based on the asset id that is being requested. You were looking either for a way to dynamically create the json schema (i.e. not to have a fixed schema) or - alternatively - to just treat the output as an text and not interpreting it as json at all. We would like to suggest the following approach: * Create a delimited file data source with HTTP Route and configure the call to the REST API . * Now, for the configuration of the delimited file, choose: * Column delimiter: A non-existent value such as "12394xaksdjf78" in order to retreat the final result just as one single column (the jsonarray). * Check the box Delimiter consists of multiple characters * For the End of line delimiter also choose a non-existent delimiter such as "\t124x3745uxaj" This approach causes that the whole json structure is being read as one single column and value, as you desired. For more details, refer to the [JSON Sources](https://community.denodo.com/docs/html/browse/8.0/en/vdp/administration/creating_data_sources_and_base_views/json_sources/json_sources#json-sources) section of the Virtual DataPort Administration Guide. Hope this helps!
Denodo Team
23-06-2022 10:09:14 -0400
You must sign in to add an answer. If you do not have an account, you can register here