[teiid-issues] [JBoss JIRA] Commented: (TEIID-1102) Create a replacement for the text translator

Create a replacement for the text translator -------------------------------------------- Key: TEIID-1102 URL: https://jira.jboss.org/browse/TEIID-1102 Project: Teiid Issue Type: Feature Request Components: Misc. Connectors, Query Engine Affects Versions: 7.0 Reporter: Steve Hawkins Assignee: Steve Hawkins Fix For: 7.0 The current solution for the text connector requires the use of an external metadata file. This complicates the user experience with integrating text. An alternative approach would be to offer a built-in table function for text parsing: TEXTTABLE(expression COLUMNS column [, column]* [DELIMITER char] [(QUOTE|ESCAPE) char] [HEADER [integer]] [SKIP integer]) AS identifier column := identifier datatype [WIDTH integer] 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. 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. this would be implicitly a nested table, so it could be used to process multiple files: select x.* from t, texttable(t.clobcolumn COLUMNS first string, second date SKIP 1) x