akvo-lumen-db.org

Akvo Lumen database structure walkthrough

List all databases

\l+

#+RESULTS[fa6b8d98a3f29f7f168400e3cde139632f526afe]:

List of databases
Name	Owner	Encoding	Collate	Ctype	Access privileges	Size	Tablespace	Description
lumen	lumen	UTF8	en_US.UTF-8	en_US.UTF-8		14 MB	pg_default
lumen_tenant_1	lumen	UTF8	en_US.UTF-8	en_US.UTF-8		15 MB	pg_default
lumen_tenant_2	lumen	UTF8	en_US.UTF-8	en_US.UTF-8		15 MB	pg_default
postgres	postgres	UTF8	en_US.utf8	en_US.utf8		17 MB	pg_default	default administrative connection database
template0	postgres	UTF8	en_US.utf8	en_US.utf8	=c/postgres
postgres=CTc/postgres	6953 kB	pg_default	unmodifiable empty database
template1	postgres	UTF8	en_US.utf8	en_US.utf8	=c/postgres
postgres=CTc/postgres	6953 kB	pg_default	default template for new databases
template_postgis	postgres	UTF8	en_US.utf8	en_US.utf8		17 MB	pg_default
test_lumen	lumen	UTF8	en_US.UTF-8	en_US.UTF-8		14 MB	pg_default
test_lumen_tenant_1	lumen	UTF8	en_US.UTF-8	en_US.UTF-8		14 MB	pg_default
test_lumen_tenant_2	lumen	UTF8	en_US.UTF-8	en_US.UTF-8		14 MB	pg_default

On lumen (master) database

Akvo Lumen has one main database that holds information about all existing tenants. Each tenant has its own database.

\c lumen
\d+

#+RESULTS[5dc62590b727dfd9098055633bea6bb6dff6599f]:

List of relations
Schema	Name	Type	Owner	Size	Description
public	geography_columns	view	postgres	0 bytes
public	geometry_columns	view	postgres	0 bytes
public	ragtime_migrations	table	lumen	8192 bytes
public	raster_columns	view	postgres	0 bytes
public	raster_overviews	view	postgres	0 bytes
public	spatial_ref_sys	table	postgres	4616 kB
public	tenants	table	lumen	16 kB
public	tenants_id_seq	sequence	lumen	8192 bytes

List tenants

\c lumen
SELECT * FROM tenants

#+RESULTS[a0adf644db5ee80ce042bf0098bb1bf3965510c4]:

id	db_uri	enabled	label	title
1	MxTiCr0Vcw6m78X3ChEDoHbDk7GxOWf8eQBcJ69imThODU4jIPFkM6UxPZb7aUJtp+GSaumwPnTpDmRSPNrYDaE+yBgr6tdgMKWgoQJ6tkA=	t	t1	Tenant 1
2	MxTiCr0Vcw6m78X3ChEDoHbDk7GxOWf8eQBcJ69imThrMApuVdqnC8IIbge5ugbF8Vl2dBOmcVYy/J4JZQCTsRqu2CAa4bdi/CCEMea7ov4=	t	t2	Tenant 2

List tables on lumen_tenant_1

\d+

#+RESULTS[79897a3a390121bf5b0dff72f57ad1e44ea34490]:

List of relations
Schema	Name	Type	Owner	Size	Description
public	collection	table	lumen	8192 bytes
public	collection_entity	table	lumen	8192 bytes
public	dashboard	table	lumen	8192 bytes
public	dashboard_visualisation	table	lumen	8192 bytes
public	data_source	table	lumen	8192 bytes
public	dataset	table	lumen	8192 bytes
public	dataset_version	table	lumen	8192 bytes
public	geography_columns	view	postgres	0 bytes
public	geometry_columns	view	postgres	0 bytes
public	invite	table	lumen	8192 bytes
public	job_execution	table	lumen	8192 bytes
public	ragtime_migrations	table	lumen	8192 bytes
public	raster_columns	view	postgres	0 bytes
public	raster_dataset	table	lumen	8192 bytes
public	raster_overviews	view	postgres	0 bytes
public	share	table	lumen	8192 bytes
public	spatial_ref_sys	table	postgres	4616 kB
public	visualisation	table	lumen	8192 bytes

job_execution, data_source, dataset and dataset_version

Any import process starts with a data_source and a job_execution. Since importing data is a long process, we keep track if its state.

\d job_execution

#+RESULTS[7515c8951426a0d202b5574456bac7df10b3a91d]:

Table “public.job_execution”
Column	Type	Collation	Nullable	Default
id	text		not null
data_source_id	text
dataset_id	text
type	text		not null
execution_log	jsonb
error_log	jsonb
status	text		not null	‘PENDING’::text
created	timestamp with time zone		not null	now()
modified	timestamp with time zone		not null	now()
Indexes:
“job_execution_pkey” PRIMARY KEY, btree (id)
Foreign-key constraints:
“job_execution_data_source_id_fkey” FOREIGN KEY (data_source_id) REFERENCES data_source(id) ON DELETE CASCADE
“job_execution_dataset_id_fkey” FOREIGN KEY (dataset_id) REFERENCES dataset(id) ON DELETE CASCADE
Referenced by:
TABLE “dataset_version” CONSTRAINT “dataset_version_job_execution_id_fkey” FOREIGN KEY (job_execution_id) REFERENCES job_execution(id) ON DELETE CASCADE
TABLE “raster_dataset” CONSTRAINT “raster_dataset_job_execution_id_fkey” FOREIGN KEY (job_execution_id) REFERENCES job_execution(id) ON DELETE CASCADE
Triggers:
job_execution_history AFTER INSERT OR DELETE OR UPDATE ON job_execution FOR EACH ROW EXECUTE PROCEDURE history.log_change()
job_execution_modified BEFORE UPDATE ON job_execution FOR EACH ROW EXECUTE PROCEDURE update_modified()

\d data_source

#+RESULTS[26a99523e25b84b5aee1e36fc37459e203683ed9]:

Table “public.data_source”
Column	Type	Collation	Nullable	Default
id	text		not null
spec	jsonb		not null
created	timestamp with time zone			now()
modified	timestamp with time zone			now()
Indexes:
“data_source_pkey” PRIMARY KEY, btree (id)
Referenced by:
TABLE “job_execution” CONSTRAINT “job_execution_data_source_id_fkey” FOREIGN KEY (data_source_id) REFERENCES data_source(id) ON DELETE CASCADE
Triggers:
data_source_history AFTER INSERT OR DELETE OR UPDATE ON data_source FOR EACH ROW EXECUTE PROCEDURE history.log_change()
data_source_modified BEFORE UPDATE ON data_source FOR EACH ROW EXECUTE PROCEDURE update_modified()

\d dataset

#+RESULTS[47e718884dfd51f0e3fd3b6bd0ea1c5f7e781cba]:

Table “public.dataset”
Column	Type	Collation	Nullable	Default
id	text		not null
title	text		not null
description	text		not null	”::text
created	timestamp with time zone		not null	now()
modified	timestamp with time zone		not null	now()
author	jsonb
Indexes:
“dataset_pkey” PRIMARY KEY, btree (id)
Referenced by:
TABLE “collection_entity” CONSTRAINT “collection_entity_dataset_id_fkey” FOREIGN KEY (dataset_id) REFERENCES dataset(id) ON UPDATE CASCADE ON DELETE CASCADE
TABLE “dataset_version” CONSTRAINT “dataset_version_dataset_id_fkey” FOREIGN KEY (dataset_id) REFERENCES dataset(id) ON DELETE CASCADE
TABLE “job_execution” CONSTRAINT “job_execution_dataset_id_fkey” FOREIGN KEY (dataset_id) REFERENCES dataset(id) ON DELETE CASCADE
TABLE “visualisation” CONSTRAINT “visualisation_dataset_id_fkey” FOREIGN KEY (dataset_id) REFERENCES dataset(id) ON DELETE CASCADE
Triggers:
dataset_history AFTER INSERT OR DELETE OR UPDATE ON dataset FOR EACH ROW EXECUTE PROCEDURE history.log_change()
dataset_modified BEFORE UPDATE ON dataset FOR EACH ROW EXECUTE PROCEDURE update_modified()
update_dashboard_filter_trigger BEFORE DELETE ON dataset FOR EACH ROW EXECUTE PROCEDURE update_dashboard_filter()

