DBTimes
Defines required field for source loader application that uses DW Source Loader library. The application configuration can have other root level fields.
Configuration for Source Loader
Version of a Source Loader json configuration
Specific value:"1"
Specifications for loading files or database tables into staging files. A load action is used to load one database table or one file. A single load action can also be used to load multiple files if their names follow the same pattern with a date being a variable part
No Additional ItemsIf 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 destination file defined with "isVersioned"=true, then the schema must be defined with at least one column as primary key, i.e. "isUniqueKey": true
true
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 incrementalLoad is defined then destination must be versioned, i.e., with "isVersioned"=true
true
A regular expression of file names to process. The files can have effective date as part of the name. In that case the files are processed in a date ascending order.
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.
This attribute must be defined when a user function is used to provide a path of the file to be loaded. The user function must be implemented in Scala object or Java static method. The method must take one parameter of type Option[Config]and returns a String with file path or file content (depending on "methodReturns" value - "FilePath" or "FileData”, e.g.,
def sampleForCsvFileCustomLocation( params: Option[Config] ): String = {
s"/some-path/csv-file.csv"
}
The type of data that the method returns: FilePath - the path of the file, FileData - the string with the file content
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.
Specifies the file being loaded is .csv. This object can optionally specify any valid Spark read .csv file options used for loading csv file.
Example of a configuration with no options,
"csv" : { }
Example of a configuration with some options,
"csv" :
{
"mode" : "PERMISSIVE",
"header" : true,
"encoding" : "ISO-8859-1"
}
Properties for csv file can be any of the options for Spark csv file processing. "schema" array must be defined if inferSchema is not set to true
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.
Defines SQL Server as source DBMS. The fields in sqlServer object can be any valid fields defined by spark specification for jdbc database source – see https://spark.apache.org/docs/latest/sql-data-sources-jdbc.html.
For example,
"url" : "jdbc:sqlserver://my-server\SQLSERVER2019:1433;database=my-db-name;integratedSecurity=true;".
If "numPartitions" is defined then one of the columns in "schema" must have attribute "isPartitionColumn" set to true. The "lowerBound" and "upperBound" are optional – if absent they will be determined by the loader by querying the source table.
"partitionColumn" is excluded from the list of options for jdbc source since this column has to be defined in "schema" with "isPartitionColumn" flag
When load action defines incremental load, the attribute, if used, should have a statement that loads data incrementally based on a watermark column or columns. The watermark column is a column whose previously loaded MAX value is used to determine the new rows for the incremental load.
When load action defines incremental load, the attribute, if used, should have a statement that loads data incrementally based on a watermark column or columns. The watermark column is a column whose previously loaded MAX value is used to determine the new rows for the incremental load.
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.
Defines Oracle as source DBMS. The fields inside sqlServer object can be any valid field defined by spark specification for jdbc database source – see https://spark.apache.org/docs/latest/sql-data-sources-jdbc.html
For example,
"url" : "jdbc:oracle:thin@//host-name:port/service-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.
Properties for dbmsSource mongoDb can be any of the options supported by MongoDb Spark connector in batch mode. Schema must be defined
When load action defines incremental load, the attribute, if used, should have a statement that loads data incrementally based on a watermark column or columns. The watermark column is a column whose previously loaded MAX value is used to determine the new rows for the incremental load.
All properties whose name matches the following regular expression must respect the following conditions
Property name regular expression:^partitioner\.options\.
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.
Incremental load defines loading of newly added or changed data from the source. The incremetal load uses a field designated as watermark column. Watermark column can be any value that grows when the row is inserted or updated, e.g., row update timestamp or in case of SQL Server a ROWVERSION column. The loader determines MAX value for the watermark column in current data and and injects it in to the query to allow selection of data rows that were added or changed since the last load.
Unique keys are needed to delete records from the destination file for keys that are no longer in the source. If unique keys are not defined they will be generated programmatically.
Watermark columns are the columns in the destination file that are used in incremental load. The query to get data incrementally uses the MAX current value of watermark column to get new records with the value of the watermark column greater that the one in the destination file already. Delimiter designates a column in the query as a watermark column. For example, with the delimiter as three colons, the query for incremental load may look like this: SELECT OrderNumber, OrderTime From Orders WHERE OrderTime > :::OrderTime:::
Must match regular expression:^[:@#!-]+$
Must be at most 5 characters 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.
A name of the load action to differentiate multiple load actions. The name of a load action must be unique within the configuration.
If the value of isActive is false, the load action will not be processed.
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.
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 directory with the load control file.
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.
When the value of isVersioned is true, the loader will maintain versioned destination file. The new version of the row is inserted based on unique key when any of the field’s values change. The unique key can be a single column or comprised of multiple columns (compound key). The granularity of the versioning is a calendar day defined by effective date. If a source has a date field that is designated as effective date then its value will be used to create versions. In the absence of such a field, the loader will use the current date.
When the data source has a value of isVersioned set to false, the loaded will maintain non-versioned destination file. Non-versioned file saves every effective date data in full.
Metadata columns are the columns with reserved names that serve special functions. If the name of the metadata column collides with data column, use metadata columns prefix to differentiate the names.
Must match regular expression:^[A-Za-z_][A-Za-z0-9_]*$
Defines parquet as a type of destination file
Full path of the destination file.
If defined the Source Loader will save a copy of a current file to this name. This can be useful to go back to previous version of the file if needed.
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.
Shema defines the structure of the destination file. Schema can be absent for DBMS source. It can also be absent for file source if “inferSchema” option is set to true.
If fileDestination.isVersioned set to true the schema must have one or more columns with isUniqueKey attribute set to true.
No Additional ItemsIf 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
If "isPartitionColumn" attribute of a column is set to true, colType must be defined (it is needed to determine the type of upper and lower bounds).
Column name
Column data type
Designates column as a unique key. This column attribute cannot be derived from the DBMS, so to designate a column as a unique key the column has to be included in schema.
Multiple columns can be designated as unique key to create a composite unique key.
Unique key is used for incremental load from dbms sources or for creating a versioned destination file.
For DBMS source one column can be defined as partition column to read data on multiple connections in parallel. isPartitionColumn must be defined if loadAction.dbmsSource.numberOfPartitions is defined.
This field is used for versioned destination file. When this flag is set to true then even if the field changed in the source it will not trigger the creation of a new version of the row.
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 that are used for post processing current staging source. These sources will be preloaded and made available in the post process method as views
No Additional ItemsStaging source moniker is a unique identifier of a staging source. It is used as a view name in Spark SQL.
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.
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.