文档首页/ 数据仓库服务 GaussDB(DWS)/ 工具指南/ DSC/ Teradata语法迁移/ 视图迁移
更新时间：2024-11-15 GMT+08:00
查看PDF
分享

视图迁移

CREATE VIEW （缩写关键字为CV）和SELECT一同使用，用于创建视图。

Teradata和GaussDB(DWS)均支持关键词VIEW，但SELECT语句在迁移过程中会用()。详情请参见下方图片。

通过tdMigrateVIEWCHECKOPTIO....参数可以配置如何迁移包含WITH CHECK OPTION关键字的视图。如果该参数设置为false，则工具跳过该查询并记录日志。

如果CREATE VIEW包含LOCK关键字，则工具根据tdMigrateLOCKoption的设置决定如何迁移VIEW查询。

输入：CREATE VIEW

1
2
3
 
CREATE VIEW DP_STEDW.MY_PARAM 
AS 
SELECT RUNDATE FROM  DP_STEDW.DATE_TBL   WHERE   dummy = 1;

输出：

1
2
3
4
5
 
CREATE OR REPLACE VIEW DP_STEDW.MY_PARAM 
AS 
SELECT RUNDATE
  FROM  DP_STEDW.DATE_TBL
        WHERE   dummy = 1;

输入：CREATE VIEW，使用FORCE关键字

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
 
CREATE 
OR REPLACE FORCE VIEW IS2010_APP_INFO (
  APP_ID, APP_SHORTNAME, APP_CHNAME, 
  APP_ENNAME
) AS 
select 
  t.app_id, 
  t.app_shortname, 
  t.app_chname, 
  t.app_enname 
from 
  newdrms.seas_app_info t 
WHERE 
  t.app_status <> '2';

输出：

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
 
CREATE
OR REPLACE
/*FORCE*/
VIEW IS2010_APP_INFO (
    APP_ID,
    APP_SHORTNAME,
    APP_CHNAME,
    APP_ENNAME ) AS
SELECT
    t.app_id,
    t.app_shortname,
    t.app_chname,
    t.app_enname
FROM
    newdrms.seas_app_info t
WHERE
t.app_status <> '2';

REPLACE VIEW

在Teradata中，REPLACE VIEW语句用于创建新视图，或重建现有视图。DSC将其迁移为DWS中兼容的CREATE OR REPLACE VIEW语句中。

输入：REPLACE VIEW

1
2
3
4
5
6
7
 
REPLACE VIEW DP_STEDW.MY_PARAM AS SELECT
          RUNDATE
     FROM
          DP_STEDW.DATE_TBL
     WHERE
          dummy = 1
;

输出：

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
 
CREATE
OR REPLACE VIEW DP_STEDW.MY_PARAM AS (
     SELECT
               RUNDATE
          FROM
               DP_STEDW.DATE_TBL
          WHERE
               dummy = 1
)
;

输入：REPLACE RECURSIVE VIEW

1
2
3
4
5
6
 
Replace RECURSIVE VIEW reachable_from (
emp_id,emp_name,DEPTH) 
AS (
SELECT root.emp_id,root.emp_name,0 AS DEPTH
FROM emp AS root
WHERE root.mgr_id IS NULL);

输出：

1
2
3
4
5
6
7
8
 
CREATE OR REPLACE VIEW reachable_from AS (
WITH RECURSIVE reachable_from (
emp_id,emp_name,DEPTH) 
AS (
SELECT root.emp_id,root.emp_name,0 AS DEPTH
FROM emp AS root
WHERE root.mgr_id IS NULL
) SELECT * FROM reachable_from);

REPLACE FUNCTION

输入：

REPLACE FUNCTION up_load1.RPT_016_BUS_DATE()
RETURNS DATE 
LANGUAGE SQL 
CONTAINS SQL 
DETERMINISTIC 
SQL SECURITY DEFINER 
COLLATION INVOKER 
INLINE TYPE 1 
RETURN DATE'2017-08-22';

输出：

CREATE OR REPLACE FUNCTION up_load1.RPT_016_BUS_DATE()
RETURNS DATE
LANGUAGE SQL
IMMUTABLE
SECURITY DEFINER
AS 
$$
SELECT CAST('2017-08-20' AS DATE)
$$
;

CHECK OPTION

通过tdMigrateVIEWCHECKOPTIO....参数可以配置如何迁移包含CHECK OPTION关键字的视图。

如果源数据库中出现含有CHECK OPRTION关键词的视图，则工具在目标数据库中注释掉CHECK OPRTION。

输入：VIEW，使用CHECK OPTION

1
2
3
4
5
6
 
CV  mgr15 AS SEL *
FROM
    employee
WHERE
    manager_id = 15 WITH CHECK OPTION
;

输出：（tdMigrateVIEWCHECKOPTION=True）

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
 
CREATE
     OR REPLACE VIEW mgr15 AS (
          SELECT
                    *
               FROM
                    employee
               WHERE
                    manager_id = 15 /*WITH CHECK OPTION */
     )
;

输出：（tdMigrateVIEWCHECKOPTION=False）

1
2
3
4
5
6
 
CV  mgr15 AS SEL *
FROM
    employee
WHERE
    manager_id = 15 WITH CHECK OPTION
;

VIEW WITH RECURSIVE

GaussDB(DWS)不支持Teradata关键词RECURSIVE VIEW。因此，工具采用VIEW WITH RECURSIVE替代该关键词，如下图所示。

图1 输入视图：CREATE RECURSIVE VIEW
图2 输出视图

VIEW WITH ACCESS LOCK

