Connection Configuration Interface

The CData Virtuality Server can add new data sources on the fly. To connect them, you can use special stored procedures listed on this page.

System Stored Procedures: Short Description

createConnection(name, jbossCLITemplateName, connectionOrResourceAdapterProperties, encryptedProperties) creates a connection with the name passed with name and properties passed with connectionOrResourceAdapterProperties (may be null) using a CLI template jbossCLITemplateName. Actual JNDI name will be formed from the given JNDI name and CLI template name divided with a hyphen (like 'template-name'). Encrypted properties may be provided via encryptedProperties.
SQL
```
CALL SYSADMIN.createConnection('<data connection name>', '<cli template name>', 'server=<server>,port=<port>,database=<database>', ''); 
```
removeConnection(name) removes connection with the name passed with name.
SQL
```
CALL SYSADMIN.removeConnection('<data connection name>');
```
createDataSource(name, translator, modelProperties, translatorProperties, encryptedModelProperties, encryptedTranslatorProperties) creates a data source with the name passed with name and on top of translator passed with translator, model properties passed with modelProperties (may be null) and translator properties passed with translatorProperties (may be null). Encrypted properties may be provided via encryptedModelProperties and encryptedTranslatorProperties .
SQL
```
CALL SYSADMIN.createDatasource('<data source name>', '<server-type>', 'importer.useFullSchemaName=<TRUE|FALSE>, importer.schemaPattern=<schema names and/or patterns>', '', '', '')
```
For the schemaPattern property usage details, please check the Data Source Properties section of the JDBC Connectors page.
removeDataSource(name) removes a data source with the name passed with name.
SQL
```
CALL SYSADMIN.removeDatasource('<data source name>');
```
refreshDataSource(name) refreshes a data source with the name passed with name.
SQL
```
CALL SYSADMIN.refreshDataSource('<data source name>');
```
refreshAllDataSources() refreshes all data sources.
getDataSourceMetadataDiff(name) returns metadata difference for the data source name passed with name.
SQL
```
SELECT * FROM (CALL SYSADMIN.getDataSourceMetadataDiff('<data source name>')) a;
```
getAllDataSourcesMetadataDiff() returns metadata difference for all data sources.

executeCli(script) executes an arbitrary JBoss CLI script passed with script. Each command in the script is on a different line (divided by the LF char).

SQL

CALL SYSADMIN.executeCli('/subsystem=resource-adapters/resource-adapter=somename:add(archive=somearchive.rar, transaction-support=NoTransaction)' || chr(10) ||   '/subsystem=resource-adapters/resource-adapter=${name}:activate');

recreateConnection(name) removes the specified connection and then recreates it using stored template name and properties.

For more details, see also System Procedures.

Properties Parser

The parser expects all property parameters to be in CSV-like form: user=<username>,host=<hostname>.

For now, the parser supports all features provided by StreamTokenizer. Particularly, "some,words" (quoted) will be rendered into some,words (without quotes), but the comma will not be treated as a property divider.
To add actual quote char, pass it as "\042" (quoted).

Here are some examples:

Before parsing	After parsing
host=localhost,port=25	host=localhost,port=25
importer.schemaPattern=public,importer.tableTypes="TABLE,VIEW"	importer.schemaPattern=public,importer.tableTypes=TABLE,VIEW
importer.schemaPattern=public,importer.tableTypes="\042TABLE,VIEW""\042"	importer.schemaPattern=public,importer.tableTypes="TABLE,VIEW"

System Tables

Connections holds list of all connections registered (managed with *Connection() procedures)
DataSources holds list of all data sources (managed with *DataSource() procedures)

Predefined CLI Templates

Here is the list of predefined CLI templates with appropriate translator names and properties. A connection name parameter is added as implicit name property for a template.

AdWords

Template name: adwords

Appropriate translator name: adwords or adwordsmgmt

Properties:

ClientId
ClientSecret
RefreshToken
DeveloperToken
UserAgent (default: DataVirtuality)
authCode
redirectUri
authType (default: service)
min-pool-size (default: 0)
max-pool-size (default: 20)

Amazon Athena

Template name: amazonathena

Appropriate translator name: amazonathena

Properties:

user
password (default: empty)
profile (default: empty; an AWS profile name to connect to a database)
S3OutputLocation
AwsRegion
driver (default: amazonathena)
driver-class (default: com.simba.athena.jdbc.Driver)
jdbc-properties (arbitrary extra properties)

The profile connection property available since v4.1

Amazon Aurora MySQL

Template name: aws_aurora_mysql

Appropriate translator name: mysql or mysql5

Properties:

host (default: localhost)
port (default: 3306)
db
user-name
password (default: empty)
driver (default: mysql)
driver-class (default: com.mysql.jdbc.Driver)
ssl (TRUE|FALSE)
jdbc-properties (arbitrary extra properties)
new-connection-sql (default: set SESSION sql_mode = 'ANSI')
check-valid-connection-sql (default: select 1)
min-pool-size (default: 2)
max-pool-size (default: 70)
cloudAgent (default: FALSE)

Amazon Aurora PostgreSQL

Template name: aws_aurora_postgresql

Appropriate translator name: postgresql

Properties:

host (default: localhost)
port (default: 5432)
db
user-name
password (default: empty)
driver (default: org.postgresql)
driver-class (default: org.postgresql.Driver)
ssl (TRUE|FALSE; default: FALSE)
sslmode (can be set to one of the SSL modes available in PostgreSQL such as require, verify-ca, etc. Value is used and validated only if SSL is enabled; default: require)
jdbc-properties (arbitrary extra properties)
new-connection-sql
check-valid-connection-sql (default: select 0)
min-pool-size (default: 2)
max-pool-size (default: 70)
cloudAgent (default: FALSE)

Amazon Redshift

Template name: redshift

Appropriate translator name: redshift

Properties:

host (default: localhost)
port (default: 5439)
db
user-name
password (default: empty)
driver (default: redshift)
driver-class (default: com.amazon.redshift.jdbc.Driver)
ssl (TRUE|FALSE; default: FALSE)
jdbc-properties (arbitrary extra properties)
new-connection-sql
check-valid-connection-sql (default: select 0)
min-pool-size (default: 2)
max-pool-size (default: 70)
cloudAgent (default: FALSE)

Azure

Template name: azure

Appropriate translator name: azure

Properties:

host
port (default: 1433)
db (default: empty)
jdbc-properties (default: encrypt=true;trustServerCertificate=false;hostNameInCertificate=*.database.windows.net;loginTimeout=30;)
user-name
password
storageAccountKey (for blob upload mode)
stringDelimiter
driver (default: com.microsoft.sqlserver)
driver-class (default: com.microsoft.sqlserver.jdbc.SQLServerDriver)
new-connection-sql (default: empty)
check-valid-connection-sql (default: select 1)
min-pool-size (default: 2)
max-pool-size (default: 70)
cloudAgent (default: FALSE)

Azure MSI

Template name: azure_msi

Appropriate translator name: azure

Properties:

host
port (default: 1433)
db (default: empty)
jdbc-properties (default: encrypt=true;trustServerCertificate=false;hostNameInCertificate=*.database.windows.net;loginTimeout=30;)
user-name
password
stringDelimiter
driver (default: com.microsoft.sqlserver)
driver-class (default: com.microsoft.sqlserver.jdbc.SQLServerDriver)
new-connection-sql (default: empty)
check-valid-connection-sql (default: select 1)
min-pool-size (default: 2)
max-pool-size (default: 70)
cloudAgent (default: FALSE)

Azure Table

Template name: azuretable

Appropriate translator name: azuretable

Properties:

accountName (required)
accountKey
defaultEndpointsProtocol
endpointSuffix
numberOfThreads (controls the number of threads being used to write to Azure Table Storage; default: 3; reliable values: up to 15)

blob

Template name: blob

Appropriate translator name: ufile

Properties:

accountName (required)
accountKey
sasToken
containerName (required)
defaultEndpointsProtocol
prefix
batchSize
min-pool-size (default: 0)
max-pool-size (default: 100)

Cassandra

Template name: cassandra

Appropriate translator name: cassandra

Properties:

