DDL VDB

A Virtual Database (VDB) can created through DDL statements. Teiid supports SQL-MED specification to configure the foreign data sources.

A DDL file captures information about the VDB, the sources it integrate, and preferences for importing metadata. The format of the DDL file can be any elements in documented here. The DDL file may be deployed as a single file, or in a zip archive. See Developing a Virtual Database for a discussion of the .vdb zip packaging.

DDL File Deployment

You can simply create a SOME-NAME-vdb.ddl file.

Important

The VDB name pattern must adhere to "-vdb.ddl" for the Teiid VDB deployer to recognize this file when deployed in Teiid Server.

Example VDB DDL Template

CREATE DATABASE my_example VERSION '1.0.0';
USE DATABASE my_example VERSION '1.0.0'
CREATE FOREIGN DATA WRAPPER postgresql;
CREATE SERVER pgsql TYPE 'postgresql-9.4-1201.jdbc41.jar'
    VERSION 'one' FOREIGN DATA WRAPPER postgresql
    OPTIONS (
        "jndi-name" 'java:/postgres-ds'
    );
CREATE SCHEMA test SERVER pgsql;
IMPORT FOREIGN SCHEMA public FROM SERVER pgsql INTO test
    OPTIONS(
        importer.useFullSchemaName false,
        importer.tableTypes 'TABLE,VIEW'
);

DDL File Format

Step 1: Create a Database

Every VDB file must start with database definition where it specifies the name and version of the database. The create syntax for database is

CREATE DATABASE {db-name} [VERSION {version-string}] OPTIONS ( <options-clause>)

<options-clause> ::=
    <key> <value>[,<key>, <value>]*

An example statement

CREATE DATABASE my_example VERSION '1' OPTIONS ("cache-metadata" true);

you can add additional OPTIONS at later time using below syntax.

ALTER  DATABASE <name> OPTIONS (ADD|SET|DROP <key-value>)

For list database scoped properties see VDB properties

As we learned about the VDB components earlier in the guide, we need to first create translators, then connections to data sources, and then using these we can gather metadata about these sources. There is no limit on how many translators, or data sources or schemas you create to build VDB.

Step 2A: Create a Translator

Translator is an adapter to the foreign data source, the creation of translator in the context of the VDB creates a reference to the software module that is available in the Teiid system. For list of available translators, you should look up in Teiid system. Some of the examples of available translator modules include

oracle
mysql
postgresql
mongodb

CREATE FOREIGN ( DATA WRAPPER | TRANSLATOR ) {translator-name}
    [ TYPE {base-translator-type} ]
    OPTIONS ( <options-clause>)

<options-clause> ::=
    <key> <value>[,<key>, <value>]*

Optional TYPE is used to create "override" translator. The OPTIONS clause is used to provide the "execution-properties" of a specific translator defined in either in {translator-name} or {base-translator-name}. These names MUST match with available Translators in the system. link:Translators.adoc[Translators} documents all the available translators.

Example 1: Example creating translator

CREATE FOREIGN DATA WRAPPER postgresql;

For all available translators see Translators

Example 2: Example creating Override Translator

CREATE FOREIGN DATA WRAPPER oracle-override TYPE oracle OPTIONS (useBindVariables false);

The above example creates a translator override with an example showing turning off the prepared statements.

Additional management support to alter, delete a translator

ALTER  (DATA WRAPPER|TRANSLATOR) {translator-name} OPTIONS (ADD|SET|DROP <key-value>);

DROP FOREIGN [<DATA> <WRAPPER>|<TRANSLATOR>] {translator-name}

Step 2C: Create a Connection To External Source

Before you can create a connection to the data source, you must either have a JDBC driver (Type 4) that can connect to the data source, or Teiid system must have provided a resource adapter (RAR) file to enable connection to the data source. If you are using the JDBC driver file this should have already been deployed to the Teiid system, or made it available on the classpath in the case of the Teiid Embedded. This is Step 2B from earlier, there is currently no DDL mechanism to deploy the external drivers.

Now to create connection to the external data source. One needs to know the name of deployment. For JDBC drivers, it is typically JAR name with out path. For resource adapters, it is the name of the resource-adapter. Step also associates the connection created with the translator to be used in communicating with this source.

CREATE SERVER {source-name} TYPE '{source-type}'
    [VERSION '{version}'] FOREIGN DATA WRAPPER {translator-name}
    OPTIONS (<options-clause>)

<options-clause> ::=
    <key> <value>[,<key>, <value>]*

Name

Description

source-name

Name given to the source’s connection.

source-type

For JDBC connection, the driver name or resource-adapter name.

translator-name

Name of the translator to be used with this server.

options

All connection properties for the connection.

For all available translators see Translators

Example 3: creating a data source connection to Postgres database

CREATE SERVER pgsql TYPE 'postgresql-9.4-1201.jdbc41.jar'
    FOREIGN DATA WRAPPER postgresql
    OPTIONS (
        "jndi-name" 'java:/postgres-ds'
    );

