Use Amazon Athena parameterized queries to provide data as a service

海外精选

海外精选的内容汇集了全球优质的亚马逊云科技相关技术内容。同时，内容中提到的“AWS” 是 “Amazon Web Services” 的缩写，在此网站不作为商标展示。

{"value":"[Amazon Athena](http://aws.amazon.com/athena) now provides you more flexibility to use parameterized queries, and we recommend you use them as the best practice for your Athena queries moving forward so you benefit from the security, reusability, and simplicity they offer. In a previous post, [Improve reusability and security using Amazon Athena parameterized queries](https://aws.amazon.com/blogs/big-data/improve-reusability-and-security-using-amazon-athena-parameterized-queries/), we explained how parameterized queries with prepared statements provide reusability of queries, protection against SQL injection, and masking of query strings from [AWS CloudTrail](http://aws.amazon.com/cloudtrail) events. In this post, we explain how you can run Athena parameterized queries using the ```ExecutionParameters``` property in your ```StartQueryExecution``` requests. We provide a sample application you can reference for using parameterized queries, with and without prepared statements. Athena parameterized queries can be integrated into many data driven applications, and we walk you through a sample data as a service application to see how parameterized queries can plug in.\n\nCustomers tell us they are finding new ways to make effective use of their data assets by providing data as a service (DaaS). In this post, we share a sample architecture using parameterized queries applied in the form of a DaaS application. This is helpful for many types of organizations, whether you’re working with an enterprise making data available to other lines of business, a regulator making reports available to your industry, a company monetizing your data assets, an independent software vendor (ISV) enabling your applications’ tenants to query their data when they need it, or trying to share data at scale in other ways. In DaaS applications, you can provide predefined queries to run against your governed datasets with values your users input. You can expand your DaaS application to break away from monolithic data infrastructure by treating data as a product (DaaP) and providing a distribution of datasets, which have distinct domain-specific data pipelines. You can authorize these datasets to consumers in your DaaS application permissions. You can use Athena parameterized queries as a way to predefine your queries, which you can use to run queries across your datasets, and serve as a layer of protection for your DaaS applications. This post first describes how parameterized queries work, then applies parameterized queries in the form of a DaaS application.\n\n\n#### **Feature overview**\n\n\nIn queries you send to Athena, you can use positional parameters declared by a question mark (```?```) in your query string, then declare values as execution parameters sequentially in your ```StartQueryExecution``` request. You can use execution parameters with your existing prepared statements and also with supported SQL queries in Athena. You can still take advantage of the reusability and security benefits of parameterized queries, and using execution parameters also masks your query’s parameters when [viewing recent queries](https://docs.aws.amazon.com/athena/latest/ug/querying.html#queries-viewing-history) in Athena. You can also change from building SQL query strings manually to using execution parameters; this allows you to run parameterized queries without needing to first create prepared statements. Parameterized queries are currently supported for SELECT, INSERT INTO, CTAS, and UNLOAD statements. For the most current list, and for information on execution parameters, refer to [Querying with parameterized queries](https://docs.aws.amazon.com/athena/latest/ug/querying-with-prepared-statements.html).\n\nPreviously, you could only run parameterized queries by first creating [prepared statements](https://docs.aws.amazon.com/athena/latest/ug/querying-with-prepared-statements.html#querying-with-prepared-statements-querying) in your Athena workgroup, then running parameterized queries while passing variables into an EXECUTE SQL statement with the USING clause. You are no longer required to create and maintain prepared statements across all of your Athena workgroups to take advantage of parameterization. This is helpful if you run the same queries across multiple workgroups or otherwise do not need the prepared statements feature.\n\nYou can continue to use Athena workgroups to isolate, implement individual cost constraints, and track query-related metrics for tenants within your multi-tenant application. For example, your DaaS application’s customers can run the same queries against your dataset with separate workgroups. For more information on Athena workgroups, refer to [Using workgroups for running queries](https://docs.aws.amazon.com/athena/latest/ug/workgroups.html).\n\n##### **Changing your code to use parameterized queries**\n\nChanging your existing code to use parameterized queries is a small change which will have an immediate positive impact. Previously, you were required to build your query string value manually using environment variables as parameter placeholders. Manipulating the query string can be burdensome and has an inherent risk for injecting undesired values or SQL fragments (such as SQL operators), regardless of intent. You can now replace variables in your query string with a question mark (```?```), and declare your variable values sequentially with the ```ExecutionParameters``` option. By doing so, you take advantage of the security benefits of parameterized queries, and your queries are less complicated to author and maintain. The syntax change is shown in the following code, using the AWS Command Line Interface (AWS CLI) as an example.\n\nPreviously, running queries against Athena without execution parameters:\n\nBash\n```\naws athena start-query-execution \\\n--query-string \"SELECT * FROM table WHERE x = $ARG1 AND y = $ARG2 AND z = $ARG3\" \\\n--query-execution-context \"Database\"=\"default\" \\\n--work-group myWorkGroup\n```\n\nNow, running parameterized queries against Athena with execution parameters:\n\nBash\n```\naws athena start-query-execution \\\n--query-string \"SELECT * FROM table WHERE x = ? AND y = ? AND z = ?\" \\\n--query-execution-context \"Database\"=\"default\" \\\n--work-group myWorkGroup \\\n--execution-parameters $ARG1 $ARG2 $ARG3\n```\n\nThe following is an example of a command that creates a prepared statement in your Athena workgroup. To learn more about creating prepared statements, refer to [Querying with prepared statements](https://docs.aws.amazon.com/athena/latest/ug/querying-with-prepared-statements.html).\n\nBash\n```\naws athena start-query-execution \\\n--query-string \"PREPARE my-prepared-statement FROM SELECT * FROM table WHERE x = ? AND y = ? AND z = ?\" \\\n--query-execution-context \"Database\"=\"default\" \\\n--work-group myWorkGroup\n```\n\nPreviously, running parameterized queries against prepared statements without execution parameters:\n\nBash\n```\naws athena start-query-execution \\\n--query-string \"EXECUTE my-prepared-statement USING $ARG1, $ARG2, $ARG3“ \\\n--query-execution-context \"Database\"=\"default\" \\\n--work-group myWorkGroup\n```\n\nNow, running parameterized queries against prepared statements with execution parameters:\n\nBash\n```\naws athena start-query-execution \\\n--query-string \"EXECUTE my-prepared-statement\" \\\n--query-execution-context \"Database\"=\"default\" \\\n--work-group myWorkGroup \\\n--execution-parameters $ARG1 $ARG2 $ARG3\n```\n\n#### **Sample architecture**\n\nThe purpose of this sample architecture is to apply the ```ExecutionParameters``` feature when running Athena queries, with and without prepared statements. This is not intended to be a DaaS solution for use with your production data.\n\nThis sample architecture exhibits a DaaS application with a user interface (UI) that presents three Athena parameterized queries written against the public [Amazon.com customer reviews dataset](https://s3.amazonaws.com/amazon-reviews-pds/readme.html). The following figure depicts this workflow when a user submits a query to Athena. This example uses [AWS Amplify](https://aws.amazon.com/amplify/) to host a front-end application. The application calls an [Amazon API Gateway](https://aws.amazon.com/api-gateway) HTTP API, which invokes AWS Lambda functions to authenticate requests, fetch the Athena prepared statements and named queries, and run the parameterized queries against Athena. The Lambda function uses the name of the Athena workgroup, statement name, statement type (prepared statement or not), and a list of query parameters input by the user. Athena queries data in an Amazon Simple Storage Service (Amazon S3), bucket which is cataloged in AWS Glue, and presents results to the user on the DaaS application UI.\n\n\n![image.png](https://dev-media.amazoncloud.cn/40a6dc42bb264bff823ab114d5bedd93_image.png)\n\nEnd-users of the DaaS application UI can run only parameterized queries against Athena. The DaaS application UI demonstrates two ways to run parameterized queries with execution parameters: with and without prepared statements. In both cases, the Lambda function submits the query, waits for the query to complete, and provides the results that match the query parameters. The following figure depicts the DaaS application UI.\n\n![image.png](https://dev-media.amazoncloud.cn/340f939043e24dba93df99bef45a0567_image.png)\n\nYou may want your users to have the ability to list all Athena prepared statements within your Athena workgroup, select a statement, input arguments, and run the query; on the left side of the DaaS application UI, you use an EXECUTE statement to query the data lake with an Athena prepared statement. You may have several reporting queries maintained in your code base. In this case, your users select a statement, input arguments, and run the query. On the right side of the DaaS application UI, you use a SELECT statement to use Athena parameterized queries without prepared statements.\n\n\n#### **Prerequisites**\n\n\nThis post uses the following AWS services to demonstrate a DaaS architecture pattern that uses Athena to query the Amazon.com customer reviews dataset:\n\n- [AWS Amplify](https://aws.amazon.com/amplify/)\n- [Amazon API Gateway](https://aws.amazon.com/api-gateway)\n- [Amazon Athena](http://aws.amazon.com/athena)\n- [AWS CloudFormation](http://aws.amazon.com/cloudformation)\n- [Amazon DynamoDB](https://aws.amazon.com/dynamodb/)\n- [AWS Glue](https://aws.amazon.com/glue) and the AWS Glue Data Catalog\n- [AWS Identity and Access Management](http://aws.amazon.com/iam) (IAM)\n- [AWS Lambda](http://aws.amazon.com/lambda)\n- [Amazon S3](https://docs.aws.amazon.com/AmazonS3/latest/userguide/Welcome.html)\n\nThis post assumes you have the following:\n\n- An AWS account. For instructions, refer to [Creating an AWS account](https://docs.aws.amazon.com/accounts/latest/reference/manage-acct-creating.html).\n- A CloudTrail trail created. For instructions, refer to [Creating a trail](https://docs.aws.amazon.com/awscloudtrail/latest/userguide/cloudtrail-create-a-trail-using-the-console-first-time.html).\n- An S3 bucket created. For instructions, refer to [Creating a bucket](https://docs.aws.amazon.com/AmazonS3/latest/userguide/create-bucket-overview.html).\n- [Node.js](https://nodejs.org/en/) version 16+ installed on your device.\n\n\n#### **Deploy the CloudFormation stack**\n\n\nIn this section, you deploy a CloudFormation template that creates the following resources:\n\n- AWS Glue Data Catalog database\n- AWS Glue Data Catalog table\n- An Athena workgroup\n- Three Athena prepared statements\n- Three Athena named queries\n- The API Gateway HTTP API\n- The Lambda execution role for Athena queries\n- The Lambda execution role for API Gateway HTTP API authorization\n- Five Lambda functions:\n\t- Update the AWS Glue Data Catalog\n\t- Authorize API Gateway requests\n\t- Submit Athena queries\n\t- List Athena prepared statements\n\t- List Athena named queries\nNote that this CloudFormation template was tested in AWS Regions ```ap-southeast-2```, ```ca-central-1```, ```eu-west-2```, ```us-east-1```, ```us-east-2```, and ```us-west-2```. Note that deploying this into your AWS account will incur cost. Steps for cleaning up the resources are included later in this post.\n\nTo deploy the CloudFormation stack, follow these steps:\n\n1. Navigate to this post’s [GitHub repository](https://github.com/aws-samples/amazon-athena-execution-parameters-blog).\n2. Clone the repository or copy the CloudFormation template ```athena-parameterized-queries.yaml```.\n3. On the AWS CloudFormation console, choose **Create stack**.\n4. Select Upload a template file and choose **Choose file**.\n5. Upload ```athena-parameterized-queries.yaml```, then choose Next.\n6. On the Specify stack details page, enter the stack name ```athena-parameterized-queries```.\n7. On the same page, there are two parameters:\na. For **S3QueryResultsBucketName**, enter the S3 bucket name in your AWS account and in the same AWS Region as where you’re running your CloudFormation stack. (For this post, we use the bucket name value, like ```my-bucket```).\nb. For **APIPassphrase**, enter a passphrase to authenticate API requests. You use this later.\n8. Choose **Next**.\n9. On the **Configure stack** **options** page, choose **Next**.\n10. On the **Review** page, select **I acknowledge that AWS CloudFormation might create IAM resources with custom names**, and choose **Create stack**.\n\nThe script takes less than two minutes to run and change to a ```CREATE_COMPLETE``` state. If you deploy the stack twice in the same AWS account and Region, some resources may already exist, and the process fails with a message indicating the resource already exists in another template.\n\n11. On the **Outputs** tab, copy the ```APIEndpoint``` value to use later.\n\nFor least-privilege authorization for deployment of the CloudFormation template, you can create an [AWS CloudFormation service role with](https://docs.aws.amazon.com/AWSCloudFormation/latest/UserGuide/using-iam-servicerole.html) the following []() actions. To do this, you must create an [IAM policy](https://docs.aws.amazon.com/IAM/latest/UserGuide/access_policies_create.html) and [IAM role](https://docs.aws.amazon.com/IAM/latest/UserGuide/id_roles_create.html), and choose this role when configuring stack options. You need to replace the values for ```${Partition}```, ```${AccountId}```, and ```${Region}``` with your own values; for more information on these values, refer to [Pseudo parameters reference](https://docs.aws.amazon.com/AWSCloudFormation/latest/UserGuide/pseudo-parameter-reference.html).\n\nJson\n```\n{\n \"Version\": \"2012-10-17\",\n \"Statement\": [\n {\n \"Sid\": \"IAM\",\n \"Effect\": \"Allow\",\n \"Action\": [\n \"iam:GetRole\",\n \"iam:UntagRole\",\n \"iam:TagRole\",\n \"iam:CreateRole\",\n \"iam:DeleteRole\",\n \"iam:PassRole\",\n \"iam:GetRolePolicy\",\n \"iam:PutRolePolicy\",\n \"iam:AttachRolePolicy\",\n \"iam:TagPolicy\",\n \"iam:DeleteRolePolicy\",\n \"iam:DetachRolePolicy\",\n \"iam:UntagPolicy\"\n ],\n \"Resource\": [\n \"arn:${Partition}:iam::${AccountId}:role/LambdaAthenaExecutionRole-athena-parameterized-queries\",\n \"arn:${Partition}:iam::${AccountId}:role/service-role/LambdaAthenaExecutionRole-athena-parameterized-queries\",\n \"arn:${Partition}:iam::${AccountId}:role/service-role/LambdaAuthorizerExecutionRole-athena-parameterized-queries\",\n \"arn:${Partition}:iam::${AccountId}:role/LambdaAuthorizerExecutionRole-athena-parameterized-queries\"\n ]\n },\n {\n \"Sid\": \"LAMBDA\",\n \"Effect\": \"Allow\",\n \"Action\": [\n \"lambda:CreateFunction\",\n \"lambda:GetFunction\",\n \"lambda:InvokeFunction\",\n \"lambda:AddPermission\",\n \"lambda:DeleteFunction\",\n \"lambda:RemovePermission\",\n \"lambda:UpdateFunctionConfiguration\"\n ],\n \"Resource\": [\n \"arn:${Partition}:lambda:${Region}:${AccountId}:function:LambdaRepairFunction-athena-parameterized-queries\",\n \"arn:${Partition}:lambda:${Region}:${AccountId}:function:LambdaAthenaFunction-athena-parameterized-queries\",\n \"arn:${Partition}:lambda:${Region}:${AccountId}:function:LambdaAuthorizerFunction-athena-parameterized-queries\",\n \"arn:${Partition}:lambda:${Region}:${AccountId}:function:GetPrepStatements-athena-parameterized-queries\",\n \"arn:${Partition}:lambda:${Region}:${AccountId}:function:GetNamedQueries-athena-parameterized-queries\"\n ]\n },\n {\n \"Sid\": \"ATHENA\",\n \"Effect\": \"Allow\",\n \"Action\": [\n \"athena:GetWorkGroup\",\n \"athena:CreateWorkGroup\",\n \"athena:DeleteWorkGroup\",\n \"athena:DeleteNamedQuery\",\n \"athena:CreateNamedQuery\",\n \"athena:CreatePreparedStatement\",\n \"athena:DeletePreparedStatement\",\n \"athena:GetPreparedStatement\"\n ],\n \"Resource\": [\n \"arn:${Partition}:athena:${Region}:${AccountId}:workgroup/ParameterizedStatementsWG\"\n ]\n },\n {\n \"Sid\": \"GLUE\",\n \"Effect\": \"Allow\",\n \"Action\": [\n \"glue:CreateDatabase\",\n \"glue:DeleteDatabase\",\n \"glue:CreateTable\",\n \"glue:DeleteTable\"\n ],\n \"Resource\": [\n \"arn:${Partition}:glue:${Region}:${AccountId}:catalog\",\n \"arn:${Partition}:glue:${Region}:${AccountId}:database/athena_prepared_statements\",\n \"arn:${Partition}:glue:${Region}:${AccountId}:table/athena_prepared_statements/*\",\n \"arn:${Partition}:glue:${Region}:${AccountId}:userDefinedFunction/athena_prepared_statements/*\"\n ]\n },\n {\n \"Sid\": \"APIGATEWAY\",\n \"Effect\": \"Allow\",\n \"Action\": [\n \"apigateway:DELETE\",\n \"apigateway:PUT\",\n \"apigateway:PATCH\",\n \"apigateway:POST\",\n \"apigateway:TagResource\",\n \"apigateway:UntagResource\"\n ],\n \"Resource\": [\n \"arn:${Partition}:apigateway:${Region}::/apis/*/integrations*\",\n \"arn:${Partition}:apigateway:${Region}::/apis/*/stages*\",\n \"arn:${Partition}:apigateway:${Region}::/apis/*/authorizers*\",\n \"arn:${Partition}:apigateway:${Region}::/apis/*/routes*\",\n \"arn:${Partition}:apigateway:${Region}::/tags/arn%3Aaws%3Aapigateway%3A${Region}%3A%3A%2Fv2%2Fapis%2F*\"\n ]\n },\n {\n \"Sid\": \"APIGATEWAYMANAGEAPI\",\n \"Effect\": \"Allow\",\n \"Action\": [\n \"apigateway:DELETE\",\n \"apigateway:PUT\",\n \"apigateway:PATCH\",\n \"apigateway:POST\",\n \"apigateway:GET\"\n ],\n \"Resource\": [\n \"arn:${Partition}:apigateway:${Region}::/apis\"\n ],\n \"Condition\": {\n \"StringEquals\": {\n \"apigateway:Request/ApiName\": \"AthenaAPI-athena-parameterized-queries\"\n }\n }\n },\n {\n \"Sid\": \"APIGATEWAYMANAGEAPI2\",\n \"Effect\": \"Allow\",\n \"Action\": [\n \"apigateway:DELETE\",\n \"apigateway:PUT\",\n \"apigateway:PATCH\",\n \"apigateway:POST\",\n \"apigateway:GET\"\n ],\n \"Resource\": [\n \"arn:${Partition}:apigateway:${Region}::/apis/*\"\n ],\n \"Condition\": {\n \"StringEquals\": {\n \"apigateway:Resource/ApiName\": \"AthenaAPI-athena-parameterized-queries\"\n }\n }\n },\n {\n \"Sid\": \"APIGATEWAYGET\",\n \"Effect\": \"Allow\",\n \"Action\": [\n \"apigateway:GET\"\n ],\n \"Resource\": [\n \"arn:${Partition}:apigateway:${Region}::/apis/*\"\n ]\n },\n {\n \"Sid\": \"LAMBDALAYER\",\n \"Effect\": \"Allow\",\n \"Action\": [\n \"lambda:GetLayerVersion\"\n ],\n \"Resource\": [\n \"arn:${Partition}:lambda:*:280475519630:layer:boto3-1_24*\"\n ]\n }\n ]\n}\n```\n\nAfter you create the CloudFormation stack, you use the AWS management console to deploy an Amplify application and view the Lambda functions. The following is the scoped-down IAM policy that you can attach to an IAM user or role to perform these operations:\n\nJSON\n```\n{\n \"Version\": \"2012-10-17\",\n \"Statement\": [\n {\n \"Sid\": \"AmplifyCreateApp\",\n \"Effect\": \"Allow\",\n \"Action\": [\n \"amplify:CreateBranch\",\n \"amplify:StartDeployment\",\n \"amplify:CreateDeployment\",\n \"amplify:CreateApp\",\n \"amplify:StartJob\"\n ],\n \"Resource\": \"arn:${Partition}:amplify:${Region}:${AccountId}:apps/*\"\n },\n {\n \"Sid\": \"AmplifyList\",\n \"Effect\": \"Allow\",\n \"Action\": \"amplify:List*\",\n \"Resource\": \"arn:${Partition}:amplify:${Region}:${AccountId}:apps/*\"\n },\n {\n \"Sid\": \"AmplifyGet\",\n \"Effect\": \"Allow\",\n \"Action\": \"amplify:GetJob\",\n \"Resource\": \"arn:${Partition}:amplify:${Region}:${AccountId}:apps/*\"\n },\n {\n \"Sid\": \"LambdaList\",\n \"Effect\": \"Allow\",\n \"Action\": [\n \"lambda:GetAccountSettings\",\n \"lambda:ListFunctions\"\n ],\n \"Resource\": \"*\"\n },\n {\n \"Sid\": \"LambdaFunction\",\n \"Effect\": \"Allow\",\n \"Action\": [\n \"lambda:GetFunction\"\n ],\n \"Resource\": \"arn:${Partition}:lambda:${Region}:${AccountId}:function:LambdaAthenaFunction-athena-parameterized-queries\"\n }\n ]\n}\n```\n\nNote that you need the following IAM policy when deploying your Amplify application to set a global password, and when cleaning up your resources to delete the Amplify application. Remember to replace ${AppARN} with the ARN of the Amplify application. You can find the ARN after creating the Amplify app on the General tab in the App Settings section of the Amplify console.\n\nJSON\n```\n{\n \"Version\": \"2012-10-17\",\n \"Statement\": [\n {\n \"Sid\": \"UpdateAndDeleteAmplifyApp\",\n \"Effect\": \"Allow\",\n \"Action\": [\n \"amplify:DeleteApp\",\n \"amplify:UpdateApp\"\n ],\n \"Resource\": \"${AppARN}\"\n }\n ]\n}\n```\n\n\n#### **Deploy the Amplify application**\n\n\nIn this section, you deploy your Amplify application.\n\n1. In the cloned repository, open ```web-application/.env``` in a text editor.\n2. Set ```AWS_API_ENDPOINT``` as the ```APIEndpoint``` value from the CloudFormation stack **Outputs** For example: ```AWS_API_ENDPOINT=\"https://123456abcd.execute-api.your-region.amazonaws.com\"```.\n3. Set ```API_AUTH_CODE``` as the value you input as the CloudFormation stack’s ```APIPassphrase``` parameter argument. For example: ```API_AUTH_CODE=\"YOUR_PASSPHRASE\"```.\n4. Navigate to the ```web-application/ directory and run ```npm install```.\n5. Run ```npm run build``` to compile distribution assets.\n6. On the Amplify console, choose **All apps**.\n7. Choose **New app**.\n8. Select **Host web app**, select **Deploy without Git provider**, then choose **Continue**.\n9. For **App name**, enter ```Athena Parameterized Queries App```.\n10. For **Environment name**¸ you don’t need to enter a value.\n11. Select **Drag and Drop**.\n12. Locate the ``dist/``` directory inside ```web-application/```, drag it into the window and drop it. Ensure you drag the entire directory, not the files within it.\n\n![image.png](https://dev-media.amazoncloud.cn/f1184ebefae0494dba58a30e7067980f_image.png)\n\n13. Choose **Save and deploy** to deploy the web application on Amplify.\n\nThis step takes less than a minute to complete.\n\n14. Under **App settings**, choose **Access control**, then choose **Manage access**.\n15. Select **Apply a global password**, then enter values for **Username** and **Password**.\n\nYou use these credentials to access your Amplify application.\n\n\n#### **Access your Amplify application and run queries**\n\n\nIn this section, you use the Amplify application to run Athena parameterized queries against the Amazon.com customer reviews dataset. The left side of the application shows how you can run parameterized queries using Athena prepared statements. The right side of the application shows how you can run parameterized queries without prepared statements, such as if the queries are written in your code. The sample in this post uses named queries within the Athena workgroup. For more information about named queries, refer to [NamedQuery](https://docs.aws.amazon.com/athena/latest/APIReference/API_NamedQuery.html).\n\n\n1. Open the Amplify web application link located under **Domain**. For example: ```https://dev123.abcd12345xyz.amplifyapp.com/```.\n2. In the **Sign in** prompt, enter the user name and password you provided as the Amplify application global password.\n3. For **Workgroup Name**, choose the ```ParameterizedStatementsWG``` workgroup.\n4. Choose a statement example on the **Prepared Statement** or **SQL Statement** drop-down menu.\n\nSelecting a statement displays a description about the query, including examples of parameters you can try with this statement, and the original SQL query string. SQL parameters of type string must be surrounded by single quotes, for example: ```'your_string_value'```.\n\n5. Enter your query parameters.\n\nThe following figure shows an example of the parameters to input for the ```product_helpful_reviews``` prepared statement.\n\n![image.png](https://dev-media.amazoncloud.cn/b0b9163cc3594389a68676221b954865_image.png)\n\n6. Choose **Run Query** to send the query request to the API endpoint.\n\nAfter the query runs, the sample application presents the results in a table format, as depicted in the following screenshot. This is one of many ways to present results, and your application can display results in the format which makes the most sense for your users. The complete query workflow is depicted in the previous architecture diagram.\n\n![image.png](https://dev-media.amazoncloud.cn/6a48a038e73844058d88a3f7d583735a_image.png)\n\n\n#### **Using execution parameters with the AWS SDK for Python (Boto3)**\n\n\nIn this section, you inspect the Lambda function code for using the ```StartQueryExecution``` API with and without prepared statements.\n\n1. On the Lambda console, choose **Functions**.\n2. Navigate to the ```LambdaAthenaFunction-athena-parameterized-queries``` function.\n3. Choose the **Code Source** window.\n\nExamples of passing parameters to the Athena ```StartQueryExecution``` API using the AWS SDK for Python (Boto3) begin on lines 39 and 49. Note the ```ExecutionParameters``` option on lines 45 and 55.\n\nThe following code uses execution parameters with Athena prepared statements:\n\nPython\n```\nresponse = athena.start_query_execution(\n QueryString=f'EXECUTE {statement}', # Example: \"EXECUTE prepared_statement_name\"\n WorkGroup=workgroup,\n QueryExecutionContext={\n 'Database': 'athena_prepared_statements'\n },\n ExecutionParameters=input_parameters\n)\n```\n\nThe following code uses execution parameters without Athena prepared statements:\n\nPython \n```\nresponse = athena.start_query_execution(\n QueryString=statement, # Example: \"SELECT * FROM TABLE WHERE parameter_name = ?\"\n WorkGroup=workgroup,\n QueryExecutionContext={\n 'Database': 'athena_prepared_statements'\n },\n ExecutionParameters=input_parameters\n)\n```\n\n\n#### **Clean up**\n\n\nIn this post, you created several components, which generate cost. To avoid incurring future charges, remove the resources with the following steps:\n\n1. [Delete the S3 bucket’s results prefix](https://docs.aws.amazon.com/AmazonS3/latest/userguide/using-folders.html#delete-folders) created after you ran a query on your workgroup.\n\nWith the default template, the prefix is named ```<S3QueryResultsBucketName>/athena-results```. Use caution in this step. Unless you are [using versioning on your S3 bucket](https://docs.aws.amazon.com/AmazonS3/latest/userguide/Versioning.html), deleting S3 objects can’t be undone.\n\n2. On the Amplify console, select the app to delete and on the **Actions** menu, choose **Delete app**, then confirm.\n3. On the AWS CloudFormation console, select the stack to delete, choose **Delete**, and confirm.\n\n\n#### **Conclusion**\n\n\nIn this post, we showed how you can build a DaaS application using Athena parameterized queries. The ```StartQueryExecution``` API in Athena now supports execution parameters, which allows you to run Athena queries as parameterized queries. You can decouple your execution parameters from your query strings, and use parameterized queries without being limited to the Athena workgroups where you have created prepared statements. You can take advantage of the security benefits Athena offers with parameterized queries, and developers no longer need to build query strings manually. In this post, you learned how to use execution parameters, and you deployed a DaaS reference architecture to see how parameterized queries can be applied.\n\nYou can get started with Athena parameterized queries by using the Athena console, the [AWS CLI](https://docs.aws.amazon.com/cli/latest/reference/athena/), or the [AWS SDK](https://boto3.amazonaws.com/v1/documentation/api/latest/reference/services/athena.html). To learn more about Athena, refer to the [Amazon Athena User Guide](https://docs.aws.amazon.com/athena/latest/ug/getting-started.html). For more information on using execution parameters, refer to [Querying with parameterized queries](https://docs.aws.amazon.com/athena/latest/ug/querying-with-prepared-statements.html).\n\nThanks for reading this post! If you have questions about Athena prepared statements and parameterized queries, don’t hesitate to leave a comment.\n\n\n##### **About the Authors**\n\n\n![image.png](https://dev-media.amazoncloud.cn/a8ef2f4f8bc4440f9a69a30faea50f6e_image.png)\n\n**Blayze Stefaniak** is a Senior Solutions Architect for the Technical Strategist Program supporting Executive Customer Programs in AWS Marketing. He has experience working across industries including healthcare, automotive, and public sector. He is passionate about breaking down complex situations into something practical and actionable. In his spare time, you can find Blayze listening to Star Wars audiobooks, trying to make his dogs laugh, and probably talking on mute.\n\n![image.png](https://dev-media.amazoncloud.cn/669cbc714cfd4b098307b1512f125d69_image.png)\n\n**Daniel Tatarkin** is a Solutions Architect at Amazon Web Services (AWS) supporting Federal Financial organizations. He is passionate about big data analytics and serverless technologies. Outside of work, he enjoys learning about personal finance, coffee, and trying out new programming languages for fun.\n\n![image.png](https://dev-media.amazoncloud.cn/5e7530d2ba844103bff56872d4894f2d_image.png)\n\n**Matt Boyd** is a Senior Solutions Architect at AWS working with federal financial organizations. He is passionate about effective cloud management and governance, as well as data governance strategies. When he’s not working, he enjoys running, weight lifting, and teaching his elementary-age son ethical hacking skills.","render":"<a href=\"http://aws.amazon.com/athena\" target=\"_blank\">Amazon Athena</a> now provides you more flexibility to use parameterized queries, and we recommend you use them as the best practice for your Athena queries moving forward so you benefit from the security, reusability, and simplicity they offer. In a previous post, <a href=\"https://aws.amazon.com/blogs/big-data/improve-reusability-and-security-using-amazon-athena-parameterized-queries/\" target=\"_blank\">Improve reusability and security using Amazon Athena parameterized queries</a>, we explained how parameterized queries with prepared statements provide reusability of queries, protection against SQL injection, and masking of query strings from <a href=\"http://aws.amazon.com/cloudtrail\" target=\"_blank\">AWS CloudTrail</a> events. In this post, we explain how you can run Athena parameterized queries using the <code>ExecutionParameters</code> property in your <code>StartQueryExecution</code> requests. We provide a sample application you can reference for using parameterized queries, with and without prepared statements. Athena parameterized queries can be integrated into many data driven applications, and we walk you through a sample data as a service application to see how parameterized queries can plug in.\nCustomers tell us they are finding new ways to make effective use of their data assets by providing data as a service (DaaS). In this post, we share a sample architecture using parameterized queries applied in the form of a DaaS application. This is helpful for many types of organizations, whether you’re working with an enterprise making data available to other lines of business, a regulator making reports available to your industry, a company monetizing your data assets, an independent software vendor (ISV) enabling your applications’ tenants to query their data when they need it, or trying to share data at scale in other ways. In DaaS applications, you can provide predefined queries to run against your governed datasets with values your users input. You can expand your DaaS application to break away from monolithic data infrastructure by treating data as a product (DaaP) and providing a distribution of datasets, which have distinct domain-specific data pipelines. You can authorize these datasets to consumers in your DaaS application permissions. You can use Athena parameterized queries as a way to predefine your queries, which you can use to run queries across your datasets, and serve as a layer of protection for your DaaS applications. This post first describes how parameterized queries work, then applies parameterized queries in the form of a DaaS application.\n<h4><a id=\"Feature_overview_5\"></a>Feature overview</h4>\nIn queries you send to Athena, you can use positional parameters declared by a question mark (<code>?</code>) in your query string, then declare values as execution parameters sequentially in your <code>StartQueryExecution</code> request. You can use execution parameters with your existing prepared statements and also with supported SQL queries in Athena. You can still take advantage of the reusability and security benefits of parameterized queries, and using execution parameters also masks your query’s parameters when <a href=\"https://docs.aws.amazon.com/athena/latest/ug/querying.html#queries-viewing-history\" target=\"_blank\">viewing recent queries</a> in Athena. You can also change from building SQL query strings manually to using execution parameters; this allows you to run parameterized queries without needing to first create prepared statements. Parameterized queries are currently supported for SELECT, INSERT INTO, CTAS, and UNLOAD statements. For the most current list, and for information on execution parameters, refer to <a href=\"https://docs.aws.amazon.com/athena/latest/ug/querying-with-prepared-statements.html\" target=\"_blank\">Querying with parameterized queries</a>.\nPreviously, you could only run parameterized queries by first creating <a href=\"https://docs.aws.amazon.com/athena/latest/ug/querying-with-prepared-statements.html#querying-with-prepared-statements-querying\" target=\"_blank\">prepared statements</a> in your Athena workgroup, then running parameterized queries while passing variables into an EXECUTE SQL statement with the USING clause. You are no longer required to create and maintain prepared statements across all of your Athena workgroups to take advantage of parameterization. This is helpful if you run the same queries across multiple workgroups or otherwise do not need the prepared statements feature.\nYou can continue to use Athena workgroups to isolate, implement individual cost constraints, and track query-related metrics for tenants within your multi-tenant application. For example, your DaaS application’s customers can run the same queries against your dataset with separate workgroups. For more information on Athena workgroups, refer to <a href=\"https://docs.aws.amazon.com/athena/latest/ug/workgroups.html\" target=\"_blank\">Using workgroups for running queries</a>.\n<h5><a id=\"Changing_your_code_to_use_parameterized_queries_14\"></a>Changing your code to use parameterized queries</h5>\nChanging your existing code to use parameterized queries is a small change which will have an immediate positive impact. Previously, you were required to build your query string value manually using environment variables as parameter placeholders. Manipulating the query string can be burdensome and has an inherent risk for injecting undesired values or SQL fragments (such as SQL operators), regardless of intent. You can now replace variables in your query string with a question mark (<code>?</code>), and declare your variable values sequentially with the <code>ExecutionParameters</code> option. By doing so, you take advantage of the security benefits of parameterized queries, and your queries are less complicated to author and maintain. The syntax change is shown in the following code, using the AWS Command Line Interface (AWS CLI) as an example.\nPreviously, running queries against Athena without execution parameters:\nBash\n<pre><code class=\"lang-\">aws athena start-query-execution \\\n--query-string "SELECT * FROM table WHERE x = $ARG1 AND y = $ARG2 AND z = $ARG3" \\\n--query-execution-context "Database"="default" \\\n--work-group myWorkGroup\n</code></pre>\nNow, running parameterized queries against Athena with execution parameters:\nBash\n<pre><code class=\"lang-\">aws athena start-query-execution \\\n--query-string "SELECT * FROM table WHERE x = ? AND y = ? AND z = ?" \\\n--query-execution-context "Database"="default" \\\n--work-group myWorkGroup \\\n--execution-parameters $ARG1 $ARG2 $ARG3\n</code></pre>\nThe following is an example of a command that creates a prepared statement in your Athena workgroup. To learn more about creating prepared statements, refer to <a href=\"https://docs.aws.amazon.com/athena/latest/ug/querying-with-prepared-statements.html\" target=\"_blank\">Querying with prepared statements</a>.\nBash\n<pre><code class=\"lang-\">aws athena start-query-execution \\\n--query-string "PREPARE my-prepared-statement FROM SELECT * FROM table WHERE x = ? AND y = ? AND z = ?" \\\n--query-execution-context "Database"="default" \\\n--work-group myWorkGroup\n</code></pre>\nPreviously, running parameterized queries against prepared statements without execution parameters:\nBash\n<pre><code class=\"lang-\">aws athena start-query-execution \\\n--query-string "EXECUTE my-prepared-statement USING $ARG1, $ARG2, $ARG3“ \\\n--query-execution-context "Database"="default" \\\n--work-group myWorkGroup\n</code></pre>\nNow, running parameterized queries against prepared statements with execution parameters:\nBash\n<pre><code class=\"lang-\">aws athena start-query-execution \\\n--query-string "EXECUTE my-prepared-statement" \\\n--query-execution-context "Database"="default" \\\n--work-group myWorkGroup \\\n--execution-parameters $ARG1 $ARG2 $ARG3\n</code></pre>\n<h4><a id=\"Sample_architecture_70\"></a>Sample architecture</h4>\nThe purpose of this sample architecture is to apply the <code>ExecutionParameters</code> feature when running Athena queries, with and without prepared statements. This is not intended to be a DaaS solution for use with your production data.\nThis sample architecture exhibits a DaaS application with a user interface (UI) that presents three Athena parameterized queries written against the public <a href=\"https://s3.amazonaws.com/amazon-reviews-pds/readme.html\" target=\"_blank\">Amazon.com customer reviews dataset</a>. The following figure depicts this workflow when a user submits a query to Athena. This example uses <a href=\"https://aws.amazon.com/amplify/\" target=\"_blank\">AWS Amplify</a> to host a front-end application. The application calls an <a href=\"https://aws.amazon.com/api-gateway\" target=\"_blank\">Amazon API Gateway</a> HTTP API, which invokes AWS Lambda functions to authenticate requests, fetch the Athena prepared statements and named queries, and run the parameterized queries against Athena. The Lambda function uses the name of the Athena workgroup, statement name, statement type (prepared statement or not), and a list of query parameters input by the user. Athena queries data in an Amazon Simple Storage Service (Amazon S3), bucket which is cataloged in AWS Glue, and presents results to the user on the DaaS application UI.\n<img src=\"https://dev-media.amazoncloud.cn/40a6dc42bb264bff823ab114d5bedd93_image.png\" alt=\"image.png\" />\nEnd-users of the DaaS application UI can run only parameterized queries against Athena. The DaaS application UI demonstrates two ways to run parameterized queries with execution parameters: with and without prepared statements. In both cases, the Lambda function submits the query, waits for the query to complete, and provides the results that match the query parameters. The following figure depicts the DaaS application UI.\n<img src=\"https://dev-media.amazoncloud.cn/340f939043e24dba93df99bef45a0567_image.png\" alt=\"image.png\" />\nYou may want your users to have the ability to list all Athena prepared statements within your Athena workgroup, select a statement, input arguments, and run the query; on the left side of the DaaS application UI, you use an EXECUTE statement to query the data lake with an Athena prepared statement. You may have several reporting queries maintained in your code base. In this case, your users select a statement, input arguments, and run the query. On the right side of the DaaS application UI, you use a SELECT statement to use Athena parameterized queries without prepared statements.\n<h4><a id=\"Prerequisites_86\"></a>Prerequisites</h4>\nThis post uses the following AWS services to demonstrate a DaaS architecture pattern that uses Athena to query the Amazon.com customer reviews dataset:\n<ul>\n<li><a href=\"https://aws.amazon.com/amplify/\" target=\"_blank\">AWS Amplify</a></li>\n<li><a href=\"https://aws.amazon.com/api-gateway\" target=\"_blank\">Amazon API Gateway</a></li>\n<li><a href=\"http://aws.amazon.com/athena\" target=\"_blank\">Amazon Athena</a></li>\n<li><a href=\"http://aws.amazon.com/cloudformation\" target=\"_blank\">AWS CloudFormation</a></li>\n<li><a href=\"https://aws.amazon.com/dynamodb/\" target=\"_blank\">Amazon DynamoDB</a></li>\n<li><a href=\"https://aws.amazon.com/glue\" target=\"_blank\">AWS Glue</a> and the AWS Glue Data Catalog</li>\n<li><a href=\"http://aws.amazon.com/iam\" target=\"_blank\">AWS Identity and Access Management</a> (IAM)</li>\n<li><a href=\"http://aws.amazon.com/lambda\" target=\"_blank\">AWS Lambda</a></li>\n<li><a href=\"https://docs.aws.amazon.com/AmazonS3/latest/userguide/Welcome.html\" target=\"_blank\">Amazon S3</a></li>\n</ul>\nThis post assumes you have the following:\n<ul>\n<li>An AWS account. For instructions, refer to <a href=\"https://docs.aws.amazon.com/accounts/latest/reference/manage-acct-creating.html\" target=\"_blank\">Creating an AWS account</a>.</li>\n<li>A CloudTrail trail created. For instructions, refer to <a href=\"https://docs.aws.amazon.com/awscloudtrail/latest/userguide/cloudtrail-create-a-trail-using-the-console-first-time.html\" target=\"_blank\">Creating a trail</a>.</li>\n<li>An S3 bucket created. For instructions, refer to <a href=\"https://docs.aws.amazon.com/AmazonS3/latest/userguide/create-bucket-overview.html\" target=\"_blank\">Creating a bucket</a>.</li>\n<li><a href=\"https://nodejs.org/en/\" target=\"_blank\">Node.js</a> version 16+ installed on your device.</li>\n</ul>\n<h4><a id=\"Deploy_the_CloudFormation_stack_109\"></a>Deploy the CloudFormation stack</h4>\nIn this section, you deploy a CloudFormation template that creates the following resources:\n<ul>\n<li>AWS Glue Data Catalog database</li>\n<li>AWS Glue Data Catalog table</li>\n<li>An Athena workgroup</li>\n<li>Three Athena prepared statements</li>\n<li>Three Athena named queries</li>\n<li>The API Gateway HTTP API</li>\n<li>The Lambda execution role for Athena queries</li>\n<li>The Lambda execution role for API Gateway HTTP API authorization</li>\n<li>Five Lambda functions:\n<ul>\n<li>Update the AWS Glue Data Catalog</li>\n<li>Authorize API Gateway requests</li>\n<li>Submit Athena queries</li>\n<li>List Athena prepared statements</li>\n<li>List Athena named queries \nNote that this CloudFormation template was tested in AWS Regions <code>ap-southeast-2</code>, <code>ca-central-1</code>, <code>eu-west-2</code>, <code>us-east-1</code>, <code>us-east-2</code>, and <code>us-west-2</code>. Note that deploying this into your AWS account will incur cost. Steps for cleaning up the resources are included later in this post.</li>\n</ul>\n</li>\n</ul>\nTo deploy the CloudFormation stack, follow these steps:\n<ol>\n<li>Navigate to this post’s <a href=\"https://github.com/aws-samples/amazon-athena-execution-parameters-blog\" target=\"_blank\">GitHub repository</a>.</li>\n<li>Clone the repository or copy the CloudFormation template <code>athena-parameterized-queries.yaml</code>.</li>\n<li>On the AWS CloudFormation console, choose Create stack.</li>\n<li>Select Upload a template file and choose Choose file.</li>\n<li>Upload <code>athena-parameterized-queries.yaml</code>, then choose Next.</li>\n<li>On the Specify stack details page, enter the stack name <code>athena-parameterized-queries</code>.</li>\n<li>On the same page, there are two parameters: \na. For S3QueryResultsBucketName, enter the S3 bucket name in your AWS account and in the same AWS Region as where you’re running your CloudFormation stack. (For this post, we use the bucket name value, like <code>my-bucket</code>). \nb. For APIPassphrase, enter a passphrase to authenticate API requests. You use this later.</li>\n<li>Choose Next.</li>\n<li>On the Configure stack options page, choose Next.</li>\n<li>On the Review page, select I acknowledge that AWS CloudFormation might create IAM resources with custom names, and choose Create stack.</li>\n</ol>\nThe script takes less than two minutes to run and change to a <code>CREATE_COMPLETE</code> state. If you deploy the stack twice in the same AWS account and Region, some resources may already exist, and the process fails with a message indicating the resource already exists in another template.\n<ol start=\"11\">\n<li>On the Outputs tab, copy the <code>APIEndpoint</code> value to use later.</li>\n</ol>\nFor least-privilege authorization for deployment of the CloudFormation template, you can create an <a href=\"https://docs.aws.amazon.com/AWSCloudFormation/latest/UserGuide/using-iam-servicerole.html\" target=\"_blank\">AWS CloudFormation service role with</a> the following <a href=\"\" target=\"_blank\"></a> actions. To do this, you must create an <a href=\"https://docs.aws.amazon.com/IAM/latest/UserGuide/access_policies_create.html\" target=\"_blank\">IAM policy</a> and <a href=\"https://docs.aws.amazon.com/IAM/latest/UserGuide/id_roles_create.html\" target=\"_blank\">IAM role</a>, and choose this role when configuring stack options. You need to replace the values for <code>${Partition}</code>, <code>${AccountId}</code>, and <code>${Region}</code> with your own values; for more information on these values, refer to <a href=\"https://docs.aws.amazon.com/AWSCloudFormation/latest/UserGuide/pseudo-parameter-reference.html\" target=\"_blank\">Pseudo parameters reference</a>.\nJson\n<pre><code class=\"lang-\">{\n "Version": "2012-10-17",\n "Statement": [\n {\n "Sid": "IAM",\n "Effect": "Allow",\n "Action": [\n "iam:GetRole",\n "iam:UntagRole",\n "iam:TagRole",\n "iam:CreateRole",\n "iam:DeleteRole",\n "iam:PassRole",\n "iam:GetRolePolicy",\n "iam:PutRolePolicy",\n "iam:AttachRolePolicy",\n "iam:TagPolicy",\n "iam:DeleteRolePolicy",\n "iam:DetachRolePolicy",\n "iam:UntagPolicy"\n ],\n "Resource": [\n "arn:${Partition}:iam::${AccountId}:role/LambdaAthenaExecutionRole-athena-parameterized-queries",\n "arn:${Partition}:iam::${AccountId}:role/service-role/LambdaAthenaExecutionRole-athena-parameterized-queries",\n "arn:${Partition}:iam::${AccountId}:role/service-role/LambdaAuthorizerExecutionRole-athena-parameterized-queries",\n "arn:${Partition}:iam::${AccountId}:role/LambdaAuthorizerExecutionRole-athena-parameterized-queries"\n ]\n },\n {\n "Sid": "LAMBDA",\n "Effect": "Allow",\n "Action": [\n "lambda:CreateFunction",\n "lambda:GetFunction",\n "lambda:InvokeFunction",\n "lambda:AddPermission",\n "lambda:DeleteFunction",\n "lambda:RemovePermission",\n "lambda:UpdateFunctionConfiguration"\n ],\n "Resource": [\n "arn:${Partition}:lambda:${Region}:${AccountId}:function:LambdaRepairFunction-athena-parameterized-queries",\n "arn:${Partition}:lambda:${Region}:${AccountId}:function:LambdaAthenaFunction-athena-parameterized-queries",\n "arn:${Partition}:lambda:${Region}:${AccountId}:function:LambdaAuthorizerFunction-athena-parameterized-queries",\n "arn:${Partition}:lambda:${Region}:${AccountId}:function:GetPrepStatements-athena-parameterized-queries",\n "arn:${Partition}:lambda:${Region}:${AccountId}:function:GetNamedQueries-athena-parameterized-queries"\n ]\n },\n {\n "Sid": "ATHENA",\n "Effect": "Allow",\n "Action": [\n "athena:GetWorkGroup",\n "athena:CreateWorkGroup",\n "athena:DeleteWorkGroup",\n "athena:DeleteNamedQuery",\n "athena:CreateNamedQuery",\n "athena:CreatePreparedStatement",\n "athena:DeletePreparedStatement",\n "athena:GetPreparedStatement"\n ],\n "Resource": [\n "arn:${Partition}:athena:${Region}:${AccountId}:workgroup/ParameterizedStatementsWG"\n ]\n },\n {\n "Sid": "GLUE",\n "Effect": "Allow",\n "Action": [\n "glue:CreateDatabase",\n "glue:DeleteDatabase",\n "glue:CreateTable",\n "glue:DeleteTable"\n ],\n "Resource": [\n "arn:${Partition}:glue:${Region}:${AccountId}:catalog",\n "arn:${Partition}:glue:${Region}:${AccountId}:database/athena_prepared_statements",\n "arn:${Partition}:glue:${Region}:${AccountId}:table/athena_prepared_statements/*",\n "arn:${Partition}:glue:${Region}:${AccountId}:userDefinedFunction/athena_prepared_statements/*"\n ]\n },\n {\n "Sid": "APIGATEWAY",\n "Effect": "Allow",\n "Action": [\n "apigateway:DELETE",\n "apigateway:PUT",\n "apigateway:PATCH",\n "apigateway:POST",\n "apigateway:TagResource",\n "apigateway:UntagResource"\n ],\n "Resource": [\n "arn:${Partition}:apigateway:${Region}::/apis/*/integrations*",\n "arn:${Partition}:apigateway:${Region}::/apis/*/stages*",\n "arn:${Partition}:apigateway:${Region}::/apis/*/authorizers*",\n "arn:${Partition}:apigateway:${Region}::/apis/*/routes*",\n "arn:${Partition}:apigateway:${Region}::/tags/arn%3Aaws%3Aapigateway%3A${Region}%3A%3A%2Fv2%2Fapis%2F*"\n ]\n },\n {\n "Sid": "APIGATEWAYMANAGEAPI",\n "Effect": "Allow",\n "Action": [\n "apigateway:DELETE",\n "apigateway:PUT",\n "apigateway:PATCH",\n "apigateway:POST",\n "apigateway:GET"\n ],\n "Resource": [\n "arn:${Partition}:apigateway:${Region}::/apis"\n ],\n "Condition": {\n "StringEquals": {\n "apigateway:Request/ApiName": "AthenaAPI-athena-parameterized-queries"\n }\n }\n },\n {\n "Sid": "APIGATEWAYMANAGEAPI2",\n "Effect": "Allow",\n "Action": [\n "apigateway:DELETE",\n "apigateway:PUT",\n "apigateway:PATCH",\n "apigateway:POST",\n "apigateway:GET"\n ],\n "Resource": [\n "arn:${Partition}:apigateway:${Region}::/apis/*"\n ],\n "Condition": {\n "StringEquals": {\n "apigateway:Resource/ApiName": "AthenaAPI-athena-parameterized-queries"\n }\n }\n },\n {\n "Sid": "APIGATEWAYGET",\n "Effect": "Allow",\n "Action": [\n "apigateway:GET"\n ],\n "Resource": [\n "arn:${Partition}:apigateway:${Region}::/apis/*"\n ]\n },\n {\n "Sid": "LAMBDALAYER",\n "Effect": "Allow",\n "Action": [\n "lambda:GetLayerVersion"\n ],\n "Resource": [\n "arn:${Partition}:lambda:*:280475519630:layer:boto3-1_24*"\n ]\n }\n ]\n}\n</code></pre>\nAfter you create the CloudFormation stack, you use the AWS management console to deploy an Amplify application and view the Lambda functions. The following is the scoped-down IAM policy that you can attach to an IAM user or role to perform these operations:\nJSON\n<pre><code class=\"lang-\">{\n "Version": "2012-10-17",\n "Statement": [\n {\n "Sid": "AmplifyCreateApp",\n "Effect": "Allow",\n "Action": [\n "amplify:CreateBranch",\n "amplify:StartDeployment",\n "amplify:CreateDeployment",\n "amplify:CreateApp",\n "amplify:StartJob"\n ],\n "Resource": "arn:${Partition}:amplify:${Region}:${AccountId}:apps/*"\n },\n {\n "Sid": "AmplifyList",\n "Effect": "Allow",\n "Action": "amplify:List*",\n "Resource": "arn:${Partition}:amplify:${Region}:${AccountId}:apps/*"\n },\n {\n "Sid": "AmplifyGet",\n "Effect": "Allow",\n "Action": "amplify:GetJob",\n "Resource": "arn:${Partition}:amplify:${Region}:${AccountId}:apps/*"\n },\n {\n "Sid": "LambdaList",\n "Effect": "Allow",\n "Action": [\n "lambda:GetAccountSettings",\n "lambda:ListFunctions"\n ],\n "Resource": "*"\n },\n {\n "Sid": "LambdaFunction",\n "Effect": "Allow",\n "Action": [\n "lambda:GetFunction"\n ],\n "Resource": "arn:${Partition}:lambda:${Region}:${AccountId}:function:LambdaAthenaFunction-athena-parameterized-queries"\n }\n ]\n}\n</code></pre>\nNote that you need the following IAM policy when deploying your Amplify application to set a global password, and when cleaning up your resources to delete the Amplify application. Remember to replace ${AppARN} with the ARN of the Amplify application. You can find the ARN after creating the Amplify app on the General tab in the App Settings section of the Amplify console.\nJSON\n<pre><code class=\"lang-\">{\n "Version": "2012-10-17",\n "Statement": [\n {\n "Sid": "UpdateAndDeleteAmplifyApp",\n "Effect": "Allow",\n "Action": [\n "amplify:DeleteApp",\n "amplify:UpdateApp"\n ],\n "Resource": "${AppARN}"\n }\n ]\n}\n</code></pre>\n<h4><a id=\"Deploy_the_Amplify_application_388\"></a>Deploy the Amplify application</h4>\nIn this section, you deploy your Amplify application.\n<ol>\n<li>In the cloned repository, open <code>web-application/.env</code> in a text editor.</li>\n<li>Set <code>AWS_API_ENDPOINT</code> as the <code>APIEndpoint</code> value from the CloudFormation stack Outputs For example: <code>AWS_API_ENDPOINT="https://123456abcd.execute-api.your-region.amazonaws.com"</code>.</li>\n<li>Set <code>API_AUTH_CODE</code> as the value you input as the CloudFormation stack’s <code>APIPassphrase</code> parameter argument. For example: <code>API_AUTH_CODE="YOUR_PASSPHRASE"</code>.</li>\n<li>Navigate to the <code>web-application/ directory and run </code>npm install```.</li>\n<li>Run <code>npm run build</code> to compile distribution assets.</li>\n<li>On the Amplify console, choose All apps.</li>\n<li>Choose New app.</li>\n<li>Select Host web app, select Deploy without Git provider, then choose Continue.</li>\n<li>For App name, enter <code>Athena Parameterized Queries App</code>.</li>\n<li>For Environment name¸ you don’t need to enter a value.</li>\n<li>Select Drag and Drop.</li>\n<li>Locate the ``dist/<code>directory inside</code>web-application/```, drag it into the window and drop it. Ensure you drag the entire directory, not the files within it.</li>\n</ol>\n<img src=\"https://dev-media.amazoncloud.cn/f1184ebefae0494dba58a30e7067980f_image.png\" alt=\"image.png\" />\n<ol start=\"13\">\n<li>Choose Save and deploy to deploy the web application on Amplify.</li>\n</ol>\nThis step takes less than a minute to complete.\n<ol start=\"14\">\n<li>Under App settings, choose Access control, then choose Manage access.</li>\n<li>Select Apply a global password, then enter values for Username and Password.</li>\n</ol>\nYou use these credentials to access your Amplify application.\n<h4><a id=\"Access_your_Amplify_application_and_run_queries_418\"></a>Access your Amplify application and run queries</h4>\nIn this section, you use the Amplify application to run Athena parameterized queries against the Amazon.com customer reviews dataset. The left side of the application shows how you can run parameterized queries using Athena prepared statements. The right side of the application shows how you can run parameterized queries without prepared statements, such as if the queries are written in your code. The sample in this post uses named queries within the Athena workgroup. For more information about named queries, refer to <a href=\"https://docs.aws.amazon.com/athena/latest/APIReference/API_NamedQuery.html\" target=\"_blank\">NamedQuery</a>.\n<ol>\n<li>Open the Amplify web application link located under Domain. For example: <code>https://dev123.abcd12345xyz.amplifyapp.com/</code>.</li>\n<li>In the Sign in prompt, enter the user name and password you provided as the Amplify application global password.</li>\n<li>For Workgroup Name, choose the <code>ParameterizedStatementsWG</code> workgroup.</li>\n<li>Choose a statement example on the Prepared Statement or SQL Statement drop-down menu.</li>\n</ol>\nSelecting a statement displays a description about the query, including examples of parameters you can try with this statement, and the original SQL query string. SQL parameters of type string must be surrounded by single quotes, for example: <code>'your_string_value'</code>.\n<ol start=\"5\">\n<li>Enter your query parameters.</li>\n</ol>\nThe following figure shows an example of the parameters to input for the <code>product_helpful_reviews</code> prepared statement.\n<img src=\"https://dev-media.amazoncloud.cn/b0b9163cc3594389a68676221b954865_image.png\" alt=\"image.png\" />\n<ol start=\"6\">\n<li>Choose Run Query to send the query request to the API endpoint.</li>\n</ol>\nAfter the query runs, the sample application presents the results in a table format, as depicted in the following screenshot. This is one of many ways to present results, and your application can display results in the format which makes the most sense for your users. The complete query workflow is depicted in the previous architecture diagram.\n<img src=\"https://dev-media.amazoncloud.cn/6a48a038e73844058d88a3f7d583735a_image.png\" alt=\"image.png\" />\n<h4><a id=\"Using_execution_parameters_with_the_AWS_SDK_for_Python_Boto3_444\"></a>Using execution parameters with the AWS SDK for Python (Boto3)</h4>\nIn this section, you inspect the Lambda function code for using the <code>StartQueryExecution</code> API with and without prepared statements.\n<ol>\n<li>On the Lambda console, choose Functions.</li>\n<li>Navigate to the <code>LambdaAthenaFunction-athena-parameterized-queries</code> function.</li>\n<li>Choose the Code Source window.</li>\n</ol>\nExamples of passing parameters to the Athena <code>StartQueryExecution</code> API using the AWS SDK for Python (Boto3) begin on lines 39 and 49. Note the <code>ExecutionParameters</code> option on lines 45 and 55.\nThe following code uses execution parameters with Athena prepared statements:\nPython\n<pre><code class=\"lang-\">response = athena.start_query_execution(\n QueryString=f'EXECUTE {statement}', # Example: "EXECUTE prepared_statement_name"\n WorkGroup=workgroup,\n QueryExecutionContext={\n 'Database': 'athena_prepared_statements'\n },\n ExecutionParameters=input_parameters\n)\n</code></pre>\nThe following code uses execution parameters without Athena prepared statements:\nPython\n<pre><code class=\"lang-\">response = athena.start_query_execution(\n QueryString=statement, # Example: "SELECT * FROM TABLE WHERE parameter_name = ?"\n WorkGroup=workgroup,\n QueryExecutionContext={\n 'Database': 'athena_prepared_statements'\n },\n ExecutionParameters=input_parameters\n)\n</code></pre>\n<h4><a id=\"Clean_up_484\"></a>Clean up</h4>\nIn this post, you created several components, which generate cost. To avoid incurring future charges, remove the resources with the following steps:\n<ol>\n<li><a href=\"https://docs.aws.amazon.com/AmazonS3/latest/userguide/using-folders.html#delete-folders\" target=\"_blank\">Delete the S3 bucket’s results prefix</a> created after you ran a query on your workgroup.</li>\n</ol>\nWith the default template, the prefix is named <code><S3QueryResultsBucketName>/athena-results</code>. Use caution in this step. Unless you are <a href=\"https://docs.aws.amazon.com/AmazonS3/latest/userguide/Versioning.html\" target=\"_blank\">using versioning on your S3 bucket</a>, deleting S3 objects can’t be undone.\n<ol start=\"2\">\n<li>On the Amplify console, select the app to delete and on the Actions menu, choose Delete app, then confirm.</li>\n<li>On the AWS CloudFormation console, select the stack to delete, choose Delete, and confirm.</li>\n</ol>\n<h4><a id=\"Conclusion_497\"></a>Conclusion</h4>\nIn this post, we showed how you can build a DaaS application using Athena parameterized queries. The <code>StartQueryExecution</code> API in Athena now supports execution parameters, which allows you to run Athena queries as parameterized queries. You can decouple your execution parameters from your query strings, and use parameterized queries without being limited to the Athena workgroups where you have created prepared statements. You can take advantage of the security benefits Athena offers with parameterized queries, and developers no longer need to build query strings manually. In this post, you learned how to use execution parameters, and you deployed a DaaS reference architecture to see how parameterized queries can be applied.\nYou can get started with Athena parameterized queries by using the Athena console, the <a href=\"https://docs.aws.amazon.com/cli/latest/reference/athena/\" target=\"_blank\">AWS CLI</a>, or the <a href=\"https://boto3.amazonaws.com/v1/documentation/api/latest/reference/services/athena.html\" target=\"_blank\">AWS SDK</a>. To learn more about Athena, refer to the <a href=\"https://docs.aws.amazon.com/athena/latest/ug/getting-started.html\" target=\"_blank\">Amazon Athena User Guide</a>. For more information on using execution parameters, refer to <a href=\"https://docs.aws.amazon.com/athena/latest/ug/querying-with-prepared-statements.html\" target=\"_blank\">Querying with parameterized queries</a>.\nThanks for reading this post! If you have questions about Athena prepared statements and parameterized queries, don’t hesitate to leave a comment.\n<h5><a id=\"About_the_Authors_507\"></a>About the Authors</h5>\n<img src=\"https://dev-media.amazoncloud.cn/a8ef2f4f8bc4440f9a69a30faea50f6e_image.png\" alt=\"image.png\" />\nBlayze Stefaniak is a Senior Solutions Architect for the Technical Strategist Program supporting Executive Customer Programs in AWS Marketing. He has experience working across industries including healthcare, automotive, and public sector. He is passionate about breaking down complex situations into something practical and actionable. In his spare time, you can find Blayze listening to Star Wars audiobooks, trying to make his dogs laugh, and probably talking on mute.\n<img src=\"https://dev-media.amazoncloud.cn/669cbc714cfd4b098307b1512f125d69_image.png\" alt=\"image.png\" />\nDaniel Tatarkin is a Solutions Architect at Amazon Web Services (AWS) supporting Federal Financial organizations. He is passionate about big data analytics and serverless technologies. Outside of work, he enjoys learning about personal finance, coffee, and trying out new programming languages for fun.\n<img src=\"https://dev-media.amazoncloud.cn/5e7530d2ba844103bff56872d4894f2d_image.png\" alt=\"image.png\" />\nMatt Boyd is a Senior Solutions Architect at AWS working with federal financial organizations. He is passionate about effective cloud management and governance, as well as data governance strategies. When he’s not working, he enjoys running, weight lifting, and teaching his elementary-age son ethical hacking skills.\n"}

亚马逊云科技解决方案基于行业客户应用场景及技术领域的解决方案

联系亚马逊云科技专家