Install

1. Install a database #

First of all you need a database. The easiest way is to install it via your distribution. If you are on Ubuntu 18.04, this is straight forward:

sudo apt install postgresql-10

2. Download LESQL #

Download LESQL, make it executable and put it into your PATH

wget https://lesql.com/dl/latest/lesql-linux-amd64
mv lesql-linux-amd64 lesql
chmod 755 lesql
sudo mv lesql /usr/local/bin

lesql version
  _                        _
 | |   ___   ___    __ _  | |
 | |  / _ \ / __|  / _` | | |
 | | |  __/ \__ \ | (_| | | |
 |_|  \___| |___/  \__, | |_|
                      |_|

Run your ETL with LESS SQL

lesql version 0.9.0-123bb0a linux/amd64

3. Setup your work directory #

mkdir -p ~/Workspace/lesql
cd ~/Workspace/lesql
lesql init .

This creates a base structure for you models and adds an example project in this structure called zzzexampleproject. Futhermore it creates an SQL file which will set up the DB, schema and tables in your local Postgresql database. Just run the following command:

sudo su
sudo -u postgres psql < quickstart-zzzexampleproject.sql
exit

#Now you should delete the quickstart-zzzexampleproject.sql as it is not needed anymore#

rm quickstart-zzzexampleproject.sql

4. Run your 1st workflow #

lesql run zzzexampleproject -d

This runs the whole project workflow named zzzexampleproject. At this stage it consists of one model only:

{{ block "CONFIG" .}}
{{end}}

{{ block "SELECT" .}}
SELECT * FROM {{ .Project }}.{{ ref "test1" }}
WHERE "date" BETWEEN '{{ .StartDate }}' and '{{ .EndDate }}'
{{end}}

Every model does at least three things:

• It creates the destination table named "zzzexampleproject"."test2", if it doesn’t exist.
• It deletes the data of the last 30 days with (This query was run Nov 23rd): DELETE FROM "zzzexampleproject"."test2" WHERE "date" BETWEEN '2019-10-24' AND '2019-11-23';
• It inserts all data between today and today minus 30 days from table test1 to the destination table "zzzexampleproject"."test2"