Hive tables with Data Modeler

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!

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

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

Reverse engineer and discover foreign keys

You can easily document your database with Data Modeler: just reverse engineer the database with File-> Import -> Data Dictionary. But what if the database has no foreign keys? What’s the point of documenting then? Just tables that has nothing to do with each other….

Well, Data Modeler can help with that too: you can use “Discover Foreign Keys” functionality to find the foreign keys that should be in the database and even generate the DDLs for them if needed…

There are two ways Data Modeler can guess the foreign keys for you: the column has the same name as a primary key column in parent table or using the naming templates. And combinations of those.

Select the relational model from the Browser. Right-click and select “Discover Foreign Keys”. I have two tables: CUSTOMER and ORDER.

Tables

I will ask Data Modeler to find me the foreign keys.

If I select “Referred Column” as the “FK Column name property” and press “Scan Again”, Data Modeler suggests that column CustNo might be a potential foreign key column.

ReferredColumn

If I agree on that I press OK and the foreign key has been created automatically into my relational model.

If I select “FK Column Template” as the “FK Column name property” and press “Scan Again”, Data Modeler suggests that column Customer_ID might be a potential foreign key column.

FKColumnTemplate

The reason for that is that I have defined in my Design Properties that a foreign key column will be named as PARENTTABLE_ID.

Template

If you want Customer_ID to be the foreign key just press OK.

You can also select either “Referred and Template” or “Template and Referred” as the “FK Column name property”. The first one tries to find first columns with fitting name and secondary columns that fit the template, the latter does the opposite. In my example if I select “Referred and Template” the result would be the same as with “Referred” and if I select “Template and Referred” it would be the same as with “FK Column Template”. But if you have both naming standards in the database the results might be very different.

When you have created the discovered foreign keys into the logical model remember to press “Engineer to Logical Model” to get the relationships to entities and to the logical model as well.

All discovered foreign keys have a Dynamic Property (createdByFKDiscoverer) attached to them and can be easily found using that. If you want to remove the discovered foreign keys, select “Remove Discovered Foreign Keys “ from “Edit” menu. The foreign keys are removed without notice. Note that the Dynamic Property is not engineered to the relationship in logical model. If you decide to remove the discovered foreign keys for some reason remember to press “Engineer to Logical Model” again so that the relationships will also be removed.

I am sure you already guessed that there are some restrictions:

- the parent table must have either a Primary Key or a Unique Key that will be used while comparing

- the columns compared must be of same datatype, so not only the naming is enough

I think this is a great functionality and I am sure you will save a lot of time and money with it. Have fun!

WIT again

Correction after a comment I got: this is really not about WIT but the topic is WIT because I was contacted by people under the topic WIT. As you see in the end this all applies to both women and men and has nothing to do with IT.

And I think what should happen first is that women start respecting and supporting each other. That is the best WIT.

I am sorry I got stuck to this topic but after my post about my thoughts on WIT a lot has happened. And it made me think again.

The female boss I mentioned in my previous post. She has probably been my biggest idol ever. She is smart, honest, mother of three sons, a grandmother and always ready to help anybody. She is now retired and I met her a while ago. She is enjoying her life: the grandchildren, hobbies, volunteer work etc. When she looks back to her career she is very happy except about one horrible thing that happened to her during the years. There was a female IT co-worker who tried to make her life a nightmare and almost succeeded. I did not know her that time but I can only imagine. So this wonderful lady did not say anything bad about male colleagues but only about one female colleague. I feel really bad. I know her job was not easy but I know she made my life always easy by fighting for my rights whenever needed (and not event telling me about it, I only realized it after she left the position).

I have been contacted by couple of people either telling that they do not have the strenght to fight back or people saying they are tired of fighting for other peoples rights. I find this horrible. Why do you need to fight for your rights?! If you are good (or excellent) in what you are doing why do you still need to fight to be able to do your work?!

If people are like this the wars will never be over. Why can we not be friends and respect each other? Why do we need to make other people look bad and think that this way we will look better? I do not think somebody is better if she/he treats other people badly, I think she/he is a bad person. And I do not want to spend my time with bad people.

WIT (Women In Technology)

This is a topic I have been thinking a lot lately. Mainly because several people have asked me questions related to it and I had to think to be able to answer.

