-
Notifications
You must be signed in to change notification settings - Fork 0
/
company.team.extract_load_postgres.yml
60 lines (54 loc) · 1.71 KB
/
company.team.extract_load_postgres.yml
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
id: extract_load_postgres
namespace: company.team
variables:
db: jdbc:postgresql://host.docker.internal:5432/postgres
table: public.orders
tasks:
- id: extract
type: io.kestra.plugin.core.http.Download
uri: https://huggingface.co/datasets/kestra/datasets/raw/main/csv/orders.csv
- id: query
type: io.kestra.plugin.jdbc.postgresql.Query
url: "{{ vars.db }}"
username: postgres
password: "{{ secret('DB_PASSWORD') }}"
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_to_postgres
type: io.kestra.plugin.jdbc.postgresql.CopyIn
url: "{{ vars.db }}"
username: postgres
password: "{{ secret('DB_PASSWORD') }}"
from: "{{ outputs.extract.uri }}"
format: CSV
header: true
table: "{{ vars.table }}"
extend:
title: Run DDL queries and load data to Postgres
description: >
This flow will extract data from a remote CSV file and load it into a
Postgres database. It's recommended to store database credentials using
secrets.
For local testing, you can start a Postgres database using Docker:
```bash
docker run -d --name mypostgres -v mypostgresdb:/var/lib/postgresql/data -p
5432:5432 -e POSTGRES_USER=postgres -e POSTGRES_PASSWORD=topSecret42 -e
POSTGRES_DB=postgres postgres:latest
```
tags:
- Ingest
- Postgres
- SQL
ee: false
demo: false
meta_description: This flow will extract data from a remote CSV file and load it
into a Postgres database.