Create a connection to Apache Hive by navigating to the Connections page in the Sync App application and selecting the corresponding icon in the Add Connections panel. If the Apache Hive icon is not available, click the Add More icon to download and install the Apache Hive connector from the CData site.

Required properties are listed under the Settings tab. The Advanced tab lists connection properties that are not typically required.

Connecting to Apache Hive

Self-hosted Instances

Specify the following to establish a connection with Apache Hive:

• TransportMode: The transport mode to use to communicate with the Hive server. Accepted entries are BINARY and HTTP. BINARY is selected by default.
• Server: Set this to the host name or IP address of the server hosting HiveServer2.
• Port: Set this to the port for the connection to the HiveServer2 instance.
• UseSSL (optional): Set this to enable TLS/SSL.

Amazon EMR Instances

Connections to Amazon EMR will need to be established using an SSH tunnel.

Use the following procedure to create an SSH tunnel to EMR.

1. To begin, you will need an active EMR cluster and an EC2 key pair. The key pair can be in .ppk or .pem format.
2. Next, authorize inbound traffic in your cluster settings.

Set the following to connect (while running an active tunnel session to EMR):

• Server: Set this to the master node (master-public-dns-name) where the Apache Hive server is running.
• Port: Set this to the port required to connect to Apache Hive.
• UseSSH: Set this to true.
• SSHServer: Set this to the master node (master-public-dns-name).
• SSHPort: Set this to 22.
• SSHAuthMode: Set this to PUBLIC_KEY.
• SSHUser: Set this to hadoop.
• SSHClientCert: Set this to the full path to the key file.
• SSHClientCertType: Set this to type that corresponds to the key file. Typically either PEMKEY_FILE or PPKFILE.

Hadoop Cluster on Azure HDInsight Instances

You will need to supply the following to establish a connection to a Hadoop cluster hosted on Azure HDInsight:

• User: Set this to the cluster username that you specified when creating the cluster on Azure HDInsight.
• Password: Set this to the cluster password that you specified when creating the cluster on Azure HDInsight.
• Server: The server corresponding to your cluster. For example: myclustername.azurehdinsight.net.
• Port: Set this to the port running HiveServer2. This will be 443 by default.
• HTTPPath: Set this to the HTTP path for the hive2 service. This will be hive2 by default.
• TransportMode: Set this to HTTP.
• UseSSL: Set this to true.
• QueryPassthrough (optional): Set QueryPassthrough to true to bypass the SQL engine of the Sync App and execute HiveQL queries directly to Apache Hive.

Google DataProc Instances

Before Connecting

Ensure that the Apache Hive server on DataProc was created with the DataProc Component Gateway enabled.

Next, obtain the external IP address of the Hive Cluster. To find this, load up the Cloud Shell and list the instances.

gcloud compute instances list

Note the external IP of the relevant machine.

Build an SSH Tunnel to the Hive Cluster Web Interface

Navigate to the Hive cluster on DataProc and select the WEB INTERFACES tab. Select Create an SSH tunnel to connect to a web interface.

A cloud console command will be shown that can be used to create an SSH key pair. Download the private key from the directory specified in the console.

Configure the SSH tunnel in an SSH utility:

• Host Name: Set this to the external IP noted above.
• Port: 22
• Point the tool to your private SSH key.
• For the Tunnel, map an open port to localhost:10000. localhost will be resolved properly on the server.

Connecting to Hive on Google DataProc

Specify the following information to connect to Apache Hive:

• TransportMode: Set this to BINARY.
• AuthScheme: Set this to Plain.
• Port: Set this to the chosen SSH Tunnel port on the local machine.

Authenticating to Apache Hive

PLAIN

Set AuthScheme to PLAIN when the hive.server2.authentication property is set to None (uses Plain SASL), PAM, or CUSTOM. In addition, set the following connection properties:

• User: Set this to user to login as. If nothing is set, 'anonymous' will be sent instead.
• Password: Set this to the password of the user. If nothing is set, 'anonymous' will be sent instead.

LDAP

Set AuthScheme to LDAP when the hive.server2.authentication property is set to LDAP. In addition, set the following connection properties:

• User: Set this to user to login as.
• Password: Set this to the password of the user.

NOSASL

