SQL – NuoDB and Third Party Explorer – SQuirreL SQL Client, SQL Workbench/J and DbVisualizer

I recently wrote a four-part series on how I started to learn about and begin my journey with NuoDB. Big Data is indeed a big world and the learning of the Big Data is like spaghetti – no one knows in reality where to start, so I decided to learn it with the help of NuoDB. You can download NuoDB and continue your journey with me as well.

…and in this blog post we will try to answer the most asked question about NuoDB.

“I like the NuoDB Explorer but can I connect to NuoDB from my preferred Graphical User Interface?”
Honestly, I did not expect this question to be asked of me so many times but from the question it is clear that we developers absolutely want to learn new things and along with that we do want to continue to use our most efficient developer tools. Now here is the answer to the question:

“Absolutely, you can continue to use any of the following most popular SQL clients.”
NuoDB supports the three most popular 3rd-party SQL clients. In all the leading development environments there are always more than one database installed and managing each of them with a different tool is often a very difficult task. Developers like to use one tool, which can control most of the databases. Once developers are familiar with one database tool it is very difficult for them to switch to another tool. This is particularly difficult when we developers find that tool to be the key reason for our efficiency.

Let us see how to install each of the NuoDB supported 3rd party tools along with a quick tutorial on how to go about using them.

SQuirreL SQL Client

First download SQuirreL Universal SQL client. On the Windows platform you can double-click on the file and it will install the SQuirrel client. Once it is installed, open the application and it will bring up the following screen. Now go to the Drivers tab on the left side and scroll it down. You will find NuoDB mentioned there.

Now right click over it and click on Modify Driver.

Now here is where you need to make sure that you make proper entries or your client will not work with the database. Enter following values:
Name: NuoDB
Example URL: jdbc:com:nuodb://localhost:48004/test
Website URL: http://www.nuodb.com

Now click on the Extra Class Path tab and Add the location of the nuodbjdbc.jar file. If you are following my blog posts and have installed NuoDB in the default location, you will find the default path as C:\Program Files\NuoDB\jar\nuodbjdbc.jar.

The class name of the driver is automatically populated.


Once you click OK you will see that there is a small icon displayed to the left of NuoDB, which shows that you have successfully configured and installed the NuoDB driver.

Now click on the tab of Alias tab and you can notice that it is empty. Now click on the big Plus icon and it will open screen of adding an alias.

“Alias” means nothing more than adding a database to your system. The database name of the original installation can be anything and, if you wish, you can register the database with any other alternative name.

Here are the details you should fill into the Alias screen below.

Name: Test (or your preferred alias)
Driver: NuoDB
URL: jdbc:com:nuodb://localhost:48004/test (This is for test database)
User Name: dba (This is the username which I entered for test Database)
Password: goalie (This is the password which I entered for test Database)
Check Auto Logon and Connect at Startup and click on OK.

That’s it! You are done. On the right side you will see a table name and on the left side you will see various tabs with all the relevant details from respective table. You can see various metadata, schemas, data types and other information in the table.

In addition, you can also generate script and do various important tasks related to database.

You can see how easy it is to configure NuoDB with the SQuirreL Client and get going with it immediately.

SQL Workbench/J

This is another wonderful client tool, which works very well with NuoDB. The best part is that in the Driver dropdown you will see NuoDB being mentioned there. Click here todownload  SQL Workbench/J Universal SQL client.

The download process is straight forward and the installation is a very easy process for SQL Workbench/J. As soon as you open the client, you will notice on following screen the NuoDB driver when selecting a New Connection Profile.

Select NuoDB from the drop down and click on OK.


In the driver information, enter following details:
Driver: NuoDB (com.nuodb.jdbc.Driver)
URL: jdbc:com.nuodb://localhost/test
Username: dba
Password: goalie

While clicking on OK, it will bring up the following pop-up. Click Yes to edit the driver information.

Click on OK and it will bring you to following screen. This is the screen where you can perform various tasks.

You can write any SQL query you want and it will instantly show you the results. Now click on the database icon, which you see right on the left side of the word User=dba. 

Once you click on Database Explorer, you can perform various database related tasks.

As a developer, one of my favorite tasks is to look at the source of the table as it gives me a proper view of the structure of the database. I find SQL Workbench/J very efficient in doing the same.

DbVisualizer

DBVisualizer is another great tool, which helps you to connect to NuoDB and retrieve database information in your desired format. A developer who is familiar with DBVisualizer will find this client to be very easy to work with.

The installation of the DBVisualizer is very pretty straight forward. When we open the client, it will bring us to the following screen.

