ignite.net custom sql functions

classic Classic list List threaded Threaded
12 messages Options
wt wt
Reply | Threaded
Open this post in threaded view
|

ignite.net custom sql functions

good day

I can't seem to find anything on the .net api for custom sql functions. Can
anyone tell me if it is possible in .net as it is here in Java

https://apacheignite-sql.readme.io/docs/custom-sql-functions

Thanks
Wayne




--
Sent from: http://apache-ignite-users.70518.x6.nabble.com/
ilya.kasnacheev ilya.kasnacheev
Reply | Threaded
Open this post in threaded view
|

Re: ignite.net custom sql functions

Hello!

They are not supported yet.

However, you can still configure your caches to use Java custom functions and use them even if the rest of your project is .Net. I assume that functions are usually self-contained.

Regards,
--
Ilya Kasnacheev


ср, 27 февр. 2019 г. в 12:13, wt <[hidden email]>:
good day

I can't seem to find anything on the .net api for custom sql functions. Can
anyone tell me if it is possible in .net as it is here in Java

https://apacheignite-sql.readme.io/docs/custom-sql-functions

Thanks
Wayne




--
Sent from: http://apache-ignite-users.70518.x6.nabble.com/
wt wt
Reply | Threaded
Open this post in threaded view
|

Re: ignite.net custom sql functions

Thanks Ilya

So i need a little guidance here if you don't mind i am just throwing
together a quick test as a precursor to a new project.

I have a .net ignite service wrapper


----
 var cfg = new IgniteConfiguration()
                {
                    SpringConfigUrl = "/ignitecfg.xml",
                    DiscoverySpi = new TcpDiscoverySpi
                    {
                        IpFinder = new TcpDiscoveryStaticIpFinder
                        {
                            Endpoints = new[] { "127.0.0.1:47500..47509" }
                        },
                        SocketTimeout = TimeSpan.FromSeconds(0.3)
                    }

                };

                Ignition.Start(cfg);

-----

i am using the spring xml section to register the custom sql functions which
i have written in java and compiled to a jar file. I have found several
examples in java code for registering the custom function but nothing
showing how to do it in the xml config file. Also i am unsure where the
classpath will be given that this is all running with a .net wrapper. Any
guidance here will be super awesome.

thanks
Wayne



--
Sent from: http://apache-ignite-users.70518.x6.nabble.com/
ptupitsyn ptupitsyn
Reply | Threaded
Open this post in threaded view
|

Re: ignite.net custom sql functions

Hi Wayne,

You can write a service in Java that registers custom SQL function.
Then call that service from .NET.
Classpath is provided in IgniteConfiguration.JvmClasspath.

See https://ptupitsyn.github.io/Ignite-Plugin/ to get some idea on how to combine Java-based Ignite stuff with Ignite.NET.
Let me know if you need more details.

Thanks,
Pavel

On Wed, Feb 27, 2019 at 5:54 PM wt <[hidden email]> wrote:
Thanks Ilya

So i need a little guidance here if you don't mind i am just throwing
together a quick test as a precursor to a new project.

I have a .net ignite service wrapper


----
 var cfg = new IgniteConfiguration()
                {
                    SpringConfigUrl = "/ignitecfg.xml",
                    DiscoverySpi = new TcpDiscoverySpi
                    {
                        IpFinder = new TcpDiscoveryStaticIpFinder
                        {
                            Endpoints = new[] { "127.0.0.1:47500..47509" }
                        },
                        SocketTimeout = TimeSpan.FromSeconds(0.3)
                    }

                };

                Ignition.Start(cfg);

-----

i am using the spring xml section to register the custom sql functions which
i have written in java and compiled to a jar file. I have found several
examples in java code for registering the custom function but nothing
showing how to do it in the xml config file. Also i am unsure where the
classpath will be given that this is all running with a .net wrapper. Any
guidance here will be super awesome.

thanks
Wayne



--
Sent from: http://apache-ignite-users.70518.x6.nabble.com/
ilya.kasnacheev ilya.kasnacheev
Reply | Threaded
Open this post in threaded view
|

Re: ignite.net custom sql functions

Hello!

> I have found several examples in java code for registering the custom function but nothing
showing how to do it in the xml config file.
// Preparing a cache configuration.
CacheConfiguration cfg = new CacheConfiguration();

// Registering the class that contains custom SQL functions.
cfg.setSqlFunctionClasses(MyFunctions.class);
should roughy convert to
<bean class="org.apache.ignite.configuration.CacheConfiguration">
<property name="sqlFunctionClasses"><list><value>com.pany.MyFunctions</value></list></property>

