progress® datadirect® for jdbc™ for google bigquery™ user ... · of google bigquery. it...

Progress® DataDirect® forJDBC™ for Google BigQuery™

User's Guide

Release 6.0.0

Copyright

© 2020 Progress Software Corporation and/or its subsidiaries or affiliates. All rightsreserved.These materials and all Progress® software products are copyrighted and all rights are reserved by ProgressSoftware Corporation. The information in these materials is subject to change without notice, and ProgressSoftware Corporation assumes no responsibility for any errors that may appear therein. The references inthese materials to specific platforms supported are subject to change.

Corticon, DataDirect (and design), DataDirect Cloud, DataDirect Connect, DataDirect Connect64, DataDirectXML Converters, DataDirect XQuery, DataRPM, Defrag This, Deliver More Than Expected, Icenium, Ipswitch,iMacros, Kendo UI, Kinvey, MessageWay, MOVEit, NativeChat, NativeScript, OpenEdge, Powered by Progress,Progress, Progress Software Developers Network, SequeLink, Sitefinity (and Design), Sitefinity, SpeedScript,Stylus Studio, TeamPulse, Telerik, Telerik (and Design), Test Studio, WebSpeed, WhatsConfigured,WhatsConnected, WhatsUp, and WS_FTP are registered trademarks of Progress Software Corporation or oneof its affiliates or subsidiaries in the U.S. and/or other countries. Analytics360, AppServer, BusinessEdge,DataDirect Autonomous REST Connector, DataDirect Spy, SupportLink, DevCraft, Fiddler, iMail, JustAssembly,JustDecompile, JustMock, NativeScript Sidekick, OpenAccess, ProDataSet, Progress Results, ProgressSoftware, ProVision, PSE Pro, SmartBrowser, SmartComponent, SmartDataBrowser, SmartDataObjects,SmartDataView, SmartDialog, SmartFolder, SmartFrame, SmartObjects, SmartPanel, SmartQuery, SmartViewer,SmartWindow, and WebClient are trademarks or service marks of Progress Software Corporation and/or itssubsidiaries or affiliates in the U.S. and other countries. Java is a registered trademark of Oracle and/or itsaffiliates. Any other marks contained herein may be trademarks of their respective owners.

Updated: 2020/03/04

3Progress® DataDirect® for JDBC™ for Google BigQuery™ Driver: User's Guide: Version 6.0.0

Progress® DataDirect® for JDBC™ for Google BigQuery™ Driver: User's Guide: Version 6.0.04

Copyright

Table of Contents

Welcome to the Progress DataDirect for JDBC for Google BigQuery

Driver...........................................................................................................9What's new in this release?..................................................................................................................10

Quick start............................................................................................................................................10

Requirements.......................................................................................................................................11

Version string information.....................................................................................................................11

Driver and DataSource classes............................................................................................................11

Connection URL...................................................................................................................................12

Connection properties..........................................................................................................................13

Data types............................................................................................................................................13

getTypeInfo()..............................................................................................................................14

DataDirect Tools...................................................................................................................................18

Troubleshooting....................................................................................................................................19

Contacting Technical Support...............................................................................................................19

Using the driver............................................................................................21Required permissions for Java SE with the standard Security Manager enabled................................22

Permissions for establishing connections..................................................................................22

Granting access to Java properties...........................................................................................23

Granting access to temporary files............................................................................................23

Connecting from an application............................................................................................................23

Setting the classpath ................................................................................................................24

Connecting using the JDBC Driver Manager.............................................................................24

Connecting using data sources.................................................................................................28

Connecting through a proxy server.......................................................................................................32

Performance considerations.................................................................................................................33

Authentication.......................................................................................................................................33

Configuring OAuth 2.0 authentication........................................................................................33

Configuring service account authentication...............................................................................34

Generating access tokens and refresh tokens...........................................................................35

Data encryption....................................................................................................................................38

Standard and legacy SQL support.......................................................................................................38

Internal and external tables support.....................................................................................................38

Using identifiers....................................................................................................................................38

Failover support....................................................................................................................................39

Timeouts...............................................................................................................................................39

SQL escape sequences.......................................................................................................................39

Supported scalar functions........................................................................................................40


Contents

Statement pooling.................................................................................................................................40

Connection pooling...............................................................................................................................41

Isolation levels......................................................................................................................................41

Scrollable cursors.................................................................................................................................41

Unicode support...................................................................................................................................42

Error handling.......................................................................................................................................42

Parameter metadata support................................................................................................................42

Insert and update statements....................................................................................................43

Select statements......................................................................................................................43

ResultSet metadata support.................................................................................................................44

Connection property descriptions.............................................................45AccessToken.........................................................................................................................................51

AllowLargeResults................................................................................................................................52

AuthenticationMethod...........................................................................................................................52

ClientID.................................................................................................................................................53

ClientSecret..........................................................................................................................................54

ConfigOptions.......................................................................................................................................54

SchemaSet (Configuration Option)............................................................................................55

VarcharLength (Configuration Option).......................................................................................56

ConnectionRetryCount.........................................................................................................................57

ConnectionRetryDelay..........................................................................................................................58

ConvertNull...........................................................................................................................................58

CreateMap............................................................................................................................................59

Dataset.................................................................................................................................................60

FetchSize..............................................................................................................................................61

ImportStatementPool............................................................................................................................62

JavaDoubleToString..............................................................................................................................62

JobTimeout...........................................................................................................................................63

JsonFormat...........................................................................................................................................64

KMSKeyName......................................................................................................................................65

LegacyDataset......................................................................................................................................66

LegacyTable..........................................................................................................................................67

Location................................................................................................................................................67

LogConfigFile.......................................................................................................................................68

LoginTimeout........................................................................................................................................69

MaximumBytesBilled............................................................................................................................69

MaxPooledStatements..........................................................................................................................70

Project..................................................................................................................................................71

ProxyHost.............................................................................................................................................72

ProxyPassword.....................................................................................................................................72

ProxyPort..............................................................................................................................................73

ProxyUser.............................................................................................................................................74

RefreshSchema....................................................................................................................................74


Contents

RefreshSchemaForDDL.......................................................................................................................75

RefreshToken........................................................................................................................................76

RegisterStatementPoolMonitorMBean.................................................................................................77

SchemaMap.........................................................................................................................................77

Scope...................................................................................................................................................79

ServiceAccountEmail............................................................................................................................80

ServiceAccountPrivateKey...................................................................................................................81

SpyAttributes........................................................................................................................................81

Syntax...................................................................................................................................................82

UseQueryCache...................................................................................................................................83

WSFetchSize........................................................................................................................................83

WSPoolSize..........................................................................................................................................84

WSRetryCount.....................................................................................................................................85

WSTimeout...........................................................................................................................................86


Contents


Contents

1Welcome to the Progress DataDirect forJDBC for Google BigQuery Driver

The Progress® DataDirect® for JDBC™ for Google BigQuery™ driver supports both standard and legacy SQL dialectsof Google BigQuery. It allows create, read, update, and delete operations in internal tables (stored insideGoogle BigQuery) and read operations in external tables (stored in data sources outside Google BigQuery).It leverages the SQL engine functionality of Google BigQuery to execute SQL queries.

The documentation set also includes the Progress DataDirect for JDBC Drivers Reference. The referenceprovides general reference information for all DataDirect drivers for JDBC, including content on troubleshooting,supported SQL escapes, and DataDirect tools. For the complete documentation set, refer to DataDirectDocumentation.

For details, see the following topics:

• What's new in this release?

• Quick start

• Requirements

• Version string information

• Driver and DataSource classes

• Connection URL

• Connection properties

• Data types

• DataDirect Tools


https://documentation.progress.com/output/DataDirect/jdbcreferencehelp/index.html

https://www.progress.com/documentation/datadirect-connectors

https://www.progress.com/documentation/datadirect-connectors

• Troubleshooting

• Contacting Technical Support

What's new in this release?

Support and certificationVisit the following web pages for the latest support and certification information.

• Release Notes

• Supported Configurations

• DataDirect Support Matrices

Changes Since 6.0.0

• Changed Behavior

• The default value of the SchemaSet configuration option has been changed to the project and datasetspecified at connection. For details, see SchemaSet (Configuration Option) on page 55

Highlights of 6.0.0 Release

• The driver supports both standard and legacy Google BigQuery SQL dialects. See Standard and legacySQL support on page 38 for more information.

• The driver supports JDBC core functions. For details, refer to JDBC support in the Progress DataDirect forJDBC Drivers Reference.

• The driver serves as a complete pass-through driver. It leverages Google BigQuery SQL engine to executequeries.

• The driver supports create, read, update, and delete (CRUD) operations.

• The driver returns data for complex data types, such as Array and Struct, as JSON strings, which are easyto comprehend for the JDBC applications. See JsonFormat on page 64 for more information.

• The driver provides proxy support. See ProxyHost on page 72, ProxyPassword on page 72, ProxyPort onpage 73, and ProxyUser on page 74 for more information.

• The driver supports the handling of large result sets with configurable paging and the FetchSize on page61 and WSFetchSize on page 83 connection properties.

Quick startThe documentation library includes a quick start guide that provides the basic information necessary to install,configure, and connect to Google BigQuery. The quick start guide for Google BigQuery is available at thefollowing link: Progress DataDirect for JDBC for Google BigQuery Driver Quick Start.


Chapter 1: Welcome to the Progress DataDirect for JDBC for Google BigQuery Driver

https://www.progress.com/datadirect-connectors/whats-new#jdbc

https://www.progress.com/supported-configurations/datadirect

https://www.progress.com/matrices/datadirect

https://documentation.progress.com/output/DataDirect/jdbcreferencehelp/index.html#context/JDBC/jdbc_support

https://documentation.progress.com/output/DataDirect/jdbcquickstarts/bigqueryjdbc_quickstart/index.html

RequirementsThe driver is compatible with JDBC 2.0, 3.0, 4.0, 4.1, and 4.2. The driver requires one of the following JavaVirtual Machines (JVM):

• Oracle JDK 8 or higher (LTS version)

• OpenJDK 8 or higher (LTS version)

• IBM SDK (Java) 7 or higher (LTS version)

Version string informationThe DatabaseMetaData.getDriverVersion() method returns a Driver Version string in the format:

M.m.s.bbbbbb(CXXXX.FYYYYYY.UZZZZZZ)

where:

M is the major version number.

m is the minor version number.

s is the service pack number.

bbbbbb is the driver build number.

XXXX is the cloud adapter build number.

YYYYYY is the framework build number.

ZZZZZZ is the utl build number.

For example:

6.0.0.000002(C0003.F000001.U000002) |____| |___| |_____| |_____| Driver Cloud Frame Utl

Driver and DataSource classesThe Driver class for the driver is:

com.ddtek.jdbc.googlebigquery.GoogleBigQueryDriver

The DataSource class for the driver is:

com.ddtek.jdbcx.googlebigquery.GoogleBigQueryDataSource


Requirements

Connection URLAfter setting the CLASSPATH, the required connection information needs to be passed in the form of aconnection URL.

jdbc:datadirect:googlebigquery:Project=project;Dataset=dataset;AccessToken=accesstoken;RefreshToken=refreshtoken;ClientID=clientid;ClientSecret=clientsecret[;property=value[;...]]

where:

Project

