Recently one of our clients was interested in using Omniscope to export each table in a database and export it to an iod file. The number and the names of the tables might not be known beforehand, and it might change over time.
To solve the issue we need to retrieve the list of tables that we need to export, then for each one, read the content of the table and publish an iod: this requires performing the same operation for each element of a sequence of unknown length. Also each export needs to account for the table name in the workflow (for example the table name option in the database source block, or the name of the iod file).
There is no built in way for Omniscope to iterate over the list of tables, but we can use the custom block and the scheduler app to achieve the same result.
Our solution uses the custom block to iterate over each table name, and for each table it will generate a scheduler action. The scheduler action will be saved as an xml file in the watch folder.
The scheduler will read the actions from the watch folder one by one and trigger the execution of a workflow file responsible to export a single table.
We will need 3 files for the solution to work:
- Export_DbTable.iox : this will export a table from the Database to an iod file. The table name and destination iod file path are both read from project parameters (Table_Name and Iod_FileName).
- Action_Template.xml: Is not something you use directly, it is used by the custom block as a template to generate actions. This is an XML file containing
the scheduler configuration to set the Table_Name e and Iod_FileName file name in Export_DbTable.iox, and execute the workflow. The value of the two parameters in this file are placeholders that will be replaced by the custom block.
- Generate_Scheduler_Config.iox uses a custom SQL query to retrieve the table names from the database. Then it executes a custom block. The custom block has three options:
1. Template Filename : this is the path of Action_Template.xml discussed above
2. Xml Watch Folder: Omniscope Watch folder in your environment. Usually <HOME_FOLDER>/omniscope-server/watch. This is where the scheduler actions will be saved.
3. Iod Export Dir: the location where the output iods containing the dumps of the tables should be saved.
When the workflow of Generate_Scheduler_Config.iox is executed the Database source block will extract the list of table names from the database. The custom block will then iterate over each table and for each one will generate a new xml file for the scheduler to execute.
The new xml file will be written in the watch folder (it will have the same name as the table with .xml extension). The content of the file is obtained by replacing the Iod_FileName and Table_Name placeholders in Action_Template.xml with the table name read from the input.
The scheduler will execute the xml files in the watch folder one by one, hence "iterating" over the list of tables, and because each file effectively dumps the content of a table into an iod, when the process is over all the tables are exported to iod files.
An example of this solution is attached. Option values need to be customised depending on the database environment (e.g., vendor, syntax, host, credentials ...). The query in the example is for Microsoft SQL Server.
We implemented the custom block using Python hence, for this solution to work, Python needs to be installed on the system. The same result could be obtained using R.
Recently one of our clients was interested in using Omniscope to export each table in a database and export it to an iod file. The number and the names of the tables might not be known beforehand, and it might change over time.
To solve the issue we need to retrieve the list of tables that we need to export, then for each one, read the content of the table and publish an iod: this requires performing the same operation for each element of a sequence of unknown length. Also each export needs to account for the table name in the workflow (for example the table name option in the database source block, or the name of the iod file).
There is no built in way for Omniscope to iterate over the list of tables, but we can use the custom block and the scheduler app to achieve the same result.
Our solution uses the custom block to iterate over each table name, and for each table it will generate a scheduler action. The scheduler action will be saved as an xml file in the watch folder.
The scheduler will read the actions from the watch folder one by one and trigger the execution of a workflow file responsible to export a single table.
We will need 3 files for the solution to work:
- Export_DbTable.iox : this will export a table from the Database to an iod file. The table name and destination iod file path are both read from project parameters (Table_Name and Iod_FileName).
- Action_Template.xml: Is not something you use directly, it is used by the custom block as a template to generate actions. This is an XML file containing
the scheduler configuration to set the Table_Name e and Iod_FileName file name in Export_DbTable.iox, and execute the workflow. The value of the two parameters in this file are placeholders that will be replaced by the custom block.
- Generate_Scheduler_Config.iox uses a custom SQL query to retrieve the table names from the database. Then it executes a custom block. The custom block has three options:
1. Template Filename : this is the path of Action_Template.xml discussed above
2. Xml Watch Folder: Omniscope Watch folder in your environment. Usually <HOME_FOLDER>/omniscope-server/watch. This is where the scheduler actions will be saved.
3. Iod Export Dir: the location where the output iods containing the dumps of the tables should be saved.
When the workflow of Generate_Scheduler_Config.iox is executed the Database source block will extract the list of table names from the database. The custom block will then iterate over each table and for each one will generate a new xml file for the scheduler to execute.
The new xml file will be written in the watch folder (it will have the same name as the table with .xml extension). The content of the file is obtained by replacing the Iod_FileName and Table_Name placeholders in Action_Template.xml with the table name read from the input.
The scheduler will execute the xml files in the watch folder one by one, hence "iterating" over the list of tables, and because each file effectively dumps the content of a table into an iod, when the process is over all the tables are exported to iod files.
An example of this solution is attached. Option values need to be customised depending on the database environment (e.g., vendor, syntax, host, credentials ...). The query in the example is for Microsoft SQL Server.
We implemented the custom block using Python hence, for this solution to work, Python needs to be installed on the system. The same result could be obtained using R.
Attachments (1)
ExportDbTables.zip
100 KB
0 Votes
0 Comments
Login or Sign up to post a comment