Articles in this section

See more

Writing and Using a Custom Flat File Utility

The purpose of this example is to show how to set up custom function for a flat file Connection. Here we will be creating a Java method that converts a timestamp value into a standard format in order to compare values in different files that have different timestamp formats. The idea is to convert the timestamp formats in your QueryPair queries so that the QuerySurge analysis will be done correctly.

Note: The JDK level used to compile the jar containing your custom function file must match the JDK level used by QuerySurge. You can find your QuerySurge Java Version in Administration > Server Properties > Java Version.

  1. Create an external Java method to handle the timestamp conversion. In this example, the following Java method is used. As illustrated here, custom methods should always be public and static.



     See Resources section: CustomFunctions.java, ExtraFunctions.java

  2. Compile the Java to create the jar file and copy it, along with any dependencies to the //../<QuerySurge Install Dir>/QuerySurge/agent/jdbc folder.

    Note: Do not seal the jar file.

     See Resources section: stels_custom_extension.jar

  3. Stop your Agent(s).

  4. Locate the agentconfig.xml file for your Agent(s), and make a copy of the file before editing it:
    //../<QuerySurge Install Dir>/QuerySurge/agent/config/agentconfig.xml:

    Add the following driver property to the original file, which specifies:
    • the driver name
    • the name of the custom function that you will use
    • the data type of the return value of the method
    • the Java class and method that the function maps to

    You may need Admin rights to edit this file.

    <connectionProps>
    ...
     
    <driverProp driver="jstels.jdbc.csv.CsvDriver2" prop="function:parse_sql_timestamp" type="String" value="com.rttsweb.stels_custom_extension.CustomFunctions.parse_sql_timestamp" />
    ...
    </connectionProps>

  5. Restart the QuerySurgeAgent service for each Agent modified.

  6. Extract the sample CSV files to a folder by themselves (These files will be used as the data for your QueryPair)

     See Resources section: sample_CSV_files.zip

  7. Create a Delimited Flat File Connection that points to the folder containing the two sample CSV files.


    Reference: https://querysurge.zendesk.com/hc/en-us/articles/206083633-Configuring-Connections-Delimited-Flat-Files

  8. Create a QueryPair and add a call to the function:

    The call syntax in your QueryPair is:

    parse_sql_timestamp('<timestamp field>', '<timestamp format>');

    This call can be made as part of a SELECT.

    A sample query in a QueryPair looks like this:

    During execution, the parse_sql_timestamp() method will convert the timestamp in the field (specified by the first argument) to a standard format that matches the timestamp format in the Target file. The CSV driver will then execute the query against the files and QuerySurge will complete QueryPair execution as usual.

    A full QueryPair Example for included files:

 

Additional Notes:

The example above is just one example of how the custom function mechanism can be used. Besides the use case above, where a field is directly passed to the function, you can perform functions externally to the SELECT statement. This is useful for doing all kinds of file conversions and other operations that aren’t necessarily dependent on the fields in the query.

Here is an example of a custom function that merges all of the CSV files it finds in a specified directory into a single file before running a query against that file:

Note: This function is NOT included in the provided sample files.

 

In this example, the function merge_csv_files takes in two arguments:

  1. The location of the folder to search for CSV files (e.g. 'C:\Test\Samples_CSV_Files')
  2. The location of the merged output file (e.g. 'C:\Test\output.csv')

The QueryPair first calls the merge_csv_files function, which performs the merge and outputs the output.csv file. It thens perform the SELECT query on the newly created output.csv file.

As you can see the custom function feature is pretty versatile and can be used in many different ways to help test your data.

Resources

Related articles