Monthly Archives: November 2014

DOAG, Germany

25 Tuesday Nov 2014

Posted by in Events

≈ Leave a comment

On Nov 18-20 I attended the User Group (DOAG) event in Nuremberg, Germany. The event was amazing. The content was super quality, plenty of people to network with and such a great hospitality from the organizers.

On Tuesday I presented with Jeff Smith from Oracle Corp. We were showing how Data Modeler can be used in collaborative work with Subversion and with Microsoft Excel. I think the presentation was very well taken and I always love speaking with Jeff: he is fantastic. And I am glad Maria Colgan found our presentation both informative and funny:) Thank you, Maria, for your great feedback! In the evening we had the speakers’ dinner in a lovely small restaurant. We had a fun and enjoyable evening!

On Wednesday I did not have any presentations but I attended the user group presidents’ meeting that DOAG was kind enough to organize. The meeting was excellent and we shared a lot of ideas and concerns. In the evening we had a great conference party with plenty of food, drinks, football and music. And a lot of networking. FUN!

On Thursday I gave my database security talk in the biggest room. That was quite scary. See yourself…

DOAG

The content in the conference was excellent and everything was just perfect but I think the most valuable thing in visiting events is meeting people. I met many of my friends and I made so many new friends it makes me happy. And I must say the Nuremberg airport was a great place to be on Thursday evening to meet the rest of the gang:-) It was so nice to see you all!

Thank you DOAG for your support to the Oracle Community! And thank you for inviting me!

Hive tables with Data Modeler

16 Sunday Nov 2014

Posted by in Data Modeler

≈ Leave a comment

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.

Winner!

08 Saturday Nov 2014

Posted by in General

≈ Leave a comment

Yesterday I faced the writer’s block. To get over it I told myself I am not ALLOWED to write at all. It took me 60min before I broke the rule and started to write 🙂 I am easy to trick… LOL

Now the last chapter has been sent for tech editing! Hooray!

OK, there is still plenty of work with tech editors’ comments and copy editing but I declare myself a winner now! Short celebration and then back to writing.

Writer’s block

07 Friday Nov 2014

Posted by in General

≈ Leave a comment

Here we go. As I have mentioned probably too many times I am writing a book and I love it. People told me that there is a thing called “writer’s block” that will hit you several times during the writing. I was thinking it will never hit me because I love writing so much. Once again I was wrong. It did hit me today. I am working on several chapters (one is still waiting for my finalization for tech editors and four chapters waiting for me to implement the comments from tech editors) and I just jump from one chapter to another and hope that a miracle happens and I write again. But no. Cannot get it going.

Bobby Curtis (@dbasolved) just adviced me to relax and I will follow his advice. For some reason I trust he knows what he is talking about 😉 and I will relax now and try not to write at all. Let’s see what happens…

If I know myself by 00.00 o’clock I am writing and cannot stop before 3 am:)