How to install beehive on windows, to allow remote execution of hql scripts

Question

How do you run beeline on local machine once you've downloaded the jar files?

According to this answer (for Ubuntu), you don't need to do a complete install of Hive to run the beeline CLI. You just need to download the relevant jars.

To determine the files to download for the beeline CLI, I've followed the instructions here, and created a script to run in Git Bash: https://learn.microsoft.com/en-in/azure/hdinsight/hadoop/apache-hadoop-connect-hive-jdbc-driver#connect-with-squirrel-sql-client

#!/bin/bash

# References:
# -----------
#
#   Insert multi-line string into a file
#       https://stackoverflow.com/a/23929367/893766
#
#   Setup ssh programatically
#       https://stackoverflow.com/a/3659691/893766
#       https://superuser.com/a/411991/174619
#       https://www.digitalocean.com/community/questions/new-ssh-key-for-a-new-different-user?answer=38759
#       https://superuser.com/a/1052675/174619
#
#   Relevant files to copy from cluster for hive drivers
#       https://learn.microsoft.com/en-in/azure/hdinsight/hadoop/apache-hadoop-connect-hive-jdbc-driver#connect-with-squirrel-sql-client
#
#   Loop through array of strings
#       https://stackoverflow.com/a/8880633/893766
#
#
# Helpful commands
# ----------------
#
#   Copy and paste into terminal and run to generate removal command
#       echo "ssh hive ${SSH_HOSTNAME} \"sed -i.bak '/${USERNAME}@${HOSTNAME}/d' ~/.ssh/authorized_keys\""


# control variables
echo "------------------------------------------------------------------------"
echo "SETTING CONTROL VARIABLES"
echo
echo "..."
HDINSIGHT_CLUSTER_NAME="<CLUSTERNAME>.azurehdinsight.net"
HDINSIGHT_USER_NAME="sshuser"
SSH_HOST_NAME="hive"
SSH_KEY_PATH=~/.ssh/"${SSH_HOST_NAME}"
LOCAL_HIVE_DIR="/c/ProgramData/hive"
SSH_CONFIG_PATH=~/.ssh/config
SSH_CONFIG="Host ${SSH_HOST_NAME}
   Hostname ${HDINSIGHT_CLUSTER_NAME}
   User ${HDINSIGHT_USER_NAME}
   IdentityFile ${SSH_KEY_PATH}
"
declare -a FILE_GLOBS=(
    "/usr/hdp/current/hadoop-client/hadoop-common.jar"
    "/usr/hdp/current/hadoop-client/hadoop-auth.jar"
    "/usr/hdp/current/hadoop-client/lib/log4j-*.jar"
    "/usr/hdp/current/hadoop-client/lib/slf4j-*.jar"
    "/usr/hdp/current/hive-client/lib/hive-*-1.2*.jar"
    "/usr/hdp/current/hive-client/lib/httpclient-*.jar"
    "/usr/hdp/current/hive-client/lib/httpcore-*.jar"
    "/usr/hdp/current/hive-client/lib/libthrift-*.jar"
    "/usr/hdp/current/hive-client/lib/libfb*.jar"
    "/usr/hdp/current/hive-client/lib/commons-logging-*.jar"
)
echo


# create ssh key for hive access
echo "------------------------------------------------------------------------"
echo "CREATING SSH KEY"
echo 
yes "y" | ssh-keygen -t rsa -N "" -f ${SSH_KEY_PATH}
echo 


# add hive ssh config for easier use in the terminal
echo "------------------------------------------------------------------------"
echo "SETTING SSH CONFIG FOR NEW KEY"
echo
echo -e "${SSH_CONFIG}" | tee ${SSH_CONFIG_PATH}
echo


# copy ssh key to edge node
echo "------------------------------------------------------------------------"
echo "COPYING SSH KEY TO EDGE NODE FOR SECURE PASSWORD-LESS ACCESS"
echo 
ssh-copy-id -i ${SSH_KEY_PATH} $HDINSIGHT_USER_NAME@$HDINSIGHT_CLUSTER_NAME
echo

# create local directory for hive drivers
echo "------------------------------------------------------------------------"
echo "CREATING DIRECTORY FOR HIVE DRIVERS"
echo 
mkdir -p -v ${LOCAL_HIVE_DIR}
echo

