Important

The SingleStore 9.0 release candidate (RC) gives you the opportunity to preview, evaluate, and provide feedback on new and upcoming features prior to their general availability. In the interim, SingleStore 8.9 is recommended for production workloads, which can later be upgraded to SingleStore 9.0.

Connect with Apache NiFi

Prerequisites

Download and install Apache NiFi.
Install Java 21+.
Download the SingleStore JDBC driver .jar file.
Start Apache NiFi. Run the following command from the NiFi installation directory.
Shell
```
./bin/nifi.sh start
```

Copy the username and password from the generated log. Use these credentials to log in to NiFi Flow. For example,

Shell

tail -n 100 <NiFi_installation_directory>/logs/nifi-app.log | grep Generated

Generated Username [eea280ec-6000-4000-a000-9aaf521ef5cf]
Generated Password [He5Gd9ZHVgkA0JQR9d9uNc2A5WAHROI4]

Configure the Connection to SingleStore

To configure the connection to SingleStore in NiFi Flow, perform the following:

Open NiFi Flow, go to https://localhost:8443/nifi.
Log in to NiFi Flow using the generated username and password.
On the Nifi Flow canvas, open the options menu (right-click) and select Controller Services.
Select the + icon.
On the Add Controller Service dialog, from the list of controllers, select DBCPConnectionPool > Add.
On the Controller Services page. Select the three dots for the controller added in the previous step, and then select Edit.
In the Edit Controller Service dialog,
1. On the Settings tab, enter SingleStore in the Name field.
2. On the Properties tab, enter or select the following:
  1. Database Connection URL: Specify the connection string for the SingleStore deployment in the following format:
    
    jdbc:singlestore://<endpoint>:<port>/<database>
  2. Database Driver Class Name: Specify com.singlestore.jdbc.Driver.
  3. Database Driver Location: Specify the path to the SingleStore JDBC driver .jar file downloaded earlier.
  4. Database User: Specify the username of the SingleStore database user with which to connect.
  5. Password: Specify the password for the SingleStore database user.
  6. Specify other connection configuration properties as applicable.
  7. Under Verification, select ✔ > Verify to verify the connection configuration.
3. After the connection configuration is verified, a message similar to the following is displayed:
```
Perform Validation
Component Validation passed
Configure Data Source
Successfully configured data source
Establish Connection
Successfully established Database Connection
```
4. Select Apply. The SingleStore controller service is now configured.
Enable the SingleStore controller service. Select the three dots for the controller, and then select Enable from the list.
Close the Enable Controller Service dialog once the SingleStore controller service is enabled.

The connection to the SingleStore deployment is now configured. Specify the SingleStore controller service in processor configuration to connect to your SingleStore database.

Example

The following example connects to a SingleStore deployment, runs a SQL query, and then stores the output in a file.

On the NiFi Flow canvas, add the ExecuteSQL processor by dragging the processor to the canvas.
Configure the ExecuteSQL processor. Open the options menu for the processor (right-click) and then select Configure.
On the Edit Processor dialog, enter or select the following:
1. On the Settings tab, enter a name for the processor. For example, Query table.
2. On the Properties tab, select Database Connection Pooling Service and then select SingleStore (the configured controller service) from the list.
3. In the SQL Query field, enter a SQL query to run.
4. Specify other connection configuration properties as applicable.
5. Select Apply.
On the NiFi Flow canvas, add the ConvertRecord processor.
Configure the ConvertRecord processor. Open the options menu for the processor (right-click) and then select Configure.
On the Edit Processor dialog, enter or select the following:
- Record Reader: Select the three dots and then select Create new service > AvroReader. This example uses the AvroReader service.
- Record Writer: Select the three dots and then select Create new service > JSONRecordSetWriter. This example uses the JSONRecordSetWriter service.
On the NiFi Flow canvas, add the PutFile processor.
Configure the PutFile processor. Open the options menu for the processor (right-click) and then select Configure.
In the Edit Processor dialog, enter or select the following:
1. On the Properties tab, enter the path to the directory where the output file is stored in the Directory field.
2. On the Relationships tab, select terminate for success and failure.
3. Select Apply.
Configure the relationships between the ExecuteSQL, ConvertRecord, and PutFile processors.
(If required) Enable all the controller services. Open the options menu for the canvas (right-click), and then select Enable All Controller Services.
Open the options menu for the canvas and select Start.

The output is written to a file in the specified directory. For example,

Note

The output is formatted for readability.