Running cql statements with shell script

cql tutorial
cql commands
create keyspace cassandra
cassandra copy command
cqlsh run single command
connect to cassandra from terminal
cqlsh command not found
cassandra login

I have a below shell script which reads a file and updates the table. But the problem with the below code is that a connection is established for running each sql statements. I am looking for recommendation to enhance this code.

#!/bin/bash
input="/Users/temp/newfile.txt"
while IFS= read -r var
do
 echo "update keyspace.tableName set randomColumn='randomText' where random_id='$var'; exit" | ./cqlsh serverName  -u username -p password
if [ $? -eq 0 ]; then
   echo SUCCESS
   echo "select random_id,randomColumn from keyspace.tableName where random_id='$var'; exit" | ./cqlsh serverName  -u username -p password
else
    echo FAIL
fi
done < "$input"

I suggest doing this with the Python driver if you'd like better performance.

This example should be roughly equivalent:

#! /usr/bin/env python3

from cassandra.cluster import Cluster
from cassandra.auth import PlainTextAuthProvider

cluster = Cluster(auth_provider=PlainTextAuthProvider(username='cassandra', password='cassandra'))
session = cluster.connect()

with open("/Users/temp/newfile.txt") as f:
    for line in f:
        try:
            rows = session.execute("UPDATE keyspace.tableName SET randomColumn='randomText' WHERE random_id=%(var)s", {'var'=line})
            print("SUCCESS")
            for r in rows:
                print(r)
        except Exception:
            print("FAIL")

edit: you can even go a step further and use prepared statements and async queries, which will provide very significant performance increases if you are making a large volume of queries.

Starting cqlsh, Starts the CQL shell interactive terminal with specified options. Execute the cqlsh Cassandra python script to start the CQL shell; CQL shell is a -e " cql_statement ", --execute=" cql_statement ", Execute the CQL statement and exit. To direct  Automate sql script execution using bash script by Ramakanta · Published July 29, 2014 · Updated January 26, 2017 Many times as a system administrator you will be requiring the need to check few db details time to time or need to update,insert or simply execute a sql file automatically.

cqlsh has a -f option which allows to run cql statement from a file. You could generate the cql statements by parsing your newfile.txt and then run cqlsh with -f option

At the very basic level, you could do something like this:

#!/bin/bash
input="newfile.txt"
while IFS= read -r var
do
 echo "update ks.t1 set name='randomText' where id=$var;" >> result 
done < "$input"

./cqlsh serverName  -u username -p password -f result

if [ $? -eq 0 ]; then
   echo SUCCESS
   echo "select * from keyspace.tableName; exit" | ./cqlsh serverName  -u username -p password
else
    echo FAIL
fi

CQL shell (cqlsh) reference, Commands specific to the Cassandra Query Language shell (cqlsh) utility. Type help name to find out more about the name command. CQL shell uses native protocol and the DataStax Python Driver to execute CQL commands Enables or disables request tracing for all CQL statements in the current CQLSH session. Answer: The shell script given below prompts some basic information and displays the output of the SQL. You can use the same concept and hard-code some of these values in the shell-script itself and even run this script in the background to generate the output of an oracle sql query automatically (or use the oracle shell script from cron job).

I suggest to use cqlsh -e with xargs :

#!/bin/bash
input="/Users/temp/newfile.txt"
while IFS= read -r var
do
    echo "update keyspace.tableName set randomColumn='randomText' where random_id='$var';" | xargs cqlsh serverName  -u 'username' -p 'password' -e
if [ $? -eq 0 ]; then
    echo SUCCESS
    echo "select random_id,randomColumn from keyspace.tableName where random_id='$var';" | xargs cqlsh serverName  -u 'username' -p 'password' -e
else
    echo FAIL
fi
done < "$input"

Quick start – running CQL queries, Step 1 – start CQL shell (cqlsh) It allows you to run CQL queries directly onto Cassandra database. It is created using the following command statement:. The Invoke-Sqlcmd cmdlet runs a script containing the languages and commands supported by the SQL Server SQLCMD utility. The commands supported are Transact-SQL statements and the subset of the XQuery syntax that is supported by the database engine. This cmdlet also accepts many of the commands supported natively by SQLCMD, such as GO and QUIT.

Cassandra - Cqlsh, cqlsh --execute. cql_statement. Directs the shell to accept and execute a CQL command. cqlsh --file= SOURCE − Executes a file that contains CQL statements. unix - Running cql statements with shell script - Stack Overflow. I have a below shell script which reads a file and updates the table. But the problem with the below code is that a connection is established for running each sql statements. I am looking for.

CQLSh: the CQL shell, cqlsh is a command line shell for interacting with Cassandra through CQL (the location for the cqlshrc file; -e --execute: Execute the given statement, then exit  This type of stuff seems to just keep coming back. People want to run scripts remotely. I've tried every way I can think of and this is the best way. You not only get functionality but you get security as well. Every Unix environment needs a way to issue shell scripts, perl script, SQL scripts to remote servers.

Using the SQL Shell Interface, This interactive SQL Shell allows you to execute SQL statements dynamically. The SQL Shell Executing an SQL script file using the Shell's RUN command. I need to call a .sql file (A.sql) from a Unix Shell Script and save the output in a text file in the format AA_BB_(Oct-2010)_RR.txt. The Month-Year format in the output text file naming convention should be dynamic according to current month &amp; year , as &amp; when the Shell script runs.Please explain the process with the help of a sample code

Comments
  • OP wants the select ... to happen within the loop, when the query fails. $var would hold the value of the last row read from the input file and I am not sure if that's what OP wants.
  • Thanks for the response
  • I tried this one option before I post this, but it doesn't remove the fact that it needs to connect to database every single query and exit.
  • Hello in your previous request you add and 'exit;' is it possible to check before comment ?