Set AuthScheme to NOSASL when the hive.server2.authentication property is set to NOSASL. There are no user credentials submitted in this auth scheme.

Kerberos

Set AuthScheme to Kerberos when the hive.server2.authentication property is set to Kerberos. Please see Using Kerberos for details on how to authenticate with Kerberos.

Apache Hive supports multiple ways to perform similar operations. The options below allow you to configure which HiveQL statement is issued to perform an operation.

• UseShowDatabasesQuery: When this property is set to true, the Sync App will issue a "SHOW DATABASES" query (instead of using the Thrift API) to retrieve the available schemas.
• UseShowTablesQuery: When this property is set to true, the Sync App will issue a "SHOW TABLES" query (instead of using the Thrift API) to retrieve the available tables.
• UseInsertSelectSyntax: This option is specified via the Other property.

  When set to true, the Sync App will perform INSERT queries using the INSERT INTO SELECT syntax: INSERT INTO TABLE [table] SELECT T.* FROM (....) AS T.

  If set to false (default), the INSERT INTO VALUES syntax will be used: INSERT INTO TABLE [table] VALUES (....).

  The Sync App will automatically determine which syntax is supported by your Hive server but setting this option to true will force the INSERT INTO SELECT syntax to be used.

This section shows how to use the Sync App to authenticate using Kerberos.

Kerberos

To authenticate to Apache Hive using Kerberos, set the following properties:

• KerberosKDC: Set this to the host name or IP Address of your Kerberos KDC machine.
• KerberosSPN: Set this to the service and host of the Apache Hive Kerberos Principal. This is the value prior to the '@' symbol (for instance, ServiceName/MyHost) of the principal value (for instance, ServiceName/[email protected]).

Retrieve the Kerberos Ticket

You can use one of the following options to retrieve the required Kerberos ticket.

MIT Kerberos Credential Cache File

This option enables you to use the MIT Kerberos Ticket Manager or kinit command to get tickets. Note that you do not need to set the User or Password connection properties with this option.

1. Ensure that you have an environment variable created called KRB5CCNAME.
2. Set the KRB5CCNAME environment variable to a path pointing to your credential cache file (for instance, C:\krb_cache\krb5cc_0 or /tmp/krb5cc_0). This file is created when generating your ticket with MIT Kerberos Ticket Manager.
3. To obtain a ticket, open the MIT Kerberos Ticket Manager application, click Get Ticket, enter your principal name and password, then click OK. If successful, ticket information appears in Kerberos Ticket Manager and is stored in the credential cache file.
4. Now that you have created the credential cache file, the Sync App uses the cache file to obtain the Kerberos ticket to connect to Apache Hive.

As an alternative to setting the KRB5CCNAME environment variable, you can directly set the file path using the KerberosTicketCache property. When set, the Sync App uses the specified cache file to obtain the Kerberos ticket to connect to Apache Hive.

Keytab File

If the KRB5CCNAME environment variable has not been set, you can retrieve a Kerberos ticket using a Keytab File. To do so, set the User property to the desired username and set the KerberosKeytabFile property to a file path pointing to the keytab file associated with the user.

User and Password

If both the KRB5CCNAME environment variable and the KerberosKeytabFile property have not been set, you can retrieve a ticket using a user and password combination. To do this, set the User and Password properties to the user/password combination that you use to authenticate with Apache Hive.

Cross-Realm

More complex Kerberos environments may require cross-realm authentication where multiple realms and KDC servers are used (e.g., where one realm/KDC is used for user authentication and another realm/KDC is used for obtaining the service ticket).

In such an environment, set the KerberosRealm and KerberosKDC properties to the values required for user authentication. Also set the KerberosServiceRealm and KerberosServiceKDC properties to the values required to obtain the service ticket.

This section details a selection of advanced features of the Apache Hive Sync App.

User Defined Views

The Sync App allows you to define virtual tables, called user defined views, whose contents are decided by a pre-configured query. These views are useful when you cannot directly control queries being issued to the drivers. See User Defined Views for an overview of creating and configuring custom views.

SSL Configuration

Use SSL Configuration to adjust how Sync App handles TLS/SSL certificate negotiations. You can choose from various certificate formats; see the SSLServerCert property under "Connection String Options" for more information.

