• Stars
    star
    60
  • Rank 492,218 (Top 10 %)
  • Language
    Go
  • License
    Apache License 2.0
  • Created about 5 years ago
  • Updated about 2 months ago

Reviews

There are no reviews yet. Be the first to send feedback to the community and the maintainers!

Repository Details

Official native Go client for the Vertica Analytics Database.

vertica-sql-go

License Go Reference 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 has been tested with Vertica 24.1.0 and Go 1.17/1.18/1.19/1.20/1.21.

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 programmatically 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.

Example:

export VERTICA_SQL_GO_LOG_LEVEL=3

Setting the Log File

By default, log messages are sent to stdout, but the vertica-sql-go driver can also output to a file in cases where stdout is not available. Simply set the environment variable VERTICA_SQL_GO_LOG_FILE to your desired output location.

Example:

export VERTICA_SQL_GO_LOG_FILE=/var/log/vertica-sql-go.log

Creating a connection

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

where myDBConnectString is of the form:

vertica://(user):(password)@(host):(port)/(database)[?arg1=value&...&argN=valueN]

All parameters must be escaped (to URL-encoded format). If the host is a literal IPv6 address it must be enclosed in square brackets.

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)
connection_load_balance Whether to enable connection load balancing on the client side. 0 = (default) disable load balancing
1 = enable load balancing
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
  • {customName} = use custom registered tls.Config (see "Using custom TLS config" section below)
  • backup_server_node A list of backup hosts for the client to try to connect if the primary host is unreachable. a comma-seperated list of backup host-port pairs. E.g.
    'host1:port1,host2:port2,host3:port3'
    client_label Sets a label for the connection on the server. This value appears in the client_label column of the SESSIONS system table. (default) vertica-sql-go-{version}-{pid}-{timestamp}
    autocommit Controls whether the connection automatically commits transactions. 1 = (default) on
    0 = off
    oauth_access_token To authenticate via OAuth, provide an OAuth Access Token that authorizes a user to the database. unspecified by default, if specified then user is optional
    workload Sets workload property of the session, enabling use of workload routing empty string by default. Valid values are workload names that already exist in a workload routing rule on the server. If a workload name that doesn't exist is entered, the server will reject it and it will be set to the default empty string

    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.

    Using custom TLS config

    Custom TLS config(s) can be registered for TLS / SSL encrypted connection to the server. Here is an example of registering and using a tls.Config:

    import vertigo "github.com/vertica/vertica-sql-go"
    
    // Register tls.Config
    rootCertPool := x509.NewCertPool()
    pem, err := ioutil.ReadFile("/certs/ca.crt")
    if err != nil {
        LOG.Warningln("ERROR: failed reading cert file", err)
    }
    if ok := rootCertPool.AppendCertsFromPEM(pem); !ok {
        LOG.Warningln("ERROR: Failed to append PEM")
    }
    tlsConfig := &tls.Config{RootCAs: rootCertPool, ServerName: host}
    vertigo.RegisterTLSConfig("myCustomName", tlsConfig)
    
    // Connect using tls.Config
    var rawQuery = url.Values{}
    rawQuery.Add("tlsmode", "myCustomName")
    var query = url.URL{
        Scheme:   "vertica",
        User:     url.UserPassword(user, password),
        Host:     fmt.Sprintf("%s:%d", host, port),
        Path:     databaseName,
        RawQuery: rawQuery.Encode(),
    }
    sql.Open("vertica", query.String())

    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.

    Named Arguments

    rows, err := connDB.QueryContext(ctx, "SELECT name FROM MyTable WHERE id=@id and something=@example", sql.Named("id", 21), sql.Named("example", "hello"))

    Named arguments are emulated by the driver. They will be converted to positional arguments by the driver and the named arguments given later will be slotted into the required positions. This still allows server side prepared statements as @id and @example above will be replaced by ? before being sent. If you use named arguments, all the arguments must be named. Do not mix positional and named together. All named arguments are normalized to upper case which means @param, @PaRaM, and @PARAM are treated as equivalent.

    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.
    }

    Paging in Data

    By default, the query results are cached in memory allowing for rapid iteration of result row content. This generally works well, but in the case of exceptionally large result sets, you could run out of memory.

    If such a query needs to be performed, it is recommended that you tell the driver that you wish to cache that data in a temporary file, so its results can be "paged in" as you iterate the results. The data is stored in a process-read-only file in the OS's temp directory.

    To enable result paging, simply create a VerticaContext and use it to perform your query.

    vCtx := NewVerticaContext(context.Background())
    
    // Only keep 50000 rows in memory at once.
    vCtx.SetInMemoryResultRowLimit(50000)
    
    rows, _ := connDB.QueryContext(
        vCtx,
        "SELECT a, b, c, d, e FROM result_cache_test ORDER BY a")
    
    defer rows.Close()
    
    // Use rows result as normal.

    If you want to disable paging on the same context all together, you can simply set the row limit to 0 (the default).

    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.

    COPY modes Supported

    COPY FROM STDIN

    vertica-sql-go supports copying from stdin. This allows you to write a command-line tool that accepts stdin as an input and passes it to Vertica for processing. An example:

    _, err = connDB.ExecContext(ctx, "COPY stdin_data FROM STDIN DELIMITER ','")

    This will process input from stdin until an EOF is reached.

    COPY FROM STDIN with alternate stream

    In your code, you may also supply a different io.Reader object (such as *File) from which to supply your data. Simply create a new VerticaContext, set the copy input stream, and provide this context to the execute call. An example:

    fp, err := os.OpenFile("./resources/csv/sample_data.csv", os.O_RDONLY, 0600)
    ...
    vCtx := NewVerticaContext(ctx)
    vCtx.SetCopyInputStream(fp)
    
    _, err = connDB.ExecContext(vCtx, "COPY stdin_data FROM STDIN DELIMITER ','")

    If you provide a VerticaContext but don't set a copy input stream, the driver will fall back to os.stdin.

    Full 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/db1?connection_load_balance=1")
    
        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.

    Benchmarks

    You can run a benchmark and profile it with a command like: go test -bench '^BenchmarkRowsWithLimit$' -benchmem -memprofile memprofile.out -cpuprofile profile.out -run=none

    and then explore it with go tool pprof. The -run part excludes the tests for brevity.

    Acknowledgements

    • @grzm (Github)
    • @watercraft (Github)
    • @fbernier (Github)
    • @mlh758 (Github) for the awesome work filling in and enhancing the driver in many important ways.
    • Tom Wall (Vertica) for the infinite patience and deep knowledge.
    • The creators and contributors of the vertica-python library, and members of the Vertica team, for their help in understanding the wire protocol.

    More Repositories

    1

    vertica-python

    Official native Python client for the Vertica Analytics Database.
    Python
    376
    star
    2

    VerticaPy

    VerticaPy is a Python library that exposes sci-kit like functionality to conduct data science projects on data stored in Vertica, thus taking advantage Vertica’s speed and built-in analytics and machine learning capabilities.
    Python
    218
    star
    3

    DistributedR

    R
    163
    star
    4

    Vertica-Extension-Packages

    User Defined Extensions (UDX) to the Vertica Analytic Database
    HTML
    119
    star
    5

    ddR

    Standard API for Distributed Data Structures in R
    R
    117
    star
    6

    Graph-Analytics----Triangle-Counting

    Use Big data tools such as Vertica, Hadoop and PIG to count triangles in a graph. Experimentally compare their performance.
    Java
    49
    star
    7

    vertica-kubernetes

    Operator, container and Helm chart to deploy Vertica in Kubernetes
    Go
    42
    star
    8

    Vertica-Hadoop-Connector

    Vertica Hadoop Connector
    Java
    38
    star
    9

    awesome-vertica

    A curated list of awesome Vertica libraries, tools and resources
    27
    star
    10

    dbt-vertica

    Official dbt adapter for Vertica
    Python
    25
    star
    11

    Machine-Learning-Examples

    Vertica Machine Learning examples and example data.
    Python
    24
    star
    12

    vertica.dplyr

    R
    22
    star
    13

    dblink

    A Vertica User Defined Transform function to interact with other databases via ODBC
    C++
    20
    star
    14

    spark-connector

    This component acts as a bridge between Spark and Vertica, allowing the user to either retrieve data from Vertica for processing in Spark, or store processed data from Spark into Vertica.
    Scala
    18
    star
    15

    VerticaPyLab

    Use Docker to install a simple demo Vertica.
    Jupyter Notebook
    17
    star
    16

    vertica-containers

    Docker containers for building and testing Vertica extensions
    Shell
    14
    star
    17

    UDx-Examples

    Vertica User Defined Extension (UDx) examples.
    C++
    12
    star
    18

    vertica-grafana-datasource-.2.0

    This project defines the official Vertica datasource plugin for Grafana.
    JavaScript
    12
    star
    19

    ODBC-Loader

    C++
    11
    star
    20

    vertica-nodejs

    Official native node.js client for the Vertica Analytics Database.
    JavaScript
    11
    star
    21

    PSTL

    Parallel Streaming Transformation Loader
    Java
    9
    star
    22

    vertica-prometheus-exporter

    Official open source Vertica Prometheus Exporter
    Go
    7
    star
    23

    r-dataconnector

    Distributed Data Connector R package
    C++
    7
    star
    24

    Vertica-Geospatial

    Vertica-Geospatial Examples
    HTML
    7
    star
    25

    Social-Media-Connector

    Java
    5
    star
    26

    client-application-examples

    Examples of how to write a client application to connect to vertica
    C#
    4
    star
    27

    Vertica-AMI-Packer-Templates

    Packer templates and provision scripts for creating Vertica AMIs
    Shell
    4
    star
    28

    aws-lambda-vertica-loader

    Automatic HP Vertica Database Loader for AWS S3
    JavaScript
    4
    star
    29

    hackathon

    A getting started kit for Hackathons using Vertica!
    Python
    4
    star
    30

    vertica-grafana-datasource

    Official Vertica datasource plugin for Grafana.
    TypeScript
    3
    star
    31

    grafana-dashboards

    Dashboards that can be imported into Grafana that use the Prometheus metrics Vertica exports
    Python
    2
    star
    32

    vertica.github.io

    Vertica Open Source
    HTML
    2
    star
    33

    FlexTable

    HPE Vertica Flex Table source code of VMap APIs and parsers
    C++
    2
    star
    34

    vertica-sqlalchemy-dialect

    Official Vertica SQLAlchemy dialect to connect Vertica and SQLAlchemy applications.
    Python
    2
    star
    35

    charts

    1
    star
    36

    vertica-airbyte

    Python
    1
    star
    37

    DistributedR-demos

    CSS
    1
    star
    38

    data2ddl

    command line tool guessing a table's matching CREATE TABLE statement with optimal data types from a CSV file
    C
    1
    star
    39

    integrators-guide

    A community repo for users that integrate with Vertica with provisioning, monitoring, management, storage, clients, loaders, and other tools.
    SCSS
    1
    star
    40

    vertica-testenv

    Docker-based testing environment for Vertica.
    Shell
    1
    star
    41

    vertica-highcharts

    Python
    1
    star