\d dataset_version

#+RESULTS[aa16d0d81b091d3ac98d64daf6396154bff49c3e]:

Table “public.dataset_version”
Column	Type	Collation	Nullable	Default
id	text		not null
dataset_id	text		not null
job_execution_id	text		not null
transformations	jsonb		not null	’[]’::jsonb
version	smallint		not null
table_name	text
imported_table_name	text		not null
columns	jsonb		not null	’[]’::jsonb
created	timestamp with time zone		not null	now()
modified	timestamp with time zone		not null	now()
Indexes:
“dataset_version_pkey” PRIMARY KEY, btree (id)
“dataset_version_dataset_id_version_key” UNIQUE CONSTRAINT, btree (dataset_id, version)
“dataset_version_table_name_key” UNIQUE CONSTRAINT, btree (table_name)
“dataset_version_version_idx” btree (version)
Foreign-key constraints:
“dataset_version_dataset_id_fkey” FOREIGN KEY (dataset_id) REFERENCES dataset(id) ON DELETE CASCADE
“dataset_version_job_execution_id_fkey” FOREIGN KEY (job_execution_id) REFERENCES job_execution(id) ON DELETE CASCADE
Triggers:
dataset_version_history AFTER INSERT OR DELETE OR UPDATE ON dataset_version FOR EACH ROW EXECUTE PROCEDURE history.log_change()
dataset_version_modified BEFORE UPDATE ON dataset_version FOR EACH ROW EXECUTE PROCEDURE update_modified()

Let’s import some data

URL: https://code.montera34.com:4443/numeroteca/covid19/-/raw/master/data/output/covid19-cases-uci-deaths-by-ccaa-spain-by-day-accumulated.csv?inline=false

cd /tmp
wget -nc -O covid19.csv \
"https://code.montera34.com:4443/numeroteca/covid19/-/raw/master/data/output/covid19-cases-uci-deaths-by-ccaa-spain-by-day-accumulated.csv?inline=false"
head covid19.csv

#+RESULTS[3697a7a1bc9ceb275523675728fa2080d8e5a794]:

date

region_code

region

country

population

cases_registered

cases_per_100000

intensive_care

intensive_care_per_100000

deceassed

deceassed_per_100000

recovered

recovered_per_100000

hospitalized

hospitalized_per_100000

2020-02-21

1

Andalucía

Spain

8414240

0

0

NA

NA

NA

NA

NA

NA

NA

NA

2020-02-22

1

Andalucía

Spain

8414240

0

0

NA

NA

NA

NA

NA

NA

NA

NA

2020-02-23

1

Andalucía

Spain

8414240

0

0

NA

NA

NA

NA

NA

NA

NA

NA

2020-02-24

1

Andalucía

Spain

8414240

0

0

NA

NA

NA

NA

NA

NA

NA

NA

2020-02-25

1

Andalucía

Spain

8414240

0

0

NA

NA

NA

NA

NA

NA

NA

NA

2020-02-26

1

Andalucía

Spain

8414240

0

0

NA

NA

NA

NA

NA

NA

NA

NA

2020-02-27

1

Andalucía

Spain

8414240

1

0.01

NA

NA

NA

NA

NA

NA

NA

NA

2020-02-28

1

Andalucía

Spain

8414240

6

0.07

NA

NA

NA

NA

NA

NA

NA

NA

2020-02-29

1

Andalucía

Spain

8414240

8

0.1

NA

NA

NA

NA

NA

NA

NA

NA

SELECT * FROM data_source ORDER BY created

#+RESULTS[9e7ac063b7491823c9d541b6e8510c84316aa056]:

