Talend Open Studio Cookbook by Rick Barton

 

Packt Publishing hat recently published a new Book: the "Talend Open Studio Cookbook" by Rick Barton.

As Data Warehouse developer and engineer, I've been extensively used Talend for many years. Apart from the basic tutorials provided by Talend, however, my only source for learning has always been the proactive Talend online community.

This book doesn't digress too much in theory and provides a full, comprehensive view on many every-day, concrete situations and their corresponding solving patterns. The learning-by-doing "recipe" approach followed by the book makes it easier to read and understand it. The book also provides a good foundation of XML principles; it lacks, however, a chapter illustrating the development of custom components, a scenario that is more likely to occur than expected.

Prerequisites for the book are a basic knowledge of Java or any c-like object-oriented programming language, as well as a rudimentary understanding of relational concepts.

I highly recommend this book for both IT experts and novices with a focus on system and data integration.

How to Determine if Two Date Ranges Overlap in SQL

By the many possible configurations two time intervals can have with respect to each other, the most common is when two or more date ranges overlap. How to express it in relational terms?

Suppose we have a reference time range X. X will be in SQL represented by a pair of date columns or variables (how else?): @pr_DateFrom and @pr_DateTo.

We are now interested in determining whether the date ranges contained in one table, tb_DateRanges, are overlapping with our reference date range. tb_DateRanges has two column, DateFrom and DateTo. Moreover, if only one of those ranges are overlapping, our query should return a positive result. In SQL, the solution to this problem can be easly determined by an aggregating query.

The reference date range (black), the overlapping date ranges (green) and the non-overlapping date ranges (red).

We begin by expressing the opposite condition, i.e. if date range A does NOT overlap with reference range X. This happens when:
   

tb_DateRanges.DateTo <= @pr_DateFrom

OR

tb_DateRanges.DateFrom >= @pr_DateTo

Now, according to deMorgan's law:
   

not(A OR B)

is equivalent to  

not(A) AND not(B)

which means:

NOT(tb_DateRanges.DateTo <= @pr_DateFrom)

AND

NOT(tb_DateRanges.DateFrom >= @pr_DateTo)

   

By cross-applying this condition for every row of tb_DateRanges, we obtain a sequence of "true" (1) and "false" (0) values. By aggregating this raw result with the MAX() SQL function, we simulate an "OR" condition within the values.
   
This solution admits configurations where the edges overlap exactly. If you wish to exclude that, you may change the non-equijoins operators to ">= "to ">", and "<=" to "<", and viceversa.

A similar pattern can be used for spatial problems. For istance, for three-dimensional objects the same logic can be applied by listing the relation for each coordinate separately.

How to Split a Comma Separated Values Column into Multiple Rows

In many cases a SQL Developer may face a situation in which different values are "wrapped" into the same column. Even though this violates the First Normal Form, it is not uncommon.

Depending on your design considerations, you may want to "unwrap" the column, generating a new table with multiple rows with the same ID -each for every corresponding "splitted" value-, or simply use an additional reference table. The first solution fits well with the fact table of a dimensional data warehouse, while the second is more suitable for a normalized environment, such as a traditional information system.

Here is my definitely not elegant but working solution for a DWH scenario. First of all, you need a splitting table-valued function:

create function [dbo].[ft_Split]
(
    @pr_RowData nvarchar(2000),
    @pr_SplitOn nvarchar(5)
) 
returns @vr_Rtn table
(
    Id int identity(1,1),
    Data nvarchar(max)
)
as
begin
    Declare @vr_Cnt int
    Set @vr_Cnt = 1

    while (Charindex(@pr_SplitOn, @pr_RowData); 0)
    begin
        Insert Into @vr_Rtn (data)
        Select
            Data = ltrim(rtrim(substring(@pr_RowData, 1, charindex(@pr_SplitOn, @pr_RowData)-1)))

        Set @pr_RowData = substring(@pr_RowData, charindex(@pr_SplitOn, @pr_RowData)+1, len(@pr_RowData))
        Set @vr_Cnt = @vr_Cnt + 1
    end
   
    insert Into @vr_Rtn (data)
    Select Data = nullif(ltrim(rtrim(@pr_RowData)), N'')

    Return
