Consultant, author, and Oracle ACE Director Kent Graziano takes it into overtime with his tip on color coding data model diagrams in Oracle SQL Developer Data Modeler.


Kent's tip is based on a post on his blog: Better Data Modeling: Color Code Your Data Model Diagrams using #SQLDevModeler.

 

Kent's blog also includes information about his books, training courses, and more.

               

Want more 2 Minute Tech Tips? Click here.

       

Have and idea for a 2 Minute Tech Tip? Contact The specified item was not found.

Data Modeler (DM) 4.0.3 (and all earlier versions) allows “Before Create”, “After Create”  and “End of Scripts” scripts to be defined at table level and they will be included in generated DDL if related option is set. DM 4.0.3 introduced support for some variables to be used thus allowing building of templates - {table}, {table abbr}, {pk name}, {view}, {schema}, {long name}. Still there is a drawback that those scripts need to be added to tables.

DM 4.1 approached the problem in different way - was added support  transformation scripts to be used at specific points of DDL generation for table – “Before Create”, “Instead of Create”, “After Create” and “End of Script”.

If “Instead of Create” script is defined it’ll suppress built-in DDL generation for table and its components so DDL generation for non supported databases can be implemented.

Sets of such quadruples of scripts can be defined and one of them could be set as active – if no one is active then there is no custom DDL generation.

User_Defined_DDL_Test.png

 

The dialog is activated from “Tools>Design rules and Transformations>Table DDL Transformations”. It allows writing, testing and debugging scripts. Table can be selected for “Test” and DDL preview window will pop-up and shows resulting DDL. There is no DDL preview in “Debug” mode.

Following variables are available to script during execution:

- model - relational model instance;

- pModel - physical model instance;

- table - the table in relational model;

- tableProxy - table definition in physical model;

- ddlStatementsList - should be used to return the list with DDL statements that are created by script – each DDL statement should be added as is shown below:

 

ddlStatementsList.add(new java.lang.String(ddl));

 

There is a new property on table defined “Use DDL Transformation Script” and if it’s set then table will be included in custom DDL generation. That property can be set in table properties dialog or in “DDL generation options”

Inclide_Table_DDL_Script.png

 

DM 4.1 is distributed with “After create” script that generates journal table and related supporting trigger providing 2 DDL statements for each processed table:

The script:

/*
variable ddlStatementsList should be used to return the list with DDL statements
that are created by script - as shown below:
ddlStatementsList.add(new java.lang.String(ddl));
other available variables:
- model - relational model instance
- pModel - physical model instance
- table - the table in relational model
- tableProxy - table definition in physical model
*/
var ddl;
var lname;
//journal table name suffix
jnTabSuf = "_JN";
// trigger name suffix
jnTrgSuf = "_JNtrg";
prompt = model.getAppView().getSettings().isIncludePromptInDDL();
useSchema = model.getAppView().getSettings().isIncludeSchemaInDDL();
if(model.getStorageDesign().isOpen()){
    if(useSchema){
         lname = tableProxy.getLongName();
    }else{
        lname = tableProxy.getName();
    }
}else{
    if(useSchema){
         lname = table.getLongName();
    }else{
        lname = table.getName();
    }
}
if(prompt){
    ddl= "PROMPT Creating Journal Table for '"+lname+"';\n";
}else{
    ddl = "";
}
ddl = ddl + "CREATE TABLE "+lname+jnTabSuf+"\n"+
" (JN_OPERATION CHAR(3) NOT NULL"+"\n"+
" ,JN_ORACLE_USER VARCHAR2(30) NOT NULL"+"\n"+
" ,JN_DATETIME DATE NOT NULL"+"\n"+
" ,JN_NOTES VARCHAR2(240)"+"\n"+
" ,JN_APPLN VARCHAR2(35)"+"\n"+
" ,JN_SESSION NUMBER(38)"+"\n";
cols = table.getElements();
for(var i=0;i<cols.length;i++){
    col = cols[i];
    ddl = ddl +" ,"+col.getName()+" "+col.getDatatypeString();
    if(col.isMandatory()){
        ddl = ddl + " NOT NULL\n";
    }else{
        ddl = ddl + "\n";
    }
}
ddl = ddl + " );";
ddlStatementsList.add(new java.lang.String(ddl));
if(prompt){
    ddl= "\nPROMPT Creating Journal Trigger for '"+lname+"';\n";
}else{
    ddl = "\n";
}
ddl = ddl +
"CREATE OR REPLACE TRIGGER "+lname+jnTrgSuf +"\n"+
"  AFTER \n" +
"  INSERT OR \n" +
"  UPDATE OR \n" +
"  DELETE ON emp2 for each row \n" +
" Declare \n" +
"  rec "+lname+"_JN"+"%ROWTYPE; \n" +
"  blank "+lname+"_JN"+"%ROWTYPE; \n" +
"  BEGIN \n" +
"    rec := blank; \n" +
"    IF INSERTING OR UPDATING THEN \n" ;
for(var i=0;i<cols.length;i++){
    col = cols[i];
    ddl = ddl + "      rec."+col.getName()+" := :NEW."+col.getName()+"; \n";
}
ddl = ddl +
"      rec.JN_DATETIME := SYSDATE; \n" +
"      rec.JN_ORACLE_USER := SYS_CONTEXT ('USERENV', 'SESSION_USER'); \n" +
"      rec.JN_APPLN := SYS_CONTEXT ('USERENV', 'MODULE'); \n" +
"      rec.JN_SESSION := SYS_CONTEXT ('USERENV', 'SESSIONID'); \n" +
"      IF INSERTING THEN \n" +
"        rec.JN_OPERATION := 'INS'; \n" +
"      ELSIF UPDATING THEN \n" +
"        rec.JN_OPERATION := 'UPD'; \n" +
"      END IF; \n" +
"    ELSIF DELETING THEN \n" ;
for(var i=0;i<cols.length;i++){
    col = cols[i];
    ddl = ddl + "      rec."+col.getName()+" := :OLD."+col.getName()+"; \n";
}
ddl = ddl +
"      rec.JN_DATETIME := SYSDATE; \n" +
"      rec.JN_ORACLE_USER := SYS_CONTEXT ('USERENV', 'SESSION_USER'); \n" +
"      rec.JN_APPLN := SYS_CONTEXT ('USERENV', 'MODULE'); \n" +
"      rec.JN_SESSION := SYS_CONTEXT ('USERENV', 'SESSIONID'); \n" +
"      rec.JN_OPERATION := 'DEL'; \n" +
"    END IF; \n" +
"    INSERT into "+lname+"_JN"+" VALUES rec; \n" +
"  END; \n" +
"  /"
ddlStatementsList.add(new java.lang.String(ddl));

 

