How to run cql files (.cql) from within cqlsh?

Question

The problem that I am having is that I want to run the following command (and I can't):

cqlsh < cql_directory/cql_create_stuff.cql

Because I have not logged in to cqlsh.

So I logged in:

cqlsh -u 'my_username' -p 'my_super_secret_password'

and now I tried doing the command in cqlsh shell but It just responds with a syntax error.

Basically, how do I login into cqlsh and run an external CQL script in my file system?

Mandle

Answer 1

Use the SOURCE

http://www.datastax.com/documentation/cql/3.1/cql/cql_reference/source_r.html

You can use -f option as well to execute commands from file

http://www.datastax.com/documentation/cql/3.1/cql/cql_reference/cqlsh.html

Eristic

Yep that worked. Do you mind puttying an example explicitly too in your response? Thanks! :) – Mandle

But its so annoying that I have to even go to the cqlsh, can't I login in bash and do it in bash and remain in bash? like cqlsh < cql_directory/cql_create_stuff.cql? The reason that worked before was because I did not have authentication set up, so u can only do that trick if authentication is set up? – Mandle

bin/cqlsh -u user -p pwd -f file.cql should work – Eristic

The original links on this answer were not working as of Oct 2019. The new link is docs.datastax.com/en/dse/5.1/cql/cql/cql_reference/… – Temperature

Answer 2

Assuming that the path of the file with the CQL commands is /mydir/myfile.cql, there are two ways:

If you are not logged in to cqlsh:

cqlsh -u 'my_username' -p 'my_password' -f /mydir/myfile.cql

If you are logged in to cqlsh:

SOURCE '/mydir/myfile.cql'

Notice the single quotation marks. The shorthand notation for $HOME (for example, '~/mydir/myfile.cql') is also supported.

Both ways also work with relative paths (to the current directory).

Anciently

Answer 3

Assuming your filename is "tables.cql" and it is placed as: /files/tables.cql;

A - Locally

cqlsh -f /files/tables.cql

B - Connecting To A Docker Container Running Cassandra

Assuming the name of the Docker container that which running Cassandra is "cas" (keep in mind that you can also use the hash id of the docker container if there is no name assigned to it);

docker exec -it cas cqlsh -f /files/tables.cql

As stated on other answers, -u and -p options can be added in order to use the username/password combinations.

Bellflower

Answer 4

This is for Window system

suppose you cassandra dir is C:\Program Files\DataStax-DDC\apache-cassandra\bin

Suppose directory where your .cql file OR cql query file is D:\ril\s\developement\new one\excel after parse\Women catalogue template.cql

Now follow below steps for importing cql file

1. Go on command prompt (cmd)
2. Go on the directory where cql file is there (cd "..\ril\sizeguide\developement\new one\excel after parse")
3. Run below command "c:\Program Files\DataStax-DDC\apache-cassandra\bin\cqlsh.bat" <"Women catalogue template.cql"

And its Done.

Important Note:

• Please make sure column value should not have single quote ' character like ('If you don't find a exact match, go for the next large size') other wise it will fail.
• If you want single quote to be inserted, please use it two times like below and Cassandra will treat it as one time ('If you don''t find a exact match, go for the next large size')
• All text column should be enclosed by single quote '' like 'Sale category'. For empty value, please use two single quote ''.

Danieldaniela