A lot of companies are using Airtable as their databases, but are struggling to find a way to sync their Airtable data to their SQL.
You might be trying to move Airtable data to have a backup on your own database or gather your data in a data warehouse.
There are a few ways of doing this. First, you could ask your IT or development team to implement such data pipelines using Airtable’s API (but frankly, I like to be autonomous and not rely on the tech team. We would rather have them focus on the customer-facing product than writing scripts for internal processes).
Another option is using Zapier. It doesn’t take too long to set up the codeless integration between Airtable and your SQL database, but it does require a lot of maintenance. You’ll need to do different “zaps” for creating new fields as well as updating fields when your data in Airtable changes. It also takes some time to match each corresponding field in Airtable with your SQL database.
Or you could use Actiondesk. Actiondesk connects Airtable and your SQL database without any code. After importing your Airtable data into Actiondesk’s spreadsheet interface, you can “dump” all your data into your SQL database with a simple click. There’s no need to tediously match the fields. Voila. It’s super simple.
You can also use the powerful data transformations features of Actiondesk to create a real relational database structure from your Airtable data (see more below)
I’m going to walk you through the basics of how to use Actiondesk to sync data between Airtable and your SQL database. I’ll also go over what unnesting is and how to do it in a couple of minutes on Actiondesk.
Open Actiondesk and create a new Workflow. Select Airtable as your integration, plug in your API key to set up the integration (don’t know where to find it? No worries; it’s very easy! Here’s how), and choose which table you want to export to your SQL database.
Now choose which fields you want to import (or press “Select All”). Click on Confirm and then “Import Data.” All your selected data is now in Actiondesk.
To push that data to your SQL, click “Program Actions,” and choose your SQL database. (If you don’t see your database, click on the “plus” button to see). Connect your SQL to Actiondesk with a few simple steps (read how to do that here).
Choose “Dump a Whole Table” and select which table in your SQL database you want this data pushed too. You’ll see the name of all your tables from your SQL database in a drop-down menu.
There you have it! Your data is pushed to your SQL database and you can schedule it to automatically as frequently as you would like. This way, if you delete, add or update fields or columns in Airtable, this data is reflected live in your SQL database.
When handling data from Airtable, the Unnest operation can come in handy.
It is equivalent to the Unnest operator in SQL.
If one of your columns is an array (or a string with some commas in it), it will split that column and return as many rows as there were elements in the array (with all other columns being equal). Your columns could be in an array if you have multiple fields in Airtable (say multiple bugs for one feature or multiple items purchased by one customer).
For example, it will turn the table on the left into the table on the right.
The Unnest operation will also work if your variable is text with different values separated by a comma.
Going back to moving Airtable data to your SQL database. Using the unnest function makes it easy to build mapping tables when you have associations in your tables.
That’s not clear? Let’s go over an example.
Let’s say you have two tables in Airtable that are associated. Maybe you have one table with customer information that includes their email, name and what products they have bought in a column called “orders”.
You might have another table that has the Product ID of the products the customers have bought in another table.
As you can see, some customers bought more than one product. You have an n to n relationship (if you’re lost on relationships in databases, check out our introduction to relational databases). When you export that data from Airtable it appears in an array, which is not easily usable in a SQL database. Ideally, you’ll need an additional mapping table, something that would look like this:
Fortunately, you can do that with Actiondesk!
Once you’ve imported your data from Airtable into Actiondesk, press “unnest” in the upper righthand corner and choose which column has the string you want to separate. In this example, I’m unnesting the string of text from “Orders.”
You’ll see at the bottom a new sheet opens up and has the new unnested version of your data, ready to be exported to your SQL database. You can see there are 15 rows even though there are only 10 customers. This is because the customers repeat when they have more than one product in their order.
This unnested table is what enables you to have a relational database structure and to use easily SQL and make joins. Here’s an example of what the schema would look like once you import this into your SQL database.
I hope you found this article useful and saved you time and effort when exporting your data to SQL.
Have any further questions on Actiondesk or unnesting?
Let me know in a comment below!
Read the original article here.