Re: Is it possible to load data (> 10,000 rows) to... - Page 2 - Qlik Community

elkhounds · ‎2019-07-22

Greetings,

My use case is to create a Talend job that calls Host Analytics' SOAP API to load data from a Oracle table (> 100,000 rows) into a hosted table. I was successful (with help from rhall_2_0) in creating a job that uses the tREST component to call Host Analytics' SOAP API to clear the data from the hosted table. It appears possible to load a few rows of data using tREST, but would like to know what the optimum approach is (and which components to use) for loading many rows (full refresh). And will it be necessary to use a different Talend product, such as API Designer,Cloud or ESB?

Let me know if you need more information.

Thanks

Anonymous · ‎2019-08-01

You need to print out the XML you are building and compare it to the hardcoded XML. It might help if you could post it here as text.

elkhounds · ‎2019-08-02

Here is the SOAP message that dynamically uses the data_merged variable that is constructed from the 24 column values in tMap and is the tSOAP one that fails:

"<soapenv:Envelope xmlns:soapenv=\"http://schemas.xmlsoap.org/soap/envelope/\" xmlns:ns=\"http://www.HostAnalytics.com/API/SOAP/StateFree/Common/2009/03/19\">
\r\n
<soapenv:Header/>
\r\n
<soapenv:Body>
\r\n
<ns:BulkStringLoadWithLogin>
<ns:LoginName>r*********</ns:LoginName>
<nsassword>********</nsassword>
<ns:TenantCode>sf218452</ns:TenantCode>
<ns:Rule>WS_Actual_Transaction_Detail_Load</ns:Rule>

<nsata>

<ns:string>data_merged</ns:string>

</nsata>

<ns:strDelimiter>|</ns:strDelimiter>
\r\n
</ns:BulkStringLoadWithLogin>
\r\n
</soapenv:Body>
\r\n
</soapenv:Envelope>
\r\n"

And here is the SOAP message that is hard-coded with the output of the data_merged variable that was constructed from the 24 column values in tMap and is the tSOAP one that succeeds:

"<soapenv:Envelope xmlns:soapenv=\"http://schemas.xmlsoap.org/soap/envelope/\" xmlns:ns=\"http://www.HostAnalytics.com/API/SOAP/StateFree/Common/2009/03/19\">
\r\n
<soapenv:Header/>
\r\n
<soapenv:Body>
\r\n
<ns:BulkStringLoadWithLogin>
<ns:LoginName>*********</ns:LoginName>
<nsassword>*********</nsassword>
<ns:TenantCode>sf218452</ns:TenantCode>
<ns:Rule>WS_Actual_Transaction_Detail_Load</ns:Rule>

<nsata>

<ns:string>2018|5|FA0000011|0|278.83|-278.83|DEPRECIATION|default|default|0000001952|20180531|default|default|default|US105|9000|125300|998|Accumulated Depreciation - Mac|20180525|152|USD|89|20180525</ns:string>

</nsata>

<ns:strDelimiter>|</ns:strDelimiter>
\r\n
</ns:BulkStringLoadWithLogin>
\r\n
</soapenv:Body>
\r\n
</soapenv:Envelope>
\r\n"

And here is the tMap expression that constructs data_merged:

Thanks

Ray

Anonymous · ‎2019-08-02

The data_merged variable isn't a variable in your XML. It is a literal.

<ns:string>data_merged</ns:string>

You need to output your data_merged computed value to a column in a row coming out of the tMap. You also need to remove the <ns:string> and </ns:string> from your variable when it is created. Then add it to the XML body like this....

"<soapenv:Envelope xmlns:soapenv=\"http://schemas.xmlsoap.org/soap/envelope/\" xmlns:ns=\"http://www.HostAnalytics.com/API/SOAP/StateFree/Common/2009/03/19\">
\r\n
<soapenv:Header/>
\r\n
<soapenv:Body>
\r\n
<ns:BulkStringLoadWithLogin>
<ns:LoginName>r*********</ns:LoginName>
<nsSmiley Tongueassword>********</nsSmiley Tongueassword>
<ns:TenantCode>sf218452</ns:TenantCode>
<ns:Rule>WS_Actual_Transaction_Detail_Load</ns:Rule>


<nsSmiley Very Happyata>

<ns:string>"+row.data_merged_column+"</ns:string>

</nsSmiley Very Happyata>

<ns:strDelimiter>|</ns:strDelimiter>
\r\n
</ns:BulkStringLoadWithLogin>
\r\n
</soapenv:Body>
\r\n
</soapenv:Envelope>"

elkhounds · ‎2019-08-02

With your help and guidance, I was able to finally get it to work. What a relief.

Ray

Anonymous · ‎2019-08-05

No problem. Hopefully going through this has meant you have learned a lot about Talend as well 🙂

elkhounds · ‎2019-08-07

Greetings,

As mentioned before, I was able to get the job to work by concatenating the 24 column values into the data_merged variable for each row in tMap and using it in the tSOAP message for loading data into Host.

However, the performance is very poor because the tSOAP component is called for each row (> 10,000).

So, now that the job is functional, I'd like to re-visit your idea of merging each row concatenation into the data_merged variable, except I'd prefer to chunk the data (perhaps 1000 rows at a time) into the variable, then to tAggregateRow and then to the tSOAP and then loop through until all rows have been processed - tOracleInput---->tMap--->tAggregateRow--->tSOAP. I'm not sure which component needs to be added, perhaps tLoop?

