How to Integrate SQuirreL with Phoenix

If you’re looking to use a client GUI, or graphical user interface, to interact with Phoenix, you might want to give SQuirreL a try. SQuirreL is a graphical java program that lets you see the structure of a database, browse the data in tables, and perform SQL queries.

Installing SQuirreL

The first thing you’ll want to do is install SQuirreL. To do this, go here and select the appropriate download for your operating system.

Once you have downloaded the file, open a new terminal window. Navigate to wherever you downloaded the file to, and run this code:

java -jar squirrel-sql-3.6-MACOSX-install.jar

You may have to modify that code slightly based on the name of the file you downloaded (version and operating system).

Once the installer dialog pops up, follow the instructions to install SQuirreL onto your system. You can choose to select optional installs if you like. For now, I chose to just do the base and standard install:

Installing SQuirrelSQL

SQuirrelSQL is now installed on your local machine, but we’re not done yet. We still need to set it up so we can use it with Phoenix.

Configuring SQuirrelSQL for Phoenix

Before we get started, make sure you have your Hortonworks sandbox VM up and running. You should have already followed this guide to set up Phoenix on your sandbox.

Step 1 – Move Phoenix client jar to SQuirreL lib directory

In your VM, navigate to the folder where Phoenix is installed. For me that folder is /usr/hdp/2.3.0.0-2557/phoenix. You need to move the phoenix client jar from that folder to your local machine. If you have a shared folder set up between your vm and your computer, run code similar to this to copy it to your local machine:

cp /usr/hdp/2.3.0.0-2557/phoenix/phoenix-4.4.0.2.3.0.0-2557-client.jar /media/sf_hdp_shared_folder/

Once you’ve got the file on your local machine, add it to SQuirrelSQL’s lib directory. On a Mac, you do this by navigating to Applications then right clicking on SQuirrelSQL and clicking ‘Show Package Contents’:

Finding SQuirreL's lib folder

From there, navigate to Contents > Resources > Java > lib. Copy the phoenix client jar to that directory:

Adding the phoenix jar to SQuirreL's lib folder

Step 2 – Switch your VM’s network adapter to Host-only

To make things easier for us, we’re going to switch the VM’s network adapter to Host-only. This gets rid of some bugs that can pop up if you try to connect to the VM when it’s using a NAT adapter. If your VM’s network adapter is already in Host-only mode, skip ahead to step 3.

To switch to Host-only mode, first power off your Hortonworks sandbox. Open up the VirtualBox Manager, click on your Hortonworks Sandbox, and select Settings > Network. Disable your NAT adapter, or any other adapters, if they are enabled. Then, with an empty adapter spot click Enable Network Adapter. Attach it to ‘Host-only Adapter’, then select any of the available options for its Name. Leave the Advanced Settings as they are:

Host-only network settings

Troubleshooting – What if there is no option for a Host-only Adapter after the Name field?

In this case, you need to edit your VirtualBox Manager settings. Close out of the Hortonworks settings and go to VirtualBox > Preferences > Network. Select the Host-only Networks tab, then click the plus icon:

Adding a new host-only network

A new Host-only network should now be available for you to select for your Hortonworks sandbox. Make sure you go back into the settings for your Hortonworks sandbox and switch your VM’s network adapter to your new Host-only adapter. When you have done that, move on to Step 3.

Step 3 – Add hortonworks.hbase.vm to your hosts file

If you haven’t already updated your local machine’s hosts file with the ip address of your sandbox, we’re going to do that in this step. If you have already done that, skip ahead to step 4.

To add hortonworks.hbase.vm to your hosts file, open up a terminal window. On a Mac, type this code:

sudo nano /etc/hosts

You’ll be asked to enter your password, then nano (a text editor) will open. Add this line at the end of your hosts file:

192.168.56.101 hortonworks.hbase.vm

This is what my hosts file currently looks like:

Hosts file

Some important notes

  • The ip address you enter may be different than mine. To check it, type this code in your Hortonworks sandbox:
    ifconfig
  • You can call your sandbox whatever you like in the hosts file. I chose to go with hortonworks.hbase.vm, but you can call it sandbox.hortonworks.com if you like. Just remember what you called it because we’ll be using that name later.

Step 4 – Add Phoenix driver to SQuirreL

Open up SQuirrel, click the Drivers tab on the left side of the window, and click the plus button to create a new driver. Enter this information into the driver creation window:

  • Name: Phoenix
  • Example URL: jdbc:phoenix:hortonworks.hbase.vm:2181:/hbase-unsecure
  • Website URL: [Blank. Do not write anything here]
  • Class Name: org.apache.phoenix.jdbc.PhoenixDriver

It should look like this:

Phoenix Driver Settings

Click OK. You should get a message which reads “Driver class org.apache.phoenix.jdbc.PhoenixDriver successfully registered for driver definition: Phoenix”

Step 4.5 – Ensure your sandbox and HBase are running

Before continuing, make sure both your Hortonworks sandbox and HBase are running. Recall that you can check if HBase is running in Ambari at your-sandbox-ip:8080. If you’re having trouble starting HBase or getting warnings that don’t disappear after a minute or two, check out the ‘Starting HBase on the sandbox’ section of this guide.

Step 5 – Create an Alias

Switch to the Aliases tab and click the plus button to create a new alias. Enter this information in the alias creation window:

  • Name – Alias name (ex: hortonworksSandbox, whatever you want)
  • Driver – Phoenix
  • URL – This should be auto-populated when you select your driver with jdbc:phoenix:hortonworks.hbase.vm:2181:/hbase-unsecure
  • User Name – Whatever you like (ex: admin)
  • Password – Whatever you like (ex: admin)

It should look like this:

Phoenix Alias

Once you’ve filled out the above information, click Test then select Connect. A box should pop up which says “Connection successful”. Click OK then OK again to create the alias.

Step 6 – Connect

We’re almost done! Double click on your newly created alias and click Connect. You should now be successfully connected and able to run SQL queries.

For a very short guide to using SQuirreL, check out this link.

Leave a Comment.