Schedule data ingestion with cron and GitHub Actions¶

Cronjobs are a universal way of scheduling tasks. Learn how to keep your data in sync with cronjobs or GitHub Actions and the Tinybird REST API.

Overview¶

For this example, assume you've already imported a Data Source to your Tinybird account and properly defined its schema and partition key. Once everything is set, you can perform operations using the Data Sources API to periodically append to or replace data in your Data Sources. This guide shows you some examples.

About crontab¶

Crontab is a native Unix tool that schedules command execution at a specified time or time interval. It works by defining the schedule, and the command to execute, in a text file. This can be achieved using sudo crontab -e. You can learn more about using crontab using many online resources like crontab.guru and the man page for crontab.

The cron table format¶

Cron follows a table format like the following. You can also use external tools like crontab.guru to help you define the cron job schedule:

*   *   *   *   *  Command_to_execute
|   |   |   |   |       
|   |   |   |   Day of the Week ( 0 - 6 ) ( Sunday = 0 )
|   |   |   |
|   |   |   Month ( 1 - 12 )
|   |   |
|   |   Day of Month ( 1 - 31 )
|   |
|   Hour ( 0 - 23 )
|
Min ( 0 - 59 )

Using this format, the following would be typical cron schedules to execute commands at different times:

• Every five minutes: 0/5 \* \* \* \*
• Every day at midnight: 0 0 \* \* \*
• Every first day of month: \* \* 1 \* \*
• Every Sunday at midnight: 0 0 \* \* 0

Append data periodically¶

It's common to have a Data Source that grows over time. An ETL process often extracts this data from the transactional database and generates CSV files with the last X hours or days of data. You might want to append those recently generated rows to your Tinybird Data Source.

For this example, imagine you generate new CSV files every day at 00:00 that you want to append to Tinybird everyday at 00:10.

Option 1: With a shell script¶

Create a shell script file containing the Tinybird API request operation:

#!/bin/bash
TOKEN=your_token
CSV_URL="http://your_url.com"

curl \
  -H "Authorization: Bearer $TOKEN" \
  -X POST \
  -d url=$CSV_URL \
  -d mode='append' \
  -d name='events' \
  https://<your_host>/v0/datasources

Add a new line to your crontab file (using sudo crontab -e):

10 0 * * * sh -c /opt/cronjobs/append.sh

Option 2: Using GitHub Actions¶

Use GitHub Actions to schedule periodic jobs. Create a new file called .github/workflows/append.yml to append data from a CSV given its URL every day at 00:10.

name: Append data every day at 00:10

on:
  push:
  workflow_dispatch:
  schedule:
    - cron:  '10 0 * * *'

jobs:
  scheduled:
    runs-on: ubuntu-latest
    steps:
    - name: Check out this repo
      uses: actions/checkout@v2
    - name: Append new data
      run: |-
        curl \
          -H "Authorization: Bearer $TOKEN" \
          -X POST \
            -d url=$CSV_URL \
            -d mode='append' \
            -d name='events' \
            https://<your_host>/v0/datasources

Replace data periodically¶

Consider another example. With this fictional Data Source, imagine a scenario where you want to replace the whole Data Source with a CSV file at a publicly accessible URL every first day of the month.

Option 1: With a shell script¶

#!/bin/bash
TOKEN=your_token
CSV_URL="http://your_url.com"

curl \
  -H "Authorization: Bearer $TOKEN" \
  -X POST \
  -d url=$CSV_URL \
  -d mode='replace' \
  -d name='events' \
  https://<your_host>/v0/datasources

Edit the crontab file to execute your script periodically. Run sudo crontab -e:

* * 1 * * sh -c /opt/cronjobs/replace.sh

Option 2: With GitHub Actions¶

Create a new file called .github/workflows/replace.yml to replace all your data with the CSV at the given URL every day at 00:10.

name: Replace all data every day at 00:10

on:
  push:
  workflow_dispatch:
  schedule:
    - cron:  '10 0 * * *'

jobs:
  scheduled:
    runs-on: ubuntu-latest
    steps:
    - name: Check out this repo
      uses: actions/checkout@v2
    - name: Replace all data
      run: |-
        curl \
          -H "Authorization: Bearer $TOKEN" \
          -X POST \
            -d url=$CSV_URL \  
            -d mode='replace' \
            -d name='events' \
            https://<your_host>/v0/datasources

Replace one month of data¶

Putting your API call inside a shell script supports more complex ingestion processes. For example, imagine you want to replace the last month of events data every day. Each day, you would export a CSV file to a publicly accessible URL and name it something like events_YYYY-MM-DD.csv.

Option 1: With a shell script¶

Script a conditional data replacement process as follows:

#!/bin/bash

TODAY=`date +"%Y-%m-%d"`
ONE_MONTH_AGO=`date -v -1m +%Y-%m-%d`
TOKEN=your_token
DATASOURCE=events
CSV_URL="http://your_url.com"

curl \
  -H "Authorization: Bearer $TOKEN" \
  -X POST \
  -d url=$CSV_URL \  
  -d mode='replace' \
  -d replace_condition=(created_at+BETWEEN+'${ONE_MONTH_AGO}'+AND+'${TODAY}')" \
  -d name=$DATASOURCE \
  https://<your_host>/v0/datasources

Save the file to /opt/cronjobs/daily_replace.sh and add the following line to crontab to run it every day at midnight:

0 0 * * * sh -c /opt/cronjobs/daily_replace.sh

Option 2: With GitHub Actions¶

Create a new file called .github/workflows/replace_last_month.yml to replace the data for the last month every day at 00:10.

name: Replace all data every day at 00:10

on:
  push:
  workflow_dispatch:
  schedule:
    - cron:  '10 0 * * *'

jobs:
  scheduled:
    runs-on: ubuntu-latest
    steps:
    - name: Check out this repo
      uses: actions/checkout@v2
    - name: Replace all data
      run: |-
        TODAY=`date +"%Y-%m-%d"`                                 
        ONE_MONTH_AGO=`date -v -1m +%Y-%m-%d`
        DATASOURCE=events # could also be set via github secrets
        CSV_URL="http://your_url.com" # could also be set via github secrets
        curl \
          -H "Authorization: Bearer $TOKEN" \
          -X POST \
          -d url=$CSV_URL \  
          -d mode='replace' \
          -d replace_condition=(created_at+BETWEEN+'${ONE_MONTH_AGO}'+AND+'${TODAY}')" \
          -d name=$DATASOURCE \
          https://<your_host>/v0/datasources

Next steps¶

• Learn more about GitHub Actions and CI/CD processes on Tinybird.
• Understand how to work with time.