IBM Netezza SQL translation guide

IBM Netezza data warehousing is designed to work with Netezza-specific SQL syntax. Netezza SQL is based on Postgres 7.2. SQL scripts written for Netezza can'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 between Netezza 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 use batch SQL translation to migrate your SQL scripts in bulk, or interactive SQL translation to translate ad-hoc queries. IBM Netezza SQL/NZPLSQL is supported by both tools in preview.

Data types

Timestamp and date type formatting

For more information about the date type formatting that Netezza SQL uses, see the Netezza Date Time template patterns documentation. For more information about the date time functions, see the Netezza date/time functions documentation.

When you convert date type formatting elements from Netezza to GoogleSQL, you must pay particular attention to time zone differences between TIMESTAMP and DATETIME, as summarized in the following table:

SELECT statement

Generally, the Netezza SELECT statement is compatible with BigQuery. 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 consistency semantics than the equivalent statements in Netezza. Also note that BigQuery does not offer constraints apart from NOT NULL.

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

UPDATE statement

In Netezza, the WHERE clause is optional, but in BigQuery it is necessary.

UPDATE tbl
SET
tbl.col1=val1;

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

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

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

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

UPDATE A
SET z = B.z
FROM B
WHERE A.x = B.x
  AND A.y = B.y

UPDATE A
SET z = B.z
FROM B
WHERE A.x = B.x
  AND A.y = B.y;

For examples, see UPDATE examples.

Because of DML quotas, we recommend that you use larger MERGE statements instead of multiple single UPDATE and INSERT statements. DML scripts in BigQuery have slightly different consistency semantics than equivalent statements in Netezza. For an overview of snapshot isolation and session and transaction handling, see Consistency guarantees and transaction isolation.

DELETE and TRUNCATE statements

The DELETE and TRUNCATE statements are both ways to remove rows from a table without affecting the table schema or indexes. The TRUNCATE statement has the same effect as the DELETE statement, but is much faster than the DELETE statement for large tables. The TRUNCATE statement is supported in Netezza but not supported in BigQuery. However, you can use DELETE statements in both Netezza and BigQuery.

In BigQuery, the DELETE statement must have a WHERE clause. In Netezza, the WHERE clause is optional. If the WHERE clause is not specified, 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

A MERGE statement must match at most one source row for each target row. DML scripts in BigQuery have slightly different consistency semantics than the equivalent statements in Netezza. For an overview of snapshot isolation and session and transaction handling, see Consistency guarantees and transaction isolation. For examples, see BigQuery MERGE examples and Netezza MERGE examples.

DDL syntax

This section compares Netezza and BigQuery DDL syntax.

CREATE TABLE statement

DROP statement

Column options and attributes

Temporary tables

Netezza supports TEMPORARY tables that 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 of temp. For example, to create a table that expires in one hour, do this:

   CREATE TABLE temp.name (col1, col2, ...)
   OPTIONS(expiration_timestamp = TIMESTAMP_ADD(CURRENT_TIMESTAMP(),
   INTERVAL 1 HOUR));
   

3. Start reading and writing from the temporary table.

You can also remove duplicates independently in order to find errors in downstream systems.

Note that BigQuery does not support DEFAULT and IDENTITY (sequences) columns.

Procedural SQL statements

Netezza uses the NZPLSQL scripting language to work with stored procedures. NZPLSQL is based on Postgres' PL/pgSQL language. This section describes how to convert procedural SQL statements used in stored procedures, functions, and triggers from Netezza to BigQuery.

CREATE PROCEDURE statement

Netezza and BigQuery both support creating stored procedures by using the CREATE PROCEDURE statement. For more information, see Work with SQL stored procedures.

Variable declaration and assignment

Exception handlers

Netezza supports exception handlers that can be triggered for certain error conditions. 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) and therefore support statements separated by semicolons that are consistently executed together. For more information, see Multi-statement transactions.

Other SQL statements

Consistency guarantees and transaction isolation

Both Netezza and BigQuery are atomic, that is, ACID compliant on a per-mutation level across many rows. For example, a MERGE operation is completely atomic, even with multiple inserted values.

Transactions

Netezza syntactically accepts all four modes of ANSI SQL transaction isolation. However, regardless of what mode is specified, only the SERIALIZABLE mode is used, which provides the highest possible level of consistency. This mode also avoids dirty, nonrepeatable, and phantom reads between concurrent transactions. Netezza does not use conventional locking to enforce consistency. Instead, it uses serialization dependency checking, a form of optimistic concurrency control to automatically roll back the latest transaction when two transactions attempt to modify the same data.

BigQuery also supports transactions. BigQuery helps ensure optimistic concurrency control (first to commit has priority) with snapshot isolation, in which a query reads the last committed data before the query starts. This approach guarantees the same level of consistency on a per-row, per-mutation basis and across rows within the same DML statement, yet avoids deadlocks. In the case of multiple DML updates against the same table, BigQuery switches to pessimistic concurrency control. Load jobs can run completely independently and append to tables.

Rollback

Netezza supports the ROLLBACK statement to abort the current transaction and roll back all the changes made in the transaction.

In BigQuery, you can use the ROLLBACK TRANSACTION statement.

Database limits

What's next

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