Label*

Default Value: N/A
Example: Hive Database Account

String

Specify a unique label for the account.

Account properties*

Hostname*

Default Value: N/A
Example: 

• testdatahubh.snaplogic.com,

• cdh.clouddev.mycompany.com

String/Expression

Specify the server address to connect to. 

Port Number*

Default Value: 10000
Example: 10000

String/Expression

Specify the database server's port to connect to.

Database name*

Default Value: N/A
Example: sales_db

String/Expression

Specify the name of the database which the account is to be connected to.

Username

Default Value: N/A
Example: Snapuser 

String/Expression

Specify the username that is allowed to connect to the database. Username will be used as the default username when retrieving connections. The username must be valid in order to set up the data source.

Password

Default Value: N/A
Example: Sn@pUser.3

String/Expression

Specify the password used to connect to the data source. Password will be used as the default when retrieving connections. The password must be valid in order to set up the data source.

JDBC URL

Default Value: N/A
Example: jdbc:hive://hostname/dbname:sasl.qop=auth-int

String/Expression

Specify the URL of the JDBC database.

JDBC JARs

Use this fieldset to specify the JDBC JAR files to be loaded. A different driver binary for a driver must have a different name, the same name can not be reused for a different driver. If this property is left blank, a default JDBC driver will be loaded.

Enter the following JDBC jars to configure the Hive Database account for the respective cluster.

For HDP

• Hive-jdbc-2.0.0.2.3.5.0-81-standalone.jar

• Zookeeper-3.4.6.jar (Use this for setting up Hive with Zookeeper)

For CDH

• hive_metastore.jar

• hive_service.jar

• hiveJDBC4.jar

• libfb303-0.9.0.jar

• libthrift-0.9.0.jar

• TCLIServiceClient.jar

• Zookeeper-3.3.6.jar (Use this for setting up Hive with Zookeeper)

JDBC Driver Class*

Default Value: org.apache.hive.jdbc.HiveDriver
Example: jdbc:hive://hostname/dbname:sasl.qop=auth-int

String

Specify the JDBC Driver class name. 

For HDP Clusters

Enter the following value: org.apache.hive.jdbc.HiveDriver

For CDH Clusters

Enter the following value: com.cloudera.hive.jdbc4.HS2Driver

Advanced properties

Auto commit

Default Value: Selected

Checkbox/Expressions

Select this checkbox to commit a batch immediately after it is executed. If the Snap fails, only the batch being executed at that moment is rolled back. When deselected, the Snap execution output is committed only after all the batches are executed. If the Snap fails, the entire transaction is rolled back, unless the Snap finds invalid input data before it sends the insert request to the server, and routes the error documents to the Error view.

Batch size*

Default Value: 50
Example: 10

Integer/Expressions

Specify the number of statements to execute at a time. Using a large batch size could use up the JDBC placeholder limit of 2100.

Fetch size*

Default Value: 100
Example: 100

Integer/Expressions

Specify the number of rows to fetch at a time when executing a query. Large values could cause the server to run out of memory.

Max pool size*

Default Value: 50
Example: 10

Integer/Expressions

Specify the maximum number of idle connections a pool will maintain at a time.

Max lifetime (minutes)*

Default Value: 30
Example: 30

Integer/Expressions

Specify the maximum lifetime of a connection in the pool. Ensure that the value you enter is a few seconds shorter than any database or infrastructure-imposed connection time limit. A value of 0 indicates an infinite lifetime, subject to the Idle Timeout value. An in-use connection is never retired. Connections are removed only after they are closed.

Idle Timeout (minutes)*

Default Value: 5

Integer/Expressions

Specify the maximum amount of time a connection is allowed to sit idle in the pool. A value of 0 indicates that idle connections are never removed from the pool.

Checkout timeout (milliseconds)*

Default Value: 10000
Example: 10000

Integer/Expressions

Specify the number of milliseconds to wait for a connection to be available in the pool. Zero waits forever. After set time, then an exception will be thrown and the pipeline will fail.

Url properties

Use this fieldset to specify properties to use in JDBC Url. These properties will need to be configured when setting up SSL connection. See Advanced Configurations: Configuring Hive with SSL section below for details.

Url property name

Default Value: N/A
Example: maxAllowedPacket 

String/Expression

Specify a name for the URL property to be used by the account.

Url property value

Default Value: N/A
Example: 1000

String/Expression

Specify a value for the URL property name.

Hadoop properties

Authentication method*

Default Value: None
Example: Kerberos

Dropdown list

Select the Authentication method to use when connecting to the Hadoop service.  

• None: Allows connection even without the Username and Password

• Kerberos: Allows connection with Kerberos details such as Client Principal, Keytab file, and Service principal

• User ID: Allows connection with Username only

• User ID and Password: Allows connection with Username and Password

• User ID and Password with SSL: Allows SSL connections with Username and Password. Ensure that you have installed SSL certificates in the JCC node. 

Use Zookeeper 

Default Value: Deselected

Checkbox

Select if Zookeeper be used to locate the Hadoop service instead of a specific hostname. If the checkbox is selected, use Zookeeper to resolve the location of the database instead of using the hostname field in the standard block.

Zookeeper URL

Default Value: N/A
Example: hostname1:port,hostname2:port/;serviceDiscoveryMode=zooKeeper;zooKeeperNamespace=hiveserver2

String

Specify the URL of the Zookeeper service. Zookeeper URL formats are different for CDH and HDP. If you intend to use Zookeeper, then you must provide the following details:

• For HDP:

  • Format: hostname1:port,hostname2:port/;serviceDiscoveryMode=zooKeeper;zooKeeperNamespace=hiveserver2

  • Example: na77sl-ihdc-ux02011.clouddev.snaplogic.com:2181,na77sl-ihdc-ux02012.clouddev.snaplogic.com:2181,na77sl-ihdc-ux02013.clouddev.snaplogic.com:2181/;serviceDiscoveryMode=zooKeeper;zooKeeperNamespace=hiveserver2

• For CDH:

  • Format: zk=hostname1:port,hostname2:port/hiveserver2

  • Example: Zk = jdbc:hive2://cdhclusterqa-1-1.cloudev.snaplogic.com:2181.cdhclusterqa-1-2.clouddev.snaplogic.com:2181.cdhclusterqa-1-3.clouddev.smaplogic.com:2181/hiveserver2

Hive properties

JDBC Subprotocol*

Default Value: Hive
Example: Impala

Dropdown list

Specify the JDBC Subprotocol to be used. This is required when the Authentication method is Kerberos. Available options are:

• Hive

• Impala

Kerberos properties

Use this fieldset to configure information required for the Kerberos authentication. These properties must be configured if you select Kerberos in the Authentication method property.

Client Principal

Default Value: N/A
Example: hiveclient@EXAMPLE.COM

String

Specify the principal used to authenticate to Kerberos KDC (Kerberos Key Distribution Center - Network service used by the clients and servers for authentication). 

Keytab File

Default Value: N/A
Example:  etc/krb5.keytab

String

Specify the Keytab file (file used to store encryption keys) used to authenticate to Kerberos KDC.

Service Principal

Default Value: N/A
Example:  hive/host@REALM or impala/host@REALM

String

Specify the principal used by an instance of a service.

Examples: 

• If you are connecting to a specific server: hive/host@REALM or impala/host@REALM

• If you are connecting(more common for the Snap) to any compliant host (see Use Zookeeper property's description) in which case the principal is: 'hive/_HOST@REALM' or 'impala/_HOST@REALM'.