Setting up SQuirrel and Phoenix Integration for Sandbox

[#1]

Installing SQuirrel

Download the SQuirrel jar from here, as per your operating system. Open the terminal and go to the directory where you have downloaded the file. Run the following command:

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

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. I am choosing only the base and standard install. If you want other plugins, you can select them from the list given.

Configuring SQuirrel and Phoenix Integration:

1. Make sure your Sandbox and HBase is up and running.

2. Open Additional Ports.

You need all the HBase and Region Server ports forwarded, 16000, 16010, 16020, 16030. To do this, click on the Settings button for the VM instance. Then click the Network button in the pop up window. There is a port forwarding button at the bottom, click it. Click to add each of the following ports that you don't currently have.

3. Copy the Phoenix client jar to SQuirrel

SSH to your Sandbox terminal with root user.

Check the location of phoenix client jar by navigating to /usr/hdp/2.5.0.0-817/phoenix/

Check your HDP version.

Now go back to your local machine terminal and run the following command to copy the jar from sandbox to the local machine:

scp -P 2222 root@127.0.0.1:/usr/hdp/2.5.0.0-817/phoenix/phoenix-4.7.0.2.5.0.0-817-client.jar ~

Now let’s copy this jar to SQuirrel lib. On a Mac, go to Applications and click Open in Finder. Right click on SQuirrel SQL and then click on Show Package Contents. Then navigate to Contents > Resources > Java > lib and paste that phoenix client jar over here.

4. Add sandbox.hortonworks.com in /private/etc/hosts file

Type the following command

sudo vi /private/etc/hosts

And add this entry - sandbox.hortonworks.com and save the file.

5. Add Phoenix driver in 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:sandbox.hortonworks.com:2181:/hbase-unsecure

Website URL: (it should be blank)

Class Name: org.apache.phoenix.jdbc.PhoenixDriver

It should look like this:

Click OK.

6. 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 – PhoenixOnHortonworksSandbox

Driver – Phoenix

URL – This should be auto-populated when you select your driver with jdbc:phoenix:sandbox.hortonworks.com:2181:/hbase-unsecure

User Name – root

Password – same password that you use while ssh

It should look like this:

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.

7. Connect

Double click on your newly created alias and click Connect. You will see a screen like this:

You should now be successfully connected and able to run SQL queries.

[#2]

The current Sandbox VM image (which uses Docker internally) needs a few alterations to work with this guide (namely, the Docker sandbox container needs to have ports 16000 and 16020 forwarded within the VM - in addition to the host-> VM forwarding described in this article):

• Once you have adjusted VM port forwarding (step #2), login to the VM as root using the console (not ssh, as ssh will log you into the container not the VM)
• docker stop sandbox
• docker rm sandbox
• edit /root/startup_scripts/start_sandbox.sh
  • add ‘-p 16000:16000 \’
  • add ‘-p 16020:16020 \’
• restart the VM to get clean environment startup

This should clear up any Timeout issues you have been experiencing while trying to connect SQuirreL to Phoenix.

[#3]

Any idea why none of the screenshots attached in this post are not visible. I am using Oracle VM Virtualbox with HDP 2.5 and am at Step 2, but without the screenshots, it is hard to understand what to change in the port forwarding rules dialog box. This is an old thread, hoping someone is still monitoring and can reply.

[#4]

This works perfectly with Field Cloud. If you want to run some queries on phoenix by following this and Phoenix and Hbase tutorials this is an awesome demoable material

[#5]

The pictures are not visible to me as well. Beside that I get a strange error

java.util.concurrent.ExecutionException: java.lang.RuntimeException: java.sql.SQLException: ERROR 2007 (INT09): Outdated jars. The following servers require an updated phoenix.jar to be put in the classpath of HBase: region=SYSTEM.CATALOG.

The odbc driver works smoothly.