Ask Your Question
1

Record function can be written in a file of /resource used by the Miscellaneous Functions runtime:loadResource

asked 2018-08-17 06:24:27 -0500

Dean Han gravatar image

Hi, Is it possible a Record function can be written in a file of /resource used by the Miscellaneous Functions runtime:loadResource? Like in the file /home/user/streamsets-datacollector-3.4.0/resources/mytest.txt, I write a sql with a record function,

SELECT id FROM dbo.xxxxxx WHERE id = ${record:value('/productid')}

I got a sql syntax error when I preview, looks like ${record:value('/productid')} are not explained . Thanks.

edit retag flag offensive close merge delete

Comments

What is your goal here - to be able to change the query while the pipeline is running?

metadaddy gravatar imagemetadaddy ( 2018-08-17 09:18:34 -0500 )edit

Just hide some sensitive info. btw, as you asked, is it possible to change query while the pipeline is running? Thanks.

Dean Han gravatar imageDean Han ( 2018-08-17 09:53:25 -0500 )edit

Edited my answer with more info

metadaddy gravatar imagemetadaddy ( 2018-08-17 10:40:49 -0500 )edit

Got it, big thanks.

Dean Han gravatar imageDean Han ( 2018-08-17 21:41:47 -0500 )edit

1 Answer

Sort by ยป oldest newest most voted
1

answered 2018-08-17 09:18:29 -0500

metadaddy gravatar image

updated 2018-08-17 10:40:38 -0500

This won't work, since the expression is only evaluated once per record. This evaluation will replace ${runtime:loadResource(...)} with your query, but the expression in the loaded query will not be evaluated.

You may be able to achieve what you're looking for by using a query like:

SELECT id FROM ${runtime:loadResource('table', true)} WHERE id = '${record:value(runtime:loadResource('fieldPath', true))}'

Note - if you specify true as the second argument to runtime:loadResource then you must ensure that the resource file is owner read/write only.

The JDBC Lookup Processor query is reevaluated for each record, so any changes you make in those resource files will be immediately reflected in the pipeline.

edit flag offensive delete link more

Comments

Thanks a lot, got it.

Dean Han gravatar imageDean Han ( 2018-08-17 09:53:49 -0500 )edit
Login/Signup to Answer

Question Tools

1 follower

Stats

Asked: 2018-08-17 06:24:27 -0500

Seen: 55 times

Last updated: Aug 17