Import > Import DDL File

The DDL import service can be accessed from the Import Tab of Data Assets and Datatypes collections. It reads SQL Data Definition Language (DDL) statements (CREATE TABLE statements, etc.) from a text file and creates the corresponding entities in EDG.

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

At least one Physical Data Model and its corresponding Relational Database
Relational Databases contain Database Schemas
Database Schemas contain Database Tables and Database Views
Database Tables contain Database Columns, Unique Constraints, and Foreign Keys
Database Views contain Database Columns
Database Columns specify various properties, including their Physical Datatypes and whether they are part of the table’s primary key
Unique Constraints specify the column(s) within a table that must contain unique values
Foreign Keys specify the column(s) within a table that reference the primary key or unique column(s) in another or the same table
Physical Datatypes define the various database datatypes

To perform a DDL import, arguments for the following parameters can be specified:

Input file: Select the text file containing the SQL DDL to be parsed and converted into Data Asset entities.

Default database name: Any entity (e.g. a TABLE) within the uploaded SQL DDL file that does not specify a database identifier will be assigned the value specified in this field. If no default database name is specified, the base name of the uploaded file will be used (e.g. NORTHWIND for northwind.sql). The database identifier is the first node of a typical 3-node, fully-qualified entity name (e.g. DATABASE.SCHEMA.TABLE).

Default schema name: Any entity (e.g. a TABLE) within the uploaded SQL DDL file that does not specify a schema identifier will be assigned the value specified in this field. If no default schema name is specified, a schema will not be added. The schema identifier is the second node of a typical 3-node, fully-qualified entity name (e.g. DATABASE.SCHEMA.TABLE).

Database Type: Select the database-specific dialect of the uploaded SQL DDL file.

Model for Datatype Definitions: The DDL import will associate each table (or view) column with the Physical Datatype corresponding to the column’s declared datatype. The import will create new datatypes for any types not previously defined. This model specifies where the DDL import is to look for pre-existing datatypes and where to add any new datatypes encountered. The options are:

Any EDG Datatypes collection included by the Data Assets collection (via Settings > Includes).
The Data Assets collection itself.

For imported datatypes, the recommended practice is to store the datatypes in a separate EDG Datatypes collection rather than in the Data Assets collection itself.

Record each new triple in change history: If this checkbox is enabled, the DDL import will create a change history record for each new triple created as a result of the import. Enabling this checkbox is not recommended for DDL scripts that define a large number of the database entities listed above.

SQL Compatibility

The DDL import service supports SQL DDL dialects for

Apache Hive
MariaDB
Microsoft SQL Server
MySQL
Oracle
PostgreSQL
SAP HANA
Snowflake
Teradata

The DDL import service supports only the incremental creation of various database entities via the following statements and clauses:

CREATE TABLE
- Column definitions
  - Datatype
  - Length
  - Precision
  - Scale
  - Unsigned
  - Comment
  - Constraints
    - NOT NULL
    - PRIMARY KEY
    - DISTINCT/UNIQUE
    - FOREIGN KEY/REFERENCES
    - AUTO_INCREMENT
- Constraints
  - PRIMARY KEY
  - DISTINCT/UNIQUE
  - FOREIGN KEY/REFERENCES
CREATE VIEW
- Column names
ALTER TABLE
- MODIFY COLUMN
  - Constraints (see Column Definitions - Constraints, above)
- ADD CONSTRAINT
  - Constraints (see CREATE TABLE - Constraints, above)
COMMENT ON

All other SQL statements and clauses in the file are ignored.

Typical Problems

SQL statements cannot be parsed: When an uploaded file cannot be parsed, the import is aborted and nothing will be imported. An error message will indicate the location in the file where the parse error occurred. It may be possible to manually edit the SQL file to remove the problematic text.
SQL statements only partially understood: In some cases, the import service will be able to understand the basic intent of a DDL statement but not a specific clause or argument in the statement. In this case, the processing will continue and import whatever was understood. Therefore, imported data should be carefully reviewed to verify that all needed information has been imported.