Forum

Discussions specific to version 8.1
Single Poster
Offline
User avatar
Posts: 1
Joined: Tue Jul 12, 2016 2:13 pm

Alternative for oracle procedures

by LearningCoder » Tue Jul 12, 2016 2:21 pm

Hi,

I have a set of oracle based procedures which we are migrating from oracle to kognitio.
Is there any alternative that has been tried apart from kognitio External script.

External script does not seem to work around for our work, since our requirement is to more of call SQL based script to carry out logic.

Kindly help or share your experience if you have dealt with it previously.

Best Regards,
Learner
Reply with quote Top
Contributor
Offline
User avatar
Posts: 386
Joined: Thu May 23, 2013 4:48 pm

Re: Alternative for oracle procedures

by markc » Wed Jul 13, 2016 8:21 am

Can you change the code so that you feed all the relevant information into the external script instance(s), rather than having to run SQL within each instance?
Reply with quote Top
Contributor
Offline
Posts: 39
Joined: Mon Jan 06, 2014 10:36 am

Re: Alternative for oracle procedures

by skkirkham » Wed Jul 13, 2016 8:52 am

Hi Learner

As well as external scripts that are geared towards running no-sql code within a SQL framework. Kognitio console can be used to develop SQL scripts although unlike stored procedures these are not saved within Kognitio itself. The basis of Kognitio scripting is the lua language and complex scripts can be developed easily. Check out the documentation on Kog scripting here.

When converting Oracle stored procedures in the past I have found that many of these often involve looping through a set of values for a given field (such as weeks in a date range for example). This is to manage the fact that Oracle can struggle running the particular processes over all the data at once.

If this is the case in the procedures you are looking at I would investigate the option of replacing the stored procedure with a series of normal SQL commands that work over all or significant subsets of the data rather than having to use looping. Kognitio will probably work fine with this. If not you can do the looping using the kog scripting mentioned above although the script cannot be saved into the database.

Regards
Sharon
Reply with quote Top
Contributor
Offline
Posts: 185
Joined: Wed May 29, 2013 2:10 pm
Location: Bracknell

Re: Alternative for oracle procedures

by MikeAtkinson » Wed Jul 13, 2016 9:22 am

As Sharon says there are also the wxsubmit and kogscript tools. These are available in both server and client (Console) installations.

Depending on what you wish to achieve, either or both might be suitable, for new scripts we generally recommend KogScript. They work best when queries return relatively small amounts of data (a few rows) and when processing can be done outside the query.

wxsubmit

wxsubmit allows variables to be set, sql queries to be executed with variable substitution and simple flow control based on the results of those queries.

The following example shows looping on a counter, using variables and the goto statement:

Code: Select all

create table rec_counter (counter int);

truncate table rec_counter;
insert into rec_counter values(1);


begin:

select counter from rec_counter;

set variable pass $Col1;

-- If we have gone past the last week then exit the loop
if $pass > 52 goto end;

select $pass;

-- Update the week counter
update rec_counter set counter = counter + 1;

-- Restart the loop
goto begin;


end:

select 'end';

kogscript

kogscript is a complete scripting language based on Lua which also allows embedded "naked" SQL statements. As a full language KogScript has a full range of flow control, procedures, closures, coroutines, modules, etc.

The following example gives a flavour of the language when used for a non-trivial task (in this case clustering a graph):

Code: Select all

-- Uncomment to change schema from user's default
--set schema your_schema;

