® 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