External Tables and Connectors

This section gives detailed instructions on using external tables and connectors.

Overview

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.

Connector Syntax

Connectors have their own privilege domain, with permissions to View (see metadata), Connect, Configure (set connection string), Activate and Drop.

To define a connector, the CREATE CONNECTOR privilege is required, this is granted with:

GRANT CREATE CONNECTOR ON SYSTEM TO user

A connector can then be created with:

CREATE CONNECTOR name [SOURCE connector-name] [COMMAND command] [TARGET
target-string]

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

ALTER CONNECTOR name [ACTIVATE | DEACTIVATE]

Other options can be changed with the ALTER CONNECTOR command:

ALTER CONNECTOR name SET COMMENT TO comment;
ALTER CONNECTOR name RELOAD;
ALTER CONNECTOR name SET SOURCE TO connector-name;
ALTER CONNECTOR name SET COMMAND TO command;
ALTER CONNECTOR name SET TARGET TO target-string;

There is also a CONNECTOR_ID function for looking up the id of a given connector name:

SELECT CONNECTOR_ID(name)

Privileges also exist on connectors. For example, to grant permission for a user to use the connector created above, the following should be used (privileges in this domain are CONNECT, DROP, ACTIVATE, CONFIGURE, and EXECUTE):

GRANT CONNECT ON CONNECTOR hdfscon TO user

To drop a connector, the syntax is as follows (note that CASCADE is required if dependent external tables exist):

DROP CONNECTOR name [CASCADE | RESTRICT]

External Table Syntax

External tables define the structure of the data held in external sources and accessed via connectors. The syntax for creating an external table is:

CREATE EXTERNAL TABLE tablename
[({column-name [data-type]}]
FROM connector-name
TARGET target-string

The syntax for defining the external table columns using column-name and data-type is identical to a standard CREATE TABLE statement.

The connector-name specifies the connector to be used to establish the connection to the external data source.

The target-string contains information that must be passed to the connector in order to access the external data source. This is source specific but typically contains information on the file location with the external data source and format of the data.

There are several examples in the Creating Tables section of the External Table user documentation and on the Connector Quick Reference Sheets.

Anonymous External Tables

It is also possible to have anonymous external tables by placing the EXTERNAL TABLE definition directly into a SELECT statement. For example:

SELECT *
FROM (EXTERNAL TABLE etcpasswd_ext(...) FROM hdfscon
      TARGET 'file /user/wxadmin/etcpasswd.txt
      FMT_FIELD_SEPARATOR ":"
      FMT_IGNORE_EXTRA_FIELDS yes'
     ) dt

The advantages of anonymous external tables are:

  • Users without CREATE TABLE permissions are allowed to use external tables in an ad-hoc fashion.
  • The external table is defined and used in one statement.
  • The SQL query indicates where the data is coming from.

Logging of Conversion Errors

The view, SYS.IPE_CONV_ERROR, contains details of any conversion errors encountered during an external table access; for example, a target column on Kognitio is too small to hold the data supplied by the external data source.

External Table Licencing and Configuration

Availability of external connectors depends on how your system is deployed and how it is licensed:

Kognitio on Hadoop

External tables are available by default on Hadoop deployments.

Kognitio on MapR

External tables are available by default on MapR deployments.

Kognitio standalone

To use external tables, you must have a licence specifically allowing the capability. This is only possible if the licence has a restriction on the RAM size of the Kognitio platform.

In addition to having an appropriate licence, the [boot options] section of the configuration file must also contain the following line to enable the use of external tables:

external_tables=yes