Since version 4.0.2 Data Modeler has supported Hive tables, now in 4.0.3 also SQL Developer does that. It means that at least you can:

– design Hive tables

– reverse engineer Hive tables

– create SQL queries on both Oracle tables and Hive tables

The implementation is a special external table.

I will write a little about designing and reverse engineering a Hive table. I leave the query part to Jeff Smith to complete:-)

1. How too design a Hive table

Design a table as usual. In relational model you can set dynamic properties hiveName and hiveSchema for the table. These properties will be taken into account in DDL generation.

In Physical Mode define “Organization” property for a table to “External” and the “Access Driver” in “External Table Propertied” to “ORACLE_HIVE”.

And there you are. Now just Export the DDLs and you have the DDLs for creating the external Hive table.

Note that the table will be found under “External tables” tab in “DDL Generation Options”.

2. How to reverse engineer a Hive table

Since version 4.0.2 Data Modeler and since version 4.0.3 SQL Developer has been supporting Oracle Big Data SQL and Hive. This support allows you to connect to Hive and reverse engineer Hive tables. To be able to create a connection to Hive you must first download the JDBC drivers and connectors. Data Modeler supports both Hortonworks and Coludera implementations, SQL Developer only supports Cloudera. You can download the Cloudera JDBC Drivers from http://www.cloudera.com/content/cloudera/en/downloads.html.

Unzip. Note: there is also a PDF file that is worth reading.

Start Data Modeler or SQL Developer. Go to Tools, Preferences, Third Party JDBC Drivers and add the drivers on the list: press the green plus and find the files extracted from Cloudera_HiveJDBC4_<version>.zip. Add all the files.

Close Data Modeler or SQL Developer and restart it. Select “Import”, “Data Dictionary” from “File” menu and press “Add” in the “Data Dictionary Import Wizard” to create a new connection.

For Data Modeler the Hive connection is a JDBC connection with “Other ThirdParty Driver”. Just select the JDBC tab and define the “JDBC URL” and “Driver Class”. When you download the drivers you will also get a PDF file that explains what to insert here.

For SQL Developer it is a new tab “Hive” in “New / Select Database Connection” screen.

After you have created the connection you can reverse engineer from a Hive table using the “Import”, “Data Dictionary” and your new connection.