MAPR Working with Hive (i) ODBC Connector for--hive

This page contains more information about setting up and using the ODBC Connector hive. This page contains the following topics: Before you start the SQL Connector software and hardware requirements to install and configure authentication in DSN configuration SSL configuration DSN run SQLPrepare optimization Note data type HIVEQL notes in Application Note Microsoft Access Microsoft E Xcel/Query screen before the desktop starts

The ODBC Connector for the MAPR hive is an ODBC driver for the Apache Hive 0.7.0 and later, in accordance with the ODBC 3.52 specification. Using the ODBC driver, configure the definition of a data source name (DSN) that specifies how to connect to the hive. DSN is typically managed by the operating system and can be used by multiple applications. Some applications do not use DSN. You will need to consult the documentation for your particular application to learn how to connect using ODBC.

The standard query Language database is SQL. HiveQL, standard query Language hive, includes a subset of ANSI SQL-92. A connection using an ODBC hive application may need to modify the query if the query uses the SQL functionality that is not present in the hive. Using a SQL application recognizes HIVEQL, but may not provide access to HIVEQL-specific features, such as multiple table insertions.

See HIVEQL Wiki for up-to-date information hiveql. in the SQL connector

The SQL Connector feature transforms the standard SQL-92 query into an equivalent HIVEQL query. Syntax translation and structural transformation in SQL connector. For example: Quoted identifiers: When referencing identifiers, Hiveql uses anti-quotation marks ('), while SQL uses double quotation marks ("), even if the driver reports a quotation mark as a quotation mark character, some applications still produce double-quote identifiers. Table aliases: HIVEQL does not support the AS keyword between tables
Reference and its alias. The SQL syntax for the Join,inner join and Cross join is translated into the HIVEQL join syntax. SQL TOP n queries are converted to HIVEQL limit queries. software and hardware requirements

ODBC connector requirements for using the MAPR hive on Windows: Use Windows®7 Pro or windows®2008 R2. Both 32-bit and 64-bit editions are supported. Microsoft's Visual C + + 2010 can be used to distribute the running time required to install a developed application on a computer that is running with Visual C + + 2010 that does not have Visual C + +. The Hadoop cluster installs the hive's services and runs. You should find out the port from the Cluster Administrator's hostname or IP address hive service that the service is running on. (The default port for the hive is 10000.) ) Installation and configuration

Version of the 32-bit and 64-bit applications that have connectors. The 64-bit version of the connector is only available for 64-bit dsn,32-bit connectors for 32-bit DSN only. Because the 64-bit Windows machine can run both 64-bit and 32-bit applications, the DSN established with both types of applications works with the two versions of the installation connector. If two 32-bit connectors and 64-bit connectors are installed, you must configure the DSN to be separate for each individual data Source Administrator.to install the ODBC Connector for the hive:Run Setup to get started: To install a 64-bit connector, download and run Http://package.mapr.com/tools/MapR-ODBC/MapR_odbc_2.1.0_x64.exe. To install a 32-bit connector, download and run Http://package.mapr.com/tools/MapR-ODBC/MapR_odbc_2.1.0_x86.exe. Follow these steps to clickNext StepAfter each: Accept the license agreement. Select an installation folder. In the Information window, clickNext Step。 At the completion of ... window, clickFinish (completed). Install the DSN that corresponds to your hive server.to create a data source name (DSN)From the Start menu, Open Data Source Manager. For example:Start > MapR hive ODBC driver 2.0> 64-bit ODBC driver manager
InUser DSNtab, clickAddTo open the Create New Data Source dialog box.
ChooseODBC Connector for MAPR Hive, and then clickFinish (complete)To open the Hive ODBC Driver's DSN settings window.
Enter the connection information for the hive instance:
Data Source Name-The name of the specified DSN.Description-Enter a description of the DSN (optional).Host-Enter the host name or IP address of the server running HiveServer1 or HiveServer2.Port-Enter the service where the listener port is the hive.Database-Leave the default hive database connected to the default, or enter a specific database name.Hive Server Type:-Set to HiveServer1 or HiveServer2.Identity Verification-If you are using HiveServer2, set the following.mechanism:-Set the authentication mechanism that you are using. The MAPR ODBC driver supports user names, usernames and passwords, user names and passwords through SSL authentication.User name:-Set the user to run the query.Password:-User's password, if you choose one of the authentication mechanism requirements. ClickTestTo test the connection.
When you are sure that the connection is working, clickFinish (complete)。 Your new connection will appear in the list of user data sources.
Configuring SSL in DSN

Select the ODBC Data Source Administrator window from DSN, and then click Configure to display the Settings dialog box. From the Settings dialog box, click Advanced Options... to display the Advanced Options dialog box. In the SSL pane, click the check box next to allow common name host name mismatch, control whether the driver allows a common name for a CA
The issued certificate does not match the host name of the hive server. For self-signed certificates, the driver always allows the public name of the certificate to not match the host name. If you want to specify a locally trusted certificate file, click the trusted Certificates field next to browse to browse to your location in the Cacerts.pem file. The default setting uses the Pem file with the trusted CA certificate that installs the driver. Icon

The driver always accepts a self-signed SSL certificate. Advanced Options Select the use native query check box to disable the SQL Connector feature. The SQL Connector feature has been added to the driver transformation application to the query emitted by the application to convert them to hiveql in an equivalent form. If the application is hive aware and has issued HIVEQL and then shut down the SQL Connector feature avoids the additional overhead of the
query transformation. Select the run SQLPrepare quickly check box to defer query execution SQLExecute. When using native query mode, the driver executes a HIVEQL query to retrieve the result set metadata to run SQLPrepare. As a result, running SQLPrepare can be slow. Run SQLPrepare If the result set metadata is not required after the call is enabled for this option. In the fetch per block field, type the number of rows that are fetched for each block. Any positive 32-bit integer is valid. The performance elevation margin exceeds the 10000 default value of the
row. In the default string column length field, type the length of the default string column to use. The hive does not provide a length string in its column metadata column. This option allows you to adjust the length of the string column. In the decimal column scale field, type the maximum number of digits to the decimal number data type of the right. To allow a CA to issue a common name for the SSL certificate, select the allow generic name does not match check box to the host name of the group's non-hive server. This setting applies only to the user name and password (SSL) authentication mechanism and is ignored by other authentication mechanisms. Enter the path of the trusted certificate file that is included in the Trust certificate edit box to configure the driver to verify that the hive server loads the certificate from the specified file using SSL. This is only applicable to the user name and password (SSL) authentication mechanism and is ignored by other authentication mechanisms. If this setting is not set, the driver will default to use a PEM file that installs the trusted CA certificate. To create a server-side property, click the Add button, and then type the appropriate value in the key and Value fields, and then click OK . Click the Edit button to change an existing property or delete to delete an attribute. Icon

Enter set-V in the Hive CLI command line or line to display a list of the Hadoop and Hive server-side properties supported by your implementation. If you selected Hive server 2 as the Hive server type, then select or clear the Require check box for the application server-side performance query . If you selected Hive server 2, then the apply with Query server-side properties check box is selected by default. Select the check box to configure the driver to apply each server-side property that you can set by executing a query when you open a session to the Hive server. Clearing this check box configures the driver to use a more efficient method to apply server-side properties and does not involve additional network round tripping. Some hive server 2 builds are not compatible with more efficient methods. If you do not take the server-side properties into effect, the check box is cleared, and then the appropriate check box is selected. If you selected Hive server 1 hive server type, then the query for Application server-side performance check box is selected and unavailable. Configure authentication for DSN

Some client applications, such as the screen, provide some support for connecting to a data source using a driver instead of a DSN. The connection uses the ODBC data source application to send a data source that defines the appropriate authentication credentials to work with Hive server 2. Application is Hive server 1 aware, but not hive server 2 recognize and connect using a dsn-less connection will not have a device sending authentication credentials to hive server 2.
You can use the Driver Configuration tool to configure the ODBC driver with authentication credentials. Icon

Define credential precedence authentication at the data source
Configured to use the driver Configuration tool. Use the Driver Configuration tool to configure credentials for all connections using the Dsn-less connection unless the client application is a Hive server 2 aware and requires credentials for the user to make. to configure authentication for a dsn-less connection driver: Start the driver configuration from the program start menu. Select a hive server type from the drop-down list. Apply the mechanism from the drop-down authentication mechanism, and then configure any fields that are required. Optionally, click Advanced , and configure any required advanced options. Icon

The MAPR ODBC driver supports only user names , user names and Passwords , and authentication mechanisms for user names and Passwords (SSL) . Run SQLPrepare optimization

The connector currently uses the query execution to determine the metadata of the result set running SQLPrepare. The disadvantage of this is that running SQLPrepare is slow because query execution tends to be slow. You can configure the connector to speed up running SQLPrepare if you don't need the metadata for the result set. To change the behavior of running SQLPrepare, create a String value Nopsqlprepare based on your DSN. If the value is set to a value other than 0, running SQLPrepare will not use the query to perform the metadata for the exported result set. If the registry key is not defined, the default value is 0. Notes Data Type

Support for the following data types:

type	Description
TINYINT	1-byte integer
SMALLINT	2-byte integer
int	4-byte integer
BIGINT	8-byte integer
Shareholding	Single-precision floating-point number
Double	Double precision floating point number
Decimal	Decimal numbers
Boolean	True/false value
STRING	Character sequence
TIMESTAMP	Date and time values

Not yet supported: Total type (ARRAY,MAP and structure) HIVEQL considerations Cast function

HIVEQL does not support the CONVERT function, which uses the cast function to perform type conversions. Example: CAST (as <expression> <TYPE>)

Use cast in HIVEQL: Use the HIVEQL name to cast an expression from the eight supported data types in the hive. For example, an integer converted to 1.0, using cast (1.0 as INT) instead of cast (1.0 as Sql_integer). The hive does not do a series of check cast operations. For example, CAST (1000000 as Sql_tinyint) returns a TINYINT value of 64 instead of the expected error. With SQL, the hive returns a null value instead of an error if it fails to convert the data. For example, CAST ("STRING" is an int) returns NULL.

Use the cast and Boolean values: Boolean true to convert to a value of 1 Boolean false to convert to a numeric value of 0 for a value of 0 to convert to a Boolean value of false, any other number to true to convert an empty string to a Boolean value false, or to convert any other string to True

The HIVEQL string type stores the text string and corresponds to the SQL_LONGVARCHAR data type. The cast succeeds in converting the string to a number, or the conversion fails if the string contains only numeric characters.

You can adjust the string column for column length. To change the default length of the report string column, add the registry key Defaultstringcolumnlength based on your DSN and specify a value. If the registry key is not defined, it is used for a preset length of 1024 characters. Separators

The connector uses thrift to connect to the hive server. Hive returns the result set of the HIVEQL query is delimited by a tab character as a line-break delimited field. The hive does not currently escape any tabs in the field. Be sure to escape the hive data for any tab or newline character, indlucing the platform-specific newline character sequence, such as line break (LF), for use on Unix/linux/mac OS x/etc, carriage return/line feed (cr/lf ) for Windows and carriage return (CR) for older Macintosh platforms. in the Application Note in Microsoft access

Beta version	"2010" (= 14.0), 32-bit and 64-bit.
Notes	Linked tables are not available at this time.

Microsoft Excel/Query

Beta version	"2010" (= 14.0), 32-bit and 64-bit.
Notes	From the data color ramp, use the from Oth and select from Data Connection Wizard or from Microsoft Query. The former requires a pre-defined DSN and the latter supports dynamic creation of a DSN. You can use the ODBC driver to bridge the ODBC driver through OLE DB.

Screen Desktop

Beta	7.0, 32-bit only. Works with only ODBC driver volumes.
Notes	Prior to this 7.0.N version, you will need to install a TDC to maximize the driver's ability.   Starting with version 7.0.N, you can connect to the Data tab from the options by specifying the driver MAPR the configuration unit for Hadoop.