Support Questions
Find answers, ask questions, and share your expertise

NIFI execute script issue

New Contributor

I am trying to execute postgres psql command from a NIFI instance installed on a AWS EC2 instance. I am using the grrovy script.

I have installed the postgresql utility on EC2 instance using

yum install postgres

Then I execute the command using psql directly from ec2 instance

psql --host=<RDS instance> --port=5432 --dbname=<dbname> --username=<master user> --no-password --command="create database kiran_test_2"

This executes fine.

I try the same on groovy console, it works fine. Please find the code below

import java.sql.DriverManager; import java.sql.Connection; import java.sql.SQLException; import groovy.sql.Sql; def copyCommand = /psql --host=<AWS host> --port=5432 --dbname=<AWS DB> --username=<AWS user> --no-password --command="create database 2"/ def copyCommand = /psql --host=<AWS RDS instance> --port=5432 --dbname=<dbname> --username=<usrname> --no-password --command="create database kiran_test_1"/ println copyCommand //run psql tool as an external process def process = copyCommand.execute() def out = new StringBuffer() def err = new StringBuffer() process.waitForProcessOutput(out, err) //wait for the process to finish println "exit code: ${process.exitValue()}" if (out.size()) { println "Success: $out" } if (err.size()) { println "Error: $err" }

But when I copy the in NIFI script body, it fails complaining "additional attribute database " and fails.

Please advise.

Thanks,

Kiran

1 ACCEPTED SOLUTION

Accepted Solutions

Re: NIFI execute script issue

Explorer

Kiran, I believe this has to do with groovy's execute method. I think it is sending the quotes as part of the actual sql to the psql command. Meaning postgres is failing because it doesn't expect the single quotes in a command. Something like the below command would work.

import java.sql.DriverManager; 
import java.sql.Connection; 
import java.sql.SQLException; 
import groovy.sql.Sql;

def copyCommand = ['psql', '--host=localhost', '--port=5432', '--dbname=db', '--username=uname', '--no-password', '-c', 'create database kiran_test_0']

println copyCommand //run psql tool as an external process

def process = copyCommand.execute() 
def out = new StringBuffer() 
def err = new StringBuffer()

process.waitForProcessOutput(out, err) //wait for the process to finish 
println "exit code: ${process.exitValue()}"

if (out.size()) { 
println "Success: $out" 
}

if (err.size()) { 
println "Error: $err" 
throw new RuntimeException("$err") 
}

View solution in original post

3 REPLIES 3

Re: NIFI execute script issue

Super Guru

Do you mind formatting the code in a "code" block? I see two definitions of copyCommand (you probably wanted to remove the one with all your hostname/port user/pass info)

Re: NIFI execute script issue

Super Guru

I wonder if the slashy string is causing the problem. Since you don't have any single quotes in your command, maybe try enclosing the command in single quotes rather than slashes.

Re: NIFI execute script issue

Explorer

Kiran, I believe this has to do with groovy's execute method. I think it is sending the quotes as part of the actual sql to the psql command. Meaning postgres is failing because it doesn't expect the single quotes in a command. Something like the below command would work.

import java.sql.DriverManager; 
import java.sql.Connection; 
import java.sql.SQLException; 
import groovy.sql.Sql;

def copyCommand = ['psql', '--host=localhost', '--port=5432', '--dbname=db', '--username=uname', '--no-password', '-c', 'create database kiran_test_0']

println copyCommand //run psql tool as an external process

def process = copyCommand.execute() 
def out = new StringBuffer() 
def err = new StringBuffer()

process.waitForProcessOutput(out, err) //wait for the process to finish 
println "exit code: ${process.exitValue()}"

if (out.size()) { 
println "Success: $out" 
}

if (err.size()) { 
println "Error: $err" 
throw new RuntimeException("$err") 
}

View solution in original post