[JBoss JIRA] (TEIID-5093) Amazon s3 csv/xml lookup querry exception
by Steven Hawkins (JIRA)
[ https://issues.jboss.org/browse/TEIID-5093?page=com.atlassian.jira.plugin... ]
Steven Hawkins commented on TEIID-5093:
---------------------------------------
> either not use XMLPARSE and cast the returned object to XML
No I'm saying you have to use the WELLFORMED option on XMLPARSE (there isn't a direct string cast to xml) if it's a read once stream.
> or do not use Streaming (or add a way to configure)
Or make it explicit in the docs that the S3 result stream can only be read once.
> I did not understand how to check for DataSource to be reusable?
You can call DataSource.getInputStream twice and fully read the result both times.
The ConnectorWorkItem logic will assume that if two different inputstreams are returned from two calls to getInputStream that the DataSource is reusable, otherwise it will create a copyonread stream that will buffer the result so that it can be reread. https://github.com/teiid/teiid/blob/2514258192f5226d04fcccacad6eca8938153...
The STREAMING option for the ws procedure is like a user hint to prevent any any temporary copies is possible.
> Amazon s3 csv/xml lookup querry exception
> -----------------------------------------
>
> Key: TEIID-5093
> URL: https://issues.jboss.org/browse/TEIID-5093
> Project: Teiid
> Issue Type: Bug
> Components: Misc. Connectors
> Affects Versions: 8.12.x-6.4
> Reporter: Mario Majernik
> Assignee: Steven Hawkins
>
> Querry :
> {code:java}
> SELECT IntKey, StringKey, lookup('BQT1.SmallB', 'DoubleNum', 'IntKey', IntKey) AS DoubleNum FROM BQT1.SmallA UNION SELECT IntKey, lookup('BQT1.SmallA', 'StringKey', 'IntKey', IntKey) AS StringKey, DoubleNum FROM BQT1.SmallA
> {code}
> returns TeiidSQLException for csv and xml files from amazon s3.
> Stacktrace:
> {code:java}
> WARN [org.teiid.PROCESSOR] (Worker2_QueryProcessorQueue41) TEIID30020 Processing exception for request 8O9K6VJIjd9y.7 'TEIID30180 java.sql.SQLException: java.sql.SQLException: TEIID60019 Streaming result has already been read once. Ensure that only one read operation needs to be performed, for example XMLPARSE without the WELLFORMED operation must read the entire stream to validate its contents. Or you may choose to use a non-streaming result. '. Originally TeiidProcessingException BinaryWSProcedureExecution.java:78. Enable more detailed logging to see the entire stacktrace.
> {code}
> Teiid command log for querry:
> {code:java}
> 15:11:34,160 INFO [org.teiid.COMMAND_LOG] (New I/O worker #2) 8O9K6VJIjd9y START USER COMMAND: startTime=2017-10-09 15:11:34.16 requestID=8O9K6VJIjd9y.7 txID=null sessionID=8O9K6VJIjd9y applicationName=JDBC principal=user@teiid-security vdbName=csv vdbVersion=1 sql=SELECT IntKey, StringKey, lookup('BQT1.SmallB', 'DoubleNum', 'IntKey', IntKey) AS DoubleNum FROM BQT1.SmallA UNION SELECT IntKey, lookup('BQT1.SmallA', 'StringKey', 'IntKey', IntKey) AS StringKey, DoubleNum FROM BQT1.SmallA
> 15:11:34,192 DEBUG [org.teiid.COMMAND_LOG] (Worker0_QueryProcessorQueue40) 8O9K6VJIjd9y START DATA SRC COMMAND: startTime=2017-10-09 15:11:34.192 requestID=8O9K6VJIjd9y.7 sourceCommandID=6 executionID=2 txID=null modelName=sourceModel translatorName=user-s3 sessionID=8O9K6VJIjd9y principal=user@teiid-security sql=EXEC sourceModel.getTextFile('csv/smallaCsv.csv')
> 15:11:34,856 DEBUG [org.teiid.COMMAND_LOG] (Worker2_QueryProcessorQueue41) 8O9K6VJIjd9y END SRC COMMAND: endTime=2017-10-09 15:11:34.856 requestID=8O9K6VJIjd9y.7 sourceCommandID=6 executionID=2 txID=null modelName=sourceModel translatorName=user-s3 sessionID=8O9K6VJIjd9y principal=user@teiid-security finalRowCount=1 cpuTime(ns)=34673443
> 15:11:34,862 INFO [org.teiid.COMMAND_LOG] (Worker2_QueryProcessorQueue41) 8O9K6VJIjd9y ERROR USER COMMAND: endTime=2017-10-09 15:11:34.862 requestID=8O9K6VJIjd9y.7 txID=null sessionID=8O9K6VJIjd9y principal=user@teiid-security vdbName=csv vdbVersion=1 finalRowCount=null
> 15:11:34,876 INFO [org.teiid.COMMAND_LOG] (Worker2_QueryProcessorQueue41) 8O9K6VJIjd9y END USER COMMAND: endTime=2017-10-09 15:11:34.876 requestID=8O9K6VJIjd9y.7 txID=null sessionID=8O9K6VJIjd9y principal=user@teiid-security vdbName=csv vdbVersion=1 finalRowCount=0
> {code}
--
This message was sent by Atlassian JIRA
(v7.5.0#75005)
7 years, 3 months
[JBoss JIRA] (TEIID-5093) Amazon s3 csv/xml lookup querry exception
by Ramesh Reddy (JIRA)
[ https://issues.jboss.org/browse/TEIID-5093?page=com.atlassian.jira.plugin... ]
Ramesh Reddy commented on TEIID-5093:
-------------------------------------
So you are saying either not use XMLPARSE and cast the returned object to XML or do not use Streaming (or add a way to configure)?
I did not understand how to check for DataSource to be reusable?
> Amazon s3 csv/xml lookup querry exception
> -----------------------------------------
>
> Key: TEIID-5093
> URL: https://issues.jboss.org/browse/TEIID-5093
> Project: Teiid
> Issue Type: Bug
> Components: Misc. Connectors
> Affects Versions: 8.12.x-6.4
> Reporter: Mario Majernik
> Assignee: Steven Hawkins
>
> Querry :
> {code:java}
> SELECT IntKey, StringKey, lookup('BQT1.SmallB', 'DoubleNum', 'IntKey', IntKey) AS DoubleNum FROM BQT1.SmallA UNION SELECT IntKey, lookup('BQT1.SmallA', 'StringKey', 'IntKey', IntKey) AS StringKey, DoubleNum FROM BQT1.SmallA
> {code}
> returns TeiidSQLException for csv and xml files from amazon s3.
> Stacktrace:
> {code:java}
> WARN [org.teiid.PROCESSOR] (Worker2_QueryProcessorQueue41) TEIID30020 Processing exception for request 8O9K6VJIjd9y.7 'TEIID30180 java.sql.SQLException: java.sql.SQLException: TEIID60019 Streaming result has already been read once. Ensure that only one read operation needs to be performed, for example XMLPARSE without the WELLFORMED operation must read the entire stream to validate its contents. Or you may choose to use a non-streaming result. '. Originally TeiidProcessingException BinaryWSProcedureExecution.java:78. Enable more detailed logging to see the entire stacktrace.
> {code}
> Teiid command log for querry:
> {code:java}
> 15:11:34,160 INFO [org.teiid.COMMAND_LOG] (New I/O worker #2) 8O9K6VJIjd9y START USER COMMAND: startTime=2017-10-09 15:11:34.16 requestID=8O9K6VJIjd9y.7 txID=null sessionID=8O9K6VJIjd9y applicationName=JDBC principal=user@teiid-security vdbName=csv vdbVersion=1 sql=SELECT IntKey, StringKey, lookup('BQT1.SmallB', 'DoubleNum', 'IntKey', IntKey) AS DoubleNum FROM BQT1.SmallA UNION SELECT IntKey, lookup('BQT1.SmallA', 'StringKey', 'IntKey', IntKey) AS StringKey, DoubleNum FROM BQT1.SmallA
> 15:11:34,192 DEBUG [org.teiid.COMMAND_LOG] (Worker0_QueryProcessorQueue40) 8O9K6VJIjd9y START DATA SRC COMMAND: startTime=2017-10-09 15:11:34.192 requestID=8O9K6VJIjd9y.7 sourceCommandID=6 executionID=2 txID=null modelName=sourceModel translatorName=user-s3 sessionID=8O9K6VJIjd9y principal=user@teiid-security sql=EXEC sourceModel.getTextFile('csv/smallaCsv.csv')
> 15:11:34,856 DEBUG [org.teiid.COMMAND_LOG] (Worker2_QueryProcessorQueue41) 8O9K6VJIjd9y END SRC COMMAND: endTime=2017-10-09 15:11:34.856 requestID=8O9K6VJIjd9y.7 sourceCommandID=6 executionID=2 txID=null modelName=sourceModel translatorName=user-s3 sessionID=8O9K6VJIjd9y principal=user@teiid-security finalRowCount=1 cpuTime(ns)=34673443
> 15:11:34,862 INFO [org.teiid.COMMAND_LOG] (Worker2_QueryProcessorQueue41) 8O9K6VJIjd9y ERROR USER COMMAND: endTime=2017-10-09 15:11:34.862 requestID=8O9K6VJIjd9y.7 txID=null sessionID=8O9K6VJIjd9y principal=user@teiid-security vdbName=csv vdbVersion=1 finalRowCount=null
> 15:11:34,876 INFO [org.teiid.COMMAND_LOG] (Worker2_QueryProcessorQueue41) 8O9K6VJIjd9y END USER COMMAND: endTime=2017-10-09 15:11:34.876 requestID=8O9K6VJIjd9y.7 txID=null sessionID=8O9K6VJIjd9y principal=user@teiid-security vdbName=csv vdbVersion=1 finalRowCount=0
> {code}
--
This message was sent by Atlassian JIRA
(v7.5.0#75005)
7 years, 3 months
[JBoss JIRA] (TEIID-5098) Unsyncronized static date format for Salesforce bulk inserts
by Steven Hawkins (JIRA)
Steven Hawkins created TEIID-5098:
-------------------------------------
Summary: Unsyncronized static date format for Salesforce bulk inserts
Key: TEIID-5098
URL: https://issues.jboss.org/browse/TEIID-5098
Project: Teiid
Issue Type: Bug
Components: Salesforce Connector
Reporter: Steven Hawkins
Assignee: Steven Hawkins
Priority: Critical
Fix For: 10.0, 9.3.4, 9.2.7
The date formatter used for bulk updates is static and unsynchronized, thus unpredictable errors or data corruption could occur with running concurrent bulk inserts.
--
This message was sent by Atlassian JIRA
(v7.5.0#75005)
7 years, 3 months
[JBoss JIRA] (TEIID-5097) Cannot run time-based queries against Osisoft PI
by Ramesh Reddy (JIRA)
[ https://issues.jboss.org/browse/TEIID-5097?page=com.atlassian.jira.plugin... ]
Ramesh Reddy edited comment on TEIID-5097 at 10/11/17 11:53 AM:
----------------------------------------------------------------
[~asmigala] You should be issuing queries in Teiid dialect like
{code}
select * from dvqe.Data.Archive a where a.time BETWEEN TIMESTAMPADD(SQL_TSI_DAY, -14, now()) AND now()
{code}
I do not expect Teiid understanding Pi semantics of time notation unless it ANSI SQL format. There may be a way to error out, I am not sure. [~shawkins] might know.
was (Author: rareddy):
[~asmigala] You should be issuing queries in Teiid dialect like
{code}
select * from dvqe.Data.Archive a where a.time BETWEEN TIMESTAMPADD(SQL_TSI_DAY, -14, now()) AND now()
{code}
> Cannot run time-based queries against Osisoft PI
> ------------------------------------------------
>
> Key: TEIID-5097
> URL: https://issues.jboss.org/browse/TEIID-5097
> Project: Teiid
> Issue Type: Bug
> Components: JDBC Connector
> Affects Versions: 8.12.x-6.4
> Reporter: Andrej Šmigala
> Assignee: Steven Hawkins
> Priority: Blocker
>
> Osisoft PI supports a relative time literals syntax, e.g.
> {code:sql}
> select * from dvqe.Data.Archive a where a.time between '*-14d' and '*'
> {code}
> will select all data between right now and 14 days ago, and
> {code:sql}
> select * from dvqe.Data.Archive a where a.time > 'y'
> {code}
> will select all data after yesterday midnight.
> The string literals are converted to time values in the PI Server
> Running the same queries through teiid however returns incorrect results, because teiid pushes a cast to string on the Time column, which results in string comparison on the datasource:
> {code:sql|title=Pushed query}
> SELECT TOP 100 cast(g_0.[ElementAttributeID] as String), g_0.[Time] AS c_1, g_0.[Value] AS c_2, g_0.[ValueInt] AS c_3, g_0.
> [ValueDbl] AS c_4, g_0.[ValueStr] AS c_5, cast(g_0.[ValueGuid] as String), g_0.[ValueDateTime] AS c_7, g_0.[Status] AS c_8, g_0.[Annotated] AS c_9, g_0.[IsGood] A
> S c_10, g_0.[Questionable] AS c_11, g_0.[Substituted] AS c_12 FROM [dvqe].[Data].[Archive] AS g_0 WHERE cast(g_0.[Time] AS String) > 'y'
> {code}
--
This message was sent by Atlassian JIRA
(v7.5.0#75005)
7 years, 3 months
[JBoss JIRA] (TEIID-5097) Cannot run time-based queries against Osisoft PI
by Ramesh Reddy (JIRA)
[ https://issues.jboss.org/browse/TEIID-5097?page=com.atlassian.jira.plugin... ]
Ramesh Reddy commented on TEIID-5097:
-------------------------------------
[~asmigala] You should be issuing queries in Teiid dialect like
{code}
select * from dvqe.Data.Archive a where a.time BETWEEN TIMESTAMPADD(SQL_TSI_DAY, -14, now()) AND now()
{code}
> Cannot run time-based queries against Osisoft PI
> ------------------------------------------------
>
> Key: TEIID-5097
> URL: https://issues.jboss.org/browse/TEIID-5097
> Project: Teiid
> Issue Type: Bug
> Components: JDBC Connector
> Affects Versions: 8.12.x-6.4
> Reporter: Andrej Šmigala
> Assignee: Steven Hawkins
> Priority: Blocker
>
> Osisoft PI supports a relative time literals syntax, e.g.
> {code:sql}
> select * from dvqe.Data.Archive a where a.time between '*-14d' and '*'
> {code}
> will select all data between right now and 14 days ago, and
> {code:sql}
> select * from dvqe.Data.Archive a where a.time > 'y'
> {code}
> will select all data after yesterday midnight.
> The string literals are converted to time values in the PI Server
> Running the same queries through teiid however returns incorrect results, because teiid pushes a cast to string on the Time column, which results in string comparison on the datasource:
> {code:sql|title=Pushed query}
> SELECT TOP 100 cast(g_0.[ElementAttributeID] as String), g_0.[Time] AS c_1, g_0.[Value] AS c_2, g_0.[ValueInt] AS c_3, g_0.
> [ValueDbl] AS c_4, g_0.[ValueStr] AS c_5, cast(g_0.[ValueGuid] as String), g_0.[ValueDateTime] AS c_7, g_0.[Status] AS c_8, g_0.[Annotated] AS c_9, g_0.[IsGood] A
> S c_10, g_0.[Questionable] AS c_11, g_0.[Substituted] AS c_12 FROM [dvqe].[Data].[Archive] AS g_0 WHERE cast(g_0.[Time] AS String) > 'y'
> {code}
--
This message was sent by Atlassian JIRA
(v7.5.0#75005)
7 years, 3 months
[JBoss JIRA] (TEIID-5096) Using /*+ MAKEDEP */ blocks the deploy proces when using DDL based vdb
by Steven Hawkins (JIRA)
[ https://issues.jboss.org/browse/TEIID-5096?page=com.atlassian.jira.plugin... ]
Steven Hawkins commented on TEIID-5096:
---------------------------------------
This does seem odd. DDL VDBs with that hint seem to delpoy just fine. Can you provide a thread dump of when it appears to hang?
> Using /*+ MAKEDEP */ blocks the deploy proces when using DDL based vdb
> ----------------------------------------------------------------------
>
> Key: TEIID-5096
> URL: https://issues.jboss.org/browse/TEIID-5096
> Project: Teiid
> Issue Type: Bug
> Affects Versions: 9.3.3
> Reporter: Bram Gadeyne
> Assignee: Steven Hawkins
>
> Hi,
> I've created a vdb that is depoyed using a vdb-ddl.ddl file.
> The deployment just stops and returned no error message when adding the SQL part below. Subsequent deployments are not handles. It seems like the deployment process just hangs. The only way to resolve this was to stop the wildfly server, remove the latest added content from the standalone/data/content directory and restart wildfly.
> {code:sql}
> CREATE VIEW tv_retrieve_monvals(
> admissionid integer not null,
> VariableID integer not null,
> Datetime timestamp not null,
> Entertime timestamp not null,
> varvalue double not null,
> primary key (admissionid, VariableID, Datetime)
> )
> AS
> SELECT v.PatientID AS admissionid, v.VariableID, v.Datetime, v.Entertime, v."Value" AS varvalue
> FROM (
> SELECT mv.PatientID, mv.VariableID, mv.Datetime, mv.Entertime, mv."Value",
> ROW_NUMBER() OVER (PARTITION BY mv.PatientID, mv.VariableID, mv.Datetime ORDER BY mv.Entertime DESC) AS rang
> FROM izisprod.P_GeneralData AS gd
> INNER JOIN /*+ MAKEDEP */ izisprod.P_MonVals AS mv ON
> gd.PatientID = mv.PatientID AND
> bitand(mv.Status, 8) = 8 AND
> bitand(mv.Status, 2) <> 2
> WHERE gd.Status = 1 OR (gd.Status >= 4 AND gd.Status <> 5)
> UNION
> SELECT mv.PatientID, mv.VariableID, mv.Datetime, mv.Entertime, mv."Value",
> ROW_NUMBER() OVER (PARTITION BY mv.PatientID, mv.VariableID, mv.Datetime ORDER BY mv.Entertime DESC) AS rang
> FROM iziswh.P_GeneralData AS gd
> INNER JOIN /*+ MAKEDEP */ iziswh.P_MonVals AS mv ON
> gd.PatientID = mv.PatientID AND
> bitand(mv.Status, 8) = 8 AND
> bitand(mv.Status, 2) <> 2
> ) AS v
> WHERE v.rang = 1;
> {code}
> Removing the /*+ MAKDEP */ references resolves the issue.
> I've tried creating a small ddl script that uses /*+ MAKEDEP */ to reproduce this issue but this script does deploy so currently I can not make a small example.
--
This message was sent by Atlassian JIRA
(v7.5.0#75005)
7 years, 3 months
[JBoss JIRA] (TEIID-4748) Provide translator/connector for calling Java api
by Steven Hawkins (JIRA)
[ https://issues.jboss.org/browse/TEIID-4748?page=com.atlassian.jira.plugin... ]
Steven Hawkins updated TEIID-4748:
----------------------------------
Fix Version/s: Open To Community
> Provide translator/connector for calling Java api
> -------------------------------------------------
>
> Key: TEIID-4748
> URL: https://issues.jboss.org/browse/TEIID-4748
> Project: Teiid
> Issue Type: Feature Request
> Components: Misc. Connectors
> Reporter: Van Halbert
> Assignee: Van Halbert
> Fix For: Open To Community
>
>
> Provide a translator/connector for doing the following:
> - calling a java method on an API that returns an object (or collection)
> - the return object should be exposed as a table
> - the resource adapter should be able to provide the connection to the java api (either remote or local)
> This scenario doesn't work with the current object translator, as its based on reading from a cache (map) and the connection methods are exposed based on that scenario.
> The object translator has, at its core, the base for handling all the reflection logic for reading and writing on a java object. It may make sense to refactor out the core logic for reuse.
--
This message was sent by Atlassian JIRA
(v7.5.0#75005)
7 years, 3 months
[JBoss JIRA] (TEIID-5097) Cannot run time-based queries against Osisoft PI
by RH Bugzilla Integration (JIRA)
[ https://issues.jboss.org/browse/TEIID-5097?page=com.atlassian.jira.plugin... ]
RH Bugzilla Integration updated TEIID-5097:
-------------------------------------------
Bugzilla References: https://bugzilla.redhat.com/show_bug.cgi?id=1500814
Bugzilla Update: Perform
> Cannot run time-based queries against Osisoft PI
> ------------------------------------------------
>
> Key: TEIID-5097
> URL: https://issues.jboss.org/browse/TEIID-5097
> Project: Teiid
> Issue Type: Bug
> Components: JDBC Connector
> Affects Versions: 8.12.x-6.4
> Reporter: Andrej Šmigala
> Assignee: Steven Hawkins
> Priority: Blocker
>
> Osisoft PI supports a relative time literals syntax, e.g.
> {code:sql}
> select * from dvqe.Data.Archive a where a.time between '*-14d' and '*'
> {code}
> will select all data between right now and 14 days ago, and
> {code:sql}
> select * from dvqe.Data.Archive a where a.time > 'y'
> {code}
> will select all data after yesterday midnight.
> The string literals are converted to time values in the PI Server
> Running the same queries through teiid however returns incorrect results, because teiid pushes a cast to string on the Time column, which results in string comparison on the datasource:
> {code:sql|title=Pushed query}
> SELECT TOP 100 cast(g_0.[ElementAttributeID] as String), g_0.[Time] AS c_1, g_0.[Value] AS c_2, g_0.[ValueInt] AS c_3, g_0.
> [ValueDbl] AS c_4, g_0.[ValueStr] AS c_5, cast(g_0.[ValueGuid] as String), g_0.[ValueDateTime] AS c_7, g_0.[Status] AS c_8, g_0.[Annotated] AS c_9, g_0.[IsGood] A
> S c_10, g_0.[Questionable] AS c_11, g_0.[Substituted] AS c_12 FROM [dvqe].[Data].[Archive] AS g_0 WHERE cast(g_0.[Time] AS String) > 'y'
> {code}
--
This message was sent by Atlassian JIRA
(v7.5.0#75005)
7 years, 3 months
[JBoss JIRA] (TEIID-5097) Cannot run time-based queries against Osisoft PI
by Andrej Šmigala (JIRA)
Andrej Šmigala created TEIID-5097:
-------------------------------------
Summary: Cannot run time-based queries against Osisoft PI
Key: TEIID-5097
URL: https://issues.jboss.org/browse/TEIID-5097
Project: Teiid
Issue Type: Bug
Components: JDBC Connector
Affects Versions: 8.12.x-6.4
Reporter: Andrej Šmigala
Assignee: Steven Hawkins
Priority: Blocker
Osisoft PI supports a relative time literals syntax, e.g.
{code:sql}
select * from dvqe.Data.Archive a where a.time between '*-14d' and '*'
{code}
will select all data between right now and 14 days ago, and
{code:sql}
select * from dvqe.Data.Archive a where a.time > 'y'
{code}
will select all data after yesterday midnight.
The string literals are converted to time values in the PI Server
Running the same queries through teiid however returns incorrect results, because teiid pushes a cast to string on the Time column, which results in string comparison on the datasource:
{code:sql|title=Pushed query}
SELECT TOP 100 cast(g_0.[ElementAttributeID] as String), g_0.[Time] AS c_1, g_0.[Value] AS c_2, g_0.[ValueInt] AS c_3, g_0.
[ValueDbl] AS c_4, g_0.[ValueStr] AS c_5, cast(g_0.[ValueGuid] as String), g_0.[ValueDateTime] AS c_7, g_0.[Status] AS c_8, g_0.[Annotated] AS c_9, g_0.[IsGood] A
S c_10, g_0.[Questionable] AS c_11, g_0.[Substituted] AS c_12 FROM [dvqe].[Data].[Archive] AS g_0 WHERE cast(g_0.[Time] AS String) > 'y'
{code}
--
This message was sent by Atlassian JIRA
(v7.5.0#75005)
7 years, 3 months