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.

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)
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
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
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
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
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
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!