Support Questions
Find answers, ask questions, and share your expertise

Connecting to Hive via PowerShell ODBC Connection

New Contributor

Hello all!

Has anyone successfully connected to Hive via Windows Powershell? We've connected to both ORACLE and MSSQL databases via ODBC connections quite easily in the past, but we're having a hard time finding any documentation/code for connecting to Hive via Windows Powershell. We currently don't have any Azure subscriptions, so Azure Powershell/HdInsight is off the table.

Thanks for your time!

1 REPLY 1

New Contributor

Assuming you have the ODBC drivers installed the following should work.

Another few notes, this is through Knox to LLAP.


$s_ID = "youruser"
$s_PWD = "yourpassword"

$conn_string = "DRIVER={Hortonworks Hive ODBC Driver};ThriftTransport=2;SSL=1;Host=yourhost.com;Port=8443;Schema=yourdb;HiveServerType=2;AuthMech=3;HTTPPath=/gateway/default/llap;CAIssuedCertNamesMismatch=1;AllowSelfSignedServerCert=1;SSP_mapreduce.job.queuename=somequeue;SSP_tez.queue.name=somequeue;UseNativeQuery=1;UID=" + $s_ID + ";PWD=" + $s_PWD + ";"
$conn = New-Object System.Data.Odbc.OdbcConnection

$sql = "Show Tables" 

$conn.ConnectionString = $conn_string
$conn.Open()
$cmd = New-Object System.Data.Odbc.OdbcCommand
$cmd.Connection = $conn
$cmd.CommandText = $sql
$execute = $cmd.ExecuteReader()

while ( $execute.read() )
{
    $execute.GetValue(0)
}


; ;