8:20 a.m.
[
https://issues.jboss.org/browse/TEIIDDES-1017?page=com.atlassian.jira.plu...
]
Barry LaFond commented on TEIIDDES-1017:
----------------------------------------
1.5.3.2. TEXTTABLE
The TEXTTABLE funciton processes character input to produce tabular ouptut. 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
preceeding FROM clause entries.
Usage:
TEXTTABLE(expression COLUMNS <COLUMN>, ... [DELIMITER char] [(QUOTE|ESCAPE) char]
[HEADER [integer]] [SKIP integer]) AS name
COLUMN := name datatype [WIDTH integer]
Parameters
expression - the text content to process, which should be convertable to CLOB.
DELIMITER sets the field delimiter character to use. Defaults to ','.
QUOTE sets the quote, or qualifier, character used to wrap field values. Defaults to
'"'.
ESCAPE sets the escape character to use if no quoting character is in use. This is
used in situations where the delimiter or new line characters are escaped with a preceding
character, e.g. \,
HEADER specifies the text line number (counting every new line) on which the column
names occur. All lines prior to the header will be skipped. If HEADER is specified, then
the header line will be used to determine the TEXTTABLE column position by
case-insensitive name matching. This is especially useful in situations where only a
subset of the columns are needed. If the HEADER value is not specified, it defaults to 1.
If HEADER is not specified, then columns are expected to match positionally with the text
contents.
SKIP specifies the number of text lines (counting every new line) to skip before
parsing the contents. HEADER may still be specified with SKP.
Syntax Rules:
If width is specified for one column it must be specified for all columns.
If width is specified, then fixed width parsing is used and ESCAPE, QUOTE, and HEADER
should not be specified.
The columns names must be not contain duplicates.
Examples
Use of the HEADER parameter, returns 1 row ['b']:
select * from texttable('col1,col2,col3\na,b,c' COLUMNS col2 string HEADER) x
Use of fixed width, returns 1 row ['a', 'b', 'c']:
select * from texttable('abc' COLUMNS col1 string width 1, col2 string width
1, col3 string width 1) x
Use of ESCAPE parameter, returns 1 row ['a,', 'b']:
select * from texttable('a:,,b' COLUMNS col1 string, col2 string ESCAPE
':') x
As a nested table:
select x.* from t, texttable(t.clobcolumn COLUMNS first string, second date SKIP 1) x
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
Attachments: EmpData.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-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
--
This message is automatically generated by JIRA.
For more information on JIRA, see: http://www.atlassian.com/software/jira