Modifying and Querying the external JDBC table

Learn how to modify and query external JDBC tables in Impala, and use the CLEAN_DBCP_DS_CACHE option for connection pooling.

Modifying the external JDBC table

You can use the ALTER TABLE statement to add, drop, or modify columns, or modify the table properties of existing external JDBC tables. The syntax is the same as the other Impala tables.

Example:

Using ALTER TABLE statement to add, drop, or modify columns:

ALTER TABLE student_jdbc ADD COLUMN IF NOT EXISTS date_col DATE;
ALTER TABLE student_jdbc DROP COLUMN int_col;
ALTER TABLE student_jdbc CHANGE COLUMN date_col timestamp_col
TIMESTAMP;

Using ALTER TABLE statement to modify table properties:

ALTER TABLE student_jdbc
SET TBLPROPERTIES ("dbcp.username"="impala",
"dbcp.password"="password");

Querying external JDBC tables

Querying or reading external JDBC tables is the same as querying regular tables in Impala. You can use SELECT statements to query data and can also join the external table with other tables across databases. However, do note that the metadata for the external tables is not persisted in Hive Metastore (HMS).

Example: SELECT * from student_jdbc;

Query options for external JDBC tables

A new query option, CLEAN_DBCP_DS_CACHE is added to save the DBCP SQL DataSource objects in the cache for a longer period of time. This allows the DBCP connection pools to be reused across multiple queries. When the value is set to false, the DBCP SQL DataSource object is not closed when its reference count is 0. The SQL DataSource object is kept in cache until the object is idle for more than 5 minutes.

Type: BOOLEAN

Default: True (1)