id	spec	created	modified
5eb0e110-b755-4c6a-a062-ddf67d76fe6c	{“name”: “Spain Covid19”, “source”: {“url”: “https://code.montera34.com:4443/numeroteca/covid19/-/raw/master/data/output/covid19-cases-uci-deaths-by-ccaa-spain-by-day-accumulated.csv?inline=false”, “kind”: “LINK”, “email”: “jerome@t1.akvolumen.org”, “guessColumnTypes”: true, “hasColumnHeaders”: true}}	2020-05-05 03:44:16.422315+00	2020-05-05 03:44:16.422315+00

SELECT * FROM job_execution ORDER BY created

#+RESULTS[84f87f757eb17eecb03db5be719778eb31906efe]:

id	data_source_id	dataset_id	type	execution_log	error_log	status	created	modified
5eb0e110-21cb-44d1-9323-36a51f0cdb1f	5eb0e110-b755-4c6a-a062-ddf67d76fe6c	5eb0e115-8dc3-443a-a709-6f5efbe1d161	IMPORT			OK	2020-05-05 03:44:16.450618+00	2020-05-05 03:44:21.240064+00

SELECT * FROM dataset ORDER BY created

#+RESULTS[ce52bb868ad8bdeb195460890e39fb2850316ed7]:

id	title	description	created	modified	author
5eb0e115-8dc3-443a-a709-6f5efbe1d161	Spain Covid19		2020-05-05 03:44:21.201343+00	2020-05-05 03:44:21.201343+00	{“name”: “Jerome Eginla”, “email”: “jerome@t1.akvolumen.org”, “given_name”: “Jerome”, “family_name”: “Eginla”}

SELECT * FROM dataset_version ORDER BY created

#+RESULTS[613311f4b1f7dac906953b4184ee366344d95e25]:

id	dataset_id	job_execution_id	transformations	version	table_name	imported_table_name	columns	created	modified
5eb0e115-16d1-4e2b-8473-18ec4c578898	5eb0e115-8dc3-443a-a709-6f5efbe1d161	5eb0e110-21cb-44d1-9323-36a51f0cdb1f	[]	1	ds_eca447a5_37dc_49ba_afbe_a4710273c6f0	imported_22b2c554_3219_4925_8df1_e874fd95c38d	[{“key”: false, “sort”: null, “type”: “text”, “title”: “date”, “hidden”: false, “groupId”: null, “direction”: null, “groupName”: null, “columnName”: “c1”, “multipleId”: null, “multipleType”: null}, {“key”: false, “sort”: null, “type”: “number”, “title”: “region_code”, “hidden”: false, “groupId”: null, “direction”: null, “groupName”: null, “columnName”: “c2”, “multipleId”: null, “multipleType”: null}, {“key”: false, “sort”: null, “type”: “text”, “title”: “region”, “hidden”: false, “groupId”: null, “direction”: null, “groupName”: null, “columnName”: “c3”, “multipleId”: null, “multipleType”: null}, {“key”: false, “sort”: null, “type”: “text”, “title”: “country”, “hidden”: false, “groupId”: null, “direction”: null, “groupName”: null, “columnName”: “c4”, “multipleId”: null, “multipleType”: null}, {“key”: false, “sort”: null, “type”: “number”, “title”: “population”, “hidden”: false, “groupId”: null, “direction”: null, “groupName”: null, “columnName”: “c5”, “multipleId”: null, “multipleType”: null}, {“key”: false, “sort”: null, “type”: “number”, “title”: “cases_registered”, “hidden”: false, “groupId”: null, “direction”: null, “groupName”: null, “columnName”: “c6”, “multipleId”: null, “multipleType”: null}, {“key”: false, “sort”: null, “type”: “number”, “title”: “cases_per_100000”, “hidden”: false, “groupId”: null, “direction”: null, “groupName”: null, “columnName”: “c7”, “multipleId”: null, “multipleType”: null}, {“key”: false, “sort”: null, “type”: “text”, “title”: “intensive_care”, “hidden”: false, “groupId”: null, “direction”: null, “groupName”: null, “columnName”: “c8”, “multipleId”: null, “multipleType”: null}, {“key”: false, “sort”: null, “type”: “text”, “title”: “intensive_care_per_100000”, “hidden”: false, “groupId”: null, “direction”: null, “groupName”: null, “columnName”: “c9”, “multipleId”: null, “multipleType”: null}, {“key”: false, “sort”: null, “type”: “text”, “title”: “deceassed”, “hidden”: false, “groupId”: null, “direction”: null, “groupName”: null, “columnName”: “c10”, “multipleId”: null, “multipleType”: null}, {“key”: false, “sort”: null, “type”: “text”, “title”: “deceassed_per_100000”, “hidden”: false, “groupId”: null, “direction”: null, “groupName”: null, “columnName”: “c11”, “multipleId”: null, “multipleType”: null}, {“key”: false, “sort”: null, “type”: “text”, “title”: “recovered”, “hidden”: false, “groupId”: null, “direction”: null, “groupName”: null, “columnName”: “c12”, “multipleId”: null, “multipleType”: null}, {“key”: false, “sort”: null, “type”: “text”, “title”: “recovered_per_100000”, “hidden”: false, “groupId”: null, “direction”: null, “groupName”: null, “columnName”: “c13”, “multipleId”: null, “multipleType”: null}, {“key”: false, “sort”: null, “type”: “text”, “title”: “hospitalized”, “hidden”: false, “groupId”: null, “direction”: null, “groupName”: null, “columnName”: “c14”, “multipleId”: null, “multipleType”: null}, {“key”: false, “sort”: null, “type”: “text”, “title”: “hospitalized_per_100000”, “hidden”: false, “groupId”: null, “direction”: null, “groupName”: null, “columnName”: “c15”, “multipleId”: null, “multipleType”: null}]	2020-05-05 03:44:21.233018+00	2020-05-05 03:44:21.233018+00

The imported table

\d+ imported_22b2c554_3219_4925_8df1_e874fd95c38d

#+RESULTS[cc1cabe9746ca2a157dcc047609a9316cd0d5c41]:

Table “public.imported_22b2c554_3219_4925_8df1_e874fd95c38d”
Column	Type	Collation	Nullable	Default	Storage	Stats target	Description
rnum	integer		not null	nextval(‘ds_eca447a5_37dc_49ba_afbe_a4710273c6f0_rnum_seq’::regclass)	plain
c1	text				extended
c2	double precision				plain
c3	text				extended
c4	text				extended
c5	double precision				plain
c6	double precision				plain
c7	double precision				plain
c8	text				extended
c9	text				extended
c10	text				extended
c11	text				extended
c12	text				extended
c13	text				extended
c14	text				extended
c15	text				extended
Indexes:
“imported_22b2c554_3219_4925_8df1_e874fd95c38d_pkey” PRIMARY KEY, btree (rnum)

SELECT * FROM imported_22b2c554_3219_4925_8df1_e874fd95c38d LIMIT 10

#+RESULTS[45a5d97f52c9be5a0706508b085bf1a3543a6b4e]:

rnum	c1	c2	c3	c4	c5	c6	c7	c8	c9	c10	c11	c12	c13	c14	c15
1	2020-02-21	1	Andalucía	Spain	8414240	0	0	NA	NA	NA	NA	NA	NA	NA	NA
2	2020-02-22	1	Andalucía	Spain	8414240	0	0	NA	NA	NA	NA	NA	NA	NA	NA
3	2020-02-23	1	Andalucía	Spain	8414240	0	0	NA	NA	NA	NA	NA	NA	NA	NA
4	2020-02-24	1	Andalucía	Spain	8414240	0	0	NA	NA	NA	NA	NA	NA	NA	NA
5	2020-02-25	1	Andalucía	Spain	8414240	0	0	NA	NA	NA	NA	NA	NA	NA	NA
6	2020-02-26	1	Andalucía	Spain	8414240	0	0	NA	NA	NA	NA	NA	NA	NA	NA
7	2020-02-27	1	Andalucía	Spain	8414240	1	0.01	NA	NA	NA	NA	NA	NA	NA	NA
8	2020-02-28	1	Andalucía	Spain	8414240	6	0.07	NA	NA	NA	NA	NA	NA	NA	NA
9	2020-02-29	1	Andalucía	Spain	8414240	8	0.1	NA	NA	NA	NA	NA	NA	NA	NA
10	2020-03-01	1	Andalucía	Spain	8414240	12	0.14	NA	NA	NA	NA	NA	NA	NA	NA

The underlying table

\d+ ds_eca447a5_37dc_49ba_afbe_a4710273c6f0

#+RESULTS[8ca156646ccad2c43c1ade726c0b336d7794eacf]:

Table “public.ds_eca447a5_37dc_49ba_afbe_a4710273c6f0”
Column	Type	Collation	Nullable	Default	Storage	Stats target	Description
rnum	integer		not null	nextval(‘ds_eca447a5_37dc_49ba_afbe_a4710273c6f0_rnum_seq’::regclass)	plain
c1	text				extended
c2	double precision				plain
c3	text				extended
c4	text				extended
c5	double precision				plain
c6	double precision				plain
c7	double precision				plain
c8	text				extended
c9	text				extended
c10	text				extended
c11	text				extended
c12	text				extended
c13	text				extended
c14	text				extended
c15	text				extended
Indexes:
“ds_eca447a5_37dc_49ba_afbe_a4710273c6f0_pkey” PRIMARY KEY, btree (rnum)

SELECT * FROM ds_eca447a5_37dc_49ba_afbe_a4710273c6f0 LIMIT 10

#+RESULTS[867d81d5fd23af3d50023f1ea2931b506b1f42bd]:

rnum	c1	c2	c3	c4	c5	c6	c7	c8	c9	c10	c11	c12	c13	c14	c15
1	2020-02-21	1	Andalucía	Spain	8414240	0	0	NA	NA	NA	NA	NA	NA	NA	NA
2	2020-02-22	1	Andalucía	Spain	8414240	0	0	NA	NA	NA	NA	NA	NA	NA	NA
3	2020-02-23	1	Andalucía	Spain	8414240	0	0	NA	NA	NA	NA	NA	NA	NA	NA
4	2020-02-24	1	Andalucía	Spain	8414240	0	0	NA	NA	NA	NA	NA	NA	NA	NA
5	2020-02-25	1	Andalucía	Spain	8414240	0	0	NA	NA	NA	NA	NA	NA	NA	NA
6	2020-02-26	1	Andalucía	Spain	8414240	0	0	NA	NA	NA	NA	NA	NA	NA	NA
7	2020-02-27	1	Andalucía	Spain	8414240	1	0.01	NA	NA	NA	NA	NA	NA	NA	NA
8	2020-02-28	1	Andalucía	Spain	8414240	6	0.07	NA	NA	NA	NA	NA	NA	NA	NA
9	2020-02-29	1	Andalucía	Spain	8414240	8	0.1	NA	NA	NA	NA	NA	NA	NA	NA
10	2020-03-01	1	Andalucía	Spain	8414240	12	0.14	NA	NA	NA	NA	NA	NA	NA	NA

Let’s add one transformation

SELECT * FROM dataset ORDER BY created

#+RESULTS[ce52bb868ad8bdeb195460890e39fb2850316ed7]:

id	title	description	created	modified	author
5eb0e115-8dc3-443a-a709-6f5efbe1d161	Spain Covid19		2020-05-05 03:44:21.201343+00	2020-05-05 03:57:24.884617+00	{“name”: “Jerome Eginla”, “email”: “jerome@t1.akvolumen.org”, “given_name”: “Jerome”, “family_name”: “Eginla”}

SELECT * FROM dataset_version ORDER BY created

#+RESULTS[613311f4b1f7dac906953b4184ee366344d95e25]:

id	dataset_id	job_execution_id	transformations	version	table_name	imported_table_name	columns	created	modified
5eb0e115-16d1-4e2b-8473-18ec4c578898	5eb0e115-8dc3-443a-a709-6f5efbe1d161	5eb0e110-21cb-44d1-9323-36a51f0cdb1f	[]	1		imported_22b2c554_3219_4925_8df1_e874fd95c38d	[{“key”: false, “sort”: null, “type”: “text”, “title”: “date”, “hidden”: false, “groupId”: null, “direction”: null, “groupName”: null, “columnName”: “c1”, “multipleId”: null, “multipleType”: null}, {“key”: false, “sort”: null, “type”: “number”, “title”: “region_code”, “hidden”: false, “groupId”: null, “direction”: null, “groupName”: null, “columnName”: “c2”, “multipleId”: null, “multipleType”: null}, {“key”: false, “sort”: null, “type”: “text”, “title”: “region”, “hidden”: false, “groupId”: null, “direction”: null, “groupName”: null, “columnName”: “c3”, “multipleId”: null, “multipleType”: null}, {“key”: false, “sort”: null, “type”: “text”, “title”: “country”, “hidden”: false, “groupId”: null, “direction”: null, “groupName”: null, “columnName”: “c4”, “multipleId”: null, “multipleType”: null}, {“key”: false, “sort”: null, “type”: “number”, “title”: “population”, “hidden”: false, “groupId”: null, “direction”: null, “groupName”: null, “columnName”: “c5”, “multipleId”: null, “multipleType”: null}, {“key”: false, “sort”: null, “type”: “number”, “title”: “cases_registered”, “hidden”: false, “groupId”: null, “direction”: null, “groupName”: null, “columnName”: “c6”, “multipleId”: null, “multipleType”: null}, {“key”: false, “sort”: null, “type”: “number”, “title”: “cases_per_100000”, “hidden”: false, “groupId”: null, “direction”: null, “groupName”: null, “columnName”: “c7”, “multipleId”: null, “multipleType”: null}, {“key”: false, “sort”: null, “type”: “text”, “title”: “intensive_care”, “hidden”: false, “groupId”: null, “direction”: null, “groupName”: null, “columnName”: “c8”, “multipleId”: null, “multipleType”: null}, {“key”: false, “sort”: null, “type”: “text”, “title”: “intensive_care_per_100000”, “hidden”: false, “groupId”: null, “direction”: null, “groupName”: null, “columnName”: “c9”, “multipleId”: null, “multipleType”: null}, {“key”: false, “sort”: null, “type”: “text”, “title”: “deceassed”, “hidden”: false, “groupId”: null, “direction”: null, “groupName”: null, “columnName”: “c10”, “multipleId”: null, “multipleType”: null}, {“key”: false, “sort”: null, “type”: “text”, “title”: “deceassed_per_100000”, “hidden”: false, “groupId”: null, “direction”: null, “groupName”: null, “columnName”: “c11”, “multipleId”: null, “multipleType”: null}, {“key”: false, “sort”: null, “type”: “text”, “title”: “recovered”, “hidden”: false, “groupId”: null, “direction”: null, “groupName”: null, “columnName”: “c12”, “multipleId”: null, “multipleType”: null}, {“key”: false, “sort”: null, “type”: “text”, “title”: “recovered_per_100000”, “hidden”: false, “groupId”: null, “direction”: null, “groupName”: null, “columnName”: “c13”, “multipleId”: null, “multipleType”: null}, {“key”: false, “sort”: null, “type”: “text”, “title”: “hospitalized”, “hidden”: false, “groupId”: null, “direction”: null, “groupName”: null, “columnName”: “c14”, “multipleId”: null, “multipleType”: null}, {“key”: false, “sort”: null, “type”: “text”, “title”: “hospitalized_per_100000”, “hidden”: false, “groupId”: null, “direction”: null, “groupName”: null, “columnName”: “c15”, “multipleId”: null, “multipleType”: null}]	2020-05-05 03:44:21.233018+00	2020-05-05 03:57:24.884617+00
5eb0e424-1a49-40e5-8971-e535fa6bfbea	5eb0e115-8dc3-443a-a709-6f5efbe1d161	5eb0e424-abe7-4eec-8611-d85f056e0673	[{“op”: “core/change-datatype”, “args”: {“newType”: “number”, “columnName”: “c8”, “parseFormat”: “YYYY-MM-DD”, “defaultValue”: null}, “onError”: “default-value”, “changedColumns”: {“c8”: {“after”: {“key”: false, “sort”: null, “type”: “number”, “title”: “intensive_care”, “hidden”: false, “groupId”: null, “direction”: null, “groupName”: null, “columnName”: “c8”, “multipleId”: null, “multipleType”: null}, “before”: {“key”: false, “sort”: null, “type”: “text”, “title”: “intensive_care”, “hidden”: false, “groupId”: null, “direction”: null, “groupName”: null, “columnName”: “c8”, “multipleId”: null, “multipleType”: null}}}}]	2	ds_eca447a5_37dc_49ba_afbe_a4710273c6f0	imported_22b2c554_3219_4925_8df1_e874fd95c38d	[{“key”: false, “sort”: null, “type”: “text”, “title”: “date”, “hidden”: false, “groupId”: null, “direction”: null, “groupName”: null, “columnName”: “c1”, “multipleId”: null, “multipleType”: null}, {“key”: false, “sort”: null, “type”: “number”, “title”: “region_code”, “hidden”: false, “groupId”: null, “direction”: null, “groupName”: null, “columnName”: “c2”, “multipleId”: null, “multipleType”: null}, {“key”: false, “sort”: null, “type”: “text”, “title”: “region”, “hidden”: false, “groupId”: null, “direction”: null, “groupName”: null, “columnName”: “c3”, “multipleId”: null, “multipleType”: null}, {“key”: false, “sort”: null, “type”: “text”, “title”: “country”, “hidden”: false, “groupId”: null, “direction”: null, “groupName”: null, “columnName”: “c4”, “multipleId”: null, “multipleType”: null}, {“key”: false, “sort”: null, “type”: “number”, “title”: “population”, “hidden”: false, “groupId”: null, “direction”: null, “groupName”: null, “columnName”: “c5”, “multipleId”: null, “multipleType”: null}, {“key”: false, “sort”: null, “type”: “number”, “title”: “cases_registered”, “hidden”: false, “groupId”: null, “direction”: null, “groupName”: null, “columnName”: “c6”, “multipleId”: null, “multipleType”: null}, {“key”: false, “sort”: null, “type”: “number”, “title”: “cases_per_100000”, “hidden”: false, “groupId”: null, “direction”: null, “groupName”: null, “columnName”: “c7”, “multipleId”: null, “multipleType”: null}, {“key”: false, “sort”: null, “type”: “number”, “title”: “intensive_care”, “hidden”: false, “groupId”: null, “direction”: null, “groupName”: null, “columnName”: “c8”, “multipleId”: null, “multipleType”: null}, {“key”: false, “sort”: null, “type”: “text”, “title”: “intensive_care_per_100000”, “hidden”: false, “groupId”: null, “direction”: null, “groupName”: null, “columnName”: “c9”, “multipleId”: null, “multipleType”: null}, {“key”: false, “sort”: null, “type”: “text”, “title”: “deceassed”, “hidden”: false, “groupId”: null, “direction”: null, “groupName”: null, “columnName”: “c10”, “multipleId”: null, “multipleType”: null}, {“key”: false, “sort”: null, “type”: “text”, “title”: “deceassed_per_100000”, “hidden”: false, “groupId”: null, “direction”: null, “groupName”: null, “columnName”: “c11”, “multipleId”: null, “multipleType”: null}, {“key”: false, “sort”: null, “type”: “text”, “title”: “recovered”, “hidden”: false, “groupId”: null, “direction”: null, “groupName”: null, “columnName”: “c12”, “multipleId”: null, “multipleType”: null}, {“key”: false, “sort”: null, “type”: “text”, “title”: “recovered_per_100000”, “hidden”: false, “groupId”: null, “direction”: null, “groupName”: null, “columnName”: “c13”, “multipleId”: null, “multipleType”: null}, {“key”: false, “sort”: null, “type”: “text”, “title”: “hospitalized”, “hidden”: false, “groupId”: null, “direction”: null, “groupName”: null, “columnName”: “c14”, “multipleId”: null, “multipleType”: null}, {“key”: false, “sort”: null, “type”: “text”, “title”: “hospitalized_per_100000”, “hidden”: false, “groupId”: null, “direction”: null, “groupName”: null, “columnName”: “c15”, “multipleId”: null, “multipleType”: null}]	2020-05-05 03:57:24.884617+00	2020-05-05 03:57:24.884617+00

SELECT version, table_name, imported_table_name from dataset_version ORDER by created

#+RESULTS[57090c8a2e22cdf4c676a33366d4c8b7c5749316]:

version	table_name	imported_table_name
1		imported_22b2c554_3219_4925_8df1_e874fd95c38d
2	ds_eca447a5_37dc_49ba_afbe_a4710273c6f0	imported_22b2c554_3219_4925_8df1_e874fd95c38d

SELECT * FROM job_execution ORDER BY created

#+RESULTS[84f87f757eb17eecb03db5be719778eb31906efe]:

id	data_source_id	dataset_id	type	execution_log	error_log	status	created	modified
5eb0e110-21cb-44d1-9323-36a51f0cdb1f	5eb0e110-b755-4c6a-a062-ddf67d76fe6c	5eb0e115-8dc3-443a-a709-6f5efbe1d161	IMPORT			OK	2020-05-05 03:44:16.450618+00	2020-05-05 03:44:21.240064+00
5eb0e424-abe7-4eec-8611-d85f056e0673		5eb0e115-8dc3-443a-a709-6f5efbe1d161	TRANSFORMATION			OK	2020-05-05 03:57:24.849812+00	2020-05-05 03:57:24.884617+00

\d+ ds_eca447a5_37dc_49ba_afbe_a4710273c6f0

#+RESULTS[8ca156646ccad2c43c1ade726c0b336d7794eacf]:

Table “public.ds_eca447a5_37dc_49ba_afbe_a4710273c6f0”
Column	Type	Collation	Nullable	Default	Storage	Stats target	Description
rnum	integer		not null	nextval(‘ds_eca447a5_37dc_49ba_afbe_a4710273c6f0_rnum_seq’::regclass)	plain
c1	text				extended
c2	double precision				plain
c3	text				extended
c4	text				extended
c5	double precision				plain
c6	double precision				plain
c7	double precision				plain
c8	double precision				plain
c9	text				extended
c10	text				extended
c11	text				extended
c12	text				extended
c13	text				extended
c14	text				extended
c15	text				extended
Indexes:
“ds_eca447a5_37dc_49ba_afbe_a4710273c6f0_pkey” PRIMARY KEY, btree (rnum)

SELECT * FROM ds_eca447a5_37dc_49ba_afbe_a4710273c6f0 LIMIT 10

#+RESULTS[867d81d5fd23af3d50023f1ea2931b506b1f42bd]:

rnum	c1	c2	c3	c4	c5	c6	c7	c8	c9	c10	c11	c12	c13	c14	c15
1	2020-02-21	1	Andalucía	Spain	8414240	0	0		NA	NA	NA	NA	NA	NA	NA
2	2020-02-22	1	Andalucía	Spain	8414240	0	0		NA	NA	NA	NA	NA	NA	NA
3	2020-02-23	1	Andalucía	Spain	8414240	0	0		NA	NA	NA	NA	NA	NA	NA
4	2020-02-24	1	Andalucía	Spain	8414240	0	0		NA	NA	NA	NA	NA	NA	NA
5	2020-02-25	1	Andalucía	Spain	8414240	0	0		NA	NA	NA	NA	NA	NA	NA
6	2020-02-26	1	Andalucía	Spain	8414240	0	0		NA	NA	NA	NA	NA	NA	NA
7	2020-02-27	1	Andalucía	Spain	8414240	1	0.01		NA	NA	NA	NA	NA	NA	NA
8	2020-02-28	1	Andalucía	Spain	8414240	6	0.07		NA	NA	NA	NA	NA	NA	NA
9	2020-02-29	1	Andalucía	Spain	8414240	8	0.1		NA	NA	NA	NA	NA	NA	NA
10	2020-03-01	1	Andalucía	Spain	8414240	12	0.14		NA	NA	NA	NA	NA	NA	NA

Let’s add another transformation

SELECT * FROM dataset

#+RESULTS[6ad7bb7ab69c578be4bc2739ef99eac1d49cce37]:

id	title	description	created	modified	author
5eb0e115-8dc3-443a-a709-6f5efbe1d161	Spain Covid19		2020-05-05 03:44:21.201343+00	2020-05-05 04:02:43.342994+00	{“name”: “Jerome Eginla”, “email”: “jerome@t1.akvolumen.org”, “given_name”: “Jerome”, “family_name”: “Eginla”}

SELECT version, table_name, imported_table_name FROM dataset_version ORDER BY created

#+RESULTS[8051fb164df46d26530dbe6f66c06e5fb1125827]:

version	table_name	imported_table_name
1		imported_22b2c554_3219_4925_8df1_e874fd95c38d
2		imported_22b2c554_3219_4925_8df1_e874fd95c38d
3	ds_eca447a5_37dc_49ba_afbe_a4710273c6f0	imported_22b2c554_3219_4925_8df1_e874fd95c38d

Let’s update the dataset

SELECT * FROM dataset

#+RESULTS[6ad7bb7ab69c578be4bc2739ef99eac1d49cce37]:

id	title	description	created	modified	author
5eb0e115-8dc3-443a-a709-6f5efbe1d161	Spain Covid19		2020-05-05 03:44:21.201343+00	2020-05-05 04:05:06.375539+00	{“name”: “Jerome Eginla”, “email”: “jerome@t1.akvolumen.org”, “given_name”: “Jerome”, “family_name”: “Eginla”}

SELECT * FROM job_execution ORDER BY created

#+RESULTS[84f87f757eb17eecb03db5be719778eb31906efe]:

id	data_source_id	dataset_id	type	execution_log	error_log	status	created	modified
5eb0e110-21cb-44d1-9323-36a51f0cdb1f	5eb0e110-b755-4c6a-a062-ddf67d76fe6c	5eb0e115-8dc3-443a-a709-6f5efbe1d161	IMPORT			OK	2020-05-05 03:44:16.450618+00	2020-05-05 03:44:21.240064+00
5eb0e424-abe7-4eec-8611-d85f056e0673		5eb0e115-8dc3-443a-a709-6f5efbe1d161	TRANSFORMATION			OK	2020-05-05 03:57:24.849812+00	2020-05-05 03:57:24.884617+00
5eb0e563-0336-48fd-96b1-c7e589b29d54		5eb0e115-8dc3-443a-a709-6f5efbe1d161	TRANSFORMATION			OK	2020-05-05 04:02:43.32378+00	2020-05-05 04:02:43.342994+00
5eb0e5f1-5c7f-4202-9629-1f90f0c4bbf2	5eb0e110-b755-4c6a-a062-ddf67d76fe6c	5eb0e115-8dc3-443a-a709-6f5efbe1d161	UPDATE			OK	2020-05-05 04:05:05.90735+00	2020-05-05 04:05:06.375539+00

SELECT * FROM dataset_version ORDER BY created

#+RESULTS[613311f4b1f7dac906953b4184ee366344d95e25]:

id	dataset_id	job_execution_id	transformations	version	table_name	imported_table_name	columns	created	modified
5eb0e115-16d1-4e2b-8473-18ec4c578898	5eb0e115-8dc3-443a-a709-6f5efbe1d161	5eb0e110-21cb-44d1-9323-36a51f0cdb1f	[]	1		imported_22b2c554_3219_4925_8df1_e874fd95c38d	[{“key”: false, “sort”: null, “type”: “text”, “title”: “date”, “hidden”: false, “groupId”: null, “direction”: null, “groupName”: null, “columnName”: “c1”, “multipleId”: null, “multipleType”: null}, {“key”: false, “sort”: null, “type”: “number”, “title”: “region_code”, “hidden”: false, “groupId”: null, “direction”: null, “groupName”: null, “columnName”: “c2”, “multipleId”: null, “multipleType”: null}, {“key”: false, “sort”: null, “type”: “text”, “title”: “region”, “hidden”: false, “groupId”: null, “direction”: null, “groupName”: null, “columnName”: “c3”, “multipleId”: null, “multipleType”: null}, {“key”: false, “sort”: null, “type”: “text”, “title”: “country”, “hidden”: false, “groupId”: null, “direction”: null, “groupName”: null, “columnName”: “c4”, “multipleId”: null, “multipleType”: null}, {“key”: false, “sort”: null, “type”: “number”, “title”: “population”, “hidden”: false, “groupId”: null, “direction”: null, “groupName”: null, “columnName”: “c5”, “multipleId”: null, “multipleType”: null}, {“key”: false, “sort”: null, “type”: “number”, “title”: “cases_registered”, “hidden”: false, “groupId”: null, “direction”: null, “groupName”: null, “columnName”: “c6”, “multipleId”: null, “multipleType”: null}, {“key”: false, “sort”: null, “type”: “number”, “title”: “cases_per_100000”, “hidden”: false, “groupId”: null, “direction”: null, “groupName”: null, “columnName”: “c7”, “multipleId”: null, “multipleType”: null}, {“key”: false, “sort”: null, “type”: “text”, “title”: “intensive_care”, “hidden”: false, “groupId”: null, “direction”: null, “groupName”: null, “columnName”: “c8”, “multipleId”: null, “multipleType”: null}, {“key”: false, “sort”: null, “type”: “text”, “title”: “intensive_care_per_100000”, “hidden”: false, “groupId”: null, “direction”: null, “groupName”: null, “columnName”: “c9”, “multipleId”: null, “multipleType”: null}, {“key”: false, “sort”: null, “type”: “text”, “title”: “deceassed”, “hidden”: false, “groupId”: null, “direction”: null, “groupName”: null, “columnName”: “c10”, “multipleId”: null, “multipleType”: null}, {“key”: false, “sort”: null, “type”: “text”, “title”: “deceassed_per_100000”, “hidden”: false, “groupId”: null, “direction”: null, “groupName”: null, “columnName”: “c11”, “multipleId”: null, “multipleType”: null}, {“key”: false, “sort”: null, “type”: “text”, “title”: “recovered”, “hidden”: false, “groupId”: null, “direction”: null, “groupName”: null, “columnName”: “c12”, “multipleId”: null, “multipleType”: null}, {“key”: false, “sort”: null, “type”: “text”, “title”: “recovered_per_100000”, “hidden”: false, “groupId”: null, “direction”: null, “groupName”: null, “columnName”: “c13”, “multipleId”: null, “multipleType”: null}, {“key”: false, “sort”: null, “type”: “text”, “title”: “hospitalized”, “hidden”: false, “groupId”: null, “direction”: null, “groupName”: null, “columnName”: “c14”, “multipleId”: null, “multipleType”: null}, {“key”: false, “sort”: null, “type”: “text”, “title”: “hospitalized_per_100000”, “hidden”: false, “groupId”: null, “direction”: null, “groupName”: null, “columnName”: “c15”, “multipleId”: null, “multipleType”: null}]	2020-05-05 03:44:21.233018+00	2020-05-05 03:57:24.884617+00
5eb0e424-1a49-40e5-8971-e535fa6bfbea	5eb0e115-8dc3-443a-a709-6f5efbe1d161	5eb0e424-abe7-4eec-8611-d85f056e0673	[{“op”: “core/change-datatype”, “args”: {“newType”: “number”, “columnName”: “c8”, “parseFormat”: “YYYY-MM-DD”, “defaultValue”: null}, “onError”: “default-value”, “changedColumns”: {“c8”: {“after”: {“key”: false, “sort”: null, “type”: “number”, “title”: “intensive_care”, “hidden”: false, “groupId”: null, “direction”: null, “groupName”: null, “columnName”: “c8”, “multipleId”: null, “multipleType”: null}, “before”: {“key”: false, “sort”: null, “type”: “text”, “title”: “intensive_care”, “hidden”: false, “groupId”: null, “direction”: null, “groupName”: null, “columnName”: “c8”, “multipleId”: null, “multipleType”: null}}}}]	2		imported_22b2c554_3219_4925_8df1_e874fd95c38d	[{“key”: false, “sort”: null, “type”: “text”, “title”: “date”, “hidden”: false, “groupId”: null, “direction”: null, “groupName”: null, “columnName”: “c1”, “multipleId”: null, “multipleType”: null}, {“key”: false, “sort”: null, “type”: “number”, “title”: “region_code”, “hidden”: false, “groupId”: null, “direction”: null, “groupName”: null, “columnName”: “c2”, “multipleId”: null, “multipleType”: null}, {“key”: false, “sort”: null, “type”: “text”, “title”: “region”, “hidden”: false, “groupId”: null, “direction”: null, “groupName”: null, “columnName”: “c3”, “multipleId”: null, “multipleType”: null}, {“key”: false, “sort”: null, “type”: “text”, “title”: “country”, “hidden”: false, “groupId”: null, “direction”: null, “groupName”: null, “columnName”: “c4”, “multipleId”: null, “multipleType”: null}, {“key”: false, “sort”: null, “type”: “number”, “title”: “population”, “hidden”: false, “groupId”: null, “direction”: null, “groupName”: null, “columnName”: “c5”, “multipleId”: null, “multipleType”: null}, {“key”: false, “sort”: null, “type”: “number”, “title”: “cases_registered”, “hidden”: false, “groupId”: null, “direction”: null, “groupName”: null, “columnName”: “c6”, “multipleId”: null, “multipleType”: null}, {“key”: false, “sort”: null, “type”: “number”, “title”: “cases_per_100000”, “hidden”: false, “groupId”: null, “direction”: null, “groupName”: null, “columnName”: “c7”, “multipleId”: null, “multipleType”: null}, {“key”: false, “sort”: null, “type”: “number”, “title”: “intensive_care”, “hidden”: false, “groupId”: null, “direction”: null, “groupName”: null, “columnName”: “c8”, “multipleId”: null, “multipleType”: null}, {“key”: false, “sort”: null, “type”: “text”, “title”: “intensive_care_per_100000”, “hidden”: false, “groupId”: null, “direction”: null, “groupName”: null, “columnName”: “c9”, “multipleId”: null, “multipleType”: null}, {“key”: false, “sort”: null, “type”: “text”, “title”: “deceassed”, “hidden”: false, “groupId”: null, “direction”: null, “groupName”: null, “columnName”: “c10”, “multipleId”: null, “multipleType”: null}, {“key”: false, “sort”: null, “type”: “text”, “title”: “deceassed_per_100000”, “hidden”: false, “groupId”: null, “direction”: null, “groupName”: null, “columnName”: “c11”, “multipleId”: null, “multipleType”: null}, {“key”: false, “sort”: null, “type”: “text”, “title”: “recovered”, “hidden”: false, “groupId”: null, “direction”: null, “groupName”: null, “columnName”: “c12”, “multipleId”: null, “multipleType”: null}, {“key”: false, “sort”: null, “type”: “text”, “title”: “recovered_per_100000”, “hidden”: false, “groupId”: null, “direction”: null, “groupName”: null, “columnName”: “c13”, “multipleId”: null, “multipleType”: null}, {“key”: false, “sort”: null, “type”: “text”, “title”: “hospitalized”, “hidden”: false, “groupId”: null, “direction”: null, “groupName”: null, “columnName”: “c14”, “multipleId”: null, “multipleType”: null}, {“key”: false, “sort”: null, “type”: “text”, “title”: “hospitalized_per_100000”, “hidden”: false, “groupId”: null, “direction”: null, “groupName”: null, “columnName”: “c15”, “multipleId”: null, “multipleType”: null}]	2020-05-05 03:57:24.884617+00	2020-05-05 04:02:43.342994+00
5eb0e563-307c-471a-b82e-68380b6c9ee7	5eb0e115-8dc3-443a-a709-6f5efbe1d161	5eb0e563-0336-48fd-96b1-c7e589b29d54	[]	3	ds_eca447a5_37dc_49ba_afbe_a4710273c6f0	imported_22b2c554_3219_4925_8df1_e874fd95c38d	[{“sort”: null, “type”: “text”, “title”: “date”, “hidden”: false, “groupId”: null, “direction”: null, “groupName”: null, “columnName”: “c1”}, {“sort”: null, “type”: “number”, “title”: “region_code”, “hidden”: false, “groupId”: null, “direction”: null, “groupName”: null, “columnName”: “c2”}, {“sort”: null, “type”: “text”, “title”: “region”, “hidden”: false, “groupId”: null, “direction”: null, “groupName”: null, “columnName”: “c3”}, {“sort”: null, “type”: “text”, “title”: “country”, “hidden”: false, “groupId”: null, “direction”: null, “groupName”: null, “columnName”: “c4”}, {“sort”: null, “type”: “number”, “title”: “population”, “hidden”: false, “groupId”: null, “direction”: null, “groupName”: null, “columnName”: “c5”}, {“sort”: null, “type”: “number”, “title”: “cases_registered”, “hidden”: false, “groupId”: null, “direction”: null, “groupName”: null, “columnName”: “c6”}, {“sort”: null, “type”: “number”, “title”: “cases_per_100000”, “hidden”: false, “groupId”: null, “direction”: null, “groupName”: null, “columnName”: “c7”}, {“sort”: null, “type”: “text”, “title”: “intensive_care”, “hidden”: false, “groupId”: null, “direction”: null, “groupName”: null, “columnName”: “c8”}, {“sort”: null, “type”: “text”, “title”: “intensive_care_per_100000”, “hidden”: false, “groupId”: null, “direction”: null, “groupName”: null, “columnName”: “c9”}, {“sort”: null, “type”: “text”, “title”: “deceassed”, “hidden”: false, “groupId”: null, “direction”: null, “groupName”: null, “columnName”: “c10”}, {“sort”: null, “type”: “text”, “title”: “deceassed_per_100000”, “hidden”: false, “groupId”: null, “direction”: null, “groupName”: null, “columnName”: “c11”}, {“sort”: null, “type”: “text”, “title”: “recovered”, “hidden”: false, “groupId”: null, “direction”: null, “groupName”: null, “columnName”: “c12”}, {“sort”: null, “type”: “text”, “title”: “recovered_per_100000”, “hidden”: false, “groupId”: null, “direction”: null, “groupName”: null, “columnName”: “c13”}, {“sort”: null, “type”: “text”, “title”: “hospitalized”, “hidden”: false, “groupId”: null, “direction”: null, “groupName”: null, “columnName”: “c14”}, {“sort”: null, “type”: “text”, “title”: “hospitalized_per_100000”, “hidden”: false, “groupId”: null, “direction”: null, “groupName”: null, “columnName”: “c15”}]	2020-05-05 04:02:43.342994+00	2020-05-05 04:05:06.375539+00
5eb0e5f4-dcd0-40f3-91e7-d51a94cf063c	5eb0e115-8dc3-443a-a709-6f5efbe1d161	5eb0e5f1-5c7f-4202-9629-1f90f0c4bbf2	[{“op”: “core/change-datatype”, “args”: {“newType”: “number”, “columnName”: “c8”, “parseFormat”: “YYYY-MM-DD”, “defaultValue”: null}, “onError”: “default-value”, “changedColumns”: {“c8”: {“after”: {“sort”: null, “type”: “number”, “title”: “intensive_care”, “hidden”: false, “groupId”: null, “direction”: null, “groupName”: null, “columnName”: “c8”}, “before”: {“sort”: null, “type”: “text”, “title”: “intensive_care”, “hidden”: false, “groupId”: null, “direction”: null, “groupName”: null, “columnName”: “c8”}}}}, {“op”: “core/to-uppercase”, “args”: {“columnName”: “c4”, “defaultValue”: null}, “onError”: “default-value”, “changedColumns”: {}}]	4	ds_bd94aa24_6b47_4f99_9b4b_8f66809223c8	imported_9eb81ef9_c9df_4865_a0f9_5434afb57b61	[{“sort”: null, “type”: “text”, “title”: “date”, “hidden”: false, “groupId”: null, “direction”: null, “groupName”: null, “columnName”: “c1”}, {“sort”: null, “type”: “number”, “title”: “region_code”, “hidden”: false, “groupId”: null, “direction”: null, “groupName”: null, “columnName”: “c2”}, {“sort”: null, “type”: “text”, “title”: “region”, “hidden”: false, “groupId”: null, “direction”: null, “groupName”: null, “columnName”: “c3”}, {“sort”: null, “type”: “text”, “title”: “country”, “hidden”: false, “groupId”: null, “direction”: null, “groupName”: null, “columnName”: “c4”}, {“sort”: null, “type”: “number”, “title”: “population”, “hidden”: false, “groupId”: null, “direction”: null, “groupName”: null, “columnName”: “c5”}, {“sort”: null, “type”: “number”, “title”: “cases_registered”, “hidden”: false, “groupId”: null, “direction”: null, “groupName”: null, “columnName”: “c6”}, {“sort”: null, “type”: “number”, “title”: “cases_per_100000”, “hidden”: false, “groupId”: null, “direction”: null, “groupName”: null, “columnName”: “c7”}, {“sort”: null, “type”: “number”, “title”: “intensive_care”, “hidden”: false, “groupId”: null, “direction”: null, “groupName”: null, “columnName”: “c8”}, {“sort”: null, “type”: “text”, “title”: “intensive_care_per_100000”, “hidden”: false, “groupId”: null, “direction”: null, “groupName”: null, “columnName”: “c9”}, {“sort”: null, “type”: “text”, “title”: “deceassed”, “hidden”: false, “groupId”: null, “direction”: null, “groupName”: null, “columnName”: “c10”}, {“sort”: null, “type”: “text”, “title”: “deceassed_per_100000”, “hidden”: false, “groupId”: null, “direction”: null, “groupName”: null, “columnName”: “c11”}, {“sort”: null, “type”: “text”, “title”: “recovered”, “hidden”: false, “groupId”: null, “direction”: null, “groupName”: null, “columnName”: “c12”}, {“sort”: null, “type”: “text”, “title”: “recovered_per_100000”, “hidden”: false, “groupId”: null, “direction”: null, “groupName”: null, “columnName”: “c13”}, {“sort”: null, “type”: “text”, “title”: “hospitalized”, “hidden”: false, “groupId”: null, “direction”: null, “groupName”: null, “columnName”: “c14”}, {“sort”: null, “type”: “text”, “title”: “hospitalized_per_100000”, “hidden”: false, “groupId”: null, “direction”: null, “groupName”: null, “columnName”: “c15”}]	2020-05-05 04:05:06.375539+00	2020-05-05 04:05:06.375539+00

SELECT version, imported_table_name, table_name FROM dataset_version ORDER BY created

#+RESULTS[7e3627eb4eecf4ffbc81d54455bb97a7d6b8fa6a]:

version	imported_table_name	table_name
1	imported_22b2c554_3219_4925_8df1_e874fd95c38d
2	imported_22b2c554_3219_4925_8df1_e874fd95c38d
3	imported_22b2c554_3219_4925_8df1_e874fd95c38d	ds_eca447a5_37dc_49ba_afbe_a4710273c6f0
4	imported_9eb81ef9_c9df_4865_a0f9_5434afb57b61	ds_bd94aa24_6b47_4f99_9b4b_8f66809223c8

SELECT * FROM imported_9eb81ef9_c9df_4865_a0f9_5434afb57b61 ORDER BY rnum LIMIT 10

#+RESULTS[5d79dd0ee95e46590ec1dfe4b7e08eab39b87754]:

rnum	c1	c2	c3	c4	c5	c6	c7	c8	c9	c10	c11	c12	c13	c14	c15
1	2020-02-21	1	Andalucía	Spain	8414240	0	0	NA	NA	NA	NA	NA	NA	NA	NA
2	2020-02-22	1	Andalucía	Spain	8414240	0	0	NA	NA	NA	NA	NA	NA	NA	NA
3	2020-02-23	1	Andalucía	Spain	8414240	0	0	NA	NA	NA	NA	NA	NA	NA	NA
4	2020-02-24	1	Andalucía	Spain	8414240	0	0	NA	NA	NA	NA	NA	NA	NA	NA
5	2020-02-25	1	Andalucía	Spain	8414240	0	0	NA	NA	NA	NA	NA	NA	NA	NA
6	2020-02-26	1	Andalucía	Spain	8414240	0	0	NA	NA	NA	NA	NA	NA	NA	NA
7	2020-02-27	1	Andalucía	Spain	8414240	1	0.01	NA	NA	NA	NA	NA	NA	NA	NA
8	2020-02-28	1	Andalucía	Spain	8414240	6	0.07	NA	NA	NA	NA	NA	NA	NA	NA
9	2020-02-29	1	Andalucía	Spain	8414240	8	0.1	NA	NA	NA	NA	NA	NA	NA	NA
10	2020-03-01	1	Andalucía	Spain	8414240	12	0.14	NA	NA	NA	NA	NA	NA	NA	NA

SELECT * FROM ds_bd94aa24_6b47_4f99_9b4b_8f66809223c8 ORDER BY rnum LIMIT 10

#+RESULTS[2c48e3e3e560ad3e531f08b71fba502ead16a747]:

rnum	c1	c2	c3	c4	c5	c6	c7	c8	c9	c10	c11	c12	c13	c14	c15
1	2020-02-21	1	Andalucía	SPAIN	8414240	0	0		NA	NA	NA	NA	NA	NA	NA
2	2020-02-22	1	Andalucía	SPAIN	8414240	0	0		NA	NA	NA	NA	NA	NA	NA
3	2020-02-23	1	Andalucía	SPAIN	8414240	0	0		NA	NA	NA	NA	NA	NA	NA
4	2020-02-24	1	Andalucía	SPAIN	8414240	0	0		NA	NA	NA	NA	NA	NA	NA
5	2020-02-25	1	Andalucía	SPAIN	8414240	0	0		NA	NA	NA	NA	NA	NA	NA
6	2020-02-26	1	Andalucía	SPAIN	8414240	0	0		NA	NA	NA	NA	NA	NA	NA
7	2020-02-27	1	Andalucía	SPAIN	8414240	1	0.01		NA	NA	NA	NA	NA	NA	NA
8	2020-02-28	1	Andalucía	SPAIN	8414240	6	0.07		NA	NA	NA	NA	NA	NA	NA
9	2020-02-29	1	Andalucía	SPAIN	8414240	8	0.1		NA	NA	NA	NA	NA	NA	NA
10	2020-03-01	1	Andalucía	SPAIN	8414240	12	0.14		NA	NA	NA	NA	NA	NA	NA

SELECT * FROM dataset

#+RESULTS[6ad7bb7ab69c578be4bc2739ef99eac1d49cce37]:

id	title	description	created	modified	author
5eb0e115-8dc3-443a-a709-6f5efbe1d161	Spain Covid19		2020-05-05 03:44:21.201343+00	2020-05-05 04:05:06.375539+00	{“name”: “Jerome Eginla”, “email”: “jerome@t1.akvolumen.org”, “given_name”: “Jerome”, “family_name”: “Eginla”}

SELECT version,job_execution_id,transformations, imported_table_name, table_name FROM dataset_version ORDER BY created

#+RESULTS[8bc6e47ee955639dd5f0f6e032e8fcb3a90f5f23]:

version	job_execution_id	transformations	imported_table_name	table_name
1	5eb0e110-21cb-44d1-9323-36a51f0cdb1f	[]	imported_22b2c554_3219_4925_8df1_e874fd95c38d
2	5eb0e424-abe7-4eec-8611-d85f056e0673	[{“op”: “core/change-datatype”, “args”: {“newType”: “number”, “columnName”: “c8”, “parseFormat”: “YYYY-MM-DD”, “defaultValue”: null}, “onError”: “default-value”, “changedColumns”: {“c8”: {“after”: {“key”: false, “sort”: null, “type”: “number”, “title”: “intensive_care”, “hidden”: false, “groupId”: null, “direction”: null, “groupName”: null, “columnName”: “c8”, “multipleId”: null, “multipleType”: null}, “before”: {“key”: false, “sort”: null, “type”: “text”, “title”: “intensive_care”, “hidden”: false, “groupId”: null, “direction”: null, “groupName”: null, “columnName”: “c8”, “multipleId”: null, “multipleType”: null}}}}]	imported_22b2c554_3219_4925_8df1_e874fd95c38d
3	5eb0e563-0336-48fd-96b1-c7e589b29d54	[]	imported_22b2c554_3219_4925_8df1_e874fd95c38d	ds_eca447a5_37dc_49ba_afbe_a4710273c6f0
4	5eb0e5f1-5c7f-4202-9629-1f90f0c4bbf2	[{“op”: “core/change-datatype”, “args”: {“newType”: “number”, “columnName”: “c8”, “parseFormat”: “YYYY-MM-DD”, “defaultValue”: null}, “onError”: “default-value”, “changedColumns”: {“c8”: {“after”: {“sort”: null, “type”: “number”, “title”: “intensive_care”, “hidden”: false, “groupId”: null, “direction”: null, “groupName”: null, “columnName”: “c8”}, “before”: {“sort”: null, “type”: “text”, “title”: “intensive_care”, “hidden”: false, “groupId”: null, “direction”: null, “groupName”: null, “columnName”: “c8”}}}}, {“op”: “core/to-uppercase”, “args”: {“columnName”: “c4”, “defaultValue”: null}, “onError”: “default-value”, “changedColumns”: {}}]	imported_9eb81ef9_c9df_4865_a0f9_5434afb57b61
5	5eb0e771-b413-4e16-923a-cd34b5e2c01d	[{“op”: “core/change-datatype”, “args”: {“newType”: “number”, “columnName”: “c8”, “parseFormat”: “YYYY-MM-DD”, “defaultValue”: null}, “onError”: “default-value”, “changedColumns”: {“c8”: {“after”: {“sort”: null, “type”: “number”, “title”: “intensive_care”, “hidden”: false, “groupId”: null, “direction”: null, “groupName”: null, “columnName”: “c8”}, “before”: {“sort”: null, “type”: “text”, “title”: “intensive_care”, “hidden”: false, “groupId”: null, “direction”: null, “groupName”: null, “columnName”: “c8”}}}}]	imported_9eb81ef9_c9df_4865_a0f9_5434afb57b61	ds_1dd6afa1_70d9_4de2_852e_8d7e7fa09120

SELECT * FROM job_execution ORDER BY created

#+RESULTS[84f87f757eb17eecb03db5be719778eb31906efe]:

id	data_source_id	dataset_id	type	execution_log	error_log	status	created	modified
5eb0e110-21cb-44d1-9323-36a51f0cdb1f	5eb0e110-b755-4c6a-a062-ddf67d76fe6c	5eb0e115-8dc3-443a-a709-6f5efbe1d161	IMPORT			OK	2020-05-05 03:44:16.450618+00	2020-05-05 03:44:21.240064+00
5eb0e424-abe7-4eec-8611-d85f056e0673		5eb0e115-8dc3-443a-a709-6f5efbe1d161	TRANSFORMATION			OK	2020-05-05 03:57:24.849812+00	2020-05-05 03:57:24.884617+00
5eb0e563-0336-48fd-96b1-c7e589b29d54		5eb0e115-8dc3-443a-a709-6f5efbe1d161	TRANSFORMATION			OK	2020-05-05 04:02:43.32378+00	2020-05-05 04:02:43.342994+00
5eb0e5f1-5c7f-4202-9629-1f90f0c4bbf2	5eb0e110-b755-4c6a-a062-ddf67d76fe6c	5eb0e115-8dc3-443a-a709-6f5efbe1d161	UPDATE			OK	2020-05-05 04:05:05.90735+00	2020-05-05 04:05:06.375539+00
5eb0e771-b413-4e16-923a-cd34b5e2c01d		5eb0e115-8dc3-443a-a709-6f5efbe1d161	TRANSFORMATION			OK	2020-05-05 04:11:29.44349+00	2020-05-05 04:11:29.465166+00