question Re: NIFI execute script issue in Support Questions

NIFI execute script issue

krdsranga — Wed, 22 Mar 2017 22:52:37 GMT

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

Re: NIFI execute script issue

mburgess — Thu, 23 Mar 2017 01:03:10 GMT

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

mburgess — Thu, 23 Mar 2017 01:05:48 GMT

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

jmeyer — Thu, 23 Mar 2017 02:08:55 GMT

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") 
}