Analytic DB for PostgreSQL (ADB for PG) is very compatible with Teradata grammar. It only needs limited modification to migrate Teradata application to ADB for PG. This article describes the issues that should be noted when migrating Teradata applications to ADB for PG.

1 tabular statement

We compare the table statements of ADB for PG and Teradata by an example. For the following Teradata build table SQL statements,

CREATE MULTISET TABLE test_table,NO FALLBACK ,
     NO BEFORE JOURNAL,
     NO AFTER JOURNAL,
     CHECKSUM = DEFAULT,
     DEFAULT MERGEBLOCKRATIO
     (
      First_column DATE FORMAT'YYYYMMDD'TITLE'first column' NOT NULL,
      Second_column INTEGER TITLE'second column'NOT NULL,
      Third_column CHAR (6) CHARACTER SET LATIN CASESPECIFIC TITLE'Column 3'NOT NULL,
      Four_column CHAR (20) CHARACTER SET LATIN CASESPECIFIC TITLE'Column 4'NOT NULL,
      Fifth_column CHAR (1) CHARACTER SET LATIN CASESPECIFIC TITLE'Column 5'NOT NULL,
      Six_column CHAR (24) CHARACTER SET LATIN CASESPECIFIC TITLE'Column 6'NOT NULL,
      Seventh_column VARCHAR (18) CHARACTER SET LATIN CASESPECIFIC TITLE'Column 7'NOT NULL,
      Eighth_column DECIMAL (18,0) TITLE'Column 8'NOT NULL,
      Nineth_column DECIMAL (18,6) TITLE'Column 9'NOT NULL
PRIMARY INDEX ( first_column ,fourth_column )
PARTITION BY RANGE_N(first_column  BETWEEN DATE '1999-01-01' AND DATE '2050-12-31' EACH INTERVAL '1' DAY );

CREATE INDEX test_index (first_column, fourth_column) ON test_table;

Can be modified to ADB for PG table statement:

CREATE TABLE test_table
     (
      first_column DATE NOT NULL,
      second_column INTEGER NOT NULL ,
      third_column CHAR(6) NOT NULL ,
      fourth_column CHAR(20) NOT NULL,
      fifth_column CHAR(1) NOT NULL,
      sixth_column CHAR(24) NOT NULL,
      seventh_column VARCHAR(18) NOT NULL,
      eighth_column DECIMAL(18,0) NOT NULL ,
      nineth_column DECIMAL(18,6) NOT NULL )
DISTRIBUTED BY ( first_column ,fourth_column )
PARTITION BY RANGE(first_column) 
(START (DATE '1999-01-01')  INCLUSIVE
END (DATE '2050-12-31')  INCLUSIVE
EVERY (INTERVAL '1 DAY' ) );

create index test_index on test_table(first_column, fourth_column);

Through the above examples, we can clearly analyze the similarities and differences between ADB for PG and Teradata tabulation statements:
1. The data types of ADB for PG and Teradata are compatible and need not be modified.
2. Both ADB for PG and Teradata support distribution columns, but the grammar is different. Teradata is primary index and ADB for PG is distributed by.
3. Both ADB for PG and Teradata support PARTITION BY secondary partition with the same semantics but different grammar.
4. ADB for PG and Teradata both support indexing tables, but the grammar is different.
5. ADB for PG does not support TITLE keyword, but supports adding annotations COMMENT to columns separately. The grammar is COMMENT ON COLUMN table_name. column_name IS’XXX’.
6. ADB for PG cannot declare the encoding type when defining char or varchar, but when connecting to the database, it declares the encoding type by executing “SET client_encoding = latin1;”.

2 Import and export data format

ADB for PG supports data import and export in txt and CSV formats. The difference between ADB for PG and Teradata is that Teradata supports double separators, while ADB for PG only supports single separators.

3 SQL statement

Most of the SQL grammars of ADB for PG and Teradata are compatible, except for specific Teradata grammars and system functions that need to be modified.

3.1 Specific Grammar

3.3.1 cast

Teradata supports a cast grammar similar to the following:

cast(XXX as int format '999999')
cast(XXX as date format 'YYYYMMDD')

ADB for PG supports cast (XXX as int), cast (XXX as date), and does not support declaring format in cast. Therefore, for cast (XXX as int format’999999′), functions need to be written to achieve the same function; for cast (XXX as date format’YYYMMDD’), ADB for PG supports date in’YYYY-MM-DD’, which does not affect normal use.

3.3.2 qualify

Teradata’s qualify keyword is used to further filter the results of the pre-ordering calculation function according to the user’s conditions. The following is an example of Teradata’s qualify keyword use:

SELECT itemid, sumprice, RANK() OVER (ORDER BY sumprice DESC)
     FROM (SELECT a1.item_id, SUM(a1.sale)
           FROM sales AS a1 
           GROUP BY a1.itemID) AS t1 (itemid, sumprice) 
     QUALIFY RANK() OVER (ORDER BY sum_price DESC) <=100;

ADB for PG does not support qualify keyword, so it is necessary to modify the SQL statement with qualify to nest sub-queries:

SELECT itemid, sumprice, rank from 
(SELECT itemid, sumprice, RANK() OVER (ORDER BY sumprice DESC) as rank
     FROM (SELECT a1.item_id, SUM(a1.sale)
           FROM sales AS a1 
           GROUP BY a1.itemID) AS t1 (itemid,sumprice)
)  AS a
where rank <=100;

3.3.3 macro

Teradata executes a set of SQL statements through macro. A typical macro example is:

CREATE MACRO Get_Emp_Salary(EmployeeNo INTEGER) AS ( 
   SELECT 
   EmployeeNo, 
   NetPay 
   FROM  
   Salary 
   WHERE EmployeeNo = :EmployeeNo; 
);

ADB for PG does not support macro, but Teradata’s macro function can be easily accomplished with ADB for PG’s function:

CREATE OR REPLACE FUNCTION Get_Emp_Salary(
        EmployeeNo INTEGER,
        OUT EmployeeNo INTEGER,
        OUT NetPay FLOAT
) returns setof record AS 
$$

        SELECT EmployeeNo,NetPay 
        FROM Salary
        WHERE EmployeeNo = $1

$$
 LANGUAGE SQL;

3.3.4 System Function

ADB for PG and Teradata Key System Functions Contrast Table:

Teradata application migration to AnalyticDB for PostgreSQL guidance



Author: Lu Feng

Read the original text

This article is the original content of Yunqi Community, which can not be reproduced without permission.