Parallel connections to SQL Server

danila
SQL-Fighter

I want to create a script which will be able to read data from multiple SQL Server instances in parallel.

I already saw this article: https://community.exasol.com/t5/database-features/parallel-connections-with-jdbc/ta-p/1779

But the problem is that I'm not familiar with Java and looks like it uses parallel connections to Exasol, not to other provider.

Does anyone have an example of such a script?

Currently I have only this implementation of non-parallel script and it is very slow.

 

CREATE LUA SCRIPT SANDBOX.TEST () RETURNS TABLE AS
local sSQLServerConnectionName
local success
local res
local result_table = {}
local tConnections = query([[select connection_name from exa_dba_connections]]);

for i = 1, #tConnections, 1 do
	sSQLServerConnectionName = tConnections[i].CONNECTION_NAME

	success, res = pquery([[
	import from jdbc at ::SQLServerConnectionName
	statement 'select @@servername as ServerName'
	]],{SQLServerConnectionName = sSQLServerConnectionName});

	if success 	then
		for n = 1, #res, 1 do
			table.insert (result_table, {res[n][1]})
		end
	end
end

exit (result_table, [[Server_Name varchar(128)]])

 

14 REPLIES 14

danila
SQL-Fighter

SQL Servers may see each other via Linked Server, but currently Linker Servers are not created. And I'd like to leave it as is.

When I try to use Exasol as central server, like in your query:

IMPORT INTO (SN varchar(100))
FROM EXA AT 'jdbc:exa:192.168.0.1..7:8563' USER 'TEST' IDENTIFIED BY 'test123'
STATEMENT 'select SN from (import from jdbc at SQLSRV1_CONN statement ''select @@servername as SN'') t'
STATEMENT 'select SN from (import from jdbc at SQLSRV2_CONN statement ''select @@servername as SN'') t';

I get the error:

Must specify exactly one remote table [line 3, column 11]

With one STATEMENT clause it works without error.

mwellbro
Xpert

I think you´ll need to use FROM JDBC , not FROM EXA in this case

danila
SQL-Fighter

Thank you, guys. Now the query works 3 times faster.

exa-StefanR
Team Exasol
Team Exasol

Import from JDBC, not from EXA...

mwellbro
Xpert

On the note of "however, that approach will be a direct invitation to the JDBC/Java programming club...", @exa-StefanR :

 

CREATE OR REPLACE JAVA SCALAR SCRIPT MY_SCHEMA.CALL_THREADS() EMITS (c1 VARCHAR(2000)) AS 
%jar /buckets/bfsdefault/jdbc/postgresql-42.2.5.jar;

import java.sql.*;

%import MY_SCHEMA.RUNNABLE_THREAD;

class CALL_THREADS {

  static void run(ExaMetadata exa, ExaIterator ctx) throws Exception {
        final String[] my_var = new String[3];
        RUNNABLE_THREAD a = new RUNNABLE_THREAD( "Thread-1", my_var , 0);
        a.start();
        //try {
        //    a.t.join();
        //} catch (Exception e) {}

        RUNNABLE_THREAD b = new RUNNABLE_THREAD( "Thread-2", my_var , 1);
        b.start();

        //try {
        //    b.t.join();
        //} catch (Exception e) {  }


        RUNNABLE_THREAD c = new RUNNABLE_THREAD( "Thread-3", my_var , 2);
        c.start();

        try {
            c.t.join();
            b.t.join();
            a.t.join();
        } catch (Exception e) {
        }
 
  ctx.emit(my_var[0]);
  ctx.emit(my_var[1]);
  ctx.emit(my_var[2]);
 return;
 }
}
/

 

 

mwellbro_0-1617924088818.png

 

Didn´t want to fire up 3 different DB´s so just to show how something like this might ( emphasize might ) be done 🙂

Fun well known fact: once your source is clogged you don´t gain any benefits from parallization 😉

mwellbro_1-1617924256595.png

 

Cheers,
Malte