Skip navigation

SQL Developer Data Modeler

December 2014 Previous month Next month

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

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 will work for you, setting the connection in standalone version of Data Modeler looks different:



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



  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:



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 :


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)












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 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:


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)












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:


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


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


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


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)


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


DDL statement:




        CUSTOMER_ID       DECIMAL(6) ,


        CUST_LAST_NAME    VARCHAR(20) ,



        NLS_LANGUAGE      VARCHAR(3) ,

        NLS_TERRITORY     VARCHAR(30) ,

        CREDIT_LIMIT      DECIMAL(9,2) ,

        CUST_EMAIL        VARCHAR(30) ,

        ACCOUNT_MGR_ID    DECIMAL(6) ,


        DATE_OF_BIRTH     Date ,


        GENDER            VARCHAR(1) ,

        INCOME_LEVEL      VARCHAR(20)




Philip Stoyanov