DAI-C8-EC: Eines ETL i Integració de Dades. Kettle

De wikijoan
Salta a la navegació Salta a la cerca

Objectius

Unitat Didàctica: UD10. Eines ETL i Integració de Dades. Kettle

ETL. Amb informació de cens dels municipis de l'estat (informació oficial agafada del INE) haurem de fer un repositori de dades (warehouse). L'objectiu és doble:

  1. amb kettle, hauràs d'importar la informació disponible (fitxers CSV i Excel) al repositori.
  2. hauràs de definir amb Schema Workbench un cub multidimensional, i publicar-lo en un servidor Mondrian.

Desenvolupament

Kettle is an open source ETL­suite for Extracting data from various sources, Transforming it and Loading it into the data warehouse. Kettle consists of two sets of tools. Spoon and Pan are used to respectively create and execute transformations on data. Chef and Kitchen are used to organize transformations into jobs and execute them in a scheduled way.

Kettle is an acronym for Kettle E.T.T.L. Environment. Kettle is designed to help you with your ETTL needs, which include the Extraction, Transformation, Transportation and Loading of data.

Spoon is a graphical user interface that allows you to design transformations and jobs that can be run with the Kettle tools — Pan and Kitchen. Pan is a data transformation engine that performs a multitude of functions such as reading, manipulating, and writing data to and from various data sources. Kitchen is a program that executes jobs designed by Spoon in XML or in a database repository. Jobs are usually scheduled in batch mode to be run automatically at regular intervals.

Instal.lació

