Invoking a Lambda function from an Amazon Aurora MySQL DB cluster - Amazon Aurora
Services or capabilities described in Amazon Web Services documentation might vary by Region. To see the differences applicable to the China Regions, see Getting Started with Amazon Web Services in China.

Invoking a Lambda function from an Amazon Aurora MySQL DB cluster

You can invoke an Amazon Lambda function from an Amazon Aurora MySQL-Compatible Edition DB cluster with the native function lambda_sync or lambda_async. Before invoking a Lambda function from an Aurora MySQL, the Aurora DB cluster must have access to Lambda. For details about granting access to Aurora MySQL, see Giving Aurora access to Lambda. For information about the lambda_sync and lambda_async stored functions, see Invoking a Lambda function with an Aurora MySQL native function.

You can also call an Amazon Lambda function by using a stored procedure. However, using a stored procedure is deprecated. We strongly recommend using an Aurora MySQL native function if you are using one of the following Aurora MySQL versions:

  • Aurora MySQL version 1.16 and later, for MySQL 5.6-compatible clusters.

  • Aurora MySQL version 2.06 and later, for MySQL 5.7-compatible clusters.

  • Aurora MySQL version 3.01 and higher, for MySQL 8.0-compatible clusters. The stored procedure is not available in Aurora MySQL version 3.

Giving Aurora access to Lambda

Before you can invoke Lambda functions from an Aurora MySQL DB cluster, make sure to first give your cluster permission to access Lambda.

To give Aurora MySQL access to Lambda

  1. Create an Amazon Identity and Access Management (IAM) policy that provides the permissions that allow your Aurora MySQL DB cluster to invoke Lambda functions. For instructions, see Creating an IAM policy to access Amazon Lambda resources.

  2. Create an IAM role, and attach the IAM policy you created in Creating an IAM policy to access Amazon Lambda resources to the new IAM role. For instructions, see Creating an IAM role to allow Amazon Aurora to access Amazon services.

  3. Set the aws_default_lambda_role DB cluster parameter to the Amazon Resource Name (ARN) of the new IAM role.

    If the cluster is part of an Aurora global database, apply the same setting for each Aurora cluster in the global database.

    For more information about DB cluster parameters, see Amazon Aurora DB cluster and DB instance parameters.

  4. To permit database users in an Aurora MySQL DB cluster to invoke Lambda functions, associate the role that you created in Creating an IAM role to allow Amazon Aurora to access Amazon services with the DB cluster. For information about associating an IAM role with a DB cluster, see Associating an IAM role with an Amazon Aurora MySQL DB cluster.

    If the cluster is part of an Aurora global database, associate the role with each Aurora cluster in the global database.

  5. Configure your Aurora MySQL DB cluster to allow outbound connections to Lambda. For instructions, see Enabling network communication from Amazon Aurora MySQL to other Amazon services.

    If the cluster is part of an Aurora global database, enable outbound connections for each Aurora cluster in the global database.

Invoking a Lambda function with an Aurora MySQL native function

Note

You can call the native functions lambda_sync and lambda_async when you use Aurora MySQL version 1.16 and later, Aurora MySQL 2.06 and later, or Aurora MySQL version 3.01 and higher. For more information about Aurora MySQL versions, see Database engine updates for Amazon Aurora MySQL.

You can invoke an Amazon Lambda function from an Aurora MySQL DB cluster by calling the native functions lambda_sync and lambda_async. This approach can be useful when you want to integrate your database running on Aurora MySQL with other Amazon services. For example, you might want to send a notification using Amazon Simple Notification Service (Amazon SNS) whenever a row is inserted into a specific table in your database.

Working with native functions to invoke a Lambda function

The lambda_sync and lambda_async functions are built-in, native functions that invoke a Lambda function synchronously or asynchronously. When you must know the result of the Lambda function before moving on to another action, use the synchronous function lambda_sync. When you don't need to know the result of the Lambda function before moving on to another action, use the asynchronous function lambda_async.

In Aurora MySQL version 3, the user invoking a native function must be granted the AWS_LAMBDA_ACCESS role. To grant this role to a user, connect to the DB instance as the administrative user, and run the following statement.

GRANT AWS_LAMBDA_ACCESS TO user@domain-or-ip-address

You can revoke this role by running the following statement.

REVOKE AWS_LAMBDA_ACCESS FROM user@domain-or-ip-address
Tip

