Both Netezza and Teradata are MPP (massively parallel processing) data warehouse appliance with shared nothing architecture where each processing unit is self contained with CPU, memory, and storage. The database gets subdivided across these processing units. In this article is all about Netezza and Teradata comparison – Netezza vs Teradata. The comparison Between Netezza and Teradata gives you little bit Architectural difference and SQL differences.
Netezza and Teradata Comparison – Netezza vs Teradata
Below are the some of the Netezza and Teradata comparison:
Materialized view
Both data warehouse appliance supports the materialized views and there are some differences.
Teradata
Join index – Materialized views in Teradata are called join index. The join index can contain multiple tables.
There are 3 types of join index, single table, multi-table and aggregated join index.
Netezza
Netezza support simple materialized view with single table in from clause and not expression as column.
Read: Working with Materialized view in Netezza and Examples.
Distribution
Both server distribute the data evenly to achieve the MPP.
Teradata
Just like Netezza, Teradata also supports the distribution of data on column across different AMP’s. There are two type of distribution; Unique Primary index and primary index.
Netezza
Netezza uses the DISTRIBUTE ON clause to specify the distribution key column.
Read: Working with Distribution key in Netezza.
Both uses hash algorithms to distribute data across disks associated with its processing units.
Statistics
Both appliances uses statistics to speed up the data retrieval mechanism. There is command difference,
Teradata
Teradata uses the command COLLECT STATISTICS to collect the statistics on the table which is loaded.
Netezza
Teradata uses the command GENERATE STATISTICS to collect the statistics on the table which is loaded.
Read: Generate Statistics in Netezza and Examples.
Compression
Both servers supports compression of data but in different ways.
Teradata
Teradata supports the compression of the data. You need to specify compression on the column while creating tables. This reduces the disk space.
Netezza
Netezza will apply the compression automatically based on data types. For e.g. Integer data type is best suited as compression works best on this data types.
Read: Different Netezza Data Types and Examples
Architecture difference
Both Data warehouse appliance uses the massively parallel processing (MPP) concept to speed up data retrieval. However, there are differences between both appliances:
Teradata
The processing unit is called AMP in Teradata. The Teradata can be scaled by adding more nodes as and when required.
Netezza
Processing unit in Netezza is called SPU (snippet processing unit). There is no node concept in Netezza.
Read: Netezza TwinFin Architecture.
Macro concept
Teradata
Teradata supports macro concept where you can club the multiple sql statements and execute them. Macros can accept the parameter just like procedure.
Netezza
You have to write the functions explicitly in Netezza supported languages such as NZLUA, C++ etc. There is no concept of macro in Netezza.
Read: Working with functions in Netezza and working examples.
Organizing data
Both data warehouse appliance supports organizing data.
Teradata
Teradata uses secondary index to speed up the data retrieval when using WHERE condition
Netezza
Netezza uses Organize on clause to organize data.
Read: Netezza Organize on clause and Examples.
Stored Procedure Support
Both data warehouse appliance support stored procedure. Netezza stored procedure is not matured enough compared to Teradata.
Zone mapes
Teradata
Teradata supports Partitioned Primary index which is similar to zone maps in Netezza. When rows are inserted into a table, they are stored in an AMP and arranged by their row hash order. When a table is defined with PPI, the rows are sorted by their partition number. This process avoids the full table scan and improves the performance.
Netezza
support the Zone maps.
Read: Netezza zone maps.
Data mirroring
Both appliances supports mirroring of the data stored in it to provide high availability in case of processing unit failure.
Teradata
Teradata uses the concept called Fallback to store rows into another AMP so that secondary data can be used in case of primary AMP failure.
Netezza
Netezza also supports the high availability of data by copying data to other disk on different SUP.
Data Loading
Both data warehouse appliance supports the fast data loading utilities.
Teradata
Teradata supports fastload, multiload and fast export utilities to import and export data.
Netezza
Netezza supports nzload, external table to import and export data.
Read: