Integrate TiDB Cloud with Zapier
Zapier is a no-code automation tool that lets you easily create workflows that involve thousands of apps and services.
Using the TiDB Cloud app on Zapier enables you to:
- Use TiDB, a MySQL-compatible HTAP database, for free. No need to build locally.
- Make it easier to manage your TiDB Cloud.
- Connect TiDB Cloud to 5000+ apps and automate your workflows.
This guide gives a high-level introduction to the TiDB Cloud app on Zapier and an example of how to use it.
Quick start with template
Zap Templates are ready made integrations or Zaps with the apps and core fields pre-selected, for publicly available Zapier integrations.
In this section, we will use the Add new Github global events to TiDB rows template as an example to create a workflow. In this workflow, every time a new global event (any GitHub event happens from or to you, on any repo) is created from your GitHub account, Zapier adds a new row to your TiDB Cloud cluster.
Before you start, you need:
- A Zapier account.
- A GitHub account.
- A TiDB Cloud account and a Serverless Tier cluster on TiDB Cloud. For more details, see TiDB Cloud Quick Start.
Step 1: Get the template
Go to TiDB Cloud App on Zapier. Choose the Add new Github global events to TiDB rows template and click Try it. Then you will enter the editor page.
Step 2: Set up the trigger
In the editor page, you can see the trigger and action. Click the trigger to set it up.
Choose app & event
The template has set the app and the event by default, so you don't need to do anything here. Click Continue.
Choose a GitHub account that you want to connect with TiDB Cloud. You can either connect a new account or select an existing account. After you set up, click Continue.
Set up trigger
The template has set the trigger by default. Click Continue.
Click Test trigger. If the trigger is successfully set up, you can see the data of a new global event from the GitHub account. Click Continue.
Step 3: Set up the
Find Table in TiDB Cloud action
Choose app & event
Keep the default value
Find Tableset by the template. Click Continue.
- Click the Sign in button, and you will be redirected to a new login page.
- On the login page, fill in your public key and private key. To get the TiDB Cloud API key, follow the instructions in TiDB Cloud API documentation.
- Click Continue.
Set up action
In this step, you need to specify a table in your TiDB Cloud cluster to store the event data. If you do not already have a table, you can create one through this step.
From the drop-down list, choose the project name and cluster name. The connection information of your cluster will be displayed automatically.
Enter your password.
From the drop-down list, choose the database.
Zapier queries the databases from TiDB Cloud using the password you entered. If no database is found in your cluster, re-enter your password and refresh the page.
In The table you want to search box, fill in
github_global_event. If the table does not exist, the template uses the following DDL to create the table. Click Continue.
Click Test action, and Zapier will create the table. You can also skip the test, and the table will be created when this workflow is running for the first time.
Step 4: Set up the
Create Row in TiDB Cloud action
Choose app & event
Keep the default value set by the template. Click Continue.
Select the account you have chosen when you set up the
Find Table in TiDB Cloudaction. Click Continue.
Set up action
Fill in the Project Name, Cluster Name, TiDB Password, and Database Name as in the previous step.
In the Table Name, choose the github_global_event table from the drop-down list. The columns of the table are displayed.
In the Columns box, choose the corresponding data from the trigger. Fill in all the columns, and click Continue.
Click Test action to create a new row in the table. If you check your TiDB Cloud cluster, you can find the data is written successfully.
mysql> SELECT * FROM test.github_global_event; +-------------+-------------+------------+-----------------+----------------------------------------------+--------+---------------------+ | id | type | actor | repo_name | repo_url | public | created_at | +-------------+-------------+------------+-----------------+----------------------------------------------+--------+---------------------+ | 25324462424 | CreateEvent | shiyuhang0 | shiyuhang0/docs | https://api.github.com/repos/shiyuhang0/docs | True | 2022-11-18 08:03:14 | +-------------+-------------+------------+-----------------+----------------------------------------------+--------+---------------------+ 1 row in set (0.17 sec)
Step 5: Publish your zap
Click Publish to publish your zap. You can see the zap is running in the home page.
Now, this zap will automatically record all the global events from your GitHub account into TiDB Cloud.
Triggers & Actions
Triggers and actions are the key concepts in Zapier. By combining different triggers and actions, you can create various automation workflows.
This section introduces the triggers and actions provided by TiDB Cloud App on Zapier.
The following table lists the triggers supported by TiDB Cloud App.
|New Cluster||Triggers when a new cluster is created.|
|New Table||Triggers when a new table is created.|
|New Row||Triggers when new rows are created. Only fetches the recent 10000 new rows.|
|New Row (Custom Query)||Triggers when new rows are returned from a custom query that you provide.|
The following table lists the actions supported by TiDB Cloud App. Note that some actions need extra resources, and you need to prepare the corresponding resources before using the action.
|Find Cluster||Finds an existing Serverless tier or Dedicated tier.||None|
|Create Cluster||Creates a new cluster. Only supports creating a free Serverless Tier cluster.||None|
|Find Database||Finds an existing database.||A Serverless Tier cluster|
|Create Database||Creates a new database.||A Serverless Tier cluster|
|Find Table||Finds an existing Table.||A Serverless Tier cluster and a database|
|Create Table||Creates a new table.||A Serverless Tier cluster and a database|
|Create Row||Creates a new row.||A Serverless Tier cluster, a database, and a table|
|Update Row||Updates an existing row.||A Serverless Tier cluster, a database, and a table|
|Find Row||Finds a row in a table via a lookup column.||A Serverless Tier cluster, a database, and a table|
|Find Row (Custom Query)||Finds a row in a table via a custom query the you provide.||A Serverless Tier cluster, a database, and a table|
TiDB Cloud App templates
TiDB Cloud provides some templates for you to use in Zapier directly. You can find all the templates in the TiDB Cloud App page.
Here are some examples:
- Duplicate new TiDB Cloud rows in Google Sheets.
- Send emails via Gmail from new custom TiDB queries.
- Add rows to TiDB Cloud from newly caught webhooks.
- Store new Salesforce contacts on TiDB rows.
- Create TiDB rows for new Gmail emails with resumes and send direct Slack notifications
How can I set up the TiDB Cloud account in Zapier?
Zapier requires your TiDB Cloud API key to connect with your TiDB Cloud account. Zapier does not need your login account for TiDB Cloud.
To get your TiDB Cloud API key, follow the TiDB Cloud API documentation.
How do TiDB Cloud triggers perform de-duplication?
Zapier triggers can work with a polling API call to check for new data periodically (the interval depends on your Zapier plan).
TiDB Cloud triggers provide a polling API call that returns a lot of results. However, most of the results have been seen by Zapier before, that is, most of the results are duplication.
Since we don’t want to trigger an action multiple times when an item in your API exists in multiple distinct polls, TiDB Cloud triggers deduplicate the data with the
New Cluster and
New Table triggers simply use the
table_id as the
id field to do the deduplication. You do not need to do anything for the two triggers.
New Row Trigger
New Row trigger limits 10,000 results in every fetch. Therefore, if some new rows are not included in the 10,000 results, they cannot trigger Zapier.
One way to avoid this is to specify the
Order By configuration in the trigger. For example, once you sort the rows by their creation time, the new rows will always be included in the 10,000 results.
New Row trigger also uses a flexible strategy to generate the
id field to do the deduplication. The trigger generates the
id field in the following order:
- If the result contains an
idcolumn, use the
- If you specify a
Dedupe Keyin the trigger configuration, use the
- If the table has a primary key, use the primary key. If there are multiple primary keys, use the first column.
- If the table has a unique key, use the unique key.
- Use the first column of the table.
New Row (Custom Query) Trigger
New Row (Custom Query) trigger limits 1,000,000 results in every fetch. 1,000,000 is a large number, and it is only set so as to protect the whole system. It is recommended that your query includes
ORDER BY and
To perform deduplication, your query results must have a unique id field. Otherwise, you will get the
You must return the results with id field error.
Make sure that your custom query executes in less than 30 seconds. Otherwise, you will get the timeout error.
How do I use the
find or create action?
Find or create action enables you to create a resource if it does not exist. Here is an example:
set up actionstep, tick the
Create TiDB Cloud Table if it doesn’t exist yet?box to enable
find and create.
This workflow creates a table if it does not exist yet. Note that the table will be created directly if you test your action.