This topic provides an example to describe how to connect Apache Nifi to Hologres.

Background information

Apache NiFi is an easy-to-use and reliable data processing and distribution platform. It is designed to facilitate the automatic management of data streams across systems. Apache Nifi has a web-based user interface (UI) that is highly interactive and user-friendly. You can use Apache Nifi to manage and process data streams in a system or across systems.

Prerequisites

Import a local JSON file to Hologres

The following figure shows how to import a local JSON file to Hologres. Flowchart
  1. GetFile processor: reads a JSON file.
  2. ConvertJSONToSQL processor: generates an INSERT statement based on the data in the JSON file.
  3. PutSQL processor: executes the generated INSERT statement to import the data in the JSON file to a database in Hologres.
  1. Create a database and a table in Hologres.
    1. Log on to your Hologres instance and create a database named demo. For more information, see Create a database.
    2. Create a table.
      Execute the following statements to create a table to which the data in the JSON file is to be imported. 
      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 a GetFile processor.
    1. Add a GetFile processor.
      For more information, see Apache NiFi Overview.
    2. Specify the path of the JSON file.
      In the Value field corresponding to the Input Directory property on the PROPERTIES tab, specify the path of the JSON file. In this example, the JSON file named user_info.json is stored in the /opt/nifi/nifi-current/file_source directory of the Apache Nifi server. The following information shows the data in the JSON file: 
      {
    "id": 1,
    "first_name": "Sig",
    "last_name": "Olivo",
    "email": "solivo0@blinklist.com"
}
      The following figure shows how to specify the path of the JSON file. getfile processor
    3. Click APPLY to save the configuration.
  3. Configure a ConvertJSONToSQL processor.
    1. Add a ConvertJSONToSQL processor.
    2. Add a controller service in JDBC Connection Pool. When you add the service, set the Compatible Controller Services parameter to DBCPConnectionPool 1.16.0 and the Controller Service Name parameter to hologres.
      addcontrollerservice
    3. On the right side of DBCPConnectionPool, click the rightwards arrow to configure the connection string.
    4. Find the DBCPConnectionPool controller service that you add and click the Settings icon on the right side.
      Configure DBCPConnectionPool
    5. Click the PROPERTIES tab. On the PROPERTIES tab, set the properties as required. The following table describes the properties.
      Set the parameters
      Parameter Description Remarks
      Database Connection URL The JDBC connection string that you use to connect to the Hologres instance. Format: jdbc:postgresql://<endpoint>/<database name>. Example: jdbc:postgresql://hgpostcn-cn-xxxxxxxxxxx-cn-shanghai.hologres.aliyuncs.com:80/demo. The endpoint in the connection string must be the public or virtual private cloud (VPC) endpoint of the Hologres instance. To obtain the endpoint, log on to the Hologres console and go to the instance details page.
      Database Driver Class Name The driver class name. Set this property to org.postgresql.Driver. N/A
      Database Driver Location(s) The path of the JDBC driver that you use to connect to the Hologres database. Example: /opt/nifi/nifi-current/jdbc_driver/postgresql-42.3.4.jar. You can download the JDBC driver from the PostgreSQL official website. We recommend that you download the JDBC driver of 42.2.25 or a later version.
      Database User The AccessKey ID of your Alibaba Cloud account. You can obtain the AccessKey ID from the Security Management page.
      Password The AccessKey secret of your Alibaba Cloud account. You can obtain the AccessKey secret from the Security Management page.
    6. Click OK.
    7. Click ENABLE to start the controller service.
    8. Return to the page on which you configure the ConvertJSONToSQL processor and modify the parameters. The following table describes the parameters. For more information, see Apache NiFi Overview.
      Parameter Description
      Statement Type The statement type. In this example, this parameter is set to INSERT.
      Table Name The name of the Hologres table to which the data in the JSON file is to be imported. In this example, the table name is user_info.
      Schema Name The name of the schema to which the Hologres table belongs. In this example, the schema name is public.
    9. Click APPLY to save the configuration.
  4. Configure a PutSQL processor.
    1. Add a PutSQL processor.
    2. Set the JDBC Connection Pool parameter to the DBCPConnectionPool controller service that you add in step 3.ii. In this example, the name of the DBCPConnectionPool controller service is hologres.
    3. Set the Support Fragmented Transactions parameter to false.
    4. Click APPLY to save the configuration.
  5. Import the data in the JSON file.
    Run the processors that you configure in the preceding steps. Then, Apache NiFi reads the data in the JSON file and imports the data to Hologres. Import the data in the JSON file
  6. Query the data.
    In Hologres, execute the following statement to query the table named user_info: 
    SELECT * FROM user_info;
    The following figure shows the query results. Query results