Create Or Alter Package
Overview
The package includes Create or Alter node types
Installation
- In Coalesce, open the Workspace where you wish to install the package.
- Go to the Build Setting of the Workspace, tab Packages, and click the Install button on the top right of the page.
- Paste the Package ID, and proceed with the installation process.
Description
Create or Alter package
Create or Alter package includes:
Create or Alter table
The Create or alter table creates table if it doesn’t exist, or alters it according to the table definition.
Create or Alter Node properties
Create Or Alter has two configuration groups:
Create or Alter Node Properties
Property | Description |
---|---|
Storage Location | (Required) Storage Location where the Create Or Alter Table will be created |
Node Type | (Required) Name of template used to create node objects |
Description | A description of the node's purpose |
Deploy Enabled | If TRUE the node will be deployed/redeployed when changes |
are detected If FALSE the node will not be deployed or will be dropped during redeployment |
Create table Options
Options | Description |
---|---|
Create As | Choose 'table' or 'transient table' |
Cluster key | True/False toggle for clustering: True: Specify clustering |
column and optional expressions False: No clustering | |
Change Tracking | Toggle to enable or disable change tracking on the table. |
Enable Schema Evolution | Toggle to enable or disable schema evolution. |
Inline Constraints | Toggle to enable inline constraints:True: Specify column name and constraint specification False: No constraints |
Out-Of-Line Constraints | Toggle to enable or disable out-of-line constraints. |
When enabled, you can define both primary and foreign keys. | |
Data Retention Time | Set the number of days for data retention for Time Travel actions. |
Default DDL Collation | Set the default collation specification for the DDL operations. |
Insert data Options
Options | Description |
---|---|
Multi Source | Toggle: True/False Implementation of SQL UNIONs True: Combine multiple sources in a single node True Options: - UNION: Combines with duplicate elimination - UNION ALL: Combines without duplicate elimination - INSERT: Individual insert for each source False: Single source node or multiple sources combined using a join. |
Truncate Before | Toggle: True/False This determines whether a table will be overwritten each time a task executes. True: Uses INSERT OVERWRITE False: Uses INSERT to append data |
Enable tests | Toggle: True/False Determines if tests are enabled |
Distinct | Toggle: True/False True: Group by All is invisible. DISTINCT data is chosen for processing. False: Group by All is visible. |
Group by All | Toggle: True/False True: DISTINCT is invisible, data grouped by all columns False: DISTINCT is visible |
Order By | Toggle: True/False True: Sort column and sort order drop down are visible and are required to form order by clause. False: Sort options invisible |
Limitations of Create or Alter Table
- Collation specifications cannot be altered.
- Setting or unsetting an inline primary key changes the nullability of the column accordingly
- New columns can only be added to the end of the column list
- Columns cannot be renamed. If you attempt to rename a column, the column is dropped and a new column is added.WHen you rename a column in Coalesce mapping grid.Ensure to move the same to the end of column list in mapping grid.
Create Or Alter Table Deployment
Create Or Alter Table Initial Deployment
When deployed for the first time into an environment the Create or Alter table node of materialization type table or transient table will execute the below stage:
Stage | Description |
---|---|
Create/Alter table/transient table | This will execute a CREATE OR ALTER statement and create a table in the target environment |