|
Matillion Data Model for SAP HANA
|
Connection String OptionsBack To Top
- Batch Size
- Connection Life Time
- Connect On Open
- Database
- Firewall Password
- Firewall Port
- Firewall Server
- Firewall Type
- Firewall User
- Include System Objects
- Include Table Types
- Location
- Logfile
- Log Modules
- Max Log File Count
- Max Log File Size
- Max Rows
- Other
- Password
- Pool Idle Timeout
- Pool Max Size
- Pool Min Size
- Pool Wait Time
- Port
- Query Passthrough
- Readonly
- RTK
- Server
- Session Variables
- SSH Auth Mode
- SSH Client Cert
- SSH Client Cert Password
- SSH Client Cert Subject
- SSH Client Cert Type
- SSH Password
- SSH Port
- SSH Server
- SSH Server Fingerprint
- SSH User
- SSL Client Cert
- SSL Client Cert Password
- SSL Client Cert Subject
- SSL Client Cert Type
- SSL Server Cert
- Tables
- Timeout
- Use Connection Pooling
- User
- Use SSH
- Use SSL
- Verbosity
- Views
Batch Size
Data Type
int
Default Value
0
Remarks
When BatchSize is set to a value greater than 0, the batch operation will split the entire batch into separate batches of size BatchSize. The split batches will then be submitted to the server individually. This is useful when the server has limitations on the size of the request that can be submitted.
Setting BatchSize to 0 will submit the entire batch as specified.
Connection Life Time
Data Type
int
Default Value
0
Remarks
The maximum lifetime of a connection in seconds. Once the time has elapsed, the connection object is disposed. The default is 0 which indicates there is no limit to the connection lifetime.
Connect On Open
Data Type
bool
Default Value
false
Remarks
When set to true, a connection will be made to SAP HANA when the connection is opened. This property enables the Test Connection feature available in various database tools.
This feature acts as a NOOP command as it is used to verify a connection can be made to SAP HANA and nothing from this initial connection is maintained.
Setting this property to false may provide performance improvements (depending upon the number of times a connection is opened).
Database
Data Type
string
Default Value
""
Remarks
The name of the SAP HANA database running on the specified Server.
Firewall Password
Data Type
string
Default Value
""
Remarks
This property is passed to the proxy specified by FirewallServer and FirewallPort, following the authentication method specified by FirewallType.
Firewall Port
Data Type
int
Default Value
0
Remarks
This specifies the TCP port for a proxy allowing traversal of a firewall. Use FirewallServer to specify the name or IP address. Specify the protocol with FirewallType.
Firewall Server
Data Type
string
Default Value
""
Remarks
This property specifies the IP address, DNS name, or host name of a proxy allowing traversal of a firewall. The protocol is specified by FirewallType: Use FirewallServer with this property to connect through SOCKS or do tunneling.
Firewall Type
Data Type
string
Default Value
"NONE"
Remarks
This property specifies the protocol that the driver will use to tunnel traffic through the FirewallServer proxy.
| Type | Default Port | Description |
| TUNNEL | 80 | When this is set, the driver opens a connection to SAP HANA and traffic flows back and forth through the proxy. |
| SOCKS4 | 1080 | When this is set, the driver sends data through the SOCKS 4 proxy specified by FirewallServer and FirewallPort and passes the FirewallUser value to the proxy, which determines if the connection request should be granted. |
| SOCKS5 | 1080 | When this is set, the driver sends data through the SOCKS 5 proxy specified by FirewallServer and FirewallPort. If your proxy requires authentication, set FirewallUser and FirewallPassword to credentials the proxy recognizes. |
Firewall User
Data Type
string
Default Value
""
Remarks
The FirewallUser and FirewallPassword properties are used to authenticate against the proxy specified in FirewallServer and FirewallPort, following the authentication method specified in FirewallType.
Include System Objects
Data Type
bool
Default Value
false
Remarks
Set `IncludeSystemObjects` to True to fetch Hana System schema and tables. By default, this property is set to False to avoid listing the large number of system tables in the metadata listing.
Include Table Types
Data Type
bool
Default Value
false
Remarks
If set to true, the driver will report the types of individual tables and views.
Location
Data Type
string
Default Value
"%APPDATA%\\CData\\SAPHANA Data Provider\\Schema"
Remarks
The path to a directory which contains the schema files for the driver (.rsd files for tables and views, .rsb files for stored procedures). The folder location can be a relative path from the location of the executable. The Location property is only needed if you want to customize definitions (for example, change a column name, ignore a column, and so on) or extend the data model with new tables, views, or stored procedures.
If left unspecified, the default location is "%APPDATA%\\CData\\SAPHANA Data Provider\\Schema" with %APPDATA% being set to the user's configuration directory:
| Platform | %APPDATA% |
| Windows | The value of the APPDATA environment variable |
| Mac | ~/Library/Application Support |
| Linux | ~/.config |
Logfile
Data Type
string
Default Value
""
Remarks
Once this property is set, the driver will populate the log file as it carries out various tasks, such as when authentication is performed or queries are executed. If the specified file doesn't already exist, it will be created.
Connection strings and version information are also logged, though connection properties containing sensitive information are masked automatically.
If a relative filepath is supplied, the location of the log file will be resolved based on the path found in the Location connection property.
For more control over what is written to the log file, you can adjust the Verbosity property.
Log Modules
Data Type
string
Default Value
""
Remarks
Only the modules specified (separated by ';') will be included in the log file. By default all modules are included.
Max Log File Count
Data Type
int
Default Value
-1
Remarks
A string specifying the maximum file count of log files. When the limit is hit, a new log is created in the same folder with the date and time appended to the end and the oldest log file will be deleted. The minimum supported value is 2. A value of 0 or a negative value indicates no limit on the count.
Max Log File Size
Data Type
string
Default Value
"100MB"
Remarks
A string specifying the maximum size in bytes for a log file (for example, 10 MB). When the limit is hit, a new log is created in the same folder with the date and time appended to the end. The default limit is 100 MB. Values lower than 100 kB will use 100 kB as the value instead.
Max Rows
Data Type
int
Default Value
-1
Remarks
Limits the number of rows returned rows when no aggregation or group by is used in the query. This helps avoid performance issues at design time.
Other
Data Type
string
Default Value
""
Remarks
The properties listed below are available for specific use cases. Normal driver use cases and functionality should not require these properties.
Specify multiple properties in a semicolon-separated list.
Integration and Formatting
| DefaultColumnSize | Sets the default length of string fields when the data source does not provide column length in the metadata. The default value is 2000. |
| ConvertDateTimeToGMT | Determines whether to convert date-time values to GMT, instead of the local time of the machine. |
| RecordToFile=filename | Records the underlying socket data transfer to the specified file. |
Password
Data Type
string
Default Value
""
Remarks
The User and Password are together used to authenticate with the server.
Pool Idle Timeout
Data Type
int
Default Value
60
Remarks
The allowed idle time a connection can remain in the pool until the connection is closed. The default is 60 seconds.
Pool Max Size
Data Type
int
Default Value
100
Remarks
The maximum connections in the pool. The default is 100. To disable this property, set the property value to 0 or less.
Pool Min Size
Data Type
int
Default Value
1
Remarks
The minimum number of connections in the pool. The default is 1.
Pool Wait Time
Data Type
int
Default Value
60
Remarks
The max seconds to wait for a connection to become available. If a new connection request is waiting for an available connection and exceeds this time, an error is thrown. By default, new requests wait forever for an available connection.
Port
Data Type
string
Default Value
"39013"
Remarks
The port of the Server hosting the SAP HANA Database.
Query Passthrough
Data Type
bool
Default Value
true
Remarks
When this is set, queries are passed through directly to SAP HANA.
Readonly
Data Type
bool
Default Value
false
Remarks
If this property is set to true, the driver will allow only SELECT queries. INSERT, UPDATE, DELETE, and stored procedure queries will cause an error to be thrown.
RTK
Data Type
string
Default Value
""
Remarks
The RTK property may be used to license a build. See the included licensing file to see how to set this property. The runtime key is only available if you purchased an OEM license.
Server
Data Type
string
Default Value
""
Remarks
Set this property to the name or network address of the SAP HANA database instance.
Session Variables
Data Type
string
Default Value
""
Remarks
A comma-separated list of session variables to set on the current connection. For example: 'APPLICATION=myapp,var1=value1,var2=value2[,...]'
SSH Auth Mode
Data Type
string
Default Value
"Password"
Remarks
- None: No authentication will be performed. The current User value is ignored, and the connection will be logged in as anonymous.
- Password: The driver will use the values of User and Password to authenticate the user.
- Public_Key: The driver will use the values of User and SSHClientCert to authenticate the user. SSHClientCert must have a private key available for this authentication method to succeed.
SSH Client Cert
Data Type
string
Default Value
""
Remarks
In order to use public key authentication, SSHClientCert must contain a certificate with a valid private key. The certificate's public key value is sent to the server along with a signature produced using the private key. The server will first check to see if the public key values match what is known for the user and then will attempt to use those values to verify the signature.
SSH Client Cert Password
Data Type
string
Default Value
""
Remarks
This property is only used when authenticating to SFTP servers with SSHAuthMode set to PublicKey and SSHClientCert set to a private key.
SSH Client Cert Subject
Data Type
string
Default Value
"*"
Remarks
When loading a certificate the subject is used to locate the certificate in the store.
If an exact match is not found, the store is searched for subjects containing the value of the property.
If a match is still not found, the property is set to an empty string, and no certificate is selected.
The special value "*" picks the first certificate in the certificate store.
The certificate subject is a comma separated list of distinguished name fields and values. For instance "CN=www.server.com, OU=test, C=US, E=support@cdata.com". Common fields and their meanings are displayed below.
| Field | Meaning |
| CN | Common Name. This is commonly a host name like www.server.com. |
| O | Organization |
| OU | Organizational Unit |
| L | Locality |
| S | State |
| C | Country |
| E | Email Address |
If a field value contains a comma it must be quoted.
SSH Client Cert Type
Data Type
string
Default Value
"PUBLIC_KEY_FILE"
Remarks
The type of SSHClientCert certificate. This property can take one of the following values:
| 0 (USER - default) | For Windows, this specifies that the certificate store is a certificate store owned by the current user. Note: this store type is not available in Java. |
| 1 (MACHINE) | For Windows, this specifies that the certificate store is a machine store. Note: this store type is not available in Java. |
| 2 (PFXFILE) | The certificate store is the name of a PFX (PKCS12) file containing certificates. |
| 3 (PFXBLOB) | The certificate store is a string (binary or base-64-encoded) representing a certificate store in PFX (PKCS12) format. |
| 4 (JKSFILE) | The certificate store is the name of a Java Key Store (JKS) file containing certificates. Note: this store type is only available in Java. |
| 5 (JKSBLOB) | The certificate store is a string (binary or base-64-encoded) representing a certificate store in Java Key Store (JKS) format. Note: this store type is only available in Java. |
| 6 (PEMKEY_FILE) | The certificate store is the name of a PEM-encoded file that contains a private key and an optional certificate. |
| 7 (PEMKEY_BLOB) | The certificate store is a string (binary or base-64-encoded) that contains a private key and an optional certificate. |
| 8 (PUBLIC_KEY_FILE) | The certificate store is the name of a file that contains a PEM- or DER-encoded public key certificate. |
| 9 (PUBLIC_KEY_BLOB) | The certificate store is a string (binary or base-64-encoded) that contains a PEM- or DER-encoded public key certificate. |
| 10 (SSHPUBLIC_KEY_BLOB) | The certificate store is a string (binary or base-64-encoded) that contains an SSH-style public key. |
| 11 (P7BFILE) | The certificate store is the name of a PKCS7 file containing certificates. |
| 12 (P7BBLOB) | The certificate store is a string (binary) representing a certificate store in PKCS7 format. |
| 13 (SSHPUBLIC_KEY_FILE) | The certificate store is the name of a file that contains an SSH-style public key. |
| 14 (PPKFILE) | The certificate store is the name of a file that contains a PPK (PuTTY Private Key). |
| 15 (PPKBLOB) | The certificate store is a string (binary) that contains a PPK (PuTTY Private Key). |
| 16 (XMLFILE) | The certificate store is the name of a file that contains a certificate in XML format. |
| 17 (XMLBLOB) | The certificate store is a string that contains a certificate in XML format. |
SSH Password
Data Type
string
Default Value
""
Remarks
The SSH password.
SSH Port
Data Type
string
Default Value
"22"
Remarks
The SSH port.
SSH Server
Data Type
string
Default Value
""
Remarks
The SSH server.
SSH Server Fingerprint
Data Type
string
Default Value
""
Remarks
The SSH server fingerprint.
SSH User
Data Type
string
Default Value
""
Remarks
The SSH user.
SSL Client Cert
Data Type
string
Default Value
""
Remarks
The name of the certificate store for the client certificate.
The SSLClientCertType field specifies the type of the certificate store specified by SSLClientCert. If the store is password protected, specify the password in SSLClientCertPassword.
SSLClientCert is used in conjunction with the SSLClientCertSubject field in order to specify client certificates. If SSLClientCert has a value, and SSLClientCertSubject is set, a search for a certificate is initiated. See SSLClientCertSubject for more information.
Designations of certificate stores are platform-dependent.
The following are designations of the most common User and Machine certificate stores in Windows:
| MY | A certificate store holding personal certificates with their associated private keys. |
| CA | Certifying authority certificates. |
| ROOT | Root certificates. |
| SPC | Software publisher certificates. |
In Java, the certificate store normally is a file containing certificates and optional private keys.
When the certificate store type is PFXFile, this property must be set to the name of the file. When the type is PFXBlob, the property must be set to the binary contents of a PFX file (for example, PKCS12 certificate store).
SSL Client Cert Password
Data Type
string
Default Value
""
Remarks
If the certificate store is of a type that requires a password, this property is used to specify that password to open the certificate store.
SSL Client Cert Subject
Data Type
string
Default Value
"*"
Remarks
When loading a certificate the subject is used to locate the certificate in the store.
If an exact match is not found, the store is searched for subjects containing the value of the property. If a match is still not found, the property is set to an empty string, and no certificate is selected.
The special value "*" picks the first certificate in the certificate store.
The certificate subject is a comma separated list of distinguished name fields and values. For example, "CN=www.server.com, OU=test, C=US, E=support@company.com". The common fields and their meanings are shown below.
| Field | Meaning |
| CN | Common Name. This is commonly a host name like www.server.com. |
| O | Organization |
| OU | Organizational Unit |
| L | Locality |
| S | State |
| C | Country |
| E | Email Address |
If a field value contains a comma, it must be quoted.
SSL Client Cert Type
Data Type
string
Default Value
"USER"
Remarks
This property can take one of the following values:
| USER - default | For Windows, this specifies that the certificate store is a certificate store owned by the current user. Note that this store type is not available in Java. |
| MACHINE | For Windows, this specifies that the certificate store is a machine store. Note that this store type is not available in Java. |
| PFXFILE | The certificate store is the name of a PFX (PKCS12) file containing certificates. |
| PFXBLOB | The certificate store is a string (base-64-encoded) representing a certificate store in PFX (PKCS12) format. |
| JKSFILE | The certificate store is the name of a Java key store (JKS) file containing certificates. Note that this store type is only available in Java. |
| JKSBLOB | The certificate store is a string (base-64-encoded) representing a certificate store in JKS format. Note that this store type is only available in Java. |
| PEMKEY_FILE | The certificate store is the name of a PEM-encoded file that contains a private key and an optional certificate. |
| PEMKEY_BLOB | The certificate store is a string (base64-encoded) that contains a private key and an optional certificate. |
| PUBLIC_KEY_FILE | The certificate store is the name of a file that contains a PEM- or DER-encoded public key certificate. |
| PUBLIC_KEY_BLOB | The certificate store is a string (base-64-encoded) that contains a PEM- or DER-encoded public key certificate. |
| SSHPUBLIC_KEY_FILE | The certificate store is the name of a file that contains an SSH-style public key. |
| SSHPUBLIC_KEY_BLOB | The certificate store is a string (base-64-encoded) that contains an SSH-style public key. |
| P7BFILE | The certificate store is the name of a PKCS7 file containing certificates. |
| PPKFILE | The certificate store is the name of a file that contains a PuTTY Private Key (PPK). |
| XMLFILE | The certificate store is the name of a file that contains a certificate in XML format. |
| XMLBLOB | The certificate store is a string that contains a certificate in XML format. |
SSL Server Cert
Data Type
string
Default Value
""
Remarks
If using a TLS/SSL connection, this property can be used to specify the TLS/SSL certificate to be accepted from the server. Any other certificate that is not trusted by the machine is rejected.
This property can take the following forms:
| Description | Example |
| A full PEM Certificate (example shortened for brevity) | -----BEGIN CERTIFICATE----- MIIChTCCAe4CAQAwDQYJKoZIhv......Qw== -----END CERTIFICATE----- |
| A path to a local file containing the certificate | C:\cert.cer |
| The public key (example shortened for brevity) | -----BEGIN RSA PUBLIC KEY----- MIGfMA0GCSq......AQAB -----END RSA PUBLIC KEY----- |
| The MD5 Thumbprint (hex values can also be either space or colon separated) | ecadbdda5a1529c58a1e9e09828d70e4 |
| The SHA1 Thumbprint (hex values can also be either space or colon separated) | 34a929226ae0819f2ec14b4a3d904f801cbb150d |
If not specified, any certificate trusted by the machine is accepted.
Certificates are validated as trusted by the machine based on the System's trust store. The trust store used is the 'javax.net.ssl.trustStore' value specified for the system. If no value is specified for this property, Java's default trust store is used (for example, JAVA_HOME\lib\security\cacerts).
Use '*' to signify to accept all certificates. Note that this is not recommended due to security concerns.
Tables
Data Type
string
Default Value
""
Remarks
Listing the tables from some databases can be expensive. Providing a list of tables in the connection string improves the performance of the driver.
This property can also be used as an alternative to automatically listing views if you already know which ones you want to work with and there would otherwise be too many to work with.
Specify the tables you want in a comma-separated list. Each table should be a valid SQL identifier with any special characters escaped using square brackets, double-quotes or backticks. For example, Tables=TableA,[TableB/WithSlash],WithCatalog.WithSchema.`TableC With Space`.
Note that when connecting to a data source with multiple schemas or catalogs, you will need to provide the fully qualified name of the table in this property, as in the last example here, to avoid ambiguity between tables that exist in multiple catalogs or schemas.
Timeout
Data Type
int
Default Value
30
Remarks
If the Timeout property is set to 0, operations will not time out; instead, they will run until they complete successfully or encounter an error condition.
If Timeout expires and the operation is not yet complete, the driver raises an error condition.
Use Connection Pooling
Data Type
bool
Default Value
false
Remarks
This property enables connection pooling. The default is false. See Connection Pooling for information on using connection pools.
User
Data Type
string
Default Value
""
Remarks
Together with Password, this field is used to authenticate against the SAP HANA server.
Use SSH
Data Type
bool
Default Value
false
Remarks
Use SSH.
Use SSL
Data Type
bool
Default Value
false
Remarks
This field sets whether the driver will attempt to negotiate TLS/SSL connections to the server. By default, the driver checks the server's certificate against the system's trusted certificate store. To specify another certificate, set SSLServerCert.
Verbosity
Data Type
string
Default Value
"1"
Remarks
The verbosity level determines the amount of detail that the driver reports to the Logfile. Verbosity levels from 1 to 5 are supported. These are described in the following list:
| 1 | Setting Verbosity to 1 will log the query, the number of rows returned by it, the start of execution and the time taken, and any errors. |
| 2 | Setting Verbosity to 2 will log everything included in Verbosity 1 and additional information about the request, if applicable. |
| 3 | Setting Verbosity to 3 will additionally log the body of the request and the response. |
| 4 | Setting Verbosity to 4 will additionally log transport-level communication with the data source. This includes SSL negotiation. |
| 5 | Setting Verbosity to 5 will additionally log communication with the data source and additional details that may be helpful in troubleshooting problems. This includes interface commands. |
The Verbosity should not be set to greater than 1 for normal operation. Substantial amounts of data can be logged at higher verbosities, which can delay execution times.
Views
Data Type
string
Default Value
""
Remarks
Listing the views from some databases can be expensive. Providing a list of views in the connection string improves the performance of the driver.
This property can also be used as an alternative to automatically listing views if you already know which ones you want to work with and there would otherwise be too many to work with.
Specify the views you want in a comma-separated list. Each view should be a valid SQL identifier with any special characters escaped using square brackets, double-quotes or backticks. For example, Views=ViewA,[ViewB/WithSlash],WithCatalog.WithSchema.`ViewC With Space`.
Note that when connecting to a data source with multiple schemas or catalogs, you will need to provide the fully qualified name of the table in this property, as in the last example here, to avoid ambiguity between tables that exist in multiple catalogs or schemas.