I’m encountering an issue while trying to connect an on-premises Clickhouse database to Amazon Quicksight. I’m hoping someone can provide some insights or solutions.
Setup:
Database: Clickhouse (hosted on-prem in our private cloud)
Attempting to connect via Quicksight to add a dataset
Using MySQL interface as recommended by Clickhouse documentation
Issue:
When attempting to connect through Quicksight, I encounter the error:
Copy
MySQL : Public Key Retrieval is not allowed
Additional Information:
I can successfully connect to the Clickhouse database using the MySQL CLI with this command:
mysql -h server.name -P 9004 -u username -p
According to Google search, the typical solution for this error is to set allowPublicKeyRetrieval=true, but Quicksight doesn’t provide an option to add custom connection parameters.
Questions:
Is there a way to add the allowPublicKeyRetrieval=true parameter (or an equivalent) in Quicksight’s connection settings?
Are there any Quicksight-specific settings or workarounds for this issue when connecting to Clickhouse via MySQL protocol?
Could this be related to how Quicksight’s MySQL connector is configured compared to the CLI client?
Are there any recommended security settings or connection methods for linking Quicksight to a Clickhouse database that might avoid this issue?
Any advice, workarounds, or explanations would be greatly appreciated. Thank you in advance for your help!
Welcome to the community! Thanks for your question!
Connecting Amazon QuickSight to an on-premises Clickhouse database using the MySQL protocol can present some challenges, particularly with the allowPublicKeyRetrieval parameter. Here are a few strategies you can try to resolve the issue:
IAM Authentication (Alternative Approach):
If possible, consider using IAM authentication to connect to the database. This might bypass the need for the allowPublicKeyRetrieval=true parameter. However, this requires your database to support IAM authentication, which might not be the case for Clickhouse.
VPC and AWS PrivateLink:
Ensure that your on-premises Clickhouse database is accessible from the AWS environment where QuickSight resides. Using AWS PrivateLink and setting up a VPC endpoint for your Clickhouse database can help facilitate this connection securely.
Intermediate Database Proxy:
Set up an intermediate proxy server that supports the required MySQL connection parameters. You can use tools like HAProxy or MySQL Proxy to create a bridge between QuickSight and your Clickhouse database. Configure the proxy to allow public key retrieval and handle the connection parameters appropriately.
Clickhouse JDBC Connector:
As an alternative, consider using the Clickhouse JDBC connector if QuickSight supports custom JDBC drivers. This can provide more flexibility in managing connection parameters and might bypass the MySQL-specific constraints.
AWS Glue as a Data Integration Layer:
Use AWS Glue to create a data integration layer between your Clickhouse database and QuickSight. AWS Glue can extract data from Clickhouse, transform it as needed, and load it into an AWS-managed database (e.g., Amazon RDS for MySQL) that QuickSight can easily connect to.
Contact AWS Support:
If the above solutions are not feasible, consider reaching out to AWS Support. They might have insights or undocumented methods to address this specific issue with QuickSight. Here are the steps to open a support case.
Given the typical solution involving the allowPublicKeyRetrieval parameter, the lack of custom connection parameter support in QuickSight is a known limitation. Utilizing an intermediate proxy or data integration layer can often work around these limitations.
Here are some additional resources and documentation that might help:
Hello @Karmaknight1369, since we have not heard back from you with any remaining questions, I will mark @Xclipse’s response as the solution. Please let us know if you need further assistance with this issue, and we can help guide you further. Thank you!