Import > Import DDL File

This feature is available from the Import Tab for collections of type Data Assets and Datatypes. It reads DDL statements (CREATE TABLE, etc.) from an SQL file, and creates corresponding entities in TopBraid.

When importing into a Data Assets collection, the following entities from the DDL file are created:

  • a Relational Database

  • any Database Tables defined

  • any Database Views defined

  • the Database Columns of the tables and views

  • a Physical Data Model that serves as a container for the entities about the database

Database name: The importer will prefix all entity names with a database name, to distinguish the entities created by importing different databases. If no database name is specified, then the name of the SQL file will be used (e.g., NORTHWIND for northwind.sql). The database name serves a role similar to the Catalog names and Schema names within a database server.

Model for Datatype Definitions: The importer also stores the datatype of each table column. It will re-use existing datatype definitions for previously seen types, and create new ones for the rest. The drop-down tells the importer where to look for datatype definitions and where to import new ones. The options are:

  • Any EDG Datatypes that have been included into the Data Asset (via General > Includes).

  • The Data Asset itself.

To store imported datatype definitions, we recommend using EDG Datatypes rather than storing them in the Data Assets themselves.

SQL Compatibility

The DDL import functionality supports MySQL, Oracle, PostgreSQL, SQLServer, Hana, Snowflake, Teradata and Hive.

The DDL importer only recognizes CREATE TABLE and CREATE VIEW statements. All other SQL statements in the file are ignored. Because of this the CREATE statements should be generated with all columns and constraints included in them. Only primary key and foreign key constraints are recognized. All other constraint and index clauses are ignored.

Column Properties: The DDL importer tries to parse and create these properties for a column:

  • scale

  • precision

  • length

  • unsigned

  • nullable

The existance and exact meaning of these properties may vary based on the specific SQL dialect and datatype.

In many imports two forms of problems occur:

  1. SQL statements that cannot be parsed: When an input file cannot be parsed, the import process will be aborted and nothing will be imported. An error message will be shown indicating the location in the file where the parse error occurred. It may be possible to manually edit the SQL file to remove the unsupported SQL features.

  2. SQL statements only partially understood: In some cases, the importer will be able to understand the basic intent of a DDL statement, but not a specific parameter or argument to the statement. In this case, it will continue and import whatever was understood. Therefore, imported data should be carefully reviewed to ascertain that all needed information has been imported.

Customizing the DDL Import

The importing of DDL into Data Assets (import DDL file) and Datatypes (import DDL file) provides an extension point that allows developers to add custom post-processing behaviour to the DDL import. This advanced feature requires a good understanding of how to build TopBraid extensions and should be led by TopQuadrant staff. In a nutshell, it can be done by overriding the SWP element edg-importer:PostProcessImportedDDL. The arguments provided to the prototype are documented on the SWP element itself.

See Also

Further Reading on TopBraid