Connectors and External Tables

An external table allows Kognitio to process data that is not held within the Kognitio database. External tables require an appropriate connector for the external data source.

External tables are defined by the connector they use, and a set of connection attributes which tell that connector how to behave, including details of the external data source to use.

Usage

To define a connector, the create connector privilege is required, this is granted (system wide) with:

GRANT create connector ON SYSTEM TO user1

A connector can then be created with:

CREATE CONNECTOR myconnector [SOURCE data_provider] [COMMAND mycommand] [TARGET target_string]

For more details on how to create specific connectors for different data sources see Standard Connectors.

Connectors have their own privilege domain, with permissions to:

  • view - see the metadata

  • connect - use the connector to access data

  • configure - change the configuration of the connector such as the COMMAND or TARGET

  • activate - make the connector available to end-users who have the connect privilege

  • drop - delete the connector from Kognitio

  • set comment - add comments on the connector to the metadata

  • write - allow the user to write data back to the source through the connector. This is available in custom connectors only from version 8.2.3 onwards.

The privileges connect, configure, activate, drop and write are automatically granted to the user that creates the connector.

Privileges can also be granted on existing connectors. For example, to allow a user group mygroup to create external tables in a schema called myschema using myconnector (created above) run:

GRANT connect ON CONNECTOR myconnector TO mygroup;
GRANTE create external table ON SCHEMA myschema TO user2;

Notes

  • There is also a CONNECTOR_ID function for looking up the ID of a given connector name from the Kognitio system tables.

Dropping Connectors

To drop a connector, the syntax is as follows:

DROP CONNECTOR myconnector [CASCADE | RESTRICT]

A connector can only be dropped if there are no external tables dependent on it. Using the CASCADE option should be done with extreme caution as using this results in dropping all external tables based on the connector. This is similar to using the CASCADE option when dropping schemas.

There is currently no INVALIDATE DEPENDENT EXTERNAL TABLES for connectors.

Altering Connectors

By default, new connectors are active. The syntax to activate/deactivate them is:

ALTER CONNECTOR myconnector [ACTIVATE | DEACTIVATE]

Other options can be changed with the ALTER CONNECTOR command:

ALTER CONNECTOR myconnector SET COMMENT TO mycomment;
ALTER CONNECTOR myconnector RELOAD;
ALTER CONNECTOR myconnector SET SOURCE TO data_provider;
ALTER CONNECTOR myconnector SET COMMAND TO mycommand;
ALTER CONNECTOR myconnector SET TARGET TO target_string;

References

  • Standard Connectors - depending on your deployment option Kognitio comes with many of the standard connectors pre-installed.

  • External Tables - Details on how connectors and external tables can be used to access data

  • External Table Syntax - for creating external tables based on connectors