Apache Ignite querying _val and _ver columns via sqline

Question

I am just starting with Apache Ignite and getting errors querying _val and _ver columns. Using latest docker image:

docker run -it -e "CONFIG_URI=https://raw.githubusercontent.com/apache/ignite/master/examples/config/example-cache.xml" apacheignite/ignite:2.13.0

then from sqlline in container create a simple table with 3 rows:

CREATE TABLE dept
(
  deptno LONG,
  dname  VARCHAR,
  loc    VARCHAR,
  CONSTRAINT pk_dept PRIMARY KEY (deptno)
);


insert into dept (deptno, dname, loc) values (10, 'ACCOUNTING', 'NEW YORK');
insert into dept (deptno, dname, loc) values(20, 'RESEARCH', 'DALLAS');
insert into dept (deptno, dname, loc) values(30, 'SALES', 'CHICAGO');

Now the example I'm following tells me this should work:

Select _key, _val, _ver from dept;

However I am getting errors. From SYS.TABLE_COLUMNS it looks like _ver columns doesn't exist. I see it in prior versions (ex 2.7.0). was it removed?

I can see _val using CAST:

0: jdbc:ignite:thin://127.0.0.1/> SELECT _key, CAST(_val AS VARCHAR) FROM dept;
+------+---------------------------------------------------------------------------------------------------------------------------+
| _KEY |                                                   CAST(_VAL AS VARCHAR)                                                   |
+------+---------------------------------------------------------------------------------------------------------------------------+
| 10   | SQL_PUBLIC_DEPT_d6d3fe8f_dbf7_4c2a_9c86_3187dc92887c [idHash=1589491372, hash=-850331171, DNAME=ACCOUNTING, LOC=NEW YORK] |
| 20   | SQL_PUBLIC_DEPT_d6d3fe8f_dbf7_4c2a_9c86_3187dc92887c [idHash=2036917729, hash=-1365635177, DNAME=RESEARCH, LOC=DALLAS]    |
| 30   | SQL_PUBLIC_DEPT_d6d3fe8f_dbf7_4c2a_9c86_3187dc92887c [idHash=1046181438, hash=1109822257, DNAME=SALES, LOC=CHICAGO]       |
+------+---------------------------------------------------------------------------------------------------------------------------+

but not without it

0: jdbc:ignite:thin://127.0.0.1/> SELECT _key, _val FROM dept;
Error: Statement is closed. (state=,code=0)

Has it worked without cast in much older versions, but not anymore?

score 0 · Answer 1 · answered Jun 06 '22 at 09:12

I don't recall _ver ever existing. What's your example and how old is it?

The use of _key and _val makes sense, though. Your key is a long, which is a sensible SQL value. On the other hand, your value is a collection of two fields. There is no standard SQL type for "collection of two fields" so it fails.

(If you run the same SQL using an Ignite client node, it will work.)

Apache Ignite querying _val and _ver columns via sqline

1 Answers1