Connect Davinci to Hologres-Hologres(Hologres)-阿里云帮助中心

Davinci is an open-source business intelligence (BI) product developed by the Yixin data team. This topic shows you how to connect Davinci to Hologres for visual analysis.

Prerequisites

By default, Davinci does not read the schema of PostgreSQL external tables, so their metadata is not visible in the UI. However, you can still query them directly by using SQL statements. In the Hologres SQL query editor, you can browse the list of database tables, such as odps_orders, in the left-side panel. In the editor, enter select * from odps_orders and execute the statement. A successful query returns order data that includes the o_orderkey, o_custkey, o_orderstatus, o_totalprice, o_orderdate, o_orderpriority, o_clerk, o_shippriority, and o_comment fields.

Install Davinci

To install Davinci, see the Davinci official documentation.
Install the JDBC driver
1. Go to the official PostgreSQL website to download the PostgreSQL JDBC driver, version 42.2.18 or later.
2. Place the downloaded PostgreSQL JDBC driver package in the <Davinci-installation-directory>\lib directory.
3. By default, Davinci does not support PostgreSQL as a data source, so you must configure it as a custom data source. Go to the <Davinci-installation-directory>\lib directory and run the following command to create a custom data source configuration file.
```
mv datasource_driver.yml.example datasource_driver.yml
```
4. Edit the file and add the following content to configure the PostgreSQL data source.
```
postgresql:
    name: postgresql
    desc: postgresql
    version:
    driver: org.postgresql.Driver
    keyword_prefix: \"
    keyword_suffix: \"
    alias_prefix: \"
    alias_suffix: \"
```
5. Restart Davinci to complete the JDBC driver installation and PostgreSQL data source configuration.

Connect to Hologres

Log in to Davinci and click + Create in the upper-right corner to create a project.
After the project is created, click the project. In the left-side navigation pane, click the icon to go to the data source page. In the upper-right corner, click the icon to create a data source.

On the Add Source page, select the previously configured PostgreSQL data source and configure the data source. In the form, set Name to Hologres, Type to JDBC, and Database to postgresql. For JDBC URL, enter a connection string that starts with jdbc:postgresql:, and then enter the Username and Password. Click Test Connection to verify the connection, and then click Save.

Parameter	Description
Name	Enter a name for the data source, such as Hologres.
Type	Select `JDBC`.
Database	Select the `postgresql` data source that you configured.
Username	Your Alibaba Cloud account AccessKey ID. To create one, see Create an AccessKey pair.
Password	Your Alibaba Cloud account AccessKey Secret. To create one, see Create an AccessKey pair.
JDBC URL	Enter the connection URL in the format `jdbc:postgresql://<host>:<port>/<databasename>`, with the following parameters: host: The public or VPC network endpoint of the Hologres instance. The endpoint to use depends on your deployment environment. You can find the endpoint in the Network Information section of the instance details page in the Hologres management console. port: The port of the Hologres instance. You can find the port in the Network Information section of the instance details page in the Hologres management console. databasename: The name of the database that you created in Hologres.

Click Test Connection. The Test Success message confirms that the connection is successful.
Click Save to complete the Hologres data source configuration.

Data analysis

You can now use Davinci to visually analyze your Hologres data. For detailed instructions on creating charts and dashboards, see the Davinci official documentation.