DBTimes
Defines fields for the Data Warehouse application that uses dw Etl library. The configuration JSON file can have other fields at the top level in addition to dwEtl object.
Configuration for DW Etl process
If the conditions in the "If" tab are respected, then the conditions in the "Then" tab should be respected. Otherwise, the conditions in the "Else" tab should be respected.
If the conditions in the "If" tab are respected, then the conditions in the "Then" tab should be respected. Otherwise, the conditions in the "Else" tab should be respected.
version of DW ETL configuration
Specific value:"1"
If the value is true, there will be some dataframe related output sent to the standard output from the library causing slowdown in job execution.
The ETL process consists of two parts – ETL dimensions and ETL facts. jobType is used to run either both or one of these parts. The allowed values for this attribute are "LoadDimsFacts", or "LoadDims", or "LoadFacts". The main scenario for running ETL dimensions separate from ETL facts is loading dimensions in parallel by defining separate configurations for one or more dimensions (using isLoadFlag) and running ETL facts after - serially or in parallel.
The flag indicates if the load is initial or subsequent. If the load is initial, then the existing files for dimensions and/or facts are deleted. Which ones are deleted depends on the jobType - for "LoadDimsFacts" both dimensions and fact files are deleted, for "LoadDims" - dimensions are deleted, and for "LoadFacts" - fact files are deleted.
There is one use case when this flag should be set to true for ongoing loads: when dimension or fact has to be recreated every time based on current sources, i.e., when rows for non-existent natural keys deleted and all attributes are Type 1.
The attribute is used to rerun subsequent load for effective dates processed earlier after the timestamp value specified based on the log.
When this attribute is defined, the loader will create a file with summary info for each load. The info includes record counts, description of source and destination, and the action timing.
File type is parquet for load control.
A parquet file path.
All properties whose name matches the following regular expression must respect the following conditions
Property name regular expression:^__
Comment field begins with two underscores. There can be any number of comment fields.
Staging sources. This are sources for loading dimensions and fact tables.
No Additional ItemsStaging source moniker is a unique identifier of a staging source. It is used as view name for respective source in Spark SQL.
A column in a staging source data that is effective date for the current staging source.
There can be only one default effective date across all staging sources. When is set to true it designates effective date in current staging source as a default effective date.
A column in a staging source data that is has load timestamp. Timestamp can be used for incremental processing of dimension in subsequent ETL runs.
An attribute to specify how to derive effective date from effectiveDateColumn. The values can be
"DISTINCT" - only the dates in the stg source are used in the etl,
"WEEKDAYS" - all days between min and max values excluding weekends, or
"ALL" - all days between min and max values
Defines file staging source.
A parquet file path.
All properties whose name matches the following regular expression must respect the following conditions
Property name regular expression:^__
Comment field begins with two underscores. There can be any number of comment fields.
All properties whose name matches the following regular expression must respect the following conditions
Property name regular expression:^__
Comment field begins with two underscores. There can be any number of comment fields.
A section of specifications for attributes common to all dimensions.
Package name is used to dynamically create an instance of a dimension class. All dimension classes described in a configuration must be defined in the same package.
A regular expression pattern for dimension name. Each dimension’s name must match this pattern.
A section to describe a dimension destination file. The file for each dimension will be created and maintained by the ETL library.
File type is parquet for dimension destination.
Defines destination file path for all dimensions. The file name in path must contain three underscores as a placeholder for the dimension name
Must match regular expression:^.*___.*$
If defined, DW ETL will preserve a previous version of the dimension and place it in this path. A file name must contain three underscores as a placeholder for the dimension name
Must match regular expression:^.*___.*$
All properties whose name matches the following regular expression must respect the following conditions
Property name regular expression:^__
Comment field begins with two underscores. There can be any number of comment fields.
All properties whose name matches the following regular expression must respect the following conditions
Property name regular expression:^__
Comment field begins with two underscores. There can be any number of comment fields.
Describes dimensions. Since dimensions can be loaded in parallel via independent ETL runs this array can have some dimensions absent or inactive (i.e., with 'isLoad' flag set to false)
No Additional ItemsName of the dimension. Must match the pattern defined in dimensionNamePattern field.
If set to false the dimension will not be loaded. Can be used to create separate configurations to load specific dimensions or during development to skip the load of already tested dimensions.
Monikers of staging sources used to load this dimension. DW ETL will call a loadDim override for each source. If there are no staging sources loadDimm will be called just once.
No Additional ItemsOther dimensions that are sources for loading this dimension. A dimension that is a source must be loaded prior to loading this dimension.
No Additional ItemsName of the dimension.
Schema lists a subset of columns used to load current dimension (used for optimization to reduce number of columns). If schema is absent all columns can be referenced in loading current dimendion.
No Additional ItemsColumn name
All properties whose name matches the following regular expression must respect the following conditions
Property name regular expression:^__
Comment field begins with two underscores. There can be any number of comment fields.
All properties whose name matches the following regular expression must respect the following conditions
Property name regular expression:^__
Comment field begins with two underscores. There can be any number of comment fields.
Specifications for dimension columns
Must contain a minimum of 1 items
If the conditions in the "If" tab are respected, then the conditions in the "Then" tab should be respected. Otherwise, the conditions in the "Else" tab should be respected.
true
'scdType' is not applicable for a key column
If the conditions in the "If" tab are respected, then the conditions in the "Then" tab should be respected. Otherwise, the conditions in the "Else" tab should be respected.
true
'scdType' is not applicable for a Natural Key column
If the conditions in the "If" tab are respected, then the conditions in the "Then" tab should be respected. Otherwise, the conditions in the "Else" tab should be respected.
true
'isSurrogateKey' and 'unknownValue' are required for a key column
If the conditions in the "If" tab are respected, then the conditions in the "Then" tab should be respected. Otherwise, the conditions in the "Else" tab should be respected.
true
'unknownValue' is required for a natural key column
If the conditions in the "If" tab are respected, then the conditions in the "Then" tab should be respected. Otherwise, the conditions in the "Else" tab should be respected.
true
'colType' value must be Long if the column is Surrogate key as surrogate key is derived from Spark monotonicallyincreasingid
Specific value:"Long"
A name of the dimension column.
Data type of dimension column. colType must be Short, Integer or Long if the column is a dimension key (i.e., "isKey": true)
If true, this column is a dimension key.
isSurrogateKey can only be defined for dimension key column, i.e., when "isKey": true. If true, this column is a surrogate key and its value will be generated using increasing – but not necessarily sequential - distinct values. The data type for a surrogate key must be “Long”.
If isSurrogateKey is false or not defined, the value for the key column must be provided together with the rest of the dimension values.
Unknown value must be the valid value for column data type. Use yyyy-mm-dd format for Date type. If unknown value is not specified it will be set to NULL to any column but the dimension key – the default unknown value for dimension key is 0 (zero).
A flag to indicate that a column is a natural key for the dimension. Multiple columns can be defined as natural key to create a compound natural key.
Slowly changing dimension column modeling technique: 0 - Retain Original Non-Null Value, 1 - Overwrite, 2 - Add New Row, 3 - Retain Latest Non-Null Value. Default - 1
All properties whose name matches the following regular expression must respect the following conditions
Property name regular expression:^__
Comment field begins with two underscores. There can be any number of comment fields.
All properties whose name matches the following regular expression must respect the following conditions
Property name regular expression:^__
Comment field begins with two underscores. There can be any number of comment fields.
A section of specifications for attributes common to all fact tables.
All fact classes described in a configuration must be defined in the same package. Package name is used to dynamically create an instance of a fact class.
A regular expression pattern for fact table name. Each fact table’s name must match this pattern.
A section to describe a fact table destination file. The file for each fact table will be created and maintained by the ETL library.
File type is parquet for fact table destination.
Defines destination file path for all fact tables. For example, "path" : "/dw/data/dm/FactNamePlaceholder.parquet". Text FactNamePlaceholder in path must match the factNamePattern above. Text 'FactNamePlaceholder' will be replaced with actual fact table name which also has to match the pattern.
Must match regular expression:^.*___.*$
If defined, DW ETL will create fact table with natural keys used to set fact table keys in addition to regulat fact table. This file is used mostly in development to insure the correct setting on dimension keys on a fact table and troubleshoot the unknown keys. The help in troubleshooting the unknown keys can make this file useful in production. Creating of this file has relatively low performance cost.
Must match regular expression:^.*___.*$
All properties whose name matches the following regular expression must respect the following conditions
Property name regular expression:^__
Comment field begins with two underscores. There can be any number of comment fields.
All properties whose name matches the following regular expression must respect the following conditions
Property name regular expression:^__
Comment field begins with two underscores. There can be any number of comment fields.
Specifications for fact tables or facts (fact table and fact are used interchangeably). Since facts can be loaded in parallel via independent ETL runs this array can have some facta absent or inactive (i.e., with 'isLoad' flag set to false)
No Additional ItemsThe name of the fact table. The name must match the pattern defined in factNamePattern attribute.
If set to false the dimension will not be loaded. Set it to false during development to skip loading of some fact table. Can also be used in production to create different configurations to load fact tables in parallel.
The following fact processing modes are supported on subsequent ETL runs:
• REPLACE – new fact table replaces the old one. Fact table can have partition columns (one or more), i.e., columns with isForPartition flag set to true. If partition columns are defined the fact table will be replaced and partitioned.
• REPLACEPARTITION – this mode is similar to REPLACE, but requires to have partition columns. In this mode new partitions replace old partitions with the same name, but if new partition does not exist for some existing old partitions – the old partitions will remain.
• MERGE – old fact table rows are merged with new based on the merge key columns. All rows in old fact table with the same merge key are replaced with new ones. The old rows with merge keys that do not exist in the new fact table will remain.
• MERGEPARTITION – this mode is similar to MERGE, only in addition to merge key takes partition column into consideration.
• ADD – new rows are added to existing ones. Fact table can have one or more partition columns. If partition columns are defined the new rows are added to respective partitions.
Monikers of staging sources used to load this fact table. DW ETL will call a loadFact override for each source. If there are no staging sources loadFact will be called just once.
No Additional ItemsOther dimensions that are used to load this fact table. This dimensions must be distingushed from dimentions used to set key. The dimension needs to be listed here is it used to load fact values as opposed to setting keys. Dimensions to set keys are defined in schema.
No Additional ItemsName of the dimension.
Specifications for dimension columns used in loading fact table
No Additional ItemsColumn name
All properties whose name matches the following regular expression must respect the following conditions
Property name regular expression:^__
Comment field begins with two underscores. There can be any number of comment fields.
All properties whose name matches the following regular expression must respect the following conditions
Property name regular expression:^__
Comment field begins with two underscores. There can be any number of comment fields.
Specifications for fact columns.
The fact table is constructed from schema using following rule:
• If "isForPartition" column or columns are defined the fact table will be partioned on this columns using Spark partitioning feature.
• Distinct "foreignKey" values will be used to create fact table foreign keys. If multiple columns are needed to set a foreign key, the schema would have multiple elements with the same value of a foreign key – one for each column needed to asset a key. For example, if there are two columns from the underlying dimension are needed to set a key, the fact table schema will have two elements with the same value of "foreignKey".
• Columns with "isMeasure" attribute set will be added to a fact table as measures
If the conditions in the "If" tab are respected, then the conditions in the "Then" tab should be respected. Otherwise, the conditions in the "Else" tab should be respected.
If this attribute of a column is set to true, there should be three more properties defined: foreignKey, underlyingDim, and underlyingDimCol. The underlyingDimCol is optional.
If the conditions in the "If" tab are respected, then the conditions in the "Then" tab should be respected. Otherwise, the conditions in the "Else" tab should be respected.
true
Name of the column for the result of select in loadFact method. This will be the name of the column for measures (i.e., when "isMeasure": true), but will not be a name of the foreign key column. See "foreignKey" attribute for names of the foreign keys
Type of the column whose name is defined in "colName". A data type of a measure column must be Double, Short, Integer or Long. The type of foreign keys is determined by the type of the respective key on the underlying dimension
A flag to indicate that this column is used for partitioning fact data. For example, for column { "colName" : "SeasonYear", "colType" : "Integer", "isForPartition" : true } the fact directory structure would look like below
FactPlays.parquet
SeasonYear=2013
SeasonYear=2014
SeasonYear=2015
SeasonYear=2016
SeasonYear=2017
To query partitioned Fact tables in, for example Apache Drill, use syntax like this
select * from dfs.fact-file-path/FactPlay.parquet where dir0=’SeasonYear=2016’
A flag to indicate that the column is used for merge of fact data during subsequent loads. All fact table records that have a merge key value that is present in a new fact table will be deleted and replaced with new values
This flag can be set on a column of Date type to designate a column as an effective date for a dimension with scd type 2 columns. The column will be used to set the key of dimension via equation: eff-date-for-type-two-column between dimension-row-start-date and dimension-row-end-date. Only one column in fact table schema can have this attribute set to true
When isForSettingForeignKey is set, this column is used to set a foreign key from a dimension on a fact row. The column is either a part of natural dimension key or some other column in a dimension that is used in a custom logic to set a foreign dimension key.
If this attribute of a column is set to true, there should be three more properties defined: foreignKey, underlyingDim, and underlyingDimCol. The underlyingDimCol is optional.
For example, if some dimension has three natural key columns that comprise a unique compound key, in the schema there would be three columns - one for each natural key – to set one foreign key on the resulting fact table.
If underlyingDimCol is not defined the setForeignKeyOnFactTable method must be overridden to implement custom logic for setting the foreign key on the fact table
The name of the foreign key column on the fact table. If the foreign key is set using multiple fields (compound key) from the underlying dimension, there will be multiple schema elements with the same "foreignKey" value. There will be only one column with that name in the fact table
The name of the dimension with the primary key for the the given "foreignKey"
The name of the column in the underlying dimension that is used to set given "foreignKey". If all underlying dimension columns have isNaturalKey set to true, the default implementation of setForeignKeyOnFactTable will be used, otherwise this method will have to be overridden in the corresponding dimension class.
This flag designates a column as a fact table measure (or fact). The measure column must be one of the numeric types – Float, Short, Integer, or Long.
All properties whose name matches the following regular expression must respect the following conditions
Property name regular expression:^__
Comment field begins with two underscores. There can be any number of comment fields.
All properties whose name matches the following regular expression must respect the following conditions
Property name regular expression:^__
Comment field begins with two underscores. There can be any number of comment fields.
All properties whose name matches the following regular expression must respect the following conditions
Property name regular expression:^__
Comment field begins with two underscores. There can be any number of comment fields.