You can create an account from Designer or Manager. In Designer, when working on pipelines, every Snap that needs an account prompts you to create a new account or use an existing account. The accounts can be created in or used from:

• Your private project folder: This folder contains the pipelines that will use the account.
• Your Project Space’s shared folder: This folder is accessible to all the users that belong to the Project Space.
• The global shared folder: This folder is accessible to all the users within an organization in the SnapLogic instance.

Account Configuration

In Manager, you can navigate to the required folder and create an account in it (see Accounts). To create an account for a generic JDBC driver: 

1. If you have not already done so, upload the JDBC driver for this database as a file for the specific project.
2. Click Create, then select Hive > Hive Database Account or Generic Hive Database Account (as required).
3. Supply an account label.
4. Supply the necessary properties for your database. 
5. Supply the necessary JDBC driver jars for your driver.
6. (Optional) Supply additional information on this account in the Notes field of the Info tab.
7. Click Apply. 

Account Types 

Hive Database Account

Account Settings

Label

Account properties

Hostname

Required. The server address to connect to. 

Default value: [None]

Port number

Required. The database server's port to connect to.

Default value: 10000

Database name
 

Username

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.

Example: Snapuser 

Default value: [None]

Password

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

Example: Snapuser 

Default value: [None]

JDBC jars

List of 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.

Example: hiveJDBC4 

Default value: [None]

JDBC Driver Class

Required. The JDBC Driver class name to use.

Example: com.vertica.jdbc.Driver

Default value: [None]

Advanced Properties

Auto commit
optional

Default value: Selected

Batch size

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

Example: 10

Default value: 50

Fetch size

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

Example: 100

Default value: 100

Max pool size

Required. Maximum number of idle connections a pool will maintain at a time.

Example: 10

Default value: 50

Max idle time
 

Required. Minutes a connection can exist in the pool before it is destroyed.

Example: 30

Default value: 30

Idle connection Test period
 

Checkout timeout
 

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

Example: 10000

Default value: 10000

Url Properties

Properties to use in JDBC Url.

Example: maxAllowedPacket | 1000

Default value: [None]

Authentication method

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

Default value: None

Use Zookeeper 

Specifies if Zookeeper be used to locate the Hadoop service instead of a specific hostname.

If the checkbox is selected, we should use Zookeeper to resolve the location of the database instead of using the 'hostname' field in the standard block.

Default value: Not selected

Zoo Keeper URL
 

If you intend to use Zookeeper, then you must provide the following details:

You must provide the URL of the Zookeeper service. Zookeeper URL formats are different for CDH and HDP.

• 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.clouddev.snaplogic.com:2181,cdhclusterqa-1-2.clouddev.snaplogic.com:2181,cdhclusterqa-1-3.clouddev.snaplogic.com:2181/hiveserver2

Default value: None

JDBC Subprotocol

Required. JDBC Subprotocol to be used. The options available are Hive and Impala.

Default value: Hive

Client Principal

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

Default value: [None]

Keytab file

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

Default value: [None]

Service principal

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

Default value: [None]

Generic Hive Database Account

Account Settings

Label

Account properties

Username

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.

This is not a mandatory field.

Example: Snapuser 

Default value: [None]

Password

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

This is not a mandatory field.

Example: Snapuser 

Default value: [None]

JDBC URL

The URL of the JDBC database.

Example: jdbc:hive://hostname/dbname;sasl.qop=auth-int

Default value: [None]

JDBC jars

List of 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.

Example: hiveJDBC4 

JDBC Driver Class

Required. The JDBC Driver class name to use.

Example: com.vertica.jdbc.Driver

Default value: [None]

Advanced Properties

           Auto commit

Default value: Selected

Batch size

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

Example: 10

Default value: 50

Fetch size

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

Example: 100

Default value: 100

Max pool size

Required. Maximum number of idle connections a pool will maintain at a time.

Example: 10

Default value: 50

Required. Minutes a connection can exist in the pool before it is destroyed.

Example: 30

Default value: 30

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

Example: 10000

Default value: 10000

Url Properties

Properties to use in JDBC Url. These properties will need to be configured when setting up SSL connection. See Additional Configurations: Configuring Hive with SSL section below for details.

Example: maxAllowedPacket | 1000

Default value: [None]

Authentication method

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

Default value: None

Use Zookeeper 

Specifies if Zookeeper be used to locate the Hadoop service instead of a specific hostname.

If the checkbox is selected, we should use Zookeeper to resolve the location of the database instead of using the 'hostname' field in the standard block.

