Hologres:Apache NiFi

How it works

The pipeline uses three processors in sequence:

1. GetFile — reads a local file in JSON format

2. ConvertJSONToSQL — converts each JSON element into a SQL INSERT statement

3. PutSQL — executes the INSERT statement and writes the data to Hologres

Prerequisites

Before you begin, make sure you have:

• A running Hologres instance. See Purchase a Hologres instance.

• Apache NiFi installed. See How to install and start NiFi.

Step 1: Gather connection details

Collect the following information before starting the configuration. You'll need it when setting up the DBCPConnectionPool controller service.

Step 2: Download the PostgreSQL JDBC driver

Apache NiFi uses a JDBC driver to connect to Hologres, which is PostgreSQL-compatible.

1. Download the PostgreSQL JDBC driver (version 42.2.25 or later) from the PostgreSQL official website.

2. Place the JAR file in a directory accessible by NiFi, for example: /opt/nifi/nifi-current/jdbc_driver/.

3. Note the absolute path to the JAR file — you'll enter it when configuring DBCPConnectionPool.

Step 3: Create a database and table

1. Log in to your Hologres instance and create a database named demo. See Create a database.

2. Run the following SQL statement to create the target table:

   DROP TABLE IF EXISTS user_info;
   
   CREATE TABLE IF NOT EXISTS user_info (
       id int,
       first_name text,
       last_name text,
       email text
   );

Step 4: Configure the GetFile processor

1. Add a GetFile processor. See Adding a processor in the NiFi documentation.

2. On the PROPERTIES tab, set Input Directory to the path where your JSON file is stored. For example, if your JSON file is at /opt/nifi/nifi-current/file_source/user_info.json, the file content looks like this:

   {
       "id": 1,
       "first_name": "Sig",
       "last_name": "Olivo",
       "email": "solivo0@blinklist.com"
   }

   

3. Click APPLY.

Step 5: Configure the ConvertJSONToSQL processor

1. Add a ConvertJSONToSQL processor.

2. In JDBC Connection Pool, add a controller service. Set Compatible Controller Services to DBCPConnectionPool and Controller Service Name to hologres.

   

3. Click the arrow button on the right side of the JDBC Connection Pool row to open the controller service configuration.

4. Find the DBCPConnectionPool service you just created and click the settings button.

   

5. On the PROPERTIES tab, configure the following parameters:

   Parameter	Value	Notes
   Database Connection URL	jdbc:postgresql://<endpoint>/<database name> — for example, jdbc:postgresql://hgpostcn-cn-xxxxxxxxxxx-cn-shanghai.hologres.aliyuncs.com:80/demo	Use either a public network or VPC endpoint. To get the endpoint, go to the instance details page in the Hologres Management Console.
   Database Driver Class Name	org.postgresql.Driver	Not applicable
   Database Driver Location(s)	Absolute path to the JDBC driver JAR — for example, /opt/nifi/nifi-current/jdbc_driver/postgresql-42.3.4.jar	Must be accessible by the NiFi process. Use the path from Step 2.
   Database User	Your AccessKey ID	Obtained from AccessKey Management in Step 1.
   Password	Your AccessKey secret	Obtained from AccessKey Management in Step 1.

   

6. Click OK.

7. Click ENABLE to start the controller service.

8. Return to the ConvertJSONToSQL processor and configure the following parameters. For details on each parameter, see the NiFi documentation.

   Parameter	Value
   Statement Type	INSERT
   Table Name	user_info
   Schema Name	public

9. Click APPLY.

Step 6: Configure the PutSQL processor

1. Add a PutSQL processor.

2. Set JDBC Connection Pool to the DBCPConnectionPool controller service configured in the previous step (named hologres).

3. Set Support Fragmented Transactions to false.

4. Click APPLY.

Step 7: Start writing data

Set all processors to the running state. NiFi reads the JSON file and writes the data to Hologres.

Step 8: Verify the data

Run the following query in Hologres to confirm that the data was written successfully:

SELECT * FROM user_info;