Running CQL files from the command line.

[Richard Samuelson]

Dear CQL Maintainers,

When I try to run a file generated.cql from the command line using the command

java -cp cql.jar catdata.cql.AqlCmdLine generated.cql

I see the following error:

java.lang.NoClassDefFoundError: gnu/trove/map/hash/THashMap

at catdata.Util.<clinit>(Util.java:111)

at catdata.cql.AqlCmdLine.openCan(AqlCmdLine.java:26)

at catdata.cql.AqlCmdLine.main(AqlCmdLine.java:18)

Caused by: java.lang.ClassNotFoundException: gnu.trove.map.hash.THashMap

at java.base/jdk.internal.loader.BuiltinClassLoader.loadClass(BuiltinClassLoader.java:641)

at java.base/jdk.internal.loader.ClassLoaders$AppClassLoader.loadClass(ClassLoaders.java:188)

at java.base/java.lang.ClassLoader.loadClass(ClassLoader.java:520)

... 3 more

ERROR gnu/trove/map/hash/THashMap

I don't know much about Java, so I am a little helpless before this. Is it a known issue? Also, the instructions on this page seem to be out of date (aql to cql).

Sincerely,

Richard Samuelson

[Ryan Wisnesky]

I think I may have messed up the packaging for CQL just now or at some point after we converted “AQL” to “CQL” in the codebase; the error you are seeing (which presumably is limited to the command line) is basically java saying that it can’t find required libraries. There are peculiarities of how java’s class path is set when running on the command line vs graphically, and between operating systems, and when one needs to put JDBC drivers on the class path, that make this packaging tricky. I’ll take a look and get back to you soon.

> --
> You received this message because you are subscribed to the Google Groups "Categorical Data" group.
> To unsubscribe from this group and stop receiving emails from it, send an email to categoricalda...@googlegroups.com.
> To view this discussion on the web visit https://groups.google.com/d/msgid/categoricaldata/837cfb2d-1c7d-4bf7-943b-cceee11ddd2en%40googlegroups.com.

[Ryan Wisnesky]

I’ve uploaded a re-packaged version of CQL that does not have this error. Long story short: if you are on windows, then java 21, instead of 17, may now be required.

By the way, the command line CQL just writes “ok” to the screen for each command it processes; to use it in “production”, data motion in the CQL file must be done with CSV/JDBC/RDF/etc import and export commands. We’ve often created new command line drivers as well, so let me know if you need something specific; an example of this is “AqlCmdLineDarpa” which exports CQL queries as a very specific form of SQL.

[Richard Samuelson]

Dear Ryan,

Thank you for getting back to me! I would also like to export CQL queries as SQL.

I am an intern with Adobe, where I have been assigned to a project about data migrations. Your software is superb, and is able to do most of the heavy lifting.

 My approach involves translating SQL queries into and out of CQL. I implemented one direction (SQL -> CQL) myself, but I had hoped to use the CQL command line for the other direction (CQL -> SQL).

Sincerely,

Richard Samuelson

[Ryan Wisnesky]

Sounds like a worthy cause. I’ll create a clone of the DARPA command line and we can tweak it to your particular SQL export needs, just like we did for them. Can you share e.g. which SQL dialect you’re targeting, either here or to me directly? Thanks!

> To view this discussion on the web visit https://groups.google.com/d/msgid/categoricaldata/0b31899d-b263-40af-9bbf-c5964abba554n%40googlegroups.com.

[Richard Samuelson]

We are targeting PostgreSQL. The functionality would be this:

    1. Read a .cql file.

    2. Translate a specified query, defined in the file, into PostgreSQL.

    3. Return the translation.

Incidentally, the CQL IDE displays queries as SQL. Which dialect is it using?

[Richard Samuelson]

PostgreSQL 10.4, specifically.

[Ryan Wisnesky]

Hi Richard,

I changed the CommandLine to print out queries in SQL, in the same style DARPA was originally using, which I think is what you will want based on the example test file provided. However, note that in SQL, the concept of foreign key goes beyond what CQL would call foreign_keys - really, in a CQL schema coming from SQL, everything should be an attribute, and nothing a foreign_key. CQL code with foreign_keys will look kind of weird in SQL because it must be “un-nested", although it is totally functional: here is the output from your ‘co-pairing’ example, which is already un-nested:

