Vertica ↑

Vertica is a pure Ruby library for connecting to Vertica databases. You can learn more about Vertica at www.vertica.com.

Installation ↑

Add it to the Gemfile of your project:

gem 'vertica', '~> 1.0'
# gem 'vertica', git: 'git://github.com/wvanbergen/vertica.git' # HEAD version

Now, run bundle install.

Compatiblity ↑

Usage ↑

See the API Documentation for a full reference of the API. Examples of basic use cases are below

Connecting ↑

The Vertica.connect methods takes keyword arguments and returns a connection instance. For most options, the gem will use a default value if no value is provided.

connection = Vertica.connect(
  host:     'db_server',
  username: 'user',
  password: 'password',

  # ssl:           false, # use SSL for the connection
  # port:          5433,  # default Vertica port: 5433
  # database:      nil,   # there is only one database
  # role:          nil,   # the (additional) role(s) to enable for the user.
  # search_path:   nil,   # default: <user>,public,v_catalog
  # timezone:      nil,   # the timezone for the connection to convert timestamps
  # autocommit:    false, # automatically commit INSERT/UPDATE/DELETE queries
  # interruptable: false, # set to true to allow sessions to be interrupted.
  # read_timeout:  600,   # timeout in seconds when reading data
  # debug:         false, # print all the messages back and forth to STDOUT.
)

Running queries ↑

You can run queries using the query method, either in buffered and unbuffered mode. For large result sets, you probably do not want to use buffered results, because buffering the entire result may require a lot of memory.

Get all the result rows without buffering by providing a block:

connection.query("SELECT id, name FROM my_table") do |row|
  puts row['id']   # => 123
  puts row['name'] # => 'Unicorn'
end

Note: you can only use the connection for one query at the time. If you try to run another query when the connection is still busy delivering the results of a previous query, a Vertica::Error::SynchronizeError will be raised. Use buffered resultsets to prevent this problem.

Store the result of the query method as a variable to get a buffered resultset:

result = connection.query("SELECT id, name FROM my_table")
connection.close # buffered result will be available even after closing the connection.

result.size # => 2

result.each do |row|
  puts "Hello #{row['name']}, your ID is #{row['id']}."
end

Rows are provided as Vertica::Row instances. You can access the individial fields by referring to their name as String or Symbol, or the index of the field in the result.

result.each do |row|
  p row # => Vertica::Row[123, "Unicorn"]>

  puts row['id'], row[:id], row[0]     # Three times 123
  puts row['name'], row[:name], row[1] # Three times 'Unicorn'
end

Loading data into Vertica using COPY statements ↑

Using the COPY FROM STDIN statement, you can load arbitrary data from your ruby script to the database.

connection.copy("COPY table FROM STDIN ...") do |stdin|
  File.open('data.tsv', 'r') do |f|
    begin
      stdin << f.gets
    end until f.eof?
  end
end

You can also provide a filename or an IO object:

connection.copy("COPY table FROM STDIN ...", source: "data.csv")
File.open('file.csv') do |io|
  connection.copy("COPY table FROM STDIN ...", source: io)
end

For more information, see the Vertica documentation

Interrupting sessions ↑

connection = Vertica.connect(host: 'dbserver', ...)

Thread.new do
  sleep(60)
  connection.interrupt
end

begin
  result = connection.query('SELECT complicated_query FROM huge_table')
rescue Vertica::Error::ConnectionError
  # ...
end

About ↑

This package is MIT licensed. See the LICENSE file for more information.

Development ↑

This project comes with a test suite. The unit tests in /test/unit do not need a database connection to run, the functional tests in /test/functional do need a working database connection. You can specify the connection parameters by copying the file /test/connection.yml.example to /test/connection.yml and filling out the necessary fields.

The /vagrant folder contains a Vagrantfile and a setup script to help you set up a development database that you can run the functional test suite against. The full test suite is also run by Travis CI against Vertica 7 CE, and against several Ruby versions.

Authors ↑

See also ↑