8:47 p.m.
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: Connector API
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
9:06 p.m.
[
https://issues.jboss.org/browse/TEIID-1819?page=com.atlassian.jira.plugin...
]
Steven Hawkins updated TEIID-1819:
----------------------------------
Affects Version/s: 7.6
Component/s: Query Engine
(was: Connector API)
Affects: Documentation (Ref Guide, User Guide, etc.),Release Notes (was:
Documentation (Ref Guide, User Guide, etc.))
I assume this only applies to delimited data, correct? Something along these lines is
easily doable, but the question is how general to make the filtering capability.
Is it enough to rely on an initial character as a selector? Could the selector be a
column value?
Alternatively would you expect texttable to do something as general as apply a regular
expression to the line before parsing the columns?
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
5:18 a.m.
[
https://issues.jboss.org/browse/TEIID-1819?page=com.atlassian.jira.plugin...
]
Peter Larsen commented on TEIID-1819:
-------------------------------------
Yes - the selector can be done in a lot of ways. I've even had cases, where records
had special characters at the end as a "continuation marker".
I think the secret to success is focusing on performance. I don't know if regex is the
best way although it gives the biggest flexibility. The systems I've dealth with over
25 years have usually used a simple string or character to indicate the record type - a
simple "equal" or "not equal" is usually enough.
Other generic issues with comma separated files is quoted vs. non quoted, and date/time
masks. Finally, being able to derive values as expression from other columns is sometimes
necessary to ensure values don't exceed subscript ranges etc (ie. setting the value as
a substring of the value read to ensure a maximum length is kept). I've also used
expressions to refer to values in parent records.
I want to be sure not to overdo the data format "extensions" since we can simply
use the VDB model to late create derived values much easier and faster.
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
4:59 p.m.
[
https://issues.jboss.org/browse/TEIID-1819?page=com.atlassian.jira.plugin...
]
Steven Hawkins commented on TEIID-1819:
---------------------------------------
Our internal format for compiling metadata from Teiid Designer is exactly in the format
you're describing. A single character record type followed by a delimiter and the
delimited record with the possibility for continuation records. So we're very
familiar with this type of file.
We'll add a SELECTOR argument to TEXTTABLE. The base implementation would be a string
that matches the line prefix. Given that the value would be a literal, a compiled regex
pattern would not have that much performance degradation, e.g. '^x' vs. a prefix
comparison of 'x'. However if that complexity is not needed, we can stick with
just a prefix pattern or go with something in the middle, such as a like pattern. Is
you're preference to just match based upon a prefix string? Are you also requesting
the ability to support continuation records?
TEXTTABLE has support for quoted values and the use of an escape character. The data/time
conversion, if their not the same as what is allowed by the built-in type conversion,
would be specified in a select expression using a parseTime, parseDate, parseTimestamp
function. And just as you say there's no need to overdo the TEXTTABLE function, as
dealing with field length or referring to other rows could also be done after the primary
extraction.
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
1:18 a.m.
[
https://issues.jboss.org/browse/TEIID-1819?page=com.atlassian.jira.plugin...
]
Peter Larsen commented on TEIID-1819:
-------------------------------------
The continuation should be independent of the selector.
One feature that is important is the link between records. For instance, if A records are
orders, and B records are order lines, when the rows are read on B, they need to include
the reference key from A to link the records. If something like default=:columnInA is
possible, it would be pretty easily done?
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
1:44 a.m.
[
https://issues.jboss.org/browse/TEIID-1819?page=com.atlassian.jira.plugin...
]
Steven Hawkins commented on TEIID-1819:
---------------------------------------
I understand that continuations are independent of selectors, I'm just clarifying if
you are also requesting support for continuation records. If not, you can of course open
another JIRA later.
References to other records seems like it should be done after the initial read, along the
lines of:
SELECT ... FROM TEXTTABLE(... SELECTOR 'A' ...) as A, TEXTTABLE(... SELECTOR
'B' ...) as B WHERE A.id = B.order_id
But I think that you're implying that you want built-in functionality to perform such
join operations in a single pass. I would need you to elaborate more if that is the
case.
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
3:45 a.m.
[
https://issues.jboss.org/browse/TEIID-1819?page=com.atlassian.jira.plugin...
]
Peter Larsen commented on TEIID-1819:
-------------------------------------
There needs to be a way to set the values of A.id and B order_id. We cannot assume that
the data file will contain information for relational referential integrity. It may simply
be the sequence of things that indicates a relationship, so the B.order_id needs to be
derived from A.id.
Or did I misunderstand your statement here? I'm simply talking about assigning the
value to the B.order_id column. The join/relationship is of course done later on query.
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
4:54 p.m.
[
https://issues.jboss.org/browse/TEIID-1819?page=com.atlassian.jira.plugin...
]
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
3:48 a.m.
[
https://issues.jboss.org/browse/TEIID-1819?page=com.atlassian.jira.plugin...
]
Peter Larsen commented on TEIID-1819:
-------------------------------------
Steven - sorry for the late reply. Fridays are full of meetings for me.
I think I may be misunderstanding things here. Because I thought what I was talking about
was pretty basic relational theory stuff. My assumption will always be that the data file
being read can be read independent of other files. This means I will not need to refer to
other schemas or even tables from within the load descriptor. When I create a record in a
relation manner - and after all that's what we're doing with EDS - I need to be
able to build the data so the query you wrote above can be done. To do joins, I need a
primary key and an a referring foreign key. The data set being imported was NOT generated
by a relational database dump - we cannot assume it will contain those foreign key
columns. In particular because the relationship between selector A and B matters.
All I need to be able to refer to when I read B records, is the current values in the A
record. Why couldn't I use the column name as a reference (like :orderid) to specify
the value for a given column is given by it's parent? The result is, that once inside
EDS, I have the primary and foreign keys available so I can do the required join.
The assumption for the generated file is that there's always going to be a A record
first, followed by 0 or more B records, followed by 0 or more A records (and so on). In
other words, the file is a file of A records with 0 to many child B records.
I've also had (rare) occasions where the A and B records were unrelated. Basically
it's one file with content for more than one table which you define in one operation.
Seed data for instance has often been given to me this way - 10s or 100s of tables with
fixed content, given to me in a single file.
Multi levels could go deeper than two levels - but I cannot remember ever seeing that.
I'm not assuming the presence of header lines at all. They're a great help as the
demo I've seen shows to pre-populate column names.
The challenge is that you cannot simply create two filters, and process the file twice -
once with each filter. The file needs to be read ONCE only. The way I would program the
referral between the records, is simply refer to the current in memory record of A, as I
create B records one after the other.
When data content is specified, you have 3 ways of getting values:
1) Position inside file (either fixed columns, or column number given the separator)
2) Derived - a calculation/function based on existing values
3) Constant
I'm talking about case #2 here. Where I want to be able to refer to the existing
values of separator A. I would propose the design be something that relate the filter to a
table or set name. And when you refer to other values you can write :table.column to read
the value where the table part is optional so simple loads doesn't have to be
complicated.
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
2:17 p.m.
[
https://issues.jboss.org/browse/TEIID-1819?page=com.atlassian.jira.plugin...
]
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
1:19 p.m.
[
https://issues.jboss.org/browse/TEIID-1819?page=com.atlassian.jira.plugin...
]
Steven Hawkins updated TEIID-1819:
----------------------------------
Attachment: selector.patch
basic selector support
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
Attachments: selector.patch
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
1:33 p.m.
[
https://issues.jboss.org/browse/TEIID-1819?page=com.atlassian.jira.plugin...
]
Steven Hawkins updated TEIID-1819:
----------------------------------
Fix Version/s: 8.0
Putting into 8.0 as there was not sufficient time to get into 7.6. Any more feedback on
the expected format of header lines when using a selector? I am only asking for
clarification because through just cursory searches I cannot find any examples using a
;selector= prefix.
I'll also assume that the having no parent value would populate null values for any
parent fields referenced.
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
Fix For: 8.0
Attachments: selector.patch
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
10:07 p.m.
[
https://issues.jboss.org/browse/TEIID-1819?page=com.atlassian.jira.plugin...
]
Steven Hawkins commented on TEIID-1819:
---------------------------------------
Peter, can you provide any supporting links/information on the expected header format when
using multi-entity data? Was the choice of ;selector=prefix based upon a particular
product's export?
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
Fix For: 8.0
Attachments: selector.patch
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
8:34 p.m.
[
https://issues.jboss.org/browse/TEIID-1819?page=com.atlassian.jira.plugin...
]
Steven Hawkins resolved TEIID-1819.
-----------------------------------
Resolution: Done
Added the selector argument to texttable and added the ability to specify
selector/position for columns to reference parent values. This was an easier
implementation path than using the nested texttable columns approach proposed above, e.g.
with the file:
A,10-dec-2011,12345,3322,3000,222
B,1,123,Sprockets Black,30,50,1500
B,2,333,Sprockets Blue,300,5,1500
select x.* from texttable(file SELECTOR 'B' COLUMNS x string, y integer, z integer
SELECTOR 'A' 3) x";
would return:
["B", 1, 12345],
["B", 2, 12345]
the docs and release notes were also updated. There is no special treatment of the header
when using selectors. Note that parent values can only be selected positionally.
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
Fix For: 8.0
Attachments: selector.patch
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
12:19 p.m.
[
https://issues.jboss.org/browse/TEIID-1819?page=com.atlassian.jira.plugin...
]
Steven Hawkins closed TEIID-1819.
---------------------------------
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
Fix For: 8.0
Attachments: selector.patch
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
4423
days inactive
4614
days old
14 comments
5 participants
participants (5)
-
Peter Larsen (Commented) (JIRA) -
Peter Larsen (Created) (JIRA)
-
Steven Hawkins (Commented) (JIRA)
-
Steven Hawkins (JIRA)
-
Steven Hawkins (Updated) (JIRA)