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 minus sign (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:

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.

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:

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

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)

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)

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;

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

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="schema1,schema2,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.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	Set to TRUE to force 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, data source-specific functions 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 Data 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% will import foo, foobar, 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

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:

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:

Examples

Partitioning by 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.

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

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

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

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 INTEGER column:

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:

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

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:

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

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:

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') ;;

See Also

Partitioning Tables in BigQuery to learn how to convert a non-partitioned table into a partitioned one for improved query performance

Greenplum

Template name: greenplum

Appropriate translator name: jdbc-ansi

Properties:

• 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)
• 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

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.