 |
 | Create and use Db Connection metadata
|
In the Repository on the left: Right-click on Db Connections. In the menu, click Create connection to open the Database connection wizard.
|
 |
In the Database connection wizard: In the Name field, fill in the name of the connection: demo5mn. Click Next to continue.
|
 |
In the Database Settings area: In the DB Type list, select the type of database you want to connect to. In the Login and Password fields, fill in the login and password used to connect to the database. In the Server field, fill in the name or IP address of the database server. In the Port field, fill in the listening port number of the database server. In the Database field, fill in the name of the database you want to connect to. Click Check to verify the database connection.
|
 |
The Check Connection message box will indicate if the connection is working. Click OK.
|
 |
If the connection has succeeded, click Finish to close the wizard and create the new connection. If the connection has failed, change the database settings and click Check again.
|
 |
In the Repository on the left: The new connection demo5mn will appear under the Metadata > Db Connections node. Right-click on it. In the menu, click Retrieve Schema to open the Schema wizard.
|
 |
In the Schema wizard: In the Select Filter Conditions area, select Use the Name Filter or Use the Sql Filter depending on the type of filter you want to use. In the Select Types area, select the type of item you are looking for:
table, view and/or synonym. In the Set the Name Filter or Set the Sql Filter areas, set the filter you want to use. For this tutorial, leave the default settings. Click Next.
|
 |
In the Select Schema to create area, enter the name of the table you are looking for in the Name Filter field and check the corresponding box in the Name list, or simply check the table in the list. Here, we select the customers and states tables. Click Next.
|
 |
In the Schema area on the left of the wizard, select the table. Once selected, its name displays in the Name field at the top of the wizard. You can modify it. Its structure also displays in the Schema area at the bottom of the wizard. You can modify it via the toolbar below the table. For this tutorial, we don't have to modify the structure. In the Schema area on the left, click the other table. |
 |
The Name field displays the name of the table. The Schema area on the bottom displays its structure. Click Finish.
|
 |
In the Repository on the left: The new schemas are displayed in the Metadata > Db Connections > demo5mn > Table schemas node.
To add the input component, press Ctrl+Shift while you drag and drop the states metadata into the Job Designer above the tMap. The tMysqlInput component is automatically created in the Job Designer.
|
 |
In the Job Designer: Double-click the tMysqlInput to show the corresponding Component view to set its Basic settings. In the Component view: Click [...] next to the Query field to open the SQL Builder wizard.
|
 |
In the SQL Builder wizard: Click the Designer tab at the bottom of the demo5mn(demo5mn).sql view. Uncheck the ID, Capital and MostPopulousCity boxes. Click the running man icon. The result of the query displays in the Result: 1 view at the bottom of the wizard. Click OK.
|
 |
In the Job Designer: To link the components, right-click on tMysqlInput, hold, and drag to the tMap.
|
 |
In the Job Designer: Right-click on the tLogRow. In the menu, click Delete. In the Repository on the left: To add the output component, press Ctrl while you drag and drop the customers metadata in the Job Designer, to where the tLogRow was.
|
 |
In the Job Designer: Double-click on the tMysqlOutput to show the corresponding Component view and define its Basic settings. In the Component view: In the Action on table list, click Drop table if exists and create to replace the old version of the table with the new one.
|
 |
In the Job Designer:
To link the components together, right-click on the tMap, hold, and drag it to the tMysqlOutput. In the tMap_1 Output wizard, name the link between the tMap and the tMysqlOutput: out1. Double-click on the tMap to open the tMap editor. |
 |
 | Set the processing component parameters
|
In the tMap editor:Click the row1 table and click [x] to delete it. In the row2 table, select all the columns except states and drag them to the out1 table.
Now select the states column and drag it to the Postal column of the row3 table to create the join. In the row3 table, select the State column and drag it at the end of the out1 table.
Next
|
 |
In the out1 table, select the row3.State column. Go to the out1 table in the Schema editor area, and set it length to 14 characters.
Click OK |
 |
 | Run the Job
|
In the Job Designer: Press Ctrl+S to save the Job. Click the Run view. Check the Statistics and Traces boxes. Click Run to execute the Job in Statistics and Traces mode. In the Job Designer: The Traces table displays the data being processed. In the Run view: Click Kill to stop the execution of the Job.
|
 |
In the Run view: Uncheck the Traces box. Click Run again.
|
| | |
|
The Job is working! It creates a join between two inputs and populates a database with the aggregated data. Now we want to export the Job.
|
 |
| | |
