Learning Local and External Tables in Netezza

When your query generates a substantial amount of results and takes over an hour to execute, consider utilizing local external tables. This technique is beneficial for queries that require large data sets.

Tool Selection

Depending on the tool you are using, you may opt for either a JDBC SQL tool such as Squirrel or an ODBC tool like Aginity.

Creating a Local External Table

    CREATE EXTERNAL TABLE ‘C:\Users\MyUser\Account dim dump.csv’
    USING ( DELIMITER ‘,’ Y2BASE 2000 ENCODING ‘internal’ REMOTESOURCE ‘JDBC’'ODBC' ESCAPECHAR ‘\’ ) AS
    select Query here with multiple complex joins
    

Temporary External Table

Remember that local external tables are temporary, and the data they contain will be removed when the table is dropped or when the session ends.

Learning Local and External Tables in Netezza

Introduction

In this guide, we will learn about local and external tables in Netezza. These table types provide a convenient way to work with data residing outside the Netezza database. We will discuss their differences, usage scenarios, and how to create and manage them.

Local Tables

A local table is a database object that maps to an external file on the Netezza server. It allows you to read data from the file without the need for any ETL (Extract, Transform, Load) process.

Creating a Local Table

  CREATE LOCAL TABLE my_local_table (
    col1 data_type,
    col2 data_type,
    ...
  )
  UNDER /path/to/myfile.csv;
  

Loading Data into a Local Table

To load data from a local table, you can use the SELECT statement with the INTO clause:

  SELECT * FROM my_local_table
    INTO table_in_db;
  

External Tables

An external table is similar to a local table, but it maps to a file stored on an external system (e.g., Hadoop or NFS). This allows you to read data from large data stores without the need for data movement.

Creating an External Table

  CREATE EXTERNAL TABLE my_external_table (
    col1 data_type,
    col2 data_type,
    ...
  )
  UNDER EXTERNAL SCHEMA my_schema@my_hdfs_location;
  

Loading Data into an External Table

To load data from an external table, you can use the SELECT statement with the INTO clause:

  SELECT * FROM my_external_table
    INTO table_in_db;
  

Summary

Both local and external tables in Netezza provide a way to work with data residing outside the database. Local tables are used for files on the Netezza server, while external tables are used for files on an external system. Understanding these table types can help you optimize your data loading process and make the most of your Netezza environment.