# download hive drivers from edge node to local directory
echo "------------------------------------------------------------------------"
echo "DOWNLOADING HIVE DRIVERS TO:"
echo "\t${LOCAL_HIVE_DIR}"
echo 
for glob in "${FILE_GLOBS[@]}"
do
   echo "Processing glob: ${glob}"
   echo -e "\tscp -p ${SSH_HOST_NAME}:${glob} ${LOCAL_HIVE_DIR}"
   scp -p ${SSH_HOST_NAME}:"${glob}" ${LOCAL_HIVE_DIR}
   echo
done
echo


# inform user of ssh usage
echo "------------------------------------------------------------------------"
echo "OUTPUT"
echo -e "
    Command to connect to edge node:
        ssh ${SSH_HOST_NAME}

    Hive drivers installed at:
        ${LOCAL_HIVE_DIR}

    To configure JDBC clients visit:
        https://learn.microsoft.com/en-us/azure/hdinsight/hadoop/apache-hadoop-connect-hive-jdbc-driver

    Use the 'beeline' commandline client for submitting and debugging scripts:
        https://cwiki.apache.org/confluence/display/Hive/HiveServer2+Clients#HiveServer2Clients-Beeline%E2%80%93CommandLineShell!quit

    ssh key location:
        ${SSH_KEY_PATH}

    ssh config location:
        ${SSH_CONFIG_PATH}

    view authorised ssh keys on the edge node with the following command:
        ssh ${SSH_HOST_NAME} \"cat ~/.ssh/authorized_keys\"

    you can remove your ssh key from the edge node with the following command:
        ssh ${SSH_HOST_NAME} \"sed -i.bak '/${USERNAME}@${HOSTNAME}/d' ~/.ssh/authorized_keys\"
"
echo 
echo "Driver installation script has finished running"

This downloaded the following files:

$ ls -la /c/ProgramData/hive
total 56204
drwxr-xr-x 1 adria 197609        0 Feb 26 15:01 .
drwxr-xr-x 1 adria 197609        0 Feb 26 16:20 ..
-rw-r--r-- 1 adria 197609    62050 Apr  3  2017 commons-logging-1.1.3.jar
-rw-r--r-- 1 adria 197609    79075 Apr  4  2017 hadoop-auth.jar
-rw-r--r-- 1 adria 197609  3604047 Apr  4  2017 hadoop-common.jar
-rw-r--r-- 1 adria 197609   122837 Apr  4  2017 hive-accumulo-handler-1.2.1000.2.5.4.2-7.jar
-rw-r--r-- 1 adria 197609    49706 Apr  4  2017 hive-ant-1.2.1000.2.5.4.2-7.jar
-rw-r--r-- 1 adria 197609   144508 Apr  4  2017 hive-beeline-1.2.1000.2.5.4.2-7.jar
-rw-r--r-- 1 adria 197609    40077 Apr  4  2017 hive-cli-1.2.1000.2.5.4.2-7.jar
-rw-r--r-- 1 adria 197609   342718 Apr  4  2017 hive-common-1.2.1000.2.5.4.2-7.jar
-rw-r--r-- 1 adria 197609   122723 Apr  4  2017 hive-contrib-1.2.1000.2.5.4.2-7.jar
-rw-r--r-- 1 adria 197609 21703871 Apr  4  2017 hive-exec-1.2.1000.2.5.4.2-7.jar
-rw-r--r-- 1 adria 197609   118706 Apr  4  2017 hive-hbase-handler-1.2.1000.2.5.4.2-7.jar
-rw-r--r-- 1 adria 197609    29113 Apr  4  2017 hive-hwi-1.2.1000.2.5.4.2-7.jar
-rw-r--r-- 1 adria 197609   106388 Apr  4  2017 hive-jdbc-1.2.1000.2.5.4.2-7.jar
-rw-r--r-- 1 adria 197609 19636098 Apr  4  2017 hive-jdbc-1.2.1000.2.5.4.2-7-standalone.jar
-rw-r--r-- 1 adria 197609  6057211 Apr  4  2017 hive-metastore-1.2.1000.2.5.4.2-7.jar
-rw-r--r-- 1 adria 197609   924634 Apr  4  2017 hive-serde-1.2.1000.2.5.4.2-7.jar
-rw-r--r-- 1 adria 197609  1983806 Apr  4  2017 hive-service-1.2.1000.2.5.4.2-7.jar
-rw-r--r-- 1 adria 197609    32386 Apr  4  2017 hive-shims-0.20S-1.2.1000.2.5.4.2-7.jar
-rw-r--r-- 1 adria 197609    62039 Apr  4  2017 hive-shims-0.23-1.2.1000.2.5.4.2-7.jar
-rw-r--r-- 1 adria 197609    10419 Apr  4  2017 hive-shims-1.2.1000.2.5.4.2-7.jar
-rw-r--r-- 1 adria 197609   112764 Apr  4  2017 hive-shims-common-1.2.1000.2.5.4.2-7.jar
-rw-r--r-- 1 adria 197609    13562 Apr  4  2017 hive-shims-scheduler-1.2.1000.2.5.4.2-7.jar
-rw-r--r-- 1 adria 197609   719304 Apr  3  2017 httpclient-4.4.jar
-rw-r--r-- 1 adria 197609   321639 Apr  3  2017 httpcore-4.4.jar
-rw-r--r-- 1 adria 197609   313702 Apr  3  2017 libfb303-0.9.3.jar
-rw-r--r-- 1 adria 197609   234201 Apr  3  2017 libthrift-0.9.3.jar
-rw-r--r-- 1 adria 197609   489884 Apr  4  2017 log4j-1.2.17.jar
-rw-r--r-- 1 adria 197609    32119 Apr  4  2017 slf4j-api-1.7.10.jar
-rw-r--r-- 1 adria 197609     8866 Apr  4  2017 slf4j-log4j12-1.7.10.jar

