You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@ignite.apache.org by Sachin janani <sa...@gmail.com> on 2022/05/30 11:08:01 UTC
How to find primary keys column of a table in ignite using sys tables?
Hi ,
I have created a table in ignite using SQLLine and I want to see which
columns in that table are the primary key. When i queried
*SYS.TABLE_COLUMNS* I don't see the Primary key columns.Can someone please
help me how can i get the primary key columns of already created ignite
table? Following is the table i have created and the output of the
*SYS.TABLE_COLUMNS*:
0: jdbc:ignite:thin://127.0.0.1/> CREATE TABLE SUPPLIER (
. . . . . . . . . . . . . . . .)> S_SUPPKEY INT,
. . . . . . . . . . . . . . . .)> S_NAME VARCHAR,
. . . . . . . . . . . . . . . .)> S_ADDRESS VARCHAR,
. . . . . . . . . . . . . . . .)> S_CITY VARCHAR,
. . . . . . . . . . . . . . . .)> S_NATION VARCHAR,
. . . . . . . . . . . . . . . .)> S_REGION VARCHAR,
. . . . . . . . . . . . . . . .)> S_PHONE VARCHAR, PRIMARY
KEY(S_SUPPKEY, S_REGION)) WITH "TEMPLATE=PARTITIONED,
AFFINITY_KEY=S_REGION";
No rows affected (0.047 seconds)
0: jdbc:ignite:thin://127.0.0.1/> select * from sys.TABle_COLUMNS;
+-------------+------------+-------------+-----------------+----------------+---------------+----------+-------+-----------+-------+-------------------+
| COLUMN_NAME | TABLE_NAME | SCHEMA_NAME | AFFINITY_COLUMN |
AUTO_INCREMENT | DEFAULT_VALUE | NULLABLE | PK | PRECISION | SCALE
| TYPE |
+-------------+------------+-------------+-----------------+----------------+---------------+----------+-------+-----------+-------+-------------------+
| _KEY | SUPPLIER | PUBLIC | false | false
| | false | true | -1 | -1 |
|
| _VAL | SUPPLIER | PUBLIC | false | false
| | true | false | -1 | -1 |
|
| S_SUPPKEY | SUPPLIER | PUBLIC | false | false
| null | true | false | -1 | -1 |
java.lang.Integer |
| S_NAME | SUPPLIER | PUBLIC | false | false
| null | true | false | -1 | -1 |
java.lang.String |
| S_ADDRESS | SUPPLIER | PUBLIC | false | false
| null | true | false | -1 | -1 |
java.lang.String |
| S_CITY | SUPPLIER | PUBLIC | false | false
| null | true | false | -1 | -1 |
java.lang.String |
| S_NATION | SUPPLIER | PUBLIC | false | false
| null | true | false | -1 | -1 |
java.lang.String |
| S_REGION | SUPPLIER | PUBLIC | true | false
| null | true | false | -1 | -1 |
java.lang.String |
| S_PHONE | SUPPLIER | PUBLIC | false | false
| null | true | false | -1 | -1 |
java.lang.String |
+-------------+------------+-------------+-----------------+----------------+---------------+----------+-------+-----------+-------+-------------------+
9 rows selected (0.004 seconds)
0: jdbc:ignite:thin://127.0.0.1/>
As seen from the above output i dont see S_SUPPKEY and S_REGION as primary
key instead I see _KEY as primary key. So i want to know how can i find
which columns in the table are primary key from SQLLine?
Thanks and Regards,
--
*Sachin Janani*
Re: How to find primary keys column of a table in ignite using sys tables?
Posted by Sachin janani <sa...@gmail.com>.
Hi ,
Thanks for the information. But the PARGMA is not working. Its giving parse
exception as seen below. I tried different combination of this statement
but none of it worked.
0: jdbc:ignite:thin://127.0.0.1/> PRAGMA PUBLIC.table_info(SUPPLIER);
Error: Failed to parse query. Syntax error in SQL statement "PRAGMA[*]
PUBLIC.TABLE_INFO(SUPPLIER) "; expected "PREPARE, {"; SQL statement:
PRAGMA PUBLIC.table_info(SUPPLIER) [42001-197] (state=42000,code=1001)
java.sql.SQLException: Failed to parse query. Syntax error in SQL
statement "PRAGMA[*] PUBLIC.TABLE_INFO(SUPPLIER) "; expected "PREPARE,
{"; SQL statement:
PRAGMA PUBLIC.table_info(SUPPLIER) [42001-197]
at org.apache.ignite.internal.jdbc.thin.JdbcThinConnection.sendRequest(JdbcThinConnection.java:1009)
at org.apache.ignite.internal.jdbc.thin.JdbcThinStatement.execute0(JdbcThinStatement.java:234)
at org.apache.ignite.internal.jdbc.thin.JdbcThinStatement.execute(JdbcThinStatement.java:560)
at sqlline.Commands.executeSingleQuery(Commands.java:1054)
at sqlline.Commands.execute(Commands.java:1003)
at sqlline.Commands.sql(Commands.java:967)
at sqlline.SqlLine.dispatch(SqlLine.java:734)
at sqlline.SqlLine.begin(SqlLine.java:541)
at sqlline.SqlLine.start(SqlLine.java:267)
at sqlline.SqlLine.main(SqlLine.java:206)
On Thu, Jun 2, 2022 at 3:34 PM Юрий <ju...@gmail.com> wrote:
> Hi,
> There is the ticket [1] for the issue.
> While the ticket is not resolved try to use PRAGMA
> table_info(table-name); as explained in the SQLite PRAGMA documentation
> [2].
>
> [1] https://issues.apache.org/jira/browse/IGNITE-16667
> [2] http://www.sqlite.org/pragma.html#pragma_table_info
>
> пн, 30 мая 2022 г. в 14:08, Sachin janani <sa...@gmail.com>:
>
>> Hi ,
>> I have created a table in ignite using SQLLine and I want to see which
>> columns in that table are the primary key. When i queried
>> *SYS.TABLE_COLUMNS* I don't see the Primary key columns.Can someone
>> please help me how can i get the primary key columns of already created
>> ignite table? Following is the table i have created and the output of the
>> *SYS.TABLE_COLUMNS*:
>>
>> 0: jdbc:ignite:thin://127.0.0.1/> CREATE TABLE SUPPLIER (
>> . . . . . . . . . . . . . . . .)> S_SUPPKEY INT,
>> . . . . . . . . . . . . . . . .)> S_NAME VARCHAR,
>> . . . . . . . . . . . . . . . .)> S_ADDRESS VARCHAR,
>> . . . . . . . . . . . . . . . .)> S_CITY VARCHAR,
>> . . . . . . . . . . . . . . . .)> S_NATION VARCHAR,
>> . . . . . . . . . . . . . . . .)> S_REGION VARCHAR,
>> . . . . . . . . . . . . . . . .)> S_PHONE VARCHAR, PRIMARY KEY(S_SUPPKEY, S_REGION)) WITH "TEMPLATE=PARTITIONED, AFFINITY_KEY=S_REGION";
>> No rows affected (0.047 seconds)
>> 0: jdbc:ignite:thin://127.0.0.1/> select * from sys.TABle_COLUMNS;
>> +-------------+------------+-------------+-----------------+----------------+---------------+----------+-------+-----------+-------+-------------------+
>> | COLUMN_NAME | TABLE_NAME | SCHEMA_NAME | AFFINITY_COLUMN | AUTO_INCREMENT | DEFAULT_VALUE | NULLABLE | PK | PRECISION | SCALE | TYPE |
>> +-------------+------------+-------------+-----------------+----------------+---------------+----------+-------+-----------+-------+-------------------+
>> | _KEY | SUPPLIER | PUBLIC | false | false | | false | true | -1 | -1 | |
>> | _VAL | SUPPLIER | PUBLIC | false | false | | true | false | -1 | -1 | |
>> | S_SUPPKEY | SUPPLIER | PUBLIC | false | false | null | true | false | -1 | -1 | java.lang.Integer |
>> | S_NAME | SUPPLIER | PUBLIC | false | false | null | true | false | -1 | -1 | java.lang.String |
>> | S_ADDRESS | SUPPLIER | PUBLIC | false | false | null | true | false | -1 | -1 | java.lang.String |
>> | S_CITY | SUPPLIER | PUBLIC | false | false | null | true | false | -1 | -1 | java.lang.String |
>> | S_NATION | SUPPLIER | PUBLIC | false | false | null | true | false | -1 | -1 | java.lang.String |
>> | S_REGION | SUPPLIER | PUBLIC | true | false | null | true | false | -1 | -1 | java.lang.String |
>> | S_PHONE | SUPPLIER | PUBLIC | false | false | null | true | false | -1 | -1 | java.lang.String |
>> +-------------+------------+-------------+-----------------+----------------+---------------+----------+-------+-----------+-------+-------------------+
>> 9 rows selected (0.004 seconds)
>> 0: jdbc:ignite:thin://127.0.0.1/>
>>
>> As seen from the above output i dont see S_SUPPKEY and S_REGION as
>> primary key instead I see _KEY as primary key. So i want to know how can i
>> find which columns in the table are primary key from SQLLine?
>>
>> Thanks and Regards,
>> --
>> *Sachin Janani*
>>
>>
>
>
> --
> Живи с улыбкой! :D
>
--
*Sachin Janani*
Re: How to find primary keys column of a table in ignite using sys tables?
Posted by Юрий <ju...@gmail.com>.
Hi,
There is the ticket [1] for the issue.
While the ticket is not resolved try to use PRAGMA table_info(table-name); as
explained in the SQLite PRAGMA documentation [2].
[1] https://issues.apache.org/jira/browse/IGNITE-16667
[2] http://www.sqlite.org/pragma.html#pragma_table_info
пн, 30 мая 2022 г. в 14:08, Sachin janani <sa...@gmail.com>:
> Hi ,
> I have created a table in ignite using SQLLine and I want to see which
> columns in that table are the primary key. When i queried
> *SYS.TABLE_COLUMNS* I don't see the Primary key columns.Can someone
> please help me how can i get the primary key columns of already created
> ignite table? Following is the table i have created and the output of the
> *SYS.TABLE_COLUMNS*:
>
> 0: jdbc:ignite:thin://127.0.0.1/> CREATE TABLE SUPPLIER (
> . . . . . . . . . . . . . . . .)> S_SUPPKEY INT,
> . . . . . . . . . . . . . . . .)> S_NAME VARCHAR,
> . . . . . . . . . . . . . . . .)> S_ADDRESS VARCHAR,
> . . . . . . . . . . . . . . . .)> S_CITY VARCHAR,
> . . . . . . . . . . . . . . . .)> S_NATION VARCHAR,
> . . . . . . . . . . . . . . . .)> S_REGION VARCHAR,
> . . . . . . . . . . . . . . . .)> S_PHONE VARCHAR, PRIMARY KEY(S_SUPPKEY, S_REGION)) WITH "TEMPLATE=PARTITIONED, AFFINITY_KEY=S_REGION";
> No rows affected (0.047 seconds)
> 0: jdbc:ignite:thin://127.0.0.1/> select * from sys.TABle_COLUMNS;
> +-------------+------------+-------------+-----------------+----------------+---------------+----------+-------+-----------+-------+-------------------+
> | COLUMN_NAME | TABLE_NAME | SCHEMA_NAME | AFFINITY_COLUMN | AUTO_INCREMENT | DEFAULT_VALUE | NULLABLE | PK | PRECISION | SCALE | TYPE |
> +-------------+------------+-------------+-----------------+----------------+---------------+----------+-------+-----------+-------+-------------------+
> | _KEY | SUPPLIER | PUBLIC | false | false | | false | true | -1 | -1 | |
> | _VAL | SUPPLIER | PUBLIC | false | false | | true | false | -1 | -1 | |
> | S_SUPPKEY | SUPPLIER | PUBLIC | false | false | null | true | false | -1 | -1 | java.lang.Integer |
> | S_NAME | SUPPLIER | PUBLIC | false | false | null | true | false | -1 | -1 | java.lang.String |
> | S_ADDRESS | SUPPLIER | PUBLIC | false | false | null | true | false | -1 | -1 | java.lang.String |
> | S_CITY | SUPPLIER | PUBLIC | false | false | null | true | false | -1 | -1 | java.lang.String |
> | S_NATION | SUPPLIER | PUBLIC | false | false | null | true | false | -1 | -1 | java.lang.String |
> | S_REGION | SUPPLIER | PUBLIC | true | false | null | true | false | -1 | -1 | java.lang.String |
> | S_PHONE | SUPPLIER | PUBLIC | false | false | null | true | false | -1 | -1 | java.lang.String |
> +-------------+------------+-------------+-----------------+----------------+---------------+----------+-------+-----------+-------+-------------------+
> 9 rows selected (0.004 seconds)
> 0: jdbc:ignite:thin://127.0.0.1/>
>
> As seen from the above output i dont see S_SUPPKEY and S_REGION as primary
> key instead I see _KEY as primary key. So i want to know how can i find
> which columns in the table are primary key from SQLLine?
>
> Thanks and Regards,
> --
> *Sachin Janani*
>
>
--
Живи с улыбкой! :D