Kiba ETL v2 - RubyKaigi 2018

Kiba ETL v2 - RubyKaigi 2018

Kiba 2 - Past, present & future of data processing with Ruby.

Kiba ETL (http://www.kiba-etl.org) is a lightweight, generic data processing framework for Ruby, initially released in 2015 & now in v2.

In this talk, I highlight why Kiba was created, how it is used for low-maintenance data preparation and processing in the enterprise, why and how the version 2 (leveraging Ruby's Enumerator) brings a massive improvement in authoring reusable & composable data processing components, and why I'm optimistic about the future of data processing with Ruby.

91eb330fb36d1e03c856574dfb77d2bc?s=128

Thibaut Barrère

May 31, 2018
Tweet

Transcript

  1. ᇌ KIBA ETL Past, present & future of data processing

    with Ruby RubyKaigi 2018 - Thibaut Barrère (France), independent consultant thibaut.barrere@gmail.com / twitter.com/thibaut_barrere
  2. KIBA ETL? lightweight, generic data processing framework for Ruby initially

    released in 2015 & now at v2.
  3. Kiba == Fang (scrunch your data)

  4. EXTRACT TRANSFORM LOAD (see http://thibautbarrere.com)

  5. None
  6. Back in time Why was Kiba ETL created?

  7. 2006 ❤ activewarehouse-etl Thanks Anthony Eden! (now founder at DNSimple)

  8. source :in, { :file => 'extracted/crm-changes.csv', :parser => :delimited, :skip_lines

    => 1 }, [:first_name, :last_name] after_read :ensure_fields_presence, { :fields => fields } transform(:id_partenaire) { |n,v,r| some_computation } destination :out, { :file => output_file, :include_headers => true, :separator => ";" }
  9. transform(:email_provider) do |name, value, row| row.fetch(:email).downcase.split('@').last end transform :email_provider, :default,

    :default_value => 'Unknown' before_write do |r| r[:email_provider] =~ /Hotmail/ ? nil : r end
  10. before_write do |r| position, status = geocode_with_redis_cache(row) row[:geocoding_status] = status

    row[:latitude] = position.latitude row[:longitude] = position.longitude row end
  11. $ etl my_etl_script.etl

  12. Use cases 2006-2012 Extract CSV data from a ($$$) CRM

    Import to MySQL (low-cost Business Intelligence) Geocode & export to "nearby search" Rails app IBAN validation & export to COBOL back-end More details available in RuLu 2012 talk1 1 See Youtube video & SpeakerDeck slides
  13. 2011 Took over maintenance

  14. ETL::Parser::Parser: SaxParser, FixedWidthParser, NokogiriXmlParser, XmlParser, ExcelParser, CsvParser ETL::Processor::Processor: FtpUploaderProcessor, FtpDownloaderProcessor,

    EscapeCsvProcessor, ZipFileProcessor, SftpDownloaderProcessor, SftpUploaderProcessor, ImapattachmentDownloaderProcessor, EncodeProcessor, TruncateProcessor, BulkImportProcessor, Pop3attachmentDownloaderProcessor ETL::Processor::RowProcessor: BlockProcessor, EnsureFieldsPresenceProcessor, DatabaseJoinProcessor, RequireNonBlankProcessor, CopyFieldProcessor, SequenceProcessor, PrintRowProcessor, SurrogateKeyProcessor, FilterRowProcessor, CheckExistProcessor, HierarchyExploderProcessor, CheckUniqueProcessor, RenameProcessor ETL::Transform: StringToDateTimeTransform, DateToStringTransform, HierarchyLookupTransform, SplitFieldsTransform, DecodeTransform, StringToTimeTransform, Sha1Transform, TypeTransform, OrdinalizeTransform, BlockTransform, StringToDateTransform, Md5Transform, CalculationTransform, TrimTransform, ForeignKeyLookupTransform Source: ModelSource, DatabaseSource, FileSource Destination: CsvDestination, ExcelDestination, YamlDestination, DatabaseDestination, InsertUpdateDatabaseDestination, UpdateDatabaseDestination, FileDestination
  15. module ETL #:nodoc: module Parser #:nodoc: # Parses CSV files

    class CsvParser < ETL::Parser::Parser # Initialize the parser # * <tt>source</tt>: The Source object # * <tt>options</tt>: Hash of options for the parser, defaults to an empty hash def initialize(source, options={}) super configure end attr_reader :validate_rows def get_fields_names(file) File.open(file) do |input| fields = CSV.parse(input.readline, options).first new_fields = [] fields.each_with_index do |field,index| # compute the index of occurrence of this specific occurrence of the field (usually, will be 1) occurrence_index = fields[0..index].find_all { |e| e == field }.size number_of_occurrences = fields.find_all { |e| e == field }.size new_field = field + (number_of_occurrences > 1 ? "_#{occurrence_index}" : "") new_fields << Field.new(new_field.to_sym) end return new_fields end end # Returns each row. def each Dir.glob(file).each do |file| ETL::Engine.logger.debug "parsing #{file}" if fields.length == 0 ETL::Engine.logger.debug "no columns specified so reading names from first line of #{file}" @fields = get_fields_names(file) end line = 0 lines_skipped = 0 CSV.foreach(file, options) do |raw_row| if lines_skipped < source.skip_lines ETL::Engine.logger.debug "skipping line" lines_skipped += 1 next end line += 1 row = {} validate_row(raw_row, line, file) if self.validate_rows raw_row.each_with_index do |value, index| f = fields[index] row[f.name] = value end yield row end end end # Get an array of defined fields def fields @fields ||= [] end private def validate_row(row, line, file) ETL::Engine.logger.debug "validating line #{line} in file #{file}" if row.length != fields.length raise_with_info( MismatchError, "The number of columns from the source (#{row.length}) does not match the number of columns in the definition (#{fields.length})", line, file ) end end def configure @validate_rows = if source.configuration.has_key?(:validate_rows) source.configuration[:validate_rows] else true end source.definition.each do |options| case options when Symbol fields << Field.new(options) when Hash fields << Field.new(options[:name]) else raise DefinitionError, "Each field definition must either be a symbol or a hash" end end end class Field #:nodoc: attr_reader :name def initialize(name) @name = name end end end end end
  16. Photo Credit: National Fire Protection Association

  17. 2013 - Maintenance stopped Too many features Yet, features lacking

    flexibility Custom components complicated to implement Costly to maintain (code + CI)
  18. But I still have data processing needs! source transform transform

    transform destination and that row-based DSL syntax is really great for maintenance
  19. 2008 TinyTL (a tiny ETL) Clean-room implementation Minimalistic (113 lines

    total) 2 small production apps
  20. 2015 Kiba ETL v0.5.0 Lightweight. Flexible (write your own components)

    Simple assumptions (PORO) Standalone (no ActiveRecord dependency) Strong focus on documentation & articles Easy to "keep alive" as a maintainer
  21. 5 keywords only pre_process source transform destination post_process

  22. source CSVSource, filename: 'data/extract.csv', csv_options: { col_sep: ';', encoding: 'ISO-8859-1:UTF-8'

    } transform MyLookup, config: { xxx } transform do |row| { siren: row.fetch('SIREN'), libapen: row.fetch('LIBAPEN') } end destination CSVDestination, filename: 'data/output.csv'
  23. Source def initialize(*args) def each (yields N rows)

  24. class CSVSource def initialize(options) @filename = options.fetch(:filename) @csv_options = options.fetch(:csv_options)

    end def each CSV.foreach(@filename, @csv_options) do |row| yield(row) end end end
  25. Transform (as class) def initialize(*args) def process(row) (returns 1 or

    0 row) Transform (as block) transform { |row| xxx } (returns 1 or 0 row)
  26. Destination def initialize(*args) def write(row) (writes 1 row) def close

  27. class CSVDestination def initialize(filename:, csv_options: {}, headers:) @filename = filename

    @csv_options = csv_options @headers = headers end def write(row) @csv ||= CSV.open(filename, 'wb', csv_options) @headers ||= row.keys @headers_written ||= (csv << headers ; true) csv << row.fetch_values(*@headers) end def close @csv&.close end end
  28. 2015-2018 Numerous production uses Almost no core changes to Kiba

    Components easy to author & maintain "Long Term Support" of gem possible as solo dev
  29. Sustainable project evolution

  30. #1 Micro-batches < 50k rows per job Very near realtime

    sync
  31. None
  32. Push mode

  33. Pull mode (with push notify)

  34. Don't shell out from HTTP or Sidekiq ❌ ❌ ❌

    def perform system("kiba my-import-job.etl") end ❌ ❌ ❌
  35. Use the Programmatic API (e.g call from Sidekiq2) class PartnersUploadProcessorWorker

    include Sidekiq::Worker def perform(options) job = Kiba.parse do source CSVSource, filename: options.fetch('filename') transform ... transform ... transform ... destination SQLUpsert, connection: SEQUEL_DB, table: xxx end Kiba.run(job) end end 2 Kiba ETL Wiki: Considerations for running Kiba jobs programmatically
  36. Extract the job declaration module ETL module SyncPartners module_function def

    setup(source_file, sequel_connection, logger) Kiba.parse do source transform transform transform destination end end end end
  37. #2 Multistep batch processing (enterprise data aggregation) N different ERP

    systems (1 per SaaS client) Different extraction methods Different input formats (fields & data) Target: a single, common schema
  38. None
  39. None
  40. Keep things simple (cron + bash) #!/usr/bin/env bash set -e

    bundle exec kiba common/s3_downloader.etl bundle exec kiba client_acme/extractor.etl bundle exec kiba client_acme/transformer.etl bundle exec kiba common/api_uploader.etl bundle exec kiba common/cleaner.etl
  41. None
  42. Amazon S3 = inbox Email from ERP (CSV/XLSX) -> Amazon

    SES -> S3 ERP -> SFTP export -> S3 Capybara -> XLSX download from ERP -> S3
  43. Pre-extraction from S3 to local file system Generic local download

    step (Kiba script) Move from /unprocessed to /processed S3 lifecycle rules to ensure data removal config = { aws_region: ENV.fetch('ACME_CORP_AWS_REGION'), aws_bucket: ENV.fetch('ACME_CORP_AWS_BUCKET'), aws_kms_key_id: ENV.fetch('ACME_CORP_AWS_KMS_KEY_ID') } source ETL::S3::ListObjects, config transform ETL::S3::Downloader
  44. Amazon S3 = message passing between machines (scalability!)

  45. Keep things DRY & enforce re-use Sources, transforms, destinations "Meta-transforms"

    Steps (whole Kiba scripts)
  46. Actual components re-use between pipelines module DSLExtensions module DefaultValue def

    default_value(field, default_value) transform do |row| value = row.fetch(field) row.merge(field => value.blank? ? default_value : value) end end end end extend DSLExtensions::DefaultValue default_value :some_field, 'Unknown'
  47. More actual components re-use between pipelines AddTimestamps DefaultValue EnforceValuesChoice RenameField

    HashLookup RaiseOnBlanks RemapBoolean (more...)
  48. #3 Automation of internal tasks

  49. None
  50. None
  51. None
  52. source ETL::Sources::HTTPPaginatingFetcher, base_url: bank_base_url, headers: { Authorization: bank_auth } assert_equal

    :content_type, 'application/json' transform { |r| r.fetch(:body) } transform { |r| JSON.parse(r) } transform { |r| r.fetch('transactions') } transform Kiba::Common::Transforms::EnumerableExploder assert_equal 'currency', 'EUR' # SNIP destination Kiba::Pro::Destinations::SQLUpsert, database: ENV.fetch('DATABASE_URL'), unique_key: :fit_id, table: :bank_transactions
  53. source ETL::Sources::OFXSource, dir_pattern: "files/*.ofx" transform do |row| { fit_id: row.fit_id,

    memo: row.memo, name: row.name, posted_at: row.posted_at.to_date, amount: row.amount } end destination Kiba::Pro::Destinations::SQLUpsert, database: ENV.fetch('DATABASE_URL'), unique_key: :fit_id, table: :bank_transactions
  54. Source = PDF files

  55. PDF download & transform pre_process do system!("wget -A pdf ...

    https://vat-site.com") end source Kiba::Common::Enumerable, -> { Dir["downloads/*.pdf"] } transform { |r| { filename: r } } transform PDFToText, :filename_key => :filename # SNIP - extraction, verification destination CSVDestination, file: 'vat_rates.csv'
  56. class PDFToText def initialize(filename_key, content_key, pdftotext_args) @filename = filename_key @content

    = content_key @pdftotext_args = pdftotext_args end def process(row) cmd = "pdftotext #{@pdftotext_args} #{row[@filename]} -" output = system_with_output!(cmd) row[@content] = output row end end
  57. #4 Big rewrites & data migrations

  58. None
  59. None
  60. Use MiniTest & Sequel for "data screens" class Screens <

    ETL::BaseTest def test_well_known_record amount = db[:orders].where(id: 12456).amount_with_vat assert_equal 100.27, amount end def test_counts count = db[:orders].count assert_in_delta(230_000, count, 10_000) end def test_states states = db[:orders].distinct.select('state') assert_equal ['complete', 'pending'], states end end
  61. Key takeaways from data migrations Use "Bulk insert" (Kiba Pro,

    Sequel, ...) Use deterministic ids & reset sequence Bypass ActiveRecord validations validate_uniqueness_of :email, unless: -> { @etl_running } Work on a data subset for iteration Measure & optimize continuously Use screens to test specific & global data
  62. Why Kiba v2? mostly a drop-in replacement

  63. class XMLSource def initialize(...) @dir_pattern = dir_pattern end def each

    Dir[@dir_pattern].sort.each do |file| doc = Nokogiri::XML(IO.binread(file)) doc.search('/invoices/invoice').each do |item| yield(item) end end end end
  64. class CSVSource def initialize(...) @dir_pattern = dir_pattern end def each

    Dir[@dir_pattern].sort.each do |file| CSV.foreach(file, csv_options) do |row| yield(row) end end end end
  65. Kiba v2 "StreamingRunner" def transform_stream(stream, t) Enumerator.new do |y| stream.each

    do |input_row| returned_row = t.process(input_row) do |yielded_row| y << yielded_row end y << returned_row if returned_row end end end ❤ Ruby's Enumerator
  66. v2 transforms can yield N rows, not just sources More

    components re-use + more composition class EnumerableExploder def process(row) row.each { |item| yield(item) } nil end end source DirectoryLister, dir_pattern: '*.csv' transform XMLReader transform XMLSearcher, selector: '/invoices/invoice' transform EnumerableExploder
  67. Happy with current data processing toolkit Kiba (core, OSS) Kiba

    Common (extra generic components, OSS) Kiba Pro (fast SQL, S3, profilers...) Tons of gems for pretty much any format
  68. More performance available soon TruffleRuby Ruby 2.6.0 JIT improvements GIL

    & Guilds
  69. Ruby ETL performance trend4 4 https://github.com/thbar/kiba-ruby-benchmarks

  70. Data + Ruby = ❤ Great asset for data enterprise

    glue Efficient code reuse between data pipelines Components can be tested quite easily (PORO) Wealth of libraries to tap into Raw speed (single threaded) good & improving Concurrency story will improve too!