Netezza Tutorial

Netezza Tutorial

Last updated on 29th Sep 2020, Blog, Tutorials

About author

Sasikumar (Sr Associate Manager )

Delegates in Corresponding Technical Domain with 11+ Years of Experience. Also, He is a Technology Writer for Past 7 Years & Share's this Informative Blogs for us.

(5.0) | 15523 Ratings 1079
  • Netezza SQL is the Netezza Structured Query Language (SQL), which runs on the Netezza data warehouse appliance. Throughout this document, the term SQL refers to Netezza’s SQL implementation.
  • Netezza designs and markets high-performance data warehouse appliances and advanced analytics applications for uses including enterprise data warehousing, business intelligence, predictive analytics, and business continuity planning.
  • IBM Netezza appliances are now part of IBM PureSystems – expert integrated systems with built-in expertise, integration by design, and simplified user experience. Part of the Pure Data family, the Netezza appliance is now known as the Pure Data System for Analytics. It has the same key design tenets of simplicity, speed, scalability, and analytics power that was fundamental to Netezza appliances.
  • With the simple deployment, out-of-the-box optimization, no tuning, and minimal on-going maintenance, the IBM Pure Data System for Analytics have the industry’s fastest time-to-value and lowest total-cost-of-ownership.
  • The Database Accelerator along with the other components of the IBM Netezza appliance was discussed during a short high-level overview of the architecture. This overview was presented at the beginning of the workshop during a brief presentation. The presentation also included the basic usage of how to administer and maintain a Netezza database. The concepts covered in the presentation were reinforced by getting hands-on experience using a Netezza appliance. Instead of using an actual IBM Netezza appliance, a virtualized environment was provided with a lab manual outlining the steps and commands to run.
Subscribe For Free Demo

Error: Contact form not found.

What is Netezza used for?

The IBM Netezza appliance also includes a SQL dialect called Netezza Structured Query Language (NZSQL). You can use SQL commands to create and manage your Netezza databases, user access, and permissions for the databases, as well as to query and modify the contents of the databases.

IBM Netezza Analytics

Analytics is an embedded, purpose-built, advanced analytics platform — delivered with every IBM Netezza appliance — that empowers analytic enterprises to meet and exceed their business demands.

  1. 1. Predict with more accuracy
  2. 2. Deliver predictions faster
  3. 3. Respond rapidly to changes

IBM Netezza Analytics’ advanced technology fuses data warehousing and in-database analytics into a scalable, high-performance, massively parallel advanced analytic platform that is designed to crunch through petascale data volumes. This allows users to ask questions of the data that could not have been contemplated on other architectures. IBM Netezza Analytics is designed to quickly and effectively provide better and faster answers to the most sophisticated business questions.

IBM Netezza Analytics is IBM Netezza’s most powerful advanced analytics platform that provides the technology infrastructure to support enterprise deployment of in-database analytics. The analytics platform allows integration of its robust set of built-in analytics with leading analytic tools from such vendors as Revolution Analytics, SAS, IBM SPSS®, Fuzzy Logix, and Zementis, on IBM Netezza’s core data warehouse appliances. IBM Netezza pioneered the modern data warehouse appliance and has customers worldwide that have realized the value of combining data warehousing and analytics into a single, high-performance integrated system. IBM Netezza Analytics enables analytic enterprises to realize significant business value from new business models and helps companies realize both top-line revenue growth and bottom-line cost savings.

IBM Netezza Analytics capabilities

  • Data exploration and discovery
  • Data transformation
  • Model building
  • Model diagnostics
  • Model scoring

IBM Netezza Architecture

The IBM Netezza data warehouse appliance — a powerful parallel computing platform — is fully exploited by IBM Netezza Analytics to deliver high-speed, scalable analytics processing. The appliance uses the high-speed throughput of the Asymmetric Massively Parallel Processing (AMPP) architecture to maximize speed and efficiency for in-database analytics processing. The AMPP architecture is a blade-based streaming architecture that uses commodity blades and storage, combined with IBM Netezza’s patented data filtering using field-programmable gate arrays (FPGAs), to deliver large data, high-speed analytics. IBM Netezza has consolidated all analytics activity in a powerful and simple appliance.

