9 Replies Latest reply on Dec 20, 2012 8:27 AM by Van Halbert

    SQL Bulk Query Tool (BQT)

    atzakas Newbie

      Hi,

       

      I recently started working with Teiid (v7.4) to create and deploy VDBs (using Teiid Designer) and I am currently exploring ways of how to automate the testing of the models.

      I am not a developer so please bear with my newbie questions.

       

      Among other things, I would like to be able to perform bulk queries and compare the results against known values.

      This could also be used to query the system tables and ensure that the metadata (table names, column names, etc) of the model match with those of the data sources.

       

      So far I have explored the following options:

      • AdminShell and Groovy. The benefit of this approach is that AdminShell is part of Teiid (i.e. future support and development) and Groovy Sql is quite easy to get started with..
      • SQL BulkQueryTool (BQT) https://github.com/vhalbert/SQLBulkQueryTool 

       

      With regards to the BQT, I came across this framework and it looked ideal for meeting my requirement of performing bulk queries.

      After configuring the .properties/xml files and executing the run script, I get runtime exception errors (FrameworkException, NoClassDefFoundError among others).

      It seems to me that the tool is not as easy to use as I was hoping (remember, I am not an experienced Java developer) but, most importantly, it not not clear to me whether this is a community-supported tool.

       

      My questions are:

      • Is BQT a community-supported tool and, if so, is there a dedicated forum that I could join?
      • Is there any additional piece of documentation that I could use, apart from the readme file?
      • Has anyone else worked with the BQT before and, if so, what has his experience been with regards to the capabilities of the tool?
      • What is the preferred tool for performing bulk query testing with Teiid?

       

       

      Many thanks,

      Aris

        • 1. Re: SQL Bulk Query Tool (BQT)
          Van Halbert Master

          I appologize, that's my fault.  I've been in the process of moving our internally created tool to the opensource and have not finished.   We've been using this tool since the MetaMatrix days and have been trying to push this out.   It would do what you're asking for.   If you're interested and if you'll give me a few day's, I'll push it up in priority and get it done.

           

           

          Ok, the latest changes have been applied and updated the README.md to help explain how to use it.   

          1 of 1 people found this helpful
          • 2. Re: SQL Bulk Query Tool (BQT)
            atzakas Newbie

            Hello,

             

            thank you for the quick reply.

             

            It's good to know that this tool has the support of the community. I am very interested in making use of the tool and I am more than happy to contribute, if not in the coding then at least in the documentation.

             

             

            Regards,

            Aris

            • 3. Re: SQL Bulk Query Tool (BQT)
              Van Halbert Master

              Then I'll finish up on the porting process and then I'll get back to you when its ready.   And then we take it from there.

               

              Thanks.

               

              Van

              • 4. Re: SQL Bulk Query Tool (BQT)
                Van Halbert Master

                Ok, the latest changes have been applied to the git repo and an updated README.md to help explain how to use it. 

                • 5. Re: SQL Bulk Query Tool (BQT)
                  atzakas Newbie

                  Hello,

                   

                  thank you for getting back with the changes so quickly.

                   

                  I am still having some trouble making the tool work, and I would like to confirm a few things that I noticed:

                   

                  - I had to install the latest Java SDK (was using 1.6x) to complete the Maven build successfully. Is this a known dependency?

                  - I had to change the classpath to point to the generated class files, otherwise the script is complaining that it couldn't find the main class org.jboss.bqt.client.TestClient. (I commented out the existing line and replaced it with a new one)

                   

                  # CP="${ROOTDIR}:${ROOTDIR}/config/*:${ROOTDIR}/lib/*"

                  CP="C:/SQLBulkQueryTool/client/target/classes/;C:/SQLBulkQueryTool/core/target/classes;C:/SQLBulkQueryTool/framework/target/classes;C:/Dev/teiid-adminshell-7.4.0.Final/lib"

                   

                  - I am running the tool locally, on a Windows machine, using Cygwin. The dev machine, where the Teiid instance is running, is a remote Unix box. I have configured the "run.conf", as well as the .properties files, to point to the right location, but I am getting the following error:

                   

                  error.JPG

                   

                  - One of the steps in the README file says "place your jdbc driver into the lib directory". There is no /lib folder in the BQT, however I did specify the location of the driver in the CP. It maked me wonder though whether I should be running this tool on the actual machine where the VDB is deployed.

                   

                   

                  Kind regards,

                  Aris

                  • 6. Re: SQL Bulk Query Tool (BQT)
                    Van Halbert Master

                    - for JDK, that dependency can be removed from the pom

                    - ah, it appears you're trying to run the tests thru maven.   The initial release only supports using the distro (distro/target/distribution).  The enhancements can easily be made to do use maven.

                    - the error your getting is because the scenarios directory is not found.   Try using the distro, its packaged in the kit, as well as, the lib directory.

                     

                    --van

                    • 7. Re: SQL Bulk Query Tool (BQT)
                      Van Halbert Master

                      atzakas: to build the distro:  mvn clean install -Pdistro   

                      • 8. Re: SQL Bulk Query Tool (BQT)
                        atzakas Newbie

                        Hello,

                         

                        thanks for the help. I can now run my queries in bulk and do some regression testing.

                         

                        Just a couple of suggestions:

                         

                        a) With regards to the capability of capturing the query execution time, is it possible to get a more detailed view of how long it took to access each federated data source?

                        Knowing how long it takes to execute the entire query is nice, but it would be more useful if I could tell how long it took for each translated/decomposed query to execute.

                         

                        b) Would it be easy to add an option to generate a log file containing the explain query plan for each of the queries included in the tests? Along with the detailed timings from a) this would make a powerful tool for troubleshooting and optimizing queries.

                         

                         

                        Regards,

                        Aris

                        • 9. Re: SQL Bulk Query Tool (BQT)
                          Van Halbert Master

                          a)  The query plan does contain information about the node processing time, I'll need to check to see how to access it in the PlanNode.  But maybe doable.

                           

                          b)  this could be a good feature to add.  Have an option that will write out the query plan, and then use it in regression testing.   Because if you do any specific tuning on a query (i.e., add hint), it would be helpful to know if the query plan ever changes.

                           

                          I'll log an enhancement for "b", and I'll do a followup to see what's possible on "a".

                           

                          Thanks.

                           

                          Van