query Copair =
INSERT INTO S_A select concat('(vv0=', concat(cast(vv0.ID as varchar(255)), ')')) as ID, concat('(vv0=', concat(cast(cast(vv0.f as varchar(255)) as varchar(255)), ')')) as f
from A as vv0 ;
INSERT INTO S_B select concat('(vv0=', concat(cast(vv0.ID as varchar(255)), ')')) as ID, concat('(vv0=', concat(cast(cast(vv0.g as varchar(255)) as varchar(255)), ')')) as g
from B as vv0;
INSERT INTO U_ select concat('(vv0=', concat(cast(vv0.ID as varchar(255)), ')')) as ID
from A as vv0 ;

If this is what you are expecting, things like “varchar(255)” are configurable, lmk. I just uploaded a new CQL jar file. -Ryan

> To view this discussion on the web visit https://groups.google.com/d/msgid/categoricaldata/e49c75d7-f483-48ee-a7c8-bd4ed2d4a8e4n%40googlegroups.com.

[Richard Samuelson]

Thank you! I appreciate all your help!

[Richard Samuelson]

Dear Ryan,

The command line SQL output seems to ignore the "where" section of queries. For example, the .cql file

schema S = literal : sql {
entities
A B C
foreign_keys
b: A -> B
b: C -> B
attributes
number: A -> Integer
number: B -> Integer
number: C -> Integer
}

query f = simple : S {
from
a : A
b : B
where
a.b = b
b.number = 4
attributes
number -> a.number
}

generates the output:

query f = INSERT INTO Q   select concat(concat('(a=', concat(cast(a.ID as varchar(255)), ')')), concat('(unfold0=', concat(cast(unfold0.ID as varchar(255)), ')'))) as ID, a.number as number

from A as a, B as unfold0

 ;

which is missing the condition 

a.b = b
b.number = 4

Sincerely,

Richard Samuelson

[Ryan Wisnesky]

Sure enough, the code to print the where clause was just gone/missing… I put it back and uploaded a new jarfile, the output now looks like this:

query f = INSERT INTO Q select concat(concat('(a=', concat(cast(a.ID as varchar(255)), ')')), concat('(unfold0=', concat(cast(unfold0.ID as varchar(255)), ')'))) as ID, a.number as number
from A as a, B as unfold0

where unfold0.ID = a.b and unfold0.number = '4’;

BTW, if you look at the CQL output in the viewer, you can see that it is removing the FROM call to B; then to print to SQL, that FROM B call must be restored. That is to say, CQL queries need fewer joins that SQL queries do, and CQL actually removes redundant FROM clauses; the reason B’s variable is named “unfold0” in the SQL is that CQL expressions such as a.f.g.h.p.q.r, which have no direct counterpart in SQL, need to be un-nested to e.g. six different FROM clauses, which would be named unfold0 … unfold5. If you make use of attributes only, rather than CQL foreign keys, this phenomenon disappears.

> To view this discussion on the web visit https://groups.google.com/d/msgid/categoricaldata/fc84a52a-ee86-43cc-9b58-9a6f0c8f9ac6n%40googlegroups.com.

[Richard Samuelson]

Dear Ryan,

I downloaded the new file, but I am still not seeing the where clause in the terminal output.

Sincerely,

Richard Samuelson

[Ryan Wisnesky]

I’m afraid I don’t have a good explanation for that; the only one I can think of is that sometimes cloudflare takes a while to propagate changes across its CDN. In any case, I’ve re-uploaded the CQL jar, and also put an another copy at http://categoricaldata.net/cqlAlt.jar . Because it is a new file, it can’t be stale in the CDN cache. Can you verify using the new cqlAlt jarf and the exact test case on this thread? I could also see how something goes wrong with where clause printing on other examples, but this particular example in this thread should work.

> To view this discussion on the web visit https://groups.google.com/d/msgid/categoricaldata/d7d4c99d-262a-4dc5-b209-3815ef2d1418n%40googlegroups.com.