Variables in Aqua Scripts

by Nov 3, 2017

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:

Error on line 23: Wrapped org.apache.hive.service.cli.HiveSQLException: AnalysisException: Syntax error in line 1:
…table_name where mth=\\.\\.\\हिñçêČάй語简�?한\\.Lꄷ⢮ꒂ…
                             ^
Encountered: Unexpected character
Expected: CASE, CAST, EXISTS, FALSE, IF, INTERVAL, NOT, NULL, TRUNCATE, TRUE, IDENTIFIER
 
CAUSED BY: Exception: Syntax error
 (#23)

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