end

Now you can write a query with the CROSS APPLY clause, which works very much like a traditional join:

select
    s.ID
    , virtualSplit.data  as SplittedValue

    ...
from
    dbo.tb_CrazyWrappedTable as s
    cross apply dbo.ft_Split(s.CSVWrappedColumn, N',') as virtualSplit

The CROSS APPLY operator was introduced in SQL Server 2005 and in our scenario returns the original table (CrazyWrappedTable) rows matching with the virtual table (virtualSplit) generated by ft_Split.

As you might have guessed, the original table expression is processed first; the right table expression is then evaluated against each row of the left table expression. The final result-set contains all the selected columns from the left table expression combined with all the corresponding columns of the right table expression - and this means the original source table ID will be duplicated! Queries against your resulting fact table should therefore use the COUNT DISTINCT aggregate function - slow and disgraceful, but logically consistent.

To reduce performance issues or simply boost your read-only queries, you should build an index-view upon the original table, based on the two previous code fragments.

MSSQL List of Table's Referencing Objects

Similarly as we did in our Oracle past post, we now propose a simple but useful MSSQL query, that let us to know which objects (for example, stored procedures) reference a specified table (or any other object):


SELECT DISTINCT
   referenced_schema = d.referenced_schema_name
   , referenced_object_name = d.referenced_entity_name
   , referenced_object_type = o1.type_desc
   , referring_object_schema = s.name
   , referring_object_name = o.name
   , referring_object_type = o.type_desc
FROM
   sys.sql_expression_dependencies d
   INNER JOIN sys.objects o ON d.referencing_id=o.object_id
   INNER JOIN sys.schemas s ON o.schema_id=s.schema_id
   INNER JOIN sys.objects o1 ON d.referenced_id=o1.object_id
WHERE
   --d.referenced_entity_name = 'table_name'
   d.referenced_entity_name like '%object_name%'
ORDER BY
       referenced_schema_name
;



But how can MSSQL obtain such a list of referenced table objects, contained inside the code of a stored procedure? Well, everytime you CREATE or ALTER a stored procedure, the compiler dynamically creates a list of syntatic objects, internally implemented as hash table; this list contains also all the code's referenced table, and it´s therefore used to keep the system tables and metadata updated.

LIKE is not always enough

Let´s imagine a situation in which we have, as input, a column s.namelist from a table s containing a comma-separated list of values:

Fabio,Andrea,John,Sara,Sarah...

This could be, for example, a dump from a CSV file or an Oracle External Table, inside a database staging area.

Now suppose we have to perform a filtering operation basing on the corresponding value of another column, s.name; in particular, we would like to identify -and insulate- all the names from our comma-list, whose name is the same in our corresponding s.name column. So, if we have a row like:

  ... | Smith | ... | Sara, Karl, John, Smith, Bill, Smith, Hoppen,... | ....

we wanna obtain:

 | Smith | Smith, Smith |

or just the entire name list.

(this doesn´t seem having much sense, but a similar situation could easly occour in many DWH or data integration scenarios).

Starting with a simple query statement (in this case T-SQL under MSSQL) like this:

select

       s.name

       , a.namelist  LIKE ( '%|' + UPPER(RTRIM(LTRIM(b.name)))

  +'|%' ) )

Doesn´t work correctly. What happen if we have two names like "Sarah" and "Sara", while filtering for "Sara"? With the LIKE operator, we would catch also "Sarah".

The solution is to clearly break and delimiatet the comma list names before performing any additional filtering/denormalization operation, through the use of the REPLACE operator

