Create Table

You are looking at an older version of the documentation. The latest version is found here.

CREATE TABLE

The CREATE TABLE command creates a new table in a given schema (if specified). If no schema name has been included in the command, it will create a temporary table.

Syntax

SQL

CREATE TABLE [<dwh_schema_name>.]<table_name> <table_definition>

You can also use the PRIMARY KEY clause to specify the primary key if needed:

SQL

CREATE TABLE dwh.table1 (col1 integer, col2 string, PRIMARY KEY (col1)) ;;

Example:

SQL

CREATE TABLE dwh.table1 (col1 integer);

This command creates a table called table1 in the analytical storage schema dwh with column col1 of type integer.

For correct execution of this command, the schema name (if provided) must match the name of the analytical storage schema, and the table being created must not already exist in the specified schema.

SELECT INTO

The SELECT INTO command takes a source table or a join of source tables, creates a new table in the specified schema, and copies data from the source table(s).

Syntax

SQL

SELECT <projection_symbols> INTO <dwh_schema_name>.<table_name> FROM <from_clause>

Example 1:

SQL

SELECT * INTO dwh.table1 FROM source.table2;

This command creates a new table called table1 in the analytical storage dwh with the same column definition as source.table2 (the asterisk is used in the projection list).

Example 2:

SQL

SELECT t1.a, t2.b INTO dwh.table1 FROM source.table2 t1 JOIN source.table2 t2 ON t1.c = t2.c;

This command creates a new table called table1 in the analytical storage dwh with two columns a and b with the same type as in the source tables.

For correct execution of this command, the following criteria must be met:

the schema name must match the name of the analytical storage schema or a schema with a properly set importer.defaultSchema property;
the table provided in the INTO clause must not already exist in the specified schema;
the source tables must exist in the source schemas.

importer.useFullSchemaName Model Property

Both CREATE TABLE and SELECT INTO commands behave differently depending on the importer.useFullSchemaName model property in the data source. Its value can be either TRUE or FALSE, and the behaviour in both cases is as follows:

`importer.useFullSchemaName=TRUE`

Query	Schema in DB	Table name in DB	Table name in DV
`CREATE table test_dwh.public.p1 (...);;`	`public`	`p1`	`public.p1`
`CREATE table test_dwh.p2 (...) ;;`	`public`	`p2`	`public.p2`
`CREATE table test_dwh.dwh.d1 (...) ;;`	`dwh`	`d1`	`dwh.d1`
`CREATE table test_dwh.dwh2.d2 (...) ;;`	Query fails because the schema `dwh2` does not exist

`importer.useFullSchemaName=FALSE`

Query	Schema in DB	Table name in DB	Table name in DV
`CREATE table test_dwh.public.p1 (...);;`	`public`	`public.p1`	`public.p1`
`CREATE table test_dwh.p2 (...) ;;`	`public`	`p2`	`p2`
`CREATE table test_dwh.dwh.d1 (...) ;;`	`public`	`dwh.d1`	`dwh.d1`
`CREATE table test_dwh.dwh2.d2 (...) ;;`	`public`	`dwh2.d2`	`dwh2.d2`

For example, if you create a table called test_dwh.public.p1 and set the property to TRUE, the table name in your target database will be p1, and its fully qualified name will be some_DB.public.p1.

If you set the property to FALSE, the table name will be public.p1, and its fully qualified name will be some_DB.public.public.p1, where first "public" is the schema name in the database and the second one is part of the table name. In both cases, in the Data Virtuality Server it will be test_dwh.public.p1.