Couchbase Index Automation Scripts

By | June 15, 2019
couchbase image automation scripts

In Couchbase, indexes are used to fetch data in less time. With the increase in number of documents in a bucket, number of keys value pairs increase. Hence when we execute a query without any secondary index (with only primary index) then it has to scan entire key-value pairs to match the filter condition. So Indexes play an important role in Couchbase Database.

It is normal to sync indexes frequently from one cluster1 to another cluster2 where cluster1 and cluster2 have some common buckets. So you might need scripts to automate indexes sync activity.

I am going to give you three scripts for Couchbase index automation. You can use these scripts in your Couchbase Index automation according to your use.

You can check my GitHub repository to get code directly from my git –
https://github.com/rajatgupta200795/Couchbase-Documents

There are three shell script files – 

Create Index Script – 

#1. couchbase-create-index-automation.sh : If you will run this command on any server of cluster1  with your username and password, then it will generate an index.txt file. This index.txt file will be having all the indexes on cluster1.

couchbase-create-index-automation.sh

sudo /opt/couchbase/bin/cbq --script="SELECT condition, keyspace_id, name, index_key FROM system:indexes order by keyspace_id ASC" --engine=http://localhost:8091 -u $1 -p $2  |  tail -n +6 > rough-index.json
for k in $(jq '.results | keys | .[]' rough-index.json); do
    value=$(jq -r ".results[$k]" rough-index.json);
    a=$(jq -r '.index_key' <<< "$value");
    b=$(jq -r '.keyspace_id' <<< "$value");
    c=$(jq -r '.name' <<< "$value");
    d=$(jq -r '.condition' <<< "$value");
b=${b//\[/(}
b=${b//\]/)}
c=${c//\[/(}
c=${c//\]/)}
if [ "$a" == '[]' ]; then
    if [ "$c" == '#primary' ]; then
        c=$b
    fi;
    echo 'create primary index `'$c'` on `'$b'` ' >> mid-index.txt
else
    a=${a//\"/}
    a=${a//\[/(}
    a=${a//\]/)}
    if [ "$d" == null ]; then
        echo 'create index `'$c'` on `'$b'` '$a >> mid-index.txt
    else
        echo 'create index `'$c'` on `'$b'` '$a' where '$d' ' >> mid-index.txt
    fi;
fi;
done
rm -rf rough-index.json
mv mid-index.txt index.txt
#sort -u mid-index.txt > index.txt
rm -rf mid-index.txt
sed "s/(0)/[0]/g;s/(1)/[1]/g;s/(2)/[2]/g" index.txt>new-index.txt
rm -rf index.txt
mv new-index.txt index.txt
rm -rf new-index.txt

system@cluster1:~ [0]$ ./couchbase-create-index-automation.sh username password
system@cluster1:~ [0]$ ls
couchbase-create-index-automation.sh index.txt 
  
This index.txt file will contain create index queries like –

index.txt 
create index indexName1 on bucketName1
create index indexName2 on bucketName2(key1, key2)
create index indexName3 on bucketName3(key1, key2) where key3="value"
create index indexName4 on bucketName4(key1) where key1="value" and key2 in [value1, value2]
create index indexName5 on bucketName5
     

Drop Index Script – 

#2. couchbase-delete-index-automation.sh : If you will run this command on any server of cluster1 with your username and password, it will generate an index file. This index file will be having all the drop indexes queries for all indexes present on cluster1.

couchbase-delete-index-automation.sh

sudo /opt/couchbase/bin/cbq --script="SELECT keyspace_id, name, index_key FROM system:indexes" --engine=http://localhost:8091 -u $1 -p $2 |  tail -n +6 > rough-index.json
for k in $(jq '.results | keys | .[]' rough-index.json); do
value=$(jq -r ".results[$k]" rough-index.json);
a=$(jq -r '.index_key' <<< "$value");
b=$(jq -r '.keyspace_id' <<< "$value");
c=$(jq -r '.name' <<< "$value");
#echo $a' '$b' '$c' '$k
if [ "$a" == '[]' ]; then
if [ "$c" == '#primary' ]; then
c=$c
fi;
echo 'drop index `'$b'`.`'$c'`' >> mid-index.txt
else
echo 'drop index `'$b'`.`'$c'`' >> mid-index.txt
fi;
done
rm -rf rough-index.json
sort -u mid-index.txt > index.txt
file_contents=$(<index.txt)
echo "${file_contents//[/(}" > index.txt
file_contents=$(<index.txt)
echo "${file_contents//]/)}" > index.txt
rm -rf mid-index.txt

system@cluster1:~ [0]$ ./couchbase-delete-index-automation.sh username password
system@cluster1:~ [0]$ ls
couchbase-delete-index-automation.sh   index.txt 

This index.txt file will contain drop index queries like –

index.txt  
drop index bucketName1.indexName1
drop index bucketName2.indexName2
drop index bucketName3.indexName3
drop index bucketName4.indexName4
drop index bucketName5.indexName5

Index Execution Script –

#3. couchbase-index-query-executer.sh : Now this script will help you to execute all queries saved in index.txt file.

while IFS=, read -r input; do
sudo /opt/couchbase/bin/cbq --script="$input" --engine=http://localhost:8091 -u $1 -p $2
done < index.txt

Suppose index.txt file contains create index queries for all indexes of cluster1 and you want to create all cluster1 indexes on cluster2 then you can run this couchbase-index-query-executer.sh script on cluster2 with this index.txt file where index.txt file contains all create index commands.

system@cluster2:~ [0]$ ls
couchbase-index-query-executer.sh  index.txt
system@cluster2:~ [0]$ ./couchbase-index-query-executer.sh  username  password

Now suppose index.txt file contains drop index queries for all indexes of cluster1 and you want to drop all cluster1 indexes, then you can run this couchbase-index-query-executer.sh script on cluster1 with this index.txt file where index.txt file contains all drop index commands.

system@cluster1:~ [0]$ ls
couchbase-index-query-executer.sh  index.txt
system@cluster1:~ [0]$ ./couchbase-index-query-executer.sh  username  password

 

Leave a Reply

Your email address will not be published. Required fields are marked *