De SourceForge (http://sourceforge.net/projects/pentaho/files/Data Integration/) em descarrego la última versió estable: pdi-ce-3.2.0-stable.tar.gz (69 Mb)

Descomprimeixo a la carpeta /usr/local: 

$ sudo tar xvzf pdi-ce-3.2.0-stable.tar.gz

i es crea la carpeta data-integration/

llegir README_FIRST.txt i README_LINUX.txt 

To get started, look in the [docs/English] directory for the file [Spoon_3_0_0_User_Guide.pdf].
In it, you'll find an installation description and a user manual for the graphical editor Spoon.

sh spoon.sh

Mirar exemples a samples/

If you want support for the built-in browser in Linux, please install Mozilla XULRunner.

Abans de començar a treballar, crearem la base de dades municipis en el Postgres: 

$ psql -h localhost -U postgres postgres
postgres# CREATE DATABASE municipis ENCODING 'UTF-8';
postgres=# CREATE USER municipi WITH ENCRYPTED PASSWORD 'municipi';
postgres=# grant all privileges on database municipis to municipi;
postgres=# \q
$ psql -h localhost -U municipi municipis
municipis=>

Ara ja podem arrencar l'aplicació Spoon, que és el punt de partida per començar a treballar amb PDI-Kettle. 

sh spoon.sh

Configuració: creació d'un nou catàleg i connexió a la bd:

  • Nuevo Catálogo
  • Nueva Conexión a base de datos: selecciones Connection Type: PostgreSQL, i omples la resta de camps. Comproves que la connexió sigui correcta.
  • Nom del catàleg: municipis

He creat el catàleg municipis de forma correcta, però ara necessito un login i un password. 

User [joan] could not be loaded from repository: []
The user [joan] could not be found.

Solució: Login: admin; password: admin

Documentació:

The repository details are loaded from the file repositories.xml in the local directory or in the Kettle directory:

Note: The complete path and file name associated with repositories.xml file is displayed on the Spoon console. 

INFO  28-04 10:48:35,941 - Spoon - Buscando catálogo
INFO  28-04 10:48:35,975 - RepositoriesMeta - Reading repositories XML file: /home/joan/.kettle/repositories.xml

Note: The default password for the administrator (Login: admin) is admin. Pentaho strongly recommends that you change this default password using the Repository Explorer or the Repository/Edit User menu.

Font/origen de la informació

Anem a buscar la llista de municipis de l'estat espanyol a la web de l'Institut Nacional d'Estadística (www.ine.es).

Localitzo el fitxer pobmun09.xls (Tamaño: 993 Kb.): Població de municipis i províncies per sexe

Les taules que crearé al PostgreSQL són: 

COMUNITAT (cod_com,comunitat, abr_com)
PROVINCIA (cod_prov, provincia, com_com)
MUNICIPI (com_mun, municipi, cod_prov, numhab_h, numhab_d)

L'objectiu és fer un cub (cens), amb les dimensions municipi i sexe. Aquestes dues dimensions són agrupaments (?): municipi (comunitat, provincia, municipi i sexe (total, sexe). De manera que l'objectiu és aconseguir visualitzar en una taula JPivot aquestes dues dimensions i poder veure els habitants, desglossats o no per sexe, per als diferents municipis o agrupacions de municipis (províncies o comunitats).

script per crear les taules: 

DROP TABLE MUNICIPI;
DROP TABLE PROVINCIA;
DROP TABLE COMUNITAT;

CREATE TABLE COMUNITAT(
cod_com INTEGER PRIMARY KEY,
comunitat VARCHAR(80),
abr_com VARCHAR(4)
);

CREATE TABLE PROVINCIA(
cod_prov INTEGER PRIMARY KEY,
provincia VARCHAR(30) NOT NULL,
cod_com INTEGER
);

CREATE TABLE MUNICIPI(
cod_mun INTEGER PRIMARY KEY,
municipi VARCHAR(120) NOT NULL,
cod_prov INTEGER,
numhab_h INTEGER,
numhab_d INTEGER
);

create view municipis2 as 
select cod_mun,municipi,provincia,comunitat,numhab_h,numhab_d
from municipi m, provincia p, comunitat c
where m.cod_prov=p.cod_prov and p.cod_com=c.cod_com;

COMMIT;

Nota: originalment s'havia creat la clau forània que relacionava la província amb la seva comunitat, i el municipi amb la seva provínica, però s'ha hagut de treure doncs donava problemes al fer la transformació amb spoon (es pot afegir les claus forànies a posteriori). 

CREATE TABLE PROVINCIA(
...
cod_com INTEGER REFERENCES COMUNITAT
...

CREATE TABLE MUNICIPI(
...
cod_prov INTEGER REFERENCES PROVINCIA,
...

fitxer comunitats.csv: 

cod_com;comunitat;abr_com
1;Catalunya;CAT
2;Aragón;ARG
3;Comunidad Valenciana;VAL
4;Illes Balears;IB
5;Navarra;NAV
6;La Rioja;RIO
7;Euskadi;EUZ
8;Cantabria;CAN
9;Asturias;AST
10;Galicia;GAL
11;Castilla León;CL
12;Madrid;MAD
13;Extremadura;EXT
14;Castilla la Mancha;CM
15;Murcia;MUR
16;Andalucía;AND
17;Islas Canarias;IC
18;Ceuta;CEU
19;Melilla;MEL

fitxer provincies.csv: 

cod_prov;provincia;cod_com
1;Álava;7
2;Albacete;14
3;Alacant;3
4;Almería;16
5;Ávila;11
6;Badajoz;13
7;Balears;4
8;Barcelona;1
9;Burgos;11
10;Cáceres;13
11;Cádiz;16
12;Castelló;3
13;Ciudad Real;14
14;Córdoba;16
15;A Coruña;10
16;Cuenca;14
17;Girona;1
18;Granada;16
19;Guadalajara;14
20;Gipuzkoa;7
21;Huelva;16
22;Huesca;2
23;Jaén;16
24;León;11
25;Lleida;1
26;La Rioja;6
27;Lugo;10
28;Madrid;12
29;Málaga;16
30;Murcia;15
31;Navarra;5
32;Ourense;10
33;Asturias;9
34;Palencia;11
35;Las Palmas;17
36;Pontevedra;10
37;Salamanca;11
38;Santa Cruz de Tenerife;17
39;Cantabria;8
40;Segovia;11
41;Sevilla;16
42;Soria;11
43;Tarragona;1
44;Teruel;2
45;Toledo;14
46;València;3
47;Valladolid;11
48;Bizkaia;7
49;Zamora;11
50;Zaragoza;2
51;Ceuta;18
52;Melilla;19

Les dades dels municipis les agafo del fitxer pobmun09.xls. Aquest fitxer l'hauré de retocar per tal de què sigui parsejable: elimiar totes les files de tipus: Total Álava, etc. És el fitxer pobmun09_parsejable.xls. Són més de 8000 municipis, i la capçalera és: CPRO, PROVINCIA, CMUN, NOMBRE, POB09, VARONES, MUJERES.

ETL amb Spoon (Kettle)

El primer objectiu és transformar amb spoon les dades disponibles i omplir les taules COMUNITAT, PROVINCIA i MUNICIPI de la nostra base de dades relacional. (El segon objectiu serà definir el cub amb Schema Workbench i publicar-lo a un servidor Mondrian).

Volem omplir la taula COMUNITAT a partir de comunitats.csv. Passos a seguir:

  • Fichero > Nueva > Transformación
  • Nuevo Paso > Entrada > CSV File Input
  • Editem (doble click o Editar paso). Cerquem el nostre fitxer: comunitats.csv. Delimitador: ; (punt i coma); Enclosure: res; Header row present: sí (la capçalera ajuda, però no seria necessària). Cliquem sobre Traer campos i Previsualizar per assegurar-nos que el que estem fent és correcte. Li canviem el nom: comunitats csv
  • Nuevo Paso > Salida > Salida Tabla
  • Editem (doble click o Editar paso). Li canviem el nom: taula comunitat. Editem una nova connexió (si no està disponible la que ja havíem fet). Esquema: res; Taula de destí: comunitat. Specify Data Fields: sí; i OK

Ara hem de mapejar el fitxer CSV amb la taula. En aquest cas la transformació és molt fàcil i directa: és camp a camp, que a més estan ordenats i la capçalera del CSV coincideix amb els camps de la taula. Per tant, més fàcil impossible. Normalment les transformacions requeriran una mica més d'esforç.

Com en qualsevol programa nou que has d'aprendre, el millor és llegir la Guia d'Usuari doncs hi ha uns quants procediments que no són obvis. El que cal fer ara és seleccionar els dos Passos (el CSV i la taula), quedaran marcats amb un requadre negre. I amb el botó sobre un d'ells (preferentment la taula), amb el botó dret apareix el menú contextual Nuevo Salto. Apareixerà una fletxa que va d'un pas a un altre, que representa la transformació que hem de realitzar.

Ara tornem a editar la taula comunitats, anem a la pestanya Database fields i cliquem sobre el botó Enter field mapping. Afegim el mapejat, que com hem comentat és obvi.

Guardem la transformació, i ja la podem executar (botó play). Deixem les opcions per defecte. Veurem que l'aplicació treballa una mica i ens fixem amb el resultat final (pestanya Logging): 

2010/04/29 00:03:51 - comunitats - Iniciado despacho de la transformación [comunitats]
2010/04/29 00:03:51 - comunitats - Natural sort of steps executed in 0ms (2 time previous steps calculated)
2010/04/29 00:03:51 - comunitats - Esta transformación se puede repetir con fecha: 2010/04/29 00:03:51
2010/04/29 00:03:51 - taula comunitats.0 - Connected to database [municipis] (commit=1000)
2010/04/29 00:03:51 - comunitats csv.0 - Header row skipped in file '/home/joan/comunitats.csv'
2010/04/29 00:03:51 - comunitats csv.0 - Procesamiento finalizado (I=20, O=0, R=0, W=19, U=0, E=0
2010/04/29 00:03:51 - taula comunitats.0 - Procesamiento finalizado (I=0, O=19, R=19, W=19, U=0, E=0
2010/04/29 00:03:51 - Spoon - La transformación ha finalizado!!

La comprovació definitiva és fent una select a la taula comunitat.

El mateix farem per la taula PROVINCIA i el fitxer provincies.csv

En el cas de la taula MUNICIPI, el procediment és una mica diferent doncs el punt de partida (origen de les dades) és un fitxer Excel. No agafarem el fitxer que ens hem descarregat de www.ine.es (pobmun09.xls) sinó que utilitzarem el fitxer modificat: pobmun09_parsejable.xls. És habitual que l'origen de les dades necessiti algun retoc/modificació per evitar treball posterior. Aquests retocs els hem fet manualment, però amb un bon coneixement de l'eina també s'hagués pogut fer amb Kettle. Els canvis que hem fet són:

  • eliminar totes les files on posi total Àlava..., etc. Aquestes files no representen la font original de la informació.
  • hi ha un problema amb les columnes CPRO i CMUN (són columnes de text). Hem de convertir aquest valor a número. Una possibilitat és crear dues columnes noves, CPRO2 i CMUN2 i aplicar la fórmula =DECIMAL(A2;10) a totes les cel.les.
  • copiar aquestes dues columnes a CPRO3 i CMUN3 de manera que siguin numèriques i que no continguin fórmules.
  • ara bé, si ens fixem, CMUN3 no és una clau primària (es repeteixe). Per tant, creem la columna CMUN4 i l'omplim de valors des de 1 fins a 8112. Això sí que és una clau primària.

Ara els passos són:

  • Nuevo Paso > Entrada > fitxer Excel
  • seleccionem el nostre fitxer (pobmun09_parsejable.xls). No oblidar-se de clicar el botó Añadir
  • A la pestanya Campos podem clicar sobre Obtener campos de cabecera i ens hauria d'aparèixer la nostra capçalera:
CPRO, PROVINCIA, CMUN, NOMBRE, POB09, VARONES, MUJERES, CPRO2, CMUN2

Vull que els accents dels municipis s'exportin bé. En el spoon, dins la pestanya Contenido, ficar 8859-1, perquè aquesta és la codificació original que tenia en el Excel.

S'acaba de la següent manera

  • Nuevo Paso > Salida > Salida Tabla
  • Editem (doble click o Editar paso). Li canviem el nom: taula municipis. Editem una nova connexió (si no està disponible la que ja havíem fet). Esquema: res; Taula de destí: municipi. Specify Data Fields: sí; i OK
  • com abans, fem un salt de l'Excel a la taula, i mapegem els camps de l'Excel amb els camps de la taula (sense equivocar-se).
Table field	Stream field
cod_mun	        CMUN4
cod_prov	CPRO3
municipi	NOMBRE
numhab_h	VARONES
numhab_d	MUJERES

I es realitza la transformació de forma correcta. Efectivament, faig la select i veig els municipis amb els accents correcte.

Ja hem finalitzat el primer objectiu que és omplir un datawarehouse amb eines ETL. Ara sobre aquest datawarehouse haurem de definir un cub i publicar-lo a un servidor Mondrian.

Entrega

Recursos

Moodle

Durarda

2 hores


creat per Joan Quintana Compte, abril 2010

Obtingut de «http://wiki.joanillo.org/index.php?title=DAI-C8-EC:_Eines_ETL_i_Integració_de_Dades._Kettle&oldid=45614»