Bulk Loading Large Datasets

Difficulty: Beginner

Estimated Time: 15 minutes

In this learning unit, you will:

Understand DSBulk use cases
Use DSBulk commands load, unload and count
Learn about DSBulk options -url, -k, -t, -m and more
Explore several examples of using DSBulk

In this scenario, you learned about:

DSBulk use cases
DSBulk commands load, unload and count
DSBulk options -url, -k, -t, -m and more
Several examples of using DSBulk

Your environment is currently being packaged as a Docker container and the download will begin shortly. To run the image locally, once Docker has been installed, use the commands

cat scrapbook_datastax_cassandra-intro/bulk-loading_container.tar | docker load

docker run -it /datastax_cassandra-intro/bulk-loading:

Restart Scenario

Next Scenario

Step 1 of 8

DataStax Bulk Loader

DataStax Bulk Loader (DSBulk) is an efficient, flexible, easy-to-use and free command-line utility for Apache Cassandra™ that excels at loading, unloading and counting data. You should use DSBulk to:

Load data from CSV or JSON files into the database
Unload data stored in the database into CSV or JSON files
Quickly count the number of rows in a given table

DSBulk is a good choice for small, medium and large datasets. It gets data in and out of the database significantly faster than individual INSERTs, the COPY command or other community tools. Only for very large datasets that reside in a distributed file system, a potentially faster alternative to DSBulk could be data loading with Apache Spark™.

DSBulk commands and options

DSBulk usage:

dsbulk <command> [options]

DSBulk supports the following three commands:

Command	Description
`load`	Load data from external files into Apache Cassandra™
`unload`	Unload data from Apache Cassandra™ into files
`count`	Compute statistics about a Cassandra table, such as the total number of rows and other metrics

DSBulk has dozens of options. Here are some of the most common ones:

Option	Description
`url`	URL or path of the resource(s) to read from or write to
`k`	Keyspace used for loading/unloading/counting
`t`	Table used for loading/unloading/counting
`m`	Mapping from file fields to table columns
`cl`	Consistency level to use for reading or writing (`LOCAL_ONE` is the default)
`header`	Enable or disable whether the files to read or write begin with a header line
`logDir`	Directory for logs (current directory is the default)
`query`	CQL statement to use for loading/unloading/counting
`skipRecords`	Number of non-header lines to skip when reading a file

You can learn more about these and other options from the help page:

dsbulk help

Let's get started ...

Create the keyspace and four tables using the CQL shell:

cqlsh -e "

CREATE KEYSPACE killr_video
WITH replication = {
  'class': 'NetworkTopologyStrategy', 
  'DC-Houston': 1 };

USE killr_video;

CREATE TABLE users (
  id TEXT,
  gender TEXT,
  age INT,
  PRIMARY KEY ((id))
);

CREATE TABLE movies (
  id TEXT,
  title TEXT,
  year INT,
  duration INT,
  country TEXT,
  PRIMARY KEY ((id))
);

CREATE TABLE ratings_by_user (
  user_id TEXT,
  movie_id TEXT,
  rating INT,
  PRIMARY KEY ((user_id), movie_id)
);

CREATE TABLE ratings_by_movie (
  movie_id TEXT,
  user_id TEXT,
  rating INT,
  PRIMARY KEY ((movie_id), user_id)
);"

Loading users

Our first task is to load user data from file users.csv with header fields user_id, gender and age into table users with columns id, gender and age.

Output the first five lines from the file:

head -n 5 users.csv

Verify that the table is empty:

cqlsh -e "SELECT * FROM killr_video.users LIMIT 5;"

Since the file field names and the table column names do no match exactly, we have to map fields to columns explicitly. There are many ways to do this as we demonstrate in the following examples.

Load data (name-to-name mapping):

dsbulk load -url users.csv       \
            -k killr_video       \
            -t users             \
            -header true         \
            -m "user_id=id,      \
                gender=gender,   \
                age=age"         \
            -logDir /tmp/logs

