Vertica Management Tools

This blog post in my Vertica series is about the various management tools available to a Vertica user.  We briefly surveyed some of these tools in previous posts.  If you've been following this series we now have [[installing Vertica 7|Vertica installed]], we've installed the [[creating a vertica database|VMart database using admintools]], and we've installed the [[installing vertica management console|Management Console GUI]] tool.  Let's cover these tools and a few others in greater detail.  

admintools

I already briefly covered admintools when we looked at [[creating a Vertica database]].  The Administration Tools provide a GUI-like experience (on Linux you can get a GUI-like appearance...if you don't mind your tool looking like WordPerfect 5.1 for DOS with no mouse support) without requiring a windowing manager like KDE or Gnome.  Most Linux folks hate running GUIs on servers...it's just more stuff that will crash the OS and drain performance.  Admintools can be launched from a shell (ssh, telnet, xterm) by running the following commands:

1. Ensure you are logged in as dbadmin.  If you attempt to use your root-level account you'll most likely get the error noted to the right.  
2. /opt/vertica/bin/admintools

However, admintools, itself, is scriptable.  Running admintools -a will show you everything you can do with admintools.  Here are some interesting things you can script like this:  

1. create a new database (perhaps as part of a CI loop):   /opt/vertica/bin/admintools --tool create_db -s (nodeIPs) -d <dbname> 
2. start a database:   /opt/vertica/bin/admintools --tool start_db -d<dbname>
3. connect to a database (this essentially starts vsql):   /opt/vertica/bin/admintools --tool connect_db -d <dbname>

Management Console (MC)

I already covered quite a bit on MC in [[Installing Vertica Management Console]].  MC doesn't do everything admintools does (just like SSMS doesn't do everything TSQL does) but it is a great GUI tool where GUI tools are really needed and aren't cumbersome...such as detailed performance monitoring graphs).  

Database Designer (DD)

Database Designer can run from MC, admintools, or can be scripted stand-alone. It can be run by admins all the way down to basic reporting users.  A report developer may want to run this, for instance, to ensure that a new query has the correct projections available for performance reasons, just like a good SQL Server developer would ensure that their query plans are using the correct indexes.  DD functionality will be different based on the login. For instance, you may be able to create a new design, but not execute it.  DD's purpose is to analyze your logical schema, sampledata, and your sample queries and then create a physical schema design (basically projections) that can be deployed or scripted. These designs are optimized for column-oriented storage.

Your designs can create new projections for your tables based on new sample data or you can pass it queries and it will create designs for you based on that workload. The deployment scripts can even be designed on a dev instance that is k-safe=0 and deployed on a higher k-safety instance.  

In the next post we'll take a [[Database Designer Deep Dive|deeper dive]] into Database Designer.  

vsql

vsql is Vertica's shell-based query tool.  It's functionally similar to osql or sqlcmd in SQL Server, or sqlplus in Oracle.  It does not run on Windows.  If you need a query tool for Windows the suggestion is to use ssh or a third party querying tool.  There is also no GUI-based querying tool such as SSMS.  This is a foreign concept to SQL Server DBAs but is a standard practice for most other RDBMSs.  For instance, Oracle shipped no GUI-based query tool until Enterprise Manager in 8.1.  Prior to that most people used Toad.  Here's how you use ssh and vsql.  

1. connect to your server with ssh as dbadmin or an equivalent user
2. /opt/vertica/bin/vsql
3. enter your VMart password
4. use \q to quit

Even though you can't install vsql on Windows you can install it quickly on another Linux box.  

1. Ensure your Linux box is the same version as your Vertica box...same distro...etc.  
2. scp <remotehost>:/opt/vertica/bin/vsql .$ ./vsql

You can also download the vertica .deb file and install it, WITHOUT running install_vertica.  

There are many other management tools such as install_vertica and update_vertica that we've covered already.  There is also vbr.py which is the native backup utility we'll cover in a future post.  

One tool I wish Vertica had was a graphical, web-based querying tool integrated directly into MC.  Unfortunately this doesn't exist yet.  In a future post I'll show you my favorite 3rd party open source tool that covers this shortcoming and makes the Vertica developer's life much easier.  

Scrutinize and Diagnostics

/opt/vertica/bin/scrutinize and /opt/vertica/bin/diagnostics

These are both diagnostic tools that work similarly with similar goals.  I would've merged them into one general diagnostic reporting tool.  They work similarly to pssdiag in SQL Server.  They both collect a broad range of information from all nodes in the cluster.  The files are zipped and can be uploaded automatically by setting a switch in the call.  

You have just read "[[Vertica Management Tools]]" on davewentzel.com. If you found this useful please feel free to subscribe to the RSS feed.