Progress® DataDirect® for JDBC™ for Apache Spark SQL

292 Pages·2017·3.02 MB·English

by  Progress Software Corporation

Progress DataDirect
® ® for
for Apache Spark
JDBC™
SQL
™
User's Guide
Release 6.0.1

Copyright
© 2018 Progress Software Corporation and/or 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,NativeScript,
OpenEdge,PoweredbyProgress,Progress,ProgressSoftwareDevelopersNetwork,Rollbase,SequeLink,
Sitefinity(andDesign),SpeedScript,StylusStudio,TeamPulse,Telerik,Telerik(andDesign),TestStudio,and
WebSpeedareregisteredtrademarksofProgressSoftwareCorporationoroneofitsaffiliatesorsubsidiaries
intheU.S.and/orothercountries.Analytics360,AppServer,BusinessEdge,DataDirectSpy,SupportLink,
DevCraft,Fiddler,JustAssembly,JustDecompile,JustMock,Kinvey,NativeScriptSidekick,OpenAccess,
ProDataSet,ProgressResults,ProgressSoftware,ProVision,PSEPro,Sitefinity,SmartBrowser,
SmartComponent,SmartDataBrowser,SmartDataObjects,SmartDataView,SmartDialog,SmartFolder,
SmartFrame,SmartObjects,SmartPanel,SmartQuery,SmartViewer,SmartWindow,andWebClientare
trademarksorservicemarksofProgressSoftwareCorporationand/oritssubsidiariesoraffiliatesintheU.S.
andothercountries.JavaisaregisteredtrademarkofOracleand/oritsaffiliates.Anyothermarkscontained
hereinmaybetrademarksoftheirrespectiveowners.
PleaserefertothereadmeapplicabletotheparticularProgressproductreleaseforanythird-party
acknowledgementsrequiredtobeprovidedinthedocumentationassociatedwiththeProgressproduct.
Updated:2018/01/23
Progress®DataDirect®forJDBC™forApacheSparkSQL™:User'sGuide:Version6.0.1 3
Copyright
4 Progress®DataDirect®forJDBC™forApacheSparkSQL™:User'sGuide:Version6.0.1
Contents
Table of Contents
Chapter 1: Welcome to the Progress DataDirect for JDBC for Apache Spark
SQL Driver.................................................................................................11
What's New in this Release?................................................................................................................12
Requirements........................................................................................................................................13
DataSourceandDriverClasses...........................................................................................................14
Connection URL....................................................................................................................................14
VersionStringInformation.....................................................................................................................15
Connection Properties..........................................................................................................................16
Data Types............................................................................................................................................16
getTypeInfo................................................................................................................................17
Contacting Technical Support...............................................................................................................21
Chapter 2: Getting Started ..........................................................................23
DataSourceandDriverClasses...........................................................................................................23
Setting the Classpath ...........................................................................................................................24
Connecting with the JDBC Driver Manager..........................................................................................24
Registering the Driver with the Driver Manager.........................................................................25
Connection URL.........................................................................................................................25
TestingtheConnection...............................................................................................................26
Connecting Using Data Sources...........................................................................................................29
HowDataSourcesAreImplemented.........................................................................................30
Creating Data Sources...............................................................................................................30
CallingaDataSourceinanApplication.....................................................................................31
TestingaDataSourceConnection..............................................................................................31
Chapter 3: Using the Driver.........................................................................35
RequiredPermissionsforJavaPlatform...............................................................................................36
Permissions for Establishing Connections.................................................................................36
GrantingAccesstoJavaProperties...........................................................................................37
Granting Access to Temporary Files..........................................................................................37
Permissions for Kerberos Authentication...................................................................................37
Connecting from an Application............................................................................................................38
DataSourceandDriverClasses................................................................................................38
Setting the Classpath ................................................................................................................39
Connecting with the JDBC Driver Manager...............................................................................39
Connecting Using Data Sources................................................................................................43
HTTP Mode................................................................................................................................48
Progress®DataDirect®forJDBC™forApacheSparkSQL™:User'sGuide:Version6.0.1 5
Contents
Using Connection Properties................................................................................................................49
Required Properties...................................................................................................................49
User ID/Password Authentication Properties.............................................................................50
Kerberos Authentication Properties...........................................................................................51
Data Encryption Properties........................................................................................................52
Data Type Handling Properties..................................................................................................54
Timeout Properties.....................................................................................................................55
ClientInformationProperties......................................................................................................56
Statement Pooling Properties....................................................................................................56
Additional Properties..................................................................................................................58
Performance Considerations................................................................................................................60
Using Data Encryption..........................................................................................................................61
Configuring SSL Encryption.......................................................................................................62
ConfiguringSSLServerAuthentication......................................................................................62
Configuring SSL Client Authentication.......................................................................................63
Using Authentication.............................................................................................................................64
UsingtheAuthenticationMethodProperty..................................................................................64
Configuring User ID/Password Authentication...........................................................................64
ConfiguringtheDriverforKerberosAuthentication....................................................................65
UsingClientInformation........................................................................................................................71
How Databases Store Client Information...................................................................................71
Returning Client Information......................................................................................................72
ReturningMetaDataAboutClientInformationLocations...........................................................73
IP Addresses.........................................................................................................................................73
ParameterMetadataSupport................................................................................................................74
Insert, Update, and Delete Statements......................................................................................74
Select Statements......................................................................................................................74
ResultSet Metadata Support.................................................................................................................75
Isolation Levels.....................................................................................................................................76
Unicode.................................................................................................................................................76
Error Handling.......................................................................................................................................76
Large Object Support............................................................................................................................77
Rowset Support....................................................................................................................................77
Timeouts...............................................................................................................................................77
Views.....................................................................................................................................................78
SQL Escape Sequences.......................................................................................................................78
Using Scrollable Cursors......................................................................................................................78
Spark SQL Compatibility with Apache Hive..........................................................................................78
Stored Procedures................................................................................................................................78
Connection Pool Manager....................................................................................................................78
HowConnectionPoolingWorks.................................................................................................79
Implementing DataDirect Connection Pooling...........................................................................81
Configuring the Connection Pool...............................................................................................83
Connecting Using a Connection Pool........................................................................................84
Closing the Connection Pool......................................................................................................86
6 Progress®DataDirect®forJDBC™forApacheSparkSQL™:User'sGuide:Version6.0.1
Contents
CheckingthePoolManagerVersion..........................................................................................86
Enabling Pool Manager Tracing.................................................................................................86
ConnectionPool Manager Interfaces.........................................................................................87
Statement Pool Monitor........................................................................................................................92
UsingDataDirect-SpecificMethodstoAccesstheStatementPoolMonitor..............................92
UsingJMXtoAccesstheStatementPoolMonitor.....................................................................95
ImportingStatementsintoaStatementPool..............................................................................96
Clearing All Statements in a Statement Pool.............................................................................97
Freezing and Unfreezing the Statement Pool............................................................................97
GeneratingaStatementPoolExportFile...................................................................................97
DataDirectStatementPoolMonitorInterfacesandClasses......................................................98
DataDirect Test...................................................................................................................................100
DataDirect Test Tutorial............................................................................................................100
Tracking JDBC Calls with DataDirect Spy..........................................................................................133
Enabling DataDirect Spy..........................................................................................................133
Chapter 4: Connection Property Descriptions........................................137
AccountingInfo....................................................................................................................................141
ApplicationName.................................................................................................................................141
ArrayFetchSize...................................................................................................................................142
AuthenticationMethod.........................................................................................................................143
BinaryDescribeType............................................................................................................................144
ClientHostName..................................................................................................................................144
ClientUser...........................................................................................................................................145
ConnectionRetryCount........................................................................................................................146
ConnectionRetryDelay........................................................................................................................147
ConvertNull.........................................................................................................................................147
CookieName.......................................................................................................................................148
CryptoProtocolVersion........................................................................................................................149
DatabaseName...................................................................................................................................150
EnableCookieAuthentication...............................................................................................................150
EncryptionMethod...............................................................................................................................151
HostNameInCertificate........................................................................................................................152
HTTPPath...........................................................................................................................................153
ImportStatementPool..........................................................................................................................154
InitializationString................................................................................................................................155
InsensitiveResultSetBufferSize...........................................................................................................156
JavaDoubleToString............................................................................................................................157
KeyPassword......................................................................................................................................157
KeyStore.............................................................................................................................................158
KeyStorePassword.............................................................................................................................159
LoginTimeout......................................................................................................................................160
MaxBinarySize....................................................................................................................................160
MaxPooledStatements........................................................................................................................161
Progress®DataDirect®forJDBC™forApacheSparkSQL™:User'sGuide:Version6.0.1 7
Contents
Password............................................................................................................................................162
PortNumber.........................................................................................................................................163
ProgramID...........................................................................................................................................163
RegisterStatementPoolMonitorMBean................................................................................................164
RemoveColumnQualifiers...................................................................................................................165
ServerName........................................................................................................................................165
ServicePrincipalName.........................................................................................................................166
SpyAttributes.......................................................................................................................................167
StringDescribeType.............................................................................................................................168
TransactionMode................................................................................................................................169
TransportMode....................................................................................................................................169
TrustStore...........................................................................................................................................170
TrustStorePassword............................................................................................................................171
UseCurrentSchema............................................................................................................................172
User.....................................................................................................................................................172
ValidateServerCertificate.....................................................................................................................173
Chapter 5: Troubleshooting......................................................................175
Troubleshooting your application........................................................................................................175
TurningOnandOffDataDirectSpyLogging............................................................................176
DataDirect Spy Log Example...................................................................................................176
Troubleshooting Connection Pooling..................................................................................................178
Enabling Tracing with the setTracing Method..........................................................................178
Pool Manager Trace File Example...........................................................................................179
Troubleshooting Statement Pooling....................................................................................................182
Generating an Export File with the exportStatement Method..................................................183
Statement Pool Export File Example.......................................................................................183
Configuring Logging............................................................................................................................183
UsingtheJVMforLogging.......................................................................................................184
Chapter 6: Supported SQL Functionality.................................................185
Data Definition Language...................................................................................................................186
SelectingDataWiththeDriver............................................................................................................186
Select List.................................................................................................................................186
FromClause........................................................................................................................................187
Group By Clause.................................................................................................................................187
Having Clause.....................................................................................................................................187
Order By Clause.................................................................................................................................188
For Update Clause..............................................................................................................................188
Set Operators......................................................................................................................................188
Subqueries..........................................................................................................................................188
SQL Expressions................................................................................................................................189
Constants.................................................................................................................................189
8 Progress®DataDirect®forJDBC™forApacheSparkSQL™:User'sGuide:Version6.0.1
Contents
Numeric Operators...................................................................................................................189
Character Operator..................................................................................................................189
Relational Operators................................................................................................................190
Logical Operators.....................................................................................................................190
Functions..................................................................................................................................191
Chapter 7: SQL Escape Sequences for JDBC.........................................193
Date, time, and timestampescape sequences...................................................................................194
Scalar Functions.................................................................................................................................194
Outer Join Escape Sequences...........................................................................................................195
LIKEescapecharactersequenceforwildcards..................................................................................196
Procedure Call Escape Sequences....................................................................................................196
Chapter 8: JDBC support..........................................................................197
JDBC and JVM Compatibility..............................................................................................................197
Supported Functionality......................................................................................................................197
Array.........................................................................................................................................198
Blob..........................................................................................................................................198
CallableStatement....................................................................................................................199
Clob..........................................................................................................................................211
Connection...............................................................................................................................212
ConnectionEventListener.........................................................................................................217
ConnectionPoolDataSource.....................................................................................................217
DatabaseMetaData..................................................................................................................218
DataSource..............................................................................................................................226
Driver........................................................................................................................................227
ParameterMetaData.................................................................................................................227
PooledConnection....................................................................................................................228
PreparedStatement..................................................................................................................229
Ref............................................................................................................................................234
ResultSet..................................................................................................................................234
ResultSetMetaData..................................................................................................................245
RowSet.....................................................................................................................................246
SavePoint.................................................................................................................................246
Statement.................................................................................................................................246
StatementEventListener...........................................................................................................250
Struct........................................................................................................................................250
XAConnection..........................................................................................................................251
XADataSource.........................................................................................................................251
XAResource.............................................................................................................................251
Chapter 9: JDBC Extensions.....................................................................253
Using JDBC Wrapper Methods to Access JDBC Extensions.............................................................254
Progress®DataDirect®forJDBC™forApacheSparkSQL™:User'sGuide:Version6.0.1 9
Contents
DatabaseMetaData interface..............................................................................................................254
DDBulkLoad Interface.........................................................................................................................255
ExtConnection Interface......................................................................................................................262
ExtDatabaseMetaData Interface.........................................................................................................267
ExtLogControl class............................................................................................................................267
Chapter 10: Designing JDBC Applications for Performance
Optimization............................................................................................269
UsingDatabaseMetadataMethods....................................................................................................270
MinimizingtheUseofDatabaseMetadataMethods................................................................270
Avoiding Search Patterns.........................................................................................................271
UsingaDummyQuerytoDetermineTableCharacteristics.....................................................271
Returning Data....................................................................................................................................272
Returning Long Data................................................................................................................272
ReducingtheSizeofReturnedData........................................................................................273
Choosing the Right Data Type.................................................................................................273
RetrievingResultSets..............................................................................................................273
SelectingJDBCObjectsandMethods...............................................................................................274
UsingParameterMarkersasArgumentstoStoredProcedures..............................................274
UsingtheStatementObjectInsteadofthePreparedStatementObject...................................274
Using Batches Instead of Prepared Statements......................................................................275
Choosing the Right Cursor.......................................................................................................276
Using get Methods Effectively..................................................................................................276
RetrievingAutoGeneratedKeys..............................................................................................277
Managing Connections and Updates..................................................................................................277
Managing Connections............................................................................................................278
Managing Commits in Transactions.........................................................................................278
Choosing the Right Transaction Model....................................................................................279
Using updateXXX Methods......................................................................................................279
Using getBestRowIdentifier......................................................................................................279
Glossary.......................................................................................................281
Index.............................................................................................................285
10 Progress®DataDirect®forJDBC™forApacheSparkSQL™:User'sGuide:Version6.0.1