Firewall and Proxy

Configure the Sync App for compliance with Firewall and Proxy, including Windows proxies and HTTP proxies. You can also set up tunnel connections.

Query Processing

The Sync App offloads as much of the SELECT statement processing as possible to Apache Hive and then processes the rest of the query in memory (client-side).

See Query Processing for more information.

Logging

See Logging for an overview of configuration settings that can be used to refine CData logging. For basic logging, you only need to set two connection properties, but there are numerous features that support more refined logging, where you can select subsets of information to be logged using the LogModules connection property.

Customizing the SSL Configuration

By default, the Sync App attempts to negotiate SSL/TLS by checking the server's certificate against the system's trusted certificate store.

To specify another certificate, see the SSLServerCert property for the available formats to do so.

Connecting Through a Firewall or Proxy

HTTP Proxies

To connect through the Windows system proxy, you do not need to set any additional connection properties. To connect to other proxies, set ProxyAutoDetect to false.

In addition, to authenticate to an HTTP proxy, set ProxyAuthScheme, ProxyUser, and ProxyPassword, in addition to ProxyServer and ProxyPort.

Other Proxies

Set the following properties:

• To use a proxy-based firewall, set FirewallType, FirewallServer, and FirewallPort.
• To tunnel the connection, set FirewallType to TUNNEL.
• To authenticate, specify FirewallUser and FirewallPassword.
• To authenticate to a SOCKS proxy, additionally set FirewallType to SOCKS5.

The Sync App models Apache Hive instances as relational databases. Hive versions 0.11.0 and above are supported. The Sync App leverages the HiveServer2 Thrift API, to enable bidirectional access to Apache Hive data through SQL.

Discovering Schemas

The connection string properties are the various options that can be used to establish a connection. This section provides a complete list of the options you can configure in the connection string for this provider. Click the links for further details.

Authentication

Kerberos

SSL

SSH

Firewall

Proxy

Logging

Schema

Miscellaneous

This section provides a complete list of the Authentication properties you can configure in the connection string for this provider.

The authentication scheme used. Accepted entries are Plain, LDAP, NoSasl, and Kerberos.

Remarks

The AuthScheme used to authenticate with Hive.

The host name or IP address of the server hosting HiveServer2.

Remarks

If multiple Hive servers are available, FailoverHosts can be set to the additional Hive instances.

If UseZookeeperDiscovery is set to True, Server and Port must be set to the ZooKeeper server.

The port for the connection to the HiveServer2 instance.

Remarks

When using BINARY TransportMode, this property should be set to the value in the 'hive.server2.thrift.port' property of the Hive configuration file (hive-site.xml).

When using HTTP TransportMode, this property should be set to the value in the 'hive.server2.thrift.http.port' property of the Hive configuration file (hive-site.xml).

The username used to authenticate with Hive.

Remarks

The username used to authenticate with Hive.

The password used to authenticate with Hive.

Remarks

The password used to authenticate with Hive.

The Protocol Version used to authenticate with Hive.

Remarks

The most efficient protocol version will be determined automatically by the CData Sync App upon connecting to Hive. This property allows you to explicitly specify the version to use and overrides the version determined by the CData Sync App.

The transport mode to use to communicate with the Hive server. Accepted entries are BINARY and HTTP.

Remarks

The transport mode used to communicate with the Hive server.

This property should be set to the 'hive.server2.transport.mode' value specified in your Hive configuration file (hive-site.xml).

The name of the Hive database to use by default.

Remarks

When specified, the CData Sync App will issue a 'USE [Database]' command upon connecting to Hive. This will be the database schema used when executing queries that do not have a schema explicitly specified.

To execute queries to other schemas, the schema can be explicitly specified in the statement.

When Database is not set, the 'default' database schema will be used (no 'USE' statement is issued to Hive in this case).

The proxy user of the Hive user impersonation.

Remarks

The proxy user of the Hive user impersonation.

Quality of protection for the SASL framework. The level of quality is negotiated between the client and server during authentication. Used by Kerberos authentication with TCP transport.

Remarks

This property should be set to the 'hive.server2.thrift.sasl.qop' value specified in your Hive configuration file (hive-site.xml).

