Hologres:Apache Nifi

Prerequisites

• A Hologres instance is created. For more information, see Purchase a Hologres instance.

• Apache Nifi is installed. For more information, see How to install and start NiFi.

Import a local JSON file to 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.

   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.

      

   3. On the right side of DBCPConnectionPool, click the right arrow to configure the connection string.

   4. Find the DBCPConnectionPool controller service that you add and click the Settings icon on the right side.

      

   5. Click the PROPERTIES tab. On the PROPERTIES tab, set the properties as required. The following table describes the properties.

      

      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.

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.