Centralizing File Delimited metadata - 6.5

Talend Big Data Studio User Guide

EnrichVersion
6.5
EnrichProdName
Talend Big Data
task
Design and Development
EnrichPlatform
Talend Studio

If you often need to read data from and/or write data to delimited files, you may want to centralize their metadata in the Repository for easy reuse. File Delimited metadata can be used to define the properties of tFileInputDelimited, tFileOutputDelimited, and t*OutputBulk components.

Note

The file schema creation is very similar for all types of file connections: Delimited, Positional, Regex, XML, or Ldif.

Unlike the database connection wizard, the [New Delimited File] wizard gathers both file connection and schema definitions in a four-step procedure.

To create a File Delimited connection from scratch, expand Metadata in the Repository tree view, right-click File Delimited and select Create file delimited from the contextual menu to open the file metadata setup wizard.

To centralize a file connection and its schema you have defined in a Job, click the icon in the Basic settings view of the relevant component with its Property Type set to Built-in to open the file metadata setup wizard.

Then define the general properties and file schema in the wizard.

Defining the general properties

  1. In the file metadata setup wizard, fill in the Name field, which is mandatory, and the Purpose and Description fields if you choose to do so. The information you provide in the Description field will appear as a tooltip when you move your mouse pointer over the file connection.

  2. If needed, set the version and status in the Version and Status fields respectively. You can also manage the version and status of a repository item in the [Project Settings] dialog box. For more information, see Version management and Status management respectively.

  3. If needed, click the Select button next to the Path field to select a folder under the File delimited node to hold your newly created file connection. Note that you cannot select a folder if you are editing an existing connection, but you can drag and drop it to a new folder whenever you want.

  4. Click Next when completed with the general properties.

Defining the file path and format

  1. Click the Browse... button to search for the file on the local host or a LAN host.

  2. Select the OS Format the file was created in. This information is used to prefill subsequent step fields. If the list doesn't include the appropriate format, ignore it.

  3. The File viewer gives an instant picture of the file loaded. Check the file consistency, the presence of header and more generally the file structure.

  4. Click Next to proceed to the next step.

Defining the file parsing parameters

On this view, you can refine the various settings of your file so that the file schema can be properly retrieved.

  1. Set the Encoding type,and the Field and Row separators in the File Settings area.

  2. Depending on your file type (csv or delimited), set the Escape and Enclosure characters to be used.

  3. If the file preview shows a header message, exclude the header from the parsing. Set the number of header rows to be skipped. Also, if you know that the file contains footer information, set the number of footer lines to be ignored.

  4. The Limit of Rows allows you to restrict the extend of the file being parsed. If needed, select the Limit check box and set or select the desired number of rows.

  5. In the File Preview panel, view the new settings impact.

  6. Check the Set heading row as column names box to transform the first parsed row as labels for schema columns. Note that the number of header rows to be skipped is then incremented by 1.

  7. Click Refresh on the preview panel for the settings to take effect and view the result on the viewer.

  8. Click Next to proceed to the final step to check and customize the generated file schema.

Checking and customizing the file schema

The last step shows the Delimited File schema generated. You can customize the schema using the toolbar underneath the table.

Make sure the data type in the Type column is correctly defined.

For more information regarding Java data types, including date pattern, see Java API Specification.

Below are the commonly used Talend data types:

  • Object: a generic Talend data type that allows processing data without regard to its content, for example, a data file not otherwise supported can be processed with a tFileInputRaw component by specifying that it has a data type of Object.

  • List: a space-separated list of primitive type elements in an XML Schema definition, defined using the xsd:list element.

  • Dynamic: a data type that can be set for a single column at the end of a schema to allow processing fields as VARCHAR(100) columns named either as 'Column<X>' or, if the input includes a header, from the column names appearing in the header. For more information, see Dynamic schema.

  • Document: a data type that allows processing an entire XML document without regarding to its content.

  1. If the Delimited file which the schema is based on has been changed, use the Guess button to generate again the schema. Note that if you customized the schema, the Guess feature does not retain these changes.

  2. Click Finish. The new schema is displayed under the relevant File Delimited connection node in the Repository tree view.

Now you can drag and drop the file connection or any schema of it from the Repository tree view onto the design workspace as a new component or onto an existing component to reuse the metadata. For further information about how to use the centralized metadata in a Job, see How to use centralized metadata in a Joband How to set a repository schema in a Job.

To modify an existing file connection, right-click it from the Repository tree view, and select Edit file delimited to open the file metadata setup wizard.

To add a new schema to an existing file connection, right-click the connection from the Repository tree view and select Retrieve Schema from the contextual menu.

To edit an existing file schema, right-click the schema from the Repository tree view and select Edit Schema from the contextual menu.