[teiid-issues] [JBoss JIRA] (TEIID-1819) Reading multi entity data from a single data file

[Steven Hawkins (Commented) (JIRA)]

    [ https://issues.jboss.org/browse/TEIID-1819?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12642311#comment-12642311 ] 

Steven Hawkins commented on TEIID-1819:
---------------------------------------

You are not misunderstanding.  We are on the same page.  I'm simply ensuring that we're being specific enough about what this feature entails.  It will help to give concrete syntax examples.

At the base level we're introducing SELECTOR argument to TEXTTABLE - 

TEXTTABLE(expression COLUMNS <COLUMN>, ... [DELIMITER char] [(QUOTE|ESCAPE) char] [HEADER [integer]] [SKIP integer] SELECTOR string)

When specified the SELECTOR will allow only record lines that begin with the given prefix to be parsed.  The prefix will not be removed from the record line.  The SELECTOR would be case-sensitive and can only be used in delimited parsing.  From your initial example, if HEADER is specified with SELECTOR, is it expected that the header line is expected to be prefixed with ";SELECTOR="?

What you are referring to as a derived value, means derived from values outside of the current record line and specifically in another record type.   Any other situation where a value is derived (from values within the current record) would mean the value is produced by a select expression, and is not the responsibility of TEXTTABLE.

For a positional relationship a possible syntax is:

TEXTTABLE(expression COLUMNS ... [DELIMITER char] [(QUOTE|ESCAPE) char] [HEADER [integer]] [SKIP integer] SELECTOR string COLUMNS ... [HEADER [integer]] SELECTOR string)

Here the second column/header/selector arguments refer to the parent record which must appear before a set of records.  If no parent record exists we could treat that as an error, or let this function as a left out join and use null values.  Do you have a preference?  For now we would also limit the hierarchy to just a single level.

With number 3, a constant value would also not be the responsibility of TEXTTABLE as there again you can simply project a constant from the select clause.  Do you mean the ability to specify a default column value?  That can also easily be handled by an ifnull function, but could be added under this enhancement also.
                
> Reading multi entity data from a single data file
> -------------------------------------------------
>
>                 Key: TEIID-1819
>                 URL: https://issues.jboss.org/browse/TEIID-1819
>             Project: Teiid
>          Issue Type: Feature Request
>          Components: Query Engine
>    Affects Versions: 7.6
>         Environment: Any
>            Reporter: Peter Larsen
>            Assignee: Steven Hawkins
>
> A common problem for data files is the concept of multiple data sets inclosed in the same file. An example is a data file of accounts receivable orders. You'll export at least two logical entities: Orders and OrderLines. Each of the two entities have very different data sets; the relate (OrderLines belong to a particular Order) and there are a dynamic number of OrderLines per Order.
> A common way to differentiate is to put a special "record type" selector as the first field in each record. Ie. A and B. The load program will based on this selector apply different templates to map the columns, and it will also know that the OrderLines are associated with the Order above it and create that relation column ID in the out put.
> Example:
> ;selector=A,orderdate,ordernumber,customernumber,ordertotal,ordertax
> ;selector=B,lineno,itemno,description,quantity,priceach,pricetotal
> A,10-dec-2011,12345,3322,3000,222
> B,1,123,Sprockets Black,30,50,1500
> B,2,333,Sprockets Blue,300,5,1500
> A,11-dec-2011,12346,3311,.....
> etc. 

--
This message is automatically generated by JIRA.
If you think it was sent incorrectly, please contact your JIRA administrators: https://issues.jboss.org/secure/ContactAdministrators!default.jspa
For more information on JIRA, see: http://www.atlassian.com/software/jira