specifies the name of the project that you want the driver to connect to. The projects in GoogleBigQuery are equivalent to catalogs in JDBC.

Dataset

specifies the name of the dataset that you want the driver to connect to. The datasets in GoogleBigQuery are equivalent to schemas in JDBC.

AccessToken

specifies the access token required to authenticate to a Google BigQuery instance.

RefreshToken

specifies the refresh token used to either request a new access token or renew an expired accesstoken.

ClientID

specifies the consumer key for your application.

ClientSecret

specifies the consumer secret for your application.

The following example shows how to establish a connection to a Google BigQuery instance.

Connection conn = DriverManager.getConnection("jdbc:datadirect:googlebigquery:Project=myproject;Dataset=mydataset;AccessToken=abcdefghi12345678;RefreshToken=wxyz123456789;ClientID=123abc.apps.googleusercontent.com;ClientSecret=ab123xy");

See alsoConnection property descriptions on page 45



Connection propertiesThe driver includes over 40 connection properties.You can use connection properties to customize the driverfor your environment. Connection properties can be used to accomplish different tasks, such as implementingdriver functionality and optimizing performance.You can specify connection properties in a connection URLor within a JDBC data source object.


Data typesThe following table lists the data types supported by the driver and the JDBC data types they are mapped to.

Table 1: Google BigQuery Data Types

JDBC Data TypeGoogle BigQuery Data Type

VARCHARARRAY

BOOLEANBOOL

VARBINARYBYTES

DATEDATE

TIMESTAMPDATETIME

DOUBLEFLOAT64

VARCHARGEOGRAPHY

BIGINTINT64

DECIMALNUMERIC

VARCHARRECORD

VARCHARSTRING

TIMETIME

TIMESTAMPTIMESTAMP


Connection properties

getTypeInfo()

The DatabaseMetaData.getTypeInfo() method returns information about data types.The following table providesgetTypeInfo() results for supported Google BigQuery data types.

Table 2: getTypeInfo() Results

MINIMUM_SCALE = NULL

NULLABLE = 1

NUM_PREC_RADIX = NULL

PRECISION = 255

SEARCHABLE = 3

SQL_DATA_TYPE = NULL

SQL_DATETIME_SUB = NULL

UNSIGNED_ATTRIBUTE = NULL

TYPE_NAME = ARRAY

AUTO_INCREMENT = false

CASE_SENSITIVE = true

CREATE_PARAMS = NULL

DATA_TYPE = 12 (VARCHAR)

FIXED_PREC_SCALE = false

LITERAL_PREFIX = '

LITERAL_SUFFIX = '

LOCAL_TYPE_NAME = ARRAY

MAXIMUM_SCALE = NULL


NULLABLE = 1


PRECISION = 1

SEARCHABLE = 3




TYPE_NAME = BOOL


CASE_SENSITIVE = false


DATA_TYPE = 16 (BOOLEAN)


LITERAL_PREFIX = NULL

LITERAL_SUFFIX = NULL

LOCAL_TYPE_NAME = BOOL





NULLABLE = 1


PRECISION = 65535

SEARCHABLE = 3




TYPE_NAME = BYTES




DATA_TYPE = -3 (VARBINARY)




LOCAL_TYPE_NAME = BYTES


MINIMUM_SCALE = 0

NULLABLE = 1


PRECISION = 10

SEARCHABLE = 3




TYPE_NAME = DATE




DATA_TYPE = 91 (DATE)


LITERAL_PREFIX = DATE'

LITERAL_SUFFIX = '

LOCAL_TYPE_NAME = DATE


MINIMUM_SCALE = 0

NULLABLE = 1


PRECISION = 26

SEARCHABLE = 3




TYPE_NAME = DATETIME




DATA_TYPE = 93 (TIMESTAMP)


LITERAL_PREFIX = DATETIME'

LITERAL_SUFFIX = '

LOCAL_TYPE_NAME = DATETIME



Data types

MINIMUM_SCALE = -324

NULLABLE = 1

NUM_PREC_RADIX = 2

PRECISION = 15

SEARCHABLE = 3



UNSIGNED_ATTRIBUTE = false

TYPE_NAME = FLOAT64




DATA_TYPE = 8 (DOUBLE)




LOCAL_TYPE_NAME = FLOAT64



NULLABLE = 1


PRECISION = 65535

SEARCHABLE = 3




TYPE_NAME = GEOGRAPHY






LITERAL_PREFIX = '

LITERAL_SUFFIX = '

LOCAL_TYPE_NAME = GEOGRAPHY


MINIMUM_SCALE = 0

NULLABLE = 1

NUM_PREC_RADIX = 10

PRECISION = 19

SEARCHABLE = 3




TYPE_NAME = INT64




DATA_TYPE = -5 (BIGINT)




LOCAL_TYPE_NAME = INT64




MINIMUM_SCALE = 0

NULLABLE = 1

NUM_PREC_RADIX = 10

PRECISION = 38

SEARCHABLE = 3




TYPE_NAME = NUMERIC




DATA_TYPE = 3 (DECIMAL)




LOCAL_TYPE_NAME = NUMERIC

MAXIMUM_SCALE = 32767


NULLABLE = 1


PRECISION = 65535

SEARCHABLE = 3




TYPE_NAME = RECORD






LITERAL_PREFIX = '

LITERAL_SUFFIX = '

LOCAL_TYPE_NAME = RECORD



NULLABLE = 1


PRECISION = 65535

SEARCHABLE = 3




TYPE_NAME = STRING






LITERAL_PREFIX = '

LITERAL_SUFFIX = '

LOCAL_TYPE_NAME = STRING



Data types

MINIMUM_SCALE = 0

NULLABLE = 1


PRECISION = 15

SEARCHABLE = 3




TYPE_NAME = TIME




DATA_TYPE = 92 (TIME)


LITERAL_PREFIX = TIME'

LITERAL_SUFFIX = '

LOCAL_TYPE_NAME = TIME


MINIMUM_SCALE = 0

NULLABLE = 1


PRECISION = 26

SEARCHABLE = 3




TYPE_NAME = TIMESTAMP




DATA_TYPE = 93 (TIMESTAMP)


LITERAL_PREFIX = DATETIME'

LITERAL_SUFFIX = '

LOCAL_TYPE_NAME = TIMESTAMP


DataDirect ToolsProgress DataDirect for JDBC drivers install the set of tools described in this section. For detailed instructionson using these tools, refer to the corresponding topics in the Progress DataDirect for JDBC Drivers Referenceat the following links:

• DataDirect Test allows you to test your JDBC driver and learn the JDBC API.

• DataDirect Connection Pool Manager allows you to pool connections when accessing databases. Whenyour applications use connection pooling, connections are reused rather than created each time a connectionis requested. Because establishing a connection is among the most costly operations an application mayperform, using Connection Pool Manager to implement connection pooling can significantly improveperformance.

• Statement Pool Monitor loads statements into and remove statements from the statement pool as well asgenerate information to help you troubleshoot statement pooling performance.

• DataDirect Spy logs detailed information about calls your driver makes that can be used for troubleshooting.



https://documentation.progress.com/output/DataDirect/jdbcreferencehelp/index.html#context/JDBC/datadirect_test

https://documentation.progress.com/output/DataDirect/jdbcreferencehelp/index.html#context/JDBC/connection_pool

https://documentation.progress.com/output/DataDirect/jdbcreferencehelp/index.html#context/JDBC/statement_pool

https://documentation.progress.com/output/DataDirect/jdbcreferencehelp/index.html#context/JDBC/datadirect_spy

TroubleshootingThe Progress DataDirect for JDBC Drivers Reference provides information on troubleshooting problems shouldthey occur. Refer to Troubleshooting for details.

Contacting Technical SupportProgress DataDirect offers a variety of options to meet your support needs. Please visit our Web site for moredetails and for contact information:

https://www.progress.com/support

The Progress DataDirect Web site provides the latest support information through our global service network.The SupportLink program provides access to support contact details, tools, patches, and valuable information,including a list of FAQs for each product. In addition, you can search our Knowledgebase for technical bulletinsand other information.

When you contact us for assistance, please provide the following information:

• Your number or the serial number that corresponds to the product for which you are seeking support, or acase number if you have been provided one for your issue. If you do not have a SupportLink contract, theSupportLink representative assisting you will connect you with our Sales team.

• Your name, phone number, email address, and organization. For a first-time call, you may be asked for fullinformation, including location.

• The Progress DataDirect product and the version that you are using.

• The type and version of the operating system where you have installed your product.

• Any database, database version, third-party software, or other environment information required to understandthe problem.

• A brief description of the problem, including, but not limited to, any error messages you have received, whatsteps you followed prior to the initial occurrence of the problem, any trace logs capturing the issue, and soon. Depending on the complexity of the problem, you may be asked to submit an example or reproducibleapplication so that the issue can be re-created.

• A description of what you have attempted to resolve the issue. If you have researched your issue on Websearch engines, our Knowledgebase, or have tested additional configurations, applications, or other vendorproducts, you will want to carefully note everything you have already attempted.

• A simple assessment of how the severity of the issue is impacting your organization.


Troubleshooting

https://documentation.progress.com/output/DataDirect/jdbcreferencehelp/index.html#context/JDBC/troubleshooting

https://www.progress.com/support

2Using the driver

This section provides information on how to connect to your data store using either the JDBC Driver Manageror DataDirect JDBC data sources, as well as information on how to implement and use functionality supportedby the driver.


• Required permissions for Java SE with the standard Security Manager enabled

• Connecting from an application

• Connecting through a proxy server

• Performance considerations

• Authentication

• Data encryption

• Standard and legacy SQL support

• Internal and external tables support

• Using identifiers

• Failover support

• Timeouts

• SQL escape sequences

• Statement pooling

• Connection pooling


• Isolation levels

• Scrollable cursors

• Unicode support

• Error handling

• Parameter metadata support

• ResultSet metadata support

Required permissions for Java SE with the standardSecurity Manager enabled

Using the driver on a Java platform with the standard Security Manager enabled requires certain permissionsto be set in the Java SE security policy file java.policy. The default location of this file isjava_install_dir/jre/lib/security.

Note: Security manager may be enabled by default in certain scenarios, such as running on an applicationserver or in a Web browser applet.

To run an application on a Java platform with the standard Security Manager, use the following command:

"java -Djava.security.manager application_class_name"

where application_class_name is the class name of the application.

Refer to your Java documentation for more information about setting permissions in the security policy file.

Permissions for establishing connections

To establish a connection to the database server, the driver must be granted the permissions as shown in thefollowing example:

grant codeBase "file:/install_dir/lib/-" { permission java.net.SocketPermission "*", "connect";};

where:

install_dir

is the product installation directory.


Chapter 2: Using the driver

Granting access to Java properties

To allow the driver to read the value of various Java properties to perform certain operations, permissions mustbe granted as shown in the following example:

grant codeBase "file:/install_dir/lib/-" { permission java.util.PropertyPermission "*", "read, write";};

where:

install_dir


Granting access to temporary files

Access to the temporary directory specified by the JVM configuration must be granted in the Java SE securitypolicy file to use insensitive scrollable cursors or to perform client-side sorting of DatabaseMetaData resultsets. The following example shows permissions that have been granted for the C:\TEMP directory:

