Digital Developer Conference on Data and AI: Essential data science, machine learning, and AI skills and certification Register for free

Perform DML Operations with CockroachDB hosted on Red Hat Marketplace

The SQL commands that deal with data manipulation are categorized under Data Manipulation Language (DML). These commands include CREATE, INSERT, UPDATE, READ & DELETE a table. In this tutorial, we demonstrate working with these commands on a CockroachDB hosted on Red Hat Marketplace using the Python runtime and a Jupyter notebook. CockroachDB is an elastic SQL database that easily scales transactions for your apps and services.

Learning objectives

When you have completed this tutorial, you will understand how to:

  • Install a CockroachDB Operator from Red Hat Marketplace on an OpenShift Cluster
  • Create a CockroachDB cluster instance
  • Create a user and database in CockroachDB
  • Perform DML operations on CockroachDB
  • Manage a CockroachDB cluster from the Cluster Overview page

Estimated time

Completing this tutorial should take about 30 minutes.

Prerequisites

  1. Red Hat Marketplace Account
  2. Red Hat OpenShift Cluster
  3. OpenShift container & kubectl CLI
  4. Access to a Jupyter Notebook. You can install a Jupyter Notebook from python-pip or use a tool such as Anaconda to open the Jupyter Notebook

Steps

Step 1: Install the CockroachDB Operator from Red Hat Marketplace on OpenShift Cluster

Follow the steps in this tutorial to deploy a CockroachDB Operator from Red Hat Marketplace on an OpenShift Cluster: Get started using a CockroachDB Operator hosted on Red Hat Marketplace

Once you have successfully set up a CockroachDB Operator on an OpenShift Cluster you can create a database.

Step 2: Create a database in CockroachDB

Now, let’s create a user and a database.

  1. Run the following command to spin up a CockroachDB client:

     $ kubectl run -it --rm cockroach-client \
     --image=cockroachdb/cockroach \
     --restart=Never \
     --command -- \
     ./cockroach sql --insecure --host=example-cockroachdb-public.cockroachdb-test
    

    This should run the CockroachDB client and take you to a SQL Command Prompt as shown. If you don’t see a command prompt, try pressing Enter.

     root@example-cockroachdb-public.cockroachdb-test:26257/defaultdb>
    
  2. From the CockroachDB client, run the following commands:

    • Create a user maxroach as follows:

      root@example-cockroachdb-public.cockroachdb-test:26257/defaultdb> CREATE USER IF NOT EXISTS  maxroach;
        CREATE USER 1
      
        Time: 9.580878ms
        
    • Create a database bank as follows:

      root@example-cockroachdb-public.cockroachdb-test:26257/defaultdb> CREATE DATABASE bank;
        CREATE DATABASE
      
        Time: 14.449525ms
        
    • Give your user, maxroach, permission to update the database, bank as follows:

      root@example-cockroachdb-public.cockroachdb-test:26257/defaultdb> GRANT ALL ON DATABASE bank TO maxroach;
        GRANT
      
        Time: 9.308095ms
        

      At this point, you should have a user and a database.

    • Type \q to quit the client console as shown:

      root@example-cockroachdb-public.cockroachdb-test:26257/defaultdb> \q
        pod "cockroach-client" deleted
        $
        

Step 3: Access the admin console on your localhost

The following steps show you how to view the results of the commands you ran in the earlier steps via the admin console. You can access the console at localhost with port forwarding.

Note: You need to be logged in to your OpenShift Cluster with the OC login that you accessed in Step 2: Connect to the OpenShift cluster in your CLI.

  1. Run the following command to port forward 8080:

     $ kubectl port-forward example-cockroachdb-0 8080
    
     Forwarding from 127.0.0.1:8080 -> 8080
     Forwarding from [::1]:8080 -> 8080
    
  2. Visit http://localhost:8080 on your browser as shown. The page should load the cluster overview.

    cockroach-clusteroverview

  3. Click on databases to view the employees database that you created earlier.

    cockroach-databases

Step 4: Perform DML operations on CockroachDB using python runtime and Jupyter Notebook

Once you have the CockroachDB up and running and your user and database created, you can now perform DML Operations on CockroachDB in a Python runtime using a Jupyter Notebook.

  1. In your terminal, run the following command to port forward the 26257 port from the CockroachDB database instance. You will use this port in your Jupyter Notebook to establish a connection with the CockroachDB database instance.

     $ kubectl port-forward example-cockroachdb-0 26257
    
     Forwarding from 127.0.0.1:26257 -> 26257
     Forwarding from [::1]:26257 -> 26257
    
  2. Download and open the following Jupyter notebook dml-operations-with-cockroachdb.ipynb in your local machine.

  3. Select the Cell tab and click Run All.

    nb-run

    Follow the notebook instructions for more details on what is happening in each cell.

    After you execute the notebook, from your terminal, verify the table in the CockroachDB instance through the CockroachDB client.

  4. In your terminal, run the following command to spin up a CockroachDB client:

     $ kubectl run -it --rm cockroach-client \
     --image=cockroachdb/cockroach \
     --restart=Never \
     --command -- \
     ./cockroach sql --insecure --host=example-cockroachdb-public.cockroachdb-test
    
  5. This should run the CockroachDB client and take you to a SQL Command Prompt as shown. If you don’t see a command prompt, press Enter.

     root@example-cockroachdb-public.cockroachdb-test:26257/defaultdb>
    
  6. From the CockroachDB client, run the following commands to view user, database and table which was created with the Jupyter Notebook:

    • View users by running the SHOW users; command:

      root@example-cockroachdb-public.cockroachdb-test:26257/defaultdb> SHOW users;
       user_name
      `-------------`
        cpuser
        maxroach
        root
      (3 rows)
      
      Time: 3.037641ms
      
    • View databases by running the SHOW databases; command as follows:

      root@example-cockroachdb-public.cockroachdb-test:26257/defaultdb> SHOW databases;
      database_name
      `-----------------`
      bank
      defaultdb
      postgres
      system
      (4 rows)
      
      Time: 2.890031ms
    • To view the tables present in bank database, run the USE bank; command to switch to bank database. Run \d command to view the tables as follows:

      root@example-cockroachdb-public.cockroachdb-test:26257/defaultdb> USE bank;
      SET
      
      Time: 11.83841ms
      
      root@example-cockroachdb-public.cockroachdb-test:26257/bank> \d
      table_name
      `----------------------`
      accounts
      jsontbl
      test_bank_customer
      (3 rows)
      
      Time: 3.684617ms
  7. Finally, to view the accounts table, use the SELECT command:

    root@example-cockroachdb-public.cockroachdb-test:26257/defaultdb> SELECT * from accounts;
    

Summary

This tutorial showed you how to perform DML operations on CockroachDB Operator using python runtime and Jupyter Notebook.

Reference

Refer the CockroachDB documentation to learn more about the operator and its features.