Forum

Discussions specific to version 8.1
Contributor
Offline
Posts: 183
Joined: Wed May 29, 2013 2:10 pm
Location: Bracknell

Using external tables in Kognitio Console

by MikeAtkinson » Thu May 30, 2013 2:40 pm

In a previous post I showed how external scripts may be used to view the directory tree of the system on which the Kognitio Analytics Platform (KAP) is running. This example will show how external tables may be used to browse the contents of another database through the KAP.

First we need to create a script environment CROSSLOAD (the 'filetype 1' means use binary):

Code: Select all

create script environment CROSSLOAD 
    command '/opt/kognitio/wx2/current/software/Linux/wxdistload' 
    input 'filetype 1'; 
And we also need to enable the ODBC plugin module:

Code: Select all

create module ODBC mode active; 
Then we create a connector foo_crossload to a database foo (IP address 100.0.0.11 port 6550). In this example foo is a KAP, but something similar should work for any database:

Code: Select all

create connector foo_crossload 
    source crossload 
    target 'connect "SERVER=100.0.0.11:6550;UID=sys;PWD=password"'; 
Now using the foo_crossload connector we create two external tables, one to access schemas in database foo, then other to access tables:

Code: Select all

create external table foo_table 
    from foo_crossload 
    target 'query "select * from sys.ipe_table" '; 

create external table foo_schema 
    from foo_crossload 
    target 'query "select * from sys.ipe_schema" '; 
Now we may use a query to do a directory listing. Where %1 is replaced by '.' for a top level (schema) listing and by the schema name to list the tables in that schema:

Code: Select all

select priv, name from ( 
    select 'd','.', s.name from foo_schema s 
    union 
    select '_',s.name, t.name from foo_schema s, foo_table t where t.schema_id = s.id) dt(priv, path, name) 
where path = '%1'; 
And an anonymous external table to list the contents of an arbitrary table on foo, where %1 is replaced by the table name:

Code: Select all

select * from (external table from foo_crossload target 'query "select * from %1" ')et;
Using a similar external tables it is possible to browse the contents of another database from Kognitio Console connected through a Kognitio Analytics Platform instance.

In the screenshot below, Kognitio Console is connected to "latest" and is browsing the database "flakey". The contents of tables on "flakey" may be viewed, in this case the table Test1 in schema Mike.

Image
Reply with quote Top
Contributor
Offline
User avatar
Posts: 48
Joined: Tue May 28, 2013 1:44 pm

Re: Using external tables in Kognitio Console

by anonymous2 » Fri Jun 14, 2013 12:52 pm

Thanks Mike, very helpful.
Reply with quote Top
Contributor
Offline
User avatar
Posts: 11
Joined: Wed Jun 26, 2013 12:32 pm

Re: Using external tables in Kognitio Console

by schneider » Tue Nov 29, 2016 5:47 pm

HY000[Kognitio][WX2 Driver][GDW14_DEV] PI0183: Module ODBC: Invalid subnet list specified

Where would I start looking if I receive the following error?
Reply with quote Top
Contributor
Offline
User avatar
Posts: 384
Joined: Thu May 23, 2013 4:48 pm

Re: Using external tables in Kognitio Console

by markc » Wed Nov 30, 2016 8:14 am

I've seen this before with a one node system - in that case, the attribute max_link_number is 0, and link0_ip4_addr is 127.0.0.1.

These two attributes are given to the connector by the server. When it tries to make a list of subnets which correspond to this list of links, it explicitly ignores localhost addresses because usually it's not useful to listen for connections on localhost. So it ends up with no usable listener addresses.

In the previous instance of this problem, the node had eth0-eth3 with IP addresses 172.30.22.15 - 172.33.22.15, and was doing a crossload to itself as a test, so we put:

subnets 172.30.22.15/18,172.31.22.15/18,172.32.22.15/18,172.33.22.15/18

into the target string, to force it to use eth0, eth1, eth2 and eth3 even though the connector isn't told about those links.
Reply with quote Top

Who is online

Users browsing this forum: No registered users and 1 guest

cron