Person.rsd encodes the {BASE} URL string on concatenation

[jeromechoo]

Wasn't able to figure out what syntax we need to remove the double encoding issue with {BASE} when concatenated in this way:

<api:setc attr="BASE" value="https://kg.diffbot.com/kg/v3/dql?token={auth}&query=type%3APerson" />
<api:check attr="_input.city_name">
  <api:setc attr="URI" value="{BASE}%20location.city.name%3A%22{city_name}%22" />
</api:check>

Error in DataGrip looks like this.

[HY000] DATA_SOURCE https%3A%2F%2Fkg.diffbot.com%2Fkg%2Fv3%2Fdql%3Ftoken%3D%7Bauth%7D%26query%3Dtype%253APerson%20location.city.name%3A%22Atlanta%22 (No such file or directory) DATA_SOURCE https%3A%2F%2Fkg.diffbot.com%2Fkg%2Fv3%2Fdql%3Ftoken%3D%7Bauth%7D%26query%3Dtype%253APerson%20location.city.name%3A%22Atlanta%22 (No such file or directory)

You can see how the {BASE} string is getting encoded but we don't want it to. I'm not sure if the encoding is happening when it is initially set or when it is concatenated. There doesn't seem to be a difference between using api:setc or api:set.

[Anu0421]

Found the root causes and fixes. There are actually 3 separate issues found:
Issue 1: {variable} syntax double-encodes
{BASE} uses template substitution that URL-encodes on expansion. So %3A in BASE becomes %253A when substituted into another value. This is the core double-encoding bug.
Fix: Use [variable] (square brackets) instead of {variable}. Square brackets do raw substitution with no encoding.
Issue 2: api:setc is compile-time, not runtime
api:setc evaluates at parse time, so runtime values like {auth} (set via api:set) never resolve , they stay as literal %7Bauth%7D in the URL.
Fix: Use api:set for anything referencing runtime values.
Issue 3: REST Driver vs API Driver operation names
The .rsd files use apisadoExecuteJSONGet which is an API Driver operation. If you're using the REST JDBC Driver, this needs to be jsonproviderGet.

Fixed code:
`
<api:set attr="dqlquery" value="type:Organization" />

<api:check attr="_input.city_name">
<api:set attr="dqlquery" value="[dqlquery]%20locations.city.name:%22[_input.city_name]%22" />
</api:check>

<api:check attr="_input.num_employees">
<api:set attr="dqlquery" value="[dqlquery]%20nbEmployees%3E[_input.num_employees]" />
</api:check>

<api:set attr="URI" value="https://kg.diffbot.com/kg/v3/dql?query=[dqlquery]" />
<api:set attr="XPath" value="/data" />
<api:set attr="DataModel" value="FlattenedDocuments" />
<api:set attr="PageIdColumns" value="True" />

<api:set attr="EnablePaging" value="False" />
<api:script method="GET">
<api:set attr="method" value="GET" />
<api:call op="jsonproviderGet">
<api:push />
</api:call>
</api:script>
`

[Anu0421]

Hi @jeromechoo , I’ve pushed the updates to the organization.rsd file. Please test it and let me know if you have any questions.

[jeromechoo]

Thanks @Anu0421! I was able to get it working for Person.rsd as well!

The .rsd files use apisadoExecuteJSONGet which is an API Driver operation. If you're using the REST JDBC Driver, this needs to be jsonproviderGet.

Would you mind clarifying what the difference is between an API Driver operation and the REST JDBC Driver? When switching to jsonproviderGet I got this message from my DB browser. I'm using the JDBC API driver.

Edit: The error I get when using jsonproviderGet

[Anu0421]

Hi @jeromechoo , happy to clarify.

The error is occurring because the operation being used does not match the driver type.

There are two different drivers involved here:

• CData API Driver – supports operations like apixadoExecuteJSONGet
• CData REST JDBC Driver – supports operations like jsonproviderGet

These operation names are not interchangeable between drivers.

