Personal Programming Notes

To err is human; to debug, divine.

Using Virtual Machine for ETL Testing

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.

Vertica Tip: Find Empty Tables

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

Netezza CLI Tools

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 nzsql and nzload commands. 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.

NZSQL Tips for New Netezza Users

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.