Toad allows you to connect to a Hive data warehouse system. Apache Hive is one of the NoSQL data sources supported by Toad.
See also, About Apache Hive Data Sources.
To create a Hive connection
on the Connections toolbar (ALT+F+N).
Enter the connection information in the Create New Connection dialog. Review the following for additional information:
|
Server tab |
|
|
Host |
Enter the host name or IP address of the Hive data source. |
|
Port |
Enter the port number. |
|
Schema |
Enter a Hive schema to open upon connection. (HiveServer2 only) After connecting, you can select a different available schema in the Object Explorer. |
|
Server type |
Select the version of HiveServer the host uses, HiveServer1 or HiveServer2. |
|
Use SSL |
(HiveServer2 only) Select to use SSL to connect to Hive. |
|
HTTP mode |
(HiveServer2 only) Select to connect to HiveServer2 running in HTTP mode. Then enter the service endpoint. The default is cliservice. |
|
Session Initialization |
(HiveServer1 only) Enter any Hive session initialization commands. These commands will be executed once, at the start of each connection. |
|
Category |
(Optional) Select an existing category or create a new one. |
| Authentication tab (HiveServer1) | |
|
Connect with SSH |
Select to use SSH to connect to Hive. Then enter a user name and select an authentication method (key file or password). |
|
SSH user |
Enter the user name to log in when using SSH. |
|
SSH port |
Select the SSH port number. The default is 22. |
|
Authenticate with a key file |
Select if the host uses a key file to authenticate the SSH user field. Private key path—Enter the absolute path (including file name) to the private key file on the host (not the client). Pass phrase—Enter your passphrase. |
| Authenticate with a password |
Select if the host uses a password to authenticate the SSH user field. Password—Enter the password to log in using SSH. |
| Authentication tab (HiveServer2) | |
| Authentication |
Select an authentication method. To enter only a user name, select the Username and password method. |
| Username |
Enter the user name to use for this connection. If using Kerberos authentication, enter your user name in the following format: user@REALM or domain\user. |
| Hive host realm |
(Kerberos only) Enter the Kerberos realm of the HiveServer2 host. Note: If necessary, contact your IT department to obtain this information. Before you can successfully connect to Hive using Kerberos authentication, the Toad client computer must have a Kerberos Key Distribution Center (KDC) address entry for the Hive host realm. To define the KDC entry, use the following procedure. Open a Windows Command Prompt as Administrator and enter the following command: ksetup /addkdc <REALM> <KDC name> You only need to run this command once for the given realm. To confirm the setting, enter the following command: ksetup. This command should return both the name of the Hive host realm and the KDC name you specified. |
| Hive host FQDN |
(Kerberos only) Enter the fully-qualified domain name (FQDN) of the HiveServer2 host. Note: If necessary, contact your IT department to obtain this information. |
| Service name | (Kerberos only) Enter the service name of the Hive server. The default is hive. |
|
Note: In NoSQL and Business Intelligence connections, Toad automatically saves the password in the connections.xml file as obfuscated text, as well as in Toad. To add additional password security, use Toad's Master Password feature. | |
Click Connect to connect immediately while saving the connection information. Optionally, click Save to save the connection without connecting.
Tips:
You can create a new connection, or connect to an existing connection from the Create New Connection window. See Troubleshoot MySQL Issues for more information about troubleshooting MySQL connection issues.
To create a MySQL
Click
Select
Review the following for additional information:
| Login Tab | Description |
|
Connection type |
Select the protocol to use when connecting. |
|
Host |
Enter the name of the host computer where the database is installed. |
|
Password |
Enter the password to use when connecting. Tip: After connecting, you can set a master password to further secure your connection in Tools | Options| Environment | Security. |
|
Connection timeout |
Enter the number of seconds to wait when connecting before the application times out. The default is 15 seconds. |
|
SSL |
If you selected SSL as the connection type, review the following for additional information:
|
|
Compress |
Select this checkbox to compress the value of any strings and return binary values. |
|
Use MySQL libraries |
Select this checkbox to use an embedded MySQL library instead of the MySQL .NET provider. Note: If you select this option, you must specify libMySQL.dll in the path. |
|
Category |
Select or create a category if you want to color code Editor tabs for a specific connection. This can help differentiate between development and production databases. You can also set an option to color code the Object Explorer pane and object editor windows (Create, Alter, Drop, etc.). |
| Advanced Tab | Description |
|
ODBC driver |
Select an ODBC driver to use when executing cross-connection queries using this connection. Tip: You can specify the ODBC driver to use for all connections for this database provider in Tools | Options | Database | MySQL. |
Click Connect to save the connection and immediately connect to the database.
or
Click Save to save the connection without connecting to the database.
Tip: Connections are stored in the connections.xml file and can be found by clicking the Application Data Directory link in Help | About.
Toad Data Studio allows you to create a connection to a database that supports an ODBC 3.0 or later driver. ODBC connectivity provides basic querying capability, but may not be as full-featured as a native connection in Toad. For a list of databases tested with the ODBC provider, see the Release Notes.
This form of connectivity allows connections to databases such as Netezza, IBM iSeries, Ingres, and Vertica.
Note: Distinct values are not available in Code Completion for ODBC connections.
DSN Architecture (Bitness) Must Match Toad
To create a successful ODBC connection, the architecture (bitness) of the driver in the specified DSN must match the architecture (bitness) of Toad.
To create an ODBC connection
on the toolbar (ALT+F+N+O).
in the Data Source Name field. Then click Add.
Select an ODBC driver from the list, and click Finish.
Note: If creating an Oracle ODBC connection, select the ODBC driver provided by Oracle. The Microsoft ODBC for Oracle driver has less functionality than the driver provided by Oracle.
Specify the configuration properties required for the database in the Windows configuration dialog.
Notes:
Specify the connection properties in the Create New Connection dialog. Review the following for additional information:
| General | |
|
Data source name |
Select the driver or data source name you added in the previous steps. |
|
User |
Enter the user name to use when connecting. |
|
Password |
Enter the password to use when connecting. Tip: After connecting, you can set a master password to further secure your connection in Tools | Options| Environment | Security. |
|
Database |
Select a database or schema. Click |
|
Information |
|
|
Data Source Name |
Displays the selected driver or data source name. |
|
Driver |
Displays the ODBC driver associated with the data source. |
| Advanced | |
| Default Table Column |
Specify default options to use when creating a new table. Column type—Select the default data type to use when creating a table. Column length—For the selected column type, enter a default column length to use. |
| Advanced Options |
Block Cursor Size—Specify the number of rows to return in a single fetch of data from the result set. Disable multi-threading—Multi-threading is disabled by default. Enable multi-threading only if supported by the driver. Multi-threading can improve performance, but can also result in unpredictable behavior if not supported by the driver. Default: Selected |
| Category | (Optional) Select an existing category or create a new one. |
Click Connect to connect immediately while saving the connection information. Optionally, click Save to save the connection without connecting.
Tip: Connections are stored in the connections.xml file and can be found by clicking the Application Data Directory link in Help | About.
| Tip | Description |
|---|---|
| Filter DSN by bitness |
To filter Data Source Names displayed in the New Connection dialog by the bitness of Toad, go to Tools | Options | Database | ODBC. |
| Cache object metadata |
To cache object metadata for ODBC connections and retain it between sessions, go to Tools | Options | Database | ODBC and select to enable disk caching. Cached object metadata is retained until manually refreshed. |
| Refresh object metadata |
To refresh object metadata for all ODBC connections, in the Object Explorer right-click an object and select Refresh All. Right-click an object and select Refresh Schema to refresh only objects in that schema. |
| Feature | Description |
|---|---|
| Bypass schema/instance selection in SQL Editor |
For ODBC connections, in the SQL Editor you can bypass selecting a schema/instance and specify this information through the script instead. This is useful if your script executes SQL against multiple schemas. To use this method, select I will set schema/instance in the script from the schema/instance drop-down list in the SQL Editor window. Then specify the schema/instance through your script. Note: When this option is selected, some code completion features, object actions, and object information tool tips are unavailable for this SQL Editor window. |
You can create Oracle Lightweight Directory Access Protocol (LDAP) connections when your Oracle client is configured for LDAP. Once the client is configured, Toad retrieves names from your LDAP server and displays them in the Database Name list in the Create New Connection window.
Notes:
To configure the Oracle client for LDAP
Obtain the LDAP.ORA file from your database administrator.
Copy the LDAP.ORA file to the folder where the SQLNET.ORA file for your Oracle client is located. The default folder is: ORACLE_HOME\network\admin
Open the SQLNET.ORA file and enter "(LDAP)" as the value for the NAMES.DIRECTORY_PATH as follows:
NAMES.DIRECTORY_PATH= (LDAP)
Note: If you are using multiple naming methods, add the (LDAP) value to existing parameter values. For example, if you are using both the TNSNAMES and LDAP naming methods, add LDAP to TNSNAMES as follows:
NAMES.DIRECTORY_PATH= (TNSNAMES, LDAP)
Save the SQLNET.ORA file.
Launch Toad and select File | New | Connection.
Create an LDAP connection following the procedure in Create Oracle Connections. In step 3, select a database on the LDAP server from the Database Name list. Toad retrieves the list of LDAP names from the LDAP server once per Toad session. To refresh the list, click 
to create a temporary connection and display available databases/schemas in the drop-down list.