October 2016 - teiid-issues - Jboss List Archives

[JBoss JIRA] (TEIID-4284) Implement Salesforce Bulk API for SELECTS to Salesforce Connector

by sameer P (JIRA)

[ https://issues.jboss.org/browse/TEIID-4284?page=com.atlassian.jira.plugin... ] sameer P commented on TEIID-4284: --------------------------------- Got it. Thanks for the explanation Steven. Yeah looks like the *Completed* state is never reached. Unfortunately it happened the same with other tables too. > Implement Salesforce Bulk API for SELECTS to Salesforce Connector > ----------------------------------------------------------------- > > Key: TEIID-4284 > URL: https://issues.jboss.org/browse/TEIID-4284 > Project: Teiid > Issue Type: Feature Request > Components: Salesforce Connector > Affects Versions: 8.13.5 > Environment: With Salesforce datasource > Reporter: sameer P > Assignee: Steven Hawkins > Fix For: 9.1 > > > There is some huge data (many GBs) in the Salesforce which has around 1.5 million rows and doing some simple select * on it fails with QUERY_TIMEOUT. > The salesforce guys suggested to try Bulk API for select with PK chunking as stated in https://developer.salesforce.com/docs/atlas.en-us.api_asynch.meta/api_asy... . -- This message was sent by Atlassian JIRA (v6.4.11#64026)

8 years, 2 months

1
0
0 / 0

[JBoss JIRA] (TEIID-4469) Insert with query expression does not apply source hint to target

by Lucie Fabrikova (JIRA)

[ https://issues.jboss.org/browse/TEIID-4469?page=com.atlassian.jira.plugin... ] Lucie Fabrikova updated TEIID-4469: ----------------------------------- Attachment: oracledba-vdb.xml > Insert with query expression does not apply source hint to target > ----------------------------------------------------------------- > > Key: TEIID-4469 > URL: https://issues.jboss.org/browse/TEIID-4469 > Project: Teiid > Issue Type: Bug > Components: Query Engine > Affects Versions: 8.7.1.6_2 > Reporter: Marc Shirley > Assignee: Steven Hawkins > Fix For: 9.1, 9.0.5, 8.7.10_6.2 > > Attachments: oracledba-vdb.xml > > > "INSERT INTO ... SELECT ..." statements result in a ProjectIntoNode which seems to prevent any source hints from being passed down to the relevant source. These work correctly with an "INSERT INTO ... VALUES ..." statement. I've included example user and final query plan data below for the non-working [1] and working [2] insert statements. > [1] source hint not applied to ProjectIntoNode: > USER COMMAND: > INSERT /*+sh test:'append' */ INTO test.TEST (ID, "VALUE") SELECT X.id AS ID, X."value" AS "VALUE" FROM (SELECT bqt.SMALLA.INTKEY AS id, bqt.SMALLA.INTNUM AS "value" FROM bqt.SMALLA WHERE bqt.SMALLA.INTKEY = 0) AS X > ... > OPTIMIZATION COMPLETE: > PROCESSOR PLAN: > ProjectIntoNode(0) output=[Count] test.TEST > AccessNode(1) output=[bqt.SMALLA.INTKEY AS ID, bqt.SMALLA.INTNUM AS "VALUE"] SELECT /*+sh test:'append' */ g_0.INTKEY, g_0.INTNUM FROM bqt.SMALLA AS g_0 WHERE g_0.INTKEY = 0 > [2] source hint applied to AccessNode: > USER COMMAND: > INSERT /*+sh test:'append' */ INTO test.TEST (ID, "VALUE") VALUES ('-1', '-1') > ... > OPTIMIZATION COMPLETE: > PROCESSOR PLAN: > AccessNode(0) output=[Count] INSERT /*+sh test:'append' */ INTO test.TEST (ID, "VALUE") VALUES ('-1', '-1') -- This message was sent by Atlassian JIRA (v6.4.11#64026)

8 years, 2 months

1
0
0 / 0

[JBoss JIRA] (TEIID-4284) Implement Salesforce Bulk API for SELECTS to Salesforce Connector

by Steven Hawkins (JIRA)

[ https://issues.jboss.org/browse/TEIID-4284?page=com.atlassian.jira.plugin... ] Steven Hawkins commented on TEIID-4284: --------------------------------------- > And the only concern is that the query runs for a really long time (never stopped on its own still) with the below statements in the log After the initial blocking, the connector will retry the getBatchInfo https://github.com/teiid/teiid/blob/master/connectors/connector-salesforc... operation on a fixed interval. So you should see those logs repeated correct? Following https://developer.salesforce.com/docs/atlas.en-us.api_asynch.meta/api_asy... and with my local testing the code expects salesforce to return a state of Completed. What you are seeing suggests that you are never reaching that state. > Implement Salesforce Bulk API for SELECTS to Salesforce Connector > ----------------------------------------------------------------- > > Key: TEIID-4284 > URL: https://issues.jboss.org/browse/TEIID-4284 > Project: Teiid > Issue Type: Feature Request > Components: Salesforce Connector > Affects Versions: 8.13.5 > Environment: With Salesforce datasource > Reporter: sameer P > Assignee: Steven Hawkins > Fix For: 9.1 > > > There is some huge data (many GBs) in the Salesforce which has around 1.5 million rows and doing some simple select * on it fails with QUERY_TIMEOUT. > The salesforce guys suggested to try Bulk API for select with PK chunking as stated in https://developer.salesforce.com/docs/atlas.en-us.api_asynch.meta/api_asy... . -- This message was sent by Atlassian JIRA (v6.4.11#64026)

8 years, 2 months

1
0
0 / 0

[JBoss JIRA] (TEIID-4284) Implement Salesforce Bulk API for SELECTS to Salesforce Connector

by sameer P (JIRA)

[ https://issues.jboss.org/browse/TEIID-4284?page=com.atlassian.jira.plugin... ] sameer P edited comment on TEIID-4284 at 10/10/16 11:16 AM: ------------------------------------------------------------ I got your point [~shawkins], here, the query should be {code:java} SELECT /*+ sh sf:'bulk' */ * FROM "source"."test_table_vz__c" {code} Correct? . I ran it . And the only concern is that the query runs for a really long time (never stopped on its own still) with the below statements in the log: {code:java} 17:15:51,743 DEBUG [org.teiid.BUFFER_MGR] (Worker4_QueryProcessorQueue1010) gL9WQ/EygEZA gL9WQ/EygEZA.10.0.2 Blocking on DataNotAvailableException gL9WQ/EygEZA.10.0.2 17:15:51,744 DEBUG [org.teiid.BUFFER_MGR] (Worker4_QueryProcessorQueue1010) gL9WQ/EygEZA gL9WQ/EygEZA.10 Blocking on source request(s). 17:15:51,744 DEBUG [org.teiid.PROCESSOR] (Worker4_QueryProcessorQueue1010) gL9WQ/EygEZA Request Thread gL9WQ/EygEZA.10 - processor blocked {code} was (Author: sameerp): I got your point [~shawkins], here, the query should be {code:java} SELECT /*+ sh sf:'bulk' */ * FROM "source"."test_table_vz__c" {code} Correct? . I ran it . And the only concern is that the query runs for a really long time (never stopped on its own still) with the below statements in the log: {code:java} 17:12:59,116 DEBUG [org.teiid.BUFFER_MGR] (Worker3_QueryProcessorQueue578) gL9WQ/EygEZA gL9WQ/EygEZA.9 Blocking on source request(s). 17:12:59,116 DEBUG [org.teiid.PROCESSOR] (Worker3_QueryProcessorQueue578) gL9WQ/EygEZA Request Thread gL9WQ/EygEZA.9 - processor blocke {code} > Implement Salesforce Bulk API for SELECTS to Salesforce Connector > ----------------------------------------------------------------- > > Key: TEIID-4284 > URL: https://issues.jboss.org/browse/TEIID-4284 > Project: Teiid > Issue Type: Feature Request > Components: Salesforce Connector > Affects Versions: 8.13.5 > Environment: With Salesforce datasource > Reporter: sameer P > Assignee: Steven Hawkins > Fix For: 9.1 > > > There is some huge data (many GBs) in the Salesforce which has around 1.5 million rows and doing some simple select * on it fails with QUERY_TIMEOUT. > The salesforce guys suggested to try Bulk API for select with PK chunking as stated in https://developer.salesforce.com/docs/atlas.en-us.api_asynch.meta/api_asy... . -- This message was sent by Atlassian JIRA (v6.4.11#64026)

8 years, 2 months

1
0
0 / 0

[JBoss JIRA] (TEIID-4284) Implement Salesforce Bulk API for SELECTS to Salesforce Connector

by Steven Hawkins (JIRA)

[ https://issues.jboss.org/browse/TEIID-4284?page=com.atlassian.jira.plugin... ] Steven Hawkins commented on TEIID-4284: --------------------------------------- Please revert any changes you have and use the query: SELECT /*+ sh sf:'bulk' */ * FROM "source"."test_table_vz__c" Unfortunately to accommodate multi-source scenarios, we expect the source hint to match the source name, not the model name. > Implement Salesforce Bulk API for SELECTS to Salesforce Connector > ----------------------------------------------------------------- > > Key: TEIID-4284 > URL: https://issues.jboss.org/browse/TEIID-4284 > Project: Teiid > Issue Type: Feature Request > Components: Salesforce Connector > Affects Versions: 8.13.5 > Environment: With Salesforce datasource > Reporter: sameer P > Assignee: Steven Hawkins > Fix For: 9.1 > > > There is some huge data (many GBs) in the Salesforce which has around 1.5 million rows and doing some simple select * on it fails with QUERY_TIMEOUT. > The salesforce guys suggested to try Bulk API for select with PK chunking as stated in https://developer.salesforce.com/docs/atlas.en-us.api_asynch.meta/api_asy... . -- This message was sent by Atlassian JIRA (v6.4.11#64026)

8 years, 2 months

1
0
0 / 0

[JBoss JIRA] (TEIID-4469) Insert with query expression does not apply source hint to target

by Steven Hawkins (JIRA)

[ https://issues.jboss.org/browse/TEIID-4469?page=com.atlassian.jira.plugin... ] Steven Hawkins commented on TEIID-4469: --------------------------------------- Please provide your vdb or confirm what the source names are for the Oracle sources. As I see the source hint being applied to the Source model query: SELECT /*+sh Source2:'append' */ g_0.intkey, g_0.intnum FROM Source.smalla AS g_0 WHERE g_0.intkey = 1 > Insert with query expression does not apply source hint to target > ----------------------------------------------------------------- > > Key: TEIID-4469 > URL: https://issues.jboss.org/browse/TEIID-4469 > Project: Teiid > Issue Type: Bug > Components: Query Engine > Affects Versions: 8.7.1.6_2 > Reporter: Marc Shirley > Assignee: Steven Hawkins > Fix For: 9.1, 9.0.5, 8.7.10_6.2 > > > "INSERT INTO ... SELECT ..." statements result in a ProjectIntoNode which seems to prevent any source hints from being passed down to the relevant source. These work correctly with an "INSERT INTO ... VALUES ..." statement. I've included example user and final query plan data below for the non-working [1] and working [2] insert statements. > [1] source hint not applied to ProjectIntoNode: > USER COMMAND: > INSERT /*+sh test:'append' */ INTO test.TEST (ID, "VALUE") SELECT X.id AS ID, X."value" AS "VALUE" FROM (SELECT bqt.SMALLA.INTKEY AS id, bqt.SMALLA.INTNUM AS "value" FROM bqt.SMALLA WHERE bqt.SMALLA.INTKEY = 0) AS X > ... > OPTIMIZATION COMPLETE: > PROCESSOR PLAN: > ProjectIntoNode(0) output=[Count] test.TEST > AccessNode(1) output=[bqt.SMALLA.INTKEY AS ID, bqt.SMALLA.INTNUM AS "VALUE"] SELECT /*+sh test:'append' */ g_0.INTKEY, g_0.INTNUM FROM bqt.SMALLA AS g_0 WHERE g_0.INTKEY = 0 > [2] source hint applied to AccessNode: > USER COMMAND: > INSERT /*+sh test:'append' */ INTO test.TEST (ID, "VALUE") VALUES ('-1', '-1') > ... > OPTIMIZATION COMPLETE: > PROCESSOR PLAN: > AccessNode(0) output=[Count] INSERT /*+sh test:'append' */ INTO test.TEST (ID, "VALUE") VALUES ('-1', '-1') -- This message was sent by Atlassian JIRA (v6.4.11#64026)

8 years, 2 months

1
0
0 / 0

[JBoss JIRA] (TEIID-4284) Implement Salesforce Bulk API for SELECTS to Salesforce Connector

by sameer P (JIRA)

[ https://issues.jboss.org/browse/TEIID-4284?page=com.atlassian.jira.plugin... ] sameer P commented on TEIID-4284: --------------------------------- I got your point [~shawkins], here, the query should be {code:java} SELECT /*+ sh sf:'bulk' */ * FROM "source"."test_table_vz__c" {code} Correct? . I ran it . And the only concern is that the query runs for a really long time (never stopped on its own still) with the below statements in the log: {code:java} 17:12:59,116 DEBUG [org.teiid.BUFFER_MGR] (Worker3_QueryProcessorQueue578) gL9WQ/EygEZA gL9WQ/EygEZA.9 Blocking on source request(s). 17:12:59,116 DEBUG [org.teiid.PROCESSOR] (Worker3_QueryProcessorQueue578) gL9WQ/EygEZA Request Thread gL9WQ/EygEZA.9 - processor blocke {code} > Implement Salesforce Bulk API for SELECTS to Salesforce Connector > ----------------------------------------------------------------- > > Key: TEIID-4284 > URL: https://issues.jboss.org/browse/TEIID-4284 > Project: Teiid > Issue Type: Feature Request > Components: Salesforce Connector > Affects Versions: 8.13.5 > Environment: With Salesforce datasource > Reporter: sameer P > Assignee: Steven Hawkins > Fix For: 9.1 > > > There is some huge data (many GBs) in the Salesforce which has around 1.5 million rows and doing some simple select * on it fails with QUERY_TIMEOUT. > The salesforce guys suggested to try Bulk API for select with PK chunking as stated in https://developer.salesforce.com/docs/atlas.en-us.api_asynch.meta/api_asy... . -- This message was sent by Atlassian JIRA (v6.4.11#64026)

8 years, 2 months

1
0
0 / 0

[JBoss JIRA] (TEIID-4469) Insert with query expression does not apply source hint to target

by Lucie Fabrikova (JIRA)

[ https://issues.jboss.org/browse/TEIID-4469?page=com.atlassian.jira.plugin... ] Lucie Fabrikova commented on TEIID-4469: ---------------------------------------- I was testing DV 6.2.9, I also checked the source code delivered together with it and it contained the fixes. > Insert with query expression does not apply source hint to target > ----------------------------------------------------------------- > > Key: TEIID-4469 > URL: https://issues.jboss.org/browse/TEIID-4469 > Project: Teiid > Issue Type: Bug > Components: Query Engine > Affects Versions: 8.7.1.6_2 > Reporter: Marc Shirley > Assignee: Steven Hawkins > Fix For: 9.1, 9.0.5, 8.7.10_6.2 > > > "INSERT INTO ... SELECT ..." statements result in a ProjectIntoNode which seems to prevent any source hints from being passed down to the relevant source. These work correctly with an "INSERT INTO ... VALUES ..." statement. I've included example user and final query plan data below for the non-working [1] and working [2] insert statements. > [1] source hint not applied to ProjectIntoNode: > USER COMMAND: > INSERT /*+sh test:'append' */ INTO test.TEST (ID, "VALUE") SELECT X.id AS ID, X."value" AS "VALUE" FROM (SELECT bqt.SMALLA.INTKEY AS id, bqt.SMALLA.INTNUM AS "value" FROM bqt.SMALLA WHERE bqt.SMALLA.INTKEY = 0) AS X > ... > OPTIMIZATION COMPLETE: > PROCESSOR PLAN: > ProjectIntoNode(0) output=[Count] test.TEST > AccessNode(1) output=[bqt.SMALLA.INTKEY AS ID, bqt.SMALLA.INTNUM AS "VALUE"] SELECT /*+sh test:'append' */ g_0.INTKEY, g_0.INTNUM FROM bqt.SMALLA AS g_0 WHERE g_0.INTKEY = 0 > [2] source hint applied to AccessNode: > USER COMMAND: > INSERT /*+sh test:'append' */ INTO test.TEST (ID, "VALUE") VALUES ('-1', '-1') > ... > OPTIMIZATION COMPLETE: > PROCESSOR PLAN: > AccessNode(0) output=[Count] INSERT /*+sh test:'append' */ INTO test.TEST (ID, "VALUE") VALUES ('-1', '-1') -- This message was sent by Atlassian JIRA (v6.4.11#64026)

8 years, 2 months

1
0
0 / 0

[JBoss JIRA] (TEIID-4284) Implement Salesforce Bulk API for SELECTS to Salesforce Connector

by sameer P (JIRA)

[ https://issues.jboss.org/browse/TEIID-4284?page=com.atlassian.jira.plugin... ] sameer P commented on TEIID-4284: --------------------------------- here, *source* itself is the name of the datasource configured as shown below: {code:java} <vdb name="test" version="1"> <description>Test VDB</description>  <property name="UseConnectorMetadata" value="true" />  <model visible="true" name="source"> <source name="sf" translator-name="salesforce-34" connection-jndi-name="java:/salesforce34"/> </model> </vdb> {code} And by infinite loop, I meant to say a long running non stop query (sorry for the non-precise word) . And it runs continously and won't come out. > Implement Salesforce Bulk API for SELECTS to Salesforce Connector > ----------------------------------------------------------------- > > Key: TEIID-4284 > URL: https://issues.jboss.org/browse/TEIID-4284 > Project: Teiid > Issue Type: Feature Request > Components: Salesforce Connector > Affects Versions: 8.13.5 > Environment: With Salesforce datasource > Reporter: sameer P > Assignee: Steven Hawkins > Fix For: 9.1 > > > There is some huge data (many GBs) in the Salesforce which has around 1.5 million rows and doing some simple select * on it fails with QUERY_TIMEOUT. > The salesforce guys suggested to try Bulk API for select with PK chunking as stated in https://developer.salesforce.com/docs/atlas.en-us.api_asynch.meta/api_asy... . -- This message was sent by Atlassian JIRA (v6.4.11#64026)

8 years, 2 months

1
0
0 / 0

[JBoss JIRA] (TEIID-4284) Implement Salesforce Bulk API for SELECTS to Salesforce Connector

by Steven Hawkins (JIRA)

[ https://issues.jboss.org/browse/TEIID-4284?page=com.atlassian.jira.plugin... ] Steven Hawkins commented on TEIID-4284: --------------------------------------- > where source is the Salesforce Datasource source is the source name (not translator nor model name) in the vdb? {code} <model ...> <source name="source" ... </model> {code} > And about changing the source, I just removed this condition from the if block. I do not see how the behavior you are describing would arise. Where is an infinite loop coming into play? > Implement Salesforce Bulk API for SELECTS to Salesforce Connector > ----------------------------------------------------------------- > > Key: TEIID-4284 > URL: https://issues.jboss.org/browse/TEIID-4284 > Project: Teiid > Issue Type: Feature Request > Components: Salesforce Connector > Affects Versions: 8.13.5 > Environment: With Salesforce datasource > Reporter: sameer P > Assignee: Steven Hawkins > Fix For: 9.1 > > > There is some huge data (many GBs) in the Salesforce which has around 1.5 million rows and doing some simple select * on it fails with QUERY_TIMEOUT. > The salesforce guys suggested to try Bulk API for select with PK chunking as stated in https://developer.salesforce.com/docs/atlas.en-us.api_asynch.meta/api_asy... . -- This message was sent by Atlassian JIRA (v6.4.11#64026)

8 years, 2 months

1
0
0 / 0

2024

2023

2022

2021

2020

2019

2018

2017

2016

2015

2014

2013

2012

2011

2010

2009

teiid-issues October 2016