This section provides a complete list of the Kerberos properties you can configure in the connection string for this provider.

The Kerberos Key Distribution Center (KDC) service used to authenticate the user.

Remarks

The Kerberos properties are used when using SPNEGO or Windows Authentication. The Sync App will request session tickets and temporary session keys from the Kerberos KDC service. The Kerberos KDC service is conventionally colocated with the domain controller.

If Kerberos KDC is not specified, the Sync App will attempt to detect these properties automatically from the following locations:

• KRB5 Config File (krb5.ini/krb5.conf): If the KRB5_CONFIG environment variable is set and the file exists, the Sync App will obtain the KDC from the specified file. Otherwise, it will attempt to read from the default MIT location based on the OS: C:\ProgramData\MIT\Kerberos5\krb5.ini (Windows) or /etc/krb5.conf (Linux).
• Domain Name and Host: If the Kerberos Realm and Kerberos KDC could not be inferred from another location, the Sync App will infer them from the configured domain name and host.

The Kerberos Realm used to authenticate the user.

Remarks

The Kerberos properties are used when using SPNEGO or Windows Authentication. The Kerberos Realm is used to authenticate the user with the Kerberos Key Distribution Service (KDC). The Kerberos Realm can be configured by an administrator to be any string, but conventionally it is based on the domain name.

If Kerberos Realm is not specified, the Sync App will attempt to detect these properties automatically from the following locations:

• KRB5 Config File (krb5.ini/krb5.conf): If the KRB5_CONFIG environment variable is set and the file exists, the Sync App will obtain the default realm from the specified file. Otherwise, it will attempt to read from the default MIT location based on the OS: C:\ProgramData\MIT\Kerberos5\krb5.ini (Windows) or /etc/krb5.conf (Linux)
• Domain Name and Host: If the Kerberos Realm and Kerberos KDC could not be inferred from another location, the Sync App will infer them from the user-configured domain name and host. This might work in some Windows environments.

The service principal name (SPN) for the Kerberos Domain Controller.

Remarks

If the SPN on the Kerberos Domain Controller is not the same as the URL that you are authenticating to, use this property to set the SPN.

The Keytab file containing your pairs of Kerberos principals and encrypted keys.

Remarks

The Keytab file containing your pairs of Kerberos principals and encrypted keys.

The Kerberos realm of the service.

Remarks

The KerberosServiceRealm is the specify the service Kerberos realm when using cross-realm Kerberos authentication.

In most cases, a single realm and KDC machine are used to perform the Kerberos authentication and this property is not required.

This property is available for complex setups where a different realm and KDC machine are used to obtain an authentication ticket (AS request) and a service ticket (TGS request).

The Kerberos KDC of the service.

Remarks

The KerberosServiceKDC is used to specify the service Kerberos KDC when using cross-realm Kerberos authentication.

In most cases, a single realm and KDC machine are used to perform the Kerberos authentication and this property is not required.

This property is available for complex setups where a different realm and KDC machine are used to obtain an authentication ticket (AS request) and a service ticket (TGS request).

The full file path to an MIT Kerberos credential cache file.

Remarks

This property can be set if you wish to use a credential cache file that was created using the MIT Kerberos Ticket Manager or kinit command.

This section provides a complete list of the SSL properties you can configure in the connection string for this provider.

The TLS/SSL client certificate store for SSL Client Authentication (2-way SSL).

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:

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

The type of key store containing the TLS/SSL client certificate.

Remarks

This property can take one of the following values:

The password for the TLS/SSL client certificate.

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.

The subject of the TLS/SSL client certificate.

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, [email protected]". The common fields and their meanings are shown below.

If a field value contains a comma, it must be quoted.

The certificate to be accepted from the server when connecting using TLS/SSL.

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:

If not specified, any certificate trusted by the machine is accepted.

Use '*' to signify to accept all certificates. Note that this is not recommended due to security concerns.

This section provides a complete list of the SSH properties you can configure in the connection string for this provider.

The authentication method to be used to log on to an SFTP server.

Remarks

• None: No authentication will be performed. The current User value is ignored, and the connection will be logged in as anonymous.
• Password: The Sync App will use the values of User and Password to authenticate the user.
• Public_Key: The Sync App 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.

