How FPGA Works When you run SQL query in Netezza?

The FPGA is a the heart of Netezza’s patented streaming architecture and is made of a result of framework  of FPGA Accelerated streaming technology (FAST) engine.

Read:

• FPGA-Accelerated Streaming Technology (FAST) Framework in Netezza
• Netezza Data Loading Guide
• Netezza Architecture
• Slowly Changing Dimensions (SCD)
• Netezza Skew and how to avoid it
• Importance of right Distribution key in Netezza 
• Design and Implement Slowly Changing Dimension Type 2 (SCD Type2) in SQL

The performance multiplying effect of the FPGA FAST engine: Control, Parse, Visibility, Project and Restrict is shown with help of small example of processing SQL query in Netezza.

Consider following simple query in Netezza:

Select state, gender, age, count(*)

From Fact_table

Where dob > '12/31/1999'  and dob < '04/01/2000' And zip = 32605

Group by state, gender, age;

Below are the steps followed by the FPGA FAST framework to process this query;

Step 1: FPGA uncompress the data by referring the instruction set that is maintained during compress process.

Step 2: Storage manager and FPGA uses Zone Maps to limit the disk read to only those disk extents on each SPU with dates of birth occurring in the three-month period of January through March 2000, instead of scanning the entire table.

Step 3: When the data was read from the disk, the FPGA would apply the Visibility Engine to restrict the records being investigated to only those that met the ACID isolation requirements.

Step 4: It will further restrict the rows of data to those records within the three-month range and a zip code matching to 32605.

Step 5: Finally, projection engine filter out all columns, are streamed data to the memory and CPU would be limited to only state, gender and age information of each record. Project engine filters out all columns and provides only 2-3% of columns to CPU and memory.