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

For compatibility with the existing metadata system, DDL statements must appear in a specific order to define a virtual database. All of the database structure must be defined first - this includes create/alter/drop database, domains, vdb import, roles, and schemas statements. Then the schema object, schema import, and permission DDL may appear.

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);

For list database scoped properties see VDB properties.

Immediately following the create database statement is an analogous use database statement.

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.

Create a Translator

A 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. Some of the examples of available translator modules include:

  • oracle

  • mysql

  • postgresql

  • mongodb

See Data Sources for more.

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

  1. 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}

Create a Connection To an 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. 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.

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};

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}
    FROM (SERVER {server-name} | REPOSITORY {repository-name})
    INTO {schema-name}
    OPTIONS (<options-clause>)

<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. repository-name: Custom/extended "named" repositories from which metadata can be imported. See MetadataRepository interface for more details. Teiid provides a built in type called "DDL-FILE" see example below. schema-name: The foreign schema name to import from - it’s meaning is up to the translator. 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
    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.

Create Schema Objects

Most DDL statements that affect schema objects need the schema to be explicitly set. To be able to establish the schema context you are working with use following command:

Example: Set Schema
SET SCHEMA {schema-name};

then you will be create/drop/alter schema objects for that schema.

Example: Schema Object Creation
SET SCHEMA test;
CREATE VIEW my_view AS SELECT 'HELLO WORLD';

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;
Note
 Roles must be defined as a structural component of the VDB. GRANT/REVOKE may then appear after all of the database structure has been defined.

See Permissions for more details on the permission system.

BNF for GRANT/REVOKE command
GRANT [<permission-types> (,<permission-types>)* ]
    ON (<grant-resource>)
    TO {data-role}

GRANT (TEMPORARY TABLE | ALL PRIVILEGES)
    TO {data-role}

GRANT USAGE ON LANGUAGE {language-name}
    TO {data-role}

<permission-types> ::=
    SELECT | INSERT |  UPDATE | DELETE |
    EXECUTE | ALTER | DROP

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

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

REVOKE [(<permission-types> (,<permission-types>)* )]
    ON (<revoke-resource>)
    FROM {data-role}

REVOKE
    (TEMPORARY TABLE | ALL PRIVILEGES)
    FROM {data-role}

REVOKE USAGE ON LANGUAGE {language-name}
    FROM {data-role}

<revoke-resource> ::=
        TABLE {schema-name}.{table-name} [CONDITION] |
        PROCEDURE {schema-name}.{procedure-name} [CONDITION] |
        SCHEMA {schema-name} |
        COLUMN {schema-name}.{table-name}.{column-name} [MASK]

  • permission-types: Types of permissions to be granted

  • language-name: Name of the language

  • grant-resource: This is Schema element in the VDB on which this grant applies to.

  • revoke-resource: This is Schema element in the VDB on which this revoke applies to. Specifying the CONDITION or MASK keyword will attempt to move the specific CONDITION or MASK for that resource.

  • 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 {boolean 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.

Warning
 GRANT/REVOKE mostly function as direct replacements for the XML permission declarations. A grant/revoke has no effect on any other grant/revoke unless it represents the same resource, in which case its effect is combined.
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 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;

Differences with vdb.xml metadata

Using a .ddl file instead of a .xml file to define a vdb will result in differences in how metadata is loaded when using a full server deploymnet of Teiid.

Using a vdb.ddl file does not support: * metadata caching at the schema level - although this feature may be added later * metadata reload if a datasource is unavailable at deployment time * parallel loading of source metadata

All of same limitations affect all VDBs (regardless of .xml or .ddl) when using Teiid Embedded.

results matching ""

    No results matching ""