When you use the role technique in Aurora MySQL version 3, you also activate the role by using the SET ROLE role_name or SET ROLE ALL statement. If you aren't familiar with the MySQL 8.0 role system, you can learn more in Role-based privilege model. You can also find more details in Using Roles in the MySQL Reference Manual.

In Aurora MySQL version 1 and 2, the user invoking a native function must be granted the INVOKE LAMBDA privilege. To grant this privilege to a user, connect to the DB instance as the administrative user, and run the following statement.

GRANT INVOKE LAMBDA ON *.* TO user@domain-or-ip-address

You can revoke this privilege by running the following statement.

REVOKE INVOKE LAMBDA ON *.* FROM user@domain-or-ip-address

Syntax for the lambda_sync function

You invoke the lambda_sync function synchronously with the RequestResponse invocation type. The function returns the result of the Lambda invocation in a JSON payload. The function has the following syntax.

lambda_sync ( lambda_function_ARN, JSON_payload )
Note

You can use triggers to call Lambda on data-modifying statements. Remember that triggers are not run once per SQL statement, but once per row modified, one row at a time. When a trigger runs, the process is synchronous. The data-modifying statement only returns when the trigger completes.

Be careful when invoking an Amazon Lambda function from triggers on tables that experience high write traffic. INSERT, UPDATE, and DELETE triggers are activated per row. A write-heavy workload on a table with INSERT, UPDATE, or DELETE triggers results in a large number of calls to your Amazon Lambda function.

Parameters for the lambda_sync function

The lambda_sync function has the following parameters.

lambda_function_ARN

The Amazon Resource Name (ARN) of the Lambda function to invoke.

JSON_payload

The payload for the invoked Lambda function, in JSON format.

Note

Aurora MySQL version 3 supports the JSON parsing functions from MySQL 8.0. However, Aurora MySQL versions 1 and 2 don't include those functions. JSON parsing isn't required when a Lambda function returns an atomic value, such as a number or a string.

Example for the lambda_sync function

The following query based on lambda_sync invokes the Lambda function BasicTestLambda synchronously using the function ARN. The payload for the function is {"operation": "ping"}.

SELECT lambda_sync( 'arn:aws-cn:lambda:us-east-1:868710585169:function:BasicTestLambda', '{"operation": "ping"}');

Syntax for the lambda_async function

You invoke the lambda_async function asynchronously with the Event invocation type. The function returns the result of the Lambda invocation in a JSON payload. The function has the following syntax.

lambda_async ( lambda_function_ARN, JSON_payload )

Parameters for the lambda_async function

The lambda_async function has the following parameters.

lambda_function_ARN

The Amazon Resource Name (ARN) of the Lambda function to invoke.

JSON_payload

The payload for the invoked Lambda function, in JSON format.

Note

Aurora MySQL version 3 supports the JSON parsing functions from MySQL 8.0. However, Aurora MySQL versions 1 and 2 don't include those functions. JSON parsing isn't required when a Lambda function returns an atomic value, such as a number or a string.

Example for the lambda_async function

The following query based on lambda_async invokes the Lambda function BasicTestLambda asynchronously using the function ARN. The payload for the function is {"operation": "ping"}.

SELECT lambda_async( 'arn:aws-cn:lambda:us-east-1:868710585169:function:BasicTestLambda', '{"operation": "ping"}');

Invoking a Lambda function with an Aurora MySQL stored procedure (deprecated)

You can invoke an Amazon Lambda function from an Aurora MySQL DB cluster by calling the mysql.lambda_async procedure. This approach can be useful when you want to integrate your database running on Aurora MySQL with other Amazon services. For example, you might want to send a notification using Amazon Simple Notification Service (Amazon SNS) whenever a row is inserted into a specific table in your database.

Aurora MySQL version considerations

Starting in Aurora MySQL version 1.8 and Aurora MySQL version 2.06, you can use the native function method instead of these stored procedures to invoke a Lambda function. For more information about the native functions, see Working with native functions to invoke a Lambda function.

Starting with Amazon Aurora version 1.16 and 2.06, the stored procedure mysql.lambda_async is no longer supported. If you are using an Aurora version that's higher than 1.16 or 2.06, we strongly recommend that you work with native Lambda functions instead. In Aurora MySQL version 3, the stored procedure isn't available.

Working with the mysql.lambda_async procedure to invoke a Lambda function (deprecated)

