How to insert data?

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

How to insert data?

How can I insert data by sqlline.bat?
I use class DataX:
public class DataX {
        private Integer key;
        private String value;
        public DataX(int key,String value) {
                this.key=key;
                this.value=value;
        }
        public int getKey() {
                return key;
        }
        public void setKey(int key) {
                this.key=key;
        }
        public String getValue() {
                return value;
        }
        public void setValue(String value) {
                this.value=value;
        }
}

and a cache named dtCache:
IgniteCache<Integer, DataX> dtCache=ignite.getOrCreateCache("dtCache");



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

Re: How to insert data?

You need to tell the SQL engine about your POJO. There are number of ways of doing that, but one example would be:
val cacheConfiguration = new CacheConfiguration[Integer,DataX]()
val valQE = new QueryEntity()
valQE.setKeyFieldName("key")
valQE.setKeyType("java.lang.Integer")
valQE.setValueType("DataX")
valQE.addQueryField("key", "java.lang.Integer", "key")
valQE.addQueryField("value","java.lang.String","value")
cacheConfiguration.setQueryEntities(java.util.Arrays.asList(valQE))
cacheConfiguration.setName("tdCache")
val dtCache = ignite.getOrCreateCache(cacheConfiguration)
Then the SQL will work:

0: jdbc:ignite:<a href="thin://127.0.0.1" class="">thin://127.0.0.1> select * from "tdCache".DATAX;
+--------------------------------+--------------------------------+
|              KEY               |             VALUE              |
+--------------------------------+--------------------------------+
| 1                              | Stephen                        |
+--------------------------------+--------------------------------+
1 row selected (0.02 seconds)
0: jdbc:ignite:<a href="thin://127.0.0.1" class="">thin://127.0.0.1> 


You can also use annotations, define this in XML or use a SQL CREATE TABLE statement.

Regards,
Stephen

On 4 Nov 2019, at 01:35, BorisBelozerov <[hidden email]> wrote:

How can I insert data by sqlline.bat?
I use class DataX:
public class DataX {
private Integer key;
private String value;
public DataX(int key,String value) {
this.key=key;
this.value=value;
}
public int getKey() {
return key;
}
public void setKey(int key) {
this.key=key;
}
public String getValue() {
return value;
}
public void setValue(String value) {
this.value=value;
}
}

and a cache named dtCache:
IgniteCache<Integer, DataX> dtCache=ignite.getOrCreateCache("dtCache");



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


BorisBelozerov BorisBelozerov
Reply | Threaded
Open this post in threaded view
|

Re: How to insert data?

Thank you!!
How many nodes that I run your code??
I only run the "CREATE database" code in one node or all nodes??



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

Re: How to insert data?

One. The cache is cluster-wide, so once it’s created every node can see it.

> On 5 Nov 2019, at 12:36, BorisBelozerov <[hidden email]> wrote:
>
> Thank you!!
> How many nodes that I run your code??
> I only run the "CREATE database" code in one node or all nodes??
>
>
>
> --
> Sent from: http://apache-ignite-users.70518.x6.nabble.com/


BorisBelozerov BorisBelozerov
Reply | Threaded
Open this post in threaded view
|

Re: How to insert data?

val cacheConfiguration = new CacheConfiguration[Integer,DataX]()
val valQE = new QueryEntity()
valQE.setKeyFieldName("key")
valQE.setKeyType("java.lang.Integer")
valQE.setValueType("DataX")
valQE.addQueryField("key", "java.lang.Integer", "key")
valQE.addQueryField("value","java.lang.String","value")
cacheConfiguration.setQueryEntities(java.util.Arrays.asList(valQE))
cacheConfiguration.setName("tdCache")
val dtCache = ignite.getOrCreateCache(cacheConfiguration)

Your code is not seem in Eclipse, can you give me some code that can run in
file .java, in Eclipse?
Thank you!!




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

Re: How to insert data?

In reply to this post by stephendarlington
I have 3 nodes, and I code in each node:
The 1st node: in Main function
                Ignite ignite=Ignition.start();
                CacheConfiguration<Integer, DataX> cacheConfiguration = new
CacheConfiguration<Integer,DataX>();
                QueryEntity valQE = new QueryEntity();
                valQE.setKeyFieldName("key");
                valQE.setKeyType("java.lang.Integer");
                valQE.setValueType("DataX");
                valQE.addQueryField("key", "java.lang.Integer", "key");
                valQE.addQueryField("value","java.lang.String","value");
                cacheConfiguration.setQueryEntities(java.util.Arrays.asList(valQE));
                cacheConfiguration.setName("tdCache");
                IgniteCache<Integer, DataX> dtCache =
ignite.getOrCreateCache(cacheConfiguration);
The 2nd and 3rd node:
                Ignite ignite=Ignition.start();
I open ignitevisorcmd.bat but I can't see DataX or tdCache
I use sqlline.bat: select * from "tdCache".DATAX; but I can't select it:
Schema "tdCache" not found
Can you help me?? Thank you



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