select

       s.name

       , '|' + REPLACE(UPPER(LTRIM(RTRIM(a.namelist))),' ', '|') + '|'  LIKE ( '%|' + UPPER(RTRIM(LTRIM(b.name)))

  +'|%' ) )

We will then obtain a clear, delimited list of names as |Sara|Sarah|Pippo|... in which we can easly apply the LIKE operator with the name explicitely delimited by the "|" as input

The RTRIM/LTRIM, and the UPPER operators will help us on avoiiding the classical data quality problems involving fields coming from source systems as names, addresses, etc...

Oracle APEX Login Troubles

Oracle Application Express is a web-based, scalable development framework based on the Oracle Database, available from the Oracle 9.2. Oracle APEX follows a thin-client logic, demanding most of the processing and validation operations to the Oracle DBMS itself.

Oracle APEX is also available in the free XE version of the Oracle Database.
   
While providing an Oracle APEX solution to a small set of users, it's of course a good idea to deploy it in the "Embedded PL/SQL Gateway" way, simply following step by step the guidlines as specified in the Oracle official documentation.

In this way however, after a successful installation and while logging into your new APEX system, you could face the presence of the a dialog box asking a password for the "XDB" user:

The server xxx at XDB requires a username and password.

   
followed by the same request, but for the "APEX" user:

The server xxxxx at APEX requires username and password

This is why the Oracle XDB HTTP Server is by default configured to use the same TCP port of the APEX Listener, the 8080. If you do not want to use a different port or disable the XDB HTTP Server completely, here is a simple workaround.

First of all, we unlock the XDB user:

ALTER USER xdb ACCOUNT UNLOCK;

...and we set a new password:

ALTER USER xdb IDENTIFIED BY xdb_new_pwd;

We make the same for the ANONYMOUS user (as described by the Oracle documentation) and for the APEX_PUBLIC_USER.

Oracle Automatic Startup at Boot Time - Linux Script

If you are frequently working with the Oracle technology, in most of the cases your Oracle system will run over a Linux machine, for example based on a Red Hat compatible distribution (Red Hat Enterprise, Fedora, or CentOS).

In fact, if your purpose is to test, develop or evaluate a solution based on the Oracle stack, you can freely rely on a virtual machine based on the free Oracle Virtualbox (in case, do NOT forget to install the VirtualBox Guest Additions!), the CentOS distribution, and the Oracle Database Enterprise Edition (or Standard, or even XE - it depending on your purposes).

Unfortunately, by default both Oracle Enterprise and Standard Edition don' t set any startup script in the /etc/init.d/ directory at installation time. No panic: we can create a custom one and manually deploy it.


First of all, we check our /etc/oratab file, whose row follows the following syntax:

istance_sid:oracle_home:[Y|N]

The "oratab" file is automatically created by the Oracle installer, and it's used by the "dbstart" and "dbshut" scripts to figure out which database istances have to be start up or shut down. In particular:

• istance_sid: System ID (SID) of the desired oracle instance;
• oracle_home: ORACLE_HOME directory associated to the specified istance;
• [Y|N] simply indicates if the istance should automatically start at boot time (Y="yes", N="no").

Obvioulsy, we set as "Y" every database instance we wanna automatically to be started at boot time.

For example, a production /etc/oratab file could look like something like this:

orcl:/opt/oracle/product/11.2.0/dbhome_1:N
dev:/opt/oracle/product/11.2.0/dbhome_1:Y
test:/opt/oracle/product/11.2.0/dbhome_1:Y
prod:/opt/oracle/product/11.2.0/dbhome_1:Y

Now we can create our new init script. We create a new, empty file called "oradb" and we add the following lines:

#!/bin/sh
# chkconfig: 345 20 80
# description: counter daemon
# processname: counter
# /etc/rc.d/init.d/oracle
# Description: Automatically starts and stops the Oracle database and the listeners.

Note: the line "# chkconfig: 345 20 80" is absolutely necessary to make the script compatible with the Red Hat service management subsystem and should not be skipped!

The following code implements the "body" of our init script:

