SnowConvert AI - Renaming feature

Renaming objects during a database migration process is something that a lot of users need to do. For this reason, SnowConvert AI enables the Renaming feature to allow defining new names for the following types of user-defined objects:

备注

This feature is supported for Teradata, Sql Server and Redshift ONLY.

  • Schemas

  • 表

  • 视图

  • 物化视图

  • 过程

  • 函数

  • 宏

备注

重命名功能将同时应用于对象定义和对象的使用。

这些对象通常在架构或数据库中进行限定，因此，根据数据库平台的不同，对象 Table1 可能被简单地引用为 Table1，或 MySchema.Table1，或 MyDatabase.MySchema.Table1。必须 对重命名文件中的每个对象进行完全限定，以避免歧义。

新对象名称通过 .json 文件指定，格式如下。

备注

请注意，此示例包含“Macros”部分，这是 Teradata 特有的元素，可能因指定的语言而异。

{
  "Schemas": {
    "SchemaName": "NewSchema"
  },
  "Tables": {
    "SchemaName.TableName": "NewSchema.TableNameChanged",
    "Table1": "Table2"
  },
  "TablesRegex": [
    {
      "RegexExpr": "(Schema1)\\.(.*)",
      "RegexReplace": "Prefix_$1.$2"
    }
  ],
  
  "Views": {
    "ViewName": "ViewNameChanged",
    "MaterializedViewName": "MaterializedViewNameChanged",
  },
  "ViewsRegex": [
    {
      "RegexExpr": "(Schema1)\\.(.*)",
      "RegexReplace": "$2.$1"
    }
  ],
  
  "Procedures": {
    "ProcedureName": "ProcedureNameChanged"
  },
  "ProceduresRegex": [
    {
      "RegexExpr": "(Schema1)\\.(.*)",
      "RegexReplace": "$2.$1"
    }
  ],
  
  "Macros": {
    "SchemaName.MacroName": "MacroNameChanged",
    "SimpleMacro": "SimpleMacroSf"
  },
  "MacrosRegex": [
    {
      "RegexExpr": "(Schema1)\\.(.*)",
      "RegexReplace": "$2.$1"
    }
  ],
  
  "Functions": {
    "SchemaName.FunctionName": "FunctionNameChanged",
    "SimpleFunction": "SimpleFunctionSf"
  },
  "FunctionsRegex": [
    {
      "RegexExpr": "(Schema1)\\.(.*)",
      "RegexReplace": "$2.$1"
    }
  ]
}
Copy

用途

In order to use the renaming feature you have to execute the CLI version of SnowConvert AI with the following argument --RenamingFile and provide the path to the .json file containing the renaming information. An example of the command can look like this:

snowct.exe -i "somePath/input" -o "somePath/output" --RenamingFile "somePath/renamings.json"

重命名模式

请注意，每种对象都有两个字段："Tables" 和 "TablesRegex"， "Views" 和 "ViewsRegex"，依此类推。这是因为可以通过两种方式指定重命名。

逐个对象（逐行）

在此模式下，每行代表一个对象，它必须包含原始的完全限定名称和新名称。因此，如果要将名为“Table1”的对象从架构 "OriginalSchema" 中移动到架构 "SchemaSF" 中，则该行应如下所示：

"OriginalSchema.Table1": "SchemaSF.Table1"
Copy

如果我们还想将其重命名为“Table2”，则该行应如下所示：

"OriginalSchema.Table1": "SchemaSF.Table2"
Copy

必须在 .json 文件的 "Tables"、 "Views"、 "Procedures"、 "Macros" 和 "Functions" 部分中指定此信息，并且每行必须用英文逗号分隔。让我们来看一个例子：

TableExample1

"Tables": {
    "Schema1.Table1": "SF_Schema1.SF_Table1",
    "Schema1.Table2": "SF_Schema1.SF_Table2",
    "Schema1.Table3": "SF_Schema1.SF_Table3"
  },
Copy

上面的示例表明，整个工作负载中只有三个表需要重命名，分别是名为 “Table1”“Table2”和“Table3”的表，它们都位于 “Schema1”架构内；必须将它们分别重命名为“SF_Table1”“SF_Table2”和“SF_Table3”；最后，它们将位于 Snowflake 中的“SF_Schema1”架构下。

正则表达式

