Uploaded image for project: 'Teiid Designer'
  1. Teiid Designer
  2. TEIIDDES-1017

Create "Metadata from Teiid Metadata File" import option which utilizes DTP Flat File connection profile and targets Teiid formatted source table files

    XMLWordPrintable

    Details

    • Type: Feature Request
    • Status: Closed (View Workflow)
    • Priority: Major
    • Resolution: Done
    • Affects Version/s: 7.5
    • Fix Version/s: 7.5, 7.4.2
    • Component/s: Import/Export
    • Labels:
      None

      Description

      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

        Gliffy Diagrams

          Balsamiq Wireframes

            Attachments

              Issue Links

                Activity

                  People

                  • Assignee:
                    blafond Barry LaFond
                    Reporter:
                    blafond Barry LaFond
                  • Votes:
                    0 Vote for this issue
                    Watchers:
                    1 Start watching this issue

                    Dates

                    • Created:
                      Updated:
                      Resolved: