To homepage To homepage
Solved

Help with REST API based on table

  • 23 October 2023
  • 10 replies
  • 69 views
D
Rank

Hi everyone,

 

I’ve been searching but haven’t been able to find in previous threads.

 

For an API i'm trying to extract to TX, i'm running into some issues.

 

The API extracts some geographical building data, based on an ID. I have these ID's from a seperate source already in the warehouse.

What I want to do is get the ID from the ODX-table and for each ID run a call to the API. I'm new to this scripting language and maybe im overlooking it in existing documentation but for me it seems impossible.

 

Any idea's and suggestions would be welcome, thanks!

icon

Best answer by Thomas Lind 24 October 2023, 13:49

View original

10 replies

Thomas Lind
Rank
Userlevel 6
Badge +5

HI @dpjat 

How do you get hold of the IDs?

If it is not available as a field in the API it will be difficult to get it added as a dynamic value in the calls.

D
Rank

I can query the ID in my call. I just need the list of ID's to come from my ODX server.

The database probably contains over a million records. I have a subset of around 11k in datawarehouse which I need to enrich through that API.

 

Is that what you meant?

Thomas Lind
Rank
Userlevel 6
Badge +5

You want geographical data. To get this you need to apply an id, this id can not come from a different data source if you want it to be available for a RSD file. You can set it manually but that is not very dynamic.

D
Rank

So basically, I am unable to query an API based on already existing data from my ODX?

 

Is there any alternative or do I need to create a solution outside of TX?

Thomas Lind
Rank
Userlevel 6
Badge +5

I don’t see how you could filter data either existing in another data source, or only in the ODX Store for any data source, both tables needs to exist in the source for it to be able to filter based on the other.

How do you get the ID though? It seems odd that the ID and the geographical info is not related at all.

D
Rank

I would expect to be able to loop through a list of ID's and add that as input. I guess this is just not possible yet in TX.

Thomas Lind
Rank
Userlevel 6
Badge +5

How do you get the ID though? It seems odd that the ID and the geographical info is not related at all.

I mean I don’t really understand how this works, why are these two things not related.

 

D
Rank

In the original database there is information about buildings in the netherlands. This customer has a subset of data relevant to their location. (in example, I checked the cityID for one of those buildings in customer data and this shows 300k records, while their subset contains 12k of their region).

So yes, they are related. However. if I were to search for all the cities in this region I would get a million records and overload the API. Thats why I only want to get data for the buildings in my customers data. (Which are only 12k) 

Thomas Lind
Rank
Userlevel 6
Badge +5

Here is what you can do. It is a bit back and forth I guess, but I can’t see any other ways currently.

You can mix JSON and CSV in a REST provider. So if you create the list of IDs in a CSV export and uses this as the source of a RSD file, you will then have it as a list in the normal REST data source. This can then be used as a normal table with a

SELECT * FROM REST.GEOLOCATION WHERE ID in (SELECT ID FROM REST.CSVFile)

It can be a bit complicated mixing csv and json rsd files, but it is possible, so if you get stuck I can give a few tips about setting it up.

Christian Hauggaard
Rank
Userlevel 6
Badge +5

@dpjat where you able to resolve the issue? Please let us know if you have follow up questions

Reply


Terms and ConditionsCookie settings