Java info:

$ java -version
java version "1.8.0_161"
Java(TM) SE Runtime Environment (build 1.8.0_161-b12)
Java HotSpot(TM) Client VM (build 25.161-b12, mixed mode, sharing)

$ which java
/c/ProgramData/Oracle/Java/javapath/java

Now comes the part I have no idea about...

How do you run beeline on local machine once you've downloaded the jar files?

What I've tried

$ cd /c/ProgramData/hive

$ java hive-beeline-1.2.1000.2.5.4.2-7.jar
Error: Could not find or load main class hive-beeline-1.2.1000.2.5.4.2-7.jar

$ java -jar hive-beeline-1.2.1000.2.5.4.2-7.jar
no main manifest attribute, in hive-beeline-1.2.1000.2.5.4.2-7.jar

$ java -cp /c/ProgramData/hive -jar hive-beeline-1.2.1000.2.5.4.2-7.jar
no main manifest attribute, in hive-beeline-1.2.1000.2.5.4.2-7.jar

UPDATE #1

I've uninstalled the JRE 8 and installed JDK 9

$ echo $JAVA_HOME
C:\Program Files\Java\jdk-9.0.4\bin

$ java -version
java version "9.0.4"
Java(TM) SE Runtime Environment (build 9.0.4+11)
Java HotSpot(TM) 64-Bit Server VM (build 9.0.4+11, mixed mode)

$ which java
/c/Program Files/Java/jdk-9.0.4/bin/java

$ javac -version
javac 9.0.4

Contents of the beeline jar are as follows:

$ jar tvf hive-beeline-1.2.1000.2.5.4.2-7.jar
     0 Tue Apr 04 12:16:28 AEST 2017 META-INF/
   436 Tue Apr 04 12:16:26 AEST 2017 META-INF/MANIFEST.MF
     0 Tue Apr 04 12:16:26 AEST 2017 org/
     0 Tue Apr 04 12:16:26 AEST 2017 org/apache/
     0 Tue Apr 04 12:16:26 AEST 2017 org/apache/hive/
     0 Tue Apr 04 12:16:26 AEST 2017 org/apache/hive/beeline/
     0 Tue Apr 04 12:16:26 AEST 2017 org/apache/hive/beeline/util/
  9839 Tue Apr 04 12:16:26 AEST 2017 BeeLine.properties
  1139 Tue Apr 04 12:16:26 AEST 2017 beeline-log4j.properties
   164 Tue Apr 04 12:16:26 AEST 2017 META-INF/NOTICE
 11358 Tue Apr 04 12:16:26 AEST 2017 META-INF/LICENSE
 27286 Tue Apr 04 12:16:26 AEST 2017 META-INF/DEPENDENCIES
  2308 Tue Apr 04 12:16:26 AEST 2017 sql-keywords.properties
  4658 Tue Apr 04 12:16:26 AEST 2017 org/apache/hive/beeline/Reflector.class
  2767 Tue Apr 04 12:16:26 AEST 2017 org/apache/hive/beeline/AbstractCommandHandler.class
  1196 Tue Apr 04 12:16:26 AEST 2017 org/apache/hive/beeline/BeeLineCompleter.class
 38069 Tue Apr 04 12:16:26 AEST 2017 org/apache/hive/beeline/Commands.class
  5901 Tue Apr 04 12:16:26 AEST 2017 org/apache/hive/beeline/ColorBuffer.class
  1790 Tue Apr 04 12:16:26 AEST 2017 org/apache/hive/beeline/XMLElementOutputFormat.class
  1007 Tue Apr 04 12:16:26 AEST 2017 org/apache/hive/beeline/DatabaseConnection$Schema$Table.class
  2309 Tue Apr 04 12:16:26 AEST 2017 org/apache/hive/beeline/BeeLineCommandCompleter.class
  1973 Tue Apr 04 12:16:26 AEST 2017 org/apache/hive/beeline/ReflectiveCommandHandler.class
   264 Tue Apr 04 12:16:26 AEST 2017 org/apache/hive/beeline/BeeLineOpts$Env.class
  2026 Tue Apr 04 12:16:26 AEST 2017 org/apache/hive/beeline/Commands$1.class
   894 Tue Apr 04 12:16:26 AEST 2017 org/apache/hive/beeline/DriverInfo.class
  4785 Tue Apr 04 12:16:26 AEST 2017 org/apache/hive/beeline/HiveSchemaHelper.class
   181 Tue Apr 04 12:16:26 AEST 2017 org/apache/hive/beeline/OutputFormat.class
  2382 Tue Apr 04 12:16:26 AEST 2017 org/apache/hive/beeline/HiveSchemaHelper$OracleCommandParser.class
  1522 Tue Apr 04 12:16:26 AEST 2017 org/apache/hive/beeline/HiveSchemaHelper$NestedScriptParser$CommandType.class
   669 Tue Apr 04 12:16:26 AEST 2017 org/apache/hive/beeline/Commands$2.class
  4188 Tue Apr 04 12:16:26 AEST 2017 org/apache/hive/beeline/SeparatedValuesOutputFormat.class
  2671 Tue Apr 04 12:16:26 AEST 2017 org/apache/hive/beeline/DatabaseConnection$Schema.class
   902 Tue Apr 04 12:16:26 AEST 2017 org/apache/hive/beeline/HiveSchemaHelper$NestedScriptParser.class
  2532 Tue Apr 04 12:16:26 AEST 2017 org/apache/hive/beeline/Rows$Row.class
  1128 Tue Apr 04 12:16:26 AEST 2017 org/apache/hive/beeline/AbstractOutputFormat.class
  1219 Tue Apr 04 12:16:26 AEST 2017 org/apache/hive/beeline/BeeLine$1.class
   194 Tue Apr 04 12:16:26 AEST 2017 org/apache/hive/beeline/BeeLineSignalHandler.class
 17720 Tue Apr 04 12:16:26 AEST 2017 org/apache/hive/beeline/HiveSchemaTool.class
 16547 Tue Apr 04 12:16:26 AEST 2017 org/apache/hive/beeline/BeeLineOpts.class
   395 Tue Apr 04 12:16:26 AEST 2017 org/apache/hive/beeline/BeeLineOpts$Ignore.class
  2490 Tue Apr 04 12:16:26 AEST 2017 org/apache/hive/beeline/IncrementalRows.class
  3181 Tue Apr 04 12:16:26 AEST 2017 org/apache/hive/beeline/Rows.class
  2599 Tue Apr 04 12:16:26 AEST 2017 org/apache/hive/beeline/HiveSchemaHelper$DerbyCommandParser.class
  4112 Tue Apr 04 12:16:26 AEST 2017 org/apache/hive/beeline/TableOutputFormat.class
  2289 Tue Apr 04 12:16:26 AEST 2017 org/apache/hive/beeline/DeprecatedSeparatedValuesOutputFormat.class
  1209 Tue Apr 04 12:16:26 AEST 2017 org/apache/hive/beeline/OutputFile.class
  1075 Tue Apr 04 12:16:26 AEST 2017 org/apache/hive/beeline/TableNameCompletor.class
  2207 Tue Apr 04 12:16:26 AEST 2017 org/apache/hive/beeline/util/QFileClient$RegexFilterSet.class
   242 Tue Apr 04 12:16:26 AEST 2017 org/apache/hive/beeline/util/QFileClient$1.class
  9552 Tue Apr 04 12:16:26 AEST 2017 org/apache/hive/beeline/util/QFileClient.class
  1059 Tue Apr 04 12:16:26 AEST 2017 org/apache/hive/beeline/SunSignalHandler.class
  1912 Tue Apr 04 12:16:26 AEST 2017 org/apache/hive/beeline/XMLAttributeOutputFormat.class
   431 Tue Apr 04 12:16:26 AEST 2017 org/apache/hive/beeline/BooleanCompleter.class
  1289 Tue Apr 04 12:16:26 AEST 2017 org/apache/hive/beeline/DatabaseConnection$1.class
  2928 Tue Apr 04 12:16:26 AEST 2017 org/apache/hive/beeline/HiveSchemaHelper$PostgresCommandParser.class
  1792 Tue Apr 04 12:16:26 AEST 2017 org/apache/hive/beeline/BeeLine$BeelineParser.class
  4068 Tue Apr 04 12:16:26 AEST 2017 org/apache/hive/beeline/HiveSchemaHelper$AbstractCommandParser.class
   598 Tue Apr 04 12:16:26 AEST 2017 org/apache/hive/beeline/ColorBuffer$ColorAttr.class
  2220 Tue Apr 04 12:16:26 AEST 2017 org/apache/hive/beeline/VerticalOutputFormat.class
  4011 Tue Apr 04 12:16:26 AEST 2017 org/apache/hive/beeline/SQLCompleter.class
  2279 Tue Apr 04 12:16:26 AEST 2017 org/apache/hive/beeline/BufferedRows.class
  9246 Tue Apr 04 12:16:26 AEST 2017 org/apache/hive/beeline/DatabaseConnection.class
  1807 Tue Apr 04 12:16:26 AEST 2017 org/apache/hive/beeline/DatabaseConnections.class
  2370 Tue Apr 04 12:16:26 AEST 2017 org/apache/hive/beeline/HiveSchemaHelper$MSSQLCommandParser.class
   873 Tue Apr 04 12:16:26 AEST 2017 org/apache/hive/beeline/DatabaseConnection$Schema$Table$Column.class
   663 Tue Apr 04 12:16:26 AEST 2017 org/apache/hive/beeline/BeeLineOpts$1.class
  2195 Tue Apr 04 12:16:26 AEST 2017 org/apache/hive/beeline/HiveSchemaHelper$AzureDBCommandParser.class
  2792 Tue Apr 04 12:16:26 AEST 2017 org/apache/hive/beeline/HiveSchemaHelper$MySqlCommandParser.class
  6656 Tue Apr 04 12:16:26 AEST 2017 org/apache/hive/beeline/ClassNameCompleter.class
 47773 Tue Apr 04 12:16:26 AEST 2017 org/apache/hive/beeline/BeeLine.class
   467 Tue Apr 04 12:16:26 AEST 2017 org/apache/hive/beeline/CommandHandler.class
     0 Tue Apr 04 12:16:28 AEST 2017 META-INF/maven/
     0 Tue Apr 04 12:16:28 AEST 2017 META-INF/maven/org.apache.hive/
     0 Tue Apr 04 12:16:28 AEST 2017 META-INF/maven/org.apache.hive/hive-beeline/
  5422 Tue Apr 04 12:08:16 AEST 2017 META-INF/maven/org.apache.hive/hive-beeline/pom.xml
   125 Tue Apr 04 12:16:26 AEST 2017 META-INF/maven/org.apache.hive/hive-beeline/pom.properties

Not yet, have been puled onto other work, but maybe revisiting this within the next few months, so hopefully will get sometime to resolve. — Adrian Torrie, Dec 03 '18 at 04:56

How to install beehive on windows, to allow remote execution of hql scripts

UPDATE #1

0 Answers0