As a first step we need to set up the driver. Go to Tools >> Driver Manager.


It will bring up following screen where we set up the diver. Click on Create Driver and it will open up the driver settings on the right side.

On the right side of the area where it displays Driver Settings please enter the following values-
Name: NuoDB
URL Format: jdbc:com.nuodb://localhost:48004/test

Now under the driver path, click on the folder icon and it will ask for the location of the jar file. Provide the path as a C:\Program Files\NuoDB\jar\nuodbjdbc.jar and click OK. You will notice there is a green button displayed at the bottom right corner. This means the driver is configured properly.


Once driver is configured properly, we can go to Create Database Connection and create a database.

If the pop up show up for the Wizard. Click on No Wizard and continue to enter the settings manually.

Here is the Database Connection screen. This screen can be bit tricky. Here are the settings you need to remember to enter.
Name: NuoDB
Database Type: Generic
Driver: NuoDB
Database URL: jdbc:com.nuodb://localhost:48004/test
Database Userid: dba
Database Password: goalie
Once you enter the values, click on Connect.

Once Connect is pressed, it will change the button value to Reconnect if the connection is successfully established and it will show the connection details on lthe eft side.

When we further explore the NuoDB, we can see various tables created in our test application. We can further click on the right side screen and see various details on the table.

If you click on the Data Tab, it will display the entire data of the table.

The Tools menu also has some very interesting and cool features like Driver Manager, Data Monitor and SQL History.

Summary

Well, this was a relatively long post but I find it is extremely essential to cover all the three important clients, which we developers use in our daily database development. Here is my question to you?
Which one of the following is your favorite NuoDB 3rd-Party Database Client? (Pick One)

  • SQuirreL SQL Client
  • SQL Workbench/J
  • DbVisualizer

I will be very much eager to read your experience about NuoDB. You can download NuoDBfrom here.

