Click Test to make sure your entries are correct. A success message appears. Click OK. Note: If you get an error when testing your connection, ensure that you have provided the correct settings information as described in the table and that the sample database is running.
|Published (Last):||11 December 2010|
|PDF File Size:||3.39 Mb|
|ePub File Size:||7.64 Mb|
|Price:||Free* [*Free Regsitration Required]|
Click Test to make sure your entries are correct. A success message appears. Click OK. Note: If you get an error when testing your connection, ensure that you have provided the correct settings information as described in the table and that the sample database is running. Click OK, to exit the Database Connections window.
Since this table does not exist in the target database, you will need use the software to generate the Data Definition Language DDL to create the table and execute it. In the Table Output window, enable the Truncate Table property.
Click Execute to execute the SQL statement. Click OK to close the Table output window. Save your transformation. First, you will use a Text file input step to read from the source file, then you will use a Stream lookup step to bring the resolved Postal Codes into the stream. Add a new Text File Input step to your transformation. In this step you will retrieve the records from your lookup file. Do not add a hop yet.
Click Browse to locate the source file, Zipssortedbycitystate. Click Add. Examine the file to see how that input file is delimited, what enclosure character is used, and whether or not a header row is present.
In the Content tab, change the Separator character to a comma ,. Make sure the Header option is selected. Under the Fields tab, click Get Fields to retrieve the data from your.
Enter 0 in the field, then click OK. If the Scan Result window displays, click Close to close it. Click Preview rows to make sure your entries are correct. When prompted to enter the preview size, click OK.
Review the information in the window, then click Close. Add a Stream Lookup step to your transformation. To do this, click the Design tab, then expand the Lookup folder and choose Stream Lookup. Create a hop from the Read Postal Codes step to the Stream lookup step. From the Lookup step drop-down box, select Read Postal Codes as the lookup step.
Click Get Lookup Fields. To preview the data: In the canvas, select the Lookup Missing Zips step, then right-click. From the menu that appears, select Preview. Completing Your Transformation After you resolve missing zip code information , the last task is to clean up the field layout on your lookup stream.
Cleaning up makes it so that it matches the format and layout of your other stream going to the Write to Database step. Create a Select values step for renaming fields on the stream, removing unnecessary fields, and more. Add a Select Values step to your transformation by expanding the Transform folder and choosing Select Values.
Double-click the Select Values step to open its properties dialog box. Click Get fields to select to retrieve all fields and begin modifying the stream layout. You must modify your new field to match the form. Click the Meta-Data tab. Click OK to exit the edit properties dialog box. Run Your Transformation Data Integration provides a number of deployment options. Running a Transformation explains these and other options available for execution.
The Run Options window appears. Keep the default Pentaho local option for this exercise. It will use the native Pentaho engine and run the transformation on your local machine. Click Run. The transformation executes. Upon running the transformation, the Execution Results panel opens below the canvas. This tab also indicates whether an error occurred in a transformation step. We did not intentionally put any errors in this tutorial so it should run correctly. But, if a mistake had occurred, steps that caused the transformation to fail would be highlighted in red.
It also allows you to drill deeper to determine where errors occur. Error lines are highlighted in red. This feature works only if you have configured your transformation to log to a database through the Logging tab of the Transformation Settings dialog. For more information on configuring logging or viewing the execution history, see Analyzing Your Transformation Results. The Performance Graph allows you to analyze the performance of steps based on a variety of metrics including how many records were read, written, caused an error, processing speed rows per second and more.
Like the Execution History, this feature requires you to configure your transformation to log to a database through the Logging tab of the Transformation Settings dialog box. The Metrics tab allows you to see a Gantt chart after the transformation or job has run.
This shows you information such as how long it takes to connect to a database, how much time is spent executing a SQL query, or how long it takes to load a transformation. The Preview Data tab displays a preview of the data.
Pentaho Tutorial – Learn Pentaho from Experts
Through this tutorial you will understand Pentaho overview, installation, data sources and queries, transformations, reporting and more. You will learn how to validate data, handle errors, build a data mart and work with Pentaho repositories. Learn Pentaho from Intellipaat Pentaho Course and excel in your career. Read More 9.
If you are on PDI 5. Though ETL tools are most frequently used in data warehouses environments, PDI can also be used for other purposes: Migrating data between applications or databases Exporting data from databases to flat files Loading data massively into databases Data cleansing Integrating applications PDI is easy to use. Every process is created with a graphical tool where you specify what to do without writing code to indicate how to do it; because of this, you could say that PDI is metadata oriented. PDI can be used as a standalone application, or it can be used as part of the larger Pentaho Suite. As an ETL tool, it is the most popular open source tool available. PDI supports a vast array of input and output formats, including text files, data sheets, and commercial and free database engines.
PDI Transformation Tutorial