NIFI execute script issue

[#1]

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

[#2]

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)

[#3]

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.

[#4 · Accepted solution]

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