grant codeBase "file:/install_dir/lib/-" {// Permission to create and delete temporary files.// Adjust the temporary directory for your environment. permission java.io.FilePermission "C:\\TEMP\\-", "read,write,delete";};

where:

install_dir


Connecting from an applicationAfter the driver has been installed and defined on your class path, you can connect from your application toyour data in either of the following ways.

• Using the JDBC DriverManager by specifying the connection URL in theDriverManager.getConnection() method.

• Creating a JDBC data source that can be accessed through the Java Naming Directory Interface (JNDI).


Connecting from an application

Setting the classpath

Before you can connect, the driver must be defined in your CLASSPATH variable. The CLASSPATH is thesearch string your Java Virtual Machine (JVM) uses to locate JDBC drivers on your computer. If the driver isnot defined on your CLASSPATH, you will receive a class not found exception when trying to load thedriver. Set your system CLASSPATH to include the googlebigquery.jar file as shown, where install_diris the path to your product installation directory:

install_dir/lib/googlebigquery.jar

Windows Example

CLASSPATH=.;C:\Program Files\Progress\DataDirect\JDBC_60\lib\googlebigquery.jar

UNIX Example

CLASSPATH=.:/opt/Progress/DataDirect/JDBC_60/lib/googlebigquery.jar

Connecting using the JDBC Driver Manager

One way to connect to a Google BigQuery instance is through the JDBC Driver Manager using theDriverManager.getConnection() method. This method specifies a string containing a connection URL.This example shows how to establish a connection to a data source:


Passing the connection URLAfter setting the CLASSPATH, the required connection information needs to be passed in the form of aconnection URL. The connection URL takes the form:

Connection URL Syntax

jdbc:datadirect:googlebigquery:Project=project;Dataset=dataset;AccessToken=accesstoken;RefreshToken=refreshtoken;ClientID=clientid;ClientSecret=clientsecret[;property=value[;...]]

where:

Project

specifies the name of the project that you want the driver to connect to. The projects in GoogleBigQuery are equivalent to catalogs in JDBC.

Dataset

specifies the name of the dataset that you want the driver to connect to. The datasets in GoogleBigQuery are equivalent to schemas in JDBC.



AccessToken

specifies the access token required to authenticate to a Google BigQuery instance.

RefreshToken

specifies the refresh token used to either request a new access token or renew an expired accesstoken.

ClientID

specifies the consumer key for your application.

ClientSecret

specifies the consumer secret for your application.

Connection URL Example



Testing the connectionYou can use DataDirect Test™ to verify your connection. The screen shots in this section were taken on aWindows system.

To test the connection from the driver to your data source, follow these steps:

1. Navigate to the installation directory. The default location is:

• Windows systems: Program Files\Progress\DataDirect\JDBC_60\testforjdbc

• UNIX and Linux systems: /opt/Progress/DataDirect/JDBC_60/testforjdbc

Note: For UNIX/Linux, if you do not have access to /opt, your home directory will be used in its place.

2. From the testforjdbc folder, run the platform-specific tool:

• testforjdbc.bat (on Windows systems)

• testforjdbc.sh (on UNIX and Linux systems)

The Test for JDBC Tool window appears:



3. Click Press Here to Continue.

The main dialog appears:

4. From the menu bar, select Connection > Connect to DB.

The Select A Database dialog appears:



5. Select the appropriate database template from the Defined Databases field.

6. In the Database field, specify all required connection properties.

For example:

jdbc:datadirect:googlebigquery:Project=myproject;Dataset=mydataset;AccessToken=abcdefghi12345678;RefreshToken=wxyz123456789;ClientID=123abc.apps.googleusercontent.com;ClientSecret=ab123xy

7. Click Connect.

If the connection information is entered correctly, the JDBC/Database Output window reports that a connectionhas been established. (If a connection is not established, the window reports an error.)

For more information on using DataDirect test, refer to DataDirect Test in the Progress DataDirect for JDBCDrivers Reference.



https://documentation.progress.com/output/DataDirect/jdbcreferencehelp/index.html#context/JDBC/datadirect_test

Connecting using data sources

A JDBC data source is a Java object, specifically a DataSource object, that defines connection informationrequired for a JDBC driver to connect to the database. Each JDBC driver vendor provides their own data sourceimplementation for this purpose. A Progress DataDirect data source is Progress DataDirect’s implementationof a DataSource object that provides the connection information needed for the driver to connect to a database.

Because data sources work with the Java Naming Directory Interface (JNDI) naming service, data sourcescan be created and managed separately from the applications that use them. Because the connection informationis defined outside of the application, the effort to reconfigure your infrastructure when a change is made isminimized. For example, if the database is moved to another database server, the administrator need onlychange the relevant properties of the DataSource object. The applications using the database do not needto change because they only refer to the name of the data source.

How data sources are implementedData sources are implemented through a data source class. A data source class implements the followinginterfaces.

• javax.sql.DataSource

• javax.sql.ConnectionPoolDataSource (allows applications to use connection pooling)

For more information, refer to Connection Pool Manager in the Progress DataDirect for JDBC DriversReference.

See alsoDriver and DataSource classes on page 11

Creating data sourcesThe following example files provide details on creating and using Progress DataDirect data sources with theJava Naming Directory Interface (JNDI), where install_dir is the product installation directory.

• install_dir/Examples/JNDI/JNDI_LDAP_Example.java can be used to create a JDBC data sourceand save it in your LDAP directory using the JNDI Provider for LDAP.

• install_dir/Examples/JNDI/JNDI_FILESYSTEM_Example.java can be used to create a JDBCdata source and save it in your local file system using the File System JNDI Provider.

See "Example data source" for an example data source definition for the example files.

To connect using a JNDI data source, the driver needs to access a JNDI data store to persist the data sourceinformation. For a JNDI file system implementation, you must download the File System Service Provider fromthe Oracle Technology Network Java SE Support downloads page, unzip the files to an appropriate location,and add the fscontext.jar and providerutil.jar files to your CLASSPATH. These steps are notrequired for LDAP implementations because the LDAP Service Provider has been included with Java SE sinceJava 2 SDK, v1.3.

Example data source

To configure a data source using the example files, you will need to create a data source definition.The contentrequired to create a data source definition is divided into three sections.



https://documentation.progress.com/output/DataDirect/jdbcreferencehelp/index.html#context/JDBC/connection_pool

http://www.oracle.com/technetwork/java/javasebusiness/downloads/java-archive-downloads-java-plat-419418.html#7110-jndi-1.2.1-oth-JPR

First, you will need the data source class for the driver:

import com.ddtek.jdbcx.googlebigquery.GoogleBigQueryDataSource;

Next, you will need to set the values and define the data source. For example, the following definition containsthe minimum properties required to establish connection:

GoogleBigQueryDataSource mds = new GoogleBigQueryDataSource();mds.setDescription("My Google BigQuery Datasource");mds.setProject("myproject");mds.setDataset("mydataset");mds.setAccessToken("abcdefghi12345678");mds.setRefreshToken("wxyz123456789");mds.setClientID("123abc.apps.googleusercontent.com");mds.setClientSecret("ab123xy");

Finally, you will need to configure the example application to print out the data source attributes. Note that thiscode is specific to the driver and should only be used in the example application. For example, you would addthe following section for the minimum properties required to establish a connection:

if (ds instanceof GoogleBigQueryDataSource){GoogleBigQueryDataSource jmds = (GoogleBigQueryDataSource) ds;System.out.println("description=" + jmds.getDescription());System.out.println("project=" + jmds.getProject());System.out.println("dataset=" + jmds.getDataset());System.out.println("accessToken=" + jmds.getAccessToken());System.out.println("refreshToken=" + jmds.getRefreshToken());System.out.println("clientID=" + jmds.getClientID());System.out.println("clientSecret=" + jmds.getClientSecret());System.out.println();}

Calling a data source in an applicationApplications can call a Progress DataDirect data source using a logical name to retrieve thejavax.sql.DataSource object. This object loads the specified driver and can be used to establish aconnection to the database.

Once the data source has been registered with JNDI, it can be used by your JDBC application as shown in thefollowing code example.

Context ctx = new InitialContext();DataSource ds = (DataSource)ctx.lookup("EmployeeDB");Connection con = ds.getConnection();

In this example, the JNDI environment is first initialized. Next, the initial naming context is used to find thelogical name of the data source (EmployeeDB). The Context.lookup() method returns a reference to aJava object, which is narrowed to a javax.sql.DataSource object. Then, theDataSource.getConnection() method is called to establish a connection.

Testing a data source connectionYou can use DataDirect Test™ to establish and test a data source connection. The screen shots in this sectionwere taken on a Windows system.

Take the following steps to establish a connection.

1. Navigate to the installation directory. The default location is:

• Windows systems: Program Files\Progress\DataDirect\JDBC_60\testforjdbc



• UNIX and Linux systems: /opt/Progress/DataDirect/JDBC_60/testforjdbc

Note: For UNIX/Linux, if you do not have access to /opt, your home directory will be used in its place.

2. From the testforjdbc folder, run the platform-specific tool:

• testforjdbc.bat (on Windows systems)

• testforjdbc.sh (on UNIX and Linux systems)

The Test for JDBC Tool window appears:

3. Click Press Here to Continue.

The main dialog appears:



4. From the menu bar, select Connection > Connect to DB via Data Source.

The Select A Database dialog appears:

5. Select a datasource template from the Defined Datasources field.

6. Provide the following information:

a) In the Initial Context Factory, specify the location of the initial context provider for your application.

b) In the Context Provider URL, specify the location of the context provider for your application.

c) In the Datasource field, specify the name of your datasource.

7. Click Connect.

If the connection information is entered correctly, the JDBC/Database Output window reports that a connectionhas been established. If a connection is not established, the window reports an error.



Connecting through a proxy serverIn some environments, your application may need to connect through a proxy server, for example, if yourapplication accesses an external resource such as a Web service. At a minimum, your application needs toprovide the following connection information when you invoke the JVM if the application connects through aproxy server:

• Server name or IP address of the proxy server

• Port number on which the proxy server is listening for HTTP/HTTPS requests

In addition, if authentication is required, your application may need to provide a valid user ID and password forthe proxy server. Consult with your system administrator for the required information.

For example, the following command invokes the JVM while specifying a proxy server named pserver, a portof 808, and provides a user ID and password for authentication:

java -Dhttp.proxyHost=pserver -Dhttp.proxyPort=808 -Dhttp.proxyUser=smith -Dhttp.proxyPassword=secret -cp googlebigquery.jar com.acme.myapp.Main

Alternatively, you can use the ProxyHost, ProxyPort, ProxyUser, and ProxyPassword connection properties.See "Connection Property Descriptions" for details about these properties.




Performance considerationsFetchSize/WSFetchSize: The connection options FetchSize and WSFetchSize can be used to adjust thetrade-off between throughput and response time. In general, setting larger values for WSFetchSize andFetchSize will improve throughput, but can reduce response time.

WSPoolSize: WSPoolSize determines the maximum number of sessions the driver uses when there aremultiple active connections to Google BigQuery. By increasing this number, you increase the number of sessionsthe driver uses to distribute calls to Google BigQuery, thereby improving throughput and performance. Forexample, if WSPoolSize is set to 1, and you have two open connections, the session must complete a call fromone connection before it can begin processing a call from the other connection. However, if WSPoolSize isequal to 2, a second session is opened that allows calls from both connections to be processed simultaneously.

