Domain separated data

[SamoSlaus]

Is there any possibility/option to load domain separated data to different DB's? So, each domain has it's own datamart DB.

[SamoSlaus]

Tried with filter functionality to filter records by domain. But stumbled upon error "Filter not valid with Action: SYNC"

[gflewis]

Action: Sync does not allow you to specify a Filter. One reason is that for Sync the loader needs to generate SQL to query the database and find all the existing records. Filter can be used to specify an arbitrary encoded query. Translating an arbitrary encoded query into SQL could get complicated, well beyond the current capabilities of the product.

You should be able to specify a domain in a Filter for actions Insert, Update, and Prune, although I have not tested it.

The best solution for domain separation would be to extend the YAML with a new Domain option, and to make the corresponding changes in the Java code. This would allow you to specify a Domain for Sync or any of the other actions. We might also be able to figure out a way to parameterize it so that you could re-use the same YAML for a bunch of different domains. I could probably make the Java changes, but I do not have a way to test it because I do not have access to a domain separated instance.

A couple of questions:

• What would the encoded query look like if you were loading a table for a single domain?
• Are you interested in collaborating on a new release? If I could make the Java changes could you test the code?

[SamoSlaus]

I've tested Filter with sys_domain and without Sync and it works. Like this: - {name: cmdb_ci, truncate: true, filter: "sysdomain=_"} But I would need Sync functionality for large tables.

I would be happy to help on this matter.

[gflewis]

Here is an alternate approach that actually might work better.

Rather than modifying the YAML, we modify the connection profile, and add a property as follows

servicenow.domain=<domain_sys_id>

This causes the loader to automatically add "sys_domain=" to every query.

Each domain will require a different connection profile anyway, because either the database or the schema will be different. And it would be fewer changes to the Java because I do not need to modify the code for the different actions.

[SamoSlaus]

I think this would be the best approach. But we need to consider the option to specify multiple domains also. Like: sys_domain=<domain_sys_id_1>^ORsys_domain=<domain_sys_id_2>

[gflewis]

We can use a comma separated list, so the property will be

servicenow.domain=<sys_id_1>,<sys_id_2>

and the query will be

sys_domainIN<sys_id_1>,<sys_id_2>

[gflewis]

This has been implemented in 3.2.0-SNAPSHOT as described above. In the file src/main/java/api/Table.java there is a hard-coded list of tables that are excluded from domain separation. If servicenow.domain is specified in the connection profile, then the extra qualifier will get automatically prepended to any query for any table not in this list. The actual query modification happens in the function EncodedQuery.toString().

Will you be able to rebuild the JAR from the source code and run a few tests?

I hope to release 3.2.0 in a week or so. The command to rebuild from the source code is mvn package.

3.2.0 contains other changes. It includes an upgrade of mysql-connector to the latest version. As a result, it is now necessary to add a schema property to the connection profile for MySQL as follows. Are you using MySQL?

datamart.schema=databasename

3.2.0 also includes a complete re-design of the bash scripts in the bin directory. Are you using these scripts?

[SamoSlaus]

Thanks for your quick solution! I'm using MSSQL.

