Different profiling results when running column analyses with the Java and the SQL engines

author
Talend Documentation Team
EnrichVersion
6.4
6.3
6.2
6.1
EnrichProdName
Talend Open Studio for Data Quality
Talend Data Services Platform
Talend MDM Platform
Talend Data Management Platform
Talend Real-Time Big Data Platform
Talend Big Data Platform
Talend Data Fabric
task
Data Quality and Preparation
Data Quality and Preparation > Profiling data
EnrichPlatform
Talend Studio

Different profiling results when running column analyses with the Java and the SQL engines

The profiling results of column analyses that use the Week Frequency and Week Low Frequency indicators may be different between the Java and the SQL engines.
Environment

Talend Open Studio for Data Quality and all platform Studios with data quality.

Description

You may get different results when you run column analysis with Week Frequency and Week Low Frequency indicators using the Java or the SQL engine.

This is due to the fact that the date function may differ between different database systems (DBMS) or even between different installations of the same DBMS system.

Let's take the WEEK(date [,mode]) function of MySQL as an example, for further information check Date and Time Functions . This function returns the week number for date . It takes a two-arguments form that enables you to specify whether:

  • the week starts on Sunday or Monday,
  • the return value should be in the range from 0 to 53 or from 1 to 53.

With the SQL engine, Talend Studio uses the function WEEK(date) . The mode argument is omitted and thus the studio uses the default mode value as set in the DBMS configuration, in general mode=0 , but that depends on your MySQL installation.

However, if you need to change this default behavior, you can create an UDI (User Defined Indicator) where you specify the mode you want to use in the SQL query template.

With the Java engine, Talend Studio uses the parameter Locale.getDefault() to know about the above-listed two arguments and gets the results from the Java API. This means it uses the locale of the studio and not the locale of the DBMS.

This explains why you may get different profiling result between the Java and the SQL engines on (Low) Week Frequency indicator and other date functions.

Related Jira Issues

DOCT-4674