In this case, the connection is using the CData API Driver. Therefore, the correct operation to use in the .rsd file is:

"apixadoExecuteJSONGet"

The error:

[HY000] [rsb:load_operation] The operation 'jsonproviderGet' could not be found.

occurs because "jsonproviderGet" is only available in the REST JDBC Driver, not in the API Driver.

If we intend to use "jsonproviderGet", the connection must be switched to the REST JDBC Driver (jdbc:rest:). Otherwise, we should retain "apixadoExecuteJSONGet" when using the API Driver (jdbc:api:).

Let me know if you have any further questions.

[jeromechoo]

Thanks @Anu0421! I guess I'm not quite sure what the difference is between CData API Driver and CData REST JDBC Driver. This documentation appears to refer to both as the same thing?

Couple other questions:

1. In https://github.com/jerodj-cdata/diffbot-api-profile/blob/master/Person.rsd#L75, how do I encode spaces in input values? Using "United States" as input here returns an "Illegal character in query" error. When I attempt to use curly brackets to insert the value it errors as well.

2. @jerodj-cdata was mentioning a way to generate tables off JSON arrays that can be referenced by ID. Any chance you have some example syntax I could use to do that in attributes like https://github.com/jerodj-cdata/diffbot-api-profile/blob/master/Person.rsd#L24?

