[teiid-designer-issues] [JBoss JIRA] Updated: (TEIIDDES-1017) Create "Metadata from Teiid Metadata File" import option which utilizes DTP Flat File connection profile and targets Teiid formatted source table files

Create "Metadata from Teiid Metadata File" import option which utilizes DTP Flat File connection profile and targets Teiid formatted source table files -------------------------------------------------------------------------------------------------------------------------------------------------------- Key: TEIIDDES-1017 URL: https://issues.jboss.org/browse/TEIIDDES-1017 Project: Teiid Designer Issue Type: Feature Request Components: Import/Export Affects Versions: 7.5 Reporter: Barry LaFond Assignee: Barry LaFond Fix For: 7.5, 7.4.2 Attachments: EmpDataFixedWidth.txt, EmployeeData.txt, ImportTeiidMetadata Wizard.bmml, ImportTeiidMetadata Wizard.png, teiid-designer-import-options.png, teiid-metadata-file-import-configure.png, teiid-metadata-file-import-page-1.png, teiid-metadata-file-import-page-2.png, teiid-metadata-file-import-parse-column.png, teiid-metadata-file-import-view-table.png To improve usability of File connectors, users need the ability to import from a Teiid Source file via the import wizard. 1) Add an new import option > "Metadata from Teiid Metadata File" 2) Page 1 set up similar to the JDBC importer & SF importer with the Connection Profile combo box at top with select & edit Connection profiles buttons >> Would be filtered to be only select & edit Flat File Source connection profiles >> Connection profile points to folder on file system 3) Page 2 would show summary of what Metadata objects are available >> User could pick and choose (check-boxes) what tables, procedures, etc... to use 4) Page 3 maybe the Model Selection page with "Update" option, similar to JDBC importer + any more import options 5) Page 4 (if update) to show differences Relational model would be generated that contains the standard File connector procedures Virtual Model would be created (or selected) where the view tables containing the canned complex SQL which contains the FILE functions that require the table, column info. - user will define a View Table name for each data file they wish to import - if a View Table already exists in an existing View Model, user will be warned/asked to overwrite (or uncheck) before FINISH EXAMPLE: ========================================================== SELECT EMP.lastName, EMP.firstName, EMP.middleName, EMP.empId, EMP.department, EMP.annualSalary, EMP.title, EMP.homePhone, EMP.mgrId, EMP.street, EMP.city, EMP.state, EMP.ZipCode FROM         (EXEC EmployeeData.getTextFiles('EmployeeData.txt')) AS f, TEXTTABLE(file COLUMNS lastName string, firstName string, middleName string, empId biginteger, department string, annualSalary double, title string, homePhone string, mgrId biginteger, street string, city string, state string, ZipCode string HEADER 3) AS emp ========================================================== Looks like the final SQL will look like: SELECT Name, Sport, Position, Team, City, StateCode, AnnualSalary FROM(EXEC NN.getTextFiles('PlayerData.txt')) AS f, TEXTTABLE(file COLUMNS Name string, Sport string, Position string, Team string, City string, StateCode string, AnnualSalary string HEADER 2) AS A Based on Teiid docs for TEXTTABLE() function Designer will need: - Use Header option (boolean) - Quote character (Defaults to '"') - Skip integer value (specifies the number of text lines (counting every new line) to skip before parsing the contents. - HEADER may still be specified with SKIP. - FIXED WIDTH option (boolean) - If width is specified, then fixed width parsing is used and ESCAPE, QUOTE, and HEADER should not be specified. - Probably need a target NUMBER OF COLUMNS input as well to assist user in fine-tuning the column widths to fit row data