Details - Data360_DQ+ - Latest

Additional configuration options

Depending on the selected Store Repository Type, additional configuration options will be displayed.

External default data store

1. Select a Layout Type. Choose from:
   • Delimited
   • JSON
   • Parquet
   • ORC
   • Avro
   • Microsoft Excel
   • Line
   • XML
2. Configure the Layout options. The available Layout options depend on the Layout Type that you have selected.

   If you select JSON, Parquet, ORC, Avro or XML, you can use the Transformation Builder to define the data store fields. For more information, see Using the Schema and Transformation Builder.

   Tip: For information on configuring layouts, see Configuring data store layouts.

External database data store

1. Configure the Database Channel properties to allow Data360 DQ+ to connect to your database. You may need to contact the person who manages the database to gather the required information:
   • Database Type - The brand of database you are using. If your type is not listed, select OTHER. For example, to point to a DB2 database select OTHER. Note that from release 2.1.1 onwards, DB2 based data stores cannot be used as data store outputs within an analysis.
   • JDBC Driver Class - The component that helps Data360 DQ+ to communicate with your database. Common drivers for each database type will autofill this parameter when a Database Type is selected.
   • URL - The external location of your database.
     Note: For a Cloudera Hive database, specify the URL in the following form: jdbc:hive2://<hostname>:10000/;principal=hive/<hostname>@INFOGIX.COM;ssl=true;sslTrustStore=$ {igx.runtime.dir} /cm-auto-global_truststore.jks
   • Username and Password - The credentials that you use to access the database.

     For the Enterprise edition of Data360 DQ+, the following configuration options are also available:

     • Authentication Type - If the Database Type is HIVE, you can use choose between Username and Password authentication, and Kerberos authentication.
     • Principal Name - If the Authentication Type is Kerberos, enter the Kerberos Principal Name, for example principle@example.com.
     • Keytab File Name - if the Authentication Type is Kerberos, enter the Keytab file name, prefaced by the path ${igx.runtime.dir}, for example ${igx.runtime.dir}/sagacity.hive.keytab. Ensure that you use distinct names for all keytab files, for example sagacity.hadoop.keytab, sagacity.kafka.keytab, and sagacity.hive.keytab.
     Note: Data360 DQ+ does not support Hive database datastores for output.

     Click Test to test your connection to the database. Testing should always be performed prior to field generation to ensure that communication with the database has been properly established.

2. Configure the SQL Layout properties:
   • SQL - Write an SQL query to pull data from the tables you would like to include in your data store. During field generation, each field pulled from the database becomes a field in the data store. For example, the following statement would allow you to generate ID and CREATETIME fields in your data store:

     SELECT ID, CREATETIME FROM student_Data360 DQ+_data_pull

     The following statement would allow you to create all the fields from student_Data360 DQ+_data_pull in your data store:

     SELECT * FROM student_Data360 DQ+_data_pull

     Tip: As with standard SQL, more advanced statements may also be made. For example, you could use a WHERE condition to pull in specific records for specific fields, or a JOIN to combine fields from multiple tables.

     Using time stamp variables in SQL statements

     The following variables may be used in your SQL:

     Variable name	Timezone applied
     fromTimestamp/toTimestamp	Applies the timezone of the system's web application server to the time value.
     fromTimestampUTC/toTimestampUTC	Applies the UTC timezone to the time value.
     fromTimestampOrgTz/toTimestampOrgTz	Applies the timezone set in the Admin->System screen of the product to the time value.

   • Sample SQL - Optionally, write a statement that will be used when sampling the data store in an analysis. If a Sample SQL statement is not used, analysis sampling will occur according to the analysis settings.

     For example, you could write the following statements:

     SQL: SELECT * FROM EMPLOYEE where ID > 2

     Sample SQL: SELECT * FROM EMPLOYEE where ID > 5

     A data store that used these statements would bring in all records from the EMPLOYEE table that had an ID greater than 2. These records would be available for processing throughout the application. However, analysis sheets that displayed this data store's contents would only display records with IDs greater than 5.

   • Output Table Name - If the database data store is going to be used as a data store output within an analysis, you must specify the name of the table within the external database that the data store is using.
   • Concurrent Reads - You can use this setting to optimize the speed at which data is read from a data store's database, particularly when the database contains a large number of records. For example, if the database contained 1,000,000 records, a normal query would utilize 1 read, to read all 1,000,000 records. If you were to increase the number of Concurrent Reads to 10, however, this would cause the query to use 10 reads in parallel, and each read would handle 100,000 records.

     To experiment with different numbers of Concurrent Reads, select User Defined and choose a number.

     Alternatively, you can let the system make this optimization for you by selecting System Defined.

     Note: If you have set the Concurrent Reads property to System Defined or User Defined, you must order your SQL query by a field that contains unique values for each record in the data set. Note that this only applies to the SQL property and not to the Sample SQL property.
   • Once you have configured your Database Channel and SQL Layout properties, select the Fields tab and use the Generate button to generate fields. Assuming you have successfully connected to the database, generated fields and the records that populate them will be based on the SQL you have written in your layout. See Fields.

External Kafka data store

1. Select a Layout Type. Choose from:
   • JSON
   • Avro
   • Text
2. Configure the Kafka Channel properties to connect Data360 DQ+ to your Kafka channel. You may need to contact the person who manages the database to gather the required information:
   • Servers - Enter a comma-separated list of server addresses. Entries should be formatted as <hostname>:<port>. Note that Data360 DQ+ does not validate server entries.
   • Topic - Enter a comma-separated list of topics.
   • This channel will be used to write to Kafka - Determines whether the channel will write to, or read from the Kafka channel.
   • Additional Connection Properties - Use this panel to define any other connection properties. For example, to specify an SSL connection, you could add a property called "kafka.security.protocol", with the value "SSL".
3. Configure the Layout properties. The available Layout options depend on the Layout Type that you have selected:
   • JSON Layout - Click Build Schema and Transformations in the JSON Layout panel to open the Build Schema and Transformations dialog. Use this dialog to define the JSON or Avro schema for the Kafka data store, and the transform actions and the type and name of the output fields. For more information, see Using the Schema and Transformation Builder.
   • Avro Layout - You can use a Confluent Schema Registry to store and retrieve your Avro schemas, or provide a schema name and namespace.
     • Registry URL - Enter the URL of the Confluent Schema Registry, in the format <hostname>:<port>, for example 192.0.4.0:8081. A value is required for this field.
     • Schema ID - A value is required for this field. The default value is "latest".
     • Topic - Enter a comma-separated list of topics. A value is required for this field.
     • Schema Naming Strategy - Choose a naming strategy from the drop-down list. The default value is topic.name.

     Click Build Schema and Transformations to open the Build Schema and Transformations dialog. Use this dialog to define the Avro schema for the Kafka Data Store, and the transform actions and the type and name of the output fields. For more information, see Using the Schema and Transformation Builder.

     Enter the Schema Name. This field is required if Confluent Schema Registry is not selected.

     Enter the schema namespace. This field is required if Confluent Schema Registry is not selected.

   • Text - There are no applicable settings for a Layout Type of text, and the Layout panel is not displayed.

The following restrictions apply to Kafka data stores:

• Transform and Profile tabs are not applicable to Kafka data stores.
• Field definitions are not editable, and fields cannot be added, deleted, imported, or generated.
• Fields can be tested only if This channel will be used to write to Kafka is not selected.

External Spark Data Source

1. Spark Data Source Channel
   • Click Add, to define the spark parameters required to read the data from database. Below is an example of the parameters used to read the data,
     • Example:
       • Name - read.spark.url
       • Value - jdbc:oracle:thin:@172.17.13.220:1521:CAFE
       • Name - read.spark.driver
       • Value - oracle.jdbc.driver.OracleDriver
       • Name - read.spark.user
       • Value - CAFEUSER
       • Name - read.spark.password
       • Value - Infogix1

       And, if you want to sample the data then you can use below parameters,

       • Name - read.spark.sample
       • Value - 50%
   • This source also supports writing data into database. Below is an example of the parameters used for writing into the database,
     • Example:
       • Name - write.spark.url
       • Value - jdbc:oracle:thin:@10.2.20.120:1521:iadb
       • Name - write.spark.driver
       • Value - oracle.jdbc.driver.OracleDriver
       • Name - write.spark.user
       • Value - IRWHOUSE
       • Name - write.spark.password
       • Value - IRWHOUSE
2. Spark Data Source Layout
   • Spark Format: This will be jdbc if you are fetching the data from external database.
   • Click Add, to define the spark query which will be the database query. Below is an example of parameters that can be used,
     • Example:
       • Name - read.spark.query
       • Value - select * from AFDS_TEST
       • Name - write.spark.dbtable
       • Value - AFDS_TEST
3. Selection Criteria
   • Click Add, Opens SQL Editor that allows you to perform and execute SQL queries.

External S3 data store

1. Configure the S3 Channel properties to point Data360 DQ+ toward the location of the external data.
   • Bucket Name - The S3 bucket name.
   • Folder - The folder in the S3 bucket that contains the external data.
   • Filter Extension - This setting can be used when you have files in the same location that share the same name but have a different file extension. The specified Filter Extension will tell Data360 DQ+ which file to use to create the data store. For example, if you had a folder containing a customerData.txt and a customerData.csv, specifying either .txt or .csv as a Filter Extension would allow you to choose between the two.
   • Marker Extension - This setting can be used to prevent reading of files that should not be read until a Marker is present. It is available for the S3 and HDFS file systems.
   • Regular Expression for File Path - When configuring the external channel, either literal strings that represent the appropriate file path or regular expressions that represent the file path may be used.
   • Region - Choose the appropriate region-specific endpoints to connect with an AWS service.
   • Security Options - Choose relevant security options and fill in the additional information.
     • Internal Account - Select the Internal Account if S3 data is stored in default region.
     • External Account - Select the External Account if S3 data is stored in other regions apart from default region.
       • Access Key - The Access key ID key of the authenticated user that will connect to AWS account.
       • Secret Key – The secret access key of the authenticated user that will connect to AWS account.
     • Assume Role – Returns a set of temporary security credentials that you can use to access AWS resources that you might not normally have access to.
       • Role ARN – Provide The Amazon Resource Name (ARN) of the role to assume.
       • Session Name – Provide the identifier for the assumed role session.
2. Configure the Layout options. The available Layout options depend on the Layout Type that you have selected.
   Tip: For information on configuring layouts, see Configuring data store layouts.

External ADLS data store

1. Configure the ADLS Channel properties to point Data360 DQ+ toward the location of the external data.
   • Path - When configuring the external channel, the strings that represent the appropriate full path may be used, with this syntax: abfss://<file_system>@<storage_account_name>.dfs.core.windows.net/<path>
   • Filter Extension - This setting can be used when you have files in the same location that share the same name but have a different file extension. The specified Filter Extension will tell Data360 DQ+ which file to use to create the data store. For example, if you had a folder containing a customerData.txt and a customerData.csv, specifying either .txt or .csv as a Filter Extension would allow you to choose between the two.
   • Regular Expression for File Path - When configuring the external channel, either literal strings that represent the appropriate file path or regular expressions that represent the file path may be used.
   • Marker Extension - This setting can be used to prevent reading files that should not be read unless a Marker is found.
   Note: File Extension, Regular Expression for File Path, and Marker Extension has been removed in the ADLS Channel for the Delta Lake Layout.
2. Configure the Security Options.
   • Storage Account Name -The name of the ADLS Gen2 storage account.
   • Application ID - The Application (client) ID for the Azure Active Directory application.
   • Service Credential - The client secret.
   • Directory ID - The Directory (tenant) ID for the Azure Active Directory application.

External Delta Lake Data Store

1. Configure the Layout options. The available Layout options depend on the Layout Type that you have selected.
   Tip: For information on configuring layouts, see Configuring data store layouts.
   Note: Layout options, Edit Transformations and Selection Criteria will be shown for the Delta Lake Layout while Create New Definition Version, Log Activity, Ad hoc queries and Cache Query Results will be disabled.

Internal database data store

If you want to associate a data store with a case store, that is, to have it contain detail records that support the header records of the case store, you can populate an internal database data store by pushing data into it via an analysis and then associate it to a case store by adding it to the case store's workflow.

Prior to pushing data to an internal database data store via an analysis, all of the fields that the internal database data store will receive need to be created. If the internal database data store is created on-the-fly within the analysis, these fields will be created automatically. If you want to create the internal database data store ahead of time, you can create all of the required fields by using the Import Fields feature, and importing from the source data store.

When an internal database data store is added to a case store's workflow, it will become "owned" by the case store. Within the data store, this ownership is represented by the addition of the following system fields:

• OwningObjectId - The system generated ID of the data stage that "owns" the data store.
• OwningObjectType - The type of the data stage that "owns" the data store.
• OwningSubObjectUID - The UID of the case record from the case store that owns the detail record in the data store. This UID must be generated and tied to header/detail records in an analysis using the UUID() function. For more information, see Case Stores.
• OwningSubObjectID - Rather than the UID of a case record that owns the detail record in the data store, this field contains the ID of a case record that owns the detail record in the data store. This ID is system generated.
• SysDeleted - A Boolean flag that indicates whether a record has been deleted and can be physically deleted from the system at any time.