Talend Open Studio: How to Start Guide

How to Get Started with Talend Open Studio for Data Integration

Source:

https://www.talend.com/resources/get-started-talend-open-studio-data-integration

Talend Studio: Learn Data Integration Tasks

Joining two data sources with the tMap component in Talend Studio

Creating a Talend Studio project

Creating a Job to join data sources

Data joining using the tMap component

Configuring joins in the tMap component

Configuring filters in a tMap component

Talend Open Studio: Tutorials

Advanced Tutorials:

Configuring Joins in tMap

Adding Condition-Based Filters Using the tMap Component

Using Context Variables

Writing and Reading Data in HDFS

Talend: Add Objects/Components on a Job Page

Add Components

Ref: https://help.talend.com/en-US/discovering-talend-studio/8.0/configuring-a-component

Talend: Configuring a tMap component join model

Source: •https://help.talend.com/en-US/joining-two-data-sources-tmap-talend-studio/8.0/tmap-inner-join

Exporting the results of a tMap component inner join: Rejected data to a new table

Steps: https://help.talend.com/en-US/joining-two-data-sources-tmap-talend-studio/8.0/displaying-results-tmap-joining?id=10

Talend: Configuring filters in a tMap component

Source:

https://help.talend.com/en-US/joining-two-data-sources-tmap-talend-studio/7.3/tmap-filters-year

Installing Talend in MAC

Talend Open Studio for Data Integration.

If you have intel based Mac, then the OSX installer will work. 

Else, you may install a Virtual Machine such as Parallels, then have Windows in it (Parallels may come with Windows), then have Talend under Windows

Another option can be use Linux Versions. 

Download and install 

Download from: https://www.qlik.com/us/trial/talend-data-fabric

(Talend download from https://www.talend.com/products/talend-open-studio/ leads to the above URL) Instructions are also on this site.

You may see which one works for you. You will need Java to install Linux Version on Mac. Make sure, you have the correct version of Java in your system.

Python: MatPlotLib: Plot Data

import pandas as pd
import matplotlib.pyplot as plt
from mpl_toolkits.mplot3d import Axes3D


columns = ["J_ID", "P_ID", "D_ID", "Year", "JOB_TITLE", "Department_Name", "Total Salary", "Commission"]
df = pd.read_csv("datamart.csv", usecols=columns)


# Create a 3D plot
fig = plt.figure(figsize=(20, 15))
ax = fig.add_subplot(111, projection='3d')

# Scatter plot
ax.scatter(df['Year'], df['J_ID'], df['D_ID'], c=df['Total Salary'], cmap='viridis', marker='X')

# Labels
ax.set_xlabel('Year')
ax.set_ylabel('Job Title (Categorized)')
ax.set_zlabel('Departments')

# Title
ax.set_title('3D Plot of Total Salary by Year and Job Title and Department')


for i in range(df.shape[0]):
    if i%7 == 0:
        ax.text(df['Year'][i], df['J_ID'][1], df['D_ID'][i], '(' + str(df['Year'][i]) + ',' + str(df["JOB_TITLE"][i]) + ',' + str(df["Department_Name"][i]) + ',' + str(df['Total Salary'][i]) + ")")

# Show plot
plt.show()


Sample Data:

Dynamic SQL

“Dynamic SQL is the SQL statement that is constructed and executed at runtime based on input parameters passed”

https://www.sqlshack.com/dynamic-sql-in-sql-server/

Example: ChatGPT

DECLARE @sql AS NVARCHAR(MAX)

SET @sql = ‘SELECT * FROM Employees WHERE Department = ”’ + @department + ””

EXEC sp_executesql @sql

Another Example: Oracle

table_name := ‘HR.Employees’;

dyn_sql := ‘Select * from ‘ || table_name || ‘ FETCH FIRST 10 ROWS ONLY ‘;

EXECUTE IMMEDIATE dyn_sql;

DECLARE
    table_name VARCHAR2(100):= '';
    dyn_sql VARCHAR2(100):= '';
BEGIN     
    table_name := 'HR.Employees';
    dyn_sql := 'Select * from ' || table_name || ' FETCH FIRST 10 ROWS ONLY ';
    EXECUTE IMMEDIATE dyn_sql;
END;

A simple dynamic SQL in a stored Procedure (Oracle)

CREATE OR REPLACE PROCEDURE Retrieve_10_ROWS
IS
    dyn_sql VARCHAR2(200);
    table_name VARCHAR2(50);
BEGIN 
 table_name := 'HR.Employees';
 dyn_sql := 'Select * from ' || table_name || ' FETCH FIRST 10 ROWS ONLY ';
 
 EXECUTE IMMEDIATE dyn_sql;
END;

EXEC Retrieve_10_ROWS;

Dynamic/Reference Cursor and Dynamic SQL

CREATE OR REPLACE PROCEDURE Retrieve_10_ROWS_dyn_cur(p_table_to_query VARCHAR2) IS
    dyn_sql VARCHAR2(200);
    JOB_ID VARCHAR2(50);
    JOB_TITLE VARCHAR2(100);    
    TYPE cur_typ IS REF CURSOR;
    c cur_typ;
BEGIN
dyn_sql := 'Select JOB_ID, JOB_TITLE from ' || p_table_to_query || ' FETCH FIRST 10 ROWS ONLY ';
 OPEN c FOR dyn_sql; 
 --USING table_name;
    LOOP
        FETCH c INTO JOB_ID, JOB_TITLE;        
        EXIT WHEN c%NOTFOUND;
        -- process row here
        DBMS_OUTPUT.PUT_LINE(JOB_ID || ' ' || JOB_TITLE);
    END LOOP;
    CLOSE c;
 EXECUTE IMMEDIATE dyn_sql;
END;