The below are the typical properties that need to be configured for a JDBC connection

Name

Description

jndi-name

Jndi name of the datasource

Note	Any additional properties to create a data-source in WildFly can also used here in OPTIONS clause.

Important

If the data source is already exists in the configuration, then supply only provide jndi-name property (you can omit all other properties), then above command will create a new connection, but will use existing configuration in the system.

The below shows an example connection with resource adapter.

Example 4: creating a data source connection to "file" resource adapter.

CREATE SERVER marketdata TYPE 'file'
    FOREIGN DATA WRAPPER file
    OPTIONS(
        ParentDirectory '/path/to/marketdata'
    );

For all available data sources see data sources

Additional management support to alter/delete a connection.

ALTER  SERVER {source-name}  OPTIONS ( ADD|SET|DROP <key-value>);
DROP SERVER  {source-name};

Warning

ALTER can be used to change properties, but due to a bug in WildFly this feature currently does not work.

Now that we have the Translators and Connections created, the next step is to create SCHEMAs and work with metadata.

Step 3 & 5: Create SCHEMA in VDB

Before metadata about data sources or abstraction layers can be created, a container for this metadata needs to be created. In relational database concepts this is called Schema, and this also works as a namespace in which metadata elements like TABLES, VIEWS and PROCEDURES exist. The below DDL shows how to create a SCHEMA element.

CREATE [VIRTUAL] SCHEMA {schema-name}
    [SERVER {server-name} (<COMMA> {server-name})*]
    OPTIONS (<options-clause>)

<options-clause> ::=
    <key> <value>[,<key>, <value>]*

The use of VIRTUAL keyword defines if this schema is "Virtual Schema". In the absence of the VIRTUAL keyword, this Schema element represents a "Source Schema". Refer to VDB Guide about different types of Schema types.

Important

If the Schema is defined as "Source Schema", then SERVER configuration must be provided, to be able to determine the data source connection to be used when executing queries that belong to this Schema. Providing multiple Server names configure this Schema as "multi-source" model. See Multisource Models for more information.

The below are the typical properties that need to be configured for a Schema in the OPTIONS clause.

Name

Description

VISIBILITY

Is Schema visible during metadata interrogation

Example 5: Showing to create a source schema for PostgreSQL server from example above

CREATE SCHEMA test SERVER pgsql;

Additional management support to alter/delete a schema can be done through following commands.

ALTER [VIRTUAL] SCHEMA {schema-name} OPTIONS (ADD|SET|DROP <key-value>);
DROP SCHEMA {schema-name};

When working with existing VDB, or when editing multiple Schema’s in a working VDB, one needs to setup context of Schema he/she wants to modify, as some of the DDL commands are context sensitive as to which Schema they belong. To be able to establish the context that you are working with certain Schema use following command.

SET SCHEMA {schema-name};

then you will be add/drop/alter any other Schema elements.

Step 4: Importing Schema

If you are designing a source schema, you can add the TABLES, PROCEDURES manually to represent the data source, however in certain situations this can be tedious, or complicated. For example, if you need to represent 100s of existing tables from your Oracle database in Teiid? Or if you are working with MongoDB, how are you going to map a document structure into a TABLE? For this purpose, Teiid provides an import metadata command, that can import/create metadata that represents the source. The following command can be used for that purpose with most of the sources (LDAP source is only exception, not providing import)

IMPORT FOREIGN SCHEMA {foreign-schema-name} [ <import qualifications> ]
    FROM SERVER {server-name}
    INTO {schema-name}
    OPTIONS (<options-clause>)

<import qualifications> ::=
    LIMIT TO (<table name list>) | EXCEPT (<table name list>)

<options-clause> ::=
    <key> <value>[,<key>, <value>]*

foreign-schema-name : Name of schema in external data source to import. Typically most databases are tied to a schema name, like "public", "dbo" or name of the database. If you are working with non-relational source, you can provide a dummy value here. server-name: name of the server created above to import metadata from. schema-name: Name schema element created above to import metadata into. import qualifications : using this you can limit your import of the Tables from foreign datasource specified to this list. options-clause : The "importer" properties that can be used to refine the import process behavior of the metadata. Each Translator defines a set of "importer" properties with their documentation or through extension properties.

The below example shows importing metadata from a PostgreSQL using server example above.

Example 6

-- import from native database
IMPORT FOREIGN SCHEMA public
    LIMIT TO customers, orders
    FROM SERVER pgsql
    INTO test

-- in archive based vdbs(.vdb) you can provide each schema in a separate file and pull them in main vdb.ddl file as
IMPORT FOREIGN SCHEMA public
    FROM REPOSITORY DDL-FILE
    INTO test OPTIONS ("ddl-file" '/path/to/schema.ddl')

Tip	The example IMPORT SCHEMA can be used with any custom Metadata Repository, in the REPOSITORY {DDL-FILE}, DDL-FILE represents a particular type of repository.

