How to Batch 100 calls through tXMLMap

Highlighted
Six Stars

How to Batch 100 calls through tXMLMap

image.png

 

I have the above Job which will pick up more than 1000 rows from the database every night, the Soap service only accept 100 rows at a time, how should I perform this?

 

Your help is appreciated.

 


Accepted Solutions
Community Manager

Re: How to Batch 100 calls through tXMLMap

You could try with this relatively simple way of achieving this. The query below will need to be tailored to your table, but it should work...

 

 With multsOf1000 as (
 select 
 ID-999 'from_id',
 ID 'to_id'
 from MyTable
 where ID%1000=0
 ),

 remainder as (
	Select max(multsOf1000.to_id)+1 'from_id',
	max(batch.id) 'to_id'
	From multsOf1000, MyTable
 )

 select *
 from (
 select * 
 from multsOf1000
 
 UNION ALL

  select * 
 from remainder) tmp
 order by tmp.from_id

I can't guarantee that this will be super efficient, but essentially it is returning the multiples of 1000 in the first section. Then it is adding on the remainder record (the records after the last multiple of 1000). You may need to tweak this, but it should give you an idea of how to achieve this sort of thing. I am sure there are much better ways of achieving this, but this was the first that came to mind.

 


All Replies
Community Manager

Re: How to Batch 100 calls through tXMLMap

The easiest way to do this is to work out how you can limit your DB query to 100 rows of data. You might have an ID or a key somewhere else in the data set. You need to work out what you will use then add a new DB component before your Main DB component. Use this new DB component to simply return your Key values. So, for example, if your key is an Id and the ids are like below....

 

ID

1

2

3

4

5

6

7

8

9

10

 

....and you want to only return records in sets of 2, you could set your new query to simply return a 'from' and 'to' value using a query similar to this (pseudo code based on SQL Server)....

 

 select 
 ID-ID%2 as 'from',
 ID-(ID-1)%2 as 'to'
 from MyTable
 where ID%2=1

This will return something like below....

 

From To

1        2

3        4

5        6

7        8

9        10

 

Then connect your new DB component to a tFlowToIterate component and this to your Main DB component. All you need to do then is use the 'to' and 'from' values (stored in the globalMap) in the WHERE clause of your Main query.

 

What will happen is that your Main query will be run for every row returned by the query used to return your key (grouping) data. This will mean that the tXMLMap code will be run for each of these key sets. 

Six Stars

Re: How to Batch 100 calls through tXMLMap

I'll try your approach and will let you know.

 

Thanks!

Six Stars

Re: How to Batch 100 calls through tXMLMap

 

Unfortunately, there is no way to group the data I have in a sequential order.

 

Is there any other way I can do this? 

Community Manager

Re: How to Batch 100 calls through tXMLMap

You need to group the data to do this. There is always a way to group data in SQL. Have you tried using a With Clause to construct a data structure with an id field? 

Six Stars

Re: How to Batch 100 calls through tXMLMap

I think I'm doing a good progress, I'm getting a null exception, I have the below in my where clause, is it correct to use FirstIndex and SecondIndex in my Where clause?

 

SELECT ROW_NUMBER() OVER (ORDER BY PRSN_ID) AS ROW_NUM, WORK_EMAIL, PRSN_ID, FIRST_NAME, LAST_NAME
FROM MYREPORTS.MYTABLE
WHERE ACTIVE_EM = 'Y'
AND DATE_TS > TIMESTAMP '2018-12-03 03:11:13.000000'

QUALIFY ROW_NUM >= " + (String)globalMap.get("FirstIndex") + " AND ROW_NUM <=  " + (String)globalMap.get("SecondIndex")

 

Here is the first query result: 

image.png

image.pngimage.png

Community Manager

Re: How to Batch 100 calls through tXMLMap

I suspect that your globalMap values are null. There is a SQL error which shows this (in red).

Six Stars

Re: How to Batch 100 calls through tXMLMap

Awesome it works now, it was null-ing because I didn't point the From and To column values to the key element in the tFlowToIterate component below on the screenshot

 

Now excuse my Math poor skills I'm trying to get this working with 1000 batch elements, how is it going to be?

Appreciate your help!

 

select 
 ID-ID%2 as 'from',
 ID-(ID-1)%2 as 'to'
 from MyTable
 where ID%2=1

 

image.png

Community Manager

Re: How to Batch 100 calls through tXMLMap

You could try with this relatively simple way of achieving this. The query below will need to be tailored to your table, but it should work...

 

 With multsOf1000 as (
 select 
 ID-999 'from_id',
 ID 'to_id'
 from MyTable
 where ID%1000=0
 ),

 remainder as (
	Select max(multsOf1000.to_id)+1 'from_id',
	max(batch.id) 'to_id'
	From multsOf1000, MyTable
 )

 select *
 from (
 select * 
 from multsOf1000
 
 UNION ALL

  select * 
 from remainder) tmp
 order by tmp.from_id

I can't guarantee that this will be super efficient, but essentially it is returning the multiples of 1000 in the first section. Then it is adding on the remainder record (the records after the last multiple of 1000). You may need to tweak this, but it should give you an idea of how to achieve this sort of thing. I am sure there are much better ways of achieving this, but this was the first that came to mind.

 

Six Stars

Re: How to Batch 100 calls through tXMLMap

In Teradata I tried the below query:

 

WITH ID(ROW_NUM) AS 
(
    SELECT ROW_NUMBER() OVER (ORDER BY PRSN_ID) AS ROW_NUM
    FROM MyTable 
    WHERE ACTIVE_IND = 'Y' 
    GROUP BY PRSN_ID)
SELECT 
   CASE WHEN ROW_NUM = 1 THEN 0 ELSE SecondIndex-999 END AS FirstIndex,   (ROW_NUM+1)/2 * 1000 AS SecondIndex
   FROM ID
   WHERE ROW_NUM MOD 2=1

It's still not working well, but I'll continue looking into it.

Thanks!

2019 GARNER MAGIC QUADRANT FOR DATA INTEGRATION TOOL

Talend named a Leader.

Get your copy

OPEN STUDIO FOR DATA INTEGRATION

Kickstart your first data integration and ETL projects.

Download now

What’s New for Talend Summer ’19

Watch the recorded webinar!

Watch Now

An API-First Approach to Modernizing Applications

Learn how to use an API-First Approach to Modernize your Applications

Watch Now

Put Massive Amounts of Data to Work

Learn how to make your data more available, reduce costs and cut your build time

Watch Now

How to deploy Talend Jobs as Docker images to Amazon, Azure and Google Cloud reg...

Learn how to deploy Talend Jobs as Docker images to Amazon, Azure and Google Cloud registries

Blog