Default value: Not selected

If you intend to use Zookeeper, then you must provide the following details:

You must provide the URL of the Zookeeper service. Zookeeper URL formats are different for CDH and HDP.

• 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.clouddev.snaplogic.com:2181,cdhclusterqa-1-2.clouddev.snaplogic.com:2181,cdhclusterqa-1-3.clouddev.snaplogic.com:2181/hiveserver2

Default value: None

JDBC Subprotocol

Conditional. This is required when the Authentication method is Kerberos. JDBC Subprotocol to be used. The options available are Hive and Impala.

Default value: Hive

Kerberos properties

Client Principal

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

Default value: [None]

Keytab file

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

Default value: [None]

Service principal

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

Default value: [None]

Account Encryption

If you are using Standard Encryption, the High sensitivity settings under Enhanced Encryption are followed.

If you have the Enhanced Account Encryption feature, the following describes which fields are encrypted for each sensitivity level selected for this account.

Account:

• High: Password, Key tab file
• Medium + High: Password, Key tab file
• Low + Medium + High: Password, Key tab file

Additional Configurations

Configuring Hive with Kerberos

Following is the recommended list of JARs to be uploaded for the JDBC4 drivers on Hive with Kerberos:

1. hive_metastore.jar
2. hive_service.jar
3. HiveJDBC4.jar
4. libfb303-0.9.0.jar
5. libthrift-0.9.0.jar
6. TCLIServiceClient.jar

Configuring Hive with SSL

The following URL properties have to be configured:

Overview

You must create Hive accounts to connect to data sources that you want to use in your Pipelines. You can configure your Teams accounts in SnapLogic using either the Designer or Manager.

For more information on SnapLogic Accounts, refer to Accounts.            

Configuring Hive Accounts Using SnapLogic Designer

Drag a Hive Snap to the Canvas and click the Snap to open its settings. Click the Account tab. You can now either use an existing account or create a new one.

Selecting an existing account

SnapLogic organizes and displays all accounts to which you have access, sorting them by account type and location. To select an existing account:

1. In the Account tab, click the dropdown  icon to view the accounts to which you have access, and select the account that you want to use.

2. Click the Save icon.

Image Added

Creating an account

1. In the Account tab, click Add Account below the Account Reference field.

2. Select the Location in which you want to create the account, select the Account Type, and click Continue. The Add Account dialog window associated with the account type is displayed.

   Image Added

3. Enter the required account details. For detailed guidance on how to provide the information required for each account type, refer to Generic Hive Database Account and Hive Database Account.

4. Click Validate to verify the account, if the account type supports validation.

5. Click Apply to complete configuring the Hive account.

Info
Enter additional information on this account in the Notes field of the Info tab. This will help you–and other users–understand the purpose of the account, especially if there are multiple accounts of the same type.

Configuring Hive Accounts Using SnapLogic Manager

You can use Manager to create accounts without associating them immediately with Pipelines.

Accounts in SnapLogic are associated with projects. You can use accounts created in other projects only if you have at least Read access to them.

1. In the left pane, browse to the project in which you want to create the account and click  > Account > Hive, followed by the appropriate account type. The Create Account dialog associated with the selected account type is displayed.

   Image Added

2. Repeat the steps numbered 3 through 5 in the Creating an account section. 

Avoid updating account credentials while Pipelines using that account are executing. Doing so may lead to unexpected results, including your account getting locked.

Additional Configurations

Configuring Hive with SSL

Add the following properties to the Url properties table to configure Hive with SSL. These configurations work only with Groundplexes and not Cloudplexes.

URL Property Name	URL Property Value
ssl	Required. Binary value to denote that SSL is enabled. This value must always be 1.
sslTrustStore	Required.

Testing Environment

Limitations or known issues

• "Method not supported" error while validating Apache Hive JDBC v1.2.1 or earlier

  The Hive Snap Pack does not validate with Apache Hive JDBC v1.2.1 jars or earlier because of a defect in Hive. HDP 2.6.3 and HDP 2.6.1 run on Apache Hive JDBC v1.2.1 jars.
  To validate Snaps that must work with HDP 2.6.3 and HDP 2.6.1, use JDBC v2.0.0 jars.

4.14 (snapsmrc490)

• Added a new account type: Generic Hive Database Account, this enables connecting to different types of clusters using JDBC URL.

4.8.0 

• Info tab added to accounts.

• Database accounts now invalidate connection pools if account properties are modified and login attempts fail.

4.7.0