-- Function to fill a small graph with (non-random) data
function adddata(lim,t)
    for i=0,lim do
        j = i*10
        insert into $t values ($(j+1),$(j+2)), ($(j+1),$(j+3)), ($(j+1),$(j+4)), ($(j+2),$(j+3)), ($(j+2),$(j+5)), ($(j+4),$(j+3)), ($(j+4),$(j+5));
        insert into $t values ($(j+1),$(j+6)), ($(j+6),$(j+1)), ($(j+6),$(j+7)), ($(j+7),$(j+8)), ($(j+8),$(j+9)), ($(j+9),$(j+7)), ($(j+7),$(j+3));
        insert into $t values ($(j+9),$(j+10)), ($(j+5),$(j+10));
    end
    for i=0,(lim-1) do
        j = i*10
        k = i*10 + 10
        insert into $t values ($(j+1),$(k+2)), ($(k+1),$(j+3)), ($(j+3),$(k+3)), ($(j+6),$(k+5)), ($(j+8),$(k+1));
    end
    for i=0,(lim-3) do
        j = i*10
        k = i*10 + 30
        insert into $t values ($(j+1),$(k+7)), ($(k+6),$(j+2)), ($(k+9),$(j+1)), ($(k+8),$(j+2));
    end
    for i=0,(lim-5) do
        j = i*10
        k = i*10 + 50
        insert into $t values ($(j+1),$(k+5)), ($(k+9),$(j+1));
        insert into $t values ($(j+1),$(k+5)), ($(k+10),$(j+3));
    end
end

-- drop existing tables, so we can start fresh
drop table nodes;
drop table links;
create table links (o int, d int);

-- Insert a few nodes (120) and links (622) into the tables.
adddata(11,'links')

/* find all nodes in the graph */
/* assign their colours to be 0  = undetermined */
create table nodes as select distinct(n) as node, 0 as colour from (select o from links union select d from links) d(n);

/* Get the number of nodes we are working with */
select count(*) from nodes;
number_of_nodes = Col1;


/* set random nodes to have colours */
update nodes set colour=node where node mod 17 = 1;



/* iterate - follow links colouring destination nodes as we go. */
/* Alternate between using max and min() to try and ensure that the regions have roughly equal size,
     use large_number to indicate unassigned colours when we need to use a minimum */
largenumber = number_of_nodes + 1000000;

drop table nodes1;
create ram only table nodes1 as
    select node, colour from nodes where colour<>0
    union
    select links.d as node, max(nor.colour) as colour
        from nodes nor, nodes nd, links
        where (links.o=nor.node and links.d=nd.node and nd.colour=0) group by links.d;

for i=1,3 do

    drop table nodes2;
    create ram only table nodes2 as
        select node, colour from nodes1 where colour<>0
    union
        select links.d as node, min(case when nor.colour=0 then $largenumber else nor.colour end) as colour 
             from nodes1 nor, nodes1 nd, links
             where (links.o=nor.node and links.d=nd.node and nd.colour=0) group by links.d;

    drop table nodes1;
    create ram only table nodes1 as
         select node, colour from nodes2 where colour<>$largenumber
    union
         select links.d as node, max(case when nor.colour=$largenumber then 0 else nor.colour end) as colour
             from nodes2 nor, nodes2 nd, links
             where (links.o=nor.node and  links.d=nd.node and nd.colour=$largenumber )  group by links.d;

    select count(*) from nodes1 where colour=0;
    uncoloured_nodes = Col1;
    if (uncoloured_nodes / number_of_nodes) < 0.01 then break end

end;


drop view clinks;
create view image clinks hashed(colour) as select links.*, colour from links, nodes1 where links.o=nodes1.node;

drop view clinks_hairless;
create view image clinks_hairless hashed(c1) as select links.*, nor.colour c1, nd.colour c2 from links, nodes1 nor, nodes1 nd 
       where links.o=nor.node and links.d=nd.node and nor.colour=nd.colour;

drop view edge_nodes;
create view image edge_nodes as select distinct(nor.node) from links, nodes1 nor, nodes1 nd 
     where (links.o=nor.node and links.d=nd.node and nor.colour<>nd.colour);

drop view clinks_hairy;
create view image clinks_hairy hashed(c1) as select links.*, nor.colour c1, nd.colour c2 from links, nodes1 nor, nodes1 nd 
       where (links.o=nor.node and links.d=nd.node and nor.colour=nd.colour)
           or (links.o=nor.node and links.d=nd.node and nor.colour<>nd.colour)
           or (links.o=nd.node and links.d=nor.node and nor.colour<>nd.colour);

select * from clinks;
Reply with quote Top

Who is online

Users browsing this forum: No registered users and 1 guest

cron