如果需要以相同的方式重命名多个对象，该功能还允许使用正则表达式来定义模式，以应用于相同类型的对象。需要两行来指定每次重命名，第一行是 "RegexExpr"，这是匹配的表达式；第二行是 "RegexReplace"，这是替换表达式。此信息必须在 .json 文件的 "TablesRegex"、 "ViewsRegex"、 "ProceduresRegex"、 "MacrosRegex" 和 "FunctionsRegex" 部分中提供。因此，前面的示例也可以使用正则表达式功能按以下方式编写。

TableExample2

"TablesRegex": [
    {
      "RegexExpr": "Schema1\\.(.*)",
      "RegexReplace": "SF_Schema1.SF_$1"
    }
  ],
Copy

唯一的区别是，这种方式适用于“Schema1”架构中的所有表。正则表达式将匹配“Schema1”架构中定义的所有表，并将创建一个包含点号之后所有内容的捕获组。正则表达式替换会将表移至“SF_Schema1”架构，并将“SF_”前缀添加到所有引用正则表达式中创建的第一个组 ($1) 的表中。

重命名优先级

There might be renamings that apply to the same object and only one of them is chosen. Within the same section, SnowConvert AI will apply the first renaming that matches the current object's name, and it will stop trying to rename that object. So in the following example, despite the fact that "Tables" section specifies renaming "Table1" to "Table1-a" and also to "Table1-b", SnowConvert AI will only rename it to "Table1-a".

"Tables": {
    "Schema1.Table1": "Schema1.Table1-a",
    "Schema1.Table1": "Schema1.Table1-b",
  },
Copy

Also, SnowConvert AI will try to rename an object first checking the object by object renaming section before trying the regular expressions section. So, in the following example despite the fact that both renamings can apply to the same object "Schema1.Table1", only the one defined in the "Tables" section is applied.

"Tables": {
    "Schema1.Table1": "Schema1.TableA",
  }, 
  "TablesRegex": [
    {
      "RegexExpr": "Schema1\\.(.*)",
      "RegexReplace": "Schema1.SF_$1"
    }
  ],
Copy

示例

假设我们有以下输入代码。

输入代码

CREATE TABLE CLIENT ( 
    ID INTEGER, 
    NAME varchar(20));
    
CREATE TABLE TICKET ( 
    CLIENT_ID INTEGER,
    FOREIGN KEY (CLIENT_ID_FK) REFERENCES CLIENT(ID));
    
SELECT * FROM CLIENT;
Copy

还有以下重命名信息

重命名文件 (.JSON)

{
  "Tables": {
    "CLIENT": "USER"
  }
}
Copy

以下是使用重命名和不使用重命名的输出代码。

Snowflake 输出代码

CREATE OR REPLACE TABLE CLIENT (
    ID INTEGER,
    NAME varchar(20))
COMMENT = '{ "origin": "sf_sc", "name": "snowconvert", "version": {  "major": 0,  "minor": 0,  "patch": "0" }, "attributes": {  "component": "teradata",  "convertedOn": "11/13/2024",  "domain": "test" }}'
;

CREATE OR REPLACE TABLE TICKET (
    CLIENT_ID INTEGER,
       FOREIGN KEY (CLIENT_ID_FK) REFERENCES CLIENT (ID))
COMMENT = '{ "origin": "sf_sc", "name": "snowconvert", "version": {  "major": 0,  "minor": 0,  "patch": "0" }, "attributes": {  "component": "teradata",  "convertedOn": "11/13/2024",  "domain": "test" }}'
;

SELECT
    * FROM
    CLIENT;
Copy
CREATE OR REPLACE TABLE USER (
    ID INTEGER,
    NAME varchar(20))
COMMENT = '{ "origin": "sf_sc", "name": "snowconvert", "version": {  "major": 0,  "minor": 0,  "patch": "0" }, "attributes": {  "component": "teradata",  "convertedOn": "11/13/2024",  "domain": "test" }}'
;

CREATE OR REPLACE TABLE TICKET (
    CLIENT_ID INTEGER,
       FOREIGN KEY (CLIENT_ID_FK) REFERENCES USER (ID))
COMMENT = '{ "origin": "sf_sc", "name": "snowconvert", "version": {  "major": 0,  "minor": 0,  "patch": "0" }, "attributes": {  "component": "teradata",  "convertedOn": "11/13/2024",  "domain": "test" }}'
;

SELECT
    * FROM
    USER;
Copy

请留意所有对“CLIENT”的引用如何重命名为“USER”

语言: 中文