> Also i am unsure where the classpath will be given that this is all running with a .net wrapper.

You can put your JAR and all its dependencies under $IGNITE_HOME\libs

Under Windows some flavor of IGNITE_HOME will be created for you implicitly, but you can always specify your own IgniteHome, pointing to unzipped binary Ignite release.

Regards,
--
Ilya Kasnacheev


ср, 27 февр. 2019 г. в 18:42, Pavel Tupitsyn <[hidden email]>:
Hi Wayne,

You can write a service in Java that registers custom SQL function.
Then call that service from .NET.
Classpath is provided in IgniteConfiguration.JvmClasspath.

See https://ptupitsyn.github.io/Ignite-Plugin/ to get some idea on how to combine Java-based Ignite stuff with Ignite.NET.
Let me know if you need more details.

Thanks,
Pavel

On Wed, Feb 27, 2019 at 5:54 PM wt <[hidden email]> wrote:
Thanks Ilya

So i need a little guidance here if you don't mind i am just throwing
together a quick test as a precursor to a new project.

I have a .net ignite service wrapper


----
 var cfg = new IgniteConfiguration()
                {
                    SpringConfigUrl = "/ignitecfg.xml",
                    DiscoverySpi = new TcpDiscoverySpi
                    {
                        IpFinder = new TcpDiscoveryStaticIpFinder
                        {
                            Endpoints = new[] { "127.0.0.1:47500..47509" }
                        },
                        SocketTimeout = TimeSpan.FromSeconds(0.3)
                    }

                };

                Ignition.Start(cfg);

-----

i am using the spring xml section to register the custom sql functions which
i have written in java and compiled to a jar file. I have found several
examples in java code for registering the custom function but nothing
showing how to do it in the xml config file. Also i am unsure where the
classpath will be given that this is all running with a .net wrapper. Any
guidance here will be super awesome.

thanks
Wayne



--
Sent from: http://apache-ignite-users.70518.x6.nabble.com/
wt wt
Reply | Threaded
Open this post in threaded view
|

Re: ignite.net custom sql functions

Thank you both for your assistance here, it is greatly appreciated - Ignite
Rocks



--
Sent from: http://apache-ignite-users.70518.x6.nabble.com/
wt wt
Reply | Threaded
Open this post in threaded view
|

Re: ignite.net custom sql functions

one question regarding this configuration. I noticed it is cache specific and
to me that implies every cache will need to have this configuration section
set. Is there perhaps a way to configure this globally once instead of
replicating it for every cache?



--
Sent from: http://apache-ignite-users.70518.x6.nabble.com/
wt wt
Reply | Threaded
Open this post in threaded view
|

Re: ignite.net custom sql functions

In reply to this post by wt
I have just realized an issue with this process. I have built my own version
of the web console that generates caches for tables in databases. This is an
automated process in the UI so i do not know what additional caches will be
created beforehand. This leaves me with the dilemma of this custom function
configuration which seems to be associated with specific caches and not
globally available. I hope there is a solution to this else i will have a
lot of code to change to shift things over to the Public Cache which isnt
ideal as i need isolation.



--
Sent from: http://apache-ignite-users.70518.x6.nabble.com/
ilya.kasnacheev ilya.kasnacheev
Reply | Threaded
Open this post in threaded view
|

Re: ignite.net custom sql functions

In reply to this post by wt
Hello!

I think you can refer to functions as "schemaName".function()
This means you can create a single cache with known schema and all the functions, always refer to it.

Regards,
--
Ilya Kasnacheev


чт, 28 февр. 2019 г. в 11:29, wt <[hidden email]>:
one question regarding this configuration. I noticed it is cache specific and
to me that implies every cache will need to have this configuration section
set. Is there perhaps a way to configure this globally once instead of
replicating it for every cache?



--
Sent from: http://apache-ignite-users.70518.x6.nabble.com/
wt wt
Reply | Threaded
Open this post in threaded view
|

Re: ignite.net custom sql functions

I don't know why i didn't think of that - superb thanks Ilya.

Are you certain the function needs to be in libs\home - It can't seem to
find the function and i would have expected to at least see something in the
log about loading it.

ENV setup

system variable  IGNITE_HOME = c:\Ignite_2.7

test function code (no FQDN namespace it is just a class at the root):

/public class testfunc {

    @QuerySqlFunction
    public static int square(int x) {
        return x * x;
    }
}/

I copied the jar file to c:\Ignite_2.7\libs (at the root of that folder)