3. For cases where generating tables is not necessary (Arrays of strings), what would be the syntax to join and concatenate a string delimited by commas? (e.g. https://github.com/jerodj-cdata/diffbot-api-profile/blob/master/Person.rsd#L32)

[jerodj-cdata]

@jeromechoo The API Driver provides a distributable way to configure our drivers to work with an API. The REST driver would have to be configured on every machine intended to use the driver and has less support for parsing out connection string properties for use in the "table" definitions.

1. You should be able to use the urlencode formatter: [_input.location_country_name|urlencode(true)] to encode the passed data. Read more about the formatters here: https://cdn.cdata.com/help/FAM/jdbc/pg_valueitemformatters.htm
2. A good place to start for more complex profile building is these articles: https://www.cdata.com/kb/articles/apidriver-create.rst & https://www.cdata.com/kb/articles/apidriver-edit.rst
   In brief, you could create an educations.rsd file that parses each element a JSON array as a row by setting the repeatElement to something like: <api:set attr="RepeatElement" value="/json/data/entity/educations/" /> (This assumes educations is an array, of course).
3. For this one, you'd have to do some scripting to iterate over the collection or parse out each piece with array notation. If you need assistance here, I'll defer to @Anu0421

[jeromechoo]

Hi @Anu0421,

Sorry just going to keep borrowing this issue to resolve a bunch of misc questions. Thanks again for the help!

1. When running a query like

   SELECT *
   FROM Organization
   WHERE name='CData'

   The returned data does not include "CData.com". Diffbot's API response actually does return it because all name filters on Diffbot have a hybrid "LIKE" operator applied. When I use the LIKE operator in SQL with the profile, nothing is returned.

   SELECT *
   FROM Organization
   WHERE name LIKE 'CData%'

   What's the best way to handle a scenario like this? Functionally the Diffbot request does not need the LIKE operator, but it sounds like we need to support it in the profile in some way.

2. In general, what would be a way to pass operators from SQL to request params in the driver profile? (e.g. I want to be able to query for capitalization.value > 1000000) It looks like it should be somewhere in the _query item but the closest option is the complete WHERE clause which is not what I need. I need the exact operator applied to the requested input.

3. I'm trying to cast certain datetime attributes (e.g. foundingDate.timestamp) as datetime. foundingDate.timestamp has a precision in milliseconds in the Diffbot KG ontology whereas I believe SQL defaults to seconds. I see that there are math formatters available but I'm unsure how to apply these formatters for inbound data.

[Anu0421]

Hi @jeromechoo, I will update you shortly

[Anu0421]

Hi @jeromechoo,

Here's how each of the three things was resolved:

1. LIKE operator returning nothing

The fix is to add other:filter="name: =,LIKE" on the entity.name attribute so the driver maps both = and LIKE SQL operators to the same name input. The % wildcard is automatically stripped before sending to Diffbot using Replace ('%','') since the name: filter in DQL handles both exact and partial matching natively.

2. Passing operators from SQL to request params

You're right that _query only gives the full WHERE clause, not the individual operator. The correct approach is a two-part solution:

Add other:filter on the to map each SQL operator to a named input:

This tells the driver: when SQL sends >, route the value to input cap_value_gt; when SQL sends <, route to cap_value_lt, and so on.

In the script, check each input and append the correct DQL operator:
<api:check attr="_input.cap_value_gt">
<api:set attr="dqlquery" value="[dqlquery] capitalization.value>[_input.cap_value_gt]" />
</api:check>

So WHERE entity.capitalization.value > 1000000000 in SQL becomes capitalization.value>1000000000 in the Diffbot DQL request.

3. Applying math formatters for inbound datetime data

The other:xPathFormat attribute is the right place to apply math formatters for inbound data. Point other:xPath at the raw millisecond timestamp field, then use Math.Divide(1000) in the format expression to convert it to seconds before the driver interprets it as a datetime:

[value|Math.Divide(1000)] runs on data coming in from the API response, it takes the raw millisecond value and divides by 1000 before datetime conversion. This is the general pattern for any inbound data transformation using math formatters.

All three fixes are in Organization.rsd on the master branch. Please test this and feel free to reach out if you face any issues.

[jeromechoo]

Thanks @Anu0421. #1 worked perfectly.

#2:

I couldn't get this to work.

If we define each input as _input.cap_value_gt, _input.cap_value_lt etc.., what is the profile referencing when the user filters on the WHERE capitalization_value>1000000 column?

I tried putting capitalization_value in the other:queryslicercolumn attribute of api:info and got an unrecognized column error.

A query like WHERE cap_value_gt = 1000000 works when I put cap_value_gt into other:queryslicercolumn. But that makes the setting of the other:filter attribute meaningless.

#3:

Nothing appears to be happening here when I use other:xPathFormat="[value|Math.Divide(1000)]". I tried [value|divide(1000)] as per this doc as well and that didn't work either.

Pushed a new version of Organization.rsd merging the changes I had made earlier with your recommendations

[Anu0421]

HI @jeromechoo,

For #2, the key is that other:filter on the handles the operator mapping automatically, you don't need to add anything extra to other:queryslicercolumn.
The reason you got "unrecognized column" is because you typed capitalization_value (with underscore) in other:queryslicercolumn, but the actual column name is entity.capitalization.value (with dots). Those need to match exactly.
When you write:

WHERE [entity.capitalization.value] > 1000000000

The driver reads the other:filter on the entity.capitalization.value attribute, sees that > maps to cap_value_gt, sets _input.cap_value_gt = 1000000000, and the script builds capitalization.value>1000000000 in the DQL query automatically.

I tested this locally and got 41 results back (Apple, Amazon, Microsoft etc.) with the current RSD, no changes needed. The other:filter approach is already working correctly, just make sure to use the exact column name with dots and wrap it in square brackets in your SQL client:
SELECT [entity.name], [entity.capitalization.value]
FROM Organization
WHERE [entity.capitalization.value] > 1000000000

For #3, the issue is that foundingDate.timestamp math division wasn't needed, the root problem was actually in foundingDate.str. Diffbot returns the date string with a d prefix (e.g. d1998-09-04) which causes the datetime conversion to fail.

The fix was to:

Point other:xPath to foundingDate/str instead of foundingDate/timestamp
Use other:xPathFormat="[value|Substring(1)]" to strip the d prefix

Tested locally, WHERE [entity.name] = 'Google' returns entity.foundingDate.datetime as 1998-09-04 correctly.

Fix is pushed to master. I hope this helped, reach out if you run into any further issues!