Create Schemas and Tables

Data Definition Language (DDL) enables developers to create and manage Connect domain objects and tables.

In this chapter, we’ll learn how to CREATE and DROP schemas and tables.

 

Create a Schema

A schema defines the logical structure of the database that contains your data tables. By default, Connect includes four different schemas that serve most users’ needs: delta, xactly, xdms, and staging. (For definitions, see Show Schemas.)

To create a new schema, use CREATE:

create schema playground;

create_schema_playground

Check that our new schema has been added:

show schemas;

show_schemas_playground

NOTE: While Connect allows you to create custom schemas, we recommend sticking to the four default schemas, and using the delta schema for custom tables.

 

Creating Tables

There are two ways to create a table. You can populate it directly with a CREATE statement, or you can use INSERT to create a table dynamically from the results of a SELECT statement from another table.

Connect’s xSQL syntax is similar to the CREATE TABLE syntax in most relational databases:

CREATE TABLE IF NOT EXISTS <schema_name.table_name> ({column_name data_type}[, …]);

NOTES:

  • You must supply the schema_name along with table_name you are creating, separated by a period.
  • CREATE TABLE can only create custom tables in the delta schema (or custom schemas like ‘playground’ from above). Other schemas contain stock system tables.
  • The IF NOT EXISTS/IF EXISTS clause is optional.
  • You can JOIN tables across schemas.

These data types are supported:

  • boolean
  • byte
  • date
  • date_time
  • decimal(precision, scale)
  • double
  • duration
  • float
  • integer
  • long
  • offset_date_time
  • offset_time
  • short
  • varchar(length)/string(length)
  • time
  • zoned_date_time

 

In our first exercise, let’s create a new table that includes all supported data types:

create table if not exists playground.test_table
(row_num long,
row_source varchar(128),
created_at date_time,
string1 varchar(128),
string2 varchar(128),
byte1 byte,
byte2 byte,
short1 short,
short2 short,
integer1 integer,
integer2 integer,
long1 long,
long2 long,
float1 float,
float2 float,
double1 double,
double2 double,
decimal1 decimal(28,10),
decimal2 decimal(28,10),
date_time1 date_time,
date_time2 date_time,
date1 date,
date2 date,
time1 time,
time2 time,
offset_time1 offset_time,
offset_time2 offset_time,
offset_date_time1 offset_date_time,
offset_date_time2 offset_date_time,
zoned_date_time1 zoned_date_time,
zoned_date_time2 zoned_date_time,
boolean1 boolean,
boolean2 boolean);

Screen Shot 2016-07-18 at 10.41.11 AM

Create a Table Dynamically with SELECT

You can also use INSERT to create a new table dynamically from an existing table, based on the results of a SELECT statement.

In this example, we create a new table, playground.custom_participant_position, from the results of a SELECT INNER JOIN statement:

 

insert into Delta(TableName='playground.custom_participant_position', Overwrite=true)
select employee_id, 
first_name, 
hire_date, 
pos.name, 
part.effective_start_date as part_effective_start_date, 
part.effective_end_date as part_effective_end_date
from xactly.xc_participant part
inner join xactly.xc_pos_part_assignment asg on asg.participant_id = part.participant_id
inner join xactly.xc_position pos on pos.position_id = asg.position_id;

Screen Shot 2016-07-18 at 10.50.56 AM

 

Our new table is ready to be queried:

select * 
from playground.custom_participant_position;

Screen Shot 2016-07-18 at 10.55.47 AM

 

Drop the Table and Schema

Connect allows you to drop some tables, schemas, and other domain objects. The xactly, staging, and xdms schemas and tables are protected. You cannot drop those. However, you can drop tables found in the delta and any custom schemas. Drop schemas with caution, as you will lose all tables found in the schema.

drop table if exists playground.custom_participant_position;

drop_table

drop schema if exists playground;

drop_schema