Class: Vertica::Connection

Inherits:
Object
  • Object
show all
Defined in:
lib/vertica/connection.rb

Overview

A client for a Vertica server, which allows you to run queries against it.

Use connect to establish a connection. Then, the #query method will allow you to run SQL queries against the database. For COPY FROM STDIN queries, use the #copy method instead. You can use #interrupt to interrupt long running queries. #close will close the connection to the server.

Examples:

Running a buffered query against the database

connection = Vertica.connect(host: 'db_server', username: 'user', password: 'password', ...)
result = connection.query("SELECT id, name FROM my_table")
result.each do |row|
  puts "Row: #row['id']: #{row['name']}"
end
connection.close

See Also:

Instance Attribute Summary collapse

Instance Method Summary collapse

Constructor Details

#initialize(host: nil, port: 5433, username: nil, password: nil, database: nil, interruptable: false, ssl: false, read_timeout: 600, debug: false, role: nil, search_path: nil, timezone: nil, autocommit: false, skip_startup: false, skip_initialize: false, user: nil) ⇒ Connection

Creates a connection the a Vertica server.

Parameters:

  • host (String) (defaults to: nil) —

    The hostname to connect to. E.g. localhost

  • port (Integer) (defaults to: 5433) —

    The port to connect to. Defaults to 5433.

  • username (String) (defaults to: nil) —

    The username for the session.

  • password (String) (defaults to: nil) —

    The password for the session.

  • interruptable (true, false) (defaults to: false) —

    Whether to make this session interruptible. Setting this to true allows you to interrupt sessions and queries, but requires running a query during startup in order to obtain the session id.

  • ssl (OpenSSL::SSL::SSLContext, Boolean) (defaults to: false) —

    Set this to an OpenSSL::SSL::SSLContext instance to require the connection to be encrypted using SSL/TLS. true will use the default SSL options. Not every server has support for SSL encryption. In that case you'll have to leave this to false.

  • read_timeout (Integer) (defaults to: 600) —

    The number of seconds to wait for data on the connection. You should set this to a sufficiently high value when executing complicated queries that require a long time to be evaluated.

  • role (Array<String>, :all, :none, :default) (defaults to: nil) —

    A list of additional roles to enable for the session. See the Vertica documentation for SET ROLE.

  • timezone (String) (defaults to: nil) —

    The timezone to use for the session. See the Vertica documentation for SET TIME ZONE.

  • search_path (Array<String>) (defaults to: nil) —

    A list of schemas to use as search path. See the Vertica documentation for SET SEARCH_PATH.

  • autocommit (Boolean) (defaults to: false) —

    Enable autocommit on the session. See the Vertica documentation for more information.

  • debug (Boolean) (defaults to: false) —

    Setting this to true will log all the communication between client and server to STDOUT. Useful when developing this library.



53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
 
# File 'lib/vertica/connection.rb', line 53

def initialize(host: nil, port: 5433, username: nil, password: nil, database: nil, interruptable: false, ssl: false, read_timeout: 600, debug: false, role: nil, search_path: nil, timezone: nil, autocommit: false, skip_startup: false, skip_initialize: false, user: nil)
  reset_state
  @notice_handler = nil

  @options = {
    host: host,
    port: port.to_i,
    username: username || user,
    password: password,
    database: database,
    debug: debug,
    ssl: ssl,
    interruptable: interruptable,
    read_timeout: read_timeout,
    role: role,
    search_path: search_path,
    timezone: timezone,
    autocommit: autocommit,
  }

  boot_connection(skip_initialize: skip_initialize) unless skip_startup
end

Instance Attribute Details

#options ⇒ Hash (readonly)

The connection options provided to the constructor. See #initialize.

Returns:

  • (Hash) —

    the current value of options



25
26
27
 
# File 'lib/vertica/connection.rb', line 25

def options
  @options
end

#parameters ⇒ Hash<String, String> (readonly)

Connection parameters as provided by the server.

Returns:

  • (Hash<String, String>) —

    the current value of parameters



25
26
27
 
# File 'lib/vertica/connection.rb', line 25

def parameters
  @parameters
end

#transaction_status ⇒ :no_transaction, ... (readonly)

The current transaction state of the session. This will be updated after every query.

Returns:

  • (:no_transaction, :in_transaction, :failed_transaction) —

    the current value of transaction_status



25
26
27
 
# File 'lib/vertica/connection.rb', line 25

def transaction_status
  @transaction_status
end

Instance Method Details

#busy? ⇒ Boolean

Returns true iff the connection is in use.

Returns:

  • (Boolean) —

    Returns true iff the connection is in use.



94
95
96
 
# File 'lib/vertica/connection.rb', line 94

def busy?
  @mutex.locked?
end

#cancel

Note:

Vertica's protocol is based on the PostgreSQL protocol. This method to cancel sessions in PostgreSQL is accepted by the Vertica server, but I haven't actually observed queries actually being cancelled when using this method. Vertica provides an alternative method, by running SELECT CLOSE_SESSION(session_id) as a query on a different connection. See #interrupt.

This method returns an undefined value.

Cancels the current query.

See Also:



215
216
217
218
219
220
 
# File 'lib/vertica/connection.rb', line 215

def cancel
  conn = self.class.new(skip_startup: true, **options)
  conn.write_message(Vertica::Protocol::CancelRequest.new(backend_pid, backend_key))
  conn.write_message(Vertica::Protocol::Flush.new)
  conn.close_socket
end

#close

This method returns an undefined value.

Closes the connection to the Vertica server.



200
201
202
203
204
 
# File 'lib/vertica/connection.rb', line 200

def close
  write_message(Vertica::Protocol::Terminate.new)
ensure
  close_socket
end

#closed? ⇒ Boolean

Note:

Even if the connection is closed, it will be opened automatically if you use it.

Returns false iff the connection to the server is opened.

Returns:

  • (Boolean) —

    Returns false iff the connection to the server is opened.



89
90
91
 
# File 'lib/vertica/connection.rb', line 89

def closed?
  !opened?
end

#copy(sql, source: nil) {|io| ... } ⇒ String

Loads data into Vertica using a COPY table FROM STDIN query.

Examples:

Loading data using an IO object as source

connection = Vertica.connect(host: 'db_server', username: 'user', password: 'password', ...)
File.open("filename.csv", "r") do |io|
  connection.copy("COPY my_table FROM STDIN ...", source: io)
end

Loading data using a filename as source

connection = Vertica.connect(host: 'db_server', username: 'user', password: 'password', ...)
connection.copy("COPY my_table FROM STDIN ...", source: "filename.csv")

Loading data using a callback

connection = Vertica.connect(host: 'db_server', username: 'user', password: 'password', ...)
connection.copy("COPY my_table FROM STDIN ...") do |io|
  io.write("my data")
  io.write("more data")
end

Parameters:

  • sql (String) —

    The COPY ... FROM STDIN SQL command to run.

  • source (String, IO) (defaults to: nil) —

    The source of the data to be copied. This can either be a filename, or an IO object. If you don't specify a source, you'll need to provide a block that will provide the data to be copied.

Yields:

  • A block that will be called with a writer that you can provided data to. If an exception is raised in the block, the COPY command will be cancelled.

Yield Parameters:

  • io (:write) —

    An object that you can call write on to provide data to be loaded.

Returns:

  • (String) —

    The kind of command that was executed on the server. This should always be "COPY".

See Also:



179
180
181
182
183
184
185
186
187
188
189
 
# File 'lib/vertica/connection.rb', line 179

def copy(sql, source: nil, &block)
  copy_handler = if block_given?
    block
  elsif source && File.exist?(source.to_s)
    lambda { |data| file_copy_handler(source, data) }
  elsif source.respond_to?(:read) && source.respond_to?(:eof?)
    lambda { |data| io_copy_handler(source, data) }
  end

  run_in_mutex(Vertica::Query.new(self, sql, copy_handler: copy_handler))
end

#inspect ⇒ String

Returns a user-consumable string representation of this row.

Returns:

  • (String) 


193
194
195
196
 
# File 'lib/vertica/connection.rb', line 193