Note: The number specified for WSPoolSize should not exceed the amount of sessions permitted by yourGoogle BigQuery account.

AuthenticationAuthentication ensures that only the authorized users are allowed to connect to a Google BigQuery instance.

The Google BigQuery driver supports the following methods of authentication:

• OAuth 2.0 authentication. It allows the users to authenticate to a Google BigQuery instance without specifyinguser ID and password.

• Service account authentication. It allows the users to authenticate to a Google BigQuery instance using aservice account, which is a type of Google account that represents an application instead of an individualend user.

See alsoConfiguring OAuth 2.0 authentication on page 33Configuring service account authentication on page 34Connection property descriptions on page 45

Configuring OAuth 2.0 authentication

The driver supports OAuth 2.0, which is an open protocol for token-based authentication. OAuth 2.0 allowsyou to authenticate without specifying a user ID or password, eliminating the risk of exposing them tounauthorized access. It uses an access token, accompanied by the values discussed below, to authenticateto a Google BigQuery instance. See "Generating access token and refresh token" to know how to obtain anaccess token.

To configure the driver to use OAuth 2.0 authentication, set the following connection properties:

• Set the AuthenticationMethod property to oauth2.

• Set at least one of the following properties:

• AccessToken: Set this to specify the access token you have obtained to authenticate to Google BigQuery.

• RefreshToken: Set this to specify the refresh token you have obtained to authenticate to Google BigQuery.


Performance considerations

If a value for the AccessToken property is not specified, the driver uses the value of the RefreshTokenproperty to make a connection. If both values are not specified, the driver cannot make a successfulconnection. If both are specified, the driver uses the AccessToken value; however, if the AccessTokenvalue expires, it uses the RefreshToken value to generate a new AccessToken value.

• Set the ClientID property to specify the consumer key for your application.

• Set the ClientSecret property to specify the consumer secret for your application.

• Optionally, set the Scope property to specify the OAuth scope. It limits the permissions granted by an accesstoken.

The following example shows how to connect to a Google BigQuery instance using OAuth 2.0authentication.

Connection conn = DriverManager.getConnection("jdbc:datadirect:googlebigquery:AuthenticationMethod=oauth2;Project=myproject;Dataset=mydataset;AccessToken=abcdefghi12345678;RefreshToken=wxyz123456789;ClientID=123abc.apps.googleusercontent.com;ClientSecret=ab123xy","","");

See alsoAccessToken on page 51RefreshToken on page 76ClientID on page 53ClientSecret on page 54Scope on page 79

Configuring service account authentication

The driver supports service account authentication. A service account is a type of Google account that representsan application instead of an individual end user. Unlike a user account, a service account allows your applicationto authenticate and communicate to Google APIs without direct human intervention.This is useful for applicationsthat need to access their own data, not the user's data. For a successful service account authentication, youneed:

• Private key file: A .json or .p12 file that contains the key required to authenticate API calls.You candownload it from the Google Cloud Platform (GCP) Console.

• Service account's email address: A unique email address that is provisioned while creating a serviceaccount.

To know more about service account authentication, refer to the Google documentation.

To configure the driver to use service account authentication, set the following connection properties:

• Set the AuthenticationMethod property to serviceaccount.

• Set the ServiceAccountEmail property to specify your service account's email address.

• Set the ServiceAccountPrivateKey property to specify the full path to the .json or .p12 private key file.

Note: If you are using a .p12 private key file, you must set the skipDDPKIProvider system property to true.



The following example shows how to connect to a Google BigQuery instance using service accountauthentication.

