Configure SQL Workbench and Query Hive

In this blog post, I will show you how to connect to Hive using SQL Workbench.

Download the SQL Workbench and Drivers

Configure Drivers

• Launch SQLWorkbench64.exe
• Click on Manage Driver

• Click on Create New Entry Icon in the top left
• Click on the icon and select all the jars present in the directory \Workbench-Build127\AmazonHiveJDBC-1.0.9.1060
• Enter the Classname as : com.amazon.hive.jdbc41.HS2Driver

• Press OK

Configure Connection Profile

• Configure the setting as shown below.

• Click on SSH and Configure the settings as shown below.

• Select the .pem key which have created via EC2 Key Pairs.
• Click ok

Create Table in Hive

CREATE EXTERNAL TABLE users
    (user_id   BIGINT,
    name  STRING,
    age BIGINT
    )
STORED BY 'org.apache.hadoop.hive.dynamodb.DynamoDBStorageHandler'
TBLPROPERTIES(
    "dynamodb.table.name" = "users",
    "dynamodb.column.mapping"="user_id:user_id,name:name,age:age"
);

insert into users values(1,'naveen',35);

• Go to DyanmoDB and you can see the data