In this post, I am going to show you how to import data using Aginity workbench. Data import in Aginity workbench has fairly easy method to get data into Netezza system for those who are not familiar with nzload and external table options. Netezza import is one of important task in Neteza data warehouse.
Aginity workbench is an easy-to-use application that enhances performance and creates efficiencies when working with MPP data warehouse appliances. Aginity workbench provides a powerful set of GUI-based tools for developers, DBAs, and data analysts. Import is one very easy and important tool that is available in Aginity.
Aginity workbench provides couple of options to import data from Excel, CSV, Fixed width and external databases.
Option 1: Import Data using Aginity -Tools Menu
Click on the “Tools” menu, select “Import” option and select your source system.
The Aginity Workbench supports source file import for the following formats:
- Microsoft Office Excel
- Comma Separated Values (CSV)
- Fixed Column Width File
- External Database, Oracle, SQL Server etc
After you select source file data import wizard will be displayed. You can specify the data import options at this stage.
Below are the 5 steps that included in import wizard
- Specifying the file encoding and field delimiter in case of CSV files, or the column widths and file encoding in case of fixed-width columns.
- You can specify the data range
- Specify if first column is header i.e. you want to exclude it while loading data to table.
- Specifying the column data types. Import wizard will automatically identify by this time but you can always override the identified types
- Specifying the destination database and table
You should follow below steps while importing data to Netezza
- Select source file if source folder has more than one file
- Select particular worksheet while importing from excel worksheets
- Import would work better if there no embedded delimiter in files, such a delimiter always confuses the Netezza system
Option 2: Import Data using Aginity – Object Browser Option
This option is very easy and efficient way of importing data from external flat files. Only CSV files are supported in this option. You can even import the data with embedded delimiter with in this import wizard.
To use this option, go to databases on the object browser -> expand particular database where you want to import -> find the table name
Right click on table where you want to import data and select import option.
Options window will pop-up. Change the options as per your requirements. Most of the external table options are available in this option wizard.
Note: You should keep file in local directory. If you try to import data from file which is on shared drive then you may end up with error.
Click “OK” button to start data upload.
External Table Script
Click on the SQL table to check out underlying Netezza external table script. You may use this external table script to load data to specified table. The external table looks like below:
Note: You should provide all column names along with data type so after EXTERAL keyword to use external table. Netezza will not identify column available in file if you forget mention column lists.
Congratulation!! You have just imported data from Aginity workbench. Feel free to share your comments and experience 🙂