Implicit context loading - Retrieving schema from table - valued function

Five Stars

Implicit context loading - Retrieving schema from table - valued function

Hi,

 

I have a job as below

 

tmssqlinput --> tmap --> tmysqloutput

 

The tmssqlinput has a schema coming from a query such as "select * from etl.loadwfunction(1)". etl.loadwfunction(1) is a table valued function, which has complex query and return many derived columns.

 

So, the schema retrieval was not a problem when I was using the connection details that were hard coded in the context group

 

Now, I am planning to use implicit context loading at the project level. With this in place, and the connection details retrieved only during run time, how will I be able to retrieve the schema of the function in the tmssqlcomponent. The "Guess Schema" would not work as there are no connection details

 

I understand that I can retrieve schemas for all tables and store them as metadata. But, how do I do it when there is a requirement as above?

 

Any ideas on this?

 

Thanks in advance
Rathi


Accepted Solutions
Community Manager

Re: Implicit context loading - Retrieving schema from table - valued function

You shouldn't consider Contexts hardcoded unless you are not supplying a mechanism for them to be changed. Contexts are simply boxes for values. A lot of people do hardcode these values, but they are built to be able to handle being hardcoded and dynamically set. If you supply values to your Context Group contexts at design time, those will be the values that are used when importing schemas, for example. You do not need to remove these if you do not want to if you are using the implicit context load at runtime.

 

If the following contexts have the following set of values at design time....

 

context1 = "Hello"

context2 = "World"

context3 = ":-)"

 

.....if your implicit context load supplies new values for context1 ("Hey") and context2 ("Friend"), at runtime the values will be....

 

context1 = "Hey"

context2 = "Friend"

context3 = ":-)"

 

Context1 and Context2 changed, but Context3 remained with the value set at design time.

 


All Replies
Fifteen Stars TRF
Fifteen Stars

Re: Implicit context loading - Retrieving schema from table - valued function

Hi,

You should retrieve the schema(s) using a specific job for which the connection parameters are issued from context variables or hard coded.

Then you can promote the shema(s) to the repository and reuse it (them) anywhere.

Hope this helps. 


TRF
Community Manager

Re: Implicit context loading - Retrieving schema from table - valued function

Export your connection parameters (the actual values) as context groups. You can do this within the connection configuration for your metadata. This will create a context group for that database. The context group will have your values hard coded. However, this does not matter since the implicit context load will overwrite any context variables for which values are returned. 

Five Stars

Re: Implicit context loading - Retrieving schema from table - valued function

So, is it like I must first configure tmssqlcomponent to take the connection from hardcoded context group, retrieve the schema and then later remove the reference from hard coded context to implicit context connection details?

Community Manager

Re: Implicit context loading - Retrieving schema from table - valued function

You shouldn't consider Contexts hardcoded unless you are not supplying a mechanism for them to be changed. Contexts are simply boxes for values. A lot of people do hardcode these values, but they are built to be able to handle being hardcoded and dynamically set. If you supply values to your Context Group contexts at design time, those will be the values that are used when importing schemas, for example. You do not need to remove these if you do not want to if you are using the implicit context load at runtime.

 

If the following contexts have the following set of values at design time....

 

context1 = "Hello"

context2 = "World"

context3 = ":-)"

 

.....if your implicit context load supplies new values for context1 ("Hey") and context2 ("Friend"), at runtime the values will be....

 

context1 = "Hey"

context2 = "Friend"

context3 = ":-)"

 

Context1 and Context2 changed, but Context3 remained with the value set at design time.

 

Community Manager

Re: Implicit context loading - Retrieving schema from table - valued function

Did this answer your question?

Five Stars

Re: Implicit context loading - Retrieving schema from table - valued function

Hi rhall_2_0,

 

Apologies for the delayed response.

 

I am testing your solution for a job that has many jobs within. So, will take a little more time to test all the scenarios.

 

Will update the results soon as I finish testing all scenarios

 

Thanks

Rathi

Five Stars

Re: Implicit context loading - Retrieving schema from table - valued function

Hi rhall_2_0,

 

Apologies for such a delayed response. Your solution worked beautifully. Thanks a lot !!!

 

Regards

Rathi

15TH OCTOBER, COUNTY HALL, LONDON

Join us at the Community Lounge.

Register Now

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

Best Practices for Using Context Variables with Talend – Part 4

Pick up some tips and tricks with Context Variables

Blog

How Media Organizations Achieved Success with Data Integration

Learn how media organizations have achieved success with Data Integration

Read

Definitive Guide to Data Quality

Create systems and workflow to manage clean data ingestion and data transformation.

Download