address (required)
keyspace (required)
username
password (default: empty)
port (default: 9042)
min-pool-size (default: 0)
max-pool-size (default: 20)
cloudAgent (default: FALSE)

CData Virtuality

Template name: datavirtuality

Appropriate translator name: teiid

Properties:

host (default: localhost)
port (default: 31000; if ssl is set to TRUE: 31001)
vdb
user-name
password (default: empty)
driver (default: teiid)
driver-class (default: com.datavirtuality.dv.jdbc.Driver)
ssl (TRUE|FALSE; default: FALSE; if set to TRUE, port is automatically set to 31001, but still may be overridden using the port parameter)
jdbc-properties (arbitrary extra properties)
new-connection-sql
check-valid-connection-sql (default: select 1)
min-pool-size (default: 2)
max-pool-size (default: 70)
cloudAgent (default: FALSE)

ClickHouse

Template name: clickhouse

Appropriate translator name: clickhouse

Properties:

host (required)
port (default: 8123)
db (required)
user-name (required)
password
jdbc-properties (arbitrary extra properties)
min-pool-size (default: 2)
max-pool-size (default: 70)
cloudAgent (default: FALSE)

Databricks

Template name: databricks

Appropriate translator name: databricks

Properties:

host (required)
port (default: 443)
httpPath (required)
uid (default: token)
pwd (required)
jdbc-properties (arbitrary extra properties)
transportMode (default: http)
schemaName (default: default; relates to schema in driver optional parameters)
ssl (default: 1)
AuthMech (default: 3)
cloudAgent (default: FALSE)

Derby

Template name: derby

Appropriate translator name: derby

Properties:

host (default: localhost)
port (default: 1527)
db
user-name
password (default: empty)
driver-class (default: org.apache.derby.jdbc.ClientDriver)
jdbc-properties (arbitrary extra properties)
new-connection-sql
min-pool-size (default: 2)
max-pool-size (default: 70)
cloudAgent (default: FALSE)

EXASOL

Template name: exasol

Appropriate translator name: exasol

Properties:

host (default: localhost)
port (default: 8563)
user-name
password (default: empty)
driver (default: exasol)
driver-class (default: com.exasol.jdbc.EXADriver)
jdbc-properties (arbitrary extra properties)
new-connection-sql
check-valid-connection-sql (default: select 0)
min-pool-size (default: 2)
max-pool-size (default: 70)
cloudAgent (default: FALSE)

Excel

Template name: excel

Appropriate translator name: excel

Properties:

ParentDirectory (required)
AllowParentPaths (TRUE|FALSE; default: TRUE)
min-pool-size (default: 0)
max-pool-size (default: 20)

Facebook

Template name: facebook

Appropriate translator name: ws

Properties:

EndPoint
SecurityType
AuthUserName
AuthPassword
AccessToken
RefreshToken
ClientId
ClientSecret
AccessTokenEndpoint
ConfigFile
ConfigName
RequestTimeout
Scope
RedirectUri
min-pool-size (default: 0)
max-pool-size (default: 20)

File

Template name: file

Appropriate translator name: file

This template will be completely replaced by ufile in future. Please use ufile for new file datasources.

Properties:

ParentDirectory (FS path)
min-pool-size (default: 0)
max-pool-size (default: 20)

To read UTF-8 files, provide the translator property Encoding=utf8. If the UTF-8 file has UTF-8 BOM bytes, they are skipped automatically.

SQL

CALL SYSADMIN.createDataSource('csv', 'file', null, 'Encoding=utf8');

FTP

Template name: ftp

Appropriate translator name: ufile

Properties:

host (default: localhost)
port (default: 21)
user
remoteDirectory
secure
explicitTls
passive
decompressCompressedFiles
proxy
proxyHost
proxyPort
proxyUser
proxyPassword
serverTimeout (default: 60)
min-pool-size (default: 0)
max-pool-size (default: 100)
cloudAgent (default: FALSE)

Google Analytics

Template name: analytics

Appropriate translator name: analytics

Properties:

clientemail
clientkeypath (if it starts from '/' it means path to a key file under jboss.home system property, otherwise it must contain base64-encoded binary key data)
timeout (connection timeout in seconds; default: 0, meaning an infinite timeout)
СlientId
СlientSecret
authCode
redirectUri
authType (default: service) (deprecated)
SecurityType
RefreshToken
dataSourceName
min-pool-size (default: 0)
max-pool-size (default: 20)

Google Analytics Data

Template name: ga4

Appropriate translator name: ga4

Properties:

authType
privateKeyId
privateKey
clientEmail
СlientId
clientSecret
refreshToken
authCode
redirectUri
timeout
min-pool-size (default: 0)
max-pool-size (default: 20)

Google Analytics Data connector is available since v4.0.6

clientSecret and refreshToken support since v4.0.7

authCode and redirectUri support since v4.0.8

Google BigQuery

Type Name

bigquery

Connection Properties

Template name: bigquery

Appropriate translator name: bigquery

Properties:

projectId (required)
transformQuery (default: TRUE); (obsolete)
credentialFactory (default: com.datavirtuality.dv.core.oauth.credential.BigQueryOAuthCredentialFactory)
allowLargeResults (obsolete)
largeResultsDefaultDataset
tableMaxResults
fetchSize
refreshToken
accessToken
expirationTimeMilliseconds
region
authCode
redirectUri
user-name (required)
password (default: empty)
ClientId
ClientSecret
driver (default: bigquery)
driver-class (default: com.datavirtuality.jdbc.bq.BQDriver)
storageProjectId (default: empty)
storageUser (default: empty)
storagePassword (default: empty)
new-connection-sql
check-valid-connection-sql (default: select 1)
min-pool-size (default: 2)
max-pool-size (default: 70)
readTimeout (default: 20000, 0 for an infinite, a negative for the default)
connectTimeout (default: 20000, 0 for an infinite, a negative for the default)

readTimeout and connectTimeout connection properties available since v4.6

Here is an example:

SQL

CALL SYSADMIN.createConnection('bq2','bigquery','projectId=XXXX,user-name=XXXX@developer.gserviceaccount.com,password=$${jboss.server.config.dir}/../deployments/bigquery.p12') ;;
CALL  SYSADMIN.createDatasource('bq2','bigquery','importer.useFullSchemaName=false,importer.useCatalogName=false','supportsNativeQueries=true,uploadMode=CLOUDSTORAGE,bucketName=dv_upload_bucket,useDdl=false') ;;

It is highly recommended to specify a bucketName when creating a BigQuery data source because of the following:

a bucket creation limit that can be exceeded while performing multiple data inserts. Please refer to the Google Cloudstorage documentation for detailed information;
region mismatch between a BigQuery data set and a temporary bucket which may cause errors while inserting data. Please, refer to the Google BigQuery documentation for more details.

Translator Properties

Translator Properties Shared by All JDBC Connectors

(Properties listed in alphabetical order)

To view the full table, click the expand button in its top right corner