Reference: Pinal Dave (http://blog.SQLAuthority.com)

 

SQL – Migrate Database from SQL Server to NuoDB – A Quick Tutorial

Data is growing exponentially and every organization with growing data is thinking of next big innovation in the world of Big Data. Big data is a indeed a future for every organization at one point of the time. Just like every other next big thing, big data has its own challenges and issues. The biggest challenge associated with the big data is to find the ideal platform which supports the scalability and growth of the data. If you are a regular reader of this blog, you must be familiar with NuoDB. I have been working with NuoDB for a while and their recent release is the best thus far.

NuoDB is an elastically scalable SQL database that can run on local host, datacenter and cloud-based resources. A key feature of the product is that it does not require sharding(read more here). Last week, I was able to install NuoDB in less than 90 seconds and have explored their Explorer and Admin sections. You can read about my experiences in these posts:

Many SQL Authority readers have been following me in my journey to evaluate NuoDB. One of the frequently asked questions I’ve received from you is if there is any way to migrate data from SQL Server to NuoDB. The fact is that there is indeed a way to do so and NuoDB provides a fantastic tool which can help users to do it. NuoDB Migrator is a command line utility that supports the migration of Microsoft SQL Server, MySQL, Oracle, and PostgreSQL schemas and data to NuoDB.

The migration to NuoDB is a three-step process:

  • NuoDB Migrator generates a schema for a target NuoDB database
  • It loads data into the target NuoDB database
  • It dumps data from the source database

Let’s see how we can migrate our data from SQL Server to NuoDB using a simple three-step approach. But before we do that we will create a sample database in MSSQL and later we will migrate the same database to NuoDB:

Setup Step 1: Build a sample data

CREATE DATABASE [Test];
 CREATE TABLE [Department](
 [DepartmentID] [smallint] NOT NULL,
 [Name] VARCHAR(100) NOT NULL,
 [GroupName] VARCHAR(100) NOT NULL,
 [ModifiedDate] [datetime] NOT NULL,
 CONSTRAINT [PK_Department_DepartmentID] PRIMARY KEY CLUSTERED
 ( [DepartmentID] ASC )
 ) ON [PRIMARY];
 INSERT INTO Department
 SELECT *
 FROM AdventureWorks2012.HumanResources.Department;

Note that I am using the SQL Server AdventureWorks database to build this sample table but you can build this sample table any way you prefer.

Setup Step 2: Install Java 64 bit

Before you can begin the migration process to NuoDB, make sure you have 64-bit Java installed on your computer. This is due to the fact that the NuoDB Migrator tool is built in Java. You can download 64-bit Java for Windows, Mac OSX, or Linux from the following link: http://java.com/en/download/manual.jsp.

One more thing to remember is that you make sure that the path in your environment settings is set to your JAVA_HOME directory or else the tool will not work. Here is how you can do it:

Go to My Computer >> Right Click >> Select Properties >> Click on Advanced System Settings >> Click on Environment Variables >> Click on New and enter the following values.

Variable Name: JAVA_HOME
Variable Value: C:\Program Files\Java\jre7

Make sure you enter your Java installation directory in the Variable Value field.

Setup Step 3: Install JDBC driver for SQL Server.

There are two JDBC drivers available for SQL Server.  Select the one you prefer to use by following one of the two links below:

In this example we will be using jTDS JDBC driver. Once you download the driver, move the driver to your NuoDB installation folder. In my case, I have moved the JAR file of the driver into the C:\Program Files\NuoDB\tools\migrator\jar folder as this is my NuoDB installation directory.

Now we are all set to start the three-step migration process from SQL Server to NuoDB:

Migration Step 1: NuoDB Schema Generation

Here is the command I use to generate a schema of my SQL Server Database in NuoDB. First I go to the folder C:\Program Files\NuoDB\tools\migrator\bin and execute the nuodb-migrator.bat file.

Note that my database name is ‘test’. Additionally my username and password is also ‘test’. You can see that my SQL Server database is running on my localhost on port 1433. Additionally, the schema of the table is ‘dbo’.

nuodb-migrator schema –source.driver=net.sourceforge.jtds.jdbc.Driver –source.url=jdbc:jtds:sqlserver://localhost:1433/ –source.username=test –source.password=test –source.catalog=test –source.schema=dbo –output.path=/tmp/schema.sql

The above script will generate a schema of all my SQL Server tables and will put it in the folder C:\tmp\schema.sql . You can open the schema.sql file and execute this file directly in your NuoDB instance. You can follow the link here to see how you can execute the SQL script in NuoDB.

Please note that if you have not yet created the schema in the NuoDB database, you should create it before executing this step.

Step 2: Generate the Dump File of the Data

Once you have recreated your schema in NuoDB from SQL Server, the next step is very easy. Here we create a CSV format dump file, which will contain all the data from all the tables from the SQL Server database.

The command to do so is very similar to the above command. Be aware that this step may take a bit of time based on your database size.

nuodb-migrator dump –source.driver=net.sourceforge.jtds.jdbc.Driver –source.url=jdbc:jtds:sqlserver://localhost:1433/ –source.username=test –source.password=test –source.catalog=test –source.schema=dbo –output.type=csv –output.path=/tmp/dump.cat

Once the above command is successfully executed you can find your CSV file in the C:\tmp\ folder. However, you do not have to do anything manually. The third and final step will take care of completing the migration process.

Migration Step 3: Load the Data into NuoDB

After building schema and taking a dump of the data, the very next step is essential and crucial. It will take the CSV file and load it into the NuoDB database.

nuodb-migrator load –target.url=jdbc:com.nuodb://localhost:48004/mytest –target.schema=dbo –target.username=test –target.password=test –input.path=/tmp/dump.cat

Please note that in the above script we are now targeting the NuoDB database, which we have already created with the name of “MyTest”. If the database does not exist, create it manually before executing the above script. I have kept the username and password as “test”, but please make sure that you create a more secure password for your database for security reasons.

Voila!  You’re Done

That’s it. You are done. It took 3 setup and 3 migration steps to migrate your SQL Server database to NuoDB.  You can now start exploring the database and build excellent, scale-out applications.

In this blog post, I have done my best to come up with simple and easy process, which you can follow to migrate your app from SQL Server to NuoDB.

Download NuoDB

I strongly encourage you to download NuoDB and go through my 3-step migration tutorial from SQL Server to NuoDB.

Additionally here are two very important blog post from NuoDB CTO Seth Proctor. He has written excellent blog posts on the concept of the Administrative Domains. NuoDB has this concept of an Administrative Domain, which is a collection of hosts that can run one or multiple databases.  Each database has its own TEs and SMs, but all are managed within the Admin Console for that particular domain.

http://www.nuodb.com/techblog/2013/03/11/getting-started-provisioning-a-domain/
http://www.nuodb.com/techblog/2013/03/14/getting-started-running-a-database/

Advertisements

One thought on “SQL – NuoDB and Third Party Explorer – SQuirreL SQL Client, SQL Workbench/J and DbVisualizer

  1. Asking questions are truly fastidious thing if you are not understanding
    something entirely, except this post gives good understanding even.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s