文档首页 > > 工具指南> Migration Tool SQL语法迁移工具> Teradata语法迁移> 模式对象> 视图迁移

视图迁移

分享
更新时间: 2019/08/09 GMT+08:00

CREATE VIEW (缩写关键字为CV)和SELECT用于创建视图。

Teradata和DWS均支持关键词VIEW,但SELECT语句在迁移过程中会用半角括号“()”括住。有关详情,请参见如下图片。

使用tdMigrateVIEWCHECKOPTIO....配置参数可配置如何迁移包含WITH CHECK OPTION关键字的视图。如果该参数设置为false,则该工具将跳过该查询并在日志中记录消息。

如果CREATE VIEW包含LOCK关键字,tdMigrateLOCKoption的设置会决定如何迁移VIEW查询。

输入:CREATE VIEW

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

输出

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

输入:创建视图并指定FORCE关键字

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';

输出

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语句用于创建新视图,或重建现有视图。Migration Tool将其迁移到DWS中兼容的CREATE OR REPLACE VIEW语句中。

输入:REPLACE VIEW

REPLACE VIEW DP_STEDW.MY_PARAM AS SELECT
          RUNDATE
     FROM
          DP_STEDW.DATE_TBL
     WHERE
          dummy = 1
;

输出

CREATE
OR REPLACE VIEW DP_STEDW.MY_PARAM AS (
     SELECT
               RUNDATE
          FROM
               DP_STEDW.DATE_TBL
          WHERE
               dummy = 1
)
;

输入:REPLACE RECURSIVE VIEW

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);

输出

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);

CHECK OPTION

使用 tdMigrateVIEWCHECKOPTIO...配置参数可配置如何迁移包含WITH CHECK OPTION关键字的视图。

如果源数据库中出现含有CHECK OPRTION关键词的视图,则CHECK OPRTION将从目标数据库中注释掉。

输入:VIEW和CHECK OPTION

CV  mgr15 AS SEL *
FROM
    employee
WHERE
    manager_id = 15 WITH CHECK OPTION
;

输出(tdMigrateVIEWCHECKOPTION=True

CREATE
     OR REPLACE VIEW mgr15 AS (
          SELECT
                    *
               FROM
                    employee
               WHERE
                    manager_id = 15 /*WITH CHECK OPTION */
     )
;

输出(tdMigrateVIEWCHECKOPTION=False

CV  mgr15 AS SEL *
FROM
    employee
WHERE
    manager_id = 15 WITH CHECK OPTION
;

VIEW WITH RECURSIVE

DWS不支持关键词RECURSIVE VIEW在Teradata中的格式。因此,会用VIEW WITH RECURSIVE替代该关键词,如下列各图所示。

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

VIEW WITH ACCESS LOCK

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

输入:VIEW和ACCESS LOCK

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 ) ;

输出

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 ) ;
分享:

    相关文档

    相关产品

文档是否有解决您的问题?

提交成功!

非常感谢您的反馈,我们会继续努力做到更好!

反馈提交失败,请稍后再试!

*必选

请至少选择或填写一项反馈信息

字符长度不能超过200

提交反馈 取消

如您有其它疑问,您也可以通过华为云社区问答频道来与我们联系探讨

跳转到云社区