When developing data-warehouse solutions in Vertica, you want to set up some test environment. Ideally, you should have separate schema for each developer. However, it is usually NOT possible in my experience: developers and test engineers have to share very few schemas in development environment. The explanation that I usually get is that having a schema for each developer will not scale in database maintenance and administration, and there are likely some limits in Vertica’s commercial license. If that is the case, I recommend that we look into using Vertica Community Edition on Virtual Machines (VMs) for sandbox test environment, as a cheap alternative.
This post is a reminder of using Vertica’s system tables for administrating and monitoring our own tables. One common house-cleaning operation when developing/testing in Vertica is to find and drop tables that are empty (truncated) and never used again.
You might ask why the tables are not dropped directly when I truncated the table in the first place. The answer is that all those tables have some specific designs on projection segmentation and partition, and those information will be lost if I drop the tables. These tables are frequently populated with data and cleared for testing purposes, and truncating and inserting with
direct hint will give a significant performance boost (see Best practices).
Some tips on using command-line tool
vsql for connecting and interacting with Vertica database.
This post lists some tips and tricks that I learnt when working with Vertica database.
This post is about HiveQL’s parts used to put data into tables and extract data from tables to the filesystem.
In addition to using third party GUI clients such as SQuirreLSQL, you can also interact with Netezza through its command line interface (CLI) tools.
These are programs that let you do useful things like importing and exporting large volumes of data, invoking Netezza from bash scripts, controlling sessions and queries, etc.
The following is a quick overview of just the
For a description of all the CLI tools, see the documentation here.
You can install the Netezza CLI tools directly onto your system by following the instructions here.
Just like any SQL dialect, NZSQL has some quirks and odd conventions which could be really confusing to new Netezza users. This post has some tips for those new users.