Got couple of errors upon build (ScrnShot - https://imgur.com/a/ntMyLz0 )
C:\Users\Samo\Downloads\sndml3-master>mvn package [INFO] Scanning for projects... [INFO] [INFO] ----------------------------< sndml:sndml >----------------------------- [INFO] Building sndml 3.2.0-SNAPSHOT [INFO] --------------------------------[ jar ]--------------------------------- [INFO] [INFO] --- maven-resources-plugin:2.6:resources (default-resources) @ sndml --- [INFO] Using 'UTF-8' encoding to copy filtered resources. [INFO] Copying 3 resources [INFO] [INFO] --- maven-compiler-plugin:3.1:compile (default-compile) @ sndml --- [INFO] Changes detected - recompiling the module! [INFO] Compiling 104 source files to C:\Users\Samo\Downloads\sndml3-master\target\classes [INFO] ------------------------------------------------------------- [ERROR] COMPILATION ERROR : [INFO] ------------------------------------------------------------- [ERROR] /C:/Users/Samo/Downloads/sndml3-master/src/main/java/servicenow/datamart/DatabaseStatement.java:[24,9] reference to Record is ambiguous both class servicenow.api.Record in servicenow.api and class java.lang.Record in java.lang match [ERROR] /C:/Users/Samo/Downloads/sndml3-master/src/main/java/servicenow/datamart/DatabaseStatement.java:[61,47] reference to Record is ambiguous both class servicenow.api.Record in servicenow.api and class java.lang.Record in java.lang match [ERROR] /C:/Users/Samo/Downloads/sndml3-master/src/main/java/servicenow/datamart/DatabaseStatement.java:[75,47] reference to Record is ambiguous both class servicenow.api.Record in servicenow.api and class java.lang.Record in java.lang match [ERROR] /C:/Users/Samo/Downloads/sndml3-master/src/main/java/servicenow/datamart/DatabaseUpdateWriter.java:[26,26] reference to Record is ambiguous both class servicenow.api.Record in servicenow.api and class java.lang.Record in java.lang match [ERROR] /C:/Users/Samo/Downloads/sndml3-master/src/main/java/servicenow/datamart/DatabaseTableWriter.java:[76,35] reference to Record is ambiguous both class servicenow.api.Record in servicenow.api and class java.lang.Record in java.lang match [ERROR] /C:/Users/Samo/Downloads/sndml3-master/src/main/java/servicenow/datamart/DatabaseInsertWriter.java:[29,26] reference to Record is ambiguous both class servicenow.api.Record in servicenow.api and class java.lang.Record in java.lang match [ERROR] /C:/Users/Samo/Downloads/sndml3-master/src/main/java/servicenow/datamart/DatabaseDeleteWriter.java:[24,26] reference to Record is ambiguous both class servicenow.api.Record in servicenow.api and class java.lang.Record in java.lang match [INFO] 7 errors [INFO] ------------------------------------------------------------- [INFO] ------------------------------------------------------------------------ [INFO] BUILD FAILURE [INFO] ------------------------------------------------------------------------ [INFO] Total time: 4.900 s [INFO] Finished at: 2020-09-28T10:33:44+02:00 [INFO] ------------------------------------------------------------------------ [ERROR] Failed to execute goal org.apache.maven.plugins:maven-compiler-plugin:3.1:compile (default-compile) on project sndml: Compilation failure: Compilation failure: [ERROR] /C:/Users/Samo/Downloads/sndml3-master/src/main/java/servicenow/datamart/DatabaseStatement.java:[24,9] reference to Record is ambiguous [ERROR] both class servicenow.api.Record in servicenow.api and class java.lang.Record in java.lang match [ERROR] /C:/Users/Samo/Downloads/sndml3-master/src/main/java/servicenow/datamart/DatabaseStatement.java:[61,47] reference to Record is ambiguous [ERROR] both class servicenow.api.Record in servicenow.api and class java.lang.Record in java.lang match [ERROR] /C:/Users/Samo/Downloads/sndml3-master/src/main/java/servicenow/datamart/DatabaseStatement.java:[75,47] reference to Record is ambiguous [ERROR] both class servicenow.api.Record in servicenow.api and class java.lang.Record in java.lang match [ERROR] /C:/Users/Samo/Downloads/sndml3-master/src/main/java/servicenow/datamart/DatabaseUpdateWriter.java:[26,26] reference to Record is ambiguous [ERROR] both class servicenow.api.Record in servicenow.api and class java.lang.Record in java.lang match [ERROR] /C:/Users/Samo/Downloads/sndml3-master/src/main/java/servicenow/datamart/DatabaseTableWriter.java:[76,35] reference to Record is ambiguous [ERROR] both class servicenow.api.Record in servicenow.api and class java.lang.Record in java.lang match [ERROR] /C:/Users/Samo/Downloads/sndml3-master/src/main/java/servicenow/datamart/DatabaseInsertWriter.java:[29,26] reference to Record is ambiguous [ERROR] both class servicenow.api.Record in servicenow.api and class java.lang.Record in java.lang match [ERROR] /C:/Users/Samo/Downloads/sndml3-master/src/main/java/servicenow/datamart/DatabaseDeleteWriter.java:[24,26] reference to Record is ambiguous [ERROR] both class servicenow.api.Record in servicenow.api and class java.lang.Record in java.lang match [ERROR] -> [Help 1] [ERROR] [ERROR] To see the full stack trace of the errors, re-run Maven with the -e switch. [ERROR] Re-run Maven using the -X switch to enable full debug logging. [ERROR] [ERROR] For more information about the errors and possible solutions, please read the following articles: [ERROR] [Help 1] http://cwiki.apache.org/confluence/display/MAVEN/MojoFailureException

[SamoSlaus]

Fixed it with referencing Record to servicenow.api.Record. Ran the build with success. Changed the profile, so that it loads only couple of domains.

Can confirm that it works!

Thank you for all the help and quick rework!

If there is something domain-wise in the future you can contact me and I can test it out,