A private key to be used for authenticating the user.

Remarks

SSHClientCert must contain a valid private key in order to use public key authentication. A public key is optional, if one is not included then the Sync App generates it from the private key. The Sync App sends the public key to the server and the connection is allowed if the user has authorized the public key.

The SSHClientCertType field specifies the type of the key store specified by SSHClientCert. If the store is password protected, specify the password in SSHClientCertPassword.

Some types of key stores are containers which may include multiple keys. By default the Sync App will select the first key in the store, but you can specify a specific key using SSHClientCertSubject.

The password of the SSHClientCert key if it has one.

Remarks

This property is only used when authenticating to SFTP servers with SSHAuthMode set to PublicKey and SSHClientCert set to a private key.

The subject of the SSH client certificate.

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, [email protected]". Common fields and their meanings are displayed below.

If a field value contains a comma it must be quoted.

The type of SSHClientCert private key.

Remarks

This property can take one of the following values:

The SSH server.

Remarks

The SSH server.

The SSH port.

Remarks

The SSH port.

The SSH user.

Remarks

The SSH user.

The SSH password.

Remarks

The SSH password.

The SSH server fingerprint.

Remarks

The SSH server fingerprint.

Whether to tunnel the Apache Hive connection over SSH. Use SSH.

Remarks

By default the Sync App will attempt to connect directly to Apache Hive. When this option is enabled, the Sync App will instead establish an SSH connection with the SSHServer and tunnel the connection to Apache Hive through it.

This section provides a complete list of the Firewall properties you can configure in the connection string for this provider.

The protocol used by a proxy-based firewall.

Remarks

This property specifies the protocol that the Sync App will use to tunnel traffic through the FirewallServer proxy. Note that by default, the Sync App connects to the system proxy; to disable this behavior and connect to one of the following proxy types, set ProxyAutoDetect to false.

To connect to HTTP proxies, use ProxyServer and ProxyPort. To authenticate to HTTP proxies, use ProxyAuthScheme, ProxyUser, and ProxyPassword.

The name or IP address of a proxy-based firewall.

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. Use ProxyServer to connect to an HTTP proxy.

Note that the Sync App uses the system proxy by default. To use a different proxy, set ProxyAutoDetect to false.

The TCP port for a proxy-based firewall.

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.

The user name to use to authenticate with a proxy-based firewall.

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.

A password used to authenticate to a proxy-based firewall.

Remarks

This property is passed to the proxy specified by FirewallServer and FirewallPort, following the authentication method specified by FirewallType.

This section provides a complete list of the Proxy properties you can configure in the connection string for this provider.

This indicates whether to use the system proxy settings or not. This takes precedence over other proxy settings, so you'll need to set ProxyAutoDetect to FALSE in order use custom proxy settings.

Remarks

This takes precedence over other proxy settings, so you'll need to set ProxyAutoDetect to FALSE in order use custom proxy settings.

To connect to an HTTP proxy, see ProxyServer. For other proxies, such as SOCKS or tunneling, see FirewallType.

The hostname or IP address of a proxy to route HTTP traffic through.

Remarks

The hostname or IP address of a proxy to route HTTP traffic through. The Sync App can use the HTTP, Windows (NTLM), or Kerberos authentication types to authenticate to an HTTP proxy.

If you need to connect through a SOCKS proxy or tunnel the connection, see FirewallType.

By default, the Sync App uses the system proxy. If you need to use another proxy, set ProxyAutoDetect to false.

The TCP port the ProxyServer proxy is running on.

Remarks

The port the HTTP proxy is running on that you want to redirect HTTP traffic through. Specify the HTTP proxy in ProxyServer. For other proxy types, see FirewallType.

The authentication type to use to authenticate to the ProxyServer proxy.

Remarks

This value specifies the authentication type to use to authenticate to the HTTP proxy specified by ProxyServer and ProxyPort.

Note that the Sync App will use the system proxy settings by default, without further configuration needed; if you want to connect to another proxy, you will need to set ProxyAutoDetect to false, in addition to ProxyServer and ProxyPort. To authenticate, set ProxyAuthScheme and set ProxyUser and ProxyPassword, if needed.

The authentication type can be one of the following:

