Popularity
1.3
Growing
Activity
5.0
Growing
4
4
3

Description

vertica-sql-go is a native Go adapter for the Vertica (https://www.vertica.com) database.

Please check out release notes to learn about the latest improvements.

vertica-sql-go is currently in alpha stage; it has been tested for functionality and has a very basic test suite. Please use with caution, and feel free to submit issues and/or pull requests (Read up on our contributing guidelines).

vertica-sql-go has been tested with Vertica 9.2.0+ and Go 1.11.2.

Programming language: Go

vertica-sql-go alternatives and similar packages

Based on the "Relational Databases" category

Do you think we are missing an alternative of vertica-sql-go or a related project?

Add another 'Relational Databases' Package

README

vertica-sql-go

License GoDoc Go Report Card

vertica-sql-go is a native Go adapter for the Vertica (http://www.vertica.com) database.

Please check out release notes to learn about the latest improvements.

vertica-sql-go is currently in alpha stage; it has been tested for functionality and has a very basic test suite. Please use with caution, and feel free to submit issues and/or pull requests (Read up on our contributing guidelines).

vertica-sql-go has been tested with Vertica 9.2.0+ and Go 1.11.2.

Release Notes

  • As this driver is still in alpha stage, we reserve the right to break APIs and change functionality until it has been stablilized.

Installation

Source code for vertica-sql-go can be found at:

https://github.com/vertica/vertica-sql-go

Alternatively you can use the 'go get' variant to install the package into your local Go environment.

go get github.com/vertica/vertica-sql-go

Usage

As this library is written to Go's SQL standard database/sql, usage is compliant with its methods and behavioral expectations.

Importing

First ensure that you have the library checked out in your standard Go hierarchy and import it.

import (
    "context"
    "database/sql"
    "github.com/vertica/vertica-sql-go"
)

Setting the Log Level

The vertica-sql-go driver supports multiple log levels, as defined in the following table

Log Level (int) Log Level Name Description
0 TRACE Show function calls, plus all below
1 DEBUG Show low-level functional operations, plus all below
2 INFO Show important state information, plus all below
3 WARN (default) Show non-breaking abnormalities, plus all below
4 ERROR Show breaking errors, plus all below
5 FATAL Show process-breaking errors
6 NONE Disable all log messages

and they can be set programatically by calling the logger global level itself

logger.SetLogLevel(logger.DEBUG)

or by setting the environment variable VERTICA_SQL_GO_LOG_LEVEL to one of the integer values in the table above. This must be done before the process using the driver has started as the global log level will be read from here on start-up.

Creating a connection

connDB, err := sql.Open("vertica", myDBConnectString)

where myDBConnectString is of the form:

vertica://(user):(password)@(host):(port)/(database)?(queryArgs)

Currently supported query arguments are:

Query Argument Description Values
use_prepared_statements whether to use client-side query interpolation or server-side argument binding 1 = (default) use server-side bindings
0 = user client side interpolation (LESS SECURE)
tlsmode the ssl/tls policy for this connection 'none' (default) = don't use SSL/TLS for this connection
'server' = server must support SSL/TLS, but skip verification (INSECURE!)
'server-strict' = server must support SSL/TLS

To ping the server and validate a connection (as the connection isn't necessarily created at that moment), simply call the PingContext() method.

ctx := context.Background()

err = connDB.PingContext(ctx)

If there is an error in connection, the error result will be non-nil and contain a description of whatever problem occurred.

Performing a simple query

Performing a simple query is merely a matter of using that connection to create a query and iterate its results. Here is an example of a query that should always work.

rows, err := connDB.QueryContext(ctx, "SELECT * FROM v_monitor.cpu_usage LIMIT 5")

defer rows.Close()

IMPORTANT : Just as with connections, you should always Close() the results cursor once you are done with it. It's often easier to just defer the closure, for convenience.

Performing a query with arguments

This is done in a similar manner on the client side.

rows, err := connDB.QueryContext(ctx, "SELECT name FROM MyTable WHERE id=?", 21)

Behind the scenes, this will be handled in one of two ways, based on whether or not you requested client interpolation in the connection string.

With client interpolation enabled, the client library will create a new query string with the arguments already in place, and submit it as a simple query.

With client interpolation disabled (default), the client library will use the full server-side parse(), describe(), bind(), execute() cycle.

Reading query result rows.

As outlined in the GoLang specs, reading the results of a query is done via a loop, bounded by a .next() iterator.

for rows.Next() {
    var nodeName string
    var startTime string
    var endTime string
    var avgCPU float64

    rows.Scan(&nodeName, &startTime, &endTime, &avgCPU)

    // Use these values for something here.
}

If you need to examine the names of the columns, simply access the Columns() operator of the rows object.

columnNames, _ := rows.Columns()

for _, columnName := range columnNames {
        // use the column name here.
}

Performing a simple execute call

This is very similar to a simple query, but has a slightly different result type. A simple execute() might look like this:

res, err = connDB.ExecContext(ctx, "DROP TABLE IF EXISTS MyTable")

In this instance, res will contain information (such as 'rows affected') about the result of this execution.

Performing an execute with arguments

This, again, looks very similar to the query-with-arguments use case and is subject to the same effects of client-side interpolation.

res, err := connDB.ExecContext(
        ctx,
        "INSERT INTO MyTable VALUES (?)", 21)

Server-side prepared statements

IMPORTANT : Vertica does not support executing a command string containing multiple statements using server-side prepared statements.

If you wish to reuse queries or executions, you can prepare them once and supply arguments only.

// Prepare the query.
stmt, err := connDB.PrepareContext(ctx, "SELECT id FROM MyTable WHERE name=?")

// Execute it with this argument.
rows, err = stmt.Query("Joe Perry")

NOTE : Please note that this method is subject to modification by the 'interpolate' setting. If the client side interpolation is requested, the statement will simply be stored on the client and interpolated with arguments each time it's used. If not using client side interpolation (default), the statement will be parsed and described on the server as expected.

Transactions

The vertica-sql-go driver supports basic transactions as defined by the GoLang standard.

// Define the options for this transaction state
opts := &sql.TxOptions{
    Isolation: sql.LevelDefault,
    ReadOnly:  false,
}

// Begin the transaction.
tx, err := connDB.BeginTx(ctx, opts)
// You can either commit it.
err = tx.Commit()
// Or roll it back.
err = tx.Rollback()

The following transaction isolation levels are supported:

  • sql.LevelReadUncommitted †
  • sql.LevelReadCommitted
  • sql.LevelSerializable
  • sql.LevelRepeatableRead †
  • sql.LevelDefault

The following transaction isolation levels are unsupported:

  • sql.LevelSnapshot
  • sql.LevelLinearizable

† Although Vertica supports the grammars for these transaction isolation levels, they are internally promoted to stronger isolation levels.

Example

By following the above instructions, you should be able to successfully create a connection to your Vertica instance and perform the operations you require. A complete example program is listed below:

package main

import (
    "context"
    "database/sql"
    "os"

    _ "github.com/vertica/vertica-sql-go"
    "github.com/vertica/vertica-sql-go/logger"
)

func main() {
    // Have our logger output INFO and above.
    logger.SetLogLevel(logger.INFO)

    var testLogger = logger.New("samplecode")

    ctx := context.Background()

    // Create a connection to our database. Connection is lazy and won't
    // happen until it's used.
    connDB, err := sql.Open("vertica", "vertica://dbadmin:@localhost:5433/dbadmin")

    if err != nil {
        testLogger.Fatal(err.Error())
        os.Exit(1)
    }

    defer connDB.Close()

    // Ping the database connnection to force it to attempt to connect.
    if err = connDB.PingContext(ctx); err != nil {
        testLogger.Fatal(err.Error())
        os.Exit(1)
    }

    // Query a standard metric table in Vertica.
    rows, err := connDB.QueryContext(ctx, "SELECT * FROM v_monitor.cpu_usage LIMIT 5")

    if err != nil {
        testLogger.Fatal(err.Error())
        os.Exit(1)
    }

    defer rows.Close()

    // Iterate over the results and print them out.
    for rows.Next() {
        var nodeName string
        var startTime string
        var endTime string
        var avgCPU float64

        if err = rows.Scan(&nodeName, &startTime, &endTime, &avgCPU); err != nil {
            testLogger.Fatal(err.Error())
            os.Exit(1)
        }

        testLogger.Info("%s\t%s\t%s\t%f", nodeName, startTime, endTime, avgCPU)
    }

    testLogger.Info("Test complete")

    os.Exit(0)
}

License

Apache 2.0 License, please see LICENSE for details.

Contributing guidelines

Have a bug or an idea? Please see CONTRIBUTING.md for details.

Acknowledgements

We would like to thank the creators and contributors of the vertica-python library, and members of the Vertica team, for their help in understanding the wire protocol.


*Note that all licence references and agreements mentioned in the vertica-sql-go README section above are relevant to that project's source code only.