How to use Foreign Server's parameter in SQL-MR UDF

Aster
Teradata Employee

How to use Foreign Server's parameter in SQL-MR UDF

Hi Aster community,

 

I'm developping Aster SQL-MR UDF and it works well without Foreign Server.

Now, I would like to integrate the UDF into Foreign Server definition for Teradata like this.

 

create foreign server teradata_fs
using tdpid('x') username('x') password('x')
do import with load_from_teradata,
do export with load_to_teradata,
do execute with my_sqlmr_udf
;

execute ($$ select 1 $$)@teradata_fs
;

The execute end with following error.

ERROR: function password is not supported.

Does anyone have an idea how to resolve this problem?

 


Accepted Solutions
Highlighted
Teradata Employee

Re: How to use Foreign Server's parameter in SQL-MR UDF

There is almost no documentation on this so it is not clear what is expected to make this work properly. So far the feature has only been tested and implemented for run_on_hive.

1 ACCEPTED SOLUTION
5 REPLIES
Teradata Employee

Re: How to use Foreign Server's parameter in SQL-MR UDF

Hi ebyhr,

 

the UDF in foreign server definition specifies the function to run DDL on the remote system. It is used when you run EXECUTE on FOREIGN SERVER command. For example, you would have "DO EXECUTE WITH run_on_hive" in foreign server definition to create tables in Hadoop with command "EXECUTE ($$ create database testhive $$)@hdp;"  (see AX 7.00.02 user guide for more details).

 

I am not sure whether same functionality is available for Teradata. What is my_sqlmr_udf supposed to do? Are you trying to run DDL in Teradata?

 

Regards,

Margarete

Teradata Employee

Re: How to use Foreign Server's parameter in SQL-MR UDF

Hi Margarete,

 

Thanks for your information.

I've alreday used run_on_hive with foreign server. As you would guess, what I want to archive is exactly running DDL in Teradata. 

If there is a better way than developping sql-mr udf, I would use it. 

Teradata Employee

Re: How to use Foreign Server's parameter in SQL-MR UDF

Understand now. Thanks for clarification. Currently DDL pushdown to Teradata is not supported.  

Teradata Employee

Re: How to use Foreign Server's parameter in SQL-MR UDF

Yes, I know it is not supported. However, sql-mr udf supports jar file and it means we can create the feature with teradata jdbc easily.

Going back to the first topic, my curious is whether additional sql-mr in foreign server definition can use its parameters or not. (Your answer was "not sure" though...)

 

FYI, here is my repository. If I run the query without foreign server, it works.

https://github.com/ebyhr/run_on_teradata

 

 

select * from run_on_teradata ( on (select 1)
 tdpid ('teradata')
 username ('dbc')
 password ('***')
 query ('drop table schema.table')
)
;

 

 

Highlighted
Teradata Employee

Re: How to use Foreign Server's parameter in SQL-MR UDF

There is almost no documentation on this so it is not clear what is expected to make this work properly. So far the feature has only been tested and implemented for run_on_hive.