Steve Martinelli

1 minute read

Datastax is an Apache Cassandra based database that is available through IBM Cloud. See the Catalog and Docs for more details.. Below is a write up that explains how to provision an instance of the database and how to use the cql command line tool to seed the database with a CSV file.

databases Figure showing the different types of databases available on IBM Cloud

Provision Datastax on IBM Cloud

  1. Find the service in the catalog

  2. Once provisioned, you must set an admin password!

    password

  3. Download the credentials, they come in a zip file and include information about the hostname and certificates you can use for SSL based auth.

    credentials

Use cql to auth

  1. The recommended CLI to use is cql. Download the version that corresponds to the version of the server you created. In my case it was 6.8.

    cqlsh download

  2. cql downloads as cqlsh-6.8.5-bin.tar.gz, unzip it and use it as a stand-alone CLI.

    gzip -d cqlsh-6.8.5-bin.tar.gz
tar -xvf cqlsh-6.8.5-bin.tar
cd cqlsh-6.8.5

  3. Authenticate by pointing to the credentials (with a -b flag) that you downloaded, and passing in the username/password combination.

    cqlsh-6.8.5 ./bin/cqlsh -u admin -p datastax-dev -b ~/Downloads/830e4aa3-9b95-49f0-a43c-be76c509b610-public.zip
Connected to datastax_enterprise at 127.0.0.1:9042.
[cqlsh 6.8.0 | DSE 6.8.7 | CQL spec 3.4.5 | DSE protocol v2]
Use HELP for help.
admin@cqlsh>

Seed the database

I followed the steps fom the “Import Data” Docs.

  1. Create a keyspace

    admin@cqlsh> CREATE KEYSPACE telco
WITH REPLICATION = {
  'class' : 'NetworkTopologyStrategy',
  'us-south' : 1
};

  2. Create a table. Note, I’m using data from here

    admin@cqlsh> CREATE TABLE telco.customers (
  customerID text PRIMARY KEY,
  gender text,
  SeniorCitizen text,
  Partner text,
  Dependents text,
  tenure text,
  PhoneService text,
  MultipleLines text,
  InternetService text,
  OnlineSecurity text,
  OnlineBackup text,
  DeviceProtection text,
  TechSupport text,
  StreamingMovies text,
  Contract text,
  PaperlessBilling text,
  PaymentMethod text,
  MonthlyCharges text,
  TotalCharges text,
  Churn text
);

  3. Copy the data from the CSV file

    admin@cqlsh> COPY telco.customers FROM 'Telco-Customer-Churn.csv' WITH DELIMITER=',' AND HEADER=TRUE;

    Resulted in …

    Starting copy of telco.customers with columns [customerid, churn, contract, dependents, deviceprotection, gender, internetservice, monthlycharges, multiplelines, onlinebackup, onlinesecurity, paperlessbilling, partner, paymentmethod, phoneservice, seniorcitizen, streamingmovies, streamingtv, techsupport, tenure, totalcharges].
Processed: 7043 rows; Rate:    2691 rows/s; Avg. rate:    2182 rows/s
7043 rows imported from 1 files in 3.227 seconds (0 skipped).

Updated:

You May Also Enjoy