When developing queries using the Advanced Query Command of the Tabular Transformation BizApp, it’s often useful to be able to develop and test more complex queries outside of a Chain execution, especially for Chains that have large volumes of data or multiple Commands that precede the Advanced Query Command. This post describes how to use a shareware application called DBeaver for developing and testing queries.
The Advanced Query Command generally receives an Input in the form of a Command Output. To test with DBeaver, add an additional Command that persists the sending Command Output onto a network or cloud storage location may be necessary. This Output will be used to create and populate a table in DBeaver against which the query can be developed. As an example, when using a GroundRunner, the Copy Command of the File Utilities BizApp is a simple way to persist the Output file to a network file share.
These instructions have been created using a Windows 10 machine with the DBeaver 7.0.3 release. Installation and configuration on Mac or Linux OS or the use of a different version of DBeaver may vary slightly.
Start by installing DBeaver. The Community Edition is sufficient for testing queries that need to be executed in OneCloud. Once the application is installed, a new Database Project needs to be created. Select File → New. On the New Project Wizard, expand the DBeaver folder and select Database Project.
Specify a Project name and a location where project-related information will be stored.
Click Next and then Finish to create the Project.
Navigate to the Projects tab. If the Projects tab is not visible, select Window and then select Projects. Confirm the project created is displayed on the Projects tab.
Next, create a data directory where sample OneCloud Outputs (.csv) files can be stored. Right-click on the project name and select Create → Create New Folder.
Specify a name for the directory. In this example, the directory is named data_stage.
Next, a connection to the directory where the CSV file will be stored needs to be established. Expand the project folder (OneCloud) and right-click on Connections, select Create → Connection.
From the Connection wizard, select CSV and make sure the Connection view is set to Simple and then click Next.
On the JDBC Connection Settings screen, Browse and select the data directory specified earlier and then click Finish.
This creates a connection to the directory and each CSV file added to the directory will be automatically available for operations within DBeaver.
Next, a SQLite database and Connection needs to be created. Right-click Connections in the project and select Create. Select SQLite and click Next.
On the JDBC connection settings windows click Browse. Navigate to the project root directory and specify a name for the SQLite database to be created. Be sure to include the extension .sqlite.
Click Finish and the database and connection will be created.
The remaining steps need to be repeated for each unique data Output that will be tested in DBeaver.
Navigate to the project data directory and transfer the OneCloud Output file to the directory. Be sure the file extension is .csv.
In DBeaver, refresh the Connection list and each file in the data directory is displayed under the CSV connection.
The CSV data can then be used to build a table within the SQLite database. Right-click the data file and select Export Data.
On the data transfer wizard, select Database and click Next.
On the Tables Mapping windows, in the Target container field select the SQLite database that was created previously. Utilizing the Auto assign feature will create a new table in the database that is named the same as the file and the fields within the file will be automatically mapped.
Click Next. The Settings can generally be left to the default. One important note, the Truncate target table(s) before load option does not apply to SQLite databases. A separate Delete query must be run to truncate the table.
On the Confirmation windows, you can choose to Save the job by clicking the Save task button. This is valuable when expecting multiple refreshes of the data. If choosing to save the task, specify a name. A good naming convention is Refresh Table_Name.
Click Start to import the data from the CSV to the SQLite database.
The progress bar will show the number of rows that have loaded.
Once the import completes, the table will open in DBeaver. The table view can be closed.
The table is now ready for query operations. To open a SQL worksheet, ensure you are using the SQLite connection. Right-click the table name and select Read data in SQL console.
A SQL worksheet will open displaying a default Select query and a subset of records will be displayed.
This SQL worksheet can then be used to develop the query that you wish to embed in the OneCloud Advanced Query Command. A reference for SQLite capabilities can be found here. Please note that OneCloud Advanced Query only supports Select queries; Delete, Update, and Insert queries are not supported.
Streamlining The Table Refresh Action
To refresh data in a table, first the table needs to be cleared otherwise the data refresh will append to the existing data in the table. Create a SQL script that clears the table by right-clicking on the SQLite connection within the project and selecting SQL Editor.
A new SQL editor will open. Create a Delete query and using File → Save As, save the script in the Scripts subdirectory within the project directory.
Next, associate the script with the data source. Select the SQLite database for the project.
A new database task can be created to enable this script to be executed simply by running the task. If the Database Tasks window is not visible, select Window → Database Tasks. Be sure the project created in the earlier steps is set as active. It will be bold in the Projects window if active. If it is not, right-click the project name and select Set Active Project.
To create a new task, right-click in the Database Tasks window and select Create new task.
On the task properties window, select Common → SQL Script. Specify a name for the task such as Truncate Table_Name. Click Next.
Select a script file by clicking the Add Script button.
Select the SQL script created in the earlier step.
The connection information should complete automatically and the task can be saved using the Save task button. Do not click Start unless you wish to clear the table. Simply close the window after saving the task.
The tasks can be run by clicking on them and then clicking the Play button. Be sure to execute in the proper order to ensure accurate data within the table.
If you have any questions or feedback about this post, please feel free to contact us at firstname.lastname@example.org.