XML config section


 <property name="cacheConfiguration">
                          <list>
                <bean
class="org.apache.ignite.configuration.CacheConfiguration">
                    <property name="name" value="wayne"/>
                    <property name="atomicityMode" value="ATOMIC"/>
                    <property name="backups" value="1"/>
                                        <property name="sqlFunctionClasses">      
                                                <list>    
                                                        <value>testfunc</value>      
                                                </list>      
                                        </property>
                </bean>
            </list>
         </property>

I have no data in the cache but if i try run the following from dbbeaver i
get an error:

SELECT square(10) FROM WAYNE.WAYNE

here is the error and note it is trying to access the public schema.

/11:41:11,859][SEVERE][client-connector-#63][JdbcRequestHandler] Failed to
execu
e SQL query [reqId=0, req=JdbcQueryExecuteRequest [schemaName=PUBLIC,
pageSize=
024, maxRows=200, sqlQry=SELECT square(10) FROM WAYNE.WAYNE, args=Object[]
[],
tmtType=ANY_STATEMENT_TYPE, autoCommit=true]]
lass org.apache.ignite.internal.processors.query.IgniteSQLException: Failed
to
arse query. Function "SQUARE" not found; SQL statement:/


So i created a basic table in public called tes and tried to run this

SELECT wayne.square(10)  FROM tes

Same issue  - function not found

I then tried this

SELECT wayne.wayne.square(10)  FROM tes

and it states database is not found.

Seems to me like the jar file is not being loaded.






--
Sent from: http://apache-ignite-users.70518.x6.nabble.com/
ilya.kasnacheev ilya.kasnacheev
Reply | Threaded
Open this post in threaded view
|

Re: ignite.net custom sql functions

Hello!

It does not seem that you define any tables for this cache, but

0: jdbc:ignite:thin://localhost> SELECT "wayne".square(4);
+--------------------------------+
|       "wayne".SQUARE(4)        |
+--------------------------------+
| 16                             |
+--------------------------------+
1 row selected (0,096 seconds)

seems to work for me! Note the quotes since cache names/schemas are case sensitive.

Regards,
--
Ilya Kasnacheev


чт, 28 февр. 2019 г. в 14:49, wt <[hidden email]>:
I don't know why i didn't think of that - superb thanks Ilya.

Are you certain the function needs to be in libs\home - It can't seem to
find the function and i would have expected to at least see something in the
log about loading it.

ENV setup

system variable  IGNITE_HOME = c:\Ignite_2.7

test function code (no FQDN namespace it is just a class at the root):

/public class testfunc {

    @QuerySqlFunction
    public static int square(int x) {
        return x * x;
    }
}/

I copied the jar file to c:\Ignite_2.7\libs (at the root of that folder)

XML config section


 <property name="cacheConfiguration">
                          <list>
                <bean
class="org.apache.ignite.configuration.CacheConfiguration">
                    <property name="name" value="wayne"/>
                    <property name="atomicityMode" value="ATOMIC"/>
                    <property name="backups" value="1"/>
                                        <property name="sqlFunctionClasses">                                       
                                                <list>                                                   
                                                        <value>testfunc</value>                                             
                                                </list>                                     
                                        </property>
                </bean>
            </list>
         </property>

I have no data in the cache but if i try run the following from dbbeaver i
get an error:

SELECT square(10) FROM WAYNE.WAYNE

here is the error and note it is trying to access the public schema.

/11:41:11,859][SEVERE][client-connector-#63][JdbcRequestHandler] Failed to
execu
e SQL query [reqId=0, req=JdbcQueryExecuteRequest [schemaName=PUBLIC,
pageSize=
024, maxRows=200, sqlQry=SELECT square(10) FROM WAYNE.WAYNE, args=Object[]
[],
tmtType=ANY_STATEMENT_TYPE, autoCommit=true]]
lass org.apache.ignite.internal.processors.query.IgniteSQLException: Failed
to
arse query. Function "SQUARE" not found; SQL statement:/


So i created a basic table in public called tes and tried to run this

SELECT wayne.square(10)  FROM tes

Same issue  - function not found

I then tried this

SELECT wayne.wayne.square(10)  FROM tes

and it states database is not found.

Seems to me like the jar file is not being loaded.






--
Sent from: http://apache-ignite-users.70518.x6.nabble.com/
wt wt
Reply | Threaded
Open this post in threaded view
|

Re: ignite.net custom sql functions

That works - super happy with the result you are always so helpful thank you
Ilya



--
Sent from: http://apache-ignite-users.70518.x6.nabble.com/