How to connect Go Applications to Cloudera Operational Database

[#1]

The Cloudera Operational Database (COD) experience is a managed dbPaaS solution. It can auto-scale based on the workload utilization of the cluster and will be adding the ability to auto-tune (better performance within the existing infrastructure footprint) and auto-heal (resolve operational problems automatically) later this year. 

COD is backed by the proven, scalable technology in Apache HBase and Apache Phoenix.

There is an excellent blog post showing the basics of setting up a COD database and connecting to it using Python.

COD offers options to connect using both HBase APIs and ANSI SQL through Apache Phoenix. This article shows how to connect to a COD database through Phoenix using Golang. 

All of the code for this article can be found in this Github repository.

1. Getting started with COD is incredibly easy - no clusters to deploy and secure, simply log in and click “Create Database”:
   
2. Select your CDP Environment and enter a name for the new database and click “Create Database”:
   
3. Once the database is deployed and you will see the connection details for the database - for Golang and the calcite-avatica-go driver, we need the connection URL from the Phoenix (Thin) tab:
4. Set your CDP workload password.
5. From here, connecting to COD is just like any other Go sql.DB interface - provide a DSN including the database URL and credentials, and interact using ANSI SQL.

Below we see a simple example of connecting, creating a table, inserting a row, and reading back that row.

package main

import (
	"database/sql"
	"log"

	_ "github.com/apache/calcite-avatica-go"
)

func main() {
	// Connections are defined by a DSN
	// The format is http://address:port[/schema][?parameter1=value&...parameterN=value]
	// For COD, BASIC authentication is used.
	// The workload username and password are passed as parameters avaticaUser and avaticaPassword
	//
	// For example:
	// COD URL: 'https://gateway.env.cloudera.site/cdp-proxy-api/avatica/'
	// Workload username: jgoodson
	// Workload password: Secret1!
	// Would result in this DSN:
	dsn := "https://gateway.env.cloudera.site/cdp-proxy-api/avatica/?&authentication=BASIC&avaticaUser=jgoodson&avaticaPassword=Secret1!"

	log.Println("Connecting...")
	db, err := sql.Open("avatica", dsn)
	if err != nil {
		log.Fatal("Connection: ", err)
	}
	defer db.Close()

	log.Println("Create table if not exists...")
	_, err = db.Exec("CREATE TABLE IF NOT EXISTS users (id INTEGER PRIMARY KEY, username VARCHAR)")
	if err != nil {
		log.Fatal("Create: ", err)
	}

	log.Println("Insert a row...")
	_, err = db.Exec("UPSERT INTO users VALUES (?, ?)", 1, "admin")
	if err != nil {
		log.Println("Insert: ", err)
	}

	log.Println("Reading and printing rows...")
	var (
		id       int
		username string
	)
	rows, err := db.Query("SELECT id, username from users")
	if err != nil {
		log.Fatal("Query: ", err)
	}
	defer rows.Close()
	for rows.Next() {
		err := rows.Scan(&id, &username)
		if err != nil {
			log.Fatal(err)
		}
		log.Println(id, username)
	}
}

Have fun building your own applications in Golang to interact with Cloudera Operational Database!