CHAPTER 8

Managing routines ...................................253

8.1 What are routines ..............................................254

8.2 Accessing the System Routines ........................254

8.3 Customizing the system routines .....................255

8.4 Managing user routines ....................................256

8.4.1 How to create user routines ........................257

8.4.2 How to edit user routines ............................259

8.4.3 How to edit user routine libraries ...............259

8.5 Calling a routine from a Job ............................260

8.6 Use case: Creating a file for the current date .261B.2.2 Step 2: Mapping and transformations ........292B.2.3 Step 3: Reference file definition, re-mapping, inner join mode selection ...........................................294B.2.4 Step 4: Output to a MySQL table ...............296APPENDIX C SQL template writing rules .....................299C.1 SQL statements .................................................300C.2 Comment lines ...................................................300C.3 The <%...%> syntax ......................................300

C.4 The <%=...%> syntax ...................................301

C.5 The </.../> syntax ......................................301

C.6 Code to access the component schema elements ..

302

C.7 Code to access the component matrix properties

302CHAPTER 9 Using SQL templates ...............................2639.1 What is ELT ......................................................264

9.2 Introducing Talend SQL templates .................264

9.3 Managing Talend SQL templates ....................265

9.3.1 Types of system SQL templates .................265

9.3.2 How to access a system SQL template .......266

9.3.3 How to create user-defined SQL templates 268

9.3.4 A use case of system SQL Templates ........269APPENDIX D System routines .........................................305D.1 Numeric Routines .............................................306

D.1.1 How to create a Sequence ..........................306

D.1.2 How to convert an Implied Decimal ..........306

D.2 Relational Routines ...........................................307

D.3 StringHandling Routines ..................................307

D.3.1 How to store a string in alphabetical order 309

D.3.2 How to check whether a string is alphabetical .

309

D.3.3 How to replace an element in a string ........309

D.3.4 How to check the position of a specific charac-

ter or substring, within a string ..................................309

D.3.5 How to calculate the length of a string ......310

D.3.6 How to delete blank characters ..................310

D.4 TalendDataGenerator Routines ......................310

D.4.1 How to generate fictitious data ..................311

D.5 TalendDate Routines ........................................312

D.5.1 How to format a date ..................................313

D.5.2 How to check a Date ..................................313

D.5.3 How to compare Dates ...............................314

D.5.4 How to configure a date .............................314

D.5.5 How to parse a Date ...................................314

D.5.6 How to format the Current Date ................315

D.6 TalendString Routines ......................................315

D.6.1 How to format an XML string ...................316

D.6.2 How to trim a string ...................................316

D.6.3 How to remove accents from a string ........316APPENDIX A Talend Open Studio GUI ........................275A.1 Main window ....................................................276A.2 Menu bar and Toolbar .....................................277A.2.1 Menu bar of Talend Open Studio ............277A.2.2 Toolbar of Talend Open Studio ...............279A.3 Repository tree view .........................................279A.4 Design workspace ..........................................281A.5 Palette ................................................................281A.6 Configuration tabs ...........................................282A.7 Outline and code summary panel ..................284A.8 Shortcuts and aliases ........................................284APPENDIX B Theory into practice: Job example .........287B.1 Introducing the scenario ..................................288B.1.1 Input data ...................................................288B.1.2 Output data .................................................288B.1.3 Reference data ...........................................289B.2 Translating the scenario into a Job .................289B.2.1 Step 1: Job creation, input definition, file read-

ing ..............................................................................289

viiiTalend Open Studio