9.5. Import From Flat File Source You can import metadata from your flat file data sources and create the metamodels required to query your data in minutes. Using the steps below you will define your flat file data source, configure your parsing parameters for the flat file, generate a source model containing the standard Teiid flat file procedure and create view tables containing the SQL defining the column data in your flat file. JBoss Data Virtualization supports Flat Files as data sources. Teiid Designer provides an Import wizard designed to assist in creating the metadata models required to access the data in your flat files. As with Designer's JDBC, Salesforce and WSDL importers, the Flat File importer is based on utilizing a specific Data Tools Connection Profile. The results of the importer will include a source model containing the getTextFiles() procedures supported by JBoss Data Virtualization . The importer will also create a new view model containing a view table for your selected flat file source file. Within the view table will be generated SQL transformation containing the getTextFiles() procedure from your source model as well as the column definitions and parameters required for the Teiid TEXTTABLE() function used to query the data file. You can also choose to update an existing view model instead of creating a new view model. The TEXTTABLE function processes character input to produce tabular output. It supports both fixed and delimited file format parsing. The function itself defines what columns it projects. The TEXTTABLE function is implicitly a nested table and may be correlated to preceding FROM clause entries. TEXTTABLE(expression COLUMNS <COLUMN>, ... [DELIMITER char] [(QUOTE|ESCAPE) char] [HEADER [integer]] [SKIP integer]) AS name Teiid Designer will construct the full SQL statement for each view table in the form: SELECT A.Name, A.Sport, A.Position, A.Team, A.City, A.StateCode, A.AnnualSalary FROM (EXEC PlayerDataSource.getTextFiles('PlayerData.txt')) AS f, TEXTTABLE(f.file COLUMNS Name string, Sport string, Position string, Team string, City string, StateCode string, AnnualSalary string HEADER 2 SKIP 3) AS A To import from your flat file source follow the steps below. In Model Explorer , click File > Import action in the toolbar or select a project, folder or model in the tree and click Import... Select the import option Teiid Designer > File Source (Flat) >> Source and View Model and click > . Figure 9.10. Import from Flat File Source Select Flat File Import Mode and then select either Flat file on local file system or Flat file via remote URL and click > . Select existing or connection profile from the drop-down selector or click New... button to launch the New Connection Profile dialog or Edit... to modify or change an existing connection profile prior to selection. Note that the Flat File Source selection list will be populated with only Flat File connection profiles. After selecting a Connection Profile, the file contents of the folder defined in the connection profile will be displayed in the Available Data Files panel. Select the data file you wish to process. The data from this file, along with your custom import options, will be used to construct a view table containing the required SQL transformation for retrieving your data and returning a result set. Note The path to the data file you select must not contain spaces. Lastly enter a unique source model name in the Source Model Definition section at the bottom of the page or select an existing source model using the Browse button. Note the Model Status section which will indicate the validity of the model name, whether the model exists or not and whether the model already contains the getTextFiles() procedure. In this case, the source model nor the procedure will be generated. When finished with this page, click > . Figure 9.11. Data File Source Selection Page On the page enter the JNDI name and click > . The page, titled Flat File Column Format Definition, requires defining the format of your column data in the file. The options are Character delimited and Fixed width . This page contains a preview of the contents of your file to aid in determining the format. The wizard defaults to displaying the first 20 lines, but you can change that value if you wish. When finished with this page, click > . Figure 9.12. Data File Source Selection Page Character Delimited Option - The primary purpose of this importer is to help you create a view table containing the transformation required to query the user defined data file. This page presents a number of options you can use to customize the Generated SQL Statement, shown in the bottom panel, for the character delimited option. Specify header options (Column names in header, header line number and first data line number), Parse selected row and changed character delimiter. If columns names are not defined in a file header or if you wish to modify or create custom columns, you can use the ADD , DELETE , UP , DOWN to manage the column info in your SQL. When finished with this page, click > . Figure 9.13. Flat File Delimited Columns Options Page To aid in determining if your parser settings are correct you can select a data row in your File Contents Preview section and click the Parse Selected Row button. A dialog will be displayed showing the list of columns and the resulting column data. If your column data is not what you expected, you'll need to adjust your settings accordingly. Figure 9.14. Parse Column Data Dialog Fixed Column Width Option - The primary purpose of this importer is to help you create a view table containing the transformation required to query the user defined data file. This page presents a number of options you can use to customize the Generated SQL Statement, shown in the bottom panel, for the fixed column width option. Specify header options such as first data line number and changed character delimiter. If columns names are not defined in a file header or if you wish to modify or create custom columns, you can use the ADD , DELETE , UP , DOWN to manage the column info in your SQL. You can also utilize the cursor position and text length values in the upper left panel to determine what your column widths are in your data file. When finished with this page, click > . Figure 9.15. Flat File Fixed Columns Width Options Page On the View Model Definition page, select the target folder location where your new view model will be created. You can also select an existing model for your new view tables. Note the Model Status section which will indicate the validity of the model name, whether the model exists or not. Lastly, enter a unique, valid view table name. Click Finish to generate your models and finish the wizard. Figure 9.16. View Model Definition Page When your import is finished your source model will be opened in an editor and show a diagram containing the your getTextFiles() procedure. Figure 9.17. Generated Flat File Procedures In addition, the view model will be opened in an editor and will show the generated view tables containing the completed SQL required to access the data in your flat file using the getTextFiles procedure above and the Teiid TEXTTABLE() function. The following figure is an example of a generated view table. Figure 9.18. Generated Flat File View Table

Commands: