[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=12642158#comment-12642158 ] 

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

No you didn't misundertand.  In our metadata files for instance all records have identifiers and we have parent records with lists of child identifiers, children with parent identifiers, and some with 1-1 relationships.  In any of those cases it is possible to join the records after the initial extraction.

If you're basing the relationship positionally, for example B child entries occur directly after an A parent record, then we'll need concrete examples/rules for the file contents and the possible TEXTTABLE syntax.  For example are you assuming the presence of the selector header lines, or will the user need to specify the relevant schema for the parent columns?  Is only 1 level of relationship possible or can these files describe multi-level hierarchies?

Alternatively is there a standard or RFC that would be a good guide?
                
> 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