The Progress DataDirect® for ODBC for Apache Hive™ Wire Protocol Driver User's Guide and ...

186 Pages·2017·1.5 MB·English

by  Progress Software Corporation

®
The Progress DataDirect
for
for Apache Hive Wire
ODBC ™
Protocol Driver
User's Guide and Reference
Release 8.0.1

Copyright
© 2018 Progress Software Corporation and/or one of its subsidiaries or affiliates. All
rights reserved.
ThesematerialsandallProgress®softwareproductsarecopyrightedandallrightsarereservedbyProgress
SoftwareCorporation.Theinformationinthesematerialsissubjecttochangewithoutnotice,andProgress
SoftwareCorporationassumesnoresponsibilityforanyerrorsthatmayappeartherein.Thereferencesinthese
materialstospecificplatformssupportedaresubjecttochange.
Corticon,DataDirect(anddesign),DataDirectCloud,DataDirectConnect,DataDirectConnect64,DataDirect
XMLConverters,DataDirectXQuery,DataRPM,DeliverMoreThanExpected,Icenium,KendoUI,Kinvey,
NativeScript,OpenEdge,PoweredbyProgress,Progress,ProgressSoftwareDevelopersNetwork,Rollbase,
SequeLink,Sitefinity(andDesign),Sitefinity,SpeedScript,StylusStudio,TeamPulse,Telerik,Telerik(and
Design),TestStudio,andWebSpeedareregisteredtrademarksofProgressSoftwareCorporationoroneof
itsaffiliatesorsubsidiariesintheU.S.and/orothercountries.Analytics360,AppServer,BusinessEdge,DataDirect
AutonomousRESTConnector,DataDirectSpy,SupportLink,DevCraft,Fiddler,JustAssembly,JustDecompile,
JustMock,NativeChat,NativeScriptSidekick,OpenAccess,ProDataSet,ProgressResults,ProgressSoftware,
ProVision,PSEPro,SmartBrowser,SmartComponent,SmartDataBrowser,SmartDataObjects,SmartDataView,
SmartDialog,SmartFolder,SmartFrame,SmartObjects,SmartPanel,SmartQuery,SmartViewer,SmartWindow,
andWebClientaretrademarksorservicemarksofProgressSoftwareCorporationand/oritssubsidiariesor
affiliatesintheU.S.andothercountries.JavaisaregisteredtrademarkofOracleand/oritsaffiliates.Anyother
markscontainedhereinmaybetrademarksoftheirrespectiveowners.
Updated:2018/11/29
TheProgressDataDirect®forODBCforApacheHiveWireProtocolDriver:User'sGuideandReference:Version8.0.1 3
Copyright
4 TheProgressDataDirect®forODBCforApacheHiveWireProtocolDriver:User'sGuideandReference:Version8.0.1
Contents
Table of Contents
Preface..........................................................................................................11
WelcometotheProgressDataDirectforODBCforApacheHiveWireProtocolDriver.......................11
What's New in this Release?................................................................................................................12
Conventions Used in This Guide..........................................................................................................15
About the Product Documentation........................................................................................................16
Contacting Technical Support...............................................................................................................17
Chapter 1: Getting Started ..........................................................................19
ConfiguringandConnectingonWindows.............................................................................................20
Configuring a Data Source.........................................................................................................20
TestingtheConnection...............................................................................................................21
Configuring and Connecting on UNIX and Linux..................................................................................21
Environment Configuration.........................................................................................................22
Test Loading the Driver..............................................................................................................22
ConfiguringaDataSourceintheSystemInformationFile........................................................23
TestingtheConnection...............................................................................................................24
AccessingDataWithThird-PartyApplications......................................................................................24
Chapter 2: What Is ODBC?..........................................................................25
How Does It Work?...............................................................................................................................26
Why Do Application Developers Need ODBC?....................................................................................26
Chapter 3: About the Apache Hive Driver..................................................27
Driver Requirements.............................................................................................................................28
Support for Multiple Environments........................................................................................................28
Support for Windows Environments...........................................................................................28
Support for UNIX and Linux Environments................................................................................30
ODBC Compliance................................................................................................................................34
VersionStringInformation.....................................................................................................................34
getFileVersionString Function....................................................................................................36
Data Types............................................................................................................................................36
RetrievingDataTypeInformation...............................................................................................37
Isolation and Lock Levels Supported....................................................................................................38
Binding Parameter Markers..................................................................................................................39
Limitations on Apache Hive Functionality.............................................................................................39
TheProgressDataDirect®forODBCforApacheHiveWireProtocolDriver:User'sGuideandReference:Version8.0.1 5
Contents
Chapter 4: Supported Features...................................................................41
Unicode Support...................................................................................................................................41
Using IP Addresses..............................................................................................................................42
Materialized Views................................................................................................................................42
Stored Procedures ...............................................................................................................................42
SQL Support.........................................................................................................................................43
Number of Connections and Statements Supported............................................................................43
Using Arrays of Parameters..................................................................................................................43
Chapter 5: Using the Driver.........................................................................45
ConfiguringandConnectingtoDataSources.......................................................................................45
Configuring the Product on UNIX/Linux.....................................................................................46
Data Source Configuration through a GUI.................................................................................55
Using a Connection String.........................................................................................................66
UsingaLogonDialogBox..........................................................................................................67
HTTP Mode................................................................................................................................67
Performance Considerations................................................................................................................68
Using Security.......................................................................................................................................70
Authentication............................................................................................................................70
Data Encryption Across the Network.........................................................................................73
SSL Encryption..........................................................................................................................74
Apache Knox.........................................................................................................................................79
Apache ZooKeeper...............................................................................................................................80
Configuring Apache ZooKeeper for Kerberos authentication....................................................82
Chapter 6: Troubleshooting........................................................................85
Diagnostic Tools....................................................................................................................................85
ODBC Trace...............................................................................................................................85
The Test Loading Tool................................................................................................................88
ODBC Test.................................................................................................................................89
The example Application............................................................................................................89
Other Tools.................................................................................................................................89
Error Messages.....................................................................................................................................90
Troubleshooting....................................................................................................................................91
Setup/Connection Issues...........................................................................................................91
Interoperability Issues................................................................................................................92
Performance Issues...................................................................................................................93
Chapter 7: Connection Option Descriptions.............................................95
ArraySize..............................................................................................................................................99
Array Fetch Size.................................................................................................................................100
6 TheProgressDataDirect®forODBCforApacheHiveWireProtocolDriver:User'sGuideandReference:Version8.0.1
Contents
Array Insert Size.................................................................................................................................101
Authentication Method........................................................................................................................101
Batch Mechanism ..............................................................................................................................102
Catalog Mode......................................................................................................................................103
Cookie Name......................................................................................................................................104
Crypto Protocol Version......................................................................................................................104
CryptoLibName...................................................................................................................................105
Data Source Name.............................................................................................................................107
Database Name..................................................................................................................................107
DefaultBufferSizeforLong/LOBColumns(inKb).............................................................................108
Description..........................................................................................................................................108
Enable Cookie Authentication.............................................................................................................109
Enable SQLDescribeParam................................................................................................................110
Encryption Method..............................................................................................................................110
GSS Client Library..............................................................................................................................111
Host Name..........................................................................................................................................112
Host Name In Certificate.....................................................................................................................113
HTTP Path..........................................................................................................................................114
IANAAppCodePage............................................................................................................................115
Key Password.....................................................................................................................................116
Keystore..............................................................................................................................................116
Keystore Password.............................................................................................................................117
Login Timeout.....................................................................................................................................118
Max String Size...................................................................................................................................118
Max Varchar Size................................................................................................................................119
Min Long Varchar Size........................................................................................................................120
Port Number........................................................................................................................................121
Proxy User..........................................................................................................................................121
Remove Column Qualifiers.................................................................................................................122
ServicePrincipalName.......................................................................................................................123
SSLLibName.......................................................................................................................................124
StringDescribeType...........................................................................................................................125
TCP Keep Alive...................................................................................................................................126
Transaction Mode...............................................................................................................................126
Transport Mode...................................................................................................................................127
Truststore............................................................................................................................................128
Truststore Password...........................................................................................................................129
Use Current Schema for Catalog Functions.......................................................................................129
Use Native Catalog Functions............................................................................................................130
User Name..........................................................................................................................................131
ValidateServerCertificate...................................................................................................................131
Varchar Threshold...............................................................................................................................132
ZookeeperNamespace......................................................................................................................133
Zookeeper Discovery .........................................................................................................................133
TheProgressDataDirect®forODBCforApacheHiveWireProtocolDriver:User'sGuideandReference:Version8.0.1 7
Contents
Chapter 8: SQL Functionality for the Driver for Apache Hive................135
Data Definition Language (DDL).........................................................................................................136
SelectingDataWiththeDriver............................................................................................................136
Select List.................................................................................................................................136
FromClause........................................................................................................................................137
Group By Clause.................................................................................................................................137
Having Clause ....................................................................................................................................137
Insert...................................................................................................................................................138
Order By Clause.................................................................................................................................138
For Update Clause..............................................................................................................................139
Set Operators......................................................................................................................................139
Subqueries..........................................................................................................................................139
SQL Expressions................................................................................................................................139
Constants.................................................................................................................................140
Numeric Operators...................................................................................................................140
Character Operator..................................................................................................................140
Relational Operators................................................................................................................140
Logical Operators.....................................................................................................................141
Functions..................................................................................................................................142
Restrictions.........................................................................................................................................143
Update Restrictions..................................................................................................................143
Stored Procedures...................................................................................................................143
Views........................................................................................................................................143
Other Restrictions....................................................................................................................144
Part I: Reference.........................................................................................145
Chapter 9: Code Page Values............................................................147
IANAAppCodePage Values......................................................................................................147
Chapter 10: ODBC API and Scalar Functions..................................153
API Functions...........................................................................................................................153
Scalar Functions......................................................................................................................156
String Functions............................................................................................................157
Numeric Functions........................................................................................................159
Date and Time Functions..............................................................................................160
System Functions..........................................................................................................162
Chapter 11: Internationalization, Localization, and Unicode.........163
Internationalization and Localization........................................................................................163
8 TheProgressDataDirect®forODBCforApacheHiveWireProtocolDriver:User'sGuideandReference:Version8.0.1
Contents
Locale............................................................................................................................164
Language......................................................................................................................164
Country..........................................................................................................................164
Variant...........................................................................................................................165
Unicode Character Encoding...................................................................................................165
Background...................................................................................................................165
UnicodeSupportinDatabases......................................................................................166
Unicode Support in ODBC............................................................................................166
Unicode and Non-Unicode ODBC Drivers...............................................................................167
Function Calls................................................................................................................167
Data...............................................................................................................................169
Default Unicode Mapping..............................................................................................170
DriverManagerandUnicodeEncodingonUNIX/Linux...........................................................170
References....................................................................................................................171
Character Encoding in the odbc.ini and odbcinst.ini Files.......................................................171
Chapter 12: Designing ODBC Applications for Performance
Optimization....................................................................................173
Using Catalog Functions..........................................................................................................174
Caching Information to Minimize the Use of Catalog Functions...................................174
Avoiding Search Patterns..............................................................................................175
UsingaDummyQuerytoDetermineTableCharacteristics..........................................175
Retrieving Data........................................................................................................................176
RetrievingLongData.....................................................................................................176
Reducing the Size of Data Retrieved............................................................................176
UsingBoundColumns...................................................................................................177
UsingSQLExtendedFetchInsteadofSQLFetch...........................................................177
Choosing the Right Data Type......................................................................................178
Selecting ODBC Functions......................................................................................................178
Using SQLPrepare/SQLExecute and SQLExecDirect..................................................178
Using Arrays of Parameters..........................................................................................178
Using the Cursor Library...............................................................................................179
Managing Connections and Updates.......................................................................................180
ManagingConnections..................................................................................................180
Managing Commits in Transactions..............................................................................180
Choosing the Right Transaction Model.........................................................................181
UsingPositionedUpdatesandDeletes.........................................................................181
Using SQLSpecialColumns...........................................................................................181
Chapter 13: Using Indexes................................................................183
Introduction..............................................................................................................................183
ImprovingRow SelectionPerformance....................................................................................184
IndexingMultipleFields............................................................................................................184
TheProgressDataDirect®forODBCforApacheHiveWireProtocolDriver:User'sGuideandReference:Version8.0.1 9
Contents
DecidingWhichIndexestoCreate...........................................................................................185
Improving Join Performance....................................................................................................186
Chapter 14: Locking and Isolation Levels........................................187
Locking.....................................................................................................................................187
Isolation Levels........................................................................................................................188
LockingModesandLevels.......................................................................................................189
Chapter 15: SSL Encryption Cipher Suites......................................191
Chapter 16: WorkAround Options....................................................197
Chapter 17: Threading.......................................................................201
Index.............................................................................................................203
10 TheProgressDataDirect®forODBCforApacheHiveWireProtocolDriver:User'sGuideandReference:Version8.0.1