Yesterday I was speaking in an event in Tampere (Oracle 12c upgrade) organized by Oracle Finland, Miracle Finland and Arrow ECS. After the event the organizers and speakers had lunch. There were 8 people on the table: 4 men and 4 women, all working on IT. I was thinking that in my surrounding the situation is extremely good. But why?

In Finland women are equal to men in many ways. So being on IT in Finland is easier that in some other countries. I am sure it is not this easy in every country.

Then I started to think who have been the most supportive people in my career. Some men definitely (including my own hubby) but also a couple of women (my boss from years back and several colleagues). And I came to the conclusion that support is definitely needed and you cannot get the best out of yourself without that. And without the support from family you can actually do nothing.

Then I started thinking what is stopping me from doing even more and why did I not do these things I am doing now before. Number one stopping me is I. I do not know if that is because I am a woman or because I am a Finn or because I am who I am but that is definitely number one. I do not think I have special skills or knowledge (I know I do but I always “forget” it): I always think everybody knows what I know. My family is always number one whatever I am doing. So if I need to choose between a career and a family I would ALWAYS take family. It sounds strange because I am working like crazy and do not have enough time for the family but if anything happens family always wins. I like people and I always expect the best of them. That of course is sometimes disappointing… I want to help people and sometimes people take advantage of that. But I try not to care about it, I still want to be positive and trust people and humankind. What else prevents me in my career? Other people of course. I only remember a couple of men trying to make me change the career when I was younger but I do not think I actually cared about them: I just made jokes about the things they said. But I do not remember any men trying to make my life on IT harder recent years, all men have been very supportive. I think it must be that they can see I am strong and no use bullingJ Unfortunately I do remember several women doing that, when I was younger and still. And the way women do it is very bad. Why do women do that to each other?! They should be supporting other women. I think the reason is envy. Women envy other women. Why? If somebody has something good it is not away from you! Or another reason is that women do not have enough self-confidence and to hide that they try to make other women look bad. Just that you do not think I am talking about youJ Some of the most supportive people in my life are women (and I am not only talking about my amazing Mother :-)). When women are supportive they are very supportive: like mother lions.

I think what we all can do is to support other people. Listen to them and try to help in every way we can. And every now and then say something nice. People know they have skills and they are special but they forget that easily. Remind them about it. When they trust themselves they can do amazing things. And maybe they do not say NO to something very important just because you have been supportive… And remember to be a friend, a loyal friend. There can never be too many true friends. If you help others one day they will help you.

My first OTN Tour

Last week I attended my first OTN Tour as a speaker. We travelled to Sweden, Norway, Denmark and Finland. Every evening we changed the country and in the morning we started with a new conference. And again in the evening (when other attendees started to eat and drink J) we took a taxi to the airport and flew to another country. The speakers were selected by the user groups involved. My speaker colleagues were: Jonathan Lewis, Debra Lilley, Simon Haslam and Joze Senegacnik. My special skill on this trip was Swedish. I was quite surprised I still knew how to speak it:)

On Sunday we flew to Stockholm, Sweden. Jonathan was kind enough to wait for me at the airport and we took a taxi to a beautiful Japanese SPA where the conference was held the next day. The other speakers arrived a bit later. I made a reservation for dinner while waiting. Unfortunately the hotel was quite full and I got the reservation at 9pm (and we were starving!). To make the time fly faster Debra and I took a fish treatment (the fish ate our feet), that was funnyJ Finally it was dinner time. Everybody in the restaurant was wearing a kimono (the same kind of kimono) and it actually felt very nice: no need to worry what to wear… Sushi was excellent but I think we all agreed the main course was not the best one. The reason could also have been that we were already quite tired. The next morning we started with a breakfast and then to the conference, some of us (Debra, Simon and I) wearing our kimonos. I am very happy with my sessions. I presented about database security to a very good audience. Somebody from the audience tweeted that it was the topic of the day! And personally I was very happy to see an old colleague of mine (Ove) in the audience and it was so nice to hear he is doing very well in Sweden (he moved there 14 years ago). After the sessions it was time to take a taxi to the airport and fly to Oslo, Norway. I think it was a long 24h since Simon asked me at the airport: “Heli, do you come here, Copenhagen, very often?”. I think this question summarizes very well how we all felt during the week: confused…

