nestoria - business overview

UK, Spain, Italy, Germany

• product search engine
• 4 products
• product = houses/flats

nestoria - challenges

• bad data quality
• corrupt data
• missing data
• partners unable to fix bad quality ('first time export')
• no industry format
• daily ops

ETL software

• commercial
• open-source
• build-your-own

A - commercial

Enterprise

• Oracle, IBM, Microsoft, SAS, ...

problem solved

• adapters for XML, CSV/TSV, all RDBMs, SAP...
• logging, SLA tracking...
• job control, proven scaling
• XSLT
• metadata management
• custom business logic (plugins)

IBM DataStage

IBM DataStage

A - commercial

ISBN 1599941988

• documentation
• training
• support
• books

B - Open Source - Kettle

B - Open Source - Kettle

B - Open Source - Sprog

Sprog

• http://sprog.sourceforge.net
• Release 0.14 (27-Jul-2005 )
• Data Munging with Sprog

B - Open Source - Talend Open Studio

• http://www.talend.com
• Java, Eclipse GUI and Perl
• core and base modules on GPL
• active maintained
• training, support, consulting (cost)
• share components with community

B - Open Source - Talend

C - build-your-own

• unit tests
• extra flexible throughout
• job security ;)

• Perl is beautiful!

• seriously, think twice

why we've built our own

source

• startup == no budget
• slow linear scale
  • (500.000 records after 6 months)
• test driven
• complex cleansing and QA
  • 100s of regexp
• inhouse geocoder
• inhouse text-analysis

ETL @ nestoria

• capture
  • Net::FTP, LWP::Simple
• convert
  • XML::Simple, Text::CSV_XS, into our XML format
• serialize
  • Data::Dumper, JSON::XS
• transform
• QA
• batch load
  • DBI::MySQL

convert @ nestoria

• own XML standard format
• converters for other industry formats
  • Rightmove (UK), OpenImmo XML (Germany)
• uncompressing
• merging files
• fetch images

serialize

JSON:XS, Data::Dumper vs XML

• read/write/read/write again
  • e.g. arrays
• more utf8 confidence
  • XML::Simple hides too much from you
• one record per line
  • grep, human readable
• 90% compression rate
• OO wrapper
  • if you want, e.g. Class::Accessor

typical ETL transform tasks

• sort
• sampling
• filter
• ...

• mapping values
  • e.g. 'semi-detached' => 'house'
• convert value
  • math, currency
• lookup
  • static, metadata, database, fuzzy
• merge, derive
  • $cc = $aa . " " . $bb
• aggregation
  • count, sum, min, max, ...

Transform @ nestoria

• fill in missing data (default, avg, guessing)
• translation (Spanish to english)
• geocoding
• NLP (text analysis, keyword extraction)
• classification (e.g. identify flatshares)
• unique ID creation
• thumbnail generation
• filtering/data quality

lessons learned - transform

• prepare for corrupt data
• fix incoming XML

  	<address>119,+M&#xFC;hlenstra&#xDF;e+,+M&#xF6;nchengladbach,+41236</address>
  

• call XML parser record-by-record
• test incoming raw data, e.g. number of columns

free-text data example

• "5.75%"
• "4"
• "0,- Euro"
• "Courtage of 0,1% !!"
• "75%"
• "yes"
• "none"
• "commissionfree"
• "2 monthly rents"
• "36.800.00 EUR fixed (incl. VAT)"

Loading @ Nestoria

• full/segmented/incremental update
• standard MySQL, 3 tables
• staging area
  • data is on hold, searchindex-generation is 10x more complex

monitoring @ nestoria

    $ prove -Ilib -r t/etl/ t/blackbox/etl/
    t/etl/attrhash........................................ok
    t/etl/convert.........................................ok
    t/etl/dropbox.........................................ok
    t/etl/get_summaries_since.............................ok
    t/etl/images..........................................ok
    t/etl/import..........................................ok
    t/etl/object_cache....................................ok
    t/etl/openimmoutils...................................ok
    [...]
    All tests successful.
    Files=56, Tests=1493, 372 wallclock secs (305.88 cusr +  6.26 csys = 312.14 CPU)

Questions?

!! find us in the hallway !!

• mtm -at- lokku.com
• www.nestoria.co.uk/help/feedback
• (yes, we're hiring)