def inspect
  safe_options = @options.reject { |name, _| name == :password }
  "#<Vertica::Connection:#{object_id} @parameters=#{@parameters.inspect} @backend_pid=#{@backend_pid}, @backend_key=#{@backend_key}, @transaction_status=#{@transaction_status}, @socket=#{@socket}, @options=#{safe_options.inspect}>"
end

#interrupt

This method returns an undefined value.

Interrupts this session to the Vertica server, which will cancel the running query.

You'll have to call this method in a separate thread. It will open up a separate connection, and run SELECT CLOSE_SESSION(current_session_id) to close the current session. In order to be able to do this the client needs to know its session ID. You'll have to pass interruptable: true as a connection parameter (see #initialize) to make sure the connection will request its session id, by running SELECT session_id FROM v_monitor.current_session right after the connection is opened.

See Also:



234
235
236
237
238
239
240
 
# File 'lib/vertica/connection.rb', line 234

def interrupt
  raise Vertica::Error::InterruptImpossible, "Session cannopt be interrupted because the session ID is not known!" if session_id.nil?
  conn = self.class.new(skip_initialize: true, **options)
  conn.query("SELECT CLOSE_SESSION(#{Vertica.quote(session_id)})").the_value
ensure
  conn.close if conn
end

#interruptable? ⇒ Boolean

Returns true iff the connection can be interrupted.

Connections can only be interrupted if the session ID is known, so it can run SELECT CLOSE_SESSION(session_id) using a separate connection. By passing interruptable: true as a connection parameter (see #initialize), the connection will discover its session id before you can use it, allowing it to be interrupted.

Returns:

  • (Boolean) —

    Returns true iff the connection can be interrupted.

See Also:

  • Vertica::Connection.{{#interrupt}


112
113
114
 
# File 'lib/vertica/connection.rb', line 112

def interruptable?
  !session_id.nil?
end

#on_notice(&block)

This method returns an undefined value.

Installs a hanlder for notices that may be sent from the server to the client.

You can only install one handler; if you call this method again it will replace the previous handler.



248
249
250
 
# File 'lib/vertica/connection.rb', line 248

def on_notice(&block)
  @notice_handler = block
end

#opened? ⇒ Boolean

Note:

The connection will be opened automatically if you use it.

Returns true iff the connection to the server is opened.

Returns:

  • (Boolean) —

    Returns true iff the connection to the server is opened.



83
84
85
 
# File 'lib/vertica/connection.rb', line 83

def opened?
  @socket && @backend_pid && @transaction_status
end

#query(sql) ⇒ Vertica::Result #query(sql) {|row| ... } ⇒ String

Runs a SQL query against the database.

Overloads:

  • #query(sql) ⇒ Vertica::Result
    Note:

    This requires the entire result to be buffered in memory, which may cause problems for queries with large results. Consider using the unbuffered version instead.

    Runs a query against the database, and return the full result as a Result instance.

    Parameters:

    • sql (String) —

      The SQL command to run.

    Returns:

    Raises:

  • #query(sql) {|row| ... } ⇒ String

    Runs a query against the database, and yield every row to the provided block.

    Parameters:

    • sql (String) —

      The SQL command to run.

    Yields:

    • The provided block will be called for every row in the result.

    Yield Parameters:

    Returns:

    • (String) —

      The kind of command that was executed, e.g. "SELECT".

    Raises:

See Also:



145
146
147
 
# File 'lib/vertica/connection.rb', line 145

def query(sql, &block)
  run_in_mutex(Vertica::Query.new(self, sql, row_handler: block))
end

#ready_for_query? ⇒ Boolean

Returns true iff the connection is ready to handle queries.

Returns:

  • (Boolean) —

    Returns true iff the connection is ready to handle queries.



99
100
101
 
# File 'lib/vertica/connection.rb', line 99

def ready_for_query?
  !busy?
end

#ssl? ⇒ Boolean

Returns true iff the connection is encrypted.

Returns:

  • (Boolean) —

    Returns true iff the connection is encrypted.



77
78
79
 
# File 'lib/vertica/connection.rb', line 77

def ssl?
  Object.const_defined?('OpenSSL') && @socket.kind_of?(OpenSSL::SSL::SSLSocket)
end