This mode allows you to specify a set of SQL instructions inside a text file. You can then load the file into the server and allow the instructions defined in the file to be executed sequentially.
Batch mode is very useful, especially when automating a particular SQL task on multiple servers.
In Apache Cassandra, you have two methods of executing multiple instructions simultaneously:
- The first is called “batch execution,” which differs from the SQL batch mode.
- The second is the source command, which is similar to the SQL batch mode. This command allows you to define a set of CQL instructions in a file and load them into the server.
The commands in the file will be executed, and the corresponding output will be displayed in the standard output.
Let us explore how we can use this function.
Command Syntax
The following shows the command syntax:
The command accepts the path to the file you wish to execute as the parameter. Keep in mind that the file path must be a relative path. Absolute paths are not supported. It is also good to enclose the file path in quotation marks.
Create CQL Script
The first step is to create a script containing a set of CQL instructions.
For illustration purposes, we will create a keyspace, switch to the created keyspace, create a table, and insert sample data. We will also enable the CQLSH expand mode and select all the rows from the created table.
Start by creating a CQL file:
Next, we can add the instructions we wish to execute to the file, as shown below:
create keyspace zero_day
with replication = {
'class': 'SimpleStrategy',
'replication_factor': 1
};
use zero_day;
create table records(
id int,
cve_number text,
report_date date,
affected_vendor text,
severity float,
primary key(id)
);
begin batch
insert into records(id, cve_number, report_date, affected_vendor, severity)
values (1, 'CVE-2021-33852', '2022-12-02', 'WordPress', 6.0);
insert into records(id, cve_number, report_date, affected_vendor, severity)
values (2, 'CVE-2020-14723', '2020-01-11', 'Oracle', 8.2);
insert into records(id, cve_number, report_date, affected_vendor, severity)
values (3, 'CVE-2019-19306', '2019-10-14', 'Zoho', 5.4);
insert into records(id, cve_number, report_date, affected_vendor, severity)
values (4, 'CVE-2021-33850', '2021-10-18', 'Microsoft', 5.0);
insert into records(id, cve_number, report_date, affected_vendor, severity)
values (5, 'CVE-2020-24600', '2020-07-01', 'Shilpi', 8.6);
apply batch;
expand on;
select * from zero_day.records;
Save and close the file.
Execute the Script
To execute the script, log in to the server:
Finally, use the source command to execute the script as:
Executing the previous command will load the instructions in the script file and return the output (where available) to the shell.
An example output is shown below:
In our script, only the expand and select commands returns an output, as shown above.
Conclusion
In this post, we learned how to use the source command in Cassandra. The source command enables us to define CQL instructions in a file and execute them sequentially in the CQL Shell.
