You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@ignite.apache.org by "Williams, Michael" <mi...@transamerica.com> on 2018/02/08 23:48:00 UTC
Cat Example
Hi,
Quick question, submitted a ticket earlier. How would I modify the below code such that, when viewed through Sql (dbeaver, eg) it behaves as if it had been created through a CREATE TABLE statement, where the name of the table was catCache? I'm trying to directly populate a series of tables that will be used downstream primarily through SQL. I'd like to be able to go into dBeaver, browse the tables, and see 10 cats named Fluffy, if this is working correctly.
import org.apache.ignite.cache.query.annotations.*;
import java.io.*;
public class Cat implements Serializable {
@QuerySqlField
int legs;
@QuerySqlField
String name;
Cat(int l, String n)
{
legs = l;
name = n;
}
}
import org.apache.ignite.Ignition;
import org.apache.ignite.Ignite;
import org.apache.ignite.IgniteCache;
import org.apache.ignite.cache.CacheMode;
import org.apache.ignite.configuration.CacheConfiguration;
import org.apache.ignite.cache.query.SqlFieldsQuery;
import org.apache.ignite.cache.query.QueryCursor;
import java.util.List;
public class Test {
public static void main(String[] args)
{
Ignite ignite = Ignition.start();
CacheConfiguration<Integer,Cat> cfg= new CacheConfiguration("catCache");
cfg.setCacheMode(CacheMode.REPLICATED);
cfg.setSqlEscapeAll(true);
cfg.setSqlSchema("PUBLIC");
cfg.setIndexedTypes(Integer.class,Cat.class);
try(IgniteCache<Integer, Cat> cache = ignite.getOrCreateCache(cfg))
{
for (int i = 0; i < 10; ++i) {
cache.put(i, new Cat(i + 1,"Fluffy"));
}/*
SqlFieldsQuery sql = new SqlFieldsQuery("select * from catCache");
try (QueryCursor<List<?>> cursor = cache.query(sql)) {
for (List<?> row : cursor)
System.out.println("cat=" + row.get(0));
}*/
}
System.out.print("Got It!");
}}
Thanks,
Mike Williams
Re: Cat Example
Posted by Amir Akhmedov <am...@gmail.com>.
I ran your code and connected to Ignite through dBeaver and it shows single
DOG table. I think it's some glitch with your dBeaver instance. In general,
you can run H2 Console by enabling IGNITE_H2_DEBUG_CONSOLE JVM parameter
and check over there.
FYI, Ignite supports C++[1] and .NET[2] platforms, just in case :)
[1] https://apacheignite-cpp.readme.io/docs
[2] https://apacheignite-net.readme.io/docs/
On Fri, Feb 9, 2018 at 5:15 PM, Williams, Michael <
michael.williams@transamerica.com> wrote:
> So this seems to be the closest I can get to something that looks like
> DML – this does work, and the only odd thing is that table DOG shows up
> twice in dBeaver, which I can’t quite figure out. Is this the right way to
> go about getting data in? Sorry, I’m just getting started with Ignite and
> my background is more C++ than Java.
>
>
>
> Cat.java:
>
>
>
>
>
> import org.apache.ignite.cache.query.annotations.*;
>
>
>
> import java.io.*;
>
>
>
> public class Cat implements Serializable {
>
> int legs;
>
> String name;
>
>
>
> Cat(int l, String n)
>
> {
>
> legs = l;
>
> name = n;
>
> }
>
> Cat()
>
> {
>
> legs = 0;
>
> name = "";
>
> }
>
>
>
> }
>
>
>
> Main Class:
>
>
>
>
>
> import org.apache.ignite.IgniteDataStreamer;
>
> import org.apache.ignite.Ignition;
>
> import org.apache.ignite.Ignite;
>
> import org.apache.ignite.IgniteCache;
>
> import org.apache.ignite.cache.CacheMode;
>
> import org.apache.ignite.cache.QueryEntity;
>
> import org.apache.ignite.cache.QueryIndex;
>
> import org.apache.ignite.configuration.CacheConfiguration;
>
> import org.apache.ignite.cache.query.SqlFieldsQuery;
>
> import org.apache.ignite.cache.query.QueryCursor;
>
>
>
> import java.util.*;
>
>
>
> public class Test {
>
> public static void main(String[] args)
>
> {
>
>
>
> Ignite ignite = Ignition.start();
>
> CacheConfiguration<Integer,Cat> cfg= new
> CacheConfiguration("CAT");
>
> cfg.setCacheMode(CacheMode.REPLICATED);
>
> cfg.setSqlEscapeAll(true);
>
> //cfg.setIndexedTypes(Integer.class,Cat.class);
>
> QueryEntity qe = new QueryEntity();
>
> qe.setKeyType(Integer.class.getName());
>
> qe.setValueType(Cat.class.getName());
>
> LinkedHashMap<String, String> fields = new LinkedHashMap();
>
> fields.put("legs",Integer.class.getName());
>
> fields.put("name",String.class.getName());
>
> qe.setFields(fields);
>
> Collection<QueryIndex> indexes = new ArrayList<>(1);
>
> indexes.add(new QueryIndex("legs"));
>
> qe.setIndexes(indexes);
>
> qe.setTableName("DOG");
>
> cfg.setQueryEntities(Arrays.asList(qe));
>
> cfg.setSqlSchema("PUBLIC");
>
>
>
> try(IgniteCache<Integer, Cat> cache =
> ignite.getOrCreateCache(cfg))
>
> {
>
> try (IgniteDataStreamer<Integer, Cat> stmr =
> ignite.dataStreamer("CAT")) {
>
> for (int i = 0; i < 1_000_000; i++)
>
> stmr.addData(i, new Cat(i+1,"Fluffy"));
>
> stmr.flush();
>
> }
>
>
>
> SqlFieldsQuery sql = new SqlFieldsQuery("select * from DOG
> LIMIT 10");
>
> try (QueryCursor<List<?>> cursor = cache.query(sql)) {
>
> for (List<?> row : cursor)
>
> System.out.println("cat=" + row.get(0));
>
> }
>
> }
>
> System.out.print("UP!");
>
> }}
>
>
>
> *From:* Amir Akhmedov [mailto:amir.akhmedov@gmail.com]
> *Sent:* Friday, February 09, 2018 5:08 PM
>
> *To:* user@ignite.apache.org
> *Subject:* Re: Cat Example
>
>
>
> Hi Mike,
>
> As of today Ignite DML does not support transactions and every DML
> statement is executed as atomic (Igniters, please correct me if I'm wrong).
>
> But still you can use DataStreamer[1] to improve data loading. Or you can
> use Ignite's Cache API with batch operations like putAll.
>
> [1] https://apacheignite.readme.io/docs/data-streamers
> <https://urldefense.proofpoint.com/v2/url?u=https-3A__apacheignite.readme.io_docs_data-2Dstreamers&d=DwMFaQ&c=9g4MJkl2VjLjS6R4ei18BA&r=ipRRuqPnuP3BWnXGSOR_sLoARpltax56uFYU6n57c3GFvMdyEV-dz2ez2lZZpYl0&m=66Y7rAlRA_17xECa5FjIVnychzFgOU57cX95e0xK8Uw&s=QhZ2-mbmH1uyk6d0ECo3A_ghgTh1iNv1YQUGMYYx_NY&e=>
>
>
>
> On Fri, Feb 9, 2018 at 2:56 PM, Williams, Michael <michael.williams@
> transamerica.com> wrote:
>
> Is it possible to stream data into a table created by a query? For
> example, consider the following modified example. If I had a Person object,
> how would I replace the insert loop to improve speed?
>
>
>
> Thanks,
>
> Mike
>
>
>
> import org.apache.ignite.Ignite;
>
> import org.apache.ignite.IgniteCache;
>
> import org.apache.ignite.Ignition;
>
> import org.apache.ignite.cache.query.SqlFieldsQuery;
>
> import org.apache.ignite.configuration.CacheConfiguration;
>
>
>
> public class Test {
>
> private static final String DUMMY_CACHE_NAME = "dummy_cache";
>
> public static void main(String[] args)
>
> {
>
> Ignite ignite = Ignition.start();
>
> {
>
>
>
> // Create dummy cache to act as an entry point for SQL queries
> (new SQL API which do not require this
>
> // will appear in future versions, JDBC and ODBC drivers do
> not require it already).
>
> CacheConfiguration<?, ?> cacheCfg = new
> CacheConfiguration<>(DUMMY_CACHE_NAME).setSqlSchema("PUBLIC");
>
> try (IgniteCache<?, ?> cache = ignite.getOrCreateCache(
> cacheCfg))
>
> {
>
> // Create reference City table based on REPLICATED
> template.
>
> cache.query(new SqlFieldsQuery("CREATE TABLE city (id LONG
> PRIMARY KEY, name VARCHAR) WITH \"template=replicated\"")).getAll();
>
> // Create table based on PARTITIONED template with one
> backup.
>
> cache.query(new SqlFieldsQuery("CREATE TABLE person (id
> LONG, name VARCHAR, city_id LONG, PRIMARY KEY (id, city_id)) WITH
> \"template=replicated\"")).getAll();
>
>
>
> // Create an index.
>
> cache.query(new SqlFieldsQuery("CREATE INDEX on Person
> (city_id)")).getAll();
>
>
>
> SqlFieldsQuery qry = new SqlFieldsQuery("INSERT INTO city
> (id, name) VALUES (?, ?)");
>
>
>
> cache.query(qry.setArgs(1L, "Forest Hill")).getAll();
>
> cache.query(qry.setArgs(2L, "Denver")).getAll();
>
> cache.query(qry.setArgs(3L, "St. Petersburg")).getAll();
>
>
>
> qry = new SqlFieldsQuery("INSERT INTO person (id, name,
> city_id) values (?, ?, ?)");
>
> for(long i = 0; i < 100_000;++i)
>
> {
>
> cache.query(qry.setArgs(i, "John Doe", 3L)).getAll();
>
> }
>
> System.out.print("HI!");
>
> }}}}
>
>
>
>
>
>
>
> *From:* Denis Magda [mailto:dmagda@apache.org]
> *Sent:* Thursday, February 08, 2018 7:33 PM
> *To:* user@ignite.apache.org
> *Subject:* Re: Cat Example
>
>
>
> Hi Mike,
>
>
>
> If SQL indexes/configuration is set with the annotation and
> setIndexedTypes method then you have to use the type name (Cat in your
> case) as the SQL table name. It’s explained here:
>
> https://apacheignite-sql.readme.io/docs/schema-and-
> indexes#section-annotation-based-configuration
> <https://urldefense.proofpoint.com/v2/url?u=https-3A__apacheignite-2Dsql.readme.io_docs_schema-2Dand-2Dindexes-23section-2Dannotation-2Dbased-2Dconfiguration&d=DwMFaQ&c=9g4MJkl2VjLjS6R4ei18BA&r=ipRRuqPnuP3BWnXGSOR_sLoARpltax56uFYU6n57c3GFvMdyEV-dz2ez2lZZpYl0&m=Nrv7gL0zYS9bFUir2zKHmI30_jJzYTzlic8Vk0kWonQ&s=UZWx1Uz419uytrzu-xRfSiuRqc3rszCQr5rJqNjT8TI&e=>
>
>
>
> The cache name is used for IgniteCache APIs and other related methods.
>
>
>
> —
>
> Denis
>
>
>
> On Feb 8, 2018, at 3:48 PM, Williams, Michael <michael.williams@
> transamerica.com> wrote:
>
>
>
> Hi,
>
>
>
> Quick question, submitted a ticket earlier. How would I modify the below
> code such that, when viewed through Sql (dbeaver, eg) it behaves as if it
> had been created through a CREATE TABLE statement, where the name of the
> table was catCache? I’m trying to directly populate a series of tables that
> will be used downstream primarily through SQL. I’d like to be able to go
> into dBeaver, browse the tables, and see 10 cats named Fluffy, if this is
> working correctly.
>
> import org.apache.ignite.cache.query.annotations.*;
>
> import java.io
> <https://urldefense.proofpoint.com/v2/url?u=http-3A__java.io_&d=DwMFaQ&c=9g4MJkl2VjLjS6R4ei18BA&r=ipRRuqPnuP3BWnXGSOR_sLoARpltax56uFYU6n57c3GFvMdyEV-dz2ez2lZZpYl0&m=Nrv7gL0zYS9bFUir2zKHmI30_jJzYTzlic8Vk0kWonQ&s=oFi3ym9G_GwhhFWtyRu--rylVH_5Vs2w_77tNCIb1Ls&e=>
> .*;
>
>
>
> public class Cat implements Serializable {
>
> @QuerySqlField
>
> int legs;
>
> @QuerySqlField
>
> String name;
>
>
>
> Cat(int l, String n)
>
> {
>
> legs = l;
>
> name = n;
>
> }
>
> }
>
>
>
>
>
> import org.apache.ignite.Ignition;
>
> import org.apache.ignite.Ignite;
>
> import org.apache.ignite.IgniteCache;
>
> import org.apache.ignite.cache.CacheMode;
>
> import org.apache.ignite.configuration.CacheConfiguration;
>
> import org.apache.ignite.cache.query.SqlFieldsQuery;
>
> import org.apache.ignite.cache.query.QueryCursor;
>
> import java.util.List;
>
> public class Test {
>
> public static void main(String[] args)
>
> {
>
>
>
> Ignite ignite = Ignition.start();
>
> CacheConfiguration<Integer,Cat> cfg= new
> CacheConfiguration("catCache");
>
>
>
> cfg.setCacheMode(CacheMode.REPLICATED);
>
> cfg.setSqlEscapeAll(true);
>
> cfg.setSqlSchema("PUBLIC");
>
> cfg.setIndexedTypes(Integer.class,Cat.class);
>
> try(IgniteCache<Integer, Cat> cache =
> ignite.getOrCreateCache(cfg))
>
> {
>
> for (int i = 0; i < 10; ++i) {
>
> cache.put(i, new Cat(i + 1,"Fluffy"));
>
> }/*
>
> SqlFieldsQuery sql = new SqlFieldsQuery("select * from
> catCache");
>
> try (QueryCursor<List<?>> cursor = cache.query(sql)) {
>
> for (List<?> row : cursor)
>
> System.out.println("cat=" + row.get(0));
>
> }*/
>
> }
>
> System.out.print("Got It!");
>
>
>
> }}
>
> Thanks,
>
> *Mike Williams*
>
>
>
>
>
>
> --
>
> Sincerely Yours Amir Akhmedov
>
--
Sincerely Yours Amir Akhmedov
RE: Cat Example
Posted by "Williams, Michael" <mi...@transamerica.com>.
So this seems to be the closest I can get to something that looks like DML – this does work, and the only odd thing is that table DOG shows up twice in dBeaver, which I can’t quite figure out. Is this the right way to go about getting data in? Sorry, I’m just getting started with Ignite and my background is more C++ than Java.
Cat.java:
import org.apache.ignite.cache.query.annotations.*;
import java.io.*;
public class Cat implements Serializable {
int legs;
String name;
Cat(int l, String n)
{
legs = l;
name = n;
}
Cat()
{
legs = 0;
name = "";
}
}
Main Class:
import org.apache.ignite.IgniteDataStreamer;
import org.apache.ignite.Ignition;
import org.apache.ignite.Ignite;
import org.apache.ignite.IgniteCache;
import org.apache.ignite.cache.CacheMode;
import org.apache.ignite.cache.QueryEntity;
import org.apache.ignite.cache.QueryIndex;
import org.apache.ignite.configuration.CacheConfiguration;
import org.apache.ignite.cache.query.SqlFieldsQuery;
import org.apache.ignite.cache.query.QueryCursor;
import java.util.*;
public class Test {
public static void main(String[] args)
{
Ignite ignite = Ignition.start();
CacheConfiguration<Integer,Cat> cfg= new CacheConfiguration("CAT");
cfg.setCacheMode(CacheMode.REPLICATED);
cfg.setSqlEscapeAll(true);
//cfg.setIndexedTypes(Integer.class,Cat.class);
QueryEntity qe = new QueryEntity();
qe.setKeyType(Integer.class.getName());
qe.setValueType(Cat.class.getName());
LinkedHashMap<String, String> fields = new LinkedHashMap();
fields.put("legs",Integer.class.getName());
fields.put("name",String.class.getName());
qe.setFields(fields);
Collection<QueryIndex> indexes = new ArrayList<>(1);
indexes.add(new QueryIndex("legs"));
qe.setIndexes(indexes);
qe.setTableName("DOG");
cfg.setQueryEntities(Arrays.asList(qe));
cfg.setSqlSchema("PUBLIC");
try(IgniteCache<Integer, Cat> cache = ignite.getOrCreateCache(cfg))
{
try (IgniteDataStreamer<Integer, Cat> stmr = ignite.dataStreamer("CAT")) {
for (int i = 0; i < 1_000_000; i++)
stmr.addData(i, new Cat(i+1,"Fluffy"));
stmr.flush();
}
SqlFieldsQuery sql = new SqlFieldsQuery("select * from DOG LIMIT 10");
try (QueryCursor<List<?>> cursor = cache.query(sql)) {
for (List<?> row : cursor)
System.out.println("cat=" + row.get(0));
}
}
System.out.print("UP!");
}}
From: Amir Akhmedov [mailto:amir.akhmedov@gmail.com]
Sent: Friday, February 09, 2018 5:08 PM
To: user@ignite.apache.org
Subject: Re: Cat Example
Hi Mike,
As of today Ignite DML does not support transactions and every DML statement is executed as atomic (Igniters, please correct me if I'm wrong).
But still you can use DataStreamer[1] to improve data loading. Or you can use Ignite's Cache API with batch operations like putAll.
[1] https://apacheignite.readme.io/docs/data-streamers<https://urldefense.proofpoint.com/v2/url?u=https-3A__apacheignite.readme.io_docs_data-2Dstreamers&d=DwMFaQ&c=9g4MJkl2VjLjS6R4ei18BA&r=ipRRuqPnuP3BWnXGSOR_sLoARpltax56uFYU6n57c3GFvMdyEV-dz2ez2lZZpYl0&m=66Y7rAlRA_17xECa5FjIVnychzFgOU57cX95e0xK8Uw&s=QhZ2-mbmH1uyk6d0ECo3A_ghgTh1iNv1YQUGMYYx_NY&e=>
On Fri, Feb 9, 2018 at 2:56 PM, Williams, Michael <mi...@transamerica.com>> wrote:
Is it possible to stream data into a table created by a query? For example, consider the following modified example. If I had a Person object, how would I replace the insert loop to improve speed?
Thanks,
Mike
import org.apache.ignite.Ignite;
import org.apache.ignite.IgniteCache;
import org.apache.ignite.Ignition;
import org.apache.ignite.cache.query.SqlFieldsQuery;
import org.apache.ignite.configuration.CacheConfiguration;
public class Test {
private static final String DUMMY_CACHE_NAME = "dummy_cache";
public static void main(String[] args)
{
Ignite ignite = Ignition.start();
{
// Create dummy cache to act as an entry point for SQL queries (new SQL API which do not require this
// will appear in future versions, JDBC and ODBC drivers do not require it already).
CacheConfiguration<?, ?> cacheCfg = new CacheConfiguration<>(DUMMY_CACHE_NAME).setSqlSchema("PUBLIC");
try (IgniteCache<?, ?> cache = ignite.getOrCreateCache(cacheCfg))
{
// Create reference City table based on REPLICATED template.
cache.query(new SqlFieldsQuery("CREATE TABLE city (id LONG PRIMARY KEY, name VARCHAR) WITH \"template=replicated\"")).getAll();
// Create table based on PARTITIONED template with one backup.
cache.query(new SqlFieldsQuery("CREATE TABLE person (id LONG, name VARCHAR, city_id LONG, PRIMARY KEY (id, city_id)) WITH \"template=replicated\"")).getAll();
// Create an index.
cache.query(new SqlFieldsQuery("CREATE INDEX on Person (city_id)")).getAll();
SqlFieldsQuery qry = new SqlFieldsQuery("INSERT INTO city (id, name) VALUES (?, ?)");
cache.query(qry.setArgs(1L, "Forest Hill")).getAll();
cache.query(qry.setArgs(2L, "Denver")).getAll();
cache.query(qry.setArgs(3L, "St. Petersburg")).getAll();
qry = new SqlFieldsQuery("INSERT INTO person (id, name, city_id) values (?, ?, ?)");
for(long i = 0; i < 100_000;++i)
{
cache.query(qry.setArgs(i, "John Doe", 3L)).getAll();
}
System.out.print("HI!");
}}}}
From: Denis Magda [mailto:dmagda@apache.org<ma...@apache.org>]
Sent: Thursday, February 08, 2018 7:33 PM
To: user@ignite.apache.org<ma...@ignite.apache.org>
Subject: Re: Cat Example
Hi Mike,
If SQL indexes/configuration is set with the annotation and setIndexedTypes method then you have to use the type name (Cat in your case) as the SQL table name. It’s explained here:
https://apacheignite-sql.readme.io/docs/schema-and-indexes#section-annotation-based-configuration<https://urldefense.proofpoint.com/v2/url?u=https-3A__apacheignite-2Dsql.readme.io_docs_schema-2Dand-2Dindexes-23section-2Dannotation-2Dbased-2Dconfiguration&d=DwMFaQ&c=9g4MJkl2VjLjS6R4ei18BA&r=ipRRuqPnuP3BWnXGSOR_sLoARpltax56uFYU6n57c3GFvMdyEV-dz2ez2lZZpYl0&m=Nrv7gL0zYS9bFUir2zKHmI30_jJzYTzlic8Vk0kWonQ&s=UZWx1Uz419uytrzu-xRfSiuRqc3rszCQr5rJqNjT8TI&e=>
The cache name is used for IgniteCache APIs and other related methods.
—
Denis
On Feb 8, 2018, at 3:48 PM, Williams, Michael <mi...@transamerica.com>> wrote:
Hi,
Quick question, submitted a ticket earlier. How would I modify the below code such that, when viewed through Sql (dbeaver, eg) it behaves as if it had been created through a CREATE TABLE statement, where the name of the table was catCache? I’m trying to directly populate a series of tables that will be used downstream primarily through SQL. I’d like to be able to go into dBeaver, browse the tables, and see 10 cats named Fluffy, if this is working correctly.
import org.apache.ignite.cache.query.annotations.*;
import java.io<https://urldefense.proofpoint.com/v2/url?u=http-3A__java.io_&d=DwMFaQ&c=9g4MJkl2VjLjS6R4ei18BA&r=ipRRuqPnuP3BWnXGSOR_sLoARpltax56uFYU6n57c3GFvMdyEV-dz2ez2lZZpYl0&m=Nrv7gL0zYS9bFUir2zKHmI30_jJzYTzlic8Vk0kWonQ&s=oFi3ym9G_GwhhFWtyRu--rylVH_5Vs2w_77tNCIb1Ls&e=>.*;
public class Cat implements Serializable {
@QuerySqlField
int legs;
@QuerySqlField
String name;
Cat(int l, String n)
{
legs = l;
name = n;
}
}
import org.apache.ignite.Ignition;
import org.apache.ignite.Ignite;
import org.apache.ignite.IgniteCache;
import org.apache.ignite.cache.CacheMode;
import org.apache.ignite.configuration.CacheConfiguration;
import org.apache.ignite.cache.query.SqlFieldsQuery;
import org.apache.ignite.cache.query.QueryCursor;
import java.util.List;
public class Test {
public static void main(String[] args)
{
Ignite ignite = Ignition.start();
CacheConfiguration<Integer,Cat> cfg= new CacheConfiguration("catCache");
cfg.setCacheMode(CacheMode.REPLICATED);
cfg.setSqlEscapeAll(true);
cfg.setSqlSchema("PUBLIC");
cfg.setIndexedTypes(Integer.class,Cat.class);
try(IgniteCache<Integer, Cat> cache = ignite.getOrCreateCache(cfg))
{
for (int i = 0; i < 10; ++i) {
cache.put(i, new Cat(i + 1,"Fluffy"));
}/*
SqlFieldsQuery sql = new SqlFieldsQuery("select * from catCache");
try (QueryCursor<List<?>> cursor = cache.query(sql)) {
for (List<?> row : cursor)
System.out.println("cat=" + row.get(0));
}*/
}
System.out.print("Got It!");
}}
Thanks,
Mike Williams
--
Sincerely Yours Amir Akhmedov
Re: Cat Example
Posted by Amir Akhmedov <am...@gmail.com>.
Hi Mike,
As of today Ignite DML does not support transactions and every DML
statement is executed as atomic (Igniters, please correct me if I'm wrong).
But still you can use DataStreamer[1] to improve data loading. Or you can
use Ignite's Cache API with batch operations like putAll.
[1] https://apacheignite.readme.io/docs/data-streamers
On Fri, Feb 9, 2018 at 2:56 PM, Williams, Michael <
michael.williams@transamerica.com> wrote:
> Is it possible to stream data into a table created by a query? For
> example, consider the following modified example. If I had a Person object,
> how would I replace the insert loop to improve speed?
>
>
>
> Thanks,
>
> Mike
>
>
>
> import org.apache.ignite.Ignite;
>
> import org.apache.ignite.IgniteCache;
>
> import org.apache.ignite.Ignition;
>
> import org.apache.ignite.cache.query.SqlFieldsQuery;
>
> import org.apache.ignite.configuration.CacheConfiguration;
>
>
>
> public class Test {
>
> private static final String DUMMY_CACHE_NAME = "dummy_cache";
>
> public static void main(String[] args)
>
> {
>
> Ignite ignite = Ignition.start();
>
> {
>
>
>
> // Create dummy cache to act as an entry point for SQL queries
> (new SQL API which do not require this
>
> // will appear in future versions, JDBC and ODBC drivers do
> not require it already).
>
> CacheConfiguration<?, ?> cacheCfg = new
> CacheConfiguration<>(DUMMY_CACHE_NAME).setSqlSchema("PUBLIC");
>
> try (IgniteCache<?, ?> cache = ignite.getOrCreateCache(
> cacheCfg))
>
> {
>
> // Create reference City table based on REPLICATED
> template.
>
> cache.query(new SqlFieldsQuery("CREATE TABLE city (id LONG
> PRIMARY KEY, name VARCHAR) WITH \"template=replicated\"")).getAll();
>
> // Create table based on PARTITIONED template with one
> backup.
>
> cache.query(new SqlFieldsQuery("CREATE TABLE person (id
> LONG, name VARCHAR, city_id LONG, PRIMARY KEY (id, city_id)) WITH
> \"template=replicated\"")).getAll();
>
>
>
> // Create an index.
>
> cache.query(new SqlFieldsQuery("CREATE INDEX on Person
> (city_id)")).getAll();
>
>
>
> SqlFieldsQuery qry = new SqlFieldsQuery("INSERT INTO city
> (id, name) VALUES (?, ?)");
>
>
>
> cache.query(qry.setArgs(1L, "Forest Hill")).getAll();
>
> cache.query(qry.setArgs(2L, "Denver")).getAll();
>
> cache.query(qry.setArgs(3L, "St. Petersburg")).getAll();
>
>
>
> qry = new SqlFieldsQuery("INSERT INTO person (id, name,
> city_id) values (?, ?, ?)");
>
> for(long i = 0; i < 100_000;++i)
>
> {
>
> cache.query(qry.setArgs(i, "John Doe", 3L)).getAll();
>
> }
>
> System.out.print("HI!");
>
> }}}}
>
>
>
>
>
>
>
> *From:* Denis Magda [mailto:dmagda@apache.org]
> *Sent:* Thursday, February 08, 2018 7:33 PM
> *To:* user@ignite.apache.org
> *Subject:* Re: Cat Example
>
>
>
> Hi Mike,
>
>
>
> If SQL indexes/configuration is set with the annotation and
> setIndexedTypes method then you have to use the type name (Cat in your
> case) as the SQL table name. It’s explained here:
>
> https://apacheignite-sql.readme.io/docs/schema-and-
> indexes#section-annotation-based-configuration
> <https://urldefense.proofpoint.com/v2/url?u=https-3A__apacheignite-2Dsql.readme.io_docs_schema-2Dand-2Dindexes-23section-2Dannotation-2Dbased-2Dconfiguration&d=DwMFaQ&c=9g4MJkl2VjLjS6R4ei18BA&r=ipRRuqPnuP3BWnXGSOR_sLoARpltax56uFYU6n57c3GFvMdyEV-dz2ez2lZZpYl0&m=Nrv7gL0zYS9bFUir2zKHmI30_jJzYTzlic8Vk0kWonQ&s=UZWx1Uz419uytrzu-xRfSiuRqc3rszCQr5rJqNjT8TI&e=>
>
>
>
> The cache name is used for IgniteCache APIs and other related methods.
>
>
>
> —
>
> Denis
>
>
>
> On Feb 8, 2018, at 3:48 PM, Williams, Michael <michael.williams@
> transamerica.com> wrote:
>
>
>
> Hi,
>
>
>
> Quick question, submitted a ticket earlier. How would I modify the below
> code such that, when viewed through Sql (dbeaver, eg) it behaves as if it
> had been created through a CREATE TABLE statement, where the name of the
> table was catCache? I’m trying to directly populate a series of tables that
> will be used downstream primarily through SQL. I’d like to be able to go
> into dBeaver, browse the tables, and see 10 cats named Fluffy, if this is
> working correctly.
>
> import org.apache.ignite.cache.query.annotations.*;
>
> import java.io
> <https://urldefense.proofpoint.com/v2/url?u=http-3A__java.io_&d=DwMFaQ&c=9g4MJkl2VjLjS6R4ei18BA&r=ipRRuqPnuP3BWnXGSOR_sLoARpltax56uFYU6n57c3GFvMdyEV-dz2ez2lZZpYl0&m=Nrv7gL0zYS9bFUir2zKHmI30_jJzYTzlic8Vk0kWonQ&s=oFi3ym9G_GwhhFWtyRu--rylVH_5Vs2w_77tNCIb1Ls&e=>
> .*;
>
>
>
> public class Cat implements Serializable {
>
> @QuerySqlField
>
> int legs;
>
> @QuerySqlField
>
> String name;
>
>
>
> Cat(int l, String n)
>
> {
>
> legs = l;
>
> name = n;
>
> }
>
> }
>
>
>
>
>
> import org.apache.ignite.Ignition;
>
> import org.apache.ignite.Ignite;
>
> import org.apache.ignite.IgniteCache;
>
> import org.apache.ignite.cache.CacheMode;
>
> import org.apache.ignite.configuration.CacheConfiguration;
>
> import org.apache.ignite.cache.query.SqlFieldsQuery;
>
> import org.apache.ignite.cache.query.QueryCursor;
>
> import java.util.List;
>
> public class Test {
>
> public static void main(String[] args)
>
> {
>
>
>
> Ignite ignite = Ignition.start();
>
> CacheConfiguration<Integer,Cat> cfg= new
> CacheConfiguration("catCache");
>
>
>
> cfg.setCacheMode(CacheMode.REPLICATED);
>
> cfg.setSqlEscapeAll(true);
>
> cfg.setSqlSchema("PUBLIC");
>
> cfg.setIndexedTypes(Integer.class,Cat.class);
>
> try(IgniteCache<Integer, Cat> cache =
> ignite.getOrCreateCache(cfg))
>
> {
>
> for (int i = 0; i < 10; ++i) {
>
> cache.put(i, new Cat(i + 1,"Fluffy"));
>
> }/*
>
> SqlFieldsQuery sql = new SqlFieldsQuery("select * from
> catCache");
>
> try (QueryCursor<List<?>> cursor = cache.query(sql)) {
>
> for (List<?> row : cursor)
>
> System.out.println("cat=" + row.get(0));
>
> }*/
>
> }
>
> System.out.print("Got It!");
>
>
>
> }}
>
> Thanks,
>
> *Mike Williams*
>
>
>
--
Sincerely Yours Amir Akhmedov
RE: Cat Example
Posted by "Williams, Michael" <mi...@transamerica.com>.
Is it possible to stream data into a table created by a query? For example, consider the following modified example. If I had a Person object, how would I replace the insert loop to improve speed?
Thanks,
Mike
import org.apache.ignite.Ignite;
import org.apache.ignite.IgniteCache;
import org.apache.ignite.Ignition;
import org.apache.ignite.cache.query.SqlFieldsQuery;
import org.apache.ignite.configuration.CacheConfiguration;
public class Test {
private static final String DUMMY_CACHE_NAME = "dummy_cache";
public static void main(String[] args)
{
Ignite ignite = Ignition.start();
{
// Create dummy cache to act as an entry point for SQL queries (new SQL API which do not require this
// will appear in future versions, JDBC and ODBC drivers do not require it already).
CacheConfiguration<?, ?> cacheCfg = new CacheConfiguration<>(DUMMY_CACHE_NAME).setSqlSchema("PUBLIC");
try (IgniteCache<?, ?> cache = ignite.getOrCreateCache(cacheCfg))
{
// Create reference City table based on REPLICATED template.
cache.query(new SqlFieldsQuery("CREATE TABLE city (id LONG PRIMARY KEY, name VARCHAR) WITH \"template=replicated\"")).getAll();
// Create table based on PARTITIONED template with one backup.
cache.query(new SqlFieldsQuery("CREATE TABLE person (id LONG, name VARCHAR, city_id LONG, PRIMARY KEY (id, city_id)) WITH \"template=replicated\"")).getAll();
// Create an index.
cache.query(new SqlFieldsQuery("CREATE INDEX on Person (city_id)")).getAll();
SqlFieldsQuery qry = new SqlFieldsQuery("INSERT INTO city (id, name) VALUES (?, ?)");
cache.query(qry.setArgs(1L, "Forest Hill")).getAll();
cache.query(qry.setArgs(2L, "Denver")).getAll();
cache.query(qry.setArgs(3L, "St. Petersburg")).getAll();
qry = new SqlFieldsQuery("INSERT INTO person (id, name, city_id) values (?, ?, ?)");
for(long i = 0; i < 100_000;++i)
{
cache.query(qry.setArgs(i, "John Doe", 3L)).getAll();
}
System.out.print("HI!");
}}}}
From: Denis Magda [mailto:dmagda@apache.org]
Sent: Thursday, February 08, 2018 7:33 PM
To: user@ignite.apache.org
Subject: Re: Cat Example
Hi Mike,
If SQL indexes/configuration is set with the annotation and setIndexedTypes method then you have to use the type name (Cat in your case) as the SQL table name. It’s explained here:
https://apacheignite-sql.readme.io/docs/schema-and-indexes#section-annotation-based-configuration<https://urldefense.proofpoint.com/v2/url?u=https-3A__apacheignite-2Dsql.readme.io_docs_schema-2Dand-2Dindexes-23section-2Dannotation-2Dbased-2Dconfiguration&d=DwMFaQ&c=9g4MJkl2VjLjS6R4ei18BA&r=ipRRuqPnuP3BWnXGSOR_sLoARpltax56uFYU6n57c3GFvMdyEV-dz2ez2lZZpYl0&m=Nrv7gL0zYS9bFUir2zKHmI30_jJzYTzlic8Vk0kWonQ&s=UZWx1Uz419uytrzu-xRfSiuRqc3rszCQr5rJqNjT8TI&e=>
The cache name is used for IgniteCache APIs and other related methods.
—
Denis
On Feb 8, 2018, at 3:48 PM, Williams, Michael <mi...@transamerica.com>> wrote:
Hi,
Quick question, submitted a ticket earlier. How would I modify the below code such that, when viewed through Sql (dbeaver, eg) it behaves as if it had been created through a CREATE TABLE statement, where the name of the table was catCache? I’m trying to directly populate a series of tables that will be used downstream primarily through SQL. I’d like to be able to go into dBeaver, browse the tables, and see 10 cats named Fluffy, if this is working correctly.
import org.apache.ignite.cache.query.annotations.*;
import java.io<https://urldefense.proofpoint.com/v2/url?u=http-3A__java.io_&d=DwMFaQ&c=9g4MJkl2VjLjS6R4ei18BA&r=ipRRuqPnuP3BWnXGSOR_sLoARpltax56uFYU6n57c3GFvMdyEV-dz2ez2lZZpYl0&m=Nrv7gL0zYS9bFUir2zKHmI30_jJzYTzlic8Vk0kWonQ&s=oFi3ym9G_GwhhFWtyRu--rylVH_5Vs2w_77tNCIb1Ls&e=>.*;
public class Cat implements Serializable {
@QuerySqlField
int legs;
@QuerySqlField
String name;
Cat(int l, String n)
{
legs = l;
name = n;
}
}
import org.apache.ignite.Ignition;
import org.apache.ignite.Ignite;
import org.apache.ignite.IgniteCache;
import org.apache.ignite.cache.CacheMode;
import org.apache.ignite.configuration.CacheConfiguration;
import org.apache.ignite.cache.query.SqlFieldsQuery;
import org.apache.ignite.cache.query.QueryCursor;
import java.util.List;
public class Test {
public static void main(String[] args)
{
Ignite ignite = Ignition.start();
CacheConfiguration<Integer,Cat> cfg= new CacheConfiguration("catCache");
cfg.setCacheMode(CacheMode.REPLICATED);
cfg.setSqlEscapeAll(true);
cfg.setSqlSchema("PUBLIC");
cfg.setIndexedTypes(Integer.class,Cat.class);
try(IgniteCache<Integer, Cat> cache = ignite.getOrCreateCache(cfg))
{
for (int i = 0; i < 10; ++i) {
cache.put(i, new Cat(i + 1,"Fluffy"));
}/*
SqlFieldsQuery sql = new SqlFieldsQuery("select * from catCache");
try (QueryCursor<List<?>> cursor = cache.query(sql)) {
for (List<?> row : cursor)
System.out.println("cat=" + row.get(0));
}*/
}
System.out.print("Got It!");
}}
Thanks,
Mike Williams
Re: Cat Example
Posted by Denis Magda <dm...@apache.org>.
Hi Mike,
If SQL indexes/configuration is set with the annotation and setIndexedTypes method then you have to use the type name (Cat in your case) as the SQL table name. It’s explained here:
https://apacheignite-sql.readme.io/docs/schema-and-indexes#section-annotation-based-configuration <https://apacheignite-sql.readme.io/docs/schema-and-indexes#section-annotation-based-configuration>
The cache name is used for IgniteCache APIs and other related methods.
—
Denis
> On Feb 8, 2018, at 3:48 PM, Williams, Michael <mi...@transamerica.com> wrote:
>
> Hi,
>
> Quick question, submitted a ticket earlier. How would I modify the below code such that, when viewed through Sql (dbeaver, eg) it behaves as if it had been created through a CREATE TABLE statement, where the name of the table was catCache? I’m trying to directly populate a series of tables that will be used downstream primarily through SQL. I’d like to be able to go into dBeaver, browse the tables, and see 10 cats named Fluffy, if this is working correctly.
> import org.apache.ignite.cache.query.annotations.*;
> import java.io <http://java.io/>.*;
>
> public class Cat implements Serializable {
> @QuerySqlField
> int legs;
> @QuerySqlField
> String name;
>
> Cat(int l, String n)
> {
> legs = l;
> name = n;
> }
> }
>
>
> import org.apache.ignite.Ignition;
> import org.apache.ignite.Ignite;
> import org.apache.ignite.IgniteCache;
> import org.apache.ignite.cache.CacheMode;
> import org.apache.ignite.configuration.CacheConfiguration;
> import org.apache.ignite.cache.query.SqlFieldsQuery;
> import org.apache.ignite.cache.query.QueryCursor;
> import java.util.List;
> public class Test {
> public static void main(String[] args)
> {
>
> Ignite ignite = Ignition.start();
> CacheConfiguration<Integer,Cat> cfg= new CacheConfiguration("catCache");
>
> cfg.setCacheMode(CacheMode.REPLICATED);
> cfg.setSqlEscapeAll(true);
> cfg.setSqlSchema("PUBLIC");
> cfg.setIndexedTypes(Integer.class,Cat.class);
> try(IgniteCache<Integer, Cat> cache = ignite.getOrCreateCache(cfg))
> {
> for (int i = 0; i < 10; ++i) {
> cache.put(i, new Cat(i + 1,"Fluffy"));
> }/*
> SqlFieldsQuery sql = new SqlFieldsQuery("select * from catCache");
> try (QueryCursor<List<?>> cursor = cache.query(sql)) {
> for (List<?> row : cursor)
> System.out.println("cat=" + row.get(0));
> }*/
> }
> System.out.print("Got It!");
>
> }}
> Thanks,
> Mike Williams