Name	Description	Default value
`comparisonStringConversion`	Sets a template to convert `WHERE` clauses on strings Examples SQL `comparisonStringConversion=%s -- no conversion will be applied comparisonStringConversion=binary %s -- WHERE binary someStringExpression LIKE someOtherStringExpression comparisonStringConversion=(%s COLLATE Latin1_General_CS_AS) -- WHERE (someStringExpression COLLATE Latin1_General_CS_AS LIKE someOtherStringExpression)`	MS SQL Server: `(%s COLLATE Latin1_General_CS_AS)` MySQL, SingleStore: `binary %s` Others: `%s`
`DatabaseTimeZone`	Database time zone, used when fetching date, time, or timestamp values	System default time zone
`DatabaseVersion`	Specific database version, used to fine-tune pushdown support	Automatically detected by the server through the data source JDBC driver, if possible
`forbidNonMatchingNames`	Only considered when `importer.tableNamePattern` is set. When set to `FALSE`, allows creation of tables with names that do not match the `tableNamePattern`. Tables with non-matching names will only be accessible until the server restarts	`TRUE`
`ForceQuotedIdentifiers`	If `TRUE`, all identifiers in the final query (that is being generated by a translator to be sent directly to the target DBMS) will be quoted	`TRUE`
`MaxPreparedInsertBatchSize`	Maximum size of prepared insert batch	`2048`
`OrderByStringConversion`	Sets a template to convert `ORDER BY` clauses on strings Examples SQL `OrderByStringConversion=%s -- no conversion will be applied OrderByStringConversion=(binary %s) -- ORDER BY (binary someStringExpression) OrderByStringConversion=cast((%s) as varchar(2000) ccsid 1208) -- ORDER BY cast((someStringExpression) as varchar(2000) ccsid 1208)`	DB2 for AS/400: `cast((%s) as varchar(2000) ccsid 1208)` DB2 for zOS: `cast((%s) as varchar(2000) ccsid ASCII)` MS SQL Server: `(cast(cast(%s as varchar) as varbinary)` MySQL, SingleStore: (`binary %s)` PostgreSQL: `(%s COLLATE \"C\")` Others: `%s`
`supportsConvertFromClobToString`	If `TRUE`, indicates that the translator supports the `CONVERT`/`CAST` function from `clob` to `string`	MySQL, PostgreSQL, Redshift, Greenplum, Oracle, MS SQL Server: `TRUE` Others: `FALSE`
`supportsNativeQueries`	Forces a translator to issue a `native()` system procedure that can be used to pass native queries directly to an underlying DBMS Example SQL `SELECT x.* FROM table ( CALL "dwh.native"("request" => 'SELECT query, pid, elapsed, substring FROM svl_qlog ORDER BY STARTTIME DESC LIMIT 200') ) w , ARRAYTABLE( w.tuple COLUMNS query string, pid integer , elapsed string, "substring" string ) x;` More usage examples	MySQL, PostgreSQL, Redshift, Snowflake, Oracle, MS SQL Server; Exasol, Vertica, Bigquery, SingleStore, Azure: `TRUE` Others: `FALSE`
`supportsOrderByAlias`	If `FALSE`, the CData Virtuality Server does not generate aliases in the `ORDER BY` clause. If `TRUE`, indicates that the source supports aliases in the `ORDER BY` clause	PostgreSQL: `TRUE` Others: if `OrderByStringConversion` is not set, same as supportsOrderBy if `OrderByStringConversion` is set, default for `supportsOrderByAlias` is `FALSE`
`supportsOrderByString`	If `FALSE`, the CData Virtuality Server does not push down the `ORDER BY` clause if it contains at least one expression of `string` type. If `TRUE`, indicates that the source supports strings in the `ORDER BY` clause	MS SQL Server: `FALSE` Others: same as supportsOrderBy
`TrimStrings`	If `TRUE`, trims trailing whitespace from fixed-length character strings. Please note that the CData Virtuality Server only has `string` or `varchar` type that treats trailing whitespace as meaningful	`FALSE`
`UseBindVariables`	if `TRUE`, indicates that `PreparedStatements` should be used and that literal values in the source query should be replaced with bind variables. If `FALSE`, only `LOB` values will trigger the use of `PreparedStatements`	`TRUE`
`UseCommentsInSourceQuery`	Embeds a / comment / leading comment with session/request id in the source SQL query for informational purposes	`FALSE`

The names of the translator properties are case-sensitive.

Translator Properties Specific for Google BigQuery

To view the full table, click the expand button in its top right corner

Name	Description	Default value
`replaceNullCharsWith`	String property. If set, the translator replaces all null characters in strings before executing `INSERT` / `UPDATE` with the string specified as the property value. You may specify an empty string as well	Single space
`uploadMode`	Values: `CLOUDSTORAGE` (default), `STREAMING (preview)`, `INSERTS (preview)` `CLOUDSTORAGE` mode uses a Google Cloud Storage bucket to upload data. The bucket can be configured via `bucketName` or `bucketPrefix` translator parameters. If no `bucketName` is specified, the bucket will be created; `STREAMING` (preview) mode streams data via the BigQuery API; `INSERTS` (preview) mode uses JDBC prepared statements for inserting data	`CLOUDSTORAGE`
`bucketName`	Value: bucket name Only for the default (`CLOUDSTORAGE`) upload mode. Specifies a bucket name to upload data files to. If specified, the specified bucket is assumed to exist (no creation/deletion operations performed). The region specified for the bucket must be the same as the region specified via the `region` translator property. If no `bucketName` is specified, a bucket will be created and deleted after the operation is finished.
`bucketPrefix`	Value: bucket prefix Only for the default (`CLOUDSTORAGE`) upload mode. Specifies a prefix of a bucket name to upload data files to. The name of the bucket created for uploading will start with the specified prefix. The bucket will be deleted after the operation is finished.
`folderInBucket`	Value: name of a folder in a bucket Only for the default (`CLOUDSTORAGE`) upload mode. Specifies a name of a folder in a bucket to upload data files to. If specified, the specified folder is assumed to exist (no creation/deletion operations performed) within a bukcet.
`useDdl`	Value: boolean If `TRUE`, creates and drops are performed using JDBC statements instead of API	`FALSE`
`setACLforBucket`	If set to `TRUE`, ACL roles are set for the elements of the bucket used by the data source. If set to `FALSE`, only IAM permissions are used. Thus, it should be set when uniform bucket-level access is configured for the bucket	`TRUE`

folderInBucket translator property available since v4.9

Data Source Properties

Data Source Properties Shared by All JDBC Connectors

(Properties listed in alphabetical order)

To view the full table, click the expand button in its top right corner

Name	Description	Default
`importer.autoCorrectColumnNames`	Replaces `.` in a column name with `_` as the period character is not supported by the CData Virtuality Server in column names	`TRUE`
`importer.catalog`	Database catalogs to use. Can be used if the `db` connection property is omitted. Only for Microsoft SQL Server and Snowflake: Multiple catalogs (multiple databases) usage is supported; `'%'` as a wildcard is allowed: for example, `importer.catalog="foo%"` will import `foo`, `foobar`, etc. To specify several catalog names or/and patterns, values should be comma-separated and enclosed within double quotes: `importer.catalog="catalog1,catalog2,pattern1%,pattern2%"`; To use all catalogs, the value should be '%': `importer.catalog="%"` To escape a wildcard in a catalog name, use the '\' escape character. The value should be as follows: `importer.catalog="test\_tables"`	Exasol: SQL `EXA_DB` All others: empty
`importer.defaultCatalog`	For data sources using catalog (Microsoft SQL Server and Snowflake). If `importer.catalog` property is assigned multiple values `importer.defaultCatalog` will point to the catalog where tables will be created or dropped in the source.
`importer.defaultSchema`	Only for data sources that are also supported as Analytical Storage (SQLServer, MySQL, Oracle, PostgreSQL, Redshift); When the property is correctly set, `SELECT INTO`, `CREATE`, and `DROP TABLE` commands are enabled for that data source; Must point to the original schema name in the DBMS (e.g. `importer.defaultSchema=public`); Needs specifying where tables will be created or dropped in the source DBMS; Not meant to be used with Analytical Storage data source Please note that writing into a data source is only possible if this parameter is set.	Empty
`importer.enableMetadataCache`	Turns on metadata cache for a single data source even when the global option is turned off. Together with `importer.skipMetadataLoadOnStartup=true`, it allows using materialized views after server restart when the original source is unavailable	`FALSE`
`importer.excludeProcedures`	Case-insensitive regular expression that will exclude a matching fully qualified procedure name from import	Empty
`importer.excludeSchemas`	Comma-separated list of schemas (no `%` or `?` wildcards allowed) to exclude listed schemas from import. A schema specified in `defaultSchema` or `schemaPattern` will be imported despite being listed in `excludeSchemas`. Helps to speed up metadata loading	Oracle: SQL `APEX_PUBLIC_USER, DIP, FLOWS_040100, FLOWS_020100, FLOWS_FILES, MDDATA, ORACLE_OCM, SPATIAL_CSW_ADMIN_USR, SPATIAL_WFS_ADMIN_USR, XS$NULL, BI, HR, OE, PM, IX, SH, SYS, SYSTEM, MDSYS, CTXSYS` All others: empty
`importer.excludeTables`	Case-insensitive regular expression that will exclude a matching fully qualified table name from import. Does not speed up metadata loading. Here are some examples: 1. Excluding all tables in the (source) schemas `sys` and `INFORMATION_SCHEMA`: SQL `importer.excludeTables=(.[.]sys[.].\|.[.]INFORMATION_SCHEMA[.].)` 2. Excluding all tables except the ones starting with "public.br" and "public.mk" using a negative lookahead: SQL `importer.excludeTables=(?!public\.(br\|mk)).` 3. Excluding "tablename11" from the list ["tablename1", "tablename11", "company", "companies"]: SQL `importer.excludeTables=.\.(?!\btablename1\b\|\bcompan).` ".\." will match the schema portion of the table name; "\b" is a word boundary. It is used to define the boundary between text characters and non-character text; "\btablename1\b" will only match tablename1 and not tablename11 because of the use of "\b"; "\bcompan" will match "company" and "companies" because we did not use "\b" at the end to delimit a word boundary; ".*" at the end is necessary to match the trailing characters after the name	Empty
`importer.fetchSize`	Fetch size assigned to a resultset on loading metadata	No default value
`importer.importApproximateIndexes`	If set to `TRUE`, imports approximate index information	`TRUE`
`importer.importIndexes`	If set to `TRUE`, imports index/unique key/cardinality information	`FALSE`
`importer.importKeys`	If set to `TRUE`, imports primary and foreign keys	`FALSE`
`importer.importProcedures`	If set to `TRUE`, imports procedures and procedure columns. Overloaded procedures can be imported with an additional option `useProcedureSpecificName`. Please note that it is currently not possible to import procedures which use the same name for more than one parameter (e.g. same name for `IN` and `OUT` parameters). Such procedures can be excluded from import with the parameter `excludeProcedures`. Please note that it is not always possible to import procedure result set columns due to database limitations	`FALSE` `TRUE` only for CData connector
`importer.loadColumnsTableByTable`	If set to `TRUE`, forces table by table metadata processing	`FALSE`/`TRUE` only for Netsuite and SAP Advantage Database Server
`importer.loadMetadataWithJdbc`	If set to `TRUE`, turns off all custom metadata load ways	`FALSE`
`importer.loadSourceSystemFunctions`	If set to `TRUE`, /wiki//wiki/spapaces/CV//CV/pagages/332038179/332038179 are loaded. Supported for Microsoft SQL Server and Azure	`FALSE`
`importer.procedureNamePattern`	Procedure(s) to import. If omitted, all procedures will be imported. `%` as a wildcard is allowed: for example, `importer.procedureNamePattern=foo%` will import `foo` , `foobar`, etc. W orks only in combination with `importProcedures`	Empty
`importer.quoteNameInSource`	If set to `FALSE`, directs the CData Virtuality Server to create source queries using unquoted identifiers	`TRUE`
`importer.renameDuplicateColumns`	If set to `TRUE`, renames duplicate columns caused by either mixed case collisions or `autoCorrectColumnNames` replacing `.` with `_`. The suffix `_n` where `n` is an integer will be added to make the name unique	`TRUE`
`importer.renameDuplicateTables`	If set to `TRUE`, renames duplicate tables caused by mixed case collisions. The suffix `_n` where `n` is an integer will be added to make the name unique	`TRUE`
`importer.replaceSpecSymbsInColNames`	If set to `TRUE`, replaces all special symbols (any symbols not in the `^A-Za-z0-9_` sequence) to the `_` symbol in column names of tables	`FALSE` / `TRUE` only for BigQuery
`importer.schemaPattern`	Schema(s) to import. If omitted or has "" value, all schemas will be imported. `%` as wildcard is allowed: for example, `importer.schemaPattern=foo%` will import `foo`, `foobar`, etc. To specify several schema names or/and patterns, values should be comma-separated and enclosed within double quotes: `importer.schemaPattern="schema1,schema2,pattern1%,pattern2%"`. For proper escaping of special characters depending on the type of data source, check Escaping special characters in schema names or use wildcards instead: `"[schema_name]"` can be rewritten as `"%schema%name%"`. Helps to speed up metadata loading	Empty
`importer.skipMetadataLoadOnStartup`	If set to `TRUE`, allows skipping metadata loading on server startup if there's cached metadata for the data source. . Together with `importer.enableMetadataCache=true`, it allows using materialized views after server restart when the original source is unavailable	`FALSE`
`importer.tableNamePattern`	Table(s) to import. If omitted, all tables will be imported. `%` as a wildcard is allowed: for example, `importer.tableNamePattern="foo%,boo%"` will import `foo`, `foobar`, book, bookshelf, etc	Empty
`importer.tableTypes`	Comma-separated list (without spaces) of table types to import. Available types depend on the DBMS. Usual format: `"TABLE,VIEW"`. Other typical types are `"SYSTEM TABLE"`, `"GLOBAL TEMPORARY"`, `"LOCAL TEMPORARY"`, `"ALIAS"`, `"SYNONYM"`	Empty
`importer.useCatalogName`	If set to `TRUE`, uses any non-null/non-empty catalogue name as part of the name in source, e.g. `"catalogue"."table"."column"`, and in the CData Virtuality Server runtime name if `useFullSchemaName` is `TRUE`. If set to `FALSE`, will not use the catalogue name in either the name in source or the CData Virtuality Server runtime name. Should be set to `FALSE` for sources that do not fully support a catalogue concept, but return a non-null catalogue name in their metadata - such as HSQL	`TRUE` / `FALSE` only for Hive and EXASOL
`importer.useFullSchemaName`	If set to `FALSE`, directs the importer to drop the source catalogue/schema from the CData Virtuality Server object name so that the CData Virtuality Server fully qualified name will be in the form of `<model name>.<table name>`. Please note that this may lead to objects with duplicate names when importing from multiple schemas, which results in an exception	`TRUE`
`importer.useProcedureSpecificName`	If set to `TRUE`, allows the import of overloaded procedures (which will normally result in a duplicate procedure error) by using the unique procedure specific name in the CData Virtuality Server. This option will only work with JDBC 4.0 compatible drivers that report specific names	`FALSE`
`importer.widenUnsignedTypes`	If set to `TRUE`, converts unsigned types to the next widest type. For example, SQL Server reports tinyint as an unsigned type. With this option enabled, tinyint would be imported as a short instead of a byte	`TRUE`

The names of the data source properties are case-sensitive.

Escaping wildcards in importer.catalog available since v4.0.8

Default values importer.catalog='EXA_DB' and importer.useCatalogName=FALSE available since v4.4

importer.loadSourceSystemFunctions is available since v4.6

importer.importProcedures set to TRUE by default for CData connector since v4.7

importer.defaultCatalog is available since v4.11

importer.tableNamePattern allows multiple values since v4.11

Creating/Dropping Tables and Inserting Data

In order to create/drop tables and insert data into a BigQeury data source, the importer.defaultSchema data source property should be set to the name of the target dataset. Here is an example:

SQL

CALL SYSADMIN.createConnection(name => 'bq', jbossCliTemplateName => 'bigquery', connectionOrResourceAdapterProperties => 'projectId=XXXX,user-name=XXXX-ZZZZ@developer.gserviceaccount.com,password=PATH_TO_KEY_FILE') ;;
CALL SYSADMIN.createDatasource(name => 'bq', translator => 'bigquery', modelProperties => 'importer.useCatalogName=false,importer.defaultSchema=TARGET_DATA_SET,importer.schemaPattern=SCHEMA_PATTERN_INCLUDING_TARGET_DATASET,importer.useFullSchemaName=false') ;;

Segmenting with Partitioned Tables

The BigQuery partitioning functionality is supported in both API and DDL (useDdl=TRUE) modes.

Partitioning is supported via the OPTIONS clause.

Types of Partitioning

Integer range partitioning

You can partition a table based on ranges of values in a specific INTEGER column.

Time-unit column partitioning

You can partition a table on a DATE or TIMESTAMP column in the table.

For the TIMESTAMP column, the partitions can have either hourly, daily, monthly, or yearly granularity. For DATE columns, the partitions can have daily, monthly, or yearly granularity. Partitions boundaries are based on UTC time.

Ingestion time partitioning

Valid only for DDL mode.

An ingestion-time partitioned table has a pseudocolumn named _PARTITIONTIME. The value of this column is the ingestion time for each row, truncated to the partition boundary (such as hourly or daily). Instead of using _PARTITIONTIME, you can also use _PARTITIONDATE. The _PARTITIONDATE pseudocolumn contains the UTC date corresponding to the value in the _PARTITIONTIME pseudocolumn.

You can choose hourly, daily, monthly, or yearly granularity for the partitions.

Partitioning Options

partition_expiration_days

When you create a table partitioned by ingestion time or time-unit column, you can specify a partition expiration. This setting specifies how long BigQuery keeps the data in each partition. The setting applies to all partitions in the table, but is calculated independently for each partition based on the partition time.

Accepts FLOAT values.

require_partition_filter

Specifies whether queries on this table must include a a predicate filter that filters on the partitioning column. The default value is FALSE.

partition_expression

Partition_expression is an expression that determines how to partition the table. The partition expression can contain the following values:

`partition_expression`	description	partitioning type	valid for mode
`_PARTITIONDATE`	Partition by ingestion time with daily partitions	Ingestion time	DDL
`DATE(_PARTITIONTIME)`	Equivalent to `_PARTITIONDATE`	Ingestion time	DDL
`<date_column>`	Partition by the `DATE` column with daily partitions	Time-unit column	API/DDL
`DATE( <timestamp_column> )`	Partition by the `TIMESTAMP` column with daily partitions	Time-unit column	API/DDL
`DATETIME_TRUNC(<timestamp_column>, { DAY \| HOUR \| MONTH \| YEAR })`	Partition by the `TIMESTAMP` column with the specified partitioning type	Time-unit column	API/DDL
`TIMESTAMP_TRUNC(<timestamp_column>, { DAY \| HOUR \| MONTH \| YEAR })`	Partition by the `TIMESTAMP` column with the specified partitioning type	Time-unit column	DDL
`TIMESTAMP_TRUNC(_PARTITIONTIME, { DAY \| HOUR \| MONTH \| YEAR })`	Partition by ingestion time with the specified partitioning type	Ingestion time	DDL
`DATE_TRUNC(<date_column>, { MONTH \| YEAR })`	Partition by the `DATE` column with the specified partitioning type	Time-unit column	API/DDL
`RANGE_BUCKET(<int64_column>, GENERATE_ARRAY(<start>, <end>[, <interval>]))`	Partition by an integer column with the specified range, where: `start` is the start of range partitioning, inclusive. `end` is the end of range partitioning, exclusive. `interval` is the width of each range within the partition. Default: 1	Integer range	API/DDL

Examples

1. Partitioning by the DATE column with daily granularity, keeping data for 3 days in partition and required filter that filters on the partitioning column for querying from table which means that the select query should contains where clause with DATE column:

SQL

CREATE TABLE bigquery.partition1 (i INTEGER, d DATE, s STRING) OPTIONS (partition_by 'd',partition_expiration_days '3', require_partition_filter 'true') ;;

2. Creating a table partitioned on a timestamp range with monthly granularity:

SQL

CREATE TABLE bigquery_ddl.partition_ddl_date (i integer, d timestamp, s string) OPTIONS (partition_by 'DATE_TRUNC(d, MONTH)') ;;

3. Creating a table partitioned on integer range required filter that filters on the partitioning column for querying from table which means that the SELECT query should contains where clause with the INTEGER column:

SQL

CREATE TABLE bigquery_ddl.partition_ddl_integer (i integer, d timestamp, s string) OPTIONS (partition_by 'RANGE_BUCKET(i, GENERATE_ARRAY(0, 100, 10))', require_partition_filter 'true') ;;

Partitioning options and integer-range, date-unit partitioning for API mode are available since v4.10

Clustering

Clustering is supported via the OPTIONS clause:

SQL

CREATE TABLE bigquery.cluster (i integer, d date, ts timestamp, s string, b boolean) OPTIONS (cluster_by 'i,d,ts,s') ;;

Partitioning and Clustering for Materialization of Recommended Optimizations

Materialized tables in BigQuery can be partitioned and clustered using the SYSADMIN.createCreationParam procedure.

Parameter keys:

partition_by
cluster_by
partition_expiration_days
require_partition_filter

Example

SQL

-- Add "partition_by" creation parameter
CALL "SYSADMIN.createCreationParam"(
"recOptId" => 1,
"paramKey" => 'partition_by',
"paramValue" => 'RANGE_BUCKET(i, GENERATE_ARRAY(0, 100, 10))'
);;

-- Add "require_partition_filter" creation parameter
CALL "SYSADMIN.createCreationParam"(
"recOptId" => 1,
"paramKey" => 'require_partition_filter',
"paramValue" => true
);;

For the recommended optimization with an ID equal to 1 a materialized table will be created with partitioning as in the following example:

CODE

CREATE TABLE bigquery_ddl.partition_ddl_integer (i integer, d timestamp, s string) OPTIONS (partition_by 'RANGE_BUCKET(i, GENERATE_ARRAY(0, 100, 10))', require_partition_filter 'true') ;;

Partitioning and clustering options for materializations are available since v4.11

Configuring 3-legged OAuth for BigQuery

To set up 3-legged OAuth with BigQuery, follow these steps:

1. Log in to the Developer Console for your BigQuery project.

2. Generate new OAuth2 credentials (a pair of CLIENT_ID and CLIENT_SECRET keys).

3. Substitute CLIENT_ID in the link below with your CLIENT_ID and follow this link in the browser:

https: //accounts.google.com/o/oauth2/auth?scope=https%3A%2F%2Fwww.googleapis.com%2Fauth%2Fbigquery&access_type=offline&state=security_token&response_type=code&client_id=CLIENT_ID&approval_prompt=force&redirect_uri=http%3A%2F%2Flocalhost%3A9000%2Fredirect.html%3Fto%3Dconnect/bigquery/oauth

4. Choose 'Allow' to provide access:

5. You will be redirected to a non-existent page, which is correct. Copy the page URL to obtain the code:

The OAuth code is only valid for 10 minutes

http: //localhost:9000/redirect.html?to=connect/bigquery/oauth&state=security_token&code=4/cwHserUpJwJSbKWQX-1Ua-cw5xWtdJPEDBx4nfbRmJ9F0wW2NVlLEhE8umS9bE6cdXas0IK9FZfirA02ZYDeTTw&scope=https://www.googleapis.com/auth/bigquery

6. Create a data source using the CLIENT_ID and CLIENT_SECRET obtained in step 3, AUTH_CODE obtained in step 5, and PROJECT_ID of your BigQuery project. The redirectUrl should be the same as in steps 3 and 5:

SQL

CALL SYSADMIN.createConnection('bq3legged','bigquery','authType=OAUTH,
projectId=PROJECT_ID ,
authCode=AUTH_CODE ,
redirectUri=http%3A%2F%2Flocalhost%3A9000%2Fredirect.html%3Fto%3Dconnect/bigquery/oauth,
user-name=CLIENT_ID,
password=CLIENT_SECRET') ;;
CALL SYSADMIN.createDatasource('bq3legged','bigquery','importer.schemaPattern=bq_schema_pattern,importer.defaultSchema=bq_schema,
	importer.useFullSchemaName=false,importer.useCatalogName=false','supportsNativeQueries=true') ;;

host (default: localhost)
port (default: 5433)
db (required; if absent: No database name provided)
user-name
password (default: empty)
driver (default: greenplum)
driver-class (default: org.postgresql.Driver)
ssl (TRUE|FALSE)
jdbc-properties (arbitrary extra properties)
new-connection-sql
check-valid-connection-sql (default: select 0)
min-pool-size (default: 0)
max-pool-size (default: 20)
cloudAgent (default: FALSE)

H2

Template name: h2

Appropriate translator name: h2

Properties:

file (if your DB file has name 'c:/db/stats.h2.db', provide ' c:/db/stats' here, i.e. omit the '.h2.db' extension)
driver (default: com.h2database.h2)
user-name (default: sa)
password (default: empty)
driver (default: h2)
driver-class (default: org.h2.Driver)
jdbc-properties (arbitrary extra properties)
new-connection-sql
min-pool-size (default: 2)
max-pool-size (default: 70)

Hive

Template name: hive2

Appropriate translator name: hive

Properties:

host (default: localhost)
port (default: 10000)
db (default: default)
user-name
password (default: empty)
driver (hive for hive-jdbc-1.2.1, oldhive for hive-jdbc-1.1.0; default: hive)
driver-class (default: org.apache.hive.jdbc.HiveDriver)
jdbc-properties (arbitrary extra properties)
new-connection-sql
check-valid-connection-sql (default: show databases)
min-pool-size (default: 2)
max-pool-size (default: 70)
cloudAgent (default: FALSE)

IBM DB2

Template name: db2

Appropriate translator name: db2

Properties:

host (default: localhost)
port (default: 50000)
db
user-name
password (default: empty)
driver (default: db2)
driver-class (default: com.ibm.db2.jcc.DB2Driver)
jdbc-properties (arbitrary extra properties)
new-connection-sql
check-valid-connection-sql (default: VALUES 1)
min-pool-size (default: 2)
max-pool-size (default: 70)
cloudAgent (default: FALSE)

IBM DB2 for iSeries (AS/400)

Template name: db2as400

Appropriate translator name: db2as400

Properties:

host (default: localhost)
port (default: 446)
db
user-name
password (default: empty)
driver (default: db2as400)
driver-class (default: com.ibm.as400.access.AS400JDBCDriver)
jdbc-properties (arbitrary extra properties)
new-connection-sql
check-valid-connection-sql (default: VALUES 1)
min-pool-size (default: 2)
max-pool-size (default: 70)
cloudAgent (default: FALSE)

IBM DB2 for zSeries (zOS)

Template name: db2zos

Appropriate translator name: db2zos

Properties:

host (default: localhost)
port (default: 4019)
db
user-name
password (default: empty)
driver (default: db2zos)
driver-class (default: com.ibm.db2.jcc.DB2Driver)
jdbc-properties (arbitrary extra properties)
new-connection-sql
check-valid-connection-sql (default: VALUES 1)
min-pool-size (default: 2)
max-pool-size (default: 70)
cloudAgent (default: FALSE)

IBM Informix

Template name: informix

Appropriate translator name: informix

Properties:

host (default: localhost)
port
db (required; if absent: No database name provided)
server-name (required)
user-name
password
driver (default: informix)
driver-class (default: com.informix.jdbc.IfxDriver)
jdbc-properties (arbitrary extra properties)
new-connection-sql
jta (must be set to FALSE while connecting to Informix unlogged database, i.e. database that cannot use transactions
min-pool-size (default: 2)
max-pool-size (default: 6)
cloudAgent (default: FALSE)

IBM Netezza

Template name: netezza

Appropriate translator name: netezza

Properties:

host (default: localhost)
port (default: 5480)
db (default: empty)
user-name
password
driver (default: netezza)
driver-class (default: org.netezza.Driver)
new-connection-sql
jdbc-properties (arbitrary extra properties)
min-pool-size (default: 2)
max-pool-size (default: 70)
cloudAgent (default: FALSE)

Ingres

Template name: ingres

Appropriate translator name: ingres or ingres93

Properties:

host (default: localhost)
port (default: 21071)
db
user-name
password (default: empty )
driver (default: ingres)
driver-class (default: com.ingres.jdbc.IngresDriver)
jdbc-properties (arbitrary extra properties)
new-connection-sql
min-pool-size (default: 2)
max-pool-size (default: 70)
cloudAgent (default: FALSE)

InterSystems Caché

Template name: intersystemscache

Appropriate translator name: intersystems-cache

Properties:

host (default: localhost)
port (default: 1972)
db
user-name
password (default: empty )
driver (default: cache)
driver-class (default: com.intersys.jdbc.CacheDriver)
jdbc-properties (arbitrary extra properties)
new-connection-sql
min-pool-size (default: 2)
max-pool-size (default: 70)
cloudAgent (default: FALSE)

JDBC

Template name: jdbc

Appropriate translator name: jdbc-ansi or jdbc-simple

Properties:

connection-url (JDBC connection URL)
user-name
password (default: empty )
driver
driver-class
new-connection-sql
min-pool-size (default: 2)
max-pool-size (default: 70)

kdb+

Template name: kdb

Appropriate translator name: kdb

Properties:

host (default: localhost)
port (default: 5000)
user-name
password
new-connection-sql
jdbc-properties (arbitrary extra properties)
driver (default: kdb)
driver-class (default: jdbc)
min-pool-size (default: 2)
max-pool-size (default: 70)
cloudAgent (default: FALSE)

ksqlDB

Template name: ksqldb

Appropriate translator name: ksqldb

Properties:

host (default: localhost)
port (default: )
min-pool-size (default: 2)
max-pool-size (default: 70)
cloudAgent (default: FALSE)

LDAP

Template name: ldap

Appropriate translator name: ldap

Properties:

host (default: localhost)
port (default: 389)
user-dn (format: cn=???,ou=???,dc=???)
password
timeout (in milliseconds; default: -1)
min-pool-size (default: 0)
max-pool-size (default: 20)
cloudAgent (default: FALSE)

MemSQL

See SingleStore

Microsoft Dataverse

Template name: dataverse_jdbc

Appropriate translator name: jdbc-ansi

Properties:

jdbc-properties (required)
OAuthClientId (required)
OAuthClientSecret (required)
OrganizationURL (required, set via jdbc-properties)
AuthScheme (required, set via jdbc-properties)
persist_oauthaccesstoken (set via jdbc-properties)

Microsoft SQL Server

Template name: mssql

Appropriate translator name: sqlserver

Properties:

host (default: localhost)
port (no default; for connection to a Microsoft SQL Server with dynamic port allocation configured, instanceName needs to be provided via jdbc-properties)
db
user-name
password (default: empty)
encrypt (default: FALSE; enables connection encryption and has the same possible string value as the corresponding SQL Server JDBC driver property: TRUE, FALSE, and STRICT)
driver (default: com.microsoft.sqlserver)
driver-class (default: com.microsoft.sqlserver.jdbc.SQLServerDriver)
jdbc-properties (arbitrary extra properties, e.g. for setting up Microsoft Authentication; see below for more details)
new-connection-sql (default: SET ANSI_WARNINGS OFF)
check-valid-connection-sql (default: select 1)
min-pool-size (default: 2)
max-pool-size (default: 70)
cloudAgent (default: FALSE)

Microsoft SQL Server jTDS

Template name: mssql_jtds

Appropriate translator name: sqlserver

Properties:

host (default: localhost)
port (default: 1433)
db
user-name
password (default: empty )
driver (default: jtds)
driver-class (default: net.sourceforge.jtds.jdbc.Driver)
ParentDirectory (required)
AllowParentPaths (TRUE|FALSE; default: TRUE)
jdbc-properties (arbitrary extra properties)
new-connection-sql (default: SET ANSI_WARNINGS OFF)
check-valid-connection-sql (default: select 1)
min-pool-size (default: 2)
max-pool-size (default: 70)
cloudAgent (default: FALSE)

MongoDB

Template name: mongodb

Appropriate translator name: mongodb

Properties:

server (default: localhost)
port (default: 27017)
database
login (optional)
password (optional if no login specified)
authSource (optional)
readMode
options
min-pool-size (default: 0)
max-pool-size (default: 100)
cloudAgent (default: FALSE)

MySQL

Template name: mysql

Appropriate translator name: mysql or mysql5

Properties:

host (default: localhost)
port (default: 3306)
serverId (default: empty)
db
user
password (default: empty)
driver (default: mysql)
driver-class (default: com.mysql.cj.jdbc.Driver)
ssl (TRUE|FALSE)
jdbcProperties (arbitrary extra properties; default: empty)
useL2Buffer
new-connection-sql (default: set SESSION sql_mode = 'ANSI')
check-valid-connection-sql (default: select 1)
min-pool-size (default: 2)
max-pool-size (default: 70)
cloudAgent (default: FALSE)

MySQL CDC

Template name: mysqlcdc

Appropriate translator name: mysqlcdc

Properties:

host (default: localhost)
port (default: 3306)
serverId
user
password
jdbcProperties (arbitrary extra properties)
useL2Buffer
min-pool-size (default: 2)
max-pool-size (default: 70)
cloudAgent (default: FALSE)

Neo4j

Template name: neo4j

Appropriate translator name: neo4j

Properties:

host (default: localhost)
port (default: 7687)
user-name
password
driver (default: neo4j)
driver-class (default: org.neo4j.jdbc.bolt.BoltDriver)
new-connection-sql
min-pool-size (default: 2)
max-pool-size (default: 70)
cloudAgent (default: FALSE)

ODBC

Template name: odbc

Appropriate translator name: jdbc-ansi or jdbc-simple

Properties:

DSN
user-name
password (default: empty )
charset
driver (default: odbc)
driver-class (default: sun.jdbc.odbc.JdbcOdbcDriver)
new-connection-sql
min-pool-size (default: 2)
max-pool-size (default: 70)

OLAP

Mondrian OLAP

Template name: olap_mondrian

Appropriate translator name: olap

Properties:

db-config (format: DataSource=java:foodmart;Catalog=FoodMart;Catalog=deploy/mondrian.war/WEB-INF/queries/FoodMart.xml)
user-name
password (default: empty )
driver (default: olap)
driver-class (default: mondrian.olap4j.MondrianOlap4jDriver)
jdbc-properties (arbitrary extra properties)
new-connection-sql
min-pool-size (default: 2)
max-pool-size (default: 70)

XMLA OLAP

Template name: olap_xmla

Appropriate translator name: olap

Properties:

host (default: localhost)
port (default: 8080)
path (default: /mondrian/xmla)
db-config (format: DataSource=java:foodmart;Catalog=FoodMart)
user-name
password (default: empty )
driver (default: olap)
driver-class (default: org.olap4j.driver.xmla.XmlaOlap4jDriver)
jdbc-properties (arbitrary extra properties)
new-connection-sql
min-pool-size (default: 2)
max-pool-size (default: 70)
cloudAgent (default: FALSE)

Oracle ADWC

Template name: oracleadwc

Appropriate translator name: oracle

Properties:

tns
user-name
password
driver (default: oraclev7)
driver-class (default: oracle.v7.jdbc.OracleDriver)
wallet
tns_admin (default: dvserver/standalone/deployments/wallets/wallet_<tns>/; the path should end with '/')
jdbc-properties (arbitrary extra properties)
new-connection-sql (default: alter session set nls_sort = BINARY)
min-pool-size (default: 2)
max-pool-size (default: 70)

tns_admin available since v4.0.7

Oracle Database

Template name: oracle

Appropriate translator name: oracle

Properties:

host (default: localhost)
port (default: 1521)
db
sid
service-name (db, or sid, or service-name must be specified)
user-name
password (default: empty )
driver (default: oracle)
driver-class (default: oracle.jdbc.OracleDriver)
jdbc-properties (arbitrary extra properties)
new-connection-sql (default: alter session set nls_sort = BINARY)
check-valid-connection-sql (default: SELECT 1 FROM DUAL)
validate-on-match (default: FALSE; when set to TRUE, database connection is validated every time it is checked out from the connection pool)
valid-connection-checker-class-name
background-validation (default: TRUE; when set to TRUE, database connection is validated at frequent intervals; background-validation-millis should be > 0)
background-validation-millis (default: 60000)
min-pool-size (default: 2)
max-pool-size (default: 70)
cloudAgent (default: FALSE)

Oracle NetSuite

Template name: netsuite

Appropriate translator name: netsuite

Properties:

serverHost (default: odbcserver.sandbox.netsuite.com)
port (default: 1708)
serverDataSource (default: NetSuite.com)
encrypted (default: 1)
accountID (required)
roleID (default: 3)
user-name (required)
password (default: empty)
driver (default: netsuite)
driver-class (default: com.netsuite.jdbc.openaccess.OpenAccessDriver)
jdbc-properties (arbitrary extra properties)
new-connection-sql
check-valid-connection-sql (default: select 0)
min-pool-size (default: 2)
max-pool-size (default: 70)
cloudAgent (default: FALSE)

Parquet

Template name: parquet

Appropriate translator name: parquet

Properties:

path (FS path)
min-pool-size (default: 0)
max-pool-size (default: 20)

Pavi

Template name: pavi

Appropriate translator name: pavi

Properties:

EndPoint (default: http://pav3.cdyne.com/i/PavInternationalService.svc/VerifyAddressInternational)
min-pool-size (default: 0)
max-pool-size (default: 20)

PostgreSQL

Template name: postgresql

Appropriate translator name: postgresql

Properties:

host (default: localhost)
port (default: 5432)
db
user-name
password (default: empty )
driver (default: org.postgresql)
driver-class (default: org.postgresql.Driver)
ssl (TRUE|FALSE; default: FALSE)
sslmode (can be set to one of the SSL modes available in PostgreSQL such as require, verify-ca, etc. Value is used and validated only if SSL is enabled; default: require)
jdbc-properties (arbitrary extra properties)
new-connection-sql
check-valid-connection-sql (default: select 0)
min-pool-size (default: 2)
max-pool-size (default: 70)
cloudAgent (default: FALSE)

Redis

Template name: redis

Appropriate translator name: redis

Properties:

host (default: localhost)
port (default: 1433)
db
password
min-pool-size (default: 0)
max-pool-size (default: 100)
cloudAgent (default: FALSE)

Rserve

Template name: rserve

Appropriate translator name: rserve

Properties:

host (default: localhost)
port (default: 6311)
user
password
min-pool-size (default: 0)
max-pool-size (default: 20)
cloudAgent (default: FALSE)

s3

Template name: s3

Appropriate translator name: ufile

Properties:

keyId (required)
secretKey (required)
bucketName (required)
region (optional)
prefix
grantee
permission
partSize
multipartUpload
numberOfThreads
decompressCompressedFiles
min-pool-size (default: 0)
max-pool-size (default: 100)

Salesforce

Template name: salesforce

Appropriate translator name: salesforce

Properties:

url (default: https://login.salesforce.com/services/Soap/u/34.0)
user-name
password
securityToken
connectTimeout (connection timeout in milliseconds; default: 120000)
requestTimeout (request timeout in milliseconds; default: 240000)
ClientId
ClientSecret
RefreshToken
AccessTokenEndpoint (default: https://login.salesforce.com/services/oauth2/token)
defaultTokenRefreshTimeout (default: 28800)
proxyURL (optional)
proxyUsername (optional)
proxyPassword (optional)
min-pool-size (default: 0)
max-pool-size (default: 20)

proxyUsername and proxyPassword properties are available since v4.10

Salesforce41

Template name: salesforce41

Appropriate translator name: salesforce41

Properties:

url (default: https://login.salesforce.com/services/Soap/u/45.0)
user-name
password
securityToken
connectTimeout (connection timeout in milliseconds; default: 120000)
requestTimeout (request timeout in milliseconds; default: 240000)
ClientId
ClientSecret
RefreshToken
AccessTokenEndpoint (default: https://login.salesforce.com/services/oauth2/token)
defaultTokenRefreshTimeout (default: 28800)
proxyURL (optional)
proxyUsername (optional)
proxyPassword (optional)
min-pool-size (default: 0)
max-pool-size (default: 20)

proxyUsername and proxyPassword properties are available since v4.10

SAP Advantage Database Server

Template name: sapads

Appropriate translator name: sapads

Properties:

host (default: localhost)
port (default: 6262)
catalog (required; if absent: No catalog provided)
jdbc-properties (arbitrary extra properties)
user-name
password
driver (default: sapads)
driver-class (default: com.extendedsystems.jdbc.advantage.ADSDriver)
new-connection-sql
min-pool-size (default: 2)
max-pool-size (default: 70)
cloudAgent (default: FALSE)

SAP ASE (Sybase)

Template name: sybaseiq

Appropriate translator name: sybase

Properties:

host (default: localhost)
port (default: 2638)
db (default: empty)
user-name
password
driver (default: jconnect)
driver-class (default: com.sybase.jdbc4.jdbc.SybDriver)
jdbc-properties (arbitrary extra properties)
min-pool-size (default: 2)
max-pool-size (default: 70)
cloudAgent (default: FALSE)

SAP HANA

Template name: hana

Appropriate translator name: hana

Properties:

host (default: localhost)
port (default: 39013)
db (default: empty)
user-name
password
driver (default: hana)
driver-class (default: com.sap.db.jdbc.Driver)
jdbc-properties (arbitrary extra properties)
new-connection-sql
min-pool-size (default: 2)
max-pool-size (default: 70)
cloudAgent (default: FALSE)

jdbc-properties connection property is available since v4.0.7

SAS

Template name: sas

Appropriate translator name: jdbc-ansi

Properties:

user-name (mandatory)
password (optional)
host (default: localhost)
port (default: 5010)
app-name (required; if absent: No app name provided)
jdbc-properties (optional)
driver (default: sas)
driver-class (default: com.sas.net.sharenet.ShareNetDriver)
new-connection-sql (optional)
min-pool-size (default: 2)
max-pool-size (default: 70)
cloudAgent (default: FALSE)

SCP

Template name: scp

Appropriate translator name: ufile

Properties:

host (default: localhost)
port (default: 22)
user
remoteDirectory
privatekey
decompressCompressedFiles
password
min-pool-size (default: 0)
max-pool-size (default: 100)
cloudAgent (default: FALSE)

SFTP

Template name: sftp

Appropriate translator name: ufile

Properties:

host (default: localhost)
port (default: 22)
user
remoteDirectory
privatekey
decompressCompressedFiles
proxy
proxyHost
proxyPort
proxyPassword
config (default: StrictHostKeyChecking=no; used for SSH configuration)
min-pool-size (default: 0)
max-pool-size (default: 100)
cloudAgent (default: FALSE)

config connection property available since v4.0.8

SingleStore (formerly MemSQL)

Template name: memsql

Appropriate translator name: memsql

Properties:

host (default: localhost)
port (default: 3306)
db
user-name
password (default: empty )
driver (default: memsql)
driver-class (default: com.mysql.cj.jdbc.Driver)
ssl (TRUE|FALSE)
jdbc-properties (arbitrary extra properties)
new-connection-sql (default: set SESSION sql_mode = 'ANSI')
check-valid-connection-sql (default: select 1)
min-pool-size (default: 2)
max-pool-size (default: 70)
cloudAgent (default: FALSE)

Snowflake

Template name: snowflake

Appropriate translator name: snowflake

Properties:

host (default: localhost)
db (required; if absent: No database name provided)
schema
warehouse (required; if absent: No warehouse name provided; for multiple warehouses see the roleMapping translator property)
CLIENT_METADATA_REQUEST_USE_CONNECTION_CTX (default: TRUE)
application (default: DataVirtuality_DV)
user-name
password
driver (default: snowflake)
driver-class (default: net.snowflake.client.jdbc.SnowflakeDriver)
jdbc-properties (arbitrary extra properties)
new-connection-sql
check-valid-connection-sql (default: select 0)
min-pool-size (default: 2)
max-pool-size (default: 70)
cloudAgent (default: FALSE)

Teradata

Template name: teradata

Appropriate translator name: teradata

Properties:

host (default: localhost)
db (required)
user-name
password (default: empty )
driver (default: teradata)
driver-class (default: com.teradata.jdbc.TeraDriver)
jdbc-properties (arbitrary extra properties)
new-connection-sql
check-valid-connection-sql (default: select 0)
min-pool-size (default: 2)
max-pool-size (default: 70)
cloudAgent (default: FALSE)

Trino

Template name: trino
Appropriate translator name: trino
Properties:
`host`(default: `localhost`)
`port` (default: `8080`)
`catalog` (default: empty)
`db`(default: empty)
`jdbc-properties` (arbitrary extra properties)
`user-name`(default: empty)
`password`(default: empty)
`cloudAgent` (default: `FALSE`)

Trino connector available since v4.7

Twitter

Template name: twitter

Appropriate translator name: ws

Properties:

EndPoint
SecurityType
AuthUserName
AuthPassword
AccessToken
RefreshToken
ClientId
ClientSecret
AccessTokenEndpoint
ConfigFile
ConfigName
RequestTimeout
min-pool-size (default: 2)
max-pool-size (default: 70)

ufile

Template name: ufile

Apprpriate translator name: u file

Properties:

ParentDirectory (required)
AllowParentPaths (default: TRUE)
decompressCompressedFiles (default: FALSE)
min-pool-size (default: 0)
max-pool-size (default: 20)

Vertica

Template name: vertica

Appropriate translator name: vertica

Properties:

host (default: localhost)
port (default: 5433)
db (required; if absent: No database name provided)
user-name
password (default: empty )
driver (default: vertica)
driver-class (default: com.vertica.jdbc.Driver)
ssl (TRUE|FALSE)
jdbc-properties (arbitrary extra properties)
new-connection-sql
new-connection-sql
check-valid-connection-sql (default: select 0)
min-pool-size (default: 2)
max-pool-size (default: 70)
cloudAgent (default: FALSE)

WS

Template name: ws

Appropriate translator name: ws

Properties:

EndPoint (URL for HTTP, Service Endpoint for SOAP)
SecurityType (web service security used; possible values: None, HTTPBasic, WSSecurity, OAuth2,NTLM; optional)
AuthUserName (optional)
AuthPassword (optional)
AuthDomain (optional)
AuthHost (optional)
AccessToken
RefreshToken
ClientId
ClientSecret
scope (used in case of OAuth2 ClientId/ClientSecret authorization to obtain an access token)
cloudAgent (default: FALSE)
cloudAgentWsHost (target host name as seen from inside the agent network)
cloudAgentWsPath (optional)(specific endpoint path to mount the data source to)
AccessTokenEndpoint
TranslatorName
ConfigFile (CXF client configuration file or URL; optional)
ConfigName (local name of the QName of the port configuration to use with this connection; default namespace URI: http://teiid.org; optional)
ConnectTimeout (optional)
RequestTimeout (optional)
redirectUri
authCode
decompressCompressedFiles (if set to TRUE, a gzipped response of invokeHttp will be decompressed on the fly; default: FALSE)
expirationTimeMilliseconds
min-pool-size (default: 0)
max-pool-size (default: 20)
TenantId

NTLM security type available since v4.1

AuthDomain and AuthHost connection properties available since v4.1

cloudAgentWsHost and cloudAgentWsPath connection properties available since v4.7

scope parameter is available since v4.7

Do not create CLI templates with names containing the minus sign because it is used as a divider when generating internal connection names.

System Stored Procedures: Short Description

Properties Parser

System Tables

Predefined CLI Templates

AdWords

Amazon Athena

Amazon Aurora MySQL

Amazon Aurora PostgreSQL

Amazon Redshift

Azure

Azure MSI

Azure Table

blob

Cassandra

CData Virtuality

ClickHouse

Databricks

Derby

EXASOL

Excel

Facebook

File

FTP

Google Analytics

Google Analytics Data

Google BigQuery

Connection Properties

Translator Properties

[data-colorid=sjm4dwau1n]{color:#333333} html[data-color-mode=dark] [data-colorid=sjm4dwau1n]{color:#cccccc}Translator Properties Shared by All JDBC Connectors

Examples

Examples

Example

Translator Properties Specific for Google BigQuery

Data Source Properties

Creating/Dropping Tables and Inserting Data

Segmenting with Partitioned Tables

Types of Partitioning

Integer range partitioning

Time-unit column partitioning

Ingestion time partitioning

Partitioning Options

partition_expiration_days

require_partition_filter

partition_expression

Examples

Clustering

Partitioning and Clustering for Materialization of Recommended Optimizations

Example

Configuring 3-legged OAuth for BigQuery

See Also

Greenplum

H2

Hive

IBM DB2

IBM DB2 for iSeries (AS/400)

IBM DB2 for zSeries (zOS)

IBM Informix

IBM Netezza

Ingres

InterSystems Caché

JDBC

kdb+

ksqlDB

LDAP

MemSQL

Microsoft Dataverse

Microsoft SQL Server

Microsoft SQL Server jTDS

MongoDB

MySQL

MySQL CDC

Neo4j

ODBC

OLAP

Mondrian OLAP

XMLA OLAP

Oracle ADWC

Oracle Database

Oracle NetSuite

Parquet

Translator Properties Shared by All JDBC Connectors