ClickHouse

Connecting Your ClickHouse Database to Sequel

Welcome to Sequel! This guide will help you connect your ClickHouse database to Sequel, allowing you to run SQL queries using natural language and visualize results with charts and graphs.

Prerequisites

Before you begin, ensure you have the following:

• ClickHouse Database: Make sure your ClickHouse server is set up and running.
• Database Credentials: You will need the following details:
  • Host URL or IP address
  • Database name (optional, as ClickHouse can use the default database)
  • Username
  • Password
  • Port (default: 8123 for HTTP, 9000 for native/TCP protocol)
• Sequel Account: You must have a Sequel account. If you haven't signed up yet, you can create an account at Sequel.

Step 1: Navigate to Database Connections

1. On the Sequel dashboard, locate the database section from left hand side.
2. Click on Connect Database button to open the database management page.

Step 2: Add a New Connection

1. In the Databases section, click the ClickHouse card.

Step 3: Enter Connection Details

You will now need to enter the required information to connect your ClickHouse database:

1. Display Name: Give your connection a meaningful name (e.g., "ClickHouse Analytics" or "ClickHouse OLAP DB").
2. Host: Enter the hostname/URL or IP address of your ClickHouse server (Donot use https:// or http:// as prefix, this will be automatically added).
3. Port: The default port for ClickHouse HTTPs interface is 8443.
4. Username: Provide the ClickHouse username with access to the database. The default is often default.
5. Password: Enter the password for the ClickHouse user.

Understanding connection URLs

If you have a JDBC connection URL similar to the one below

jdbc:clickhouse://mxh1lsdlskdiw.ap-south-1.aws.clickhouse.cloud:8443?user=default&password=**************&ssl=true

You can extract the information required for the form as below Example Credentials:

Field	Value
Host	mxh1lsdlskdiw.ap-south-1.aws.clickhouse.cloud
Port	8443
Username	default
Password	(from connection details)

Step 4: Test and Save the Connection

Click on the "Test and Connection" button (if available) to validate the connection details. If the test is successful, the connection will be automatically saved.

Step 5: Query Your Database

Now that your ClickHouse database is connected, you can start querying it using natural language:

1. Return to the Sequel dashboard.
2. In the query input box, type a question or request, such as "Show me the top 10 most frequent events in the last 24 hours" or "What is the average query execution time?".
3. Sequel will convert your request into a ClickHouse SQL query, execute it against your database, and display the results.

Step 6: Visualize Data with Charts and Graphs

Sequel allows you to create visualizations based on your query results. You can request specific types of charts or graphs in natural language.

For example:

1. "Show me the event count trend over the last 30 days in a line chart."
2. "Display a bar graph of the top 10 slowest queries."
3. "Create a pie chart of the distribution of query types in the last hour."

Troubleshooting

If you encounter issues during the connection process:

1. Connection Errors:

   • Verify your credentials and ensure your ClickHouse server is reachable from Sequel's network.
   • Check if you need to whitelist Sequel's IP addresses in your firewall settings.

2. Protocol Issues:

   • Ensure you're using the correct port for your chosen protocol (8123 for HTTP, 8443 for HTTPS, 9000 for native/TCP).
   • If using HTTPS, make sure SSL is properly configured on both ends.

3. Authentication Problems:

   • Confirm that the user has the necessary permissions to access the specified database and execute queries.
   • If using non-default authentication methods, ensure they're properly configured.

4. Performance Concerns:

   • For large datasets, consider using the native/TCP protocol instead of HTTP for better performance.
   • Optimize your ClickHouse settings for your specific use case and hardware.

Conclusion

You've now connected your ClickHouse database to Sequel. You can leverage Sequel's natural language processing to query your ClickHouse database and generate insightful visualizations.

For optimal performance and security, always follow ClickHouse's best practices and keep your database and connection methods up to date.

If you have any further questions or need additional help, feel free to reach out to our support team or consult the Sequel documentation.

Happy querying!

---

Additional Resources

• ClickHouse Official Documentation
• ClickHouse Connection Settings
• Sequel Support