ETL and Pentaho Data Integration

Descript the ETL and introduct Pentaho Data Integration Tool(PDI or Kettle)

Transcript

1. ETL Extract  Transform  Load
2. Introduc2on  of  ETL •  ETL  is  used  to  migrate  data  from  one  database  to  another,  to   form  data  marts  and  data  warehouses  and  also  to  convert   database  from  one  format  or  type  to  another
3. Process  of  ETL •  Extract     •  Process  of  reading  data  from  a  database   •  Transform   •  Process  of  conver2ng  the  extracted  data  from  its  previous   form  into  the  form  it  need  to  be   •  By  using  rules  or  lookup  tables  or  by  combing  the  data   with  other  data   •  Load   •  Process  of  wri2ng  the  data  into  the  target  database  
4. Opera2ons  of  Transform •  Selec2ng  only  certain  columns  to  load   •  Transla2ng  coded  values   •  Encoding  free-­‐form  values   •  Sor2ng   •  Joining  data  from  mul2ple  sources   •  Aggrega2on   •  SpliFng  a  column  into  mul2ple  columns   •  Deriving  a  new  calculated  values   •  …    
5. Pentaho  Data  Integra2on •  Pentaho data integration (PDI, also called Kettle) is for ETL processes. •  Download: http://sourceforge.net/projects/ pentaho/files/Data Integration/ •  Two parts of PDI –  Transformation: transformation is the process of ETL –  Job: job is used to run transformation
6. Transforma2on •  Defini2on Transforma)on Descrip)on Value Values  are  part  of  a  row  and  can  contain  any  type  of  data:  strings,   floa2ng  point  numbers,  integers,  dates  or  boolean  values Row A  row  consists  of  0  or  more  values  that  are  processed  together  as  a   single  entry Input  Stream A  stack  of  rows  that  enters  a  step Hop A  graphical  representa2on  of  one  or  more  data  streams  between   two  steps;  a  hop  always  represents  the  output  stream  for  one  step   and  the  input  stream  for  another Note Descrip2ve  text  that  can  be  added  to  a  transforma2on Step Hop Note
7. Main  Components All  components Input Output Transforma2on
8. Job •  Defini2on Transforma)on Descrip)on Job  Entry A  part  of  job  that  performs  a  special  task Hop A  graphical  representa2on  of  one  or  more  data  streams  between   two  steps;  a  hop  always  represents  the  output  stream  for  one  step   and  the  input  stream  for  another Note Descrip2ve  text  that  can  be  added  to  a  job Job  Entry Hop Note
9. Components  of  PDI •  Spoon   –  GUI  tool  to  design  the  ETL  process  transforma2ons.   –  Crea2ng  jobs  which  automate  the  database  update  process   –  Performing  the  typical  data  flow  func2ons  including:  reading,  valida2ng,   refining,  transforming,  wri2ng  data   •  Pan   –  Applica2on  to  run  data  transforma2ons  designed  in  Spoon   •  Kitchen   –  Applica2on  helps  execute  the  jobs  in  a  batch  mode,  usually  using  a   schedule   •  Carte   –  A  web  Server  which  allows  remote  monitoring  of  the  running  PDI  ETL   processes  through  a  web  browser
10. Feature  of  PDI •  Simple  Visual  Designer   •  Graphic  ETL  tool   •  Dynamic  transforma2ons   •  Integrated  debugger  for  tes2ng  and  tuning  job  execu2on  
11. Feature  of  PDI •  Drag  and  Drop  Integra2on   •  Rich  library  of  pre-­‐build  components  to  access   •  Integra2on  with  Zero-­‐Coding  Required   •  Powerful  Administra2on  and  Management   •  Data  Profiling  and  Data  Quality   •  Iden2fy  data  that  fails  to  comply  with  business  rules  and   standards   •  Manager  data  quality  with  partners  such  as  human   interface  
12. Feature  of  PDI •  Support  for  Any  Big  Data  Source