I have two ODATA tables with different schemas, but a shared attribute (column) value.
I want to do an Inner Join.
Country lookup table:ghoapi.azureedge.net/api/DIMENSION/COUNTRY/DimensionValues
{
"traceId": "56712574a179e5e6dd090e71fbc3a119",
"request": {
"url": "api/ds/query?ds_type=dvelop-odata-datasource&requestId=explore_fcj",
"method": "POST",
"data": {
"queries": [
{
"datasource": {
"type": "dvelop-odata-datasource",
"uid": "bdyajhcljb75sb"
},
"entitySet": {
"entityType": "Default.WHOSIS_000001",
"name": "WHOSIS_000001"
},
"filterConditions": [],
"key": "Q-332e5c82-7a56-4822-90bb-1928a35b18b9-0",
"properties": [
{
"name": "SpatialDim",
"type": "Edm.String"
},
{
"name": "Dim1",
"type": "Edm.String"
},
{
"name": "ParentLocation",
"type": "Edm.String"
},
{
"name": "TimeDim",
"type": "Edm.Int32"
},
{
"name": "NumericValue",
"type": "Edm.Decimal"
},
{
"name": "Low",
"type": "Edm.Decimal"
},
{
"name": "High",
"type": "Edm.Decimal"
}
],
"refId": "A",
"datasourceId": 14,
"intervalMs": 2000,
"maxDataPoints": 1472
}
],
"from": "1726685265533",
"to": "1726688865533"
},
"hideFromInspector": false
"response": {
"results": {
},
}
yosiasz
September 19, 2024, 3:21pm
2
which one is the shared attribute in the data you provided
WHO uses a Three letter country code.
Table 1: SpatialDim
1 Like
yosiasz
September 19, 2024, 4:34pm
4
are you fetching both data points (oData) using a rest api endpoint? and which plugin are you using to fetch these rest api endpoints? infinity?
I am using the ODATA plugin for the first one, and if I knew how, I would use it for second, but right now I use Infinity REST for the second.
1 Like
yosiasz
September 19, 2024, 5:12pm
6
seems like grafana requires the columns to join on to be the same name
so for the second query you are using infinity on, use uql and then rename the code data point to be SpatialDim ie
parse-json
| scope "countries"
| extend "SpatialDim"="code"
| project-away "code"
then join on that using Join transformation
I have 40+ years of DB work. First time I saw that as a restriction. But as you can see I am just learning GRAFANA, can you point me to where I apply UDL to a query?
yosiasz
September 19, 2024, 9:20pm
8
restriction on grafana side not on db side.
so is your data source a database or a rest api that returns json data?
It is a REST API, See the code for the requests in the top post.
I only mentioned RDBD JOIN since relational DB work is typically where done does INNER/OUTER/LEFT/RIGHT/etc JOIN work.
Any examples of where in GUI and documentation on the UDL language? it is not in the UDEMY course I am taking right now.
yosiasz
September 19, 2024, 11:15pm
10
That was a big help, but it does appear that the UQL needs some tweaking. It is a public URL, if you wan try it. But here is how I setup Infinity with Backend UQL parsing, and the result is blank. Maybe do the root being warped in “value”?
1 Like
I am truncating the raw results, just the top two array values.
{
"@odata.context": "https://ghoapi.azureedge.net/api/$metadata#Collection(Default.DIMENSION_VALUE)",
"value": [
{
"Code": "ABW",
"Title": "Aruba",
"Dimension": "COUNTRY",
"ParentDimension": "REGION",
"ParentCode": "AMR",
"ParentTitle": "Americas"
},
{
"Code": "AFG",
"Title": "Afghanistan",
"Dimension": "COUNTRY",
"ParentDimension": "REGION",
"ParentCode": "EMR",
"ParentTitle": "Eastern Mediterranean"
}
]
}
yosiasz
September 20, 2024, 6:44pm
13
don’t just copy pasta
parse-json
| scope "countries"
was just something for test purposes on my end. try it out with uql without the |scope "countries and work your way up.
which gives you this data
{
"@odata.context": "https://ghoapi.azureedge.net/api/$metadata#Collection(Default.DIMENSION_VALUE)",
"value": [
{
"Code": "ABW",
"Dimension": "COUNTRY",
"ParentCode": "AMR",
"ParentDimension": "REGION",
"ParentTitle": "Americas",
"Title": "Aruba"
},
{
"Code": "AFG",
"Dimension": "COUNTRY",
"ParentCode": "EMR",
"ParentDimension": "REGION",
"ParentTitle": "Eastern Mediterranean",
"Title": "Afghanistan"
},
{
"Code": "AGO",
"Dimension": "COUNTRY",
"ParentCode": "AFR",
"ParentDimension": "REGION",
"ParentTitle": "Africa",
"Title": "Angola"
}
so keep working on extracting the data using UQL, looks like value is the root node
Thanks for the tweak on the nose about “copy pasta”. It was well deserved.
So, as a stand alone data fetch, the UQL works fine.
But when I do two queries (the top one the ODATA, and the second one the excact copy of the Infinity with UQL backend, I get a UQL error).
yosiasz
September 22, 2024, 8:22pm
16
is it “Code” or “code” in the extend of UQL
it is “Code”, Looks like I did only a partial update to the UQL. This is what I needed:
parse-json
| scope "value"
| extend "SpatialDim"="Code"
| project "SpatialDim","Title"
It works fine now. Thank you for your patience. It went above the call of duty.
1 Like
Hi @yosiasz - Thank you for the contributions. Consider using the official documentation Infinity data source plugin for Grafana | Grafana Plugins documentation
1 Like
