Steps to Invoke AWS Lambda as SQL Functions
Shows the steps to invoke AWS remote functions as SQL functions in your database.
To invoke AWS Lambda as SQL functions, we will be creating a catalog of SQL wrapper functions that reference and call their respective cloud function via their API endpoints. Before you create this catalog, it is assumed here that you have created the necessary cloud functions to be referenced by this catalog.
To access AWS lambda functions you need to configure the necessary policies. See Creating an IAM policy to access AWS Lambda resources and Using resource-based policies for Lambda for more information.
- Create a credential using the procedure
DBMS_CLOUD.CREATE_CREDENTIAL
. The credential object that you are creating must be of type secret key.SET DEFINE OFF BEGIN
DBMS_CLOUD.CREATE_CREDENTIAL
( credential_name => 'AWS_CRED', username => 'access_key_ID', password => 'secret_access_key' ); END; /This creates the
AWS_CRED
credential.See CREATE_CREDENTIAL Procedure for more information.
- Create a catalog.
A catalog is a collection of wrapper functions that reference and call their respective cloud functions via their API endpoints.
Example to create a catalog for AWS functions.
BEGIN
DBMS_CLOUD_FUNCTION.CREATE_CATALOG
( credential_name => 'AWS_CRED', catalog_name => 'AWS_DEMO_CATALOG', service_provider => 'AWS', cloud_params => '{"region_id":"ap-northeast-1"}' ); END; / PL/SQL procedure successfully completed.This creates the
AWS_DEMO_CATALOG
catalog.See CREATE_CATALOG Procedure for more information. You can query DBA_CLOUD_FUNCTION_CATALOG View and USER_CLOUD_FUNCTION_CATALOG View views to retrieve the list of all the catalogs in your database.
- You can list the functions in a catalog.
Following is the example to list AWS Lambda functions:
VAR function_list CLOB; BEGIN
DBMS_CLOUD_FUNCTION.LIST_FUNCTIONS
( credential_name => 'AWS_CRED', catalog_name => 'AWS_DEMO_CATALOG', function_list => :function_list ); END; / PL/SQL procedure successfully completed. SELECT JSON_QUERY (:function_list, '$' RETURNING VARCHAR2(32676) pretty) AS search_results FROM dual; SEARCH_RESULTS --This is a sample output ------------------------------------------------------------------------------------------------ [ { "functionName" : "test3_example", "functionArn" : "arn:aws:lambda:ap-north-1:378:func:test3_example", "invokeEndpoint" : "https://swiy3.lambda-url.ap-north-1.on.aws_example/" }, { "functionName" : "SumOfNum_example", "functionArn" : "arn:aws:lambda:ap-north-1:378:func:SumOfNum_example" SEARCH_RESULTS --------------------------------------------------------------------------------------------------- "invokeEndpoint" : "https://swiy3.lambda-url.ap-north-1.on.aws_example/" }, { "functionName" : "testlambda_example", "functionArn" : "arn:aws:lambda:ap-north-1:378:func:testlambda_example", "invokeEndpoint" : "https://swiy3.lambda-url.ap-north-1.on.aws_example/" }, SEARCH_RESULTS --------------------------------------------------------------------------------------------------- { "functionName" : "hellp-python_example", "functionArn" : "arn:aws:lambda:ap-north-1:378:func:hellp-python_example", "invokeEndpoint" : "https://swiy3.lambda-url.ap-north-1.on.aws_example/" }, { "functionName" : "testlam_example", "functionArn" : "arn:aws:lambda:ap-north-1:378:func:testlam_example", SEARCH_RESULTS --------------------------------------------------------------------------------------------------- "invokeEndpoint" : "https://swiy3.lambda-url.ap-north-1.on.aws_example/" } ]See LIST_FUNCTIONS Procedure for more information.
- Run the
DBMS_CLOUD_FUNCTION.SYNC_FUNCTIONS
to create wrapper SQL functions. You can use one of the following methods to create the wrapper SQL functions in the catalog, that call their respective cloud functions:-
SYNC_FUNCTIONS:
SYNC_FUNCTIONS
is the quickest and simplest method, which automatically syncs (creates or deletes) wrapper functions in the catalog with the complete list of cloud functions defined in the region, compartment, and tenancy with which the catalog was created. For example:
BEGIN
DBMS_CLOUD_FUNCTION.SYNC_FUNCTIONS
( catalog_name => 'AWS_DEMO_CATALOG' ); END; / PL/SQL procedure successfully completed.This creates a PL/SQL wrapper for adding new functions to the catalog and removing wrappers for functions that have been deleted from the catalog.
Run the following query to verify the sync.
SELECT object_name FROM sys.all_objects WHERE owner='TEST_USER' AND object_type='FUNCTION'; OBJECT_NAME -------------------------------------------------------------------------------- TESTLAMBDA HELLP-PYTHON TESTLAM TEST3 SUMOFNUMBERS
Note
Keep a note of the current user in order to run this command.See SYNC_FUNCTIONS Procedure for more information.
- You can manually create a
SQL
Function in your catalog that calls its respective cloud function usingDBMS_CLOUD.CREATE_FUNCTION
.
Example to create a function in the
AWS_DEMO_CATALOG
catalog.BEGIN
DBMS_CLOUD_FUNCTION.CREATE_FUNCTION
( credential_name => 'AWS_CRED', catalog_name => 'AWS_DEMO_CATALOG', function_name => 'aws_testlambda', function_id => 'arn:aws:lambda:ap-northeast-1:378079562280:function:hellp-python' ); END; / PL/SQL procedure successfully completed.This creates the
AWS_TESTLAMBDA
function in theAWS_DEMO_CATALOG
catalog.The
AWS_TESTLAMBDA
function in the catalog is a reference to the respective cloud function whose endpoint is referenced by theFUNCTION_ID
parameter. Invoking the function in the catalog along with its arguments runs the corresponding cloud function and provides the output returned by the cloud function.You can query DBA_CLOUD_FUNCTION View and USER_CLOUD_FUNCTION View views to retrieve the list of all the functions in your database.
See CREATE_FUNCTION Procedure for more information.
-
- After the function is created you can
DESCRIBE
and invoke it.DESC AWS_TESTLAMBDA COLUMN STATUS format a30 COLUMN OUTPUT format a30
SELECT AWS_TESTLAMBDA(NULL) FROM dual; AWS_TESTLAMBDA(NULL) ---------------------------------------------------- {"STATUS":"200","RESPONSE_BODY":"Hello Python!!"}
This invokes the
AWS_TESTLAMBDA
function by calling the function referencearn:aws:lambda:ap-northeast-1:378079562280:function:hellp-python
in theAWS_DEMO_CATALOG
catalog. - You can drop an existing function using
DROP_FUNCTION
procedure. For example:EXEC DBMS_CLOUD_FUNCTION.DROP_FUNCTION (CATALOG_NAME => 'AWS_DEMO_CATALOG', FUNCTION_NAME => 'AWS_TESTLAMBDA'); PL/SQL procedure successfully completed.
This drops the
AWS_TESTLAMBDA
function from theAWS_DEMO_CATALOG
catalog.See DROP_FUNCTION Procedure for more information.
- You can drop an existing catalog using
DROP_CATALOG
procedure. For example:BEGIN
DBMS_CLOUD_FUNCTION.DROP_CATALOG
( catalog_name => 'AWS_DEMO_CATALOG' ); END; / PL/SQL procedure successfully completed.This drops the
AWS_DEMO_CATALOG
from your database.See DROP_CATALOG Procedure for more information.
Parent topic: Invoke User Defined Functions