For the new development, I want to be able to first merge 4 rows into the data_merged variable before figuring out how to chunk the data.

Similar to your initial example, here is the tMap expression that builds the data_merged variable:

The other tMap expression is simply: Var.data_merged

The tSOAP message is:

"<soapenv:Envelope xmlns:soapenv=\"http://schemas.xmlsoap.org/soap/envelope/\" xmlns:ns=\"http://www.HostAnalytics.com/API/SOAP/StateFree/Common/2009/03/19\">
\r\n
<soapenv:Header/>
\r\n
<soapenv:Body>
\r\n
<ns:BulkStringLoadWithLogin>
<ns:LoginName>********</ns:LoginName>
<nsassword>********</nsassword>
<ns:TenantCode>*******</ns:TenantCode>
<ns:Rule>WS_Actual_Transaction_Detail_Load</ns:Rule>

<nsata>
mergedRow.data_merged
</nsata>

<ns:strDelimiter>|</ns:strDelimiter>
\r\n
</ns:BulkStringLoadWithLogin>
\r\n
</soapenv:Body>
\r\n
</soapenv:Envelope>
\r\n"

When I run the job, I get the following error: <soap:Fault xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/"><faultcode>soaperver</faultcode><faultstring>System error:Index was outside the bounds of the array.</faultstring><faultactor>https://epm10.hostanalytics.com/HostAPI/HostAPI_StateFree.asmx</faultactor><detail><Exception xmlns="https://epm10.hostanalytics.com/HostAPI/"><ExceptionCode /><ExceptionMessage>Index was outside the bounds of the array.</ExceptionMessage><ExceptionSource>HostAnalytics.Web.AppCode</ExceptionSource></Exception></detail></soap:Fault>

When I capture the data_merged variable into tLogRow and then insert/hard code it into the tSOAP, the load is successful for all 4 rows.

Could you examine my code and see if something is incorrect?

And let me know if you want some screen shots.

Thank you for all your help,

ElkHounds

Anonymous · ‎2019-08-08

Assuming that the mergedrow.data_merged value is correct, it looks like your tSOAP concatenation is incorrect. You have to treat this as Java String manipulation. Your mergedRow.data_merged value is a String being concatenated with your SOAP String. Essentially it looks like you are missing the close quote before you add mergedRow.data_merged and the open quote after it.

I believe your code should look like this...

"<soapenv:Envelope xmlns:soapenv=\"http://schemas.xmlsoap.org/soap/envelope/\" xmlns:ns=\"http://www.HostAnalytics.com/API/SOAP/StateFree/Common/2009/03/19\">
\r\n
<soapenv:Header/>
\r\n
<soapenv:Body>
\r\n
<ns:BulkStringLoadWithLogin>
<ns:LoginName>********</ns:LoginName>
<ns:Password>********</ns:Password>
<ns:TenantCode>*******</ns:TenantCode>
<ns:Rule>WS_Actual_Transaction_Detail_Load</ns:Rule>


<ns:Data>"
+mergedRow.data_merged+
"</ns:Data>


<ns:strDelimiter>|</ns:strDelimiter>
\r\n
</ns:BulkStringLoadWithLogin>
\r\n
</soapenv:Body>
\r\n
</soapenv:Envelope>"

elkhounds · ‎2019-08-09

Greetings,

Your idea worked like a charm. Thanks!

<ns:Data>"
+mergedRow.data_merged+
"</ns:Data>

If you have time, how would you design iterating 1000 rows of data (or chunking) from an Oracle DB to the same tSOAP call?

I've been researching all the components's documentation related to looping, iterating data, etc, but its unclear what is the best approach. I've tried tLoop, but getting odd results.

Thanks

Anonymous · ‎2019-08-12

Depending on the query for your data, you might be able to make this really easy and use the Oracle Offset and Fetch functionality
https://docs.oracle.com/javadb/10.8.3.0/ref/rrefsqljoffsetfetch.html

You already have a job which builds your XML for a fixed number of records, all you should need to do here is a few calculations to extend that. Before your DB component, you will need a tLoop component. Take a look at the documentation for that here (https://help.talend.com/reader/o2I5HrOFZtZItmjCxsjUtQ/ytM~V~xXAM_KYaKSpqBKeQ). For every loop of the tLoop, you will get a Current Iteration value from a globalMap variable which can be used like this....

((Integer)globalMap.get("tLoop_1_CURRENT_ITERATION"))

....assuming that the tLoop is called tLoop_1.

Since you know you want 1000 rows per SOAP call, all you need to know is how many rows of data you have. Once you know that, you can calculate how many iterations you will need from the tLoop. Then you simply need to adjust your Offset in your DB component's WHERE Clause, by the iteration number you are on.

For example.....

"SELECT
MYDATA1,
MYDATA2
FROM MYTABLE
OFFSET " +(1000*(((Integer)globalMap.get("tLoop_1_CURRENT_ITERATION"))-1))+ " ROWS FETCH NEXT 1000 ROWS ONLY"

By the way, the above is an example and is not guaranteed to be perfect. However, this should get you in the right direction

elkhounds · ‎2019-08-16

All the jobs are up and running.

Thank you so much for all your help!

elkhounds

Is it possible to load data (> 10,000 rows) to Host Analytics using their SOAP API?

Talend Data Integration

v6.x