How do I resolve SQL exception errors with custom SQL data sources in Quick Suite?

Short description

You receive the following error message when Quick Suite queries or refreshes your SQL data source:

"Your database generated a SQL exception. This can be caused by query timeouts, resource constraints, unexpected DDL alterations before or during a query, and other database errors. Check your database settings and your query, and try again."

You might receive the preceding error message for the following reasons:

• The query times out.
• There's an issue with the virtual private cloud (VPC) connection to your data source.
• Your Quick Suite account doesn't have permission to access the data.
• Your Quick Suite service role doesn't have permission to access the AWS managed Key Management Service (AWS KMS) key.
• You used unsupported data types or functions.

Note: For more information about what caused the error, choose Show Details under the error message.

If you receive the preceding error message when you refresh the dataset, then you can find details about the error under Summary of the dataset. Choose Status Import Failed for more information.

If the preceding error message is shown on your dashboard, then navigate to Underlying analysis, and choose Concerned visual(s). Note the dataset that's on the left pane of the analysis, and then view the Summary of the dataset.

Resolution

Note: If you receive errors when you run AWS Command Line Interface (AWS CLI) commands, then see Troubleshooting errors for the AWS CLI. Also, make sure that you're using the most recent AWS CLI version.

The query times out

If the custom SQL query times out, then simplify the query to optimize runtime. For other query timeout solutions, see How do I resolve query timeout errors in Quick Suite?

There's an issue with the VPC connection to your data source

You receive one of the following error messages:

"Communications link failure The last packet successfully received from the server was #### milliseconds ago. The last packet sent successfully to the server was #### milliseconds ago."

-or-

"Communications link failure The last packet sent successfully to the server was 0 milliseconds ago. The driver has not received any packets from the server."

If you experience VPC connection issues to your data sources, then check the network security group in the VPC that's associated with the resource. For more information, see Configuring VPC connections in Amazon Quick Suite.

Your Quick Suite account doesn't have permission to access the data

If you experience a SQL exception error when you try to access data in an AWS service, then check your Quick Suite access to AWS services.

Complete the following steps:

1. Open the Quick Suite console.
2. Choose your user name on the application bar and then choose Manage Quick Suite.
3. In the navigation pane under Permissions, choose AWS resources.
4. Configure access to the supported data sources.

If you use AWS Organizations, then you receive the error when the necessary service control policies (SCPs) aren't assigned to you. Ask the Organizations administrator to verify the permissions that are assigned to you in your SCP settings.

If you're an Organizations administrator, then see Update a service control policy (SCP).

Your Quick Suite service role doesn't have permission to access the AWS managed KMS key

You receive the following error:

"If you are encrypting query results with KMS key, please ensure you are allowed to access your KMS key."

To confirm that the Quick Suite service role has the correct AWS KMS key permissions, complete the following steps:

1. Use the AWS Identity and Access Management (IAM) console to locate the Quick Suite service role ARN.
2. Use the Amazon Simple Storage Service (Amazon S3) console to find the AWS KMS key ARN. Go to the bucket that contains your data file. Choose the Overview tab, and then locate the AWS KMS key ID.
3. Run the create-grant AWS CLI command to add the Quick Suite service role ARN to the AWS KMS key policy:

   aws kms create-grant --key-id aws_kms_key_arn --grantee-principal quicksight_role_arn --operations Decrypt

   Note: Replace aws_kms_key_arn with the ARN of your AWS KMS key and quicksight_role_arn with the ARN of your Quick Sight service role.

You use unsupported data types or functions

If you try to import an unsupported data type or use an unsupported SQL function, then you receive an SQL exception error. To resolve this issue, check the SQL data source to determine if the data type or SQL function is supported.

To see what's supported, check the following resources:

• Supported data types from external data sources
• Supported data types and values
• Functions by category

Related information

Quotas for direct SQL queries

How do I create a private connection from Amazon Quick Suite to an Amazon Redshift cluster or an Amazon RDS DB instance that's in a private subnet?

Actions, resources, and condition keys for Amazon Quick Suite