PowerDesigner批量创建Hive模型

Tips：1. 按照要求配置后excel文档，才能使用该脚本正确导入；

          2. PD运行导入脚本位置在：Tools-->Execute Commends --> Run/Eidt Script... 

1. 配置Excel文档如下图：

 第一列到第七列分别为：表名，表注释，（字段名，字段类型，字段长度，是否主键，字段注释）为隐藏信息，不用配置。

 2. 下面脚本只需要修改你的excel存放路径，然后点击run；

' 第一行是表头
' 第二行开始是列的描述，分别是：表名、表注释、列名、列数据类型、列数据长度、列是否为空、列注释
' Excel的sheet名称统一为Sheet1
'开始
Option Explicit
 
Dim mdl ' the current model
Set mdl = ActiveModel
If (mdl Is Nothing) Then
    MsgBox "There is no Active Model"
End If
 
Dim HaveExcel
Dim RQ
Dim x1sApp,xlsWorkBook,xlsSheet 
RQ = vbYes 'MsgBox("Is  Excel Installed on your machine ?", vbYesNo + vbInformation, "Confirmation")
If RQ = vbYes Then
    HaveExcel = True
    ' Open & Create  Excel Document

    Set x1sApp = CreateObject("Excel.Application")
    set xlsWorkBook = x1sApp.Workbooks.Open("D:\student.xlsx")   '指定excel文档路径  
    set xlsSheet = x1sApp.Workbooks(1).Worksheets("Sheet1")   '指定要打开的sheet名称  

Else
    HaveExcel = False
End If
 
a x1sApp, mdl,x1sApp,xlsWorkBook,xlsSheet  

sub a(x1, mdl,x1sApp,xlsWorkBook,xlsSheet)  

dim rwIndex 
dim table
dim tableName
dim colname
dim col
dim dataType
dim tableComment
dim rowCount

rowCount = xlsSheet.usedRange.Rows.Count

on error Resume Next

set tableName = ""
set tableComment = ""

For rwIndex = 2 To rowCount '
    With x1.Workbooks(1).Worksheets("Sheet1")
        If tableName = "" Then
            ' 创建表
            set table = mdl.Tables.CreateNew '创建一个 表实体
            tableName = .Cells(rwIndex, 1).Value '表名
            tableComment = .Cells(rwIndex, 2).Value
            If tableComment = "" Then 
                table.Name=tableName '表名
            Else 
                table.Name=tableComment '表名
            End If
            table.Code=.Cells(rwIndex, 1).Value '表标识
            table.Comment=tableComment '表注释
        ElseIf tableName = .Cells(rwIndex, 1).Value Then

        Else
            set table = mdl.Tables.CreateNew '创建一个 表实体
            tableName = .Cells(rwIndex, 1).Value '表名
            table.Name=.Cells(rwIndex, 1).Value '表名
            table.Code=.Cells(rwIndex, 1).Value '表标识
            table.Comment=.Cells(rwIndex, 2).Value '表注释
        End If

        ' 创建列
        set col = table.Columns.CreateNew '创建一列/字段
        If .Cells(rwIndex, 7).Value = "" Then
            col.Name = .Cells(rwIndex, 3).Value '指定列名
        Else
            col.Name = .Cells(rwIndex, 7).Value '指定列名
        End If
        col.Code = .Cells(rwIndex, 3).Value
        col.Comment = .Cells(rwIndex, 7).Value '指定列说明

        dataType = .Cells(rwIndex, 4).Value '指定列数据类型
        Select Case dataType
        Case "DATE"
            col.DataType = "datetime"
        CASE "VARCHAR"
            col.DataType = Replace("varchar(%s)","%s", .Cells(rwIndex, 5).Value)
        CASE "VARCHAR2"
            col.DataType = Replace("varchar(%s)","%s", .Cells(rwIndex, 5).Value)
        CASE "NVARCHAR2"
            col.DataType = Replace("varchar(%s)","%s", .Cells(rwIndex, 5).Value)
        CASE "CHAR"
            col.DataType = Replace("char(%s)","%s", .Cells(rwIndex, 5).Value)
        CASE "CLOB"
            col.DataType = "text"
        CASE "BLOB"
            col.DataType = "BLOB"
        CASE "FLOAT"
            col.DataType = Replace("float(%s)","%s", .Cells(rwIndex, 5).Value)
        CASE "NUMBER"
            col.DataType = "bigint"
        CASE "RAW"
            col.DataType = Replace("binary(%s)","%s", .Cells(rwIndex, 5).Value)
        CASE "TIMESTAMP(6)"
            col.DataType = "timestamp"
        ' added by minyaolee 20221206
        Case "BIGINT"
            col.DataType = "BIGINT"
        Case "STRING"
            col.DataType = "STRING"
        Case "DECIMAL" 
            col.DataType = Replace("DECIMAL(%s)","%s", .Cells(rwIndex, 5).Value)
        Case "VARCHAR" 
            col.DataType = Replace("VARCHAR(%s)","%s", .Cells(rwIndex, 5).Value)
        Case "CHAR" 
            col.DataType = Replace("CHAR(%s)","%s", .Cells(rwIndex, 5).Value)                  
        END Select
    
        If .Cells(rwIndex, 6).Value = "Y" Then
            col.Primary = true '指定列是否主键 true 为主键 '指定列是否可空 true 为不可空  
        End If

    If .Cells(rwIndex, 8).Value = "Y" Then
            col.Mandatory = true '指定列是否主键 true 为主键 
        End If
    End With
Next
MsgBox "模型导入成功！"

xlsWorkBook.Close
x1sApp.Quit
 
Exit Sub
End sub