[JBoss JIRA] (TEIID-2896) Add TEXTTABLE function capabilities to interpret fixed width data file content that is shorter than the defined line length
by Ramesh Reddy (JIRA)
[ https://issues.jboss.org/browse/TEIID-2896?page=com.atlassian.jira.plugin... ]
Ramesh Reddy commented on TEIID-2896:
-------------------------------------
IMO, we should support simple case of fix length where all the lines are same width with Designer, so that we can keep the UI simple. The SELECTOR and varying width can be user exercise as I do not think they will be used as much, and making intent show up in UI could be challenging.
> Add TEXTTABLE function capabilities to interpret fixed width data file content that is shorter than the defined line length
> ---------------------------------------------------------------------------------------------------------------------------
>
> Key: TEIID-2896
> URL: https://issues.jboss.org/browse/TEIID-2896
> Project: Teiid
> Issue Type: Enhancement
> Components: Query Engine
> Reporter: Van Halbert
> Assignee: Steven Hawkins
> Fix For: 8.7
>
> Attachments: sample, TEIID-2896.patch
>
>
> Current behavior is that data lines too short to fit a fixed width data interpretation produces an error such as "Error: Text parse error: Fixed width line width 35 is smaller than the expected 60 on text line 1 in file:/tmp/file.csv.". The customer's use case is interpreting data files from another application which bundles multiple types of data along with additional descriptive info/metadata about the data contained within the file. As the error is thrown if any lines after the first interpreted line are shorter than the summed value of the fixed width columns, they are unable to query the file. The customer wishes for the ERROR to be suppressed and the query to return the data independent of whether it fits the fixed width criteria and potentially to be able to filter the data being retrieved.
--
This message was sent by Atlassian JIRA
(v6.2.3#6260)
11 years, 10 months
[JBoss JIRA] (TEIID-2896) Add TEXTTABLE function capabilities to interpret fixed width data file content that is shorter than the defined line length
by Steven Hawkins (JIRA)
[ https://issues.jboss.org/browse/TEIID-2896?page=com.atlassian.jira.plugin... ]
Steven Hawkins commented on TEIID-2896:
---------------------------------------
To build off what you are saying, we have three scenarios:
1. standard sv or fixed width such that all rows have the same characteristics (should already be covered by designer)
2. sv file with multiple row types - here both the row selector and column selector can be used. This functionality existed before this issue. I'm not sure if designer had any support. This allows the user to create denormalized rows, such as:
type,firstName,lastName
p,John,Smith
c,Sue
c,Billy
p,Mildred,Jones
c,Tobias
With the texttable:
TEXTTABLE (file SELECTOR 'c' COLUMNS firstName string, lastName string SELECTOR 'p' 3 HEADER)
Would produce:
Sue,Smith
Billy,Smith
Tobias,Jones
3. A fixed width file with multiple row types. Because of this issue it doesn't always matter that the rows conform to a particular expected data length, or by TEIID-2898 you can selectively only parse the rows matching the row selector.
> Add TEXTTABLE function capabilities to interpret fixed width data file content that is shorter than the defined line length
> ---------------------------------------------------------------------------------------------------------------------------
>
> Key: TEIID-2896
> URL: https://issues.jboss.org/browse/TEIID-2896
> Project: Teiid
> Issue Type: Enhancement
> Components: Query Engine
> Reporter: Van Halbert
> Assignee: Steven Hawkins
> Fix For: 8.7
>
> Attachments: sample, TEIID-2896.patch
>
>
> Current behavior is that data lines too short to fit a fixed width data interpretation produces an error such as "Error: Text parse error: Fixed width line width 35 is smaller than the expected 60 on text line 1 in file:/tmp/file.csv.". The customer's use case is interpreting data files from another application which bundles multiple types of data along with additional descriptive info/metadata about the data contained within the file. As the error is thrown if any lines after the first interpreted line are shorter than the summed value of the fixed width columns, they are unable to query the file. The customer wishes for the ERROR to be suppressed and the query to return the data independent of whether it fits the fixed width criteria and potentially to be able to filter the data being retrieved.
--
This message was sent by Atlassian JIRA
(v6.2.3#6260)
11 years, 10 months
[JBoss JIRA] (TEIID-2896) Add TEXTTABLE function capabilities to interpret fixed width data file content that is shorter than the defined line length
by Barry LaFond (JIRA)
[ https://issues.jboss.org/browse/TEIID-2896?page=com.atlassian.jira.plugin... ]
Barry LaFond commented on TEIID-2896:
-------------------------------------
So what was not clear to me was what these data files actually look like so the UI (TEIIDDES-2091) could help users create these *views*
My understanding is that there are standard CSV files and *complex* CSV files
* CSV files contain multiple rows of data containing the same #columns and column data
* complex CSV will contain different *types* of rows, with each type representing a mini-CSV file format
It appears that you cannot create a TEXTTABLE() statement that crosses *types*, so Designer would need to help users choose the *type* to create a single *view* as Van just stated?
> Add TEXTTABLE function capabilities to interpret fixed width data file content that is shorter than the defined line length
> ---------------------------------------------------------------------------------------------------------------------------
>
> Key: TEIID-2896
> URL: https://issues.jboss.org/browse/TEIID-2896
> Project: Teiid
> Issue Type: Enhancement
> Components: Query Engine
> Reporter: Van Halbert
> Assignee: Steven Hawkins
> Fix For: 8.7
>
> Attachments: sample, TEIID-2896.patch
>
>
> Current behavior is that data lines too short to fit a fixed width data interpretation produces an error such as "Error: Text parse error: Fixed width line width 35 is smaller than the expected 60 on text line 1 in file:/tmp/file.csv.". The customer's use case is interpreting data files from another application which bundles multiple types of data along with additional descriptive info/metadata about the data contained within the file. As the error is thrown if any lines after the first interpreted line are shorter than the summed value of the fixed width columns, they are unable to query the file. The customer wishes for the ERROR to be suppressed and the query to return the data independent of whether it fits the fixed width criteria and potentially to be able to filter the data being retrieved.
--
This message was sent by Atlassian JIRA
(v6.2.3#6260)
11 years, 10 months
[JBoss JIRA] (TEIID-2896) Add TEXTTABLE function capabilities to interpret fixed width data file content that is shorter than the defined line length
by Van Halbert (JIRA)
[ https://issues.jboss.org/browse/TEIID-2896?page=com.atlassian.jira.plugin... ]
Van Halbert commented on TEIID-2896:
------------------------------------
Your right, there needs to be a "view" for each SELECTOR type. So designer will need to create one for each SELECTOR type it defines.
> Add TEXTTABLE function capabilities to interpret fixed width data file content that is shorter than the defined line length
> ---------------------------------------------------------------------------------------------------------------------------
>
> Key: TEIID-2896
> URL: https://issues.jboss.org/browse/TEIID-2896
> Project: Teiid
> Issue Type: Enhancement
> Components: Query Engine
> Reporter: Van Halbert
> Assignee: Steven Hawkins
> Fix For: 8.7
>
> Attachments: sample, TEIID-2896.patch
>
>
> Current behavior is that data lines too short to fit a fixed width data interpretation produces an error such as "Error: Text parse error: Fixed width line width 35 is smaller than the expected 60 on text line 1 in file:/tmp/file.csv.". The customer's use case is interpreting data files from another application which bundles multiple types of data along with additional descriptive info/metadata about the data contained within the file. As the error is thrown if any lines after the first interpreted line are shorter than the summed value of the fixed width columns, they are unable to query the file. The customer wishes for the ERROR to be suppressed and the query to return the data independent of whether it fits the fixed width criteria and potentially to be able to filter the data being retrieved.
--
This message was sent by Atlassian JIRA
(v6.2.3#6260)
11 years, 10 months
[JBoss JIRA] (TEIID-2990) MongoDB: Usage of lookup() function throws an exception
by Ramesh Reddy (JIRA)
[ https://issues.jboss.org/browse/TEIID-2990?page=com.atlassian.jira.plugin... ]
Ramesh Reddy updated TEIID-2990:
--------------------------------
Description:
Exception[1] is thrown when function lookup() is used in SQL query.
Mongo translator doesn't support selection of constants only, which causes the exception(select 1, select 'hi' from smalla).
{code}
[1] 3:59:20,997 ERROR [org.teiid.CONNECTOR] (Worker5_QueryProcessorQueue6) Connector worker process failed for atomic-request=Ef2Z7E0246by.1.5.3: com.mongodb.CommandFailureException: { "serverUsed" : "localhost/127.0.0.1:27017" , "errmsg" : "exception: $projection requires at least one output field" , "code" : 16403 , "ok" : 0.0}
at com.mongodb.CommandResult.getException(CommandResult.java:71)
{code}
Server log is in the attachment.
Sample query:
{code}
SELECT BQT1.SmallA.IntKey FROM BQT1.SmallA, BQT1.SmallB WHERE BQT1.SmallA.IntKey= lookup('BQT1.SmallB', 'IntKey', 'StringKey', BQT1.SmallA.StringKey) ORDER BY IntKey
{code}
Generated mongo commands:
{code}
{ aggregate: "smalla", pipeline: [ { $project: { _m0: "$INTKEY", _m1: "$STRINGKEY" } } ] }
{code}
and
{code}
{ aggregate: "smallb", pipeline: [ { $project: {} } ] }
{code}
was:
Exception[1] is thrown when function lookup() is used in SQL query.
Mongo translator doesn't support selection of constants only, which causes the exception(select 1, select 'hi' from smalla).
[1] 3:59:20,997 ERROR [org.teiid.CONNECTOR] (Worker5_QueryProcessorQueue6) Connector worker process failed for atomic-request=Ef2Z7E0246by.1.5.3: com.mongodb.CommandFailureException: { "serverUsed" : "localhost/127.0.0.1:27017" , "errmsg" : "exception: $projection requires at least one output field" , "code" : 16403 , "ok" : 0.0}
at com.mongodb.CommandResult.getException(CommandResult.java:71)
Server log is in the attachment.
Sample query:
SELECT BQT1.SmallA.IntKey FROM BQT1.SmallA, BQT1.SmallB WHERE BQT1.SmallA.IntKey= lookup('BQT1.SmallB', 'IntKey', 'StringKey', BQT1.SmallA.StringKey) ORDER BY IntKey
Generated mongo commands:
{ aggregate: "smalla", pipeline: [ { $project: { _m0: "$INTKEY", _m1: "$STRINGKEY" } } ] }
and
{ aggregate: "smallb", pipeline: [ { $project: {} } ] }
> MongoDB: Usage of lookup() function throws an exception
> -------------------------------------------------------
>
> Key: TEIID-2990
> URL: https://issues.jboss.org/browse/TEIID-2990
> Project: Teiid
> Issue Type: Bug
> Components: Misc. Connectors
> Affects Versions: 8.4.1
> Reporter: Filip Elias
> Assignee: Ramesh Reddy
> Fix For: 8.7.1, 8.8
>
> Attachments: server.log
>
>
> Exception[1] is thrown when function lookup() is used in SQL query.
> Mongo translator doesn't support selection of constants only, which causes the exception(select 1, select 'hi' from smalla).
> {code}
> [1] 3:59:20,997 ERROR [org.teiid.CONNECTOR] (Worker5_QueryProcessorQueue6) Connector worker process failed for atomic-request=Ef2Z7E0246by.1.5.3: com.mongodb.CommandFailureException: { "serverUsed" : "localhost/127.0.0.1:27017" , "errmsg" : "exception: $projection requires at least one output field" , "code" : 16403 , "ok" : 0.0}
> at com.mongodb.CommandResult.getException(CommandResult.java:71)
> {code}
> Server log is in the attachment.
> Sample query:
> {code}
> SELECT BQT1.SmallA.IntKey FROM BQT1.SmallA, BQT1.SmallB WHERE BQT1.SmallA.IntKey= lookup('BQT1.SmallB', 'IntKey', 'StringKey', BQT1.SmallA.StringKey) ORDER BY IntKey
> {code}
> Generated mongo commands:
> {code}
> { aggregate: "smalla", pipeline: [ { $project: { _m0: "$INTKEY", _m1: "$STRINGKEY" } } ] }
> {code}
> and
> {code}
> { aggregate: "smallb", pipeline: [ { $project: {} } ] }
> {code}
--
This message was sent by Atlassian JIRA
(v6.2.3#6260)
11 years, 10 months
[JBoss JIRA] (TEIID-2988) MongoDB: Function substring - starting index and number of arguments
by Ramesh Reddy (JIRA)
[ https://issues.jboss.org/browse/TEIID-2988?page=com.atlassian.jira.plugin... ]
Ramesh Reddy commented on TEIID-2988:
-------------------------------------
skip and starting index would be same, what is missing is third the length parameter. Since Teiid can use both SUBSTRING(X,Y) or SUBSTRING(X,Y,Z) in the first case MongoDB translator would need to set length. Since length function not available, I will be using MAX_STRING_STRING system property to define the largest string Teiid can allow.
> MongoDB: Function substring - starting index and number of arguments
> --------------------------------------------------------------------
>
> Key: TEIID-2988
> URL: https://issues.jboss.org/browse/TEIID-2988
> Project: Teiid
> Issue Type: Bug
> Components: Misc. Connectors
> Affects Versions: 8.4.1
> Reporter: Filip Elias
> Assignee: Ramesh Reddy
> Fix For: 8.7.1, 8.8
>
>
> Two minor bugs in function substring()
> Mongo's function $substr takes only 3 arguments so this query fails:
> {code}
> SELECT intkey, SUBSTRING(BQT1.SmallA.stringkey, 1) FROM BQT1.SmallA ORDER BY intkey
> {code}
> {code}
> Exception:13:18:48,220 ERROR [org.teiid.CONNECTOR] (Worker38_QueryProcessorQueue345) Connector worker process failed for atomic-request=omsmHP0o16Sa.40.0.86: com.mongodb.CommandFailureException: { "serverUsed" : "localhost/127.0.0.1:27017" , "errmsg" : "exception: the $substr operator requires 3 operand(s)" , "code" : 16020 , "ok" : 0.0}
> at com.mongodb.CommandResult.getException(CommandResult.java:71)
> at com.mongodb.CommandResult.throwOnError(CommandResult.java:110)
> at com.mongodb.DBCollection.aggregate(DBCollection.java:1308)
> {code}
> Generated mongo command:
> {code}
> { aggregate: "smalla", pipeline: [ { $project: { c_0: "$INTKEY", c_1: { $substr: [ "$STRINGKEY", 1 ] } } }, { $sort: { c_0: 1 } } ] }
> {code}
> Mongo's $substr function second argument represents number of bytes to skip, not the starting index. The function substring(StringKey,1,1) returns the second letter, but it should return the first letter.
--
This message was sent by Atlassian JIRA
(v6.2.3#6260)
11 years, 10 months
[JBoss JIRA] (TEIID-2986) issues with embedded
by Steven Hawkins (JIRA)
[ https://issues.jboss.org/browse/TEIID-2986?page=com.atlassian.jira.plugin... ]
Steven Hawkins resolved TEIID-2986.
-----------------------------------
Resolution: Done
addressed the issues listed in the description.
> issues with embedded
> --------------------
>
> Key: TEIID-2986
> URL: https://issues.jboss.org/browse/TEIID-2986
> Project: Teiid
> Issue Type: Bug
> Components: Embedded
> Affects Versions: 8.7
> Reporter: Steven Hawkins
> Assignee: Steven Hawkins
> Fix For: 8.7.1, 8.8
>
>
> The dependency set for the ws translator is incomplete the cxf bundle jar and several other dependencies are needed.
> We are not including the JBoss modules jar, but have an implied dependency through EmbeddedProfile, which should be refactored so that the modules jar is not needed in the embedded kit.
> Also the classpaths in the example scripts are incorrect with the 8.7 refactoring to put optional dependencies in subfolders.
--
This message was sent by Atlassian JIRA
(v6.2.3#6260)
11 years, 10 months
[JBoss JIRA] (TEIID-2896) Add TEXTTABLE function capabilities to interpret fixed width data file content that is shorter than the defined line length
by Steven Hawkins (JIRA)
[ https://issues.jboss.org/browse/TEIID-2896?page=com.atlassian.jira.plugin... ]
Steven Hawkins commented on TEIID-2896:
---------------------------------------
> So if you have an example of how you accomplished this, it would be appreciated.
> A column SELECTOR is not valid with fixed width parsing.
Sorry I wasn't paying full attention. What are referring to by this? To clarify there are two selectors, a row selector and a column selector. Row selectors are supported in fixed width, but yes column selectors are not. In fixed width column selectors do not make general sense unless all rows have the same data format. See also TEIID-2898
> As for the test case, you add null to the end of the line, but in the sample file attached, the actual null column is in the middle.
I'm not sure what you mean.
> and part of the problem with the sample is, each type has a different fixed width.
Generally you want only a single type, so you would use a row selector for only that type.
> Add TEXTTABLE function capabilities to interpret fixed width data file content that is shorter than the defined line length
> ---------------------------------------------------------------------------------------------------------------------------
>
> Key: TEIID-2896
> URL: https://issues.jboss.org/browse/TEIID-2896
> Project: Teiid
> Issue Type: Enhancement
> Components: Query Engine
> Reporter: Van Halbert
> Assignee: Steven Hawkins
> Fix For: 8.7
>
> Attachments: sample, TEIID-2896.patch
>
>
> Current behavior is that data lines too short to fit a fixed width data interpretation produces an error such as "Error: Text parse error: Fixed width line width 35 is smaller than the expected 60 on text line 1 in file:/tmp/file.csv.". The customer's use case is interpreting data files from another application which bundles multiple types of data along with additional descriptive info/metadata about the data contained within the file. As the error is thrown if any lines after the first interpreted line are shorter than the summed value of the fixed width columns, they are unable to query the file. The customer wishes for the ERROR to be suppressed and the query to return the data independent of whether it fits the fixed width criteria and potentially to be able to filter the data being retrieved.
--
This message was sent by Atlassian JIRA
(v6.2.3#6260)
11 years, 10 months
[JBoss JIRA] (TEIID-2991) Add an embedded web service example
by Steven Hawkins (JIRA)
Steven Hawkins created TEIID-2991:
-------------------------------------
Summary: Add an embedded web service example
Key: TEIID-2991
URL: https://issues.jboss.org/browse/TEIID-2991
Project: Teiid
Issue Type: Task
Components: Embedded
Reporter: Steven Hawkins
Assignee: Steven Hawkins
Fix For: 8.8
With fixing other embedded issue I have a simple web scenario that should be turned into an example.
--
This message was sent by Atlassian JIRA
(v6.2.3#6260)
11 years, 10 months
[JBoss JIRA] (TEIID-2896) Add TEXTTABLE function capabilities to interpret fixed width data file content that is shorter than the defined line length
by Van Halbert (JIRA)
[ https://issues.jboss.org/browse/TEIID-2896?page=com.atlassian.jira.plugin... ]
Van Halbert commented on TEIID-2896:
------------------------------------
and part of the problem with the sample is, each type has a different fixed width.
> Add TEXTTABLE function capabilities to interpret fixed width data file content that is shorter than the defined line length
> ---------------------------------------------------------------------------------------------------------------------------
>
> Key: TEIID-2896
> URL: https://issues.jboss.org/browse/TEIID-2896
> Project: Teiid
> Issue Type: Enhancement
> Components: Query Engine
> Reporter: Van Halbert
> Assignee: Steven Hawkins
> Fix For: 8.7
>
> Attachments: sample, TEIID-2896.patch
>
>
> Current behavior is that data lines too short to fit a fixed width data interpretation produces an error such as "Error: Text parse error: Fixed width line width 35 is smaller than the expected 60 on text line 1 in file:/tmp/file.csv.". The customer's use case is interpreting data files from another application which bundles multiple types of data along with additional descriptive info/metadata about the data contained within the file. As the error is thrown if any lines after the first interpreted line are shorter than the summed value of the fixed width columns, they are unable to query the file. The customer wishes for the ERROR to be suppressed and the query to return the data independent of whether it fits the fixed width criteria and potentially to be able to filter the data being retrieved.
--
This message was sent by Atlassian JIRA
(v6.2.3#6260)
11 years, 10 months