Lead your team forward
OCT 24 / 9AM ET Register nowData Sources Configuration
This article describes the properties used to configure data sources in Data Processing Engine (DPE).
To modify the default configuration, add or edit properties in the Configuration Service or in the dpe/etc/application.properties
file.
For an overview of recommended database versions, see ROOT:supported-third-party-components.adoc, section Databases. |
JDBC data source plugin default configuration
These properties define default JDBC connection configuration for all data sources. You can also configure each driver separately, in which case driver-specific properties override the values provided here for that particular driver.
Each property follows the pattern plugin.jdbcdatasource.ataccama.one.connections.{propertyName}
.
Property | Data type | Description |
---|---|---|
|
Boolean |
If set to true, connection pooling is applied. If the property is not set, the default value is If you use data source throttling by specifying |
|
Number |
Defines for how long the pool manager or, in case connection pooling is not enabled, a data source, lets the client’s connection request wait before a timeout exception is thrown. This typically happens when all available connections are already in use and no new connections can be added due to other limits. Expressed in milliseconds. The default value is |
|
Number |
Specifies for how long a connection can remain idle in the connection pool before it is closed. Applicable if connection pooling is enabled. Expressed in milliseconds. Default value: |
|
Number |
Determines for how long a connection can remain in the pool. The connections that are currently in use are not closed. Expressed in milliseconds. Applicable if connection pooling is enabled. The period should be several seconds shorter compared to the database limit. Default value: |
|
Number |
The minimum number of idle connections in the pool. Applicable if connection pooling is enabled. If the property is not set, the value corresponds to the value of the property maximum-pool-size. Default value: |
|
Number |
The maximum number of connections in the connection pool. This includes both active and idle connections. Applicable if connection pooling is enabled. When the maximum number of connections is reached, further connection requests are blocked. If the property is not set, the number of allowed connections is Default value: |
|
String |
Specifies the maximum waiting time for row count operation during preprocessing. Default value: |
JDBC data source driver configuration
The following properties need to be set when configuring a JDBC Data Source Plugin.
You can use these properties as a template for adding custom data sources. The property names follow this pattern: plugin.jdbcdatasource.ataccama.one.driver.{driverId}.{propertyName}
.
The identifier of the driver (driverId
) needs to be unique and should match the identifier of the database, for example, postgresql
.
It is also possible to add custom JDBC properties.
To do this, use the pattern For example, if you are working with an Oracle database with the default configuration and want to define how many rows are prefetched, the following property should be provided:
Or, if you need to log in as a system user in an Oracle database, the following property should be added:
|
Examples of configuration with default values are provided for the following data sources: Oracle, PostgreSQL, Amazon Aurora PostgreSQL, MySQL, Amazon Aurora MySQL, MSSQL, Azure Synapse Analytics, H2, MariaDB, Teradata, Amazon Redshift, Snowflake, Apache Cassandra, Big Query, and IBM Netezza. The H2 data source should only be used for testing purposes.
The properties related to connection pooling are driver-specific values of connection pooling properties that are defined for all drivers.
The <driverId>.driver-class
property must be added if there are multiple drivers found in the driver’s classpath (<driverId>.driver-class-path
).
Property | Data type | Description | ||
---|---|---|---|---|
|
String |
The name of the data source that is displayed in ONE Web Application. |
||
|
String |
Refers to the pattern of a valid JDBC connection string.
This serves as a template for users and should use placeholders to indicate what users need to modify.
For example, for PostgreSQL, the value is |
||
|
String |
A regular expression for a schema name from connection string in its first group. This is especially useful for data sources where all schemas are listed regardless of the database specified in the connection string, such as Teradata ( |
||
|
String |
The classpath of the driver, for example, To select multiple files, use a semicolon ( |
||
|
String |
A path for additional files that are loaded to the classpath of this driver. Suitable for adding additional libraries or license files. |
||
|
String |
The driver’s class name. Must be added in the following cases:
|
||
|
String |
The customized implementation class of the driver’s provider. If the property is not provided, the default provider is used. Omitted if configuring a custom JDBC driver. |
||
|
Boolean |
If set to |
||
|
Number |
Defines for how long the pool manager or, in case connection pooling is not enabled, a data source, lets the client’s connection request wait before a timeout exception is thrown. This typically happens when all available connections are already in use and no new connections can be added due to other limits. Expressed in milliseconds. The default value is |
||
|
Number |
Specifies for how long a connection can remain idle in the connection pool before it is closed. Applicable if connection pooling is enabled. Expressed in milliseconds. Default value: |
||
|
Number |
Determines for how long a connection can remain in the pool. The connections that are currently in use are not closed. Expressed in milliseconds. Applicable if connection pooling is enabled. The period should be several seconds shorter compared to the database limit. Default value: |
||
|
Number |
The minimum number of idle connections in the pool. Applicable if connection pooling is enabled. If the property is not set, the value corresponds to the value of the property Default value: |
||
|
Number |
The maximum number of connections in the connection pool. This includes both active and idle connections. Applicable if connection pooling is enabled. When the maximum number of connections is reached, further connection requests are blocked. If the property is not set, the number of allowed connections is Default value: |
||
|
String |
A query pattern for displaying all records, for example, Allowed placeholders: |
||
|
String |
A query pattern for displaying a preview of records, for example, Allowed placeholders: If the property is not set, the pattern |
||
|
String |
A DSL query pattern for loading data source preview. The property is mainly used for optimization purposes. By default, the pattern is as follows: Allowed placeholders:
|
||
|
String |
A DSL query pattern for importing metadata. The property is mainly used for optimization purposes. By default, the pattern is as follows: Allowed placeholders:
|
||
|
String |
A query pattern for counting the number of rows in a catalog item, for example, Allowed placeholder: |
||
|
String |
A row number column query for processing of relational databases in Spark environment. |
||
|
String |
A query pattern for retrieving a sample of records from a catalog item, for example, Allowed placeholders: |
||
|
String |
The punctuation mark used to delimit identifiers in queries, typically a backtick ( |
||
|
String |
Restricts which table types can be queried in the data source. |
||
|
Number |
If set, metadata is loaded in bulk for all tables available in a schema or database provided that the number of tables selected for import exceeds the threshold specified. Otherwise, the bulk import strategy is applied only when users attempt to load the whole schema (or, in data sources that do not use schemas, the whole database). In other words, if the property is omitted or not supported for the given data source, tables are processed and queried one by one. The property is particularly useful when working with slow databases.
For example, if querying metadata for a single table takes 2 minutes and for the full schema 5 minutes, setting the threshold to In the current version, the property can be used for the following data sources: MSSQL, Oracle, PostgreSQL, Amazon Aurora PostgreSQL. |
||
|
Boolean |
Disables the JDBC driver. To do so, set the property to true. For example:
|
||
|
String |
Customizes the query to determine the location root. The default value is data source dependent. |
||
|
String |
If set, any entity’s name matching this pattern is excluded from the job result. This property is not used for the following data sources which do not support schemas: MySQL, Amazon Aurora MySQL, MariaDB. If you specify any value here, this would result in an empty job result.
|
||
|
String |
If set, only entity’s name matching this pattern is included in the job result. This property is not used for the following data sources which do not support schemas: MySQL, Amazon Aurora MySQL, MariaDB. If you specify any value here, this would result in an empty job result.
|
||
|
String |
If set, any entity’s name matching this pattern is excluded from the job result.
|
||
|
String |
If set, only entity’s name matching this pattern is included in the job result.
|
||
|
Boolean |
If set to Default value: |
Shared configuration
The following properties are common for all configured JDBC drivers.
Property | Data type | Description | ||
---|---|---|---|---|
|
String |
Determines which table types are shown in data sources. If no value is provided or the property is removed or commented out, all table types are displayed (not recommended). This property applies to all available data sources.
Default value: |
Oracle data source configuration
The following properties are used only for Oracle databases.
Property | Data type | Description |
---|---|---|
|
String |
Restricts which schemas can be queried in the data source. |
|
Boolean |
If set to Default value: |
Oracle JDBC data source configuration
plugin.jdbcdatasource.ataccama.one.driver.oracle.name=Oracle
plugin.jdbcdatasource.ataccama.one.driver.oracle.connection-pattern=jdbc:oracle:thin:@<hostname>:<port>:<sid>
plugin.jdbcdatasource.ataccama.one.driver.oracle.driver-class-path = ojdbc*.jar
plugin.jdbcdatasource.ataccama.one.driver.oracle.additional-classpath-files = oracleLibs/*;
plugin.jdbcdatasource.ataccama.one.driver.oracle.provider-class = com.ataccama.dpe.plugin.dataconnect.jdbc.provider.oracle.OracleDataSourceClientProvider
plugin.jdbcdatasource.ataccama.one.driver.oracle.pooling-enabled = true
plugin.jdbcdatasource.ataccama.one.driver.oracle.connection-timeout = 20000
plugin.jdbcdatasource.ataccama.one.driver.oracle.idle-timeout = 300000
plugin.jdbcdatasource.ataccama.one.driver.oracle.max-lifetime = 900000
plugin.jdbcdatasource.ataccama.one.driver.oracle.minimum-idle = 1
plugin.jdbcdatasource.ataccama.one.driver.oracle.maximum-pool-size = 5
plugin.jdbcdatasource.ataccama.one.driver.oracle.full-select-query-pattern = SELECT {columns} FROM {table}
plugin.jdbcdatasource.ataccama.one.driver.oracle.preview-query-pattern = SELECT {columns} FROM {table} WHERE ROWNUM <= {previewLimit}
plugin.jdbcdatasource.ataccama.one.driver.oracle.dsl-query-preview-query-pattern = SELECT * FROM ({dslQuery}) WHERE ROWNUM <= {previewLimit}
plugin.jdbcdatasource.ataccama.one.driver.oracle.dsl-query-import-metadata-query-pattern = SELECT * FROM ({dslQuery}) WHERE ROWNUM = 0
plugin.jdbcdatasource.ataccama.one.driver.oracle.row-count-query-pattern = SELECT COUNT(*) FROM {table}
#you can replace the preceding query with the following query, which might be more effective provided your schema has up to date statistics
#plugin.jdbcdatasource.ataccama.one.driver.oracle.row-count-query-pattern = SELECT NVL(NUM_ROWS,0) from ALL_TABLES WHERE OWNER = '{schema}' AND TABLE_NAME = '{simpleTable}'
plugin.jdbcdatasource.ataccama.one.driver.oracle.sampling-query-pattern = SELECT {columns} FROM {table} SAMPLE ({percentageLimit})
plugin.jdbcdatasource.ataccama.one.driver.oracle.disallowed-indexes-table-types=SYNONYM,VIEW
plugin.jdbcdatasource.ataccama.one.driver.oracle.disallowed-indexes-schema-names=SYS
plugin.jdbcdatasource.ataccama.one.driver.oracle.bulk-import-table-count-threshold=5
plugin.jdbcdatasource.ataccama.one.driver.oracle.skip-oracle-maintained-tables=true
plugin.jdbcdatasource.ataccama.one.driver.oracle.location-query=select nvl(nullif(service_name, 'SYS$USERS'), nvl(instance_name, sid)) from (SELECT distinct sys_context('userenv', 'service_name') service_name, sys_context('userenv', 'instance_name') instance_name, sys_context('USERENV', 'SID') sid FROM DUAL)
plugin.jdbcdatasource.ataccama.one.driver.oracle.row-number-column=ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) as ____rno
PostgreSQL data source configuration
PostgreSQL JDBC data source configuration
plugin.jdbcdatasource.ataccama.one.driver.postgresql.name=PostgreSQL
plugin.jdbcdatasource.ataccama.one.driver.postgresql.connection-pattern=jdbc:postgresql://<hostname>:<port>/<database>
plugin.jdbcdatasource.ataccama.one.driver.postgresql.driver-class-path = postgresql-*.jar
plugin.jdbcdatasource.ataccama.one.driver.postgresql.provider-class = com.ataccama.dpe.plugin.dataconnect.jdbc.provider.postgresql.PostgreSQLDataSourceClientProvider
plugin.jdbcdatasource.ataccama.one.driver.postgresql.pooling-enabled = true
plugin.jdbcdatasource.ataccama.one.driver.postgresql.connection-timeout = 20000
plugin.jdbcdatasource.ataccama.one.driver.postgresql.idle-timeout = 300000
plugin.jdbcdatasource.ataccama.one.driver.postgresql.max-lifetime = 900000
plugin.jdbcdatasource.ataccama.one.driver.postgresql.minimum-idle = 1
plugin.jdbcdatasource.ataccama.one.driver.postgresql.maximum-pool-size = 5
plugin.jdbcdatasource.ataccama.one.driver.postgresql.full-select-query-pattern = SELECT {columns} FROM {table}
plugin.jdbcdatasource.ataccama.one.driver.postgresql.preview-query-pattern = SELECT {columns} FROM {table} LIMIT {previewLimit}
plugin.jdbcdatasource.ataccama.one.driver.postgresql.dsl-query-preview-query-pattern = SELECT * FROM ({dslQuery}) dslQuery LIMIT {previewLimit}
plugin.jdbcdatasource.ataccama.one.driver.postgresql.dsl-query-import-metadata-query-pattern = SELECT * FROM ({dslQuery}) dslQuery LIMIT 0
plugin.jdbcdatasource.ataccama.one.driver.postgresql.row-count-query-pattern = SELECT COUNT(*) FROM {table}
plugin.jdbcdatasource.ataccama.one.driver.postgresql.sampling-query-pattern = SELECT {columns} FROM {table} WHERE RANDOM() < {percentageLimit} limit {limit}
plugin.jdbcdatasource.ataccama.one.driver.postgresql.query-quotation-mark =\"
plugin.jdbcdatasource.ataccama.one.driver.postgresql.disallowed-indexes-table-types=SYNONYM
plugin.jdbcdatasource.ataccama.one.driver.postgresql.bulk-import-table-count-threshold=30
Amazon Aurora PostgreSQL data source configuration
Amazon Aurora PostgreSQL JDBC data source configuration
plugin.jdbcdatasource.ataccama.one.driver.aurora-postgresql.name=Amazon Aurora PostgreSQL
plugin.jdbcdatasource.ataccama.one.driver.aurora-postgresql.connection-pattern=jdbc:postgresql://<hostname>:<port>/<database>
plugin.jdbcdatasource.ataccama.one.driver.aurora-postgresql.driver-class-path = postgresql-*.jar
plugin.jdbcdatasource.ataccama.one.driver.aurora-postgresql.provider-class = com.ataccama.dpe.plugin.dataconnect.jdbc.provider.postgresql.PostgreSQLDataSourceClientProvider
plugin.jdbcdatasource.ataccama.one.driver.aurora-postgresql.pooling-enabled = true
plugin.jdbcdatasource.ataccama.one.driver.aurora-postgresql.connection-timeout = 20000
plugin.jdbcdatasource.ataccama.one.driver.aurora-postgresql.idle-timeout = 300000
plugin.jdbcdatasource.ataccama.one.driver.aurora-postgresql.max-lifetime = 900000
plugin.jdbcdatasource.ataccama.one.driver.aurora-postgresql.minimum-idle = 1
plugin.jdbcdatasource.ataccama.one.driver.aurora-postgresql.maximum-pool-size = 5
plugin.jdbcdatasource.ataccama.one.driver.aurora-postgresql.full-select-query-pattern = SELECT {columns} FROM {table}
plugin.jdbcdatasource.ataccama.one.driver.aurora-postgresql.preview-query-pattern = SELECT {columns} FROM {table} LIMIT {previewLimit}
plugin.jdbcdatasource.ataccama.one.driver.aurora-postgresql.dsl-query-preview-query-pattern = SELECT * FROM ({dslQuery}) dslQuery LIMIT {previewLimit}
plugin.jdbcdatasource.ataccama.one.driver.aurora-postgresql.dsl-query-import-metadata-query-pattern = SELECT * FROM ({dslQuery}) dslQuery LIMIT 0
plugin.jdbcdatasource.ataccama.one.driver.aurora-postgresql.row-count-query-pattern = SELECT COUNT(*) FROM {table}
plugin.jdbcdatasource.ataccama.one.driver.aurora-postgresql.sampling-query-pattern = SELECT {columns} FROM {table} WHERE RANDOM() < {percentageLimit} limit {limit}
plugin.jdbcdatasource.ataccama.one.driver.aurora-postgresql.query-quotation-mark =\"
plugin.jdbcdatasource.ataccama.one.driver.aurora-postgresql.disallowed-indexes-table-types=SYNONYM
plugin.jdbcdatasource.ataccama.one.driver.aurora-postgresql.bulk-import-table-count-threshold=30
MySQL data source configuration
MySQL JDBC data source configuration
plugin.jdbcdatasource.ataccama.one.driver.mysql.disabled=false
plugin.jdbcdatasource.ataccama.one.driver.mysql.name=MySQL
plugin.jdbcdatasource.ataccama.one.driver.mysql.connection-pattern=jdbc:mysql://<hostname>:<port>/<database>
plugin.jdbcdatasource.ataccama.one.driver.mysql.driver-class-path = mysql-connector-java-8*.jar
plugin.jdbcdatasource.ataccama.one.driver.mysql.provider-class = com.ataccama.dpe.plugin.dataconnect.jdbc.provider.mysql.MySQLDataSourceClientProvider
plugin.jdbcdatasource.ataccama.one.driver.mysql.pooling-enabled = true
plugin.jdbcdatasource.ataccama.one.driver.mysql.connection-timeout = 20000
plugin.jdbcdatasource.ataccama.one.driver.mysql.idle-timeout = 300000
plugin.jdbcdatasource.ataccama.one.driver.mysql.max-lifetime = 900000
plugin.jdbcdatasource.ataccama.one.driver.mysql.minimum-idle = 1
plugin.jdbcdatasource.ataccama.one.driver.mysql.maximum-pool-size = 5
plugin.jdbcdatasource.ataccama.one.driver.mysql.full-select-query-pattern = SELECT {columns} FROM {table}
plugin.jdbcdatasource.ataccama.one.driver.mysql.preview-query-pattern = SELECT {columns} FROM {table} LIMIT {previewLimit}
plugin.jdbcdatasource.ataccama.one.driver.mysql.dsl-query-preview-query-pattern = SELECT * FROM ({dslQuery}) dslQuery LIMIT {previewLimit}
plugin.jdbcdatasource.ataccama.one.driver.mysql.dsl-query-import-metadata-query-pattern = SELECT * FROM ({dslQuery}) dslQuery LIMIT 0
plugin.jdbcdatasource.ataccama.one.driver.mysql.row-count-query-pattern = SELECT COUNT(*) FROM {table}
plugin.jdbcdatasource.ataccama.one.driver.mysql.sampling-query-pattern = SELECT {columns} FROM {table} WHERE RAND() < {percentageLimit};
plugin.jdbcdatasource.ataccama.one.driver.mysql.query-quotation-mark =`
plugin.jdbcdatasource.ataccama.one.driver.mysql.disallowed-indexes-table-types=SYNONYM
plugin.jdbcdatasource.ataccama.one.driver.mysql.properties.characterEncoding=utf-8
Amazon Aurora MySQL data source configuration
Amazon Aurora MySQL JDBC data source configuration
plugin.jdbcdatasource.ataccama.one.driver.aurora-mysql.disabled=false
plugin.jdbcdatasource.ataccama.one.driver.aurora-mysql.name=Amazon Aurora MySQL
plugin.jdbcdatasource.ataccama.one.driver.aurora-mysql.connection-pattern=jdbc:mysql://<hostname>:<port>/<database>
plugin.jdbcdatasource.ataccama.one.driver.aurora-mysql.driver-class-path = mysql-connector-java-8*.jar
plugin.jdbcdatasource.ataccama.one.driver.aurora-mysql.provider-class = com.ataccama.dpe.plugin.dataconnect.jdbc.provider.mysql.MySQLDataSourceClientProvider
plugin.jdbcdatasource.ataccama.one.driver.aurora-mysql.pooling-enabled = true
plugin.jdbcdatasource.ataccama.one.driver.aurora-mysql.connection-timeout = 20000
plugin.jdbcdatasource.ataccama.one.driver.aurora-mysql.idle-timeout = 300000
plugin.jdbcdatasource.ataccama.one.driver.aurora-mysql.max-lifetime = 900000
plugin.jdbcdatasource.ataccama.one.driver.aurora-mysql.minimum-idle = 1
plugin.jdbcdatasource.ataccama.one.driver.aurora-mysql.maximum-pool-size = 5
plugin.jdbcdatasource.ataccama.one.driver.aurora-mysql.full-select-query-pattern = SELECT {columns} FROM {table}
plugin.jdbcdatasource.ataccama.one.driver.aurora-mysql.preview-query-pattern = SELECT {columns} FROM {table} LIMIT {previewLimit}
plugin.jdbcdatasource.ataccama.one.driver.aurora-mysql.dsl-query-preview-query-pattern = SELECT * FROM ({dslQuery}) dslQuery LIMIT {previewLimit}
plugin.jdbcdatasource.ataccama.one.driver.aurora-mysql.dsl-query-import-metadata-query-pattern = SELECT * FROM ({dslQuery}) dslQuery LIMIT 0
plugin.jdbcdatasource.ataccama.one.driver.aurora-mysql.row-count-query-pattern = SELECT COUNT(*) FROM {table}
plugin.jdbcdatasource.ataccama.one.driver.aurora-mysql.sampling-query-pattern = SELECT {columns} FROM {table} WHERE RAND() < {percentageLimit};
plugin.jdbcdatasource.ataccama.one.driver.aurora-mysql.query-quotation-mark =`
plugin.jdbcdatasource.ataccama.one.driver.aurora-mysql.disallowed-indexes-table-types=SYNONYM
plugin.jdbcdatasource.ataccama.one.driver.aurora-mysql.properties.characterEncoding=utf-8
MSSQL data source configuration
MSSQL JDBC data source configuration
plugin.jdbcdatasource.ataccama.one.driver.mssql.name=MSSQL Server
plugin.jdbcdatasource.ataccama.one.driver.mssql.connection-pattern=jdbc:sqlserver://<hostname>:<port>;databaseName=<database>
plugin.jdbcdatasource.ataccama.one.driver.mssql.driver-class-path = mssql-jdbc*.jar
plugin.jdbcdatasource.ataccama.one.driver.mssql.additional-classpath-files = mssqlLibs/*;
plugin.jdbcdatasource.ataccama.one.driver.mssql.provider-class = com.ataccama.dpe.plugin.dataconnect.jdbc.provider.mssql.MSSQLDataSourceClientProvider
plugin.jdbcdatasource.ataccama.one.driver.mssql.pooling-enabled = true
plugin.jdbcdatasource.ataccama.one.driver.mssql.connection-timeout = 20000
plugin.jdbcdatasource.ataccama.one.driver.mssql.idle-timeout = 300000
plugin.jdbcdatasource.ataccama.one.driver.mssql.max-lifetime = 900000
plugin.jdbcdatasource.ataccama.one.driver.mssql.minimum-idle = 1
plugin.jdbcdatasource.ataccama.one.driver.mssql.maximum-pool-size = 5
plugin.jdbcdatasource.ataccama.one.driver.mssql.full-select-query-pattern = SELECT {columns} FROM {table}
plugin.jdbcdatasource.ataccama.one.driver.mssql.preview-query-pattern = SELECT TOP {previewLimit} {columns} FROM {table}
plugin.jdbcdatasource.ataccama.one.driver.mssql.dsl-query-preview-query-pattern = SELECT TOP {previewLimit} * FROM ({dslQuery}) AS dslQuery
plugin.jdbcdatasource.ataccama.one.driver.mssql.dsl-query-import-metadata-query-pattern = SELECT TOP 0 * FROM ({dslQuery}) AS dslQuery
plugin.jdbcdatasource.ataccama.one.driver.mssql.row-count-query-pattern = SELECT COUNT(*) FROM {table}
plugin.jdbcdatasource.ataccama.one.driver.mssql.sampling-query-pattern=IF (SELECT TABLE_TYPE FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = '{simpleTable}' AND TABLE_SCHEMA = '{schema}') = 'BASE TABLE' \ EXEC('SELECT TOP {limit} {columns} FROM {table} TABLESAMPLE (CASE WHEN {percentageLimit}*110 > 100 THEN 100 ELSE {percentageLimit}*110 END PERCENT)') \ ELSE SELECT TOP {limit} {columns} FROM (select t1.*, RAND(CHECKSUM(NEWID())) as ____rnd from {table} as t1) as t1 where ____rnd < {percentageLimit} * 1.1
plugin.jdbcdatasource.ataccama.one.driver.mssql.disallowed-indexes-table-types=SYNONYM
plugin.jdbcdatasource.ataccama.one.driver.mssql.bulk-import-table-count-threshold=30
plugin.jdbcdatasource.ataccama.one.driver.mssql.row-number-column=ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) as ____rno
Azure Synapse Analytics data source configuration
Azure Synapse Analytics JDBC data source configuration
plugin.jdbcdatasource.ataccama.one.driver.azure-synapse.name=Azure Synapse Analytics
plugin.jdbcdatasource.ataccama.one.driver.azure-synapse.connection-pattern=jdbc:sqlserver://<hostname>:<port>;databaseName=<database>
plugin.jdbcdatasource.ataccama.one.driver.azure-synapse.driver-class-path = mssql-jdbc*.jar
plugin.jdbcdatasource.ataccama.one.driver.azure-synapse.additional-classpath-files = mssqlLibs/*;
plugin.jdbcdatasource.ataccama.one.driver.azure-synapse.provider-class = com.ataccama.dpe.plugin.dataconnect.jdbc.provider.mssql.MSSQLDataSourceClientProvider
plugin.jdbcdatasource.ataccama.one.driver.azure-synapse.pooling-enabled = true
plugin.jdbcdatasource.ataccama.one.driver.azure-synapse.connection-timeout = 20000
plugin.jdbcdatasource.ataccama.one.driver.azure-synapse.idle-timeout = 300000
plugin.jdbcdatasource.ataccama.one.driver.azure-synapse.max-lifetime = 900000
plugin.jdbcdatasource.ataccama.one.driver.azure-synapse.minimum-idle = 1
plugin.jdbcdatasource.ataccama.one.driver.azure-synapse.maximum-pool-size = 5
plugin.jdbcdatasource.ataccama.one.driver.azure-synapse.full-select-query-pattern = SELECT {columns} FROM {table}
plugin.jdbcdatasource.ataccama.one.driver.azure-synapse.preview-query-pattern = SELECT TOP {previewLimit} {columns} FROM {table}
plugin.jdbcdatasource.ataccama.one.driver.azure-synapse.dsl-query-preview-query-pattern = SELECT TOP {previewLimit} * FROM ({dslQuery}) AS dslQuery
plugin.jdbcdatasource.ataccama.one.driver.azure-synapse.dsl-query-import-metadata-query-pattern = SELECT TOP 0 * FROM ({dslQuery}) AS dslQuery
plugin.jdbcdatasource.ataccama.one.driver.azure-synapse.row-count-query-pattern = SELECT COUNT(*) FROM {table}
plugin.jdbcdatasource.ataccama.one.driver.azure-synapse.sampling-query-pattern = SELECT TOP {limit} {columns} FROM {table}
plugin.jdbcdatasource.ataccama.one.driver.azure-synapse.disallowed-indexes-table-types=SYNONYM
plugin.jdbcdatasource.ataccama.one.driver.azure-synapse.schema-exclude-pattern=sys
SAP HANA data source configuration
SAP HANA JDBC data source configuration
plugin.jdbcdatasource.ataccama.one.driver.sap-hana.disabled=false
plugin.jdbcdatasource.ataccama.one.driver.sap-hana.name=SAP HANA
plugin.jdbcdatasource.ataccama.one.driver.sap-hana.connection-pattern=jdbc:sap://<hostname>:<port>/?databaseName=<database>
plugin.jdbcdatasource.ataccama.one.driver.sap-hana.driver-class-path = ngdbc*.jar
plugin.jdbcdatasource.ataccama.one.driver.sap-hana.provider-class = com.ataccama.dpe.plugin.dataconnect.jdbc.provider.saphana.SaphanaDataSourceClientProvider
plugin.jdbcdatasource.ataccama.one.driver.sap-hana.driver-class = com.sap.db.jdbc.Driver
plugin.jdbcdatasource.ataccama.one.driver.sap-hana.pooling-enabled=true
plugin.jdbcdatasource.ataccama.one.driver.sap-hana.connection-timeout=20000
plugin.jdbcdatasource.ataccama.one.driver.sap-hana.idle-timeout=300000
plugin.jdbcdatasource.ataccama.one.driver.sap-hana.max-lifetime=900000
plugin.jdbcdatasource.ataccama.one.driver.sap-hana.minimum-idle=1
plugin.jdbcdatasource.ataccama.one.driver.sap-hana.maximum-pool-size=5
plugin.jdbcdatasource.ataccama.one.driver.sap-hana.full-select-query-pattern = SELECT {columns} FROM {table}
plugin.jdbcdatasource.ataccama.one.driver.sap-hana.preview-query-pattern = SELECT {columns} FROM {table} LIMIT {previewLimit}
plugin.jdbcdatasource.ataccama.one.driver.sap-hana.dsl-query-preview-query-pattern = SELECT * FROM ({dslQuery}) dslQuery LIMIT {previewLimit}
plugin.jdbcdatasource.ataccama.one.driver.sap-hana.dsl-query-import-metadata-query-pattern = SELECT * FROM ({dslQuery}) dslQuery LIMIT 0
plugin.jdbcdatasource.ataccama.one.driver.sap-hana.row-count-query-pattern = SELECT COUNT(*) FROM {table}
plugin.jdbcdatasource.ataccama.one.driver.sap-hana.sampling-query-pattern = SELECT {columns} FROM {table} TABLESAMPLE BERNOULLI ({percentageLimit}*100)
plugin.jdbcdatasource.ataccama.one.driver.sap-hana.disallowed-indexes-table-types=SYNONYM,VIEW
MariaDB data source configuration
MariaDB JDBC data source configuration
plugin.jdbcdatasource.ataccama.one.driver.mariadb.name=MariaDB
plugin.jdbcdatasource.ataccama.one.driver.mariadb.connection-pattern=jdbc:mariadb://<hostname>:<port>/<database>
plugin.jdbcdatasource.ataccama.one.driver.mariadb.driver-class-path = mariadb*.jar
plugin.jdbcdatasource.ataccama.one.driver.mariadb.provider-class = com.ataccama.dpe.plugin.dataconnect.jdbc.provider.mariadb.MariaDBDataSourceClientProvider
plugin.jdbcdatasource.ataccama.one.driver.mariadb.pooling-enabled = true
plugin.jdbcdatasource.ataccama.one.driver.mariadb.connection-timeout = 20000
plugin.jdbcdatasource.ataccama.one.driver.mariadb.idle-timeout = 300000
plugin.jdbcdatasource.ataccama.one.driver.mariadb.max-lifetime = 900000
plugin.jdbcdatasource.ataccama.one.driver.mariadb.minimum-idle = 1
plugin.jdbcdatasource.ataccama.one.driver.mariadb.maximum-pool-size = 5
plugin.jdbcdatasource.ataccama.one.driver.mariadb.full-select-query-pattern = SELECT {columns} FROM {table}
plugin.jdbcdatasource.ataccama.one.driver.mariadb.preview-query-pattern = SELECT {columns} FROM {table} LIMIT {previewLimit}
plugin.jdbcdatasource.ataccama.one.driver.mariadb.dsl-query-preview-query-pattern = SELECT * FROM ({dslQuery}) dslQuery LIMIT {previewLimit}
plugin.jdbcdatasource.ataccama.one.driver.mariadb.dsl-query-import-metadata-query-pattern = SELECT * FROM ({dslQuery}) dslQuery LIMIT 0
plugin.jdbcdatasource.ataccama.one.driver.mariadb.row-count-query-pattern = SELECT COUNT(*) FROM {table}
plugin.jdbcdatasource.ataccama.one.driver.mariadb.sampling-query-pattern = SELECT {columns} FROM {table} LIMIT {limit}
plugin.jdbcdatasource.ataccama.one.driver.mariadb.query-quotation-mark =`
plugin.jdbcdatasource.ataccama.one.driver.mariadb.disallowed-indexes-table-types=SYNONYM
Teradata data source configuration
Teradata JDBC data source configuration
plugin.jdbcdatasource.ataccama.one.driver.teradata.name=Teradata
# We strongly recommend providing the database name in the connection string.
plugin.jdbcdatasource.ataccama.one.driver.teradata.connection-pattern=jdbc:teradata://<hostname>/database=<database>,charset=UTF8
plugin.jdbcdatasource.ataccama.one.driver.teradata.driver-class-path= terajdbc4-*.jar
plugin.jdbcdatasource.ataccama.one.driver.teradata.provider-class = com.ataccama.dpe.plugin.dataconnect.jdbc.provider.teradata.TeradataDataSourceClientProvider
plugin.jdbcdatasource.ataccama.one.driver.teradata.full-select-query-pattern = SELECT {columns} FROM {table}
plugin.jdbcdatasource.ataccama.one.driver.teradata.preview-query-pattern = SELECT {columns} FROM {table} SAMPLE {previewLimit}
plugin.jdbcdatasource.ataccama.one.driver.teradata.dsl-query-preview-query-pattern = SELECT TOP {previewLimit} * FROM ({dslQuery}) dslQuery
plugin.jdbcdatasource.ataccama.one.driver.teradata.dsl-query-import-metadata-query-pattern = SELECT * FROM ({dslQuery}) dslQuery WHERE 1 = 0
plugin.jdbcdatasource.ataccama.one.driver.teradata.row-count-query-pattern = SELECT CAST(COUNT (*) AS BIGINT) FROM {table}
plugin.jdbcdatasource.ataccama.one.driver.teradata.sampling-query-pattern = SELECT {columns} FROM {table} SAMPLE {limit}
plugin.jdbcdatasource.ataccama.one.driver.teradata.pooling-enabled = true
plugin.jdbcdatasource.ataccama.one.driver.teradata.connection-timeout = 20000 // not supported anyway
plugin.jdbcdatasource.ataccama.one.driver.teradata.idle-timeout = 300000
plugin.jdbcdatasource.ataccama.one.driver.teradata.max-lifetime = 900000
plugin.jdbcdatasource.ataccama.one.driver.teradata.minimum-idle = 1
plugin.jdbcdatasource.ataccama.one.driver.teradata.maximum-pool-size = 5
plugin.jdbcdatasource.ataccama.one.driver.teradata.disallowed-indexes-table-types=SYNONYM
Amazon Redshift data source configuration
Amazon Redshift JDBC data source configuration
plugin.jdbcdatasource.ataccama.one.driver.redshift.disabled=false
plugin.jdbcdatasource.ataccama.one.driver.redshift.name=Amazon Redshift
plugin.jdbcdatasource.ataccama.one.driver.redshift.connection-pattern=jdbc:redshift://<hostname>:<port>/<database>
plugin.jdbcdatasource.ataccama.one.driver.redshift.driver-class-path = redshift-*.jar
plugin.jdbcdatasource.ataccama.one.driver.redshift.provider-class = com.ataccama.dpe.plugin.dataconnect.jdbc.provider.redshift.RedshiftDataSourceClientProvider
plugin.jdbcdatasource.ataccama.one.driver.redshift.pooling-enabled = true
plugin.jdbcdatasource.ataccama.one.driver.redshift.connection-timeout = 20000
plugin.jdbcdatasource.ataccama.one.driver.redshift.idle-timeout = 300000
plugin.jdbcdatasource.ataccama.one.driver.redshift.max-lifetime = 900000
plugin.jdbcdatasource.ataccama.one.driver.redshift.minimum-idle = 1
plugin.jdbcdatasource.ataccama.one.driver.redshift.maximum-pool-size = 5
plugin.jdbcdatasource.ataccama.one.driver.redshift.full-select-query-pattern = SELECT {columns} FROM {table}
plugin.jdbcdatasource.ataccama.one.driver.redshift.preview-query-pattern = SELECT {columns} FROM {table} LIMIT {previewLimit}
plugin.jdbcdatasource.ataccama.one.driver.redshift.dsl-query-preview-query-pattern = SELECT * FROM ({dslQuery}) dslQuery LIMIT {previewLimit}
plugin.jdbcdatasource.ataccama.one.driver.redshift.dsl-query-import-metadata-query-pattern = SELECT * FROM ({dslQuery}) dslQuery LIMIT 0
plugin.jdbcdatasource.ataccama.one.driver.redshift.row-count-query-pattern = SELECT COUNT(*) FROM {table}
plugin.jdbcdatasource.ataccama.one.driver.redshift.sampling-query-pattern = SELECT {columns} FROM {table} WHERE RANDOM() < {percentageLimit} limit {limit}
plugin.jdbcdatasource.ataccama.one.driver.redshift.query-quotation-mark =\"
plugin.jdbcdatasource.ataccama.one.driver.redshift.disallowed-indexes-table-types=SYNONYM
Snowflake data source configuration
Snowflake JDBC data source configuration
plugin.jdbcdatasource.ataccama.one.driver.snowflake.name=Snowflake
plugin.jdbcdatasource.ataccama.one.driver.snowflake.connection-pattern=jdbc:snowflake://<hostname>:<port>?db=<database>
plugin.jdbcdatasource.ataccama.one.driver.snowflake.provider-class = com.ataccama.dpe.plugin.snowflake.jdbc.SnowflakeDataSourceClientProvider
plugin.jdbcdatasource.ataccama.one.driver.snowflake.pooling-enabled = true
plugin.jdbcdatasource.ataccama.one.driver.snowflake.connection-timeout = 20000
plugin.jdbcdatasource.ataccama.one.driver.snowflake.idle-timeout = 300000
plugin.jdbcdatasource.ataccama.one.driver.snowflake.max-lifetime = 900000
plugin.jdbcdatasource.ataccama.one.driver.snowflake.minimum-idle = 1
plugin.jdbcdatasource.ataccama.one.driver.snowflake.maximum-pool-size = 5
plugin.jdbcdatasource.ataccama.one.driver.snowflake.full-select-query-pattern = SELECT {columns} FROM {table}
plugin.jdbcdatasource.ataccama.one.driver.snowflake.preview-query-pattern = SELECT {columns} FROM {table} LIMIT {previewLimit}
plugin.jdbcdatasource.ataccama.one.driver.snowflake.dsl-query-preview-query-pattern = SELECT * FROM ({dslQuery}) dslQuery LIMIT {previewLimit}
plugin.jdbcdatasource.ataccama.one.driver.snowflake.dsl-query-import-metadata-query-pattern = SELECT * FROM ({dslQuery}) dslQuery LIMIT 0
plugin.jdbcdatasource.ataccama.one.driver.snowflake.row-count-query-pattern = SELECT COUNT(*) FROM {table}
plugin.jdbcdatasource.ataccama.one.driver.snowflake.sampling-query-pattern = select {columns} from {table} sample bernoulli({percentage100Limit})
plugin.jdbcdatasource.ataccama.one.driver.snowflake.query-quotation-mark =\"
plugin.jdbcdatasource.ataccama.one.driver.snowflake.disallowed-indexes-table-types=SYNONYM
plugin.jdbcdatasource.ataccama.one.driver.snowflake.schema-exclude-pattern=^(INFORMATION_SCHEMA)|(information_schema)$
Apache Cassandra data source configuration
Apache Cassandra JDBC data source configuration
plugin.jdbcdatasource.ataccama.one.driver.cassandra.disabled=false
plugin.jdbcdatasource.ataccama.one.driver.cassandra.name=Cassandra
plugin.jdbcdatasource.ataccama.one.driver.cassandra.connection-pattern=jdbc:cassandra://<hostname>:<port>
plugin.jdbcdatasource.ataccama.one.driver.cassandra.driver-class-path = CassandraJDBC42*.jar
plugin.jdbcdatasource.ataccama.one.driver.cassandra.driver-class = com.simba.cassandra.jdbc42.Driver
plugin.jdbcdatasource.ataccama.one.driver.cassandra.pooling-enabled = true
plugin.jdbcdatasource.ataccama.one.driver.cassandra.connection-timeout = 20000
plugin.jdbcdatasource.ataccama.one.driver.cassandra.idle-timeout = 300000
plugin.jdbcdatasource.ataccama.one.driver.cassandra.max-lifetime = 900000
plugin.jdbcdatasource.ataccama.one.driver.cassandra.minimum-idle = 1
plugin.jdbcdatasource.ataccama.one.driver.cassandra.maximum-pool-size = 5
plugin.jdbcdatasource.ataccama.one.driver.cassandra.full-select-query-pattern = SELECT {columns} FROM {table}
plugin.jdbcdatasource.ataccama.one.driver.cassandra.dsl-query-preview-query-pattern = SELECT * FROM ({dslQuery}) dslQuery LIMIT {previewLimit}
plugin.jdbcdatasource.ataccama.one.driver.cassandra.dsl-query-import-metadata-query-pattern = SELECT * FROM ({dslQuery}) dslQuery LIMIT 0
plugin.jdbcdatasource.ataccama.one.driver.cassandra.preview-query-pattern = SELECT {columns} FROM {table} LIMIT {previewLimit}
plugin.jdbcdatasource.ataccama.one.driver.cassandra.row-count-query-pattern = SELECT COUNT(*) FROM {table}
plugin.jdbcdatasource.ataccama.one.driver.cassandra.sampling-query-pattern = SELECT {columns} FROM {table} LIMIT {limit}
plugin.jdbcdatasource.ataccama.one.driver.cassandra.query-quotation-mark=\"
plugin.jdbcdatasource.ataccama.one.driver.cassandra.schema-regex=jdbc:cassandra://.*?/([a-zA-Z0-9_]*).*
BigQuery data source configuration
BigQuery JDBC data source configuration
plugin.jdbcdatasource.ataccama.one.driver.bq.name=Big Query
plugin.jdbcdatasource.ataccama.one.driver.bq.connection-pattern=jdbc:bigquery://https://www.googleapis.com/bigquery/v2:443;ProjectId=<projectID>;OAuthType=<OAuthType>;
plugin.jdbcdatasource.ataccama.one.driver.bq.driver-class-path=GoogleBigQueryJDBC42.jar
plugin.jdbcdatasource.ataccama.one.driver.bq.additional-classpath-files=bigQueryLibs/*;
plugin.jdbcdatasource.ataccama.one.driver.bq.driver-class=com.simba.googlebigquery.jdbc42.Driver
plugin.jdbcdatasource.ataccama.one.driver.bq.pooling-enabled=false
plugin.jdbcdatasource.ataccama.one.driver.bq.connection-timeout=20000
plugin.jdbcdatasource.ataccama.one.driver.bq.idle-timeout=300000
plugin.jdbcdatasource.ataccama.one.driver.bq.max-lifetime=900000
plugin.jdbcdatasource.ataccama.one.driver.bq.minimum-idle=1
plugin.jdbcdatasource.ataccama.one.driver.bq.maximum-pool-size=5
plugin.jdbcdatasource.ataccama.one.driver.bq.full-select-query-pattern=SELECT {columns} FROM {table}
plugin.jdbcdatasource.ataccama.one.driver.bq.preview-query-pattern=SELECT {columns} FROM {table}
plugin.jdbcdatasource.ataccama.one.driver.bq.dsl-query-preview-query-pattern = SELECT * FROM ({dslQuery}) dslQuery LIMIT {previewLimit}
plugin.jdbcdatasource.ataccama.one.driver.bq.dsl-query-import-metadata-query-pattern = SELECT * FROM ({dslQuery}) dslQuery LIMIT 0
plugin.jdbcdatasource.ataccama.one.driver.bq.row-count-query-pattern=SELECT COUNT(*) FROM {table}
plugin.jdbcdatasource.ataccama.one.driver.bq.sampling-query-pattern=SELECT {columns} FROM {table} WHERE RAND() < {percentageLimit};
plugin.jdbcdatasource.ataccama.one.driver.bq.query-quotation-mark=`
# This driver supports defining custom properties, which can be set as follows:
#plugin.jdbcdatasource.ataccama.one.driver.bq.properties.<custom_property_name>=<custom_property_value>
plugin.jdbcdatasource.ataccama.one.driver.bq.disabled=false
# If enabled, the database connection can be used only for a specific Big Query project. If you upgraded from an earlier version of ONE, set the property to true.
# If set to false, the same connection can be used for multiple Big Query projects.
# If needed, you can also create two connections with different configurations to cover both use cases.
plugin.jdbcdatasource.ataccama.one.driver.bq.skip-database-level=false
IBM Db2 data source configuration
IBM Db2 data source configuration
plugin.jdbcdatasource.ataccama.one.driver.db2.name=DB2
plugin.jdbcdatasource.ataccama.one.driver.db2.connection-pattern=jdbc:db2://<hostname>:<port>/<database>
plugin.jdbcdatasource.ataccama.one.driver.db2.driver-class-path=db2jcc4.jar
plugin.jdbcdatasource.ataccama.one.driver.db2.driver-class=com.ibm.db2.jcc.DB2Driver
plugin.jdbcdatasource.ataccama.one.driver.db2.full-select-query-pattern=SELECT {columns} FROM {table}
plugin.jdbcdatasource.ataccama.one.driver.db2.preview-query-pattern=SELECT {columns} FROM {table} LIMIT {previewLimit}
plugin.jdbcdatasource.ataccama.one.driver.db2.dsl-query-preview-query-pattern=SELECT * FROM ({dslQuery}) dslQuery LIMIT {previewLimit}
plugin.jdbcdatasource.ataccama.one.driver.db2.dsl-query-import-metadata-query-pattern=SELECT * FROM ({dslQuery}) dslQuery LIMIT 0
plugin.jdbcdatasource.ataccama.one.driver.db2.row-count-query-pattern=SELECT COUNT(*) FROM {table}
plugin.jdbcdatasource.ataccama.one.driver.db2.sampling-query-pattern = SELECT {columns} FROM {table} WHERE RANDOM() < {percentageLimit} limit {limit}
# Depending on the Db2 server version, instead of 'values current server' you might need to use the command 'select current_server from sysibm.sysdummy1' for the following property. Run both commands on your database server before modifying the configuration. The server name is used as the top-level location of imported catalog items.
plugin.jdbcdatasource.ataccama.one.driver.db2.location-query = values current server
IBM Netezza data source configuration
IBM Netezza JDBC data source configuration
plugin.jdbcdatasource.ataccama.one.driver.netezza.name=Netezza
plugin.jdbcdatasource.ataccama.one.driver.netezza.connection-pattern=jdbc:netezza://<hostname>:<port>/<database>
plugin.jdbcdatasource.ataccama.one.driver.netezza.driver-class-path=nzjdbc.jar
plugin.jdbcdatasource.ataccama.one.driver.netezza.driver-class=org.netezza.Driver
plugin.jdbcdatasource.ataccama.one.driver.netezza.pooling-enabled=true
plugin.jdbcdatasource.ataccama.one.driver.netezza.connection-timeout=20000
plugin.jdbcdatasource.ataccama.one.driver.netezza.idle-timeout=300000
plugin.jdbcdatasource.ataccama.one.driver.netezza.max-lifetime=900000
plugin.jdbcdatasource.ataccama.one.driver.netezza.minimum-idle=1
plugin.jdbcdatasource.ataccama.one.driver.netezza.maximum-pool-size=5
plugin.jdbcdatasource.ataccama.one.driver.netezza.full-select-query-pattern=SELECT {columns} FROM {table}
plugin.jdbcdatasource.ataccama.one.driver.netezza.preview-query-pattern=SELECT {columns} FROM {table}
plugin.jdbcdatasource.ataccama.one.driver.netezza.dsl-query-preview-query-pattern = SELECT * FROM ({dslQuery}) dslQuery LIMIT {previewLimit}
plugin.jdbcdatasource.ataccama.one.driver.netezza.dsl-query-import-metadata-query-pattern = SELECT * FROM ({dslQuery}) dslQuery LIMIT 0
plugin.jdbcdatasource.ataccama.one.driver.netezza.row-count-query-pattern=SELECT COUNT(*) FROM {table}
plugin.jdbcdatasource.ataccama.one.driver.netezza.sampling-query-pattern=SELECT {columns} FROM {table} LIMIT {limit}
plugin.jdbcdatasource.ataccama.one.driver.netezza.query-quotation-mark=\"
plugin.jdbcdatasource.ataccama.one.driver.netezza.connection-test-query=select 1
Informix data source configuration
Informix JDBC data source configuration
For Informix, you need to refer to tables by a simplified syntax with no reference to the catalog: {schema}.{simpleTable}
, where:
-
`{schema} - schema name
-
{simpleT`able}
- name of the table
plugin.jdbcdatasource.ataccama.one.driver.informix.name=INFORMIX
plugin.jdbcdatasource.ataccama.one.driver.informix.connection-pattern=jdbc:informix-sqli://<hostname>:<port>/<dbname>:informixserver=<server_name>
plugin.jdbcdatasource.ataccama.one.driver.informix.driver-class-path=jdbc-4.10.8.1.jar
plugin.jdbcdatasource.ataccama.one.driver.informix.driver-class=com.informix.jdbc.IfxDriver
plugin.jdbcdatasource.ataccama.one.driver.informix.pooling-enabled=true
plugin.jdbcdatasource.ataccama.one.driver.informix.connection-timeout=20000
plugin.jdbcdatasource.ataccama.one.driver.informix.idle-timeout=300000
plugin.jdbcdatasource.ataccama.one.driver.informix.max-lifetime=900000
plugin.jdbcdatasource.ataccama.one.driver.informix.minimum-idle=1
plugin.jdbcdatasource.ataccama.one.driver.informix.maximum-pool-size=5
plugin.jdbcdatasource.ataccama.one.driver.informix.full-select-query-pattern=SELECT {columns} FROM {schema}.{simpleTable}
plugin.jdbcdatasource.ataccama.one.driver.informix.preview-query-pattern=SELECT {columns} FROM {schema}.{simpleTable} LIMIT {previewLimit}
plugin.jdbcdatasource.ataccama.one.driver.informix.dsl-query-preview-query-pattern = SELECT * FROM ({dslQuery}) dslQuery LIMIT {previewLimit}
plugin.jdbcdatasource.ataccama.one.driver.informix.dsl-query-import-metadata-query-pattern = SELECT * FROM ({dslQuery}) dslQuery LIMIT 0
plugin.jdbcdatasource.ataccama.one.driver.informix.row-count-query-pattern=SELECT COUNT(*) FROM {schema}.{simpleTable}
plugin.jdbcdatasource.ataccama.one.driver.informix.sampling-query-pattern=SELECT {columns} FROM {schema}.{simpleTable} WHERE 0 < {percentageLimit} limit {limit}
plugin.jdbcdatasource.ataccama.one.driver.informix.disallowed-indexes-table-types=SYNONYM
plugin.jdbcdatasource.ataccama.one.driver.informix.disabled=false
plugin.jdbcdatasource.ataccama.one.driver.informix.query-quotation-mark=\u0020
Dremio data source configuration
Dremio JDBC data source configuration
plugin.jdbcdatasource.ataccama.one.driver.dremio.disabled=true
plugin.jdbcdatasource.ataccama.one.driver.dremio.name=Dremio
plugin.jdbcdatasource.ataccama.one.driver.dremio.connection-pattern=jdbc:dremio:direct=<hostname>:<port>[;schema=<schema>]
plugin.jdbcdatasource.ataccama.one.driver.dremio.driver-class-path=dremio-jdbc-driver*.jar
plugin.jdbcdatasource.ataccama.one.driver.dremio.driver-class=com.dremio.jdbc.Driver
plugin.jdbcdatasource.ataccama.one.driver.dremio.pooling-enabled=false
plugin.jdbcdatasource.ataccama.one.driver.dremio.connection-timeout=20000
plugin.jdbcdatasource.ataccama.one.driver.dremio.idle-timeout=300000
plugin.jdbcdatasource.ataccama.one.driver.dremio.max-lifetime=900000
plugin.jdbcdatasource.ataccama.one.driver.dremio.minimum-idle=1
plugin.jdbcdatasource.ataccama.one.driver.dremio.maximum-pool-size=5
plugin.jdbcdatasource.ataccama.one.driver.dremio.profiling-sample-limit=100000
plugin.jdbcdatasource.ataccama.one.driver.dremio.full-select-query-pattern=SELECT {columns} FROM "{schema}"."{simpleTable}"
plugin.jdbcdatasource.ataccama.one.driver.dremio.preview-query-pattern=SELECT {columns} FROM "{schema}"."{simpleTable}" LIMIT {previewLimit}
plugin.jdbcdatasource.ataccama.one.driver.dremio.row-count-query-pattern=SELECT COUNT(*) FROM "{schema}"."{simpleTable}"
plugin.jdbcdatasource.ataccama.one.driver.dremio.sampling-query-pattern=SELECT {columns} FROM "{schema}"."{simpleTable}" WHERE RAND() < {percentageLimit};
plugin.jdbcdatasource.ataccama.one.driver.dremio.dsl-query-preview-query-pattern = SELECT * FROM ({dslQuery}) dslQuery LIMIT {previewLimit}
plugin.jdbcdatasource.ataccama.one.driver.dremio.dsl-query-import-metadata-query-pattern = SELECT * FROM ({dslQuery}) dslQuery LIMIT 0
Sybase data source configuration
Sybase data source configuration
plugin.jdbcdatasource.ataccama.one.driver.sybase.disabled=true
plugin.jdbcdatasource.ataccama.one.driver.sybase.name=Sybase
plugin.jdbcdatasource.ataccama.one.driver.sybase.connection-pattern=jdbc:jtds:sybase://<hostname>:<port>;DatabaseName=<database>
plugin.jdbcdatasource.ataccama.one.driver.sybase.driver-class-path = jtds-*.jar
plugin.jdbcdatasource.ataccama.one.driver.sybase.driver-class = net.sourceforge.jtds.jdbc.Driver
plugin.jdbcdatasource.ataccama.one.driver.sybase.pooling-enabled=true
plugin.jdbcdatasource.ataccama.one.driver.sybase.connection-timeout=20000
plugin.jdbcdatasource.ataccama.one.driver.sybase.idle-timeout=300000
plugin.jdbcdatasource.ataccama.one.driver.sybase.max-lifetime=900000
plugin.jdbcdatasource.ataccama.one.driver.sybase.minimum-idle=1
plugin.jdbcdatasource.ataccama.one.driver.sybase.maximum-pool-size=5
plugin.jdbcdatasource.ataccama.one.driver.sybase.full-select-query-pattern=SELECT {columns} FROM {table}
plugin.jdbcdatasource.ataccama.one.driver.sybase.preview-query-pattern=SELECT TOP {previewLimit} {columns} FROM {table}
plugin.jdbcdatasource.ataccama.one.driver.sybase.dsl-query-preview-query-pattern=SELECT TOP {previewLimit} * FROM ({dslQuery}) dslQuery
plugin.jdbcdatasource.ataccama.one.driver.sybase.dsl-query-import-metadata-query-pattern=SELECT TOP 0 * FROM ({dslQuery}) dslQuery
plugin.jdbcdatasource.ataccama.one.driver.sybase.row-count-query-pattern=SELECT COUNT(*) FROM {table}
plugin.jdbcdatasource.ataccama.one.driver.sybase.sampling-query-pattern=SELECT TOP {limit} {columns} FROM {table}
plugin.jdbcdatasource.ataccama.one.driver.sybase.disallowed-indexes-table-types=SYNONYM
plugin.jdbcdatasource.ataccama.one.driver.sybase.bulk-import-table-count-threshold=30
plugin.jdbcdatasource.ataccama.one.driver.sybase.row-number-column=ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) as ____rno#
SQLite data source configuration
SQLite data source configuration
Available from 13.9.2.
plugin.jdbcdatasource.ataccama.one.driver.sqlite.name=Sqlite3
plugin.jdbcdatasource.ataccama.one.driver.sqlite.connection-pattern=jdbc:sqlite:/path
plugin.jdbcdatasource.ataccama.one.driver.sqlite.driver-class-path=sqlite-jdbc-*.jar
plugin.jdbcdatasource.ataccama.one.driver.sqlite.driver-class=org.sqlite.JDBC
plugin.jdbcdatasource.ataccama.one.driver.sqlite.provider-class = com.ataccama.dpe.plugin.dataconnect.jdbc.provider.sqlite.SqliteDataSourceClientProvider
plugin.jdbcdatasource.ataccama.one.driver.sqlite.pooling-enabled=false
plugin.jdbcdatasource.ataccama.one.driver.sqlite.connection-timeout=20000
plugin.jdbcdatasource.ataccama.one.driver.sqlite.idle-timeout=300000
plugin.jdbcdatasource.ataccama.one.driver.sqlite.max-lifetime=900000
plugin.jdbcdatasource.ataccama.one.driver.sqlite.minimum-idle=1
plugin.jdbcdatasource.ataccama.one.driver.sqlite.maximum-pool-size=5
plugin.jdbcdatasource.ataccama.one.driver.sqlite.profiling-sample-limit=100000
plugin.jdbcdatasource.ataccama.one.driver.sqlite.full-select-query-pattern=SELECT {columns} FROM {table}
plugin.jdbcdatasource.ataccama.one.driver.sqlite.preview-query-pattern=SELECT {columns} FROM {table}
plugin.jdbcdatasource.ataccama.one.driver.sqlite.row-count-query-pattern=SELECT COUNT(*) FROM {table}
plugin.jdbcdatasource.ataccama.one.driver.sqlite.sampling-query-pattern=SELECT {columns} FROM {table} WHERE RAND() < {percentageLimit};
plugin.jdbcdatasource.ataccama.one.driver.sqlite.query-quotation-mark=`
plugin.jdbcdatasource.ataccama.one.driver.sqlite.dsl-query-preview-query-pattern = SELECT * FROM ({dslQuery}) dslQuery LIMIT WHERE RAND() < {percentageLimit}
plugin.jdbcdatasource.ataccama.one.driver.sqlite.dsl-query-import-metadata-query-pattern = SELECT * FROM ({dslQuery}) dslQuery LIMIT 0
Troubleshooting
Dremio error: Failure getting metadata
In case you encounter the following error Failure getting metadata: Number of fields in dataset exceeded the maximum number of fields of 800
, this means you need to review and modify the configuration of wide table limits in Dremio.
For more information, refer to the official Dremio documentation, specifically Creating and Querying Wide Tables.
BigQuery connection issues
-
When attempting to troubleshoot BigQuery connection issues, try the following:
-
Consult the ODBC and JDBC drivers for BigQuery documentation.
-
Consult the JDBC driver documentation. Included as a PDF in the downloaded JDBC driver ZIP file.
-
The recommended predefined Service Account roles used to access Big Query data are BigQuery Data Viewer (
roles/bigquery.dataViewer
) and BigQuery Job User (roles/bigquery.jobUser
). -
Pay attention to the Large Result Set Support section and the related
AllowLargeResults
,LargeResultDataset
, andLargeResultTable
properties. -
If you are using a large result set:
-
Make sure that the
LargeResultDataset
option is specified, the specified dataset exists, and is in the same region as the queried table. -
Otherwise, the Service Account used must have
bigquery.datasets.create
permissions on the BigQuery instance specified by theProjectId
property. -
Note that setting the
LargeResultTable
value causes all results to be written to the same table. However, the table creation time is not shown when the value is set.
-
-
If you are setting the
OAuthPvtKeyPath
driver property as a file path, for example, the key file is present on the DPE or the Runtime server:-
Set it directly as a JDBC string, not as a Driver property object.
-
Make sure the user running the ONE platform (DPE or Runtime server) has read access to the key files
.json
and.p12
.
-
-
Use
LogLevel=6
andLogPath=<path>
driver properties for the JDBC driver to create<path>/BigQuery…log
files with detailed information about connecting to BigQuery, metadata processing, and SQL commands execution.
Azure SQL database token failure
Connecting to Azure SQL database fails with the following error:
MSI Token failure: Failed to acquire access token from IMDS, verify your clientId
The error means that the clientId
is either wrong or missing some required privileges.
Verify the following in your environment:
-
Make sure that the Azure AD account is created within the Azure SQL server. The following example shows what the creation SQL queries look like. The user is in the table named
sys.sysusers
.Creation SQL queriesCREATE USER name_of_the_user_managed_identity FROM EXTERNAL PROVIDER; ALTER ROLE db_datareader ADD MEMBER name_of_the_user_managed_identity;
-
Make sure that in your Azure SQL server, the following property is enabled on the Set server firewall tab: Allow Azure services and resources to access this server.
-
On the virtual machine where your DPE is running, check Identity > User assigned. The managed identity should match the one in the JDBC string. Sample JDBC string:
Sample JDBC stringjdbc:sqlserver://host.database.windows.net:1433;encrypt=true;database=yourDB;encrypt=true;trustServerCertificate=false;loginTimeout=30;hostNameInCertificate=*.database.windows.net;authentication=ActiveDirectoryMSI;msiClientId=abcdxyz;
-
In most cases, completing the previous steps should be enough for DPE or any other external applications installed inside the VM to successfully connect to Azure SQL. If the problem still persists, try enabling the System assigned and remove
msiClientId
.
Was this page useful?