How to extracts value from json string in .Net Ignite sql query
Locked
|
This post was updated on .
Hi All,
I have .Net ClientServer Ignite Application.And Table Model annotation with QuerySQLField. In Tables some of column contains json string. How to extracts value from json string field in query? for example query select uuid, json_value(Payload,'$.comments') as comments from "F2DEDF6E-393E-42BC-9BB3-E835A1063B30_6EFB69B0-269F-4F92-98CF-24BC0D34BA98_COMMON".dailyjournal; Could you please guide me how to approach this. Thanks. -- Sent from: http://apache-ignite-users.70518.x6.nabble.com/ |
 
|
ilya.kasnacheev |
Re: How to extracts value from json string in .Net Ignite
|
|
Hello! You can define your own SQL function json_value to do that: https://ignite.apache.org/docs/latest/SQL/custom-sql-func You will need to do that in Java and deploy it to your server nodes (if you're using client node as a gateway for thin clients, also to such client nodes). Regards, -- Ilya Kasnacheev чт, 17 дек. 2020 г. в 20:14, siva <[hidden email]>: Hi All, |
|
|
This post was updated on .
Hi,
I have created and defined customsqlfunction IGNITE_HOME=apache-ignite-2.7.6-bin added Function jar at apache-ignite-2.7.6-bin\libs And added cache configuration in default-config <bean id="grid.cfg"
class="org.apache.ignite.configuration.IgniteConfiguration">
<property name="cacheConfiguration">
<list>
<bean class="org.apache.ignite.configuration.CacheConfiguration">
<property name="name" value="MyTemplateFunction"/>
<property name="cacheMode" value="PARTITIONED" />
<property name="backups" value="1" />
<property name="sqlFunctionClasses"
value="org.mycustomsqlfunction.MyCustomSQLFunction"/>
</bean>
</list>
</property>
</bean>And also added in .Net client and Server config files node like this <property name="cacheConfiguration">
<list>
<bean class="org.apache.ignite.configuration.CacheConfiguration">
<property name="name" value="MyTemplateFunction"/>
<property name="cacheMode" value="PARTITIONED" />
<property name="backups" value="1" />
<property name="sqlFunctionClasses"
value="org.mycustomsqlfunction.MyCustomSQLFunction"/>
</bean>
</list>
</property>Java class file package org.mycustomsqlfunction;
import org.apache.ignite.cache.query.annotations.QuerySqlFunction;
public class MyCustomSQLFunction {
@QuerySqlFunction
public static String Custom_Json_Value(String payload,String fieldName)
{
System.out.println(payload);
System.out.println(fieldName);
return "Test";
}
}But while running query getting error column not found: 0: jdbc:ignite:thin://127.0.0.1/> select
"MyTemplateFunction".Custom_Json_Value("","");
Error: Failed to parse query. Column not found; SQL statement:
select "MyTemplateFunction".Custom_Json_Value("","") [42122-197]
(state=42000,code=1001)
java.sql.SQLException: Failed to parse query. Column not found; SQL
statement:
select "MyTemplateFunction".Custom_Json_Value("","") [42122-197]
at
org.apache.ignite.internal.jdbc.thin.JdbcThinConnection.sendRequest(JdbcThinConnection.java:750)
at
org.apache.ignite.internal.jdbc.thin.JdbcThinStatement.execute0(JdbcThinStatement.java:212)
at
org.apache.ignite.internal.jdbc.thin.JdbcThinStatement.execute(JdbcThinStatement.java:475)
at sqlline.Commands.execute(Commands.java:823)
at sqlline.Commands.sql(Commands.java:733)
at sqlline.SqlLine.dispatch(SqlLine.java:795)
at sqlline.SqlLine.begin(SqlLine.java:668)
at sqlline.SqlLine.start(SqlLine.java:373)
at sqlline.SqlLine.main(SqlLine.java:265)
0: jdbc:ignite:thin://127.0.0.1/>
what am i doing wrong.please help.
Thanks. -- Sent from: http://apache-ignite-users.70518.x6.nabble.com/ |
|
|
ilya.kasnacheev |
Re: How to extracts value from json string in .Net Ignite
|
|
Hello! I don't think that you need to qualify functions with schema name. Please try it verbatim. You can specify default schema in the connection string, or alternatively try to do actual select from that table: Regards, -- Ilya Kasnacheev пн, 21 дек. 2020 г. в 15:40, siva <[hidden email]>: Hi, |
|
|
Hi,
i have tried like below in both case i am getting error 1.Without cache name 0: jdbc:ignite:thin://127.0.0.1/> select Custom_Json_Value("",""); Error: Failed to parse query. Function "CUSTOM_JSON_VALUE" not found; SQL statement: select Custom_Json_Value("","") [90022-197] (state=42000,code=1001) java.sql.SQLException: Failed to parse query. Function "CUSTOM_JSON_VALUE" not found; SQL statement: select Custom_Json_Value("","") [90022-197] at org.apache.ignite.internal.jdbc.thin.JdbcThinConnection.sendRequest(JdbcThinConnection.java:750) at org.apache.ignite.internal.jdbc.thin.JdbcThinStatement.execute0(JdbcThinStatement.java:212) at org.apache.ignite.internal.jdbc.thin.JdbcThinStatement.execute(JdbcThinStatement.java:475) at sqlline.Commands.execute(Commands.java:823) at sqlline.Commands.sql(Commands.java:733) at sqlline.SqlLine.dispatch(SqlLine.java:795) at sqlline.SqlLine.begin(SqlLine.java:668) at sqlline.SqlLine.start(SqlLine.java:373) at sqlline.SqlLine.main(SqlLine.java:265) 2.i logged in with customfunction cache name 'MyTemplateFunction' select Custom_Json_Value("",""); 0: jdbc:ignite:thin://127.0.0.1/MyTemplateFun> select Custom_Json_Value("",""); Error: Failed to set schema for DB connection for thread [schema=MYTEMPLATEFUNCTION] (state=50000,code=1) java.sql.SQLException: Failed to set schema for DB connection for thread [schema=MYTEMPLATEFUNCTION] at org.apache.ignite.internal.jdbc.thin.JdbcThinConnection.sendRequest(JdbcThinConnection.java:750) at org.apache.ignite.internal.jdbc.thin.JdbcThinStatement.execute0(JdbcThinStatement.java:212) at org.apache.ignite.internal.jdbc.thin.JdbcThinStatement.execute(JdbcThinStatement.java:475) at sqlline.Commands.execute(Commands.java:823) at sqlline.Commands.sql(Commands.java:733) at sqlline.SqlLine.dispatch(SqlLine.java:795) at sqlline.SqlLine.begin(SqlLine.java:668) at sqlline.SqlLine.start(SqlLine.java:373) at sqlline.SqlLine.main(SqlLine.java:265) 0: jdbc:ignite:thin://127.0.0.1/MyTemplateFun> Thanks. -- Sent from: http://apache-ignite-users.70518.x6.nabble.com/ |
|
|
ilya.kasnacheev |
Re: How to extracts value from json string in .Net Ignite
|
|
Hello! Seems to work for me: ~/Downloads/apache-ignite-slim-2.9.0-bin% mkdir -p org/mycustomsqlfunction ~/Downloads/apache-ignite-slim-2.9.0-bin% cd org/mycustomsqlfunction ~/Downloads/apache-ignite-slim-2.9.0-bin/org/mycustomsqlfunction% cat > MyCustomSQLFunction.java package org.mycustomsqlfunction; import org.apache.ignite.cache.query.annotations.QuerySqlFunction; public class MyCustomSQLFunction { @QuerySqlFunction public static String Custom_Json_Value(String payload,String fieldName) { System.out.println(payload); System.out.println(fieldName); return "Test"; } }% ~/Downloads/apache-ignite-slim-2.9.0-bin/org/mycustomsqlfunction% cd ../.. ~/Downloads/apache-ignite-slim-2.9.0-bin% javac -cp libs/ignite-core-2.9.0.jar:libs/ignite-indexing/ignite-indexing-2.9.0.jar:libs/ignite-indexing/h2-1.4.197.jar org/mycustomsqlfunction/MyCustomSQLFunction.java ~/Downloads/apache-ignite-slim-2.9.0-bin% jar vcf libs/mycustomsqlfunction.jar org/mycustomsqlfunction/MyCustomSQLFunction.class org/mycustomsqlfunction/MyCustomSQLFunction.class ~/Downloads/apache-ignite-slim-2.9.0-bin% vim config/default-config.xml ... ~/Downloads/apache-ignite-slim-2.9.0-bin% javac -cp libs/ignite-core-2.9.0.jar:libs/ignite-indexing/ignite-indexing-2.9.0.jar:libs/ignite-indexing/h2-1.4.197.jar org/mycustomsqlfunction/MyCustomSQLFunction.java (130)~/Downloads/apache-ignite-slim-2.9.0-bin% bin/ignite.sh .... ~/Downloads/apache-ignite-slim-2.9.0-bin% bin/sqlline.sh 0: jdbc:ignite:thin://localhost/MyTemplateFun> !connect jdbc:ignite:thin://localhost/"MyTemplateFunction" Enter username for jdbc:ignite:thin://localhost/"MyTemplateFunction": Enter password for jdbc:ignite:thin://localhost/"MyTemplateFunction": 1: jdbc:ignite:thin://localhost/"MyTemplateFu> select custom_json_value('','') foo; +--------------------------------+ | FOO | +--------------------------------+ | Test | +--------------------------------+ 1 row selected (0,108 seconds) Regards, -- Ilya Kasnacheev пн, 21 дек. 2020 г. в 17:39, siva <[hidden email]>: Hi, |
|
|
Hi,
But i am facing same issue. not sure missing any configuration? 1.added jar file in libs folder 2.added function in default config at ignite installation config directory. 3.added function in both client and server nodes xml file. I have restarted my application .Net client and server nodes. 0: jdbc:ignite:thin://127.0.0.1/MyTemplateFun> select Custom_Json_Value("","") foo; Ignite console log: [2020-12-21 20:55:33,918][ERROR][client-connector-#160%ServerNode%][JdbcRequestHandler] Failed to execute SQL query [reqId=0, req=JdbcQueryExecuteRequest [schemaName=MYTEMPLATEFUNCTION, pageSize=1024, maxRows=0, sqlQry=select Custom_Json_Value("","") foo, args=[], stmtType=ANY_STATEMENT_TYPE, autoCommit=true]] class org.apache.ignite.internal.processors.query.IgniteSQLException: Failed to set schema for DB connection for thread [schema=MYTEMPLATEFUNCTION] at org.apache.ignite.internal.processors.query.h2.IgniteH2Indexing.connectionForThread(IgniteH2Indexing.java:587) at org.apache.ignite.internal.processors.query.h2.IgniteH2Indexing.connectionForSchema(IgniteH2Indexing.java:414) at org.apache.ignite.internal.processors.query.h2.IgniteH2Indexing.querySqlFields(IgniteH2Indexing.java:2179) at org.apache.ignite.internal.processors.query.GridQueryProcessor$4.applyx(GridQueryProcessor.java:2128) at org.apache.ignite.internal.processors.query.GridQueryProcessor$4.applyx(GridQueryProcessor.java:2123) at org.apache.ignite.internal.util.lang.IgniteOutClosureX.apply(IgniteOutClosureX.java:36) at org.apache.ignite.internal.processors.query.GridQueryProcessor.executeQuery(GridQueryProcessor.java:2693) at org.apache.ignite.internal.processors.query.GridQueryProcessor.querySqlFields(GridQueryProcessor.java:2137) at org.apache.ignite.internal.processors.odbc.jdbc.JdbcRequestHandler.executeQuery(JdbcRequestHandler.java:511) at org.apache.ignite.internal.processors.odbc.jdbc.JdbcRequestHandler.doHandle(JdbcRequestHandler.java:245) at org.apache.ignite.internal.processors.odbc.jdbc.JdbcRequestHandler.handle(JdbcRequestHandler.java:208) at org.apache.ignite.internal.processors.odbc.ClientListenerNioListener.onMessage(ClientListenerNioListener.java:162) at org.apache.ignite.internal.processors.odbc.ClientListenerNioListener.onMessage(ClientListenerNioListener.java:45) at org.apache.ignite.internal.util.nio.GridNioFilterChain$TailFilter.onMessageReceived(GridNioFilterChain.java:279) at org.apache.ignite.internal.util.nio.GridNioFilterAdapter.proceedMessageReceived(GridNioFilterAdapter.java:109) at org.apache.ignite.internal.util.nio.GridNioAsyncNotifyFilter$3.body(GridNioAsyncNotifyFilter.java:97) at org.apache.ignite.internal.util.worker.GridWorker.run(GridWorker.java:120) at org.apache.ignite.internal.util.worker.GridWorkerPool$1.run(GridWorkerPool.java:70) 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) Caused by: org.h2.jdbc.JdbcSQLException: Schema "MYTEMPLATEFUNCTION" not found; SQL statement: SET SCHEMA "MYTEMPLATEFUNCTION" [90079-197] at org.h2.message.DbException.getJdbcSQLException(DbException.java:357) at org.h2.message.DbException.get(DbException.java:179) at org.h2.message.DbException.get(DbException.java:155) at org.h2.engine.Database.getSchema(Database.java:1808) at org.h2.command.dml.Set.update(Set.java:409) at org.h2.command.CommandContainer.update(CommandContainer.java:102) at org.h2.command.Command.executeUpdate(Command.java:261) at org.h2.jdbc.JdbcStatement.executeUpdateInternal(JdbcStatement.java:169) at org.h2.jdbc.JdbcStatement.executeUpdate(JdbcStatement.java:126) at org.apache.ignite.internal.processors.query.h2.IgniteH2Indexing.connectionForThread(IgniteH2Indexing.java:579) ... 20 more Thanks. -- Sent from: http://apache-ignite-users.70518.x6.nabble.com/ |
|
|
In reply to this post by ilya.kasnacheev
Hi,
I have added custom function jar file and added cache configuration in default config xml and started server using ignite.bat.I am able to query my custom function. 0: jdbc:ignite:thin://127.0.0.1/> SELECT Custom_Json_Value('','') foo; +--------------------------------+ | FOO | +--------------------------------+ | Test | +--------------------------------+ 1 row selected (0.034 seconds) 0: jdbc:ignite:thin://127.0.0.1/> But in .NET Server case it's not working IgniteHome,cacheconfig and libs there in at ignite binary. added same cache configuration in .net server xml file. i have tested create command as well with same cache template but it's not working.what might be the cause? 0: jdbc:ignite:thin://127.0.0.1/> SELECT Custom_Json_Value('','') foo; Error: Failed to parse query. Function "CUSTOM_JSON_VALUE" not found; SQL statement: SELECT Custom_Json_Value('','') foo [90022-197] (state=42000,code=1001) java.sql.SQLException: Failed to parse query. Function "CUSTOM_JSON_VALUE" not found; SQL statement: SELECT Custom_Json_Value('','') foo [90022-197] at org.apache.ignite.internal.jdbc.thin.JdbcThinConnection.sendRequest(JdbcThinConnection.java:750) at org.apache.ignite.internal.jdbc.thin.JdbcThinStatement.execute0(JdbcThinStatement.java:212) at org.apache.ignite.internal.jdbc.thin.JdbcThinStatement.execute(JdbcThinStatement.java:475) at sqlline.Commands.execute(Commands.java:823) at sqlline.Commands.sql(Commands.java:733) at sqlline.SqlLine.dispatch(SqlLine.java:795) at sqlline.SqlLine.begin(SqlLine.java:668) at sqlline.SqlLine.start(SqlLine.java:373) at sqlline.SqlLine.main(SqlLine.java:265) help is very appreciate.thanks. -- Sent from: http://apache-ignite-users.70518.x6.nabble.com/ |
|
|
ilya.kasnacheev |
Re: How to extracts value from json string in .Net Ignite
|
|
Hello! I think you need to specify your JAR file with the -JvmClasspath argument of ignite.exe or corresponding IgniteConfiguration class property. Regards, -- Ilya Kasnacheev вт, 22 дек. 2020 г. в 11:03, siva <[hidden email]>: Hi, |
|
|
Thank you so much for help .
After running by exe and passing arguments it is working in .Net server. But Function is working after creating at least on one table with cache template of function.is this should work like that? I am not sure but If you don't mind just for clarification Actually i want apply that custom function cache template in all caches using code.How to apply same function cache template on this below cache config? var cacheCfg = new CacheConfiguration("MyTemplate*") { Name = "MyTemplate", CacheStoreFactory = new ConstructionTenantCacheStoreFactory(_logger, connectionString), KeepBinaryInStore = false, // Cache store works with deserialized data. ReadThrough = true, WriteThrough = true, WriteBehindEnabled = true, QueryEntities = queryList, WriteBehindFlushThreadCount = 2, CacheMode = CacheMode.Partitioned, Backups = backupNodes, DataRegionName = "IgniteDataRegion", EvictionPolicy = new LruEvictionPolicy { MaxSize = 100000 }, EnableStatistics = cacheMetrics, WriteSynchronizationMode = CacheWriteSynchronizationMode.FullSync, GroupName=groupName }; var cache = Ignite.GetOrCreateCache<string,object>(cacheCfg); Thanks. -- Sent from: http://apache-ignite-users.70518.x6.nabble.com/ |
|
|
Hello,
This link might help you, http://apache-ignite-users.70518.x6.nabble.com/ignite-net-custom-sql-functions-td27190.html -- Sent from: http://apache-ignite-users.70518.x6.nabble.com/ |
|
|
Hi,
Thank you all for help. created function and deployed in .net nodes. But i am not getting why Function is working only after creating at least on one table with cache template of function class. is this should work like that? or something i missing? Thanks. -- Sent from: http://apache-ignite-users.70518.x6.nabble.com/ |
|
|
ilya.kasnacheev |
Re: How to extracts value from json string in .Net Ignite
|
|
Hello! Yes, it should work like that. Templates don't do anything until you create a cache with them. You can also create a simple empty table instead of putting it in a template. Regards, -- Ilya Kasnacheev чт, 24 дек. 2020 г. в 12:03, siva <[hidden email]>: Hi, |
«
Return to Apache Ignite Users
|
1 view|%1 views
| Free forum by Nabble | Edit this page |