Testing and Troubleshooting
For testing a module, the following options are available:
- Unit tests
- Execution on the server
Unit Tests
For unit tests we use JUnit, the standard in the Java world. For the database we use an in-memory H2 database, so the test world for the module looks the same as later on the server.
We again derive our test class from a base class that performs the test preparations such as setting up and filling the necessary tables, and offers a helper method for creating requests:
public class TestDsModDemo extends BaseTestMdule {
The actual tests are then performed with methods that are marked as test methods via annotations. They look something like the following method, which performs a successful validation:
@Test
public void testValidateSuccess() throws Exception {
ServerRequest request = loadServerRequest("src/test/resources/validateSuccess.json");
TestDsModDemo module = new TestDsModDemo();
Module wrapperModule = new WrapperModule(module);
ServerResponse response = wrapperModule.execute(request);
String json = response.toPrettyJson();
System.out.println(json);
Assert.assertNull("Error must be null", response.getError());
}
First, a ServerRequest is created from a JSON file and an instance of our module. With the help of a wrapper module, the "execute" method is then called. Finally, the result is output to the console and checked for expected result values.
Other test methods check a validation (testValidateFailed) that leads to an error, or start a transformation (testRun).
A core component of these tests are the JSON files. They were determined by logging GUI calls and saved (see below).
{
"requestId" : 986,
"command" : "Validate",
"payload" : {
"sheet" : {
"nodes" : [ {
"id" : 1002,
"type" : 0,
"attributes" : {
"module" : "write_csv",
"name" : "Export Person",
"action" : "SELECT id\n , fname\n , lname\n FROM public.person",
"Append" : "Y",
"Delimiter" : ",",
"moduleCommand" : "DsModDemo"
}
}, {
"id" : 1003,
"type" : 1,
"attributes" : {
"field1" : "person",
"field2" : "PUBLIC",
"field3" : "",
"objectTypeId" : 1,
"connectionId" : 1
}
}, {
"id" : 1056,
"type" : 1,
"attributes" : {
"field1" : "address",
"field2" : "PUBLIC",
"field3" : "",
"objectTypeId" : 1,
"connectionId" : 1
}
}, {
"id" : 1001,
"type" : 1,
"attributes" : {
"field1" : "output",
"field2" : "/tmp",
"field3" : null,
"objectTypeId" : 7,
"connectionId" : 1
}
} ],
"edges" : [ {
"from" : 1003,
"to" : 1002
}, {
"from" : 1056,
"to" : 1002
}, {
"from" : 1002,
"to" : 1001
} ]
}
},
"loglevel" : "FINEST",
"variableList" : [ {
"variableName" : "dsmod_template.ftl",
"variableType" : "STRING",
"variableValue" : "[#assign\nhas_equal_operator = [\"MySQL\"]?seq_contains(\"${database_product_name}\")\nhas_distinct = [\"PostgreSQL\", \"Snowflake\"]?seq_contains(\"${database_product_name}\")\nhas_decode = [\"PostgreSQL\", \"Oracle\", \"H2\", \"EXASolution\", \"Teradata\", \"Snowflake\", \"DB2\"]?seq_contains(\"${database_product_name}\")\nhas_boolean = [\"PostgreSQL\"]?seq_contains(\"${database_product_name}\")\nhas_merge_matched_and = [\"Microsoft SQL Server\", \"Snowflake\", \"H2\"]?seq_contains(\"${database_product_name}\")\nhas_merge_matched_where = [\"Oracle\", \"H2\", \"EXASolution\"]?seq_contains(\"${database_product_name}\")\nhas_delete_from = [\"Teradata\", \"Microsoft SQL Server\"]?seq_contains(\"${database_product_name}\")\nhas_delete_using = [\"PostgreSQL\", \"Snowflake\"]?seq_contains(\"${database_product_name}\")\nhas_multiple_in_cols = [\"Oracle\", \"EXASolution\"]?seq_contains(\"${database_product_name}\")\nhas_update_from = [\"PostgreSQL\", \"Snowflake\", \"Teradata\", \"Microsoft SQL Server\"]?seq_contains(\"${database_product_name}\")\n]\n[#function is_equal left_value right_value is_nullable]\n [#if has_equal_operator][#return \"${left_value} <=> ${right_value}\"]\n [#elseif !is_nullable][#return \"${left_value} = ${right_value}\"]\n [#elseif has_distinct][#return \"${left_value} IS NOT DISTINCT FROM ${right_value}\"]\n [#elseif has_decode][#return \"DECODE(${left_value}, ${right_value}, 1, 0) = 1\"]\n [#elseif has_boolean][#return \"COALESCE(${left_value} = ${right_value}, ${left_value} IS NULL AND ${right_value} IS NULL)\"]\n [#else][#return \"CASE WHEN ${left_value} = ${right_value} THEN 1 WHEN ${left_value} IS NULL AND ${right_value} IS NULL THEN 1 ELSE 0 END = 1\"]\n[/#if]\n[/#function]\n[#function concat_comma column]\n [#local res = \"\"]\n [#list column as col]\n [#if res != \"\"]\n [#local res += \", \"]\n [/#if]\n [#local res += col.name]\n [/#list]\n [#return res]\n[/#function]\n[#function add_map col value]\n [#local result=[]]\n [#if col?has_content]\n [#local result = result + [{\"name\": col[0].name, \"value\": value}]]\n [/#if]\n [#return result]\n[/#function]\n[#function get_current_datetime]\n [#if [\"Oracle\"]?seq_contains(\"${database_product_name}\")]\n [#return \"SYSDATE\"]\n [#elseif [\"Microsoft SQL Server\"]?seq_contains(\"${database_product_name}\")]\n [#return \"Getdate()\"]\n [#elseif [\"MySQL\"]?seq_contains(\"${database_product_name}\")]\n [#return \"NOW()\"]\n [/#if]\n [#return \"DATE_TRUNC('second', CURRENT_TIMESTAMP)\"]\n[/#function]\n[#function get_current_date]\n [#if [\"Oracle\"]?seq_contains(\"${database_product_name}\")]\n [#return \"TRUNC(SYSDATE)\"]\n [#elseif [\"Microsoft SQL Server\"]?seq_contains(\"${database_product_name}\")]\n [#return \"CONVERT(DATE, Getdate())\"]\n [#elseif [\"MySQL\"]?seq_contains(\"${database_product_name}\")]\n [#return \"CURDATE()\"]\n [/#if]\n [#return \"DATE_TRUNC('minute', CURRENT_TIMESTAMP)\"]\n[/#function]\n[#function string_to_datetime string]\n [#if [\"Oracle\", \"EXASolution\", \"H2\"]?seq_contains(\"${database_product_name}\")]\n [#return \"TO_DATE('${string}', 'YYYY-MM-DD HH24:MI:SS')\"]\n [#elseif [\"PostgreSQL\", \"Snowflake\"]?seq_contains(\"${database_product_name}\")]\n [#return \"timestamp '${string}'\"]\n [#elseif [\"Microsoft SQL Server\", \"Snowflake\"]?seq_contains(\"${database_product_name}\")]\n [#return \"CAST ('${string}' AS DATETIME)\"]\n [#elseif [\"MySQL\"]?seq_contains(\"${database_product_name}\")]\n [#return \"STR_TO_DATE('${string}', '%Y-%m-%d %H:%i:%s')\"]\n [/#if]\n [#return \"CAST ('${string}' AS TIMESTAMP)\"]\n[/#function]\n[#function string_to_date string]\n [#if [\"Oracle\", \"EXASolution\", \"PostgreSQL\", \"H2\", \"Snowflake\" ]?seq_contains(\"${database_product_name}\")]\n [#return \"TO_DATE('${string}', 'YYYY-MM-DD')\"]\n [#elseif [\"MySQL\"]?seq_contains(\"${database_product_name}\")]\n [#return \"STR_TO_DATE('${string}', '%Y-%m-%d')\"]\n [/#if]\n [#return \"CAST ('${string}' AS DATE)\"]\n[/#function]\n[#function interpret x]\n [#local s][@x?interpret/][/#local]\n [#return s]\n[/#function]"
}, {
"variableName" : "batch_instance_id",
"variableType" : "LONG",
"variableValue" : "-1"
}, {
"variableName" : "schedule_date",
"variableType" : "TIMESTAMP",
"variableValue" : "2020-07-19T09:13:28.309Z"
}, {
"variableName" : "action_id",
"variableType" : "LONG",
"variableValue" : "1002"
}, {
"variableName" : "request_id",
"variableType" : "LONG",
"variableValue" : "986"
} ]
}
The test classes and test resources are also included in the Zip file.
Execution on the Server
When executing on the server, there are various options for error analysis.
- Execution by the server
- Direct invocation
- Console output and logging
- Remote debugging
Execution by the server
The simplest approach is of course to integrate the module in the server and call it via the GUI for validation and execution. When everything works correctly, you can pat yourself on the back.
Direct invocation
Sometimes it doesn't work right away. In this case, you can first stimulate the module on the server using its run script and a suitable JSON request.
For this we use again the request that we also developed for the unit tests. After it has been copied to the server, it can be passed as input:
runJavaModule.sh DsModDemo < validateSuccess.json 2> output
This procedure can be particularly helpful when problems occur while developing the invocation scripts. You can simply add debug output to the script or even display each command before execution to analyze problems.
To display the commands before their execution, you can modify the runJavaModule.sh script and simply add the following lines at the beginning of the script:
#/bin/bash
exec 4>&1
BASH_XTRACEFD=4
set -x
Now the Bash debug output is sent through "set -x" to the StdOut channel and you can follow everything at the console:
$ runJavaModule.sh DsModDemo < validateSuccess.json 2> output
++ set -o nounset
++ set -o errexit
++ DEBUG=0
++ PORT=9000
++ getopts :c:dhj:p:v opt
++ shift 0
++ '[' 1 -eq 0 ']'
++ MODULE_CLASS=DsModDemo
++ MODULE_NAME=DsModDemo
++ export 'CLASSPATH=/home/datasqill/lib/DsModDemo.jar:/home/datasqill/lib/datasqill-api.jar:/home/datasqill/lib/jdbc/*'
++ CLASSPATH='/home/datasqill/lib/DsModDemo.jar:/home/datasqill/lib/datasqill-api.jar:/home/datasqill/lib/jdbc/*'
++ export JAVA_OPTS=-Djdbc.drivers=net.snowflake.client.jdbc.SnowflakeDriver:org.apache.hive.jdbc.HiveDriver
++ JAVA_OPTS=-Djdbc.drivers=net.snowflake.client.jdbc.SnowflakeDriver:org.apache.hive.jdbc.HiveDriver
++ export 'JAVA_OPTS=-Djdbc.drivers=net.snowflake.client.jdbc.SnowflakeDriver:org.apache.hive.jdbc.HiveDriver -Dde.softquadrat.datasqill.mappertool=getkey'
++ JAVA_OPTS='-Djdbc.drivers=net.snowflake.client.jdbc.SnowflakeDriver:org.apache.hive.jdbc.HiveDriver -Dde.softquadrat.datasqill.mappertool=getkey'
++ export 'JAVA_OPTS=-Djdbc.drivers=net.snowflake.client.jdbc.SnowflakeDriver:org.apache.hive.jdbc.HiveDriver -Dde.softquadrat.datasqill.mappertool=getkey -Djava.security.egd=file:/dev/urandom'
++ JAVA_OPTS='-Djdbc.drivers=net.snowflake.client.jdbc.SnowflakeDriver:org.apache.hive.jdbc.HiveDriver -Dde.softquadrat.datasqill.mappertool=getkey -Djava.security.egd=file:/dev/urandom'
++ [[ 0 -eq 0 ]]
++ DEBUG_OPTS=
++ [[ -f /home/datasqill/lib/DsModDemo.env ]]
++ exec java -cp '/home/datasqill/lib/DsModDemo.jar:/home/datasqill/lib/datasqill-api.jar:/home/datasqill/lib/jdbc/*' -Djdbc.drivers=net.snowflake.client.jdbc.SnowflakeDriver:org.apache.hive.jdbc.HiveDriver -Dde.softquadrat.datasqill.mappertool=/home/datasqill/bin/getkey -Djava.security.egd=file:/dev/urandom -Dtomee.port=17491 -Dtomee.shutdown_port=8005 -Dtomee.shutdown_command=SHUTDOWN DsModDemo
Console output and logging
To find out why the module doesn't do what it should, you can add console output and logging in the code. These are outputs of the form
System.out.println("My message to standard output ...");
System.err.println("My message to error output ...");
or
Logger logger = Logger.getLogger("de.softquadrat.datasqill.modules");
logger.info("My message to the module log");
Modules use the standard Java log classes (java.util.logging). While the standard channels (stdout, stderr) are sent back from the server to the GUI, log messages are usually discarded. To have the log output stored and displayed in the GUI at the end, you must create a configuration entry in the datasqill repository that enables storing these outputs for a specific transformation (Action).
We do this again via the deployment script. We determine the ID of our Action with the help of the GUI, create a JSON file, and load it into the server:
{
objectList:
[
{
dsdbFormat: 1
deploymentType: version
current:
{
version: 3.2.2
data:
[
{
target: VV_SQTS_CONFIG_GLOBAL
columns: [ "config_key1", "config_key2", "config_key3", "config_key4", "config_value", "config_sort", "config_comment" ]
rows: [
[ "SERVER", "MODULE", "LOGLEVEL", "1215", "FINEST", 1000, "Enable logging with level finest for action" ]
]
}
]
}
}
]
}
deploySchema.sh < debug1002.dsdb
After that, the log output appears in the GUI when validating and executing (StdOut Log tab below). Here is an example validation dialog:
Remote Debugging
Remote debugging allows error analysis with the help of an IDE like Eclipse or IntelliJ. The process on the server is started in debug mode and the IDE is connected to it. You can then use the advantages of a development environment to set breakpoints, inspect variables, and even modify them, or step through the module.
Three ingredients are needed for remote debugging:
- Use of a debug port
- Start of the module VM in debug mode
- Start of the debug session in the IDE
The debug port is any free port on the server. It must be reachable from the client with the development environment, if necessary via an SSH tunnel. The "runJavaModule.sh" script uses port 9000.
For the module's JVM to start in debug mode, you must pass the "-d" option when calling the "runJavaModule.sh" script:
runJavaModule.sh -d DsModDemo < validateSuccess.json 2> output
Then we can start a remote debug session. In Eclipse, first select the menu item Run - Debug Configurations... and switch there to Remote Java Application
There, create a new entry with New Configuration and specify the coordinates of the remote datasqill server:
Now set a breakpoint in the module and start the debugger. It connects to the server process and stops at the desired location:
