Quick BI is a self-service business intelligence (BI) platform for cloud users and enterprises. This topic describes how to connect an ApsaraDB for ClickHouse cluster to Quick BI as a data source, so you can build dashboards and run ad hoc queries on your ClickHouse data.
Prerequisites
Before you begin, ensure that you have:
An ApsaraDB for ClickHouse cluster running version 20.3 or 22.8. For more information, see Create an ApsaraDB for ClickHouse cluster.
A database account (username and password). For more information, see Create an account.
The CIDR blocks of Quick BI added to the cluster whitelist. Quick BI must be able to reach your cluster over the network, so you must allowlist its IP ranges. For more information, see Configure a whitelist.
(Required for Internet connections) A public endpoint for the cluster, if Quick BI is not deployed in the same virtual private cloud (VPC) as the cluster. For more information, see Apply for and release a public endpoint.
If you do not know the CIDR blocks for Quick BI, proceed to step 5. After you select ClickHouse as the data source type, the CIDR blocks are displayed in the Create Data Source panel under the Before you connect Quick BI to the data source, add the following CIDR blocks of Quick BI to the IP address whitelist of the data source section.
Limitations
Only workspace administrators and developers can add data sources.
Only Quick BI Enterprise Standard Version and Quick BI Advanced Version support adding ApsaraDB for ClickHouse data sources in group workspaces.
Procedure
Log on to the Quick BI Console.
In the top navigation bar, click the Workbench tab.
In the left-side navigation pane, click Data Sources.
In the upper-right corner of the Data Sources page, click Create Data Source.
In the Create Data Source panel, click ClickHouse in the Database section.
In the Configure Connection step, configure the parameters based on your network type.
Internet
Parameter
Description
Database source
The source of the database. In this example, Alibaba Cloud is selected.
Name
The name of the data source. The name is displayed in the data source list.
The name can contain letters, digits, underscores (_), and hyphens (-).
Database Address
The public endpoint of the ApsaraDB for ClickHouse cluster.
Port Number
The port number that is used to connect to the ApsaraDB for ClickHouse cluster. Default value: 8123.
Database
The name of the database in the ApsaraDB for ClickHouse cluster.
Username
The username that you use to connect to the database in the ApsaraDB for ClickHouse cluster.
Password
The password that you use to connect to the database in the ApsaraDB for ClickHouse cluster.
Time Zone
The time zone of the database in the ApsaraDB for ClickHouse cluster.
SSL
If SSL is configured for the data source that you want to add, you can turn on SSL to protect data security.
VPC Data Source
If you use the Internet to connect Quick BI to the data source, VPC Data Source is turned off by default.
Initialize SQL
If Initialize SQL is turned on, you can configure the SQL statement that is initialized and executed after the data source is connected.
The SQL statements that are initialized and executed after each data source is connected can only be SET statements. The statements are separated with semicolons (;).
Enable File Upload
If you select Enable File Upload, you can upload files.
VPC
Parameter
Description
Database source
The source of the database. In this example, Alibaba Cloud is selected.
Name
The name of the data source. The name is displayed in the data source list.
The name can contain letters, digits, underscores (_), and hyphens (-).
Database Address
The internal endpoint of the ApsaraDB for ClickHouse cluster.
Port Number
The port number that is used to connect to the ApsaraDB for ClickHouse cluster.
Database
The name of the database in the ApsaraDB for ClickHouse cluster.
Username
The username that you use to connect to the database in the ApsaraDB for ClickHouse cluster.
Password
The password that you use to connect to the database in the ApsaraDB for ClickHouse cluster.
Time Zone
The time zone of the database in the ApsaraDB for ClickHouse cluster.
VPC Data Source
Turn on VPC Data Source and configure the following parameters:
AccessKey ID: the AccessKey ID that is used to purchase the cluster. For more information, see Obtain an AccessKey pair.
AccessKey Secret: the AccessKey secret that is used to purchase the cluster. For more information, see Obtain an AccessKey pair.
Instance ID: the ID of the ApsaraDB for ClickHouse cluster.
Region: the region in which the ApsaraDB for ClickHouse cluster is deployed.
Initialize SQL
If Initialize SQL is turned on, you can configure the SQL statement that is initialized and executed after the data source is connected.
The SQL statements that are initialized and executed after each data source is connected can only be SET statements. The statements are separated with semicolons (;).
Enable File Upload
If you select Enable File Upload, you can upload files.
Click Test Connection to verify that the data source can be connected.
After the data source passes the connectivity test, click OK.
The data source that you created is displayed in the My Data Sources list.
Add an ApsaraDB for ClickHouse data source
Log on to the Quick BI console.
In the top navigation bar, click the Workbench tab.
In the left-side navigation pane, click Data Sources.
In the upper-right corner of the Data Sources page, click Create Data Source.
In the Create Data Source panel, click ClickHouse in the Database section.
In the Configure Connection step, select your network type and configure the corresponding parameters. Choose the network type based on your deployment:
Internet: Use this if Quick BI and your cluster are in different VPCs, or if you are connecting over a public endpoint.
VPC: Use this if Quick BI and your cluster are in the same VPC.
Internet connection parameters
Parameter Description Database source Select Alibaba Cloud. Name The display name of the data source. Accepts letters, digits, underscores ( _), and hyphens (-).Database Address The public endpoint of the cluster. Port Number The port used to connect to the cluster. Default: 8123.Database The name of the database in the cluster. Username The username for the database account. Password The password for the database account. Time Zone The time zone of the database. SSL Turn on to encrypt the connection. Enable this if SSL is configured on the data source. VPC Data Source Turned off by default when connecting over the Internet. Initialize SQL (Optional) SQL statements to run after the connection is established. Only SETstatements are supported. Separate multiple statements with semicolons (;).Enable File Upload (Optional) Turn on to allow file uploads. VPC connection parameters
Parameter Description Database source Select Alibaba Cloud. Name The display name of the data source. Accepts letters, digits, underscores ( _), and hyphens (-).Database Address The internal endpoint of the cluster. Port Number The port used to connect to the cluster. Database The name of the database in the cluster. Username The username for the database account. Password The password for the database account. Time Zone The time zone of the database. VPC Data Source Turn on, then configure the following sub-parameters: AccessKey ID (the AccessKey ID used to purchase the cluster), AccessKey Secret (the AccessKey secret used to purchase the cluster), Instance ID (the ID of the cluster), and Region (the region where the cluster is deployed). For more information about AccessKey pairs, see Obtain an AccessKey pair. Initialize SQL (Optional) SQL statements to run after the connection is established. Only SETstatements are supported. Separate multiple statements with semicolons (;).Enable File Upload (Optional) Turn on to allow file uploads. Click Test Connection to verify that the cluster is reachable.
After the test passes, click OK.
The data source appears in the My Data Sources list.
What's next
After the data source is connected, create a dataset to make your ClickHouse data available for analysis:
Create a dataset: Add data tables from the cluster or write custom SQL for ad hoc analysis. See Create a dataset.
Once you have a dataset, build visualizations:
Create a dashboard: Add charts and visualizations for your data. See Create a dashboard and Overview of visualization charts.
Enable drill down: Explore data at deeper levels of granularity. See the Configure the drilling feature section.