• BASIC: The Sync App performs HTTP BASIC authentication.
• DIGEST: The Sync App performs HTTP DIGEST authentication.
• NEGOTIATE: The Sync App retrieves an NTLM or Kerberos token based on the applicable protocol for authentication.
• PROPRIETARY: The Sync App does not generate an NTLM or Kerberos token. You must supply this token in the Authorization header of the HTTP request.

If you need to use another authentication type, such as SOCKS 5 authentication, see FirewallType.

A user name to be used to authenticate to the ProxyServer proxy.

Remarks

The ProxyUser and ProxyPassword options are used to connect and authenticate against the HTTP proxy specified in ProxyServer.

You can select one of the available authentication types in ProxyAuthScheme. If you are using HTTP authentication, set this to the user name of a user recognized by the HTTP proxy. If you are using Windows or Kerberos authentication, set this property to a user name in one of the following formats:

user@domain
domain\user

A password to be used to authenticate to the ProxyServer proxy.

Remarks

This property is used to authenticate to an HTTP proxy server that supports NTLM (Windows), Kerberos, or HTTP authentication. To specify the HTTP proxy, you can set ProxyServer and ProxyPort. To specify the authentication type, set ProxyAuthScheme.

If you are using HTTP authentication, additionally set ProxyUser and ProxyPassword to HTTP proxy.

If you are using NTLM authentication, set ProxyUser and ProxyPassword to your Windows password. You may also need these to complete Kerberos authentication.

For SOCKS 5 authentication or tunneling, see FirewallType.

By default, the Sync App uses the system proxy. If you want to connect to another proxy, set ProxyAutoDetect to false.

The SSL type to use when connecting to the ProxyServer proxy.

Remarks

This property determines when to use SSL for the connection to an HTTP proxy specified by ProxyServer. This value can be AUTO, ALWAYS, NEVER, or TUNNEL. The applicable values are the following:

A semicolon separated list of destination hostnames or IPs that are exempt from connecting through the ProxyServer .

Remarks

The ProxyServer is used for all addresses, except for addresses defined in this property. Use semicolons to separate entries.

Note that the Sync App uses the system proxy settings by default, without further configuration needed; if you want to explicitly configure proxy exceptions for this connection, you need to set ProxyAutoDetect = false, and configure ProxyServer and ProxyPort. To authenticate, set ProxyAuthScheme and set ProxyUser and ProxyPassword, if needed.

This section provides a complete list of the Logging properties you can configure in the connection string for this provider.

Core modules to be included in the log file.

Remarks

Only the modules specified (separated by ';') will be included in the log file. By default all modules are included.

See the Logging page for an overview.

This section provides a complete list of the Schema properties you can configure in the connection string for this provider.

A path to the directory that contains the schema files defining tables, views, and stored procedures.

Remarks