The mysql.lambda_async procedure is a built-in stored procedure that invokes a Lambda function asynchronously. To use this procedure, your database user must have EXECUTE privilege on the mysql.lambda_async stored procedure.

Syntax

The mysql.lambda_async procedure has the following syntax.

CALL mysql.lambda_async ( lambda_function_ARN, lambda_function_input )

Parameters

The mysql.lambda_async procedure has the following parameters.

lambda_function_ARN

The Amazon Resource Name (ARN) of the Lambda function to invoke.

lambda_function_input

The input string, in JSON format, for the invoked Lambda function.

Examples

As a best practice, we recommend that you wrap calls to the mysql.lambda_async procedure in a stored procedure that can be called from different sources such as triggers or client code. This approach can help to avoid impedance mismatch issues and make it easier to invoke Lambda functions.

Note

Be careful when invoking an Amazon Lambda function from triggers on tables that experience high write traffic. INSERT, UPDATE, and DELETE triggers are activated per row. A write-heavy workload on a table with INSERT, UPDATE, or DELETE triggers results in a large number of calls to your Amazon Lambda function.

Although calls to the mysql.lambda_async procedure are asynchronous, triggers are synchronous. A statement that results in a large number of trigger activations doesn't wait for the call to the Amazon Lambda function to complete, but it does wait for the triggers to complete before returning control to the client.

Example: Invoke an Amazon Lambda function to send email

The following example creates a stored procedure that you can call in your database code to send an email using a Lambda function.

Amazon Lambda Function

import boto3 ses = boto3.client('ses') def SES_send_email(event, context): return ses.send_email( Source=event['email_from'], Destination={ 'ToAddresses': [ event['email_to'], ] }, Message={ 'Subject': { 'Data': event['email_subject'] }, 'Body': { 'Text': { 'Data': event['email_body'] } } } )

Stored Procedure

DROP PROCEDURE IF EXISTS SES_send_email; DELIMITER ;; CREATE PROCEDURE SES_send_email(IN email_from VARCHAR(255), IN email_to VARCHAR(255), IN subject VARCHAR(255), IN body TEXT) LANGUAGE SQL BEGIN CALL mysql.lambda_async( 'arn:aws-cn:lambda:us-west-2:123456789012:function:SES_send_email', CONCAT('{"email_to" : "', email_to, '", "email_from" : "', email_from, '", "email_subject" : "', subject, '", "email_body" : "', body, '"}') ); END ;; DELIMITER ;

Call the Stored Procedure to Invoke the Amazon Lambda Function

mysql> call SES_send_email('example_from@amazon.com', 'example_to@amazon.com', 'Email subject', 'Email content');

Example: Invoke an Amazon Lambda function to publish an event from a trigger

The following example creates a stored procedure that publishes an event by using Amazon SNS. The code calls the procedure from a trigger when a row is added to a table.

Amazon Lambda Function

import boto3 sns = boto3.client('sns') def SNS_publish_message(event, context): return sns.publish( TopicArn='arn:aws-cn:sns:us-west-2:123456789012:Sample_Topic', Message=event['message'], Subject=event['subject'], MessageStructure='string' )

Stored Procedure

DROP PROCEDURE IF EXISTS SNS_Publish_Message; DELIMITER ;; CREATE PROCEDURE SNS_Publish_Message (IN subject VARCHAR(255), IN message TEXT) LANGUAGE SQL BEGIN CALL mysql.lambda_async('arn:aws-cn:lambda:us-west-2:123456789012:function:SNS_publish_message', CONCAT('{ "subject" : "', subject, '", "message" : "', message, '" }') ); END ;; DELIMITER ;

Table

CREATE TABLE 'Customer_Feedback' ( 'id' int(11) NOT NULL AUTO_INCREMENT, 'customer_name' varchar(255) NOT NULL, 'customer_feedback' varchar(1024) NOT NULL, PRIMARY KEY ('id') ) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Trigger

DELIMITER ;; CREATE TRIGGER TR_Customer_Feedback_AI AFTER INSERT ON Customer_Feedback FOR EACH ROW BEGIN SELECT CONCAT('New customer feedback from ', NEW.customer_name), NEW.customer_feedback INTO @subject, @feedback; CALL SNS_Publish_Message(@subject, @feedback); END ;; DELIMITER ;

Insert a Row into the Table to Trigger the Notification

mysql> insert into Customer_Feedback (customer_name, customer_feedback) VALUES ('Sample Customer', 'Good job guys!');