case "$1" in
  start)
        echo -n "Starting Oracle Databases: "
        echo "----------------------------------------------------" >> /var/log/oracle
        date +"! %T %a %D : Starting Oracle Databases as part of system up." >> /var/log/oracle
        echo "----------------------------------------------------" >> /var/log/oracle
        su - oracle -c dbstart $ORACLE_HOME >> /var/log/oracle
        echo "...done."
        echo -n "Starting Oracle Listeners: "
        su - oracle -c "lsnrctl start" >> /var/log/oracle
echo "...done."
        echo ""
        echo "----------------------------------------------------" >> /var/log/oracle
        date +"! %T %a %D : Finished." >> /var/log/oracle
        echo "----------------------------------------------------" >> /var/log/oracle
        touch /var/lock/subsys/oracle
        ;;
  stop)
        echo -n "Shutting Down Oracle Listeners: "
        echo "----------------------------------------------------" >> /var/log/oracle
        date +"! %T %a %D : Shutting Down Oracle Databases as part of system down." >> /var/log/oracle
        echo "----------------------------------------------------" >> /var/log/oracle
        su - oracle -c "lsnrctl stop" >> /var/log/oracle
        echo "...done."
        rm -f /var/lock/subsys/oracle
        echo -n "Shutting Down Oracle Databases: "
        su - oracle -c dbshut $ORACLE_HOME >> /var/log/oracle
echo "...done."
        echo ""
        echo "----------------------------------------------------" >> /var/log/oracle
        date +"! %T %a %D : Finished." >> /var/log/oracle
        echo "----------------------------------------------------" >> /var/log/oracle
        ;;
  restart)
        echo -n "Restarting Oracle Databases: "
        echo "---------i-------------------------------------------" >> /var/log/oracle
        date +"! %T %a %D : Restarting Oracle Databases as part of system up." >> /var/log/oracle
        echo "----------------------------------------------------" >> /var/log/oracle
        su - oracle -c dbshut $ORACLE_HOME >> /var/log/oracle
        su - oracle -c dbstart $ORACLE_HOME >> /var/log/oracle
        echo "...done."
echo -n "Restarting Oracle Listeners: "
        su - oracle -c "lsnrctl stop" >> /var/log/oracle
        su - oracle -c "lsnrctl start" >> /var/log/oracle
echo "...done."
        echo ""
        echo "----------------------------------------------------" >> /var/log/oracle
        date +"! %T %a %D : Finished." >> /var/log/oracle
        echo "----------------------------------------------------" >> /var/log/oracle
        touch /var/lock/subsys/oracle
        ;;
  *)
        echo "Usage: oracle {start|stop|restart}"
        exit 1
esac

As you may notice, we make use of the Oracle standard "dbstart" ("dbshut") and "lsnrctl" utilities.

Note: in order to make this script correctly work, the "dbstart", "dbshut" and "lsnrctl" utilites must be present in the PATH environmental variable! You can check it by typing the command:

# env | grep PATH

If the PATH environmental variable is not correctly set up, you can manually do it using:

# PATH:$PATH:oracle_utils_path; export PATH

You can locate the utilities using the command:

# updatebd; locate dbstart

or

# find / -name dbstart

Adding this line to the /etc/profile file will make the correct PATH variable also available at boot time.




Now you must deploy the script in the /etc/init.d/ directory. The script should be own by the "root" user:

# chown root:root /etc/init.d/oradb

and should be granted with the "775" permission:

# chmod 775 /etc/init.d/oradb

Eventually, to make Oracle automatically start at boot time type:

# chkconfig --add oradb

Restart your system, set the ORACLE_SID to your database SID name, and test the script it via SQLPLUS, TOAD, or SQL Developer. You can (as root) also manually call the script:

• # /etc/init.d/oradb start: to make the database istances start;
• # /etc/init.d/oradb stop: to make them stop;
• # /etc/init.d/oradb restart: to make them restart.