Blueprints
Create table and load data to MySQL using SQL - using taskDefaults to eliminate boilerplate code
Source
yaml
id: extract-load-mysql
namespace: company.team
variables:
table: new
tasks:
- id: extract
type: io.kestra.plugin.core.http.Download
uri: https://huggingface.co/datasets/kestra/datasets/raw/main/csv/orders.csv
- id: enable_local_files
type: io.kestra.plugin.jdbc.mysql.Query
sql: SET GLOBAL local_infile=1;
- id: create_table
type: io.kestra.plugin.jdbc.mysql.Query
sql: |
create table if not exists {{ vars.table }}
(
order_id integer,
customer_name varchar(50),
customer_email varchar(50),
product_id integer,
price real,
quantity integer,
total real
);
- id: load_data
type: io.kestra.plugin.jdbc.mysql.Query
inputFile: "{{ outputs.extract.uri }}"
sql: |
LOAD DATA LOCAL INFILE '{{ inputFile }}'
INTO TABLE {{ vars.table }}
FIELDS TERMINATED BY ','
LINES TERMINATED BY '\n'
IGNORE 1 ROWS;
pluginDefaults:
- type: io.kestra.plugin.jdbc.mysql.Query
values:
url: jdbc:mysql://host.docker.internal:3306/stage
username: root
password: "{{ secret('DB_PASSWORD') }}"
About this blueprint
Ingest SQL
This flow will extract data from a remote CSV file and load it into a MySQL database. It's recommended to store database credentials as secrets.
Note that to avoid repetition, we use pluginDefaults specifying attributes
needed to interact with MySQL.
To start a MySQL database in a Docker container, you can use the following command:
bash
docker run -d -p 3306:3306 --name mymysql -v mymysqldb:/var/lib/mysql -e
MYSQL_ROOT_PASSWORD=topSecret42 -e MYSQL_DATABASE=stage mysql:latest
More Related Blueprints