Load data (position-to-name mapping):

dsbulk load -url users.csv       \
            -k killr_video       \
            -t users             \
            -header true         \
            -m "0=id,            \
                1=gender,        \
                2=age"           \
            -logDir /tmp/logs

Load data (skip the file header and specify the column names):

dsbulk load -url users.csv       \
            -k killr_video       \
            -t users             \
            -header false        \
            -skipRecords 1       \
            -m "id, gender, age" \
            -logDir /tmp/logs

Output five rows from the table:

cqlsh -e "SELECT * FROM killr_video.users LIMIT 5;"

Loading movies

Next, load movie data from file movies.csv into table movies.

Output the first five lines from the file:

head -n 5 movies.csv

Verify that the table is empty:

cqlsh -e "SELECT * FROM killr_video.movies LIMIT 5;"

Load data:

Solution

dsbulk load -url movies.csv       \
            -k killr_video        \
            -t movies             \
            -header true          \
            -m "movie_id=id,      \
                title=title,      \
                year=year,        \
                duration=duration,\
                country=country"  \
            -logDir /tmp/logs

Output five rows from the table:

cqlsh -e "SELECT * FROM killr_video.movies LIMIT 5;"

Loading ratings

Let's load movie ratings from file ratings.csv into tables ratings_by_user and ratings_by_movie.

Output the first five lines from the file:

head -n 5 ratings.csv

Verify that the tables are empty:

cqlsh -e "SELECT * FROM killr_video.ratings_by_user LIMIT 5;"
cqlsh -e "SELECT * FROM killr_video.ratings_by_movie LIMIT 5;"

Notice that the file field names and the table column names match. We do not need to provide an explicit mapping this time.

Load data into table ratings_by_user:

dsbulk load -url ratings.csv      \
            -k killr_video        \
            -t ratings_by_user    \
            -header true          \
            -logDir /tmp/logs

Load data into table ratings_by_movie:

Solution

dsbulk load -url ratings.csv      \
            -k killr_video        \
            -t ratings_by_movie   \
            -header true          \
            -logDir /tmp/logs

Output five rows from each table:

cqlsh -e "SELECT * FROM killr_video.ratings_by_user LIMIT 5;"
cqlsh -e "SELECT * FROM killr_video.ratings_by_movie LIMIT 5;"

Unloading and counting

Finally, take a look at how commands unload and count can be used to export data from Cassandra and compute simple row counts.

Unload all rows from table ratings_by_movie:

dsbulk unload -url all_ratings    \
              -k killr_video      \
              -t ratings_by_movie \
              -header true        \
              -logDir /tmp/logs

Unload rows from table ratings_by_movie using a query:

dsbulk unload -url m267_ratings   \
              -query "            \
SELECT *                          \
FROM killr_video.ratings_by_movie \
WHERE movie_id = 'm267'"          \
              -header true        \
              -logDir /tmp/logs

Check the resulting CSV files:

head -n 5 all_ratings/*
head -n 5 m267_ratings/*

Count all rows in table ratings_by_movie:

dsbulk count  -k killr_video      \
              -t ratings_by_movie \
              -logDir /tmp/logs

Count rows in table ratings_by_movie using a query:

dsbulk count  -query "            \
SELECT *                          \
FROM killr_video.ratings_by_movie \
WHERE movie_id = 'm267'"          \
              -logDir /tmp/logs

Terminal

cd <directory>	Change directory
ls	List directory
echo 'contents' > <file>	Write contents to a file
cat <file>	Output contents of file

i	In Command Mode	Change into Insert Mode, you can now insert and edit text in the file
esc	In Insert Mode	Change into Command Mode, you can now execute commands
:wq	In Command Mode	Save and Exit

Welcome!

Bulk Loading Large Datasets

Congratulations!

You've completed the scenario!

Scenario Rating

Steps