Connection conn = DriverManager.getConnection("jdbc:datadirect:googlebigquery:AuthenticationMethod=serviceaccount;Project=myproject;Dataset=mydataset;[email protected];ServiceAccountPrivateKey=abc123.json;);

See alsoAuthenticationMethod on page 52ServiceAccountEmail on page 80ServiceAccountPrivateKey on page 81Connection property descriptions on page 45

Generating access tokens and refresh tokens

This section guides you through the process of generating access tokens and refresh tokens that are used toauthenticate to Google BigQuery when OAuth 2.0 is set as the authentication method(AuthenticationMethod=oauth2).

To generate an access token and a refresh token from Google Console:

1. Click the following link to navigate to Google Developer Console: https://console.developers.google.com/.

2. Enter your login credentials; then, click Next.

3. On the left pane, click OAuth consent screen.

4. On the OAuth consent screen, enter the following details; then, click Save:

• Application name

• Support email

• Scopes for Google APIs

• Authorized domains

• Application Homepage link

• Application Privacy Policy link

5. On the left pane, click Credentials. Next, from the Create credentials drop-down list, select OAuth clientID.


Authentication

https://console.developers.google.com/

6. The Create OAuth client ID screen appears. Select Other as your Application type, enter your application'sname, and then click Create.

.

7. OAuth client window appears. Copy and save your client ID and client secret from the corresponding fields.You will need them later in this procedure. Note that they can also be accessed from the Credentials screen,if necessary.

8. Click the following link to navigate to OAuth 2.0 Playground: https://developers.google.com/oauthplayground/.

9. Select the required scopes; then, click Authorize APIs.



https://developers.google.com/oauthplayground/

Login screen appears.

10. Click your username. The following message appears: "Google OAuth 2.0 Playground wants to accessyour Google Account."

11. Scroll down and click Allow.

You are redirected to the OAuth 2.0 Playground. It contains the newly generated authorization code foryour application.

12. Click Exchange authorization code for tokens.

The refresh token and access token are generated in the corresponding fields.

See alsoConfiguring OAuth 2.0 authentication on page 33AccessToken on page 51RefreshToken on page 76


Authentication

Data encryptionAll communication between the driver and Google BigQuery, including OAuth 2.0 authentication, is encryptedusing Secure Sockets Layer (SSL).

Standard and legacy SQL supportThe driver supports both standard and legacy SQL dialects of Google BigQuery. By default, the driver usesstandard SQL to execute queries. However, you can change the dialect to legacy SQL either by adding theprefix #legacySQL to a query or by setting the Syntax connection property to legacy (Syntax=legacy).

The following example demonstrates how to use the prefix #legacySQL in a query:

#legacySQLSELECT ID,name FROM [bigquery-public-data:samples.EMP]WHERE name CONTAINS "RA";

See alsoSyntax on page 82

Internal and external tables supportThe driver supports create, read, update, and delete operations in internal tables, which are stored insideGoogle BigQuery, and read operations in external tables, which are stored in data sources outside GoogleBigQuery.

Note that Google BigQuery has some rules on how Data Definition Language (DDL) and Data ManipulationLanguage (DML) statements can be used. Refer to the Google BigQuery documentation to learn more aboutthese rules.

Using identifiersIdentifiers are used to refer to objects exposed by the driver, such as tables, columns, or caches. The driversupports both unquoted and quoted identifiers for naming objects. An unquoted identifier must start with anASCII alpha character and can be followed by zero or more ASCII alpha or numeric characters.

Quoted identifiers must be enclosed in double quotation marks (""). A quoted identifier can contain any Unicodecharacter, including the space character, and is case-sensitive. The Google BigQuery driver recognizes theUnicode escape sequence \uxxxx as a Unicode character.

The maximum length of both quoted and unquoted identifiers is 1024 characters.



Failover supportThe driver provides connection failover support to ensure continuous, uninterrupted access to data. Connectionfailover provides failover protection for new connections. In more traditional scenarios, the driver fails over newconnections to an alternate, or backup, database server if the primary database server is unavailable. However,Google BigQuery currently supports only the notion of a single leader node.Therefore, to ensure a continuousconnection, you only need to set the ConnectionRetryCount and ConnectionRetryDelay connection properties.

See alsoConnection property descriptions on page 45ConnectionRetryCount on page 57ConnectionRetryDelay on page 58

TimeoutsThe driver supports the LoginTimeout, WSTimeout, and WSRetryCount connection properties.The LoginTimeoutproperty specifies the amount of time, in seconds, that the driver waits for a connection to be established beforetiming out the connection request. In contrast, the WSTimeout property specifies the time, in seconds, that thedriver waits for a response to a Web service request. The WSTimeout property can be used in conjunctionwith the WSRetryCount property. The WSRetryCount connection property can be used to retry select queriesthat have timed out.

Session TimeoutsMost remote data sources impose a limit on the duration of active sessions, meaning a session can fail with asession timeout error if the session extends past the limit. This is particularly true when connection pooling isused. The driver automatically attempts to re-establish a new session if the driver receives a session timeouterror from a data source. The driver uses the initial server name, remote user ID, and remote password tore-establish the session. If the attempt fails, the driver returns an error indicating that the session timed outand the attempt to re-establish the session failed.

Web Service Request TimeoutsYou can configure the driver to never time out while waiting for a response to a Web service request or to waitfor a specified interval before timing out by setting the WSTimeout connection property. For fetch requests,you can configure the driver to retry the request a specified number of times by setting the WSRetryCountconnection property. If subsequent attempts to retry a request fail, the driver returns an error indicating thatthe service request timed out and the subsequent requests failed.


SQL escape sequencesThe driver supports the following SQL escape sequences.

• Date, Time, and Timestamp Escape Sequences


Failover support

• Scalar Functions

• Outer Join Escape Sequences

• LIKE Escape Character Sequence for Wildcards

Refer to SQL Escape Sequences for JDBC in the Progress DataDirect for JDBC Drivers Reference for informationabout SQL escape sequences supported by the driver.

Supported scalar functions

The driver supports all the scalar functions supported by Google BigQuery.

Refer to the Google documentation to see the complete list of supported scalar functions. In addition, you canalso determine the supported scalar functions by using DatabaseMetaData methods.

You can use scalar functions in SQL statements with the following syntax:

{fn scalar-function}

where:

scalar-function

is a scalar function supported by the drivers, as listed in the following table.

Example:

SELECT id, name FROM emp WHERE name LIKE {fn UCASE('Smith')}

For descriptions of scalar functions, refer to Scalar Functions in the Progress DataDirect for JDBC DriversReference.

Statement poolingMost applications have a certain set of SQL statements that are executed multiple times and a few SQLstatements that are executed only once or twice during the life of the application. Similar to connection pooling,statement pooling provides performance gains for applications that execute the same SQL statements multipletimes over the life of the application.

A statement pool is a group of prepared statements that can be reused by an application. If you have anapplication that repeatedly executes the exact same SQL statements, statement pooling can improveperformance because the database server does not have to repeatedly parse and create cursors for the samestatement. In addition, the associated network round trips to the database server are avoided.

The drivers have an internal prepared statement pooling mechanism, which allows you to realize the performancebenefits of statement pooling when you are not running from within an application server or another applicationthat provides its own statement pooling.You can enable this driver-based internal statement pooling with theMaxPooledStatements connection property.



https://documentation.progress.com/output/DataDirect/jdbcreferencehelp/index.html#context/JDBC/sql_escapes

https://documentation.progress.com/output/DataDirect/jdbcreferencehelp/index.html#context/JDBC/scalar_functions

The DataDirect for JDBC drivers also support the DataDirect Statement Pool Monitor.You can use the StatementPool Monitor to load statements into and remove statements from the statement pool as well as generateinformation to help you troubleshoot statement pooling performance.The Statement Pool Monitor is an integratedcomponent of the driver, and you can manage statement pooling directly with DataDirect-specific methods. Inaddition, the Statement Pool Monitor can be enabled as a Java Management Extensions (JMX) MBean. Whenenabled as a JMX MBean, the Statement Pool Monitor can be used to manage statement pooling with standardJMX API calls, and it can easily be used by JMX-compliant tools, such as JConsole. To enable the StatementPool Monitor as a JMX MBean, you must register the Statement Pool Monitor MBean with theRegisterStatementPoolMonitorMBean connection property.

For more information, refer to Statement Pool Monitor in the Progress DataDirect for JDBC Drivers Reference.

See alsoMaxPooledStatements on page 70RegisterStatementPoolMonitorMBean on page 77

Connection poolingProgess DataDirect for JDBC drivers support connection pooling using the DataDirect Connection Pool Manager.Typically, creating a connection is the most performance-expensive operations that an application performs.Connection pooling allows you to reuse connections rather than create a new one every time a driver needsto connect to the database. Further, connection pooling manages connection sharing across multiple userrequests to maintain performance and reduce the number of new connections to be created.

JDBC support in the Progress DataDirect for JDBC Drivers Reference guides you through using connectionpooling with the DataDirect Connection Pool Manager.

Isolation levelsThe driver supports the TRANSACTION_NONE isolation level because Google BigQuery does not supporttransactions.

Scrollable cursorsThe driver supports scroll-insensitive result sets.

Note: When the driver cannot support the requested result set type or concurrency, it automatically downgradesthe cursor and generates one or more SQLWarnings with detailed information.


Connection pooling


https://documentation.progress.com/output/DataDirect/jdbcreferencehelp/index.html#context/JDBC/jdbc_support

Unicode supportMultilingual JDBC applications can be developed on any operating system using the driver to access bothUnicode and non-Unicode enabled databases. Internally, Java applications use UTF-16 Unicode encoding forstring data. When fetching data, the driver automatically performs the conversion from the character encodingused by the database to UTF-16. Similarly, when inserting or updating data in the database, the driverautomatically converts UTF-16 encoding to the character encoding used by the database.

The JDBC API provides mechanisms for retrieving and storing character data encoded as Unicode (UTF-16)or ASCII. Additionally, the Java String object contains methods for converting UTF-16 encoding of string datato or from many popular character encodings.

Error handling

SQLExceptionsThe driver reports errors to the application by throwing SQLExceptions. Each SQLException contains thefollowing information:

• Description of the probable cause of the error, prefixed by the component that generated the error

• Native error code (if applicable)

• String containing the XOPEN SQLstate

Driver ErrorsAn error generated by the driver has the format shown in the following example:

[DataDirect][GoogleBigQuery JDBC Driver]Timeout expired.

You may need to check the last JDBC call your application made and refer to the JDBC specification for therecommended action.

Database ErrorsAn error generated by the database has the format shown in the following example:

[DataDirect][GoogleBigQuery JDBC Driver][GoogleBigQuery]Invalid Object Name.

If you need additional information, use the native error code to look up details in your database documentation.

Parameter metadata supportThe driver supports returning parameter metadata as described in "Insert and Update Statements" and "SelectStatements."



Insert and update statements

The driver supports returning parameter metadata for the following forms of Insert and Update statements:

• INSERT INTO department (col1, col2, col3) VALUES(?, ?, ?)

• UPDATE employee SET col1=?, col2=?, col3=? WHERE col1 operator ? [{AND | OR}col2 operator ?]

where:

operator

is any of the following SQL operators:

=, <, >, <=, >=, and <>.

Select statements

The driver supports returning parameter metadata for Select statements that contain parameters in ANSISQL-92 entry-level predicates, for example, such as COMPARISON, BETWEEN, IN, LIKE, and EXISTSpredicate constructs. Refer to the ANSI SQL reference for detailed syntax.

Parameter metadata can be returned for a Select statement if one of the following conditions is true:

• The statement contains a predicate value expression that can be targeted against the source tables in theassociated FROM clause. For example:

SELECT * FROM foo WHERE bar > ?

In this case, the value expression "bar" can be targeted against the table "foo" to determine the appropriatemetadata for the parameter.

• The statement contains a predicate value expression part that is a nested query.The nested query's metadatamust describe a single column. For example:

SELECT * FROM foo WHERE (SELECT x FROM y WHERE z = 1) < ?

The following Select statements show further examples for which parameter metadata can be returned:

SELECT col1, col2 FROM foo WHERE col1 = ? AND col2 > ?SELECT ... WHERE colname = (SELECT col2 FROM t2 WHERE col3 = ?)SELECT ... WHERE colname LIKE ?SELECT ... WHERE colname BETWEEN ? and ?SELECT ... WHERE colname IN (?, ?, ?)SELECT ... WHERE EXISTS(SELECT ... FROM T2 WHERE col1 < ?)

ANSI SQL-92 entry-level predicates in a WHERE clause containing GROUP BY, HAVING, or ORDER BYstatements are supported. For example:

SELECT * FROM t1 WHERE col = ? ORDER BY 1

Joins are supported. For example:

SELECT * FROM t1,t2 WHERE t1.col1 = ?


Parameter metadata support

Fully qualified names and aliases are supported. For example:

SELECT a, b, c, d FROM T1 AS A, T2 AS B WHERE A.a = ? AND B.b = ?

ResultSet metadata supportIf your application requires table name information, the Google BigQuery driver can return table name informationin ResultSet metadata for Select statements. The Select statements for which ResultSet metadata is returnedmay contain aliases, joins, and fully qualified names. The following queries are examples of Select statementsfor which the ResultSetMetaData.getColumnName() method returns the correct table name for columns in theSelect list:

SELECT id, name FROM EmployeeSELECT E.id, E.name FROM Employee E SELECT E.id, E.name AS EmployeeName FROM Employee ESELECT E.id, E.name, I.location, I.phone FROM Employee E, EmployeeInfo I WHERE E.id = I.idSELECT id, name, location, phone FROM Employee, EmployeeInfo WHERE id = empIdSELECT Employee.id, Employee.name, EmployeeInfo.location, EmployeeInfo.phone FROM Employee, EmployeeInfo WHERE Employee.id = EmployeeInfo.id



3Connection property descriptions

You can use connection properties to customize the driver for your environment.This section lists the connectionproperties supported by the driver and describes each property.You can use these connection properties witheither the JDBC DriverManager or a JDBC DataSource. For a DriverManager connection, a propertyis expressed as a key value pair and takes the form property=value. For a data source connection, aproperty is expressed as a JDBC method and takes the form setproperty(value). Connection property namesare case-insensitive. For example, Password is the same as password.

Note: In a JDBC DataSource, string values must be enclosed in double quotation marks, for example,setUser("[email protected]").

Note: The data type listed for each connection property is the Java data type used for the property value in aJDBC data source.

The following tables provide a summary of the connection properties supported by the driver, their correspondingdata source methods, and their default values.

Required propertiesThe following table summarizes connection properties that are required to connect to a Google BigQueryinstance when the default authentication method, OAuth 2.0, is used for authentication.

Table 3: Required properties

DefaultData Source MethodProperty

NonesetAccessTokenAccessToken on page 51



NonesetClientIDClientID on page 53

NonesetClientSecretClientSecret on page 54

NonesetDatasetDataset on page 60

NonesetProjectProject on page 71

NonesetRefreshTokenRefreshToken on page 76

Mapping propertiesThe following table summarizes connection properties involved in mapping the remote Google BigQuery datamodel to a local schema map used to support SQL queries against Google BigQuery.

Table 4: Mapping properties


VarcharLength=65535;SchemaSet=;

setConfigOptionsConfigOptions on page 54

notExistsetCreateMapCreateMap on page 59

For Windows:<application_data_folder>\Local\Progress\DataDirect\GoogleBigQuery_Schema\<user_name>.config

For Unix/Linux:˜/progress/datadirect/GoogleBigQuery_Schema/<user_name>.config

setSchemaMapSchemaMap on page 77

OAuth 2.0 propertiesThe following table summarizes connection properties that are used for OAuth 2.0 authentication.

Table 5: OAuth 2.0 properties


NonesetAccessTokenAccessToken on page 51

NonesetClientIDClientID on page 53

NonesetClientSecretClientSecret on page 54


Chapter 3: Connection property descriptions


NonesetRefreshTokenRefreshToken on page 76

https://www.googleapis.com/auth/bigquery

setScopeScope on page 79

Service account propertiesThe following table summarizes connection properties that are used for service account authentication.

Table 6: Service account properties


NonesetServiceAccountEmailServiceAccountEmail on page 80

NonesetServiceAccountPrivateKeyServiceAccountPrivateKey onpage 81

Legacy SQL propertiesThe following table summarizes connection properties that can be used to execute queries using legacy SQL.

Table 7: Legacy SQL properties


falsesetAllowLargeResultsAllowLargeResults on page 52

_queries_setLegacyDatasetLegacyDataset on page 66

_sql_*setLegacytableLegacyTable on page 67

standardsetSyntaxSyntax on page 82

Failover propertiesThe following table summarizes connection properties that can be used to implement failover.

Table 8: Failover properties


5setConnectionRetryCountConnectionRetryCount on page57

1 (second)setConnectionRetryDelayConnectionRetryDelay on page58

Proxy server propertiesThe following table summarizes proxy server connection properties.


Table 9: Proxy Server properties


NonesetProxyHostProxyHost on page 72

NonesetProxyPasswordProxyPassword on page 72

0setProxyPortProxyPort on page 73

NonesetProxyUserProxyUser on page 74

Web Service propertiesThe following table summarizes Web service connection properties, including those related to timeouts.

Table 10: Web Service properties


10000setWSFetchSizeWSFetchSize on page 83

1setWSPoolSizeWSPoolSize on page 84

1setWSRetryCountWSRetryCount on page 85

120 (seconds)setWSTimeoutWSTimeout on page 86

Timeout propertiesThe following table summarizes timeout connection properties.

Table 11:Timeout properties


0 (no timeout)setJobTimeoutJobTimeout on page 63

0setLoginTimeoutLoginTimeout on page 69

0setWSRetryCountWSRetryCount on page 85

120 (seconds)setWSTimeoutWSTimeout on page 86

Statement pooling propertiesThe following table summarizes statement pooling connection properties.

Table 12: Statement pooling properties


NonesetImportStatementPoolImportStatementPool on page 62




0setMaxPooledStatementsMaxPooledStatements on page70

falsesetRegisterStatementPoolMonitorMBeanRegisterStatementPoolMonitorMBeanon page 77

Additional propertiesThe following table summarizes additional connection properties.

Table 13: Additional properties


1setConvertNullConvertNull on page 58

100 (rows)setFetchSizeFetchSize on page 61

falsesetJavaDoubleToStringJavaDoubleToString on page 62

rawsetJsonFormatJsonFormat on page 64

NonesetLocationLocation on page 67

ddlogging.propertiessetLogConfigFileLogConfigFile on page 68

0 (no limit)setMaximumBytesBilledMaximumBytesBilled on page 69

truesetRefreshSchemaRefreshSchema on page 74

truesetRefreshSchemaForDDLRefreshSchemaForDDL on page75

NonesetSpyAttributesSpyAttributes on page 81

1setUseQueryCacheUseQueryCache on page 83


• AccessToken

• AllowLargeResults

• AuthenticationMethod

• ClientID

• ClientSecret

• ConfigOptions

• ConnectionRetryCount


• ConnectionRetryDelay

• ConvertNull

• CreateMap

• Dataset

• FetchSize

• ImportStatementPool

• JavaDoubleToString

• JobTimeout

• JsonFormat

• KMSKeyName

• LegacyDataset

• LegacyTable

• Location

• LogConfigFile

• LoginTimeout

• MaximumBytesBilled

• MaxPooledStatements

• Project

• ProxyHost

• ProxyPassword

• ProxyPort

• ProxyUser

• RefreshSchema

• RefreshSchemaForDDL

• RefreshToken

• RegisterStatementPoolMonitorMBean

• SchemaMap

• Scope

• ServiceAccountEmail

• ServiceAccountPrivateKey

• SpyAttributes

• Syntax

• UseQueryCache



• WSFetchSize

• WSPoolSize

• WSRetryCount

• WSTimeout

AccessToken

PurposeSpecifies the access token required to authenticate to a Google BigQuery instance when OAuth 2.0 is enabled(AuthenticationMethod=oauth2).

The access token acts as a session ID for the connection. See "Generating access token and refresh token"to know how to obtain an access token.

Valid Valuesstring

where:

string

is the access token you have obtained from Google BigQuery.

Notes

• If a value for the AccessToken property is not specified, the driver uses the value of the RefreshTokenproperty to generate an access token to make a connection.

• If both AccessToken and RefreshToken values are not specified, the driver cannot make a successfulconnection.

• If both AccessToken and RefreshToken values are specified, the driver uses the AccessToken value.However, if the AccessToken value expires, it uses the RefreshToken value to generate a new AccessTokenvalue.

Data Source MethodsetAccessToken

DefaultNone

Data TypeString

See alsoConfiguring OAuth 2.0 authentication on page 33

Generating access tokens and refresh tokens on page 35


AccessToken

RefreshToken on page 76

Required properties

AllowLargeResults

PurposeDetermines whether the driver returns results larger than 128 MB for legacy SQL queries.

Note: When AllowLargeResults is set to true, the results are stored in the dataset and table specified usingLegacyDataset and LegacyTable properties.

Valid Valuesfalse | true

BehaviorIf set to false, the driver does not support query results larger than 128 MB.

If set to true, the driver supports query results larger than 128 MB.

Data Source MethodsetAllowLargeResults

Defaultfalse

Data TypeBoolean

See alsoLegacyDataset on page 66

LegacyTable on page 67

Legacy SQL properties

AuthenticationMethod

PurposeDetermines which authentication method the driver uses when establishing a connection.

Valid valuesoauth2 | serviceaccount



BehaviorIf set to oauth2, the driver uses OAuth 2.0 authentication when establishing a connection.

If set to serviceaccount, the driver uses service account authentication when establishing a connection.

Data Source MethodsetAuthenticationMethod

Defaultoauth2

Data TypeString

See AlsoConfiguring OAuth 2.0 authentication on page 33

Configuring service account authentication on page 34

ClientID

PurposeSpecifies the consumer key for your application. The driver uses this value when authenticating to a GoogleBigQuery instance using OAuth 2.0 (AuthenticationMethod=oauth2).

See "Generating access token and refresh token" to know how to obtain the client ID for your application.

Valid Valuesstring

where:

string

is the consumer key for your application.

Data Source MethodsetClientID

DefaultNone

Data TypeString



ClientID


Required properties

ClientSecret

PurposeSpecifies the consumer secret for your application. The driver uses this value when authenticating to a GoogleBigQuery instance using OAuth 2.0 (AuthenticationMethod=oauth2).

See "Generating access token and refresh token" to know how to obtain the client secret for your application.

Valid Valuesstring

where:

string

is the consumer secret for your aplication.

Data Source MethodsetClientSecret

DefaultNone

Data TypeString



Required properties

ConfigOptions

PurposeDetermines how the mapping of the remote Google BigQuery data model to a local schema map can beconfigured, customized, and updated.



NotesThis property is primarily used for initial configuration of the driver for a particular user. It is not intended foruse with every connection. By default, the driver configures itself and this option is normally not needed. IfConfigOptions is specified on a connection after the initial configuration, the values specified for ConfigOptionsmust match the values specified for the initial configuration.

Valid Values( key = value [; key = value ])

where:

key

is one of the following configuration options:

• VarcharLength

• SchemaSet

value

specifies a setting for the configuration option.

When specifying configuration options in a connection string, key value pairs must be enclosed in parenthesesand separated by a semicolon. For example:

ConfigOptions=(VarcharLength=65535;SchemaSet=;)

Data Source MethodsetConfigOptions

Default

VarcharLength=65535;SchemaSet=;

Data TypeString

See alsoMapping properties

SchemaSet (Configuration Option) on page 55

VarcharLength (Configuration Option) on page 56

SchemaSet (Configuration Option)

PurposeSpecifies the project-dataset pairs that you want the driver to fetch metadata for.


ConfigOptions

Valid ValuesString | *

where:

String

is a comma-separated list of project and dataset pairs (project:dataset).

BehaviorIf set to String, the driver fetches metadata for the specified project and dataset pairs. For example, if youset SchemaSet=project1:dataset1,project1:dataset2, the driver fetches metadata for only dataset1and dataset2 of project1. If you want the driver to fetch metadata for all the datasets of a project, put an asterisk(*) after the colon. For example, SchemaSet=project1:*.

If set to *, the driver fetches metadata for all the projects and datasets your account has access to.

Notes

• If you do not specify a value for SchemaSet, the driver fetches metadata for the project and dataset specifiedat the time of connection.

• The driver does not fetch metadata for public projects by default. To fetch metadata for public projects,specify them using the SchemaSet configuration option.

DefaultThe project and dataset specified at connection.

See alsoVarcharLength (Configuration Option) on page 56

VarcharLength (Configuration Option)

PurposeSpecifies the maximum length the driver supports for String type columns.

Valid Valuesx

where:

x

is a positive integer between 1 and 2147483647 indicating the maximum length.

Default65535

See alsoSchemaSet (Configuration Option) on page 55



ConnectionRetryCount

PurposeThe number of times the driver retries connection attempts to a Google BigQuery instance until a successfulconnection is established.

Valid Values0 | x

where x is a positive integer that represents the number of retries.

BehaviorIf set to 0, the driver does not try to reconnect after the initial unsuccessful attempt.

If set to x, the driver retries connection attempts the specified number of times. If a connection is not establishedduring the retry attempts, the driver returns an exception that is generated by the last database server to whichit tried to connect.

ExampleIf this property is set to 2, the driver retries the server twice after the initial retry attempt.

Notes

• If an application sets a login timeout value (for example, using DataSource.loginTimeout orDriverManager.loginTimeout), and the login timeout expires, the driver ceases connection attempts.

• The ConnectionRetryDelay property specifies the wait interval, in seconds, to occur between retry attempts.

Data Source MethodsetConnectionRetryCount

Default5

Data Typeint

See alsoFailover properties


ConnectionRetryCount

ConnectionRetryDelay

PurposeThe number of seconds the driver waits between connection retry attempts when ConnectionRetryCount isset to a positive integer.

Valid Values0 | x

where:

x

is a number of seconds.

BehaviorIf set to 0, the driver does not delay between retries.

If set to x, the driver waits between connection retry attempts the specified number of seconds.

ExampleIf ConnectionRetryCount is set to 2 and this property is set to 3, the driver retries the server twice after theinitial retry attempt. The driver waits 3 seconds between retry attempts.

Data Source MethodsetConnectionRetryDelay

Default1 (second)

Data Typeint

See alsoFailover properties

ConvertNull

PurposeControls how data conversions are handled for null values.

Valid Values0 | 1



BehaviorIf set to 0, the driver does not perform the data type check if the value of the column is null. This allows nullvalues to be returned even though a conversion between the requested type and the column type is undefined.

If set to 1, the driver checks the data type being requested against the data type of the table column that storesthe data. If a conversion between the requested type and column type is not defined, the driver generates an"unsupported data conversion" exception regardless of whether the column value is NULL.

Data Source MethodsetConvertNull

Default1

Data Typeint

See alsoAdditional properties

CreateMap

PurposeSpecifies whether the driver creates a new map of the Google BigQuery data model when establishing theconnection.

Valid ValuesforceNew | notExist | no | session

BehaviorIf set to forceNew, the driver deletes the current schema map specified by the SchemaMap property andcreates a new one at the same location.

Warning: This causes all views, data caches, and map customizations defined in the current schema map tobe lost.

If set to notExist, the driver uses the current schema map specified by the SchemaMap property. If one doesnot exist, the driver creates one.

If set to no, the driver uses the current schema map specified by the SchemaMap property. If one does notexist, the connection fails.

If set to session, the driver does not create or use schema map. It stores metadata in the memory.

Data Source MethodsetCreateMap


CreateMap

DefaultnotExist

Data TypeString

See Also

• SchemaMap on page 77

• Mapping properties

Dataset

PurposeSpecifies the name of the dataset that you want the driver to connect to. The datasets in Google BigQuery areequivalent to schemas in JDBC.

Note: If you want to query data in a dataset different from the one you specified at the time of connection,specify it in the following format:

project.dataset.table

Valid Valuesstring

where:

string

is the name of your Google BigQuery dataset.

Data Source MethodsetDataset

DefaultNone

Data TypeString


Project on page 71

Required properties



FetchSize

PurposeSpecifies the maximum number of rows that the driver processes before returning data to the application whenexecuting a Select. This value provides a suggestion to the driver as to the number of rows it should internallyprocess before returning control to the application. The driver may fetch fewer rows to conserve memory whenprocessing exceptionally wide rows.

Valid Values0 | x

where:

x

is a positive integer indicating the number of rows that should be processed.

BehaviorIf set to 0, the driver processes all the rows of the result before returning control to the application. When largedata sets are being processed, setting FetchSize to 0 can diminish performance and increase the likelihoodof out-of-memory errors.

If set to x, the driver limits the number of rows that may be processed for each fetch request before returningcontrol to the application.

Notes

• To optimize throughput and conserve memory, the driver uses an internal algorithm to determine how manyrows should be processed based on the width of rows in the result set. Therefore, the driver may processfewer rows than specified by FetchSize when the result set contains exceptionally wide rows. Alternatively,the driver processes the number of rows specified by FetchSize when the result set contains rows ofunexceptional width.

• FetchSize and WSFetchSize can be used to adjust the trade-off between throughput and response time.Smaller fetch sizes can improve the initial response time of the query. Larger fetch sizes can improve overallresponse times at the cost of additional memory.

• You can use FetchSize to reduce demands on memory and decrease the likelihood of out-of-memory errors.Simply, decrease FetchSize to reduce the number of rows the driver is required to process before returningdata to the application.

Data Source MethodsetFetchSize

Default100 (rows)

Data TypeInt


FetchSize


ImportStatementPool

PurposeSpecifies the path and file name of the file to be used to load the contents of the statement pool. When thisproperty is specified, statements are imported into the statement pool from the specified file.

If the driver cannot locate the specified file when establishing the connection, the connection fails and the driverthrows an exception. See "Importing Statements into a Statement Pool" for details about the import file.

Valid Valuesstring

where:

string

is the path and file name of the file to be used to load the contents of the statement pool.

Data Source MethodsetImportStatementPool

Defaultempty string

Data TypeString

See also

• Importing statements into a statement pool in the Progress DataDirect for JDBC Drivers Reference

• Statement Pooling properties

JavaDoubleToString

PurposeDetermines which algorithm the driver uses when converting a double or float value to a string value. By default,the driver uses its own internal conversion algorithm, which improves performance.

Valid Valuestrue | false



https://documentation.progress.com/output/DataDirect/jdbcreferencehelp/index.html#context/JDBC/importing_statement_pool

BehaviorIf set to true, the driver uses the JVM algorithm when converting a double or float value to a string value. Ifyour application cannot accept rounding differences and you are willing to sacrifice performance, set this valueto true to use the JVM conversion algorithm.

If set to false, the driver uses its own internal algorithm when converting a double or float value to a stringvalue.This value improves performance, but slight rounding differences within the allowable error of the doubleand float data types can occur when compared to the same conversion using the JVM algorithm.

Data Source MethodsetJavaDoubleToString

Defaultfalse

Data Typeboolean


JobTimeout

PurposeSpecifies the time, in seconds, that the driver waits for a job to run before timing it out.

Valid Values0 | x

where:

x

is a positive integer that defines the number of seconds the driver waits for a job to run.

BehaviorIf set to 0, the driver waits indefinitely for a job to run; there is no timeout.

If set to x, the driver uses the value as the default timeout for any job run against a Google BigQuery instance.

Data Source MethodsetJobTimeout

Default0 (no timeout)


JobTimeout

Data TypeInt

See alsoTimeout properties

JsonFormat

PurposeDetermines the JSON string format in which the driver returns values for complex data types, such as Arrayand Struct.

Valid Valuesraw | keyvalue | pretty | unsafe

BehaviorIf set to raw, the values are returned in their native Google BigQuery format.

If set to keyvalue, the values are returned in key value pairs. Also, if there is a closing curly bracket (}) or aback slash (\) in a value, the driver escapes it by adding a back slash (\) in front of it. For example, if the valueis "8}", the driver returns it as "8\}".

