Database Reference
In-Depth Information
Table 8-1.
Connection string to hive
Field
Description
Driver
Name of the driver: {Microsoft Hive ODBC Driver}.
Host
DNS hostname of your cluster.
Port
Connection port: The Azure HDInsight Service is 443, and the Azure HDInsight
Emulator is 10001.
Schema
Default database schema.
RowsFetchedPerBlock
Number of rows fetched per block. The recommendation is to keep it at 10,000.
HiveServerType
The HDInsight default is 2.
AuthMech
Authentication mechanism: You'll want to use a value of 6, which maps to using
the username and password you specified when the cluster was created, or a
value of 3 to connect to the Emulator.
The default length for
STRING
columns.
DefaultStringColumnLength
A sample connection string using an ODBC DSN named
HDISample
should look like this:
Provider=MSDASQL.1;Password=**********;Persist Security Info=True;User ID=admin;
Data Source=HDISample;Initial Catalog=HIVE
Note that there are only a few mandatory parameters that need to be passed in the connection string, such as
Provider, Data Source, User ID,
and
Password.
The rest of the details, like
Port Number
and
Authentication
Mechanism,
are embedded in the DSN itself and should be correctly provided while creating the DSN.
Summary
Hive acts as a data warehouse on top of HDFS (WASB, in case of HDInsight), providing an easy and familiar
SQL-like query language called HQL to fetch the underlying data. HQL queries are broken down into MapReduce
code internally, relieving the end user from writing complex MapReduce code. The Hive ODBC driver acts as an
interface between client consumers and HDInsight, enabling access from any tool supporting ODBC. In this chapter,
you learned about creating and working with Hive tables, as well as configuring and connecting to Azure HDInsight
Service and Azure HDInsight Emulator using the Microsoft Hive ODBC driver. You also learned to create a DSN-less
connection to HDInsight for client applications to connect using a connection string.