Re: How to insert data?

There are multiple ways to configure a cache to use SQL. The easiest is to use @QuerySqlField annotation. Check out this doc https://www.gridgain.com/docs/8.7.6/developers-guide/SQL/sql-api#querysqlfield-annotation.

On Tue, Nov 5, 2019 at 5:52 PM BorisBelozerov <[hidden email]> wrote:
I have 3 nodes, and I code in each node:
The 1st node: in Main function
                Ignite ignite=Ignition.start();
                CacheConfiguration<Integer, DataX> cacheConfiguration = new
CacheConfiguration<Integer,DataX>();
                QueryEntity valQE = new QueryEntity();
                valQE.setKeyFieldName("key");
                valQE.setKeyType("java.lang.Integer");
                valQE.setValueType("DataX");
                valQE.addQueryField("key", "java.lang.Integer", "key");
                valQE.addQueryField("value","java.lang.String","value");
                cacheConfiguration.setQueryEntities(java.util.Arrays.asList(valQE));
                cacheConfiguration.setName("tdCache");
                IgniteCache<Integer, DataX> dtCache =
ignite.getOrCreateCache(cacheConfiguration);
The 2nd and 3rd node:
                Ignite ignite=Ignition.start();
I open ignitevisorcmd.bat but I can't see DataX or tdCache
I use sqlline.bat: select * from "tdCache".DATAX; but I can't select it:
Schema "tdCache" not found
Can you help me?? Thank you



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

Re: How to insert data?

In reply to this post by BorisBelozerov
What tables do you see when you type “!tables” into sqlline?

> On 5 Nov 2019, at 15:52, BorisBelozerov <[hidden email]> wrote:
>
> I have 3 nodes, and I code in each node:
> The 1st node: in Main function
> Ignite ignite=Ignition.start();
> CacheConfiguration<Integer, DataX> cacheConfiguration = new
> CacheConfiguration<Integer,DataX>();
> QueryEntity valQE = new QueryEntity();
> valQE.setKeyFieldName("key");
> valQE.setKeyType("java.lang.Integer");
> valQE.setValueType("DataX");
> valQE.addQueryField("key", "java.lang.Integer", "key");
> valQE.addQueryField("value","java.lang.String","value");
> cacheConfiguration.setQueryEntities(java.util.Arrays.asList(valQE));
> cacheConfiguration.setName("tdCache");
> IgniteCache<Integer, DataX> dtCache =
> ignite.getOrCreateCache(cacheConfiguration);
> The 2nd and 3rd node:
>                Ignite ignite=Ignition.start();
> I open ignitevisorcmd.bat but I can't see DataX or tdCache
> I use sqlline.bat: select * from "tdCache".DATAX; but I can't select it:
> Schema "tdCache" not found
> Can you help me?? Thank you
>
>
>
> --
> Sent from: http://apache-ignite-users.70518.x6.nabble.com/


BorisBelozerov BorisBelozerov
Reply | Threaded
Open this post in threaded view
|

Re: How to insert data?

When I run "!tables", there isn't any tables or schemas



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

Re: How to insert data?

In reply to this post by stephendarlington
Now I can show table DATAX by run "!tables"
But when I insert data, it can't run
In Eclipse, the error is: Key is missing from query
In Sqlline, the error is: Failed to execute DML statement [stmt=insert into
"tdCache".DATAX (key,value) values (1,'value 1');]




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

Re: How to insert data?

In reply to this post by stephendarlington
When I run your code in one node, It run OK
But when I run your code in a cluster, It can't run. I can select but can't
insert or do other things



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

Re: How to insert data?

Hi Boris,

Perhaps confusing point here is that Ignite separates key and value
parts in KV storage. And there is always underlying KV storage in
Ignite. So you cannot have cache key and value in a single POJO. I
tried your class with annotations and run INSERTs with it. A trick
here is a "_key" column.
class DataX {
    @QuerySqlField
    private Integer key;
    @QuerySqlField
    private String value;
    public DataX(int key,String value) {
        this.key=key;
        this.value=value;
    }
    public int getKey() {
        return key;
    }
    public void setKey(int key) {
        this.key=key;
    }
    public String getValue() {
        return value;
    }
    public void setValue(String value) {
        this.value=value;
    }
}

IgniteCache<Object, Object> cache = ignite.createCache(new
CacheConfiguration<>(DEFAULT_CACHE_NAME)
    .setIndexedTypes(Integer.class, DataX.class));

cache.query(new SqlFieldsQuery("insert into DataX(_key, key, value)
values(1, 42, 'value42')"));

System.err.println(cache.query(new SqlFieldsQuery("select * from
DataX")).getAll());

ср, 6 нояб. 2019 г. в 11:46, BorisBelozerov <[hidden email]>:
>
> When I run your code in one node, It run OK
> But when I run your code in a cluster, It can't run. I can select but can't
> insert or do other things
>
>
>
> --
> Sent from: http://apache-ignite-users.70518.x6.nabble.com/



--
Best regards,
Ivan Pavlukhin