The path to a directory which contains the schema files for the Sync App (.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\\ApacheHive Data Provider\\Schema" with %APPDATA% being set to the user's configuration directory:

This property restricts the schemas reported to a subset of the available schemas. For example, BrowsableSchemas=SchemaA,SchemaB,SchemaC.

Remarks

Listing the schemas from databases can be expensive. Providing a list of schemas in the connection string improves the performance.

This property restricts the tables reported to a subset of the available tables. For example, Tables=TableA,TableB,TableC.

Remarks

Listing the tables from some databases can be expensive. Providing a list of tables in the connection string improves the performance of the Sync App.

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.

Restricts the views reported to a subset of the available tables. For example, Views=ViewA,ViewB,ViewC.

Remarks

Listing the views from some databases can be expensive. Providing a list of views in the connection string improves the performance of the Sync App.

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.

This section provides a complete list of the Miscellaneous properties you can configure in the connection string for this provider.

The timeout for asynchronous requests issued by the provider to download large result sets.

Remarks

If the AsyncQueryTimeout property is set to 0, asynchronous operations will not time out; instead, they will run until they complete successfully or encounter an error condition. This property is distinct from Timeout which applies to individual operations while AsyncQueryTimeout applies to execution time of the operation as a whole.

If AsyncQueryTimeout expires and the asynchronous request has not finished being processed, the Sync App raises an error condition.

This option specifies whether to add the X-XSRF-Header filter to HiveServer2 HTTP mode.

Remarks

This option specifies whether to add the X-XSRF-Header filter to HiveServer2 HTTP mode.

This property allows you to specify a list of failover hosts in addition to the one configured in Server and Port .

Remarks

Specify both a server and port with multiple values separated with commas. For example 'server1:port1,server2,port2'.

The Server and Port values take precedence and will be used to make the initial connection. Upon a failure to connect to the main host, the list of FailoverHosts will be used to attempt the connection.

The path component of the URL endpoint when using HTTP TransportMode.

Remarks

This property is used to specify the path component of the URL endpoint when using HTTP TransportMode.

This property should be set to the value specified in the 'hive.server2.thrift.http.path' property of you Hive configuration file (hive-site.xml).

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.

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.

These hidden properties are used only in specific use cases.

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

The maximum number of results to return per page from Apache Hive.

Remarks

The Pagesize property affects the maximum number of results to return per page from Apache Hive. Setting a higher value may result in better performance at the cost of additional memory allocated per page consumed.

This property indicates whether or not to include pseudo columns as columns to the table.

Remarks

This setting is particularly helpful in Entity Framework, which does not allow you to set a value for a pseudo column unless it is a table column. The value of this connection setting is of the format "Table1=Column1, Table1=Column2, Table2=Column3". You can use the "*" character to include all tables and all columns; for example, "*=*".

This option passes the query to the Apache Hive server as is.

Remarks

When this is set, queries are passed through directly to Apache Hive.

A list of server configuration variables to override the server defaults.

Remarks

This property takes a comma-separated list of Hive configuration variables. Each value in the comma-separated list will be sent as specified to the Hive server via the 'set' command to override the server default values.

Example: hive.enforce.bucketing=true,hive.enforce.sorting=true

The value in seconds until the timeout error is thrown, canceling the operation.

Remarks

If Timeout = 0, operations do not time out. The operations run until they complete successfully or until they encounter an error condition.

If Timeout expires and the operation is not yet complete, the Sync App throws an exception.

This option specifies whether the columns will be retrieved using a DESC TABLE query or the GetColumns Thrift API.

Remarks

When set to true, a DESC TABLE query will be issued to retrieve the columns for the table.

A filepath pointing to the JSON configuration file containing your custom views.

Remarks

User Defined Views are defined in a JSON-formatted configuration file called UserDefinedViews.json. The Sync App automatically detects the views specified in this file.

You can also have multiple view definitions and control them using the UserDefinedViews connection property. When you use this property, only the specified views are seen by the Sync App.

This User Defined View configuration file is formatted as follows:

• Each root element defines the name of a view.
• Each root element contains a child element, called query, which contains the custom SQL query for the view.

For example:

{
	"MyView": {
		"query": "SELECT * FROM [CData].[Default].Customers WHERE MyColumn = 'value'"
	},
	"MyView2": {
		"query": "SELECT * FROM MyTable WHERE Id IN (1,2,3)"
	}
}

"UserDefinedViews", "C:\\Users\\yourusername\\Desktop\\tmp\\UserDefinedViews.json"

This option specifies whether the schemas will be retrieved using a SHOW DATABASES query or the GetSchemas Thrift API.

Remarks

When set to true, a SHOW DATABASES query will be issued to retrieve the schemas.

This option specifies whether the tables will be retrieved using a SHOW TABLES query or the GetTables Thrift API.

Remarks

When set to true, a SHOW TABLES query will be issued to retrieve the tables for the database.

Specifies whether to use SSL Encryption when connecting to Hive.

Remarks

Set this property to the value specified in the 'hive.server2.use.SSL' property of your Hive configuration file (hive-site.xml).

Specifies whether to use ZooKeeper Service Discovery.

Remarks

When set to 'True', Hive servers will be discovered via the ZooKeeper service.

The Server and Port must be set to the configured ZooKeeper server and port. The ZookeeperNamespace property must be set to the namespace configured on ZooKeeper for the Hive Server 2 znodes.

If multiple ZooKeeper servers are available, FailoverHosts can be set to the additional ZooKeeper instances.

The namespace configured on ZooKeeper for the Hive Server 2 znodes.

Remarks

This property is only applicable when UseZookeeperDiscovery is set to 'True'.