Hologres:Apache NiFi

Prerequisites

• Activate Hologres. For more information, see Purchase a Hologres instance.

• Install Apache NiFi. For more information, see How to install and start NiFi.

Write a local JSON file to Hologres

1. Create a database and table

   1. Log on to your Hologres instance and create a database named demo. For more information, see Create a database.

   2. Create a data table.

      Run the following SQL statement to create a table. You will write data to this table later.

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

2. Configure the GetFile processor

   1. Add a GetFile processor.

      For more information, see Adding a Processor.

   2. Enter the path to the JSON file.

      In the Input Directory field on the PROPERTIES tab, enter the path where the JSON file is stored. For example, if you store the JSON file at /opt/nifi/nifi-current/file_source on the Apache NiFi server and name it user_info.json, the file content looks like this:

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

      The following figure shows a sample configuration.

   3. Click APPLY to save the configuration.

3. Configure the ConvertJSONToSQL processor

   1. Add a ConvertJSONToSQL processor.

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

      

   3. Click the rightwards arrow button on the far right of the JDBC Connection Pool row to configure the connection string.

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

      

   5. Configure the following parameters on the PROPERTIES tab of the Settings page.

      

      Parameter Name	Description	Notes
      Database Connection URL	The JDBC connection string for your Hologres instance uses the following format: jdbc:postgresql://<endpoint>/<database name>. 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)	The path to the PostgreSQL JDBC driver. Example: /opt/nifi/nifi-current/jdbc_driver/postgresql-42.3.4.jar.	Download the JDBC driver from the PostgreSQL official website. We recommend using version 42.2.25 or later.
      Database User	The AccessKey ID of your Alibaba Cloud account.	To get your AccessKey ID, go to AccessKey Management.
      Password	The AccessKey secret of your account.

   6. Click OK to complete the configuration.

   7. Click ENABLE to start the service.

   8. Return to the ConvertJSONToSQL processor and configure the following parameters. For more information, see the official documentation.

      Parameter Name	Description
      Statement Type	The type of SQL statement to execute. In this example, use INSERT.
      Table Name	The name of the table to write data to. In this example, use user_info.
      Schema Name	The schema name of the target table. In this example, use public.

   9. Click APPLY to complete the configuration.

4. Configure the PutSQL processor

   1. Add a PutSQL processor.

   2. Set JDBC Connection Pool to the DBCPConnectionPool you configured in the previous step. In this example, the DBCPConnectionPool name is hologres.

   3. Set Support Fragmented Transactions to false.

   4. Click APPLY to complete the configuration.

5. Start writing data

   You have now completed all configurations. Set all nodes to the running state. NiFi will start reading the JSON file and writing data to Hologres.

6. Query data

   Run the following command in Hologres to query the user_info table and view the inserted data.

   SELECT * FROM user_info;

   The query result appears as follows.