If set to pretty, only the values are returned (unaccompanied by keys).

If set to unsafe, the values are returned in key value pairs. However, if there are any special characters inthem, they are not escaped.

ExamplesIf the data type is Simple Array and values are [121,122,123], the driver returns the values in one of the followingformats based on the valid value you set for the JsonFormat property:

Data FormatValid Value

[{v=121},{v=122},{v=123}]keyvalue

[121, 122, 123]pretty

[{"v":"121"},{"v":"122"},{"v":"123"}]raw

[{v=121},{v=122},{v=123}]unsafe

Data Source MethodsetJsonFormat

Defaultraw



Data TypeString


KMSKeyName

PurposeSpecifies the customer-managed encryption key (CMEK) that the driver uses for executing queries. If it is notspecified, the driver uses the default key encryption key from Google.

To learn more about CMEK, refer to the Google documentation.

Valid Values

projects/project/locations/location/KeyRings/keyring/cryptoKeys/key

where:

project

specifies the name of the project that you want the driver to connect to.

location

specifies the geographical location where your dataset is stored.

keyring

specifies the key ring value, which is a prerequisite for creating CMEK. To learn how to create a keyring, refer to the Google documentation.

key

specifies the CMEK value. To learn how to create a key, refer to the Google documentation.

Notes

