Create Cloudera Impala ODBC Connections
Toad allows you to connect to a Cloudera Impala database using ODBC connectivity. Download and install the necessary ODBC driver to use for your Impala connection prior to creating the new connection in Toad.
Note: This feature is available in the Toad Data Studio Professional Edition only.
To create a Cloudera Impala ODBC connection
- Click
on the toolbar (ALT+F+N+O).
- Select Cloudera Impala from the Group list box (scroll down to NoSQL Data Sources).
- Use data source name—Select to display data source names. Clear this check box if you want to display the driver names.
- To create a data source to use in this connection, click
in the Data Source Name field. Then click Add.
-
Select an Impala ODBC driver from the list, and click Finish.
- In the configuration dialog, specify the configuration properties required for the database.
- A large default string column length could cause an out-of-memory error. To decrease this value for this ODBC connection, click Advanced Options in the configuration dialog. Then decrease the value in the String Column Length field and the Rows fetched per block field. Click OK to save your changes.
-
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
to create a temporary connection and display available databases/schemas in the drop-down list.
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.
To specify string column length or rows fetched per block
- In the Connection Manager, right-click the Impala connection and select Properties.
- In the Connection Properties dialog, click
- Select the data source used for this connection and click Configure.
- In the configuration dialog, click Advanced Options.
- In the Advanced Options dialog, reduce the value specified in the String Column Length field. You can also reduce the number in the Rows fetched per block field.
- Click OK to save your changes. To apply the changes, you may need to reconnect.
Considerations and Limitations for Impala Connections
For a complete list of Impala limitations, see the Cloudera Impala documentation: http://www.cloudera.com/content/www/en-us/documentation/enterprise/latest/topics/impala.html.
| Consideration/Limitation | Details |
|---|---|
| Data grid is read-only | For Impala connections, the data grid in Toad is read-only. |
| Out-of-memory error |
An out-of-memory error can result from a large default string column length. To avoid this error, reduce the default string column length and the number of rows fetched per block for the Impala connection. To specify string column length or rows fetched for your connection, see To specify string column length or rows fetched per block above. Depending on the driver used, modifying the Block Cursor Size might also help avoid an out-of-memory error. This setting is specified on the Advanced tab of the Connection Properties dialog. |
|
Creating a table with constraints is not supported |
Impala does not support constraints in a CREATE TABLE statement. |
| NOT NULLS are not supported | Impala does not support NOT NULL columns. |
| Indexes are not supported | Impala does not support INDEX, KEY, or PRIMARY KEY clauses in CREATE TABLE and ALTER TABLE statements. |
| Limitations when importing data to Impala |
The NOT NULL constraint cannot be applied when importing data to Impala. You cannot add an index to a table when importing data to Impala. |
| Cannot generate Update or Delete statements | The Generate SQL | Update Statement and Generate SQL | Delete Statement actions are disabled for Impala connections. |
| Views are displayed as tables | A view is represented as a table in Impala, so views display as tables in Toad. For more information, see https://issues.cloudera.org/browse/IMPALA-1697. |
| Cannot cancel a query | An Impala query cannot be canceled and will continue to run on the server. However, Toad will stop retrieving rows sometime after the first row is returned. This may result in a limited number of rows returned. |
| Bind variable in WHERE/HAVING clause | Using a bind variable in a WHERE or HAVING clause can result in a syntax error from Impala. For more information, see https://issues.cloudera.org/browse/IMPALA-3149. |
| Distinct values |
Distinct values are not available in Code Completion for ODBC connections.
|