The above command imports public.customers, public.orders tables using pgsql’s connection into a VDB schema test.

Importing another Virtual Database (VDB Reuse)

If you like to import another VDB that is created into the current VDB, the following command cn be used to import all the metadata

IMPORT DATABASE {vdb-name} VERSION {version} [WITH ACCESS CONTROL]

Specifying the WITH ACCESS CONTROL also imports any Data Roles defined in the other database.

Data Roles

Data roles, also called entitlements, are sets of permissions defined per VDB that dictate data access (create, read, update, delete). Data roles use a fine-grained permission system that Teiid will enforce at runtime and provide audit log entries for access violations. To read more about Data Roles and Permissions see Data Roles and Permissions

Here we will show DDL support to create these Data Roles and corresponding permissions.

BNF for Create Data Role

CREATE ROLE {data-role}
    [WITH JAAS ROLE {enterprise-role}(,{enterprise-role})*]
    [WITH ANY AUTHENTICATED]

data-role: Data role referenced in the VDB enterprise-role: Enterprise role(s) that this data-role represents WITH ANY AUTHENTICATED: When present, this data-role is given to any user who is valid authenticated user.

Example: Create Data Role

CREATE ROLE readWrite WITH JASS ROLE developer,analyst;

CREATE ROLE readOnly WITH ANY AUTHENTICATED;

Now to Setting Permissions, see Permissions for more details.

BNF for Grant command

GRANT  (<permission-types> (,<permission-types>)* )
    ON (<grant-resource>)
    TO {data-role}

<permission-types> ::=
    SELECT | INSERT |  UPDATE | DELETE |
    EXECUTE | LANGUAGE | ALTER | DROP |
    ALL PRIVILEGES | TEMPORARY TABLES)

<grant-resource> ::=
        DATABASE |
        TABLE {schema-name}.{table-name} [<condition>] |
        PROCEDURE {schema-name}.{procedure-name} [<condition>] |
        SCHEMA {schema-name} |
        COLUMN {schema-name}.{table-name}.{column-name} |
            [MASK [ORDER \d]  {expression} ]
            [<condition>]

<condition> ::=
    CONDITION [CONSTRAINT] {expression}

permission-types: Types of permissions to be granted
grant-resource: This is Schema element in the VDB on which this grant applies to. It can anything between a DATABASE and COLUMN
schema-name: Name of the schema this resource belongs to
table-name: Name of the Table/View
procedure-name: Procedure Name
column-name: Name of the column
condition: When present, the {expression} is appended to the WHERE clause of the query
expression: any valid sql expression, this can include columns from referenced resource
CONSTRAINT: When this is supplied along with CONDITION, the {expression} is also applied during the INSERT/UPDATE queries. By default CONDITION only applies SELECT queries. Also CONSTRAINT does NOT apply to VIEWs only FOREIGN TABLES.

Caution

LANGUAGE permission can only be applied on DATABASE

Example: Give Read, write, update permission on single table to user with enterprise role "role1"

CREATE ROLE RoleA WITH JAAS ROLE role1;
GRANT INSERT, READ, UPDATE ON TABLE test.Customer TO RoleA;

Example : Give all permissions to user with "admin" enterprise role

CREATE ROLE everything WITH JAAS ROLE admin;
GRANT ALL PRIVILEGES ON DATABASE TO everything;

Example : Use of CONDITION, all users can see only Orders table contents amount < 1000

CREATE ROLE base-role WITH ANY AUTHENTICATED;
GRANT READ ON TABLE test.Orders CONDITION 'amount < 1000' TO base-role;

Example : Use of CONDITION, override previous example to more privileged user

GRANT READ ON TABLE test.Orders CONDITION 'amount < 1000 and amount >=1000' TO RoleA;

Example : Restricting rows, ROW BASED SECURITY

GRANT READ ON TABLE test.CustomerOrders CONDITION CONSTRAINT 'name = user()' TO RoleA;

In the above example, user() function returns the currently logged in user id, if that matches to the name column, only those rows will be returned. There are functions like hasRole('x') that can be used too.

Example : Column Masking, mask "amount for all users"

GRANT READ ON COLUMN test.Order.amount
    MASK 'xxxx'
    TO base-role;

Example : Column Masking, mask "amount for all users when amount > 1000"

GRANT READ ON COLUMN test.Order.amount
    MASK 'CASE WHEN amount > 1000 THEN 'xxxx' END'
    TO base-role;

Example : Column Masking, mask "amount for all users" except the calling user is equal to the user()

GRANT READ ON COLUMN test.Order.amount
    MASK 'xxxx'
    CONDITION 'customerid <> user()'
    TO base-role;

DDL VDB

DDL VDB

DDL File Deployment

DDL File Format

Step 1: Create a Database

Step 2A: Create a Translator

Step 2C: Create a Connection To External Source

Step 3 & 5: Create SCHEMA in VDB

Step 4: Importing Schema

Importing another Virtual Database (VDB Reuse)

Data Roles

results matching ""

No results matching ""