• Passing KMSKeyName as part of job configuration is not supported for DDL statements. Therefore, forCreate statements, CMEK must be provided using the Options clause, in the following format:

CREATE TABLE <dataset_name>.<table_name>(<column_name> <column_type>)OPTIONS(kms_key_name='projects/project/locations/location/KeyRings/keyring/cryptoKeys/key')

• If a table is encrypted using CMEK, you can perform insert and select operations on it with or withoutspecifying CMEK. However, you must not specify an incorrect CMEK, as it leads to query failure.

• If you specify a CMEK to query a table that is not encrypted with CMEK, the query will fail.

• CMEKs specified at connection are used to execute queries for the life of the connection.


KMSKeyName

Data Source MethodsetKMSKeyName

DefaultNone

Data TypeString

LegacyDataset

PurposeSpecifies the dataset where results for legacy SQL queries are stored when AllowLargeResults is enabled(AllowLargeResults=true).

Valid Valuesstring

where:

string

is the name of the dataset where results for legacy SQL queries are stored when AllowLargeResultsis enabled (AllowLargeResults=true).

Data Source MethodsetLegacyDataset

Default_queries_

Data TypeString

See alsoAllowLargeResults on page 52

LegacyTable on page 67




LegacyTable

PurposeSpecifies the table where results for legacy SQL queries are stored when AllowLargeResults is enabled(AllowLargeResults=true).

Valid Valuesstring

where:

string

is the name of the table where results for legacy SQL queries are stored when AllowLargeResultsis enabled (AllowLargeResults=true).

Data Source MethodsetLegacytable

Default_sql_*

Data TypeString

See alsoAllowLargeResults on page 52

LegacyDataset on page 66


Location

PurposeSpecifies the geographical location where your dataset is stored. Google BigQuery allows storing datasets ineither one single geographical place, such as Tokyo, or a large geographical area, such as Europe.

For more information on dataset locations, refer to the Google BigQuery documentation.

Valid Valuesstring

where:


LegacyTable

string

is the geographical location where your dataset is stored.

Data Source MethodsetLocation

DefaultNone

Data TypeString

See alsoDataset on page 60

Additional properties

LogConfigFile

PurposeSpecifies the file name, and optionally, the path of the properties file used to initialize driver logging.

Valid Valuesstring

where:

string

is the relative or fully qualified path of the properties file to load to initialize driver logging. If you donot specify a path, the driver looks for this file in the current working directory. If the specified filedoes not exist, the driver continues searching for an appropriate properties file as described in "UsingJava Logging."

Data Source MethodsetLogConfigFile

Defaultddlogging.properties

Data TypeString

See alsoUsing Java Logging in the Progress DataDirect for JDBC Drivers Reference



https://documentation.progress.com/output/DataDirect/jdbcreferencehelp/index.html#context/JDBC/java_logging

Additional properties

LoginTimeout

PurposeThe amount of time, in seconds, that the driver waits for a connection to be established before timing out theconnection request.

Valid Values0 | x

where:

x

is a positive integer that represents a number of seconds.

BehaviorIf set to 0, the driver does not time out a connection request.

If set to x, the driver waits for the specified number of seconds before returning control to the application andthrowing a timeout exception.

Data Source MethodsetLoginTimeout

Default0

Data Typeint

See alsoTimeout properties

MaximumBytesBilled

PurposeSpecifies the maximum number of bytes a query can read. As per the on-demand pricing model of GoogleBigQuery, charges are billed based on the number of bytes a query reads. To control the cost a query mayincur, you can set a limit. Once the limit is exceeded, the query fails without incurring any cost.

Valid Values0 | x


LoginTimeout

where:

x

is a positive integer that defines the maximum number of bytes a query can read.

BehaviorIf set to 0, the driver allows queries to read indefinite amount of data; there is no limit.

If set to x, the driver uses the value as the limit beyond which queries fail without incurring any cost.

Data Source MethodsetMaximumBytesBilled

Default0 (no limit)

Data TypeInt


MaxPooledStatements

PurposeThe maximum number of pooled prepared statements for this connection. Setting MaxStatements to an integergreater than zero (0) enables the driver’s internal prepared statement pooling, which is useful when the driveris not running from within an application server or another application that provides its own prepared statementpooling.

Valid Values0 | x

where

x

is a positive integer that represents a number of pooled prepared statements.

BehaviorIf set to 0, the driver’s internal prepared statement pooling is not enabled.

If set to x, the driver enables the DataDirect Statement Pool and uses the specified value to cache a certainnumber of prepared statements created by an application. If the value set for this property is greater than thenumber of prepared statements that are used by the application, all prepared statements that are created bythe application are cached. Because CallableStatement is a sub-class of PreparedStatement, CallableStatementsalso are cached.



ExampleIf the value of this property is set to 20, the driver caches the last 20 prepared statements that are created bythe application.

Data Source MethodsetMaxPooledStatements

Default0

Data Typeint

See alsoStatement pooling properties

Project

PurposeSpecifies the name of the project that you want the driver to connect to. The projects in Google BigQuery areequivalent to catalogs in JDBC.

Note: If you want to query data in a project different from the one you specified at the time of connection,specify it in the following format:

project.dataset.table

Valid Valuesstring

where:

string

is the name of your Google BigQuery project.

Data Source MethodsetProject

DefaultNone

Data TypeString


Project


Dataset on page 60

Required properties

ProxyHost

DescriptionIdentifies a proxy server to use for the first connection.

Valid Valuesserver_name | IP_address

where:

server_name

is the name of the proxy server, which may be qualified with the domain name.

IP_address

is an IP address, specified in either IPv4 or IPv6 format, or a combination of the two.

Data Source MethodsetProxyHost

Defaultempty string

See also

• Connecting through a proxy server on page 32

• Proxy server properties

ProxyPassword

PurposeSpecifies the password needed to connect to a proxy server for the first connection.

Valid Valuespassword

where:



password

is a valid password for that server. Contact your system administrator to obtain a valid password.

Data Source MethodsetProxyPassword

Defaultempty string

See alsoConnecting through a proxy server on page 32

Proxy server properties

ProxyPort

PurposeSpecifies the port number where the proxy server is listening for HTTP or HTTPS requests for the first connection.

Valid Valuesport

where:

port

is the port number on which the proxy server is listening. Contact your system administrator to obtainthe correct port.

Data Source MethodsetProxyPort

Default0




ProxyPort

ProxyUser

PurposeSpecifies the specifies the user name needed to connect to a proxy server for the first connection.

Valid Valuesuser_name

where:

user_name

is a valid user ID for the proxy server.

Data Source MethodsetProxyUser

Defaultempty string



RefreshSchema

PurposeSpecifies whether the driver automatically refreshes the map of the data model when a user connects to aREST service.


BehaviorIf set to true, the driver automatically refreshes the map of the data model when a user connects to a RESTservice. Changes to objects since the last time the map was generated will be shown in the metadata.

If set to false, the driver does not refresh the map of the data model when a user connects to a REST service.

Notes

• This property should not be enabled (RefreshSchema=true) when CreateMap=session.



Data Source MethodsetRefreshSchema

Defaulttrue


RefreshSchemaForDDL

PurposeDetermines whether the driver automatically refreshes the map of the data model when a user performs a DDLoperation (Create or Drop).


BehaviorIf set to true, the driver automatically refreshes the map of the data model when a user performs a DDLoperation. As a result, the DDL operations take longer than usual to complete. Also, changes to objects sincethe last time the map was generated will be shown in the metadata.

If set to false, the driver does not refresh the map of the data model when a user performs a DDL operation.As a result, the DDL operations take less time to complete. However, the metadata calls may return inaccurateresults because changes to objects since the last time the map was generated will not be shown in the metadata.

Notes

• This property should not be enabled (RefreshSchema=true) when CreateMap=session.

Data Source MethodsetRefreshSchemaForDDL

Defaulttrue



RefreshSchemaForDDL

RefreshToken

