You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@ignite.apache.org by siva <si...@bizruntime.com> on 2018/11/17 10:25:57 UTC
How to get the column names(or fieldsNames) of existing ignite sql
table
Hi,
Creating tables using sql api
IgniteCache cache = ignite.getOrCreateCache(new
CacheConfiguration<>().setSqlSchema("PUBLIC").setName(cacheName));
createtableQuery = create table Person(name varchar ,id int primarykey) with
"template ="Replicated".
cache.query(new SqlFieldsQuery(createtableQuery)).getAll();
Quetions:
=======
1.How to get the existing table columnNames ?
i am trying to use this query but its throwing exception
*select COLUMN_NAME from INFORMATION_SCHEMA.COLUMNS where
TABLE_NAME='Person'*
2.When i create a table with above configurations
two caches gets creating
one is *cache-SQL_PUBLIC_PERSON*
another which i have passed while creating table cache-Persons
is it default functionality of ignite?
--
Sent from: http://apache-ignite-users.70518.x6.nabble.com/
Re: How to get the column names(or fieldsNames) of existing ignite
sql table
Posted by Peter Sham <pe...@yahoo.com>.
From what I understand, the first cache created by getOrCreate, where you will be using to run the SQLFieldsQuery to create your table, is just a dummy. So name it as anything other than "Persons". Yes. It may sound counter intuitive. I'm still learning so may learn the reason behind it later.
Hope this help.
On Tuesday, November 20, 2018, 1:51:37 AM EST, siva <si...@bizruntime.com> wrote:
Hi ,
Thank You for Reply,
*And for question 2, I think, yes, from my learning, SQL Create Table always
create another cache with default naming like SQL_PUBLIC_
. But according to manual, you can override the name by supplying options
after "with".*
IgniteCache cache = ignite.getOrCreateCache(new
CacheConfiguration<>().setSqlSchema("PUBLIC").setName("Persons").setSqlEscapeAll(true));
If i give same cacheName as mentioned in the above configuration in "With"
cluse getting exception
cacheName "Persons" already created.
cache.query(new SqlFieldsQuery(
"CREATE TABLE IF NOT Exists Persons(id LONG PRIMARY KEY,
name VARCHAR,dojurney date) WITH \"template=replicated,CACHE_NAME=Persons
,affinity_key=id\"")).getAll();
--
Sent from: http://apache-ignite-users.70518.x6.nabble.com/
Re: How to get the column names(or fieldsNames) of existing ignite
sql table
Posted by siva <si...@bizruntime.com>.
Hi ,
Thank You for Reply,
*And for question 2, I think, yes, from my learning, SQL Create Table always
create another cache with default naming like SQL_PUBLIC_
. But according to manual, you can override the name by supplying options
after "with".*
IgniteCache cache = ignite.getOrCreateCache(new
CacheConfiguration<>().setSqlSchema("PUBLIC").setName("Persons").setSqlEscapeAll(true));
If i give same cacheName as mentioned in the above configuration in "With"
cluse getting exception
cacheName "Persons" already created.
cache.query(new SqlFieldsQuery(
"CREATE TABLE IF NOT Exists Persons(id LONG PRIMARY KEY,
name VARCHAR,dojurney date) WITH \"template=replicated,CACHE_NAME=Persons
,affinity_key=id\"")).getAll();
--
Sent from: http://apache-ignite-users.70518.x6.nabble.com/
Re: How to get the column names(or fieldsNames) of existing ignite
sql table
Posted by siva <si...@bizruntime.com>.
Hi ,
*And for question 2, I think, yes, from my learning, SQL Create Table always
create another cache with default naming like SQL_PUBLIC_
. But according to manual, you can override the name by supplying options
after "with".*
But For execute a query we should have Ignitecache object which is from
cacheconfiguration,so that in cache confiuration we should mention
*cacheName *
IgniteCache cache = ignite.getOrCreateCache(new
CacheConfiguration<>().setSqlSchema("PUBLIC").setName(cacheName));
so if i give same cacheName in cache configuration and in "With" Cluse
,getting exception like same cache alredy created .
Where i was missed?
--
Sent from: http://apache-ignite-users.70518.x6.nabble.com/
Re: How to get the column names(or fieldsNames) of existing
ignite sql table
Posted by Peter Sham <pe...@yahoo.com>.
Hello,
I don't think you can get all the tables created in a cache by SQL. I'm learning this stuff and hit the same questions and figure I can find it out by the following steps in code (I'm coding c# and don't know if the methods apply in Java).
Get all cache names from IIgnite by GetCachenames()
Loop through all the caches in getting them one by one by IIgnite.GetCache<object, object>()
Get the cache configuration by GetConfiguration on IICache
Loop through QueryEntities on cache configuration
Each QueryEntity would hold the table name, even with those created by SQL.
In fact, within QueryEntity you will have Fields which are the columns.
And for question 2, I think, yes, from my learning, SQL Create Table always create another cache with default naming like SQL_PUBLIC_<table name>. But according to manual, you can override the name by supplying options after "with".
Happy Coding!
Peter Sham
sham.peter@gmail.com (https://link.getmailspring.com/link/1542461644.local-9c4bedcc-4679-v1.5.2-31660462@getmailspring.com/0?redirect=mailto%3Asham.peter%40gmail.com&recipient=dXNlckBpZ25pdGUuYXBhY2hlLm9yZw%3D%3D)
On Nov 17 2018, at 5:42 am, Dmitry Lazurkin <di...@gmail.com> wrote:
>
> Hello.
> On 17.11.2018 13:25, siva wrote:
> > Quetions:
> > =======
> > 1.How to get the existing table columnNames ?
> >
> > i am trying to use this query but its throwing exception
> > *select COLUMN_NAME from INFORMATION_SCHEMA.COLUMNS where
> > TABLE_NAME='Person'*
>
>
> You need query with setLocal(true). I don't known how to do this with
> thin client.
>
Re: How to get the column names(or fieldsNames) of existing ignite
sql table
Posted by Dmitry Lazurkin <di...@gmail.com>.
Hello.
On 17.11.2018 13:25, siva wrote:
> Quetions:
> =======
> 1.How to get the existing table columnNames ?
>
> i am trying to use this query but its throwing exception
>
> *select COLUMN_NAME from INFORMATION_SCHEMA.COLUMNS where
> TABLE_NAME='Person'*
You need query with setLocal(true). I don't known how to do this with
thin client.
Re: How to get the column names(or fieldsNames) of existing ignite
sql table
Posted by "Maxim.Pudov" <pu...@gmail.com>.
1. Check out this thread to get a list of fields:
http://apache-ignite-users.70518.x6.nabble.com/Get-Field-Names-In-Select-Query-td25258.html
2. "Create table..." command creates a cache under the hood.
https://apacheignite-sql.readme.io/docs/create-table
--
Sent from: http://apache-ignite-users.70518.x6.nabble.com/