CODEWITHSUNDEEP
shellunixcassandra
AVVA
AVVA

Reputation: 11

Running cql statements with shell script

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"

Upvotes: 1

Views: 1990

Answers (3)

Horia
Horia

Reputation: 2982

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

Upvotes: 1

donaldgavis
donaldgavis

Reputation: 565

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"

Upvotes: -1

Justin Cameron
Justin Cameron

Reputation: 611

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.

Upvotes: 1

Related Questions