My hotel room in Sweden:

photo

We arrived to our hotel in Oslo about 9 pm and the OUGN board was waiting for us and offered us a lovely dinner. After the dinner we were all ready to bed (ok after some work, because even we are traveling and speaking we still need to take care of our day to day work and also polish the slides for the next day). Next morning it was breakfast again and to the conference. I think Norway had the biggest audience of all. I had my security session for a full room (the biggest room they had!) and a keynote about agile database design (also for a full room). The audience was excellent, I had so much fun. After my sessions somebody from the audience came and gave me a chocolate as a present. That was such a nice gesture! Norwegians are amazing! After my keynote I ran to the taxi (all other speakers were waiting for me) and the other attendees stayed for food and beer…

Here is a view from my hotel room in Norway:

Norway

And a picture from my presentation about database security about 5-10 minutes before we started. This is about half of the room.

Audience

About 9 pm again we arrived to our hotel in Copenhagen. It was a really nice small hotel. The board of DOUG was waiting for us and we were served a surprise menu which was excellent. I tried not to eat too much main course because I heard we will have chocolate cake for desert but the huge desert plate was still too much for me… The next morning we had a breakfast and then took a taxi to the local Oracle office where the conference was held. My both sessions were well attended (database security and agile database design) and the database design was so popular some people had to bring their own chairs… and I had my second chocolate from the audience… Thank you! What was special in Denmark was that my dear friend Mogens Norgaard (I owe him a lot! I would not be the CEO for Miracle Finland without him) invited us for a quick dinner before we flew to Finland. We had a delicious meal in his friend Ali’s restaurant and then took a taxi to the airport.

This is how Denmark looks like from my hotel window:

Denmark

We arrived to Helsinki, Finland about 11 pm. My hubby came to pick me up at the airport (home!) and the rest of the gang walked to their hotel at the airport. The next morning we met at the venue which was a nice small building by the water. The temperature outside was minus something Celsius but inside it was warm and nice.

FinlandFire

Even the phones were cold and dressed for the Winter…

Phone

I was super-brave (maybe because it is my home user group or maybe because we were all getting very tired) and gave both my sessions about Data Modeler just presenting demos (LIVE! demos). I think it worked very well. I had one or two slides and thanks to Simon I have one photo of me presenting…

Presenting

After the conference we had a lovely dinner and then I was totally ready to bed.

I think the tour was excellent. All the organization was very good and travelling with other speakers was really nice. This tour made me very happy. People still valuate the database design. And I want to thank Ms Hanni Makkonen from Oracle Finland to make me speak about other topics but just database design. My security session was very well taken and I enjoyed it very much. In four words: I love my job.

I also want to thank the Oracle ACE Program: Vikki, Jennifer and Roland for making this possible. Thank you! The user groups for organizing the tour (Orcan, OUGN, DOUG and OUGF) and my fellow speakers (Debra, Jonathan, Simon and Joze) for making the tour an amazing experience. I had a great time! And I feel very privileged to call these amazing speakers my friends!

On Friday morning I was back at the office again. Not a bad place to be either…

Office

I cannot see Incoming Changes!

My colleague updated the design but I cannot see those changes in my design. I have checked the design out from Subversion and I have the .svn file in my working copy directory. What is wrong?

Nothing. You must first select Refresh Incoming Changes in Pending Changes pane and after that you can Update Working Copy. Data Modeler does not automatically ask Subversion for incoming changes. You must do it yourself before updating the working copy.

How can I see what is in Subversion?

I have heard that Subversion is integrated to Data Modeler. What does that mean and how can I see what is inside Subversion? I do not want to install any clients or anything…

Go to Team menu and select Versions. The Versions browser opens. You use this browser for creating directories to Subversion, working with Subversion and for seeing what is in Subversion. Go to Team menu again and select Pending Changes. The Pending Changes tab appears. In Pending Changes you check for incoming changes and update the working copy, commit outgoing changes and add files.

Subversion integration also means that when you are working on Pending changes you see the table etc names and not the file names. The file names are terrible for human eyes because they are based on object ids.

Follow

Get every new post delivered to your Inbox.