PurposeSpecifies the refresh token used to either request a new access token or renew an expired access token. If anaccess token is not provided or expires at the time of connection, the access token generated using refreshtoken is used to authenticate to a Google BigQuery instance when OAuth 2.0 is enabled(AuthenticationMethod=oauth2).

See "Generating access token and refresh token" to know how to obtain a refresh token.

Valid Valuesstring

where:

string

is the refresh token you have obtained from Google BigQuery.

Notes

• If a value for the AccessToken property is not specified, the driver uses the value of the RefreshTokenproperty to generate an access token to make a connection.

• If both AccessToken and RefreshToken values are not specified, the driver cannot make a successfulconnection.

• If both AccessToken and RefreshToken values are specified, the driver uses the AccessToken value.However, if the AccessToken value has expired, it uses the RefreshToken value to generate a newAccessToken value.

Data Source MethodsetRefreshToken

DefaultNone

Data TypeString



AccessToken on page 51

Required properties



RegisterStatementPoolMonitorMBean

PurposeRegisters the Statement Pool Monitor as a JMX MBean when statement pooling has been enabled withMaxPooledStatements. This allows you to manage statement pooling with standard JMX API calls and to useJMX-compliant tools, such as JConsole.


BehaviorIf set to true, the driver registers an MBean for the statement pool monitor for each statement pool.This givesapplications access to the Statement Pool Monitor through JMX when statement pooling is enabled.

If set to false, the driver does not register an MBean for the Statement Pool Monitor for any statement pool.

NotesRegistering the MBean exports a reference to the Statement Pool Monitor.The exported reference can preventgarbage collection on connections if the connections are not properly closed. When garbage collection doesnot take place on these connections, out of memory errors can occur.

Data Source MethodsetRegisterStatementPoolMonitorMBean

Defaultfalse

Data TypeBoolean

See also

• Statement Pool Monitor in the Progress DataDirect for JDBC Drivers Reference

• MaxPooledStatements on page 70

• Statement pooling properties

SchemaMap

PurposeSpecifies either the name or the absolute path and name of the configuration file where the map of the GoogleBigQuery data model is written. The driver looks for this file when connecting to a Google BigQuery instance.If the file does not exist, the driver creates one.


RegisterStatementPoolMonitorMBean


Valid Valuesstring

where:

string

is either the name or the absolute path and name (including the .config extension) of theconfiguration file. For example, if SchemaMap is set to a value of:

• ABC, the driver either creates or looks for the configuration file ABC in the working directory ofyour application.

• C:\\Users\\Default\\AppData\\Local\\Progress\\DataDirect\\GoogleBigQuery_Schema\\[email protected], the driver either creates or looksfor the configuration file [email protected] in the directoryC:\Users\Default\AppData\Local\Progress\DataDirect\GoogleBigQuery_Schema.

Notes

• When connecting to a Google BigQuery instance, the driver looks for the schema map configuration file. Ifthe configuration file does not exist, the driver creates the schema map configuration file using the nameand location you have provided. If you do not provide a name and location for the configuration file, thedriver creates it using default values.

• The driver uses the path specified in this connection property to store additional internal files.

• You can refresh the internal files related to an existing view of your data by using the SQL extension RefreshMap. Refresh Map runs a discovery against your native data and updates your internal files accordingly.

ExampleAs the following examples show, escapes are needed when specifying SchemaMap for a data source but arenot used when specifying SchemaMap in a DriverManager connection URL.

Driver Manager Example

jdbc:datadirect:googlebigquery:Project=myproject;Dataset=mydataset;AccessToken=abcdefghi12345678;RefreshToken=wxyz123456789;ClientID=123abc.apps.googleusercontent.com;ClientSecret=ab123xy;SchemaMap=C:\Users\Default\AppData\Local\Progress\DataDirect\GoogleBigQuery_Schema\[email protected]

Data Source Example

GoogleBigQueryDataSource ds = new GoogleBigQueryDataSource();ds.setDescription("My Google BigQuery Datasource");ds.setProject("myproject");ds.setDataset("mydataset");ds.setAccessToken("abcdefghi12345678");ds.setRefreshToken("wxyz123456789");ds.setClientID("123abc.apps.googleusercontent.com");ds.setClientSecret("ab123xy");ds.setSchemaMap("C:\\Users\\Default\\AppData\\Local\\Progress \\DataDirect\\GoogleBigQuery_Schema\\[email protected]")

Data Source MethodsetSchemaMap



Default

• For Windows XP and Windows Server 2003

• userprofile\ApplicationData\Local\Progress\DataDirect\GoogleBigQuery_Schema\user_name.config

• For other Windows platforms

• User data source:userprofile\AppData\Local\Progress\DataDirect\GoogleBigQuery_Schema\user_name.config

• System data source:C:\Users\Default\AppData\Local\Progress\DataDirect\GoogleBigQuery_Schema\user_name.config

• For UNIX/Linux

• ˜/progress/datadirect/GoogleBigQuery_Schema/user_name.config

Data TypeString


Mapping properties

Scope

PurposeSpecifies a space-separated list of OAuth scopes that limit the permissions granted by an access token at thetime of connection.

Valid Valuesstring

where:

string

is a space-separated list of security scopes.

ExamplesThe following example demonstrates a configuration that allows the user to view and manage tables createdfrom Google drive.

Scope=https://www.googleapis.com/auth/drive

Data Source MethodsetScope


Scope

Defaulthttps://www.googleapis.com/auth/bigquery

Data TypeString

See Also

• AccessToken on page 51

• Configuring OAuth 2.0 authentication on page 33

• OAuth 2.0 properties

ServiceAccountEmail

PurposeSpecifies the email address associated with your service account that is required to authenticate to a GoogleBigQuery instance when service account authentication is enabled(AuthenticationMethod=serviceaccount).

To learn more about service accounts and service account emails, refer to Google documentation.

Valid Valuesstring

where:

string

is your service account's email address.

Data Source MethodsetServiceAccountEmail

DefaultNone

Data TypeString

See alsoConfiguring service account authentication on page 34

ServiceAccountPrivateKey on page 81

Service account properties



ServiceAccountPrivateKey

PurposeSpecifies the full path to the .json or .p12 private key file that contains the key required to authenticate to aGoogle BigQuery instance when service account authentication is enabled(AuthenticationMethod=serviceaccount).You can dowload the private key file from Google CloudPlatform (GCP) Console.

To learn more about service accounts and service account private keys, refer to Google documentation.

Valid Valuesstring

where:

string

is the full path to the .json or .p12 private key file.

Data Source MethodsetServiceAccountPrivateKey

DefaultNone

Data TypeString

See alsoConfiguring service account authentication on page 34

ServiceAccountEmail on page 80

Service account properties

SpyAttributes

PurposeEnables DataDirect Spy to log detailed information about calls that are issued by the driver on behalf of theapplication. DataDirect Spy is not enabled by default.

Valid Values( spy_attribute [; spy_attribute ]...)

where:


ServiceAccountPrivateKey

spy_attribute

is any valid DataDirect Spy attribute. See "DataDirect Spy Attributes" for a list of supported attributes.

NotesIf coding a path on Windows to the log file in a Java string, the backslash character (\) must be preceded bythe Java escape character, a backslash. For example: log=(file)C:\\temp\\spy.log.

ExampleThe following value instructs the driver to log all JDBC activity to a file using a maximum of 80 characters foreach line.

(log=(file)/tmp/spy.log;linelimit=80)

Data Source MethodsetSpyAttributes

DefaultEmpty string

Data TypeString

See also

• Tracking JDBC calls with DataDirect Spy in the Progress DataDirect for JDBC Drivers Reference

• Additional properties

Syntax

PurposeSpecifies the Google BigQuery SQL dialect to be used for querying data.

Valid Valuesstandard | legacy

BehaviorIf set to standard, the driver uses the standard SQL dialect for querying data.

If set to legacy, the driver uses the legacy SQL dialect for querying data.

Data Source MethodsetSyntax



https://documentation.progress.com/output/DataDirect/jdbcreferencehelp/index.html#context/JDBC/datadirect_spy

Defaultstandard

Data TypeString

See alsoStandard and legacy SQL support on page 38


UseQueryCache

PurposeDetermines whether the driver uses Google BigQuery's query cache to save results.


BehaviorIf set to true, the driver uses query cache to save results.

If set to false, the driver does not use query cache.

Data Source MethodsetUseQueryCache

Defaulttrue

Data TypeBoolean


WSFetchSize

PurposeSpecifies the number of rows of data the driver attempts to fetch for each JDBC call.


UseQueryCache

Valid Values0 | x

where:

x

is a positive integer from 1 to 2147483647 that defines a number of rows.

BehaviorIf set to 0, the driver attempts to fetch up to a maximum of 2147483647 rows. This value typically provides themaximum throughput.

If set to x, the driver attempts to fetch up to a maximum of the specified number of rows. Setting the valuelower than 1000000 can reduce the response time for returning the initial data. Consider using a smallerWSFetch size for interactive applications only.

NotesWSFetchSize and FetchSize can be used to adjust the trade-off between throughput and response time. Smallerfetch sizes can improve the initial response time of the query. Larger fetch sizes can improve overall responsetimes at the cost of additional memory.

Data Source MethodsetWSFetchSize

Default1000000

Data TypeInt

See alsoWeb Service properties

WSPoolSize

PurposeSpecifies the maximum number of Google BigQuery sessions the driver uses. This allows the driver to havemultiple web service requests active when multiple JDBC connections are open, thereby improving throughputand performance.

Valid Valuesx

where:



x

is the number of Google BigQuery sessions the driver uses to distribute calls. This value should notexceed the number of sessions permitted by your Google BigQuery account.

Notes

• You can improve performance by increasing the number of sessions specified by this property. By increasingthe number of sessions the driver uses, you can improve throughput by distributing calls across multiplesessions when multiple connections are active.

• The maximum number of sessions is determined by the setting of WSPoolSize for the connection thatinitiates the session. For subsequent connections to an active session, the setting is ignored and a warningis returned.To change the maximum number of sessions, close all connections using the Google BigQuerydriver; then, open a new Google BigQuery connection with desired limit specified for this property.

Data Source MethodsetWSPoolSize

Default1

Data TypeInt


WSRetryCount

DescriptionThe number of times the driver retries a timed-out Select, Insert, Update, or Delete request.The timeout periodis specified by the WSTimeout connection property.

Valid Values0 | x

where:

x

is a positive integer.

BehaviorIf set to 0, the driver does not retry timed-out requests after the initial unsuccessful attempt.

If set to x, the driver retries the timed-out request the specified number of times.


WSRetryCount

Data Source MethodsetWSRetryCount

Default0

Data TypeInt


WSTimeout

PurposeSpecifies the time, in seconds, that the driver waits for a response to a Web service request.

Valid Values0 | x

where:

x

is a positive integer that defines the number of seconds the driver waits for a response to a Webservice request.

BehaviorIf set to 0, the driver waits indefinitely for a response; there is no timeout.

If set to x, the driver uses the value as the default timeout for any statement created by the connection.

If a Select request times out and WSRetryCount is set to retry timed-out requests, the driver retries the requestthe specified number of times.

Data Source MethodsetWSTimeout

Default120 (seconds)

Data TypeInt




progress® datadirect® for jdbc™ for google bigquery™ user ... · of google bigquery. it...

Documents