We are adding tools to Aginity Pro to make you more efficient when doing your job. One example of this is the Data Import wizard that is available for Redshift right now.
Using the Data Import Wizard
The data import wizard is a tool to help you take a local data file, upload it to a temporary location on the database server whether that is in the Cloud or local and then finally load the data to a table.
As of December 2019 we are only supporting uploading to Redshift. Over the next few releases we will be adding support for other database platforms.
To start the data importer go to the Tools menu as shown below.
Choosing a File - Step 1
When you start the wizard you will presented with a six-step process in which to identify a file to upload, describe the behavior you want and then perform the load to a table. In the first screen the following activities are performed.
- Pick a database connection where you will upload data to.
- Add a local file using the Add CSV file button.
- Choose a delimiter used to separate fields in the file.
- If you click the With header check box the parser will assume the first row in the file is a header if not it will treat the first row as a data row.
- You can click Update preview to show the first few rows in the file
When complete choose Next.
In the next screen, you will provide your security credentials specific to the database you are uploading to. Reference the sections below for specific information on each platform supported.
Redshift Data Upload - Step 2
When working with Amazon Redshift the file you upload will first be moved to a S3 bucket of your choosing prior to being loaded into a Redshift table. You must provide the following items.
- Your Access Key
- Your Secret Key
- The Region Endpoint
- Once you enter the first three you should be able to choose the S3 bucket to use to store data.
The second section within Redshift lets you control the behavior of the file you upload to S3.
- If you select the checkbox labeled Leave the intermediate CSV file on the cloud after importing data you will leave a version of the file on S3.
- You can enter a filename of choice for the file.
- If you want the file compressed in S3 you can choose from NONE, ZIP or GZIP options.
- You can control file behavior if the file exists. You can either overwrite or abort the process.
Please refer to the AWS Bucket Naming conventions article for proper naming standard.
Choosing Columns and Rows - Step 3
In step 3, you can choose a numeric limiter for both the number of columns you want to parse and then the number of rows to load. This step is valuable if you have very wide and/or very deep files and you want to work with less columns or less rows.
It is helpful with very large files to load the first 100 rows to ensure everything works well versus letting it run for an hour only to find out it didn’t work.
Choosing Columns to Include - Step 4
This step may seem redundant to the prior step for columns but it allows you to better see more about the columns. Let’s say you have 200 columns and you know you only want the first 10 in Step 3 you can choose columns 1 to 10 and in Step 4 you can better preview and determine the order of the columns you want to load.
Configuring Output Columns - Step 5
The next step lets you control the column name, data type, and nullability.