The largest and most complex phase of any data warehouse project is the Extract-Transform-Load process. This process involves the extraction of data from source systems, transformation of that data so it conforms to standardized values and conventions and loading of the data into the data warehouse. ActiveWarehouse ETL, a subproject of the ActiveWarehouse open source project, will help with the ETL development process.
Note: this documentation covers ActiveWarehouse ETL version 0.9. There is no guarantee that functionality described here will work the same or at all in previous versions.
To install ActiveWarehouse ETL you must first install Ruby and Rubygems. Once you have a functioning Ruby and Rubygems installation you can install by simply running gem install:
ActiveWarehouse ETL depends on FasterCSV, ActiveRecord and ActiveSupport. If these libraries are not already installed they should be installed with ActiveWarehouse ETL.
First create a database.yml in the directory of your ETL control files that contains information on how to connect to the etl_execution database. This database stores meta data about each ETL execution. Here is an example of the YAML file from the example application rails_warehouse in the ActiveWarehouse subversion repository:
Next create the database defined above in your database server. The first time you run the etl script it will execute the necessary migrations to construct the ETL execution environment database.
In addition to the etl_execution connection you will also define all of your connections to operational databases as well as the data warehouse environment in the database.yml file in the directory of your ETL control files. You will then reference these connections by name through your control files using the :target option.
Each ETL process is defined using an ETL control file. The common naming convention for ETL control files in ActiveWarehouse is destination.ctl where destination is the destination table name. For example, if you are loading data into a store dimension called store_dimension then the control file would be called store_dimension.ctl. If you are including the ETL control files as part of your Rails application I recommend putting your etl directory right in the RAILS_ROOT directory.
ETL control files define a pipeline for the ETL process. It is possible to both read from multiple sources and write to multiple destinations. Sometimes it is beneficial to write your ETL process to take advantage of this pipeline concept in order to reduce ETL processing time. This is especially true if you are dealing with source data that contains many records since you may be able to implement the ETL process as a single pass.
Data passes through the ETL pipeline as Hashes. Specifically, each row is a Hash where the key is the name of the field and the value is the current value of the field. All records are initially extracted as text and should be transformed using the type transform or date and time conversions if necessary. In many cases though you may just want to leave the rows as Hashes of symbols mapped to strings.
One of the most important things to keep in mind is that control files are Ruby code, therefore you can use normal Ruby expressions. This can even include importing other libraries as well as your own libraries and support code. So for example, you could put the following at the top of your control file:
This would require the file support.rb in the same directory as the control file.
As of version 0.9, ActiveWarehouse ETL now includes a batch execution system. Batch files end with .ebf which stands for “ETL Batch File”. The batch file contains directives which are applicable to the entire process. Directives are executed in the order in which they appear in the batch file.
The run directive indicates an ETL control file to run. For example, to execute the person_dimension.ctl control file you would include the following in the batch file:
The use_temp_tables directive indicates that temporary tables should be used during ETL processing. If the ETL process fails during ETL execution then the temp tables will survive and the production tables will be untouched.
The temp tables directive will affect the following processors:
It also affects the database source and database destination implementations.
Extraction takes data out of your operational databases and files and brings it into the ETL pipeline for processing. ActiveWarehouse ETL supports three types of extraction methods: flat file, database and in-memory enumeration. When you define a source to extract data from you may provide a variety of options to control aspects of the extraction. Some options are usable across all sources while some are source dependent. The source options which are applicable for all sources are:
The format for the source definition in a control file is:
The configuration argument is always a Hash. The definition argument may be a Hash or an Array, depending on the source type.
Flat file extraction pulls data from a flat text file. AW-ETL comes with a variety of parsers that can be used to parse different text formats. The following parsers are included with ActiveWarehouse ETL:
The definition argument will vary depending on the parser used. For example, the delimited parser expects an Array corresponding to the fields in each row in the order they will appear in the source file, whereas the fixed-width parser expects a hash where each name/value pair has the field name and a Hash of options for that field.
All parsers share the following common configuration elements:
The delimited parser will be used when the configuration Hash contains a :file element and a :parser element with a value of :delimited.
The delimited parser includes the following configuration options:
The definition when using the delimited parser is an Array of field names in the order which they appear in the source file.
For example, consider the following CSV file, called people.csv:
The source definition might look like this:
You could also pass parameters to the parser. Consider if you have a data file with tabs as the separator:
The configuration would look like this:
The fixed-width parser will be used when the configuration Hash contains a :file element and a :parser element with a value of :fixed_width.
The fixed-width parser includes the following configuration options:
The definition when using the fixed-width parser is a Hash. Each element in the Hash maps the field name to a Hash of options. The Hash of options may include the following elements:
Either :start and :length or :start and :end must be specified.
Consider the following source file:
The source would look like this:
Note that the fixed width parser will automatically trim each value.
The SAX parser will be used when the configuration Hash contains a :file element and a :parser element with a value of :sax. While a DOM-based XML parser is included in the ActiveWarehouse ETL library, you should always favor the SAX implementation as it performs and scales in a way that is not possible with the DOM-based XML parser.
Unlike the other parsers, the SAX parser passes its options directly via the definition. The definition takes two elements:
Consider the following XML document:
The source would look like this:
The path parser currently only handles simple expressions, specifically the path to the element or attribute. The syntax is XPath-like but it is not XPath.
The Apache combined log parser will be used when the configuration Hash contains a :file element and a :parser element with a value of :apache_combined_log.
The Apache combined log parser does not include any configuration options.
The definition when using the Apache combined log parser is not required and will be ignored. The Apache combined log parser will always inject the following fields into the ETL pipeline:
Values in the above that are a dash will be converted to nil. The timestamp value will be converted to a Ruby Time instance.
The following elements will be parsed from the :referrer string, if possible, and injected into the ETL pipeline:
In addition, the following elements will be parsed from the :user_agent string if possible and injected into the ETL pipeline:
You can also define your own parser by extending ETL::Parser::Parser. For example:
source :in, { :file => "", :parser => MyParser }, [ :name ]
Database extraction uses the ActiveRecord library to extract data from an operational database. As described in the beginning of this section, the data will be extracted to a local file first prior to being processed through the ETL pipeline.
Database connections always require the following configuration elements:
In addition, the following optional elements may be provided:
Given the following database table:
The source might look like this:
In versions before 0.9, a common pattern is to grab the configuration YAML from ActiveRecord::Base”s configuration Hash and merge that with the other options to be passed to the database source:
As of 0.9 this will no longer work. Rather you should use the :target option to select a named connection that is defined in database.yml as shown in the first example.
The options for :join, :select, :group and :order are useful for tailoring the query to get only the results you need to push through the ETL pipeline:
The enumerable source provides a means of injecting records into the ETL pipeline from any object that mixes in the Ruby Enumerable module. For example:
This feature is often used to inject a default record into the head of the ETL pipeline when populating a dimension. For example, if you had records in your operational database tied to people and if it is possible for the record to have a null reference in it”s person_id foreign key, then you might want an “Unknown” person:
When the read-locally option is specified on the command line during the etl execution the source will bypass querying the actual source and will instead use the last successfully downloaded source cache file in source_data.
The transformation portion of the ETL pipeline converts data in all kinds of ways to help standardize the way the data is recorded in the target system. ActiveWarehouse ETL supports two ways to transform, either by field transforms or by row processors. There are quite a few built in transforms and processors and making your own is a matter of using a block or creating a custom transform class or row processor class.
Field-level transforms are only applied to a single field in each row. Any transform of this type must implement the method transform(name, value, row) where name is the field name, value is the current value of the field and row is the entire row (in case the transformation depends on other fields in the row). The transform must return the value for the field after the transform is complete
Field transforms are defined in one of two ways. For built-in transforms they are defined by name:
The first argument is the field name, the second is the name of the transform and the third is an options Hash.
The second form uses a block or class:
The block takes three arguments, the name, the value and the row. The block must return a single value that is the new, transformed value.
Note that you can transform a field as many times as you want. Also note that the order in which transforms appear is the order in which they will be executed.
For example:
Uses an external decode file to convert from coded value into something that is consistent and understandable without needing to know how coded values are decoded.
For example:
This transform will return the given default value if the value passed in is blank, according to the rules for blank?, otherwise it will just return the original value.
For example:
If the value is either nil or an empty String then the row value would be changed to “No Name”, otherwise the original value would be returned.
This transform converts a date into a formatted String. Options:
For example:
This transform converts a String into a Ruby Date instance.
This transform converts a String into a Ruby Time instance.
Transform a natural key into its foreign key equivalent.
Resolve a field up its hierarchy until a non-null value is found. This transform expects the operational system have the notion of a parent_id field which can be used to walk up the tree.
Configuration options:
For example:
If :parent_id_field is not specified then :parent_id will be used.
Convert a number into its ordinalized form. For example:
If creation_order is 12 then it would be changed to 12th.
Pass the value through a one-way SHA-1 Hash.
For example:
Trim the value using Ruby’s trim method.
For example:
Convert the value to a specific type.
For example:
The supported types are:
:integer or :number converts using to_i:string converts using to_s:float converts using to_s:decimal converts using BigDecimal, the :significant option can be used to specify the number of significant places to keepRow processors are like transforms except they work on an entire row rather than just a single field. Row processors accept the current row as an argument and may return 0 to N number of rows after the transformation. This means that row transforms can be used to filter out rows which should not be kept, modify multiple dependent fields in the row at once, add or remove fields from the row or even take a single row and return many rows (which is used when exploding hierarchies).
Row processors can be applied either directly after the data is read from the source by specifying the processor using after_read or directly before the data is written to the destination by using before_write.
Row processor that checks to see whether or not the row already exists in the destination data store.
Options:
If there are no rows in the target table then this processor will be bypassed. If there are rows then each row will be checked against the target database table. Rows included in the skip list will not be checked.
Note that this transform actually goes to the target database each time through so it will be a significant bottleneck in processing.
As an example, given a product dimension with the attributes Brand and Category:
This would check each row with a query:
Where the_brand and the_category are the brand and category, respectively, for each row.
If you decided that only the brand name is a natural key, then you could do:
Would result in the query:
Row processor that checks to see whether or not the row is unique. This processor stores a cache of compound keys and checks to see if the compound key exists in the cache. If it does then it will not return the row, thus removing duplicates.
Options:
For example:
If the row {:first_name => "John", :last_name => "Smith", :email => "jsmith@foo.com"} appeared twice during the ETL process only the first instance would be written to the destination.
The copy field processor will copy the value of a field to another field.
For example:
There is also a shortcut for this:
Note that the copy shortcut executes as an after_read processor. If you need to copy a field after applying other transforms then you will need to use the :copy_field processor explicitly.
The rename field processor renames the specified field.
For example:
There is also a shortcut for this:
Note that the rename shortcut executes as an after_read processor. If you need to rename a field after applying other transforms then you will need to use the :rename_field processor explicitly.
This processor will convert a Rails-style acts_as_tree hierarchy into a dimensional bridge which is common to data warehouse systems.
Options:
A debugging processor that will print the row.
Generate the next number in a context-sensitive sequence. The context can be specified as a configuration option, thus allowing independently incrementing sequences.
The surrogate key processor is used to generate surrogate keys for dimension records.
This processor requires that the specified fields are not blank, otherwise it will throw away the current row.
The final part of the ETL process is the loading of the data into the destination system. ActiveWarehouse ETL supports two types of destinations: file and database. In almost every case you should use the file destination to write out the processed data and then use a bulk loader via a post processor to load the data.
All destinations have some common options:
A file destination will be used if the destination configuration includes a :file element.
A database destination will be used if the destination configuration includes a :database element. The configuration options are:
Virtual fields can be added to the final output by specifying the :virtual Hash in the destination configuration. For example:
Another example, this time specifying the query for the initial surrogate key:
Slowly changing dimensions require special treatment in a data warehouse. There are 3 types of slowly changing dimensions. Type 1 replaces old values when an update to a record occurs. Type 2 creates a new row and changes the expiration_date for the original record to the date the change occurred. Type 3 slowly changing dimensions has one column for the original value and one column for the current value.
Currently ActiveWarehouse ETL supports type 1 and type 2 slowly changing dimension implementation. Note that type 2 slowly changing dimensions will be relatively slow because they must delete records during execution to avoid duplicates.
Pre-processors are executed before the ETL pipeline begins.
Truncate the target table before the ETL process starts.
Post-processes execute after all of the data has been processed through the ETL pipeline and has been written to the destination(s).
The bulk import post processor uses adapter extensions to load data using native bulk load tools. Currently MySQL, PostgreSQL and MSSQL Server are supported.
The following is an example of executing a bulk import:
The common idiom of merging the database information is also often used here:
The encode processor uses the Ruby Iconv library to convert a file from one encoding to another, line by line.
Here is an example:
Screens provide a means for checking data validity after data has passed through the ETL pipeline and before it is loaded into the production system. Screens can be configured to either report a warning message, raise an error that causes just the current job to terminate, or exit the system completely if the error is fatal.
Testing inside screen blocks is accomplished using the assertion methods available in Test::Unit::Assertions from the Ruby core library.
An example of a screen:
In the example above, two connections are retrieved, one to the operational database and one to the data warehouse. Then a SQL query is executed against each connection and the results are tested for equality. Note that the result from the operational database is incremented by 1, which would be common if you include an “Unknown” person in the person_dimension to handle cases of people missing from the dimension but referenced in a fact record.
The ETL process is controlled by the execution engine. The engine coordinates the execution of the control file and captures metadata about the execution. Some of the metadata in only known during the execution while other metadata is persisted beyond the current ETL execution.
The ETL engine collects and reports some interesting statistics each time it runs. These statistics include:
These statistics are output each time the ETL process is executed and can provide valuable information on the location of bottlenecks in the ETL process.
Here is an example of output from an ETL process:
Read 37705 lines from sources Wrote 37705 lines to destinations Completed upload_fact.ctl in 1 minutes, 35 seconds with 0 errors. Processing average: 424.78251320913 rows/sec) Avg after_reads: 69589.5558703258 rows/sec Avg before_writes: 65219.6208387736 rows/sec Avg transforms: 534.787733802788 rows/sec Avg writes: 10479.4134304288 rows/sec Avg time writing execution records: 0 ETL process complete
Batch and job data is persisted automatically to the ETL execution database.
When you install the ActiveWarehouse ETL Gem the etl script will automatically be installed in your bin path. This script is used to execute ETL control files and has several options for customizing how the control file is executed.
An example of executing an ETL control file:
In order to get proper logging in ActiveSupport you may need to apply the active_support_logger.patch included in the release. This patch has been applied to the ActiveSupport library and will be included in the next release, however this patch is important for backwards compatibility.
Copyright (c) 2006-2007 Anthony Eden Permission is hereby granted, free of charge, to any person obtaining a copy of this software and associated documentation files (the "Software"), to deal in the Software without restriction, including without limitation the rights to use, copy, modify, merge, publish, distribute, sublicense, and/or sell copies of the Software, and to permit persons to whom the Software is furnished to do so, subject to the following conditions: The above copyright notice and this permission notice shall be included in all copies or substantial portions of the Software. THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY, FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT. IN NO EVENT SHALL THE AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER LIABILITY, WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING FROM, OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN THE SOFTWARE.