API Data into SQL Database

[paulrockliffe]

Data Analyst by trade, so I don't really get involved in the data procurement process, but I'm working on concept of my own to learn some stuff and need to get involved in all that.

I have data sat in an API and I've used Postman to setup Authentication to the API and I've got the data coming through in JSON. I want to end up with different bits of the various queries to end up as rows in tables in a MS SQL Database. What's the way to do this roughly? Happy to apply my Google skills if I have a vague idea of the right sort of setup I should aim for, but would like to be pointed in the right direction rather than exploring rabbit warrens.

Can I get the SQL Server to go direct to the API and then Query the JSON outputs to build my tables? That seems like what I'd want to do. If that's the best way, a rough guide would be great if anyone can help.

Thanks

[Crafty_]

have a look around for info on stored proc sp_OAMethod, you should be able to call the API with that and get the JSON back. if you then use select * from OPENJSON(..) you should be able to select the elements out of the json and bung them in to a table.

Guess what my task is for today...!

Edited by Crafty_ on Tuesday 6th October 18:40

[paulrockliffe]

Great, that sounds like a good job for this evening. Google had already got me to a Microsoft page on OpenJSON.

[paulrockliffe]

Got another question:

I assumed that the broad process for using API-fed data in an App was to use an API to get the data onto a server, bash it about into a relational model and then the app access that data away from the API.

Digging into existing apps it looks like the app manages the API access for the user's data, then the app is built off that and the app developer's server isn't a thing. Which might explain why my 'gap in the market' is there - there's a big jump required to go from simply leveraging the output of the API and augmenting it with other data to create the relational model that is required for my idea.

I looked into it and can see how I implement an SQL database in app, but does anyone have any experience on the limitations of what can be done on-device vs on server? I'm talking about something in the region of 10,000 rows of data essentially being categories based on values in the API and lookup tables that are user-generated. With the user in control of the hierarchy of categorisation. So it's not the most ridiculous amount of data or processing, but while I know it would be no problem on a proper server, I've no idea what the limitations in Android etc would be.

Thanks!

[plasticpig]

Both Android and IOS have SQLite embedded. Never had a performance issue with either on table sizes far larger than you indicate.

[paulrockliffe]

Thanks that's great, I think I vaguely know what I'm doing now then.