We use a number of different sites/services like Zendesk, Discourse, Stripe, Airtable, and others.
I need to work with some historical data and since pagination is an issue, I was thinking it would be better to get all of my data from different APIs into a MySQL database and use that as a data source in Klipfolio.
At the moment I use a bunch of Python scripts to:
- Connect to an API
- Extract data (JSON)
- Convert JSON into Python dictionary
- Iterate over dictionary
- Insert rows into MySQL server
This works, but it can be a pain to map JSON to MySQL. I'm wondering if there is any better way to do this? Are there any tools specifically for this job? I looked for "data warehouses", "data pipelines", and "ETL" but I'm not sure if the services I find (panoply.io, stitchdata.com, etc.) are suitable.
Maybe my Python scripts and MySQL server are the best way to approach this but before I start coding, I want to make sure this is the best option.