Hi, I wondered if anyone could help – am pretty new to scripting in aqua. I am trying to write a script that uses impala sql to query our database. I am trying to run 1 query that generates an answer (maximum month id), store this into a variable and then use this as a parameter in another query. I have gotten as far as the script below but its giving me an error – doesnt seem to be passing the result of the first query through correctly…
If I replace the code creating maxmth with the actual value is passes through and runs query 2.
Any help would be very much appreciated.
Code below:
// create connection var conn = aqua.project.getServerConnection('Impala'); conn.connect(); conn.changeDatabase('default'); // create report var report = aqua.report.newReport(); // define variables var query = null; var dataSet = null; var dataSetSettings = null; // --------- query 1 --------- // grab desired mth for mapping query1 = 'select max(mth)-1 from database_name.table_name'; var maxmth = conn.executeSnapshot(query1); // --------- query 2 --------- // create data set query = "create table database_name.table_name stored as parquet as select * from database_name.table_name where mth=" + maxmth + ""; dataSet = conn.executeSnapshot(query); // define data set settings dataSetSettings = aqua.report.newReportDataSetSettings(); dataSetSettings.setTitle(query) dataSetSettings.setShowHeader(true); dataSetSettings.setShowRowNumber(true); dataSetSettings.setShowAltRowColor(false); // add data set to report report.addDataSet(dataSet, dataSetSettings); // display report in browser aqua.response.write(report.renderHtml());
the error message I get is:
Response
Thomas Conrad over 5 years ago
Hi Scott,
There are a couple approaches that you can use for this.
1) You might be able to just combine the queries into one query like:
SELECT col1_bool, col1_float FROM “public”.”All_datatypes” WHERE col1_integer = (SELECT max(col1_integer) FROM “public”.”All_datatypes”)
This way you are just making one database call.
2) If you do want to do it in separate database calls, you can do something like:
var sqlstmt1 = “SELECT max(col1_integer) FROM \”public\”.\”All_datatypes\””;
try {
aqua.console.println(sqlstmt1);
var resultset = conn.executeQuery(sqlstmt1);
resultset.next()
var maxint = resultset.getInt(0);
aqua.console.println(“maxint: ” + maxint);
var dataset;
if (maxint != “”) {
var sqlstmt2 = “SELECT col1_bool, col1_float FROM \”public\”.\”All_datatypes\” WHERE col1_integer = ” + maxint;
dataset = conn.executeSnapshot(sqlstmt2);
}
}
Will the create table statement actually return a dataset?
The reason that you are getting the error is because you are trying to pass a data set into the query string that requires a string value. Look at the example that I sent. I use a result set to contain the query output instead of a data set. I can then use resultset.getInt to get the actual value. In your case, you will want to use resultset.getString method to get the string value.
Please give it a try.
Thanks,
Tom