The transformation script also shows the usage of some settings in preferences - "Include Prompt" and 'Include Schema"

And here is the script generated In "DDL Preview" for table COUNTRIES:

 

CREATE TABLE HR.COUNTRIES
    (
     COUNTRY_ID   CHAR (2 BYTE)
     CONSTRAINT COUNTRY_ID_NN NOT NULL ,
     COUNTRY_NAME VARCHAR2 (40 BYTE) ,
     REGION_ID    NUMBER ,
     CONSTRAINT COUNTRY_C_ID_PK PRIMARY KEY ( COUNTRY_ID )
    )
    ORGANIZATION INDEX
    PCTTHRESHOLD 50
;
/* ... other statements from generation ...*/
CREATE TABLE HR.COUNTRIES_JN
(JN_OPERATION CHAR(3) NOT NULL
,JN_ORACLE_USER VARCHAR2(30) NOT NULL
,JN_DATETIME DATE NOT NULL
,JN_NOTES VARCHAR2(240)
,JN_APPLN VARCHAR2(35)
,JN_SESSION NUMBER(38)
,COUNTRY_ID CHAR (2 BYTE) NOT NULL
,COUNTRY_NAME VARCHAR2 (40 BYTE)
,REGION_ID NUMBER
);

CREATE OR REPLACE TRIGGER HR.COUNTRIES_JNtrg
  AFTER
  INSERT OR
  UPDATE OR
  DELETE ON emp2 for each row
Declare
  rec HR.COUNTRIES_JN%ROWTYPE;
  blank HR.COUNTRIES_JN%ROWTYPE;
  BEGIN
    rec := blank;
    IF INSERTING OR UPDATING THEN
      rec.COUNTRY_ID := :NEW.COUNTRY_ID;
      rec.COUNTRY_NAME := :NEW.COUNTRY_NAME;
      rec.REGION_ID := :NEW.REGION_ID;
      rec.JN_DATETIME := SYSDATE;
      rec.JN_ORACLE_USER := SYS_CONTEXT ('USERENV', 'SESSION_USER');
      rec.JN_APPLN := SYS_CONTEXT ('USERENV', 'MODULE');
      rec.JN_SESSION := SYS_CONTEXT ('USERENV', 'SESSIONID');
      IF INSERTING THEN
        rec.JN_OPERATION := 'INS';
      ELSIF UPDATING THEN
        rec.JN_OPERATION := 'UPD';
      END IF;
    ELSIF DELETING THEN
      rec.COUNTRY_ID := :OLD.COUNTRY_ID;
      rec.COUNTRY_NAME := :OLD.COUNTRY_NAME;
      rec.REGION_ID := :OLD.REGION_ID;
      rec.JN_DATETIME := SYSDATE;
      rec.JN_ORACLE_USER := SYS_CONTEXT ('USERENV', 'SESSION_USER');
      rec.JN_APPLN := SYS_CONTEXT ('USERENV', 'MODULE');
      rec.JN_SESSION := SYS_CONTEXT ('USERENV', 'SESSIONID');
      rec.JN_OPERATION := 'DEL';
    END IF;
    INSERT into HR.COUNTRIES_JN VALUES rec;
  END;
  /

 

Philip Stoyanov

Import of tables and views from Hive was introduced in Oracle SQL Developer Data Modeler 4.0.2 and probably some of you have seen that description https://blogs.oracle.com/datawarehousing/entry/oracle_sql_developer_data_modeler

I'm going to describe some detail on the process

1. The goal - there is no dedicated physical model for Hive so the goal was to extract metadata about tables defined in Hive and prepare them for use with  Oracle Big Data SQL | Oracle Database | Oracle

 

2. Setting the connection

If you use SQL Developer 4.0.3 then steps described here https://blogs.oracle.com/datawarehousing/entry/oracle_sql_developer_data_modeler will work for you, setting the connection in standalone version of Data Modeler looks different:

Connection_to_Hive.png

 

If you use a JDBC driver from another vendor you need to check driver class used (could be org.apache.hive.jdbc.HiveDriver ).

 

3. Mapping of Hive primitive types

There is no physical model for Hive thus Hive native primitive types cannot fit into rules around logical types to native types mapping:

-    Import – direct name match between hive primitive type and existing Logical data type is required. New “String “ logical data types is added in order to cover “String” primitive Hive data type and it’s mapped for all supported databases to logical type Varchar with default size set to “max” – max means it’ll take max possible value for each database version supported

-    DDL generation and complex type presentation in compare/merge – the name of logical data type is taken if it matches Hive primitive type otherwise data type is transformed to Oracle 12 c data type and then transformed to Hive type:

•    CHAR, NCHAR – to CHAR

•    VARCHAR2, NVARCHAR2 – to Varchar

•    LONGRAW, BLOB, RAW – to Binary

•    FLOAT, BINARY_FLOAT – to Float

•    BINARY_DOUBLE – to Double

•    Date – to Date

•    Timestamp – to Timestamp

•    LONG, CLOB, NCLOB and everything else – to STRING

 

4. Mapping of Hive complex types

Structured types and collection types in Data Types model are used to represent Object relational definitions in Oracle database. The same constructs are used to represent Hive complex type:

-    ARRAY – mapped to collection type

-    STRUCT – mapped to Structured type

-    MAP and UNIONTYPE – mapped to Structured type – one more property is added to structured type – “Hive Type” with possible values (empty – Hive Struct, MAP – Hive MAP, UNIONTYPE – Hive Uniontype

Examples:

CREATE TABLE emp1 (

  name         STRING,

  salary       FLOAT,

  subordinates ARRAY<STRING>,

  deductions   MAP<STRING, FLOAT>,

  address      STRUCT<street:STRUCT<name:STRING, number:INT>, city:STRING, state:STRING, zip:INT>

)

 

CREATE TABLE emp_addresses (

  name         STRING,

  salary       FLOAT,

  subordinates ARRAY<STRING>,

  deductions   MAP<STRING, FLOAT>,

  addresses      ARRAY<STRUCT<street:STRUCT<name:Varchar(30), number:INT>, city:STRING, state:varchar(2), zip:INT>>

)

 

create table union_test(

foo UNION_TYPE< int , double , aray <string> , struct< a: int , b : string>>);

 

Following types will be created in data types model after importing of these definitions from Hive:

Data_Types_Model.png

 

So Hive complex type - STRUCT<street:STRUCT<name:Varchar(30), number:INT>, city:STRING, state:varchar(2), zip:INT>

Will be presented in Data Types model :

Struct_address2.png

And column with same complex type definition will be based on the same structured type – equivalent definitions are recognized and reused

 

5. Imported objects

5.1 Tables - columns with data types and defined comments for table and columns are imported; also details about some Hive specific constructs are imported and stored in dynamic properties for table and columns – partitioned columns, clustering and sorting columns, table type (external or managed – in Hive terms), location …; Those dynamic properties can be included into reports or visualized on relational diagram using settings for existing dynamic properties. In DM 4.1 they can be promoted to User defined Properties for better visualization (color) and manipulation (type, list of values,default value and group)

5.2 Views- definitions are imported as they are defined in Hive – no translation is applied

 

6.Hive tables as external tables in Oracle 12c physical model

New functionality for presenting Oracle external tables in relational model is used to represent Hive tables in relational model (with set classification type as “External”) and in Oracle physical model (with organization set to “External”).

Size for column with primitive types can be changed using already existing in DM functionality for setting size, precision and scale.

By default complex types represented by structured types and collection types are represented in generated DDL (for Oracle external tables) with size 4000 (columns subordinates, deductions and address in the example below):

CREATE TABLE emp1_addresses

    (

     name         VARCHAR2 (4000) ,

     salary       FLOAT ,

     subordinates VARCHAR2(4000) ,

     deductions   VARCHAR2(4000) ,

     address      VARCHAR2(4000)

    )

    ORGANIZATION EXTERNAL

    (

    TYPE ORACLE_HIVE

    DEFAULT DIRECTORY Dir1

    ACCESS PARAMETERS

        (

com.oracle.bigdata.tablename:emp1_addresses

        )

    )

;

 

That size for complex types can be changed at structured type/collection type or at column level in physical model for Oracle.

New property is introduced for structured and collection types – “Max Size as String”

Complex_type_size.png

Complex type can be used in definitions of several  tables and if the size is different then the size can be redefined at column level in physical model:

Size_in_physical_model.png

So we can tune generated DDL to:

CREATE TABLE emp1_addresses

    (

     name         VARCHAR2 (50) ,

     salary       FLOAT ,

     subordinates VARCHAR2(160) ,

     deductions   VARCHAR2(70) ,

     address      VARCHAR2(70)

    )

    ORGANIZATION EXTERNAL

    (

    TYPE ORACLE_HIVE

    DEFAULT DIRECTORY Dir1

    ACCESS PARAMETERS

        (

com.oracle.bigdata.tablename:emp1_addresses

        )

    )

;

 

7. Expanded visualization of complex type

New option is added to relational model diagrams that allow seeing details of structured types and collection of structured types:

expand_complex_types.png

After "resize to visible" details of complex types can be seen on diagram:

expanded_types.png

UnionType (Hive type) has a specific presentation – its meaning in Hive is that column can accommodate value of one of declared types. In used structured type those possible types are represented by attributes (>attr_1, >attr_2..) each one with related type – column foo in table default.union_test3.

Map (Hive type) is represented by structured type with 2 attributes “key” and “value” each one with type related to Hive definition – column deductions in table emp2_changed_name.

Structured type has one more property – “Hive Type” and it can be set to Map or UNIONTYPE

 

8. Visibility of existing dynamic properties

Visibility can be set for dynamic properties already created on tables and entities. New page is added for that in design level settings (settings in design properties dialog

Visibility_of_props.png

And option to show them in View>details menu on diagram ("Dynamic properties" need to be chcked - the same is used for User Defined Properties in DM 4.1):

View_details.png

9. Hive DDl preview

Limited Hive DDL can be generated If table has dynamic property hiveDDL with value true - then it’s possible to have DDL preview for Hive.

(Data Modeler 4.1 provides possibility for user defined DDL using scripting that can replace DDL generated by built-in DDL generator - so it's possible customers to generate complete Hive DDL based on Dynamic/user defined properties set for tables and columns)

Hive_DDL.png

That also works on tables not imported from Hive – as the example above from OE schema

Hive_DDL_Preview.png

DDL statement:

 

CREATE TABLE OE.CUSTOMERS

    (

        CUSTOMER_ID       DECIMAL(6) ,

        CUST_FIRST_NAME   VARCHAR(20) ,

        CUST_LAST_NAME    VARCHAR(20) ,

        CUST_ADDRESS      STRUCT<STREET_ADDRESS:VARCHAR(40),POSTAL_CODE:VARCHAR(10),CITY:VARCHAR(30),STATE_PROVINCE:VARCHAR(10),COUNTRY_ID:CHAR(2)> ,

        PHONE_NUMBERS     ARRAY<VARCHAR(25)> ,

        NLS_LANGUAGE      VARCHAR(3) ,

        NLS_TERRITORY     VARCHAR(30) ,

        CREDIT_LIMIT      DECIMAL(9,2) ,

        CUST_EMAIL        VARCHAR(30) ,

        ACCOUNT_MGR_ID    DECIMAL(6) ,

        CUST_GEO_LOCATION STRUCT<SDO_GTYPE:DECIMAL,SDO_SRID:DECIMAL,SDO_POINT:STRUCT<X:DECIMAL,Y:DECIMAL,Z:DECIMAL>,SDO_ELEM_INFO:ARRAY<DECIMAL>,SDO_ORDINATES:ARRAY<DECIMAL>> ,

        DATE_OF_BIRTH     Date ,

        MARITAL_STATUS    VARCHAR(20) ,

        GENDER            VARCHAR(1) ,

        INCOME_LEVEL      VARCHAR(20)

    )

;

 

Philip Stoyanov