Netezza-Architecture

IBM Netezza Analytics is purpose-built to simplify the building and deploying of models for analytic enterprises that demand the highest performance on large, complex volumes of data.

Easy to use

The IBM Netezza data warehouse appliance is easy-to-use and dramatically accelerates the entire analytic process. The programming interfaces and parallelization options make it straightforward to move a majority of analytics inside the appliance, regardless of whether they are being performed using tools from such vendors as IBM SPSS, SAS, or Revolution Analytics, or written in languages such as Java, Lua, Perl, Python, R or Fortran. Additionally, IBM Netezza data warehouse appliances are delivered with a built-in library of parallelized analytic functions, purpose-built for large data volumes, to kick-start and accelerate any analytic application development and deployment.

The simplicity and ease of development are what truly sets IBM Netezza apart. It is the first appliance of its kind – packing the power and scalability of hundreds of processing cores in an architecture ideally suited for parallel analytics. Instead of a fragmented analytics infrastructure with multiple systems where data is replicated, IBM Netezza Analytics consolidates all analytics activity in a powerful appliance. It is easy to deploy and requires minimal ongoing administration, for an overall low total cost of ownership.

Simplifying the process of exploring, calculating, modeling, and scoring data are key drivers for the successful adoption of analytics company-wide. With IBM Netezza, business users can run their own analytics in near real-time, which helps analytics-backed, data-driven decisions to become pervasive throughout an enterprise.

Netezza SQL functional categories

All SQL commands belong to one of the following functional categories:

  • Data Definition Language (DDL)
  • Data Control Language (DCL)
  • Data Manipulation Language (DML)
  • Transaction Control
  • Miscellaneous commands

Data Definition Language (DDL)

Use the IBM Netezza SQL Data Definition Language (DDL) to define, modify, and delete database objects, such as databases, tables, and views.

Data Control Language (DCL)

As a database security administrator, you use Data Control Language (DCL) SQL commands to control user access to database objects and their contents.

Data Manipulation Language (DML)

Use Data Manipulation Language (DML) of SQL to access and modify database data by using the select, update, insert, delete, truncate, begin, commit, and rollback commands.

Transaction Control

Transaction control enforces database integrity by ensuring that batches of SQL operations run completely or not at all. The transaction control commands are BEGIN, COMMIT, and ROLLBACK.

Functions and operators

IBM Netezza SQL provides many functions and operators. Functions are operations that take a value, whereas operators are symbols.

In many cases, you can use functions and operations to do the same task, so the difference is common with the syntax.

Netezza SQL supports the following types of functions:

Numeric

Performs mathematical operations on numeric data

Text

Manipulates strings of text

Date and time

Manipulates date and time values and extracts specific components from these values

System

Returns information specific to the RDBMS being used

Fuzzy search and phonetic matching

Provides approximate string matching that is based on defined techniques or algorithms.

User-defined

Performs actions that are defined by the function developer

Connecting to Netezza Server From Python Sample

Check out my Ipython Jupyter Notebook with Python Sample.

Step 1: Importing Python jaydebeapi library:

  • import jaydebeapi

Step 2: Setting Database connection settings:

  • dsn_database = “avkash”           
  • dsn_hostname = “172.16.181.131”
  • dsn_port = “5480”               
  • dsn_uid = “admin”       
  • dsn_pwd = “password”     
  • jdbc_driver_name = “org.netezza.Driver”
  • jdbc_driver_loc = “/Users/avkashchauhan/learn/customers/netezza/nzjdbc3.jar”
  • ###jdbc:netezza://” + server + “/” + dbName ;
  • connection_string=’jdbc:netezza://’+dsn_hostname+’:’+dsn_port+’/’+dsn_database
  • url = ‘{0}:user={1};password={2}’.format(connection_string, dsn_uid, dsn_pwd)
  • print(“URL: ” + url)
  • print(“Connection String: ” + connection_string)

Step 3: Creating Database Connection:

  • conn = jaydebeapi.connect(“org.netezza.Driver”, connection_string, {‘user’: dsn_uid, ‘password’: dsn_pwd},
  •            jars = “/Users/avkashchauhan/learn/customers/netezza/nzjdbc3.jar”)
  • curs = conn.cursor()

Step 4: Processing SQL Query:

  • curs.execute(“select * from allusers”)
  • result = curs.fetchall()
  • print(“Total records: ” + str(len(result)))
  • print(result[0])

Step 5: Printing all records:

  • for i in range(len(result)):
  •    print(result[i])

Step 6: Closing all connections:

  • curs.close()
  • conn.close()
Course Curriculum

Get Netezza Training with Industry Standard Modules By Experts Trainers

  • Instructor-led Sessions
  • Real-life Case Studies
  • Assignments
Explore Curriculum

Connecting Netezza Server With Java Code Sample

Step 1: Have the Netezza driver as nzjdbc3.jar in a folder.

Step 2: Create netezzaJdbcMain.java as below in the same folder where nzjdbc3.jar is placed.

  • import java.sql.Connection;
  • import java.sql.DriverManager;
  • import java.sql.ResultSet;
  • import java.sql.SQLException;
  • import java.sql.Statement;
  • public class netezzaJdbcMain {
  •    public static void main(String[] args) {
  •        String server = “x.x.x.x”;
  •        String port = “5480”;
  •        String dbName = “_db_name_”;
  •        String url = “jdbc:netezza://” + server + “/” + dbName ;
  •        String user = “admin”;
  •        String pwd = “password”;
  •        String schema = “db_schema”;
  •        Connection conn = null;
  •        Statement st = null;
  •        ResultSet rs = null;
  •        try {
  •            Class.forName(“org.netezza.Driver”);
  •            System.out.println(” Connecting … “);
  •            conn = DriverManager.getConnection(url, user, pwd);
  •            System.out.println(” Connected “+conn);
  •            String sql = “select * from allusers”;
  •            st = conn.createStatement();
  •            rs = st.executeQuery(sql);
  •            System.out.println(“Printing result…”);
  •            int i = 0;
  •            while (rs.next()) {
  •                String userName = rs.getString(“name”);
  •                int year = rs.getInt(“age”);
  •                System.out.println(“User: ” + userName +
  •                        “, age is: ” + year);
  •                i++;
  •            }
  •            if (i==0){
  •                System.out.println(” No data found”);
  •            }
  •        } catch (Exception e) {
  •            e.printStackTrace();
  •        } finally {
  •            try {
  •                if( rs != null)
  •                    rs.close();
  •                if( st!= null)
  •                    st.close();
  •                if( conn != null)
  •                    conn.close();
  •            } catch (SQLException e1) {
  •                    e1.printStackTrace();
  •                }
  •        }
  •    }
  • }

Step 3: Compile the code as below:

  • $ javac -cp nzjdbc3.jar -J-Xmx2g -J-XX:MaxPermSize=128m netezzaJdbcMin.java

Java HotSpot(TM) 64-Bit Server VM warning: ignoring option MaxPermSize=128m; support was removed in 8.0

Note : You should see your main class is compiled without any problem.

Step 4: Run the compiled class as below:

  • $ java -cp .:nzjdbc3.jar netezzaJdbcMain

Connecting …

Connected org.netezza.sql.NzConnection@3feba861

Printing result…

User: John                , age is: 30

User: Jason               , age is: 26

User: Jim                 , age is: 20

User: Kyle                , age is: 21

User: Kim                 , age is: 27

Note: You will see results like above.

Netezza Sample Resumes! Download & Edit, Get Noticed by Top Employers! Download

CONCLUSION

IBM provides the broadest and most comprehensive portfolio of data warehousing, information management and business analytic software, hardware and solutions to help customers maximize the value of their information assets and discover new insights to make better and faster decisions and optimize their business outcomes.

Are you looking training with Right Jobs?

Contact Us

Popular Courses