通过tdMigrateLOCKOption参数可以配置如何迁移包含LOCK关键字的查询。如果tdMigrateLOCKOption设置为false，则该工具在迁移时将跳过该查询并记录日志。

输入：VIEW，使用ACCESS LOCK

1
2
3
4
5
6
7
8
 
CREATE OR REPLACE VIEW DP_SVMEDW.S_LCR_909_001_LCRLOAN 
 AS 
 LOCK TABLE DP_STEDW.S_LCR_909_001_LCRLOAN FOR ACCESS  FOR ACCESS 
 ( SELECT RUN_ID, PRODUCT_ID, CURRENCY
            , CASHFLOW, ENTITY, LCR
            , TIME_BUCKET, MT, Ctl_Id
            , File_Id, Business_Date
      FROM DP_STEDW.S_LCR_909_001_LCRLOAN ) ;

输出：

1
2
3
4
5
6
7
8
 
CREATE OR REPLACE VIEW DP_SVMEDW.S_LCR_909_001_LCRLOAN 
 AS 
/* LOCK TABLE DP_STEDW.S_LCR_909_001_LCRLOAN FOR ACCESS */
 ( SELECT RUN_ID, PRODUCT_ID, CURRENCY
            , CASHFLOW, ENTITY, LCR
            , TIME_BUCKET, MT, Ctl_Id
            , File_Id, Business_Date
      FROM DP_STEDW.S_LCR_909_001_LCRLOAN ) ;

dbc.columnsV

输入：

SELECT A.ColumnName                                                                           
 AS V_COLS             ,A.columnname  || ' ' ||CASE WHEN columnType in ('CF','CV')                                       THEN CASE WHEN columnType='CV' THEN 'VAR' ELSE ''             END||'CHAR('||TRIM(columnlength (INT))||                 ') CHARACTER SET LATIN'||                   CASE WHEN UpperCaseFlag='N'                      THEN ' NOT' ELSE ''                   
END || ' CASESPECIFIC'                                  
WHEN columnType='DA' THEN 'DATE'                                  
WHEN columnType='TS' THEN 'TIMESTAMP(' || TRIM(DecimalFractionalDigits)||')'                                  WHEN columnType='AT' THEN 'TIME('|| TRIM(DecimalFractionalDigits)||')'                                  WHEN columnType='I' THEN 'INTEGER'                                 
 WHEN columnType='I1' THEN 'BYTEINT'                                  
WHEN columnType='I2' THEN 'SMALLINT'                                  
WHEN columnType='I8' THEN 'BIGINT'                                  
WHEN columnType='D' THEN 'DECIMAL('||TRIM(DecimalTotalDigits)||','||TRIM(DecimalFractionalDigits)||')'                                  ELSE 'Unknown'                              
END||CASE WHEN Nullable='Y'        
THEN '' ELSE ' NOT NULL' END||'0A'XC               
AS V_ColT
,D.ColumnName                                                                            
AS V_PICol          --获得目标表主索引
FROM dbc.columnsV A LEFT JOIN dbc.IndicesV B  ON A.columnName = B.columnName AND B.IndexType IN ('Q','P') AND B.DatabaseName = '${V_TDDLDB}'  AND B.tablename='${TARGET_TABLE}' WHERE A.databasename='${V_TDDLDB}' AND A.tablename = '${TARGET_TABLE}' AND A.columnname NOT IN ( 'ETL_JOB_NAME'                                                                                                     ,'ETL_TX_DATE' ,
'ETL_PROC_DATE' 
                          )ORDER BY A.columnid;

输出：

D DECLARE lv_mig_V_COLS   TEXT;         lv_mig_V_ColT        TEXT;         lv_mig_V_PICol       TEXT; BEGIN SELECT STRING_AGG(A.ColumnName, ',')                   , STRING_AGG(A.columnname  || ' ' ||CASE WHEN columnType in ('CF','CV')                                       THEN CASE WHEN columnType='CV' THEN 'VAR' ELSE ''             END||'CHAR('||TRIM(mig_td_ext.mig_fn_castasint(columnlength))||                 ') /*CHARACTER SET LATIN*/'||                   CASE WHEN UpperCaseFlag='N'                      THEN ' NOT' ELSE ''                   END || ' /*CASESPECIFIC*/'                                  WHEN columnType='DA' THEN 'DATE'                                  WHEN columnType='TS' THEN 'TIMESTAMP(' || TRIM(DecimalFractionalDigits)||')'                                  WHEN columnType='AT' THEN 'TIME('|| TRIM(DecimalFractionalDigits)||')'                                  WHEN columnType='I' THEN 'INTEGER'                                  WHEN columnType='I1' THEN 'BYTEINT'                                  WHEN columnType='I2' THEN 'SMALLINT'                                  WHEN columnType='I8' THEN 'BIGINT'                                  WHEN columnType='D' THEN 'DECIMAL('||TRIM(DecimalTotalDigits)||','||TRIM(DecimalFractionalDigits)||')'                                  ELSE 'Unknown'                              END||CASE WHEN Nullable='Y'        THEN '' ELSE ' NOT NULL' END||E'\x0A', ',')                  , STRING_AGG(B.ColumnName, ',')                INTO lv_mig_V_COLS, lv_mig_V_ColT, lv_mig_V_PICol FROM mig_td_ext.vw_td_dbc_columnsV A LEFT JOIN mig_td_ext.vw_td_dbc_IndicesV B  ON A.columnName = B.columnName AND B.IndexType IN ('Q','P') AND B.DatabaseName = 'public'  AND B.tablename='emp2' WHERE A.databasename='public' AND A.tablename = 'emp2'; -- ORDER BY A.columnid; END; /
父主题： Teradata语法迁移