IBM Netezza SQL translation guide

IBM Netezza data warehousing is designed to work with Netezza-specific SQLsyntax. Netezza SQL is based on Postgres 7.2. SQL scripts written for Netezzacan't be used in a BigQuery data warehouse without alterations,because the SQL dialects vary.

This document details the similarities and differences in SQL syntax betweenNetezza and BigQuery in the following areas:

• Data types
• SQL language elements
• Query syntax
• Data manipulation language (DML)
• Data definition language (DDL)
• Stored procedures
• Functions

You can also usebatch SQL translationto migrate your SQL scripts in bulk, orinteractive SQL translationto translate ad-hoc queries. IBM Netezza SQL/NZPLSQL is supported by bothtools inpreview.

Data types

Timestamp and date type formatting

When you convert date type formatting elements from Netezza toGoogleSQL, you must pay particular attention to time zonedifferences betweenTIMESTAMP andDATETIME, as summarized in thefollowing table:

SELECT statement

Generally, the NetezzaSELECT statement is compatible withBigQuery. The following table contains a list of exceptions:

SELECT  (subquery) AS flag,  CASE WHEN flag = 1 THEN ...

SELECT  (subquery) AS flag,  CASE WHEN (subquery) = 1 THEN ...

SELECT  q.*,  CASE WHEN flag = 1 THEN ...FROM (  SELECT    (subquery) AS flag,    ...  ) AS q

Comparison operators

Built-in SQL functions

Functions

This section compares Netezza and BigQuery functions.

Aggregate functions

Analytical functions

Date and time functions

String functions

Math functions

DML syntax

This section compares Netezza and BigQuery DML syntax.

INSERT statement

INSERT INTO table VALUES (...);

INSERT INTO table (...) VALUES (...);

INSERT INTO table (...) VALUES (...);INSERT INTO table (...) VALUES (...);

INSERT INTO table VALUES (), ();

DML scripts in BigQuery have slightly different consistencysemantics than the equivalent statements in Netezza. Also note thatBigQuery does not offer constraints apart fromNOTNULL.

For an overview of snapshot isolation and session and transaction handling, seeConsistency guarantees and transaction isolation.

UPDATE statement

In Netezza, theWHERE clause is optional, but in BigQuery it isnecessary.

UPDATE tblSETtbl.col1=val1;

UPDATE ASET  y = B.y,  z = B.z + 1FROM BWHERE A.x = B.x  AND A.y IS NULL;

UPDATE ASET  y = B.y,  z = B.z + 1FROM BWHERE A.x = B.x  AND A.y IS NULL;

UPDATE A aliasSET x = x + 1WHERE f(x) IN (0, 1)

UPDATE ASET x = x + 1WHERE f(x) IN (0, 1);

UPDATE ASET z = B.zFROM BWHERE A.x = B.x  AND A.y = B.y

UPDATE ASET z = B.zFROM BWHERE A.x = B.x  AND A.y = B.y;

For examples, seeUPDATE examples.

Because ofDML quotas,we recommend that you use largerMERGE statements instead of multiple singleUPDATE andINSERT statements. DML scripts in BigQuery haveslightly different consistency semantics than equivalent statements in Netezza.For an overview of snapshot isolation and session and transaction handling, seeConsistency guarantees and transaction isolation.

DELETE andTRUNCATE statements

TheDELETE andTRUNCATE statements are both ways to remove rows from a tablewithout affecting the table schema or indexes. TheTRUNCATE statement has thesame effect as theDELETE statement, but is much faster than theDELETEstatement for large tables. TheTRUNCATE statement is supported in Netezza butnot supported in BigQuery. However, you can useDELETEstatements in both Netezza and BigQuery.

In BigQuery, theDELETE statement must have aWHERE clause.In Netezza, theWHERE clause is optional. If theWHERE clause is notspecified, all the rows in the Netezza table are deleted.

BEGIN;LOCK TABLE A IN EXCLUSIVE MODE;DELETE FROM A;INSERT INTO A SELECT * FROM B;COMMIT;

bq query \
--replace \
--destination_table \
tableA \
'SELECT * \
FROM tableB \
WHERE ...'

bq cp \
-f tableA tableB

DELETE FROM database.table

DELETE FROM table WHERE TRUE;

MERGE statement

AMERGE statement must match at most one source row for each target row. DMLscripts in BigQuery have slightly different consistency semanticsthan the equivalent statements in Netezza. For an overview of snapshot isolationand session and transaction handling, seeConsistency guarantees and transaction isolation.For examples, seeBigQueryMERGE examplesandNetezzaMERGE examples.

DDL syntax

This section compares Netezza and BigQuery DDL syntax.

CREATE TABLE statement

DROP statement

Column options and attributes

Temporary tables

Netezza supportsTEMPORARY tablesthat exist during the duration of a session.

To build a temporary table in BigQuery, do the following:

1. Create a dataset that has a short time to live (for example, 12 hours).

2. Create the temporary table in the dataset, with a table name prefix oftemp. For example, to create a table that expires in one hour, do this:

   CREATETABLEtemp.name(col1,col2,...)OPTIONS(expiration_timestamp=TIMESTAMP_ADD(CURRENT_TIMESTAMP(),INTERVAL1HOUR));

3. Start reading and writing from the temporary table.

You can alsoremove duplicates independentlyin order to find errors in downstream systems.

Note that BigQuery does not supportDEFAULT andIDENTITY(sequences) columns.

Procedural SQL statements

Netezza uses theNZPLSQLscripting language to work with stored procedures. NZPLSQL is based on Postgres'PL/pgSQL language. This section describes how to convert procedural SQLstatements used in stored procedures, functions, and triggers from Netezza toBigQuery.

CREATE PROCEDURE statement

Netezza and BigQuery both support creating stored proceduresby using theCREATE PROCEDUREstatement. For more information, seeWork with SQL stored procedures.

Variable declaration and assignment

Exception handlers

Netezza supports exception handlers that can be triggered for certain errorconditions. BigQuery does not support condition handlers.

Dynamic SQL statements

Netezza supports dynamic SQL queries inside stored procedures.BigQuery does not support dynamic SQL statements.

Flow-of-control statements

Other statements and procedural language elements

Multi-statement and multi-line SQL statements

Both Netezza and BigQuery support transactions (sessions) andtherefore support statements separated by semicolons that are consistentlyexecuted together. For more information, seeMulti-statement transactions.

Other SQL statements

Consistency guarantees and transaction isolation

Both Netezza and BigQuery are atomic, that is,ACID compliant ona per-mutation level across many rows. For example, aMERGE operation iscompletely atomic, even with multiple inserted values.

Transactions

Netezza syntactically accepts all four modes of ANSI SQLtransaction isolation.However, regardless of what mode is specified, only theSERIALIZABLE mode isused, which provides the highest possible level of consistency. This mode alsoavoids dirty, non repeatable, and phantom reads between concurrent transactions.Netezza does not use conventionallockingto enforce consistency. Instead, it usesserialization dependency checking,a form of optimistic concurrency control to automatically roll back the latesttransaction when two transactions attempt to modify the same data.

BigQuery alsosupports transactions.BigQuery helps ensure optimistic concurrency control (first tocommit has priority) with snapshot isolation, in which a query reads the lastcommitted data before the query starts. This approach ensures the same levelof consistency on a per-row, per-mutation basis and across rows within the sameDML statement, yet avoids deadlocks. In the case of multiple DML updates againstthe same table, BigQuery switches topessimistic concurrency control.Load jobs can run completely independently and append to tables.

Rollback

Netezza supports theROLLBACK TRANSACTION statementto abort the current transaction and rollback all the changes made in thetransaction.

In BigQuery, you can use theROLLBACK TRANSACTION statement.

Database limits

What's next

• Get step-by-step instructions toMigrate from IBM Netezza to BigQuery.