search

Accenture / DBTestCompare

Application to compare results of two SQL queries
MIT License
25 stars 12 forks source link

Snowflake #30

Closed pniewiadowski closed 3 years ago

pniewiadowski commented 3 years ago

How I can add Snowflake? Are you able to help or point me where I can start adding this database to DBTestCompare tool?

raczeja commented 3 years ago

please add name of driver file to the https://github.com/ObjectivityLtd/DBTestCompare/blob/master/pom.xml#L38, downloaded drivers copy to the folder /jdbc_drivers and add new connection to the https://github.com/ObjectivityLtd/DBTestCompare/blob/master/test-definitions/cmpSqlResults-config.xml

raczeja commented 3 years ago

please let me know if it's working ?

pniewiadowski commented 3 years ago

@raczeja thanks for a quick response. I tried your solution yesterday, but wasn't able to replay you.

I have face another issue. Once I configure Snowflake DB and run test I got something like that:

[INFO ] 2021-01-28 08:19:46,684 RunTests - You can override some of the application configuration properties
Run app with:
 -DtestsDir=path            - set tests directory (default: test-definitions)
 -DteamcityLogsEnabled=true - log test output in TeamCity format
 -DfilterInclude=a.b,g.z.f  - comma separated directories or test files which you want to include
 -DfilterExclude=a.b.test   - comma separated directories or test files which you want to exclude
[DEBUG] 2021-01-28 08:19:46,686 RunTests - Scanning tests directory: test-definitions...
[DEBUG] 2021-01-28 08:19:46,687 RunTests - Reading configuration: test-definitions/cmpSqlResults-config.xml
[DEBUG] 2021-01-28 08:19:47,056 RunTests - Number of Threads set in configuration: 5
[DEBUG] 2021-01-28 08:19:47,057 RunTests - Initializing all components ...
[DEBUG] 2021-01-28 08:19:47,059 DataSource - Adding shutdown hook (closing connections)...
[DEBUG] 2021-01-28 08:19:47,059 DataSource - Initializing data source connection pools...
[DEBUG] 2021-01-28 08:19:47,060 DataSource - Max Pool Size:6 for datasource: TERADATA_SERVER
[DEBUG] 2021-01-28 08:19:47,622 DataSource - Max Pool Size:6 for datasource: SNOWFLAKE_SERVER
[DEBUG] 2021-01-28 08:19:47,637 DataSource - Max Pool Size for each DataSource should be >= Threads
[DEBUG] 2021-01-28 08:19:47,640 TestDataProvider - Initializing tests Data Provider...
[DEBUG] 2021-01-28 08:19:47,643 Printer - Initializing logger / printer ...
[DEBUG] 2021-01-28 08:19:47,759 TestDataProvider - Reading tests configuration...
[DEBUG] 2021-01-28 08:19:47,788 TestDataProvider - Running tests...
[INFO ] 2021-01-28 08:19:47,852 DBTestCompare:ae.conifg - Starting test...
[INFO ] 2021-01-28 08:19:49,844 DBTestCompare:ae.conifg - TEST FAILED
java.lang.Exception: java.sql.SQLFeatureNotSupportedException: ResultSet type 1004 is not supported.
Query 1: SELECT distinct GENDER FROM CITIBIKE."PUBLIC".TRIPS
Query 2: SELECT distinct GENDER FROM CITIBIKE."PUBLIC".TRIPS
        at uk.co.objectivity.test.db.comparators.FetchComparator.compare(FetchComparator.java:81)
        at uk.co.objectivity.test.db.DBTestCompare.testSQLResults(DBTestCompare.java:89)
        at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
        at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
        at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
        at java.lang.reflect.Method.invoke(Method.java:498)
        at org.testng.internal.MethodInvocationHelper.invokeMethod(MethodInvocationHelper.java:133)
        at org.testng.internal.TestInvoker.invokeMethod(TestInvoker.java:584)
        at org.testng.internal.TestInvoker.invokeTestMethod(TestInvoker.java:172)
        at org.testng.internal.MethodRunner.runInSequence(MethodRunner.java:46)
        at org.testng.internal.TestInvoker$MethodInvocationAgent.invoke(TestInvoker.java:804)
        at org.testng.internal.TestInvoker.invokeTestMethods(TestInvoker.java:145)
        at org.testng.internal.TestMethodWorker.invokeTestMethods(TestMethodWorker.java:146)
        at org.testng.internal.TestMethodWorker.run(TestMethodWorker.java:128)
        at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149)
        at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624)
        at java.lang.Thread.run(Thread.java:748)

I am using FETCHmethod to compare results. Will try to do some research, but maybe you have a solution already.

pniewiadowski commented 3 years ago

Ok, that was quicker than I thought :)

In FetchComparator.java there is a:

connection1.prepareStatement(sql1.getSql(), ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet
                    .CONCUR_READ_ONLY);

Which use ResultSet.TYPE_FORWARD_ONLY and according to Snowflake JDBC driver is one of not supported ResultSet types. Changing this to:

connection1.prepareStatement(sql2.getSql(), ResultSet.TYPE_FORWARD_ONLY, ResultSet
                    .CONCUR_READ_ONLY);

I was able to run the test.

raczeja commented 3 years ago

you can make PR with your changes, I will merged...

pniewiadowski commented 3 years ago

Well, no problem I can do this, but I am not sure how this influence other databases. Do you know?

So far I have test it with Teradata and Snowflake and that's working.

raczeja commented 3 years ago

during the build is tested against SQL Server, MySQL and PostgreSQL https://dev.azure.com/DBTestCompare/Build/_build/results?buildId=170&view=ms.vss-test-web.build-test-results-tab we will see...

pniewiadowski commented 3 years ago

PR #31 added

raczeja commented 3 years ago

I think that just name of new driver is missing in https://github.com/ObjectivityLtd/DBTestCompare/blob/master/pom.xml#L38

pniewiadowski commented 3 years ago

Yes, sorry about that. Done

raczeja commented 3 years ago

thank you