Last updated: Dec 13, 2024
Check the Postgres, Db2 statements for the tables that are related to your workspaces.
| Subject area | Table name | Description |
|---|---|---|
| Workspaces | containers | Catalogs and projects that are created. |
| Workspaces | projects | A project and its members. |
| Workspaces | container_assets | The assets that are defined in a catalog or project. |
| Workspaces | container_data_assets | The data assets that are defined in a catalog or project. |
| Workspaces | container_data_asset_columns | The columns in a data asset. |
| Workspaces | data_asset_column_tags | The tags that are associated with the columns. |
| Workspaces | asset_collaborators | A list of all the members of an asset. |
| Workspaces | container_members | A list of all the members of a catalog or project. |
| Workspaces | data_asset_column_class_distribution | Suggested data classes for a column with confidence. |
| Workspaces | data_asset_column_prop_values | Custom attribute column values. |
| Workspaces | data_source_definitions | Details about the data source definitions. |
| Workspaces | connection_dsd_assignments | Details about the connections assigned to data source definitions. |
containers table
This table contains information about the catalogs and projects that are created.
This table has the following columns:
container_id- The identifier of the catalog or project.container_type- Specifies whether the type of the workspace is a catalog or project.name- The name of the catalog or project.description- The description of the workspace.is_governed- Specifies whether the catalog is governed or not. Applicable to catalogs only.created_by- The identifier of the user that created the workspace.created_on- The timestamp when the workspace was created.
Postgres
CREATE TABLE statement:
create table containers(container_id varchar(36) not null,
container_type varchar(16) not null,
name varchar(256) not null,
description clob,
is_governed boolean not null,
created_by varchar(128) not null,
created_on timestamp(12) not null,
tech_start TIMESTAMP(12) not null IMPLICITLY HIDDEN generated always as row begin,
tech_end TIMESTAMP(12) not null IMPLICITLY HIDDEN generated always as row
end,
ts_id TIMESTAMP(12) not null generated always as transaction start ID,
primary key(container_id),
period SYSTEM_TIME (tech_start,
tech_end) )
Db2
CREATE TABLE statement:
CREATE TABLE containers(container_id varchar(36) NOT NULL,
container_type varchar(16) NOT NULL,
name varchar(256) NOT NULL,
description clob,
is_governed boolean NOT NULL,
created_by varchar(128) NOT NULL,
created_on timestamp(12) NOT NULL,
tech_start TIMESTAMP(12) NOT NULL IMPLICITLY HIDDEN GENERATED ALWAYS AS ROW BEGIN,
tech_end TIMESTAMP(12) NOT NULL IMPLICITLY HIDDEN GENERATED ALWAYS AS ROW END,
ts_id TIMESTAMP(12) NOT NULL GENERATED ALWAYS AS TRANSACTION START ID,
PRIMARY KEY(container_id),
PERIOD SYSTEM_TIME (tech_start,
tech_end) )
projects table
This table contains information about a project and its members.
This table has the following columns:
project_id- The identifier of the project.project_name- The name of the project.enforce_members- Specifies whether the project members are scoped to the account or SAML of the creator.
Postgres
CREATE TABLE statement:
CREATE TABLE projects(project_id varchar(36) NOT NULL,
project_name varchar(256) NOT NULL,
enforce_members boolean NOT NULL,
tech_start TIMESTAMP(6) NOT NULL DEFAULT CURRENT_TIMESTAMP,
tech_end TIMESTAMP(6) NOT NULL DEFAULT to_timestamp('9999-12-30','YYYY-MM-DD'),
ts_id TIMESTAMP(6) NOT NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY(project_id));
ALTER TABLE statement:
alter table projects add constraint fk_projects_containers_11 foreign key (project_id) references containers(container_id) on
delete
cascade on
update
no action ;
Db2
CREATE TABLE statement:
create table projects(project_id varchar(36) not null,
project_name varchar(256) not null,
enforce_members boolean not null,
tech_start TIMESTAMP(12) not null IMPLICITLY HIDDEN generated always as row begin,
tech_end TIMESTAMP(12) not null IMPLICITLY HIDDEN generated always as row
end,
ts_id TIMESTAMP(12) not null generated always as transaction start ID,
primary key(project_id),
period SYSTEM_TIME (tech_start,
tech_end) )
ALTER TABLE statement:
alter table projects add constraint fk_projects_containers_11 foreign key (project_id) references containers(container_id) on
delete
cascade on
update
no action
container_assets table
This table contains information about the assets that are defined in a catalog or project.
This table has the following columns:
asset_id- The identifier of the asset.container_id- The identifier of the catalog or project.container_type- Specifies whether the type of the workspace is a catalog or project.name- The name of the asset.description- The description of the asset.owner- The identifier of the user that owns the asset.asset_type- The type of the asset.created_on- The timestamp when the asset was created.modified_on- The timestamp when the asset was last modified.modified_by- The identifier of the user that last modified the asset.rov- The rule of visibility of the asset. For more information, see Asset primary metadata document (or card).asset_state- The current state of the asset, whether available or deleted.source- The information about the source of the asset by providing the source system.source_additional_info- Provides additional information that is related to the source of the asset.resource_key- The unique identifier for an asset that is used for deduplication.asset_category- The asset category, either a user asset or system asset.rating- The average social rating of the asset.total_ratings- The total number of ratings of the asset.format- The format of the data that is associated with the asset, for example CSV, octet-stream, or PDF.origin_country- The country from which the data originated in the format complaint with ISO 3166 Country Codes.size- The size of the local asset.parent_folder_id- The asset ID of parent folder, if any, in which this asset resides.identity_key- The identity key of the data source definition assigned connected asset.
Postgres
CREATE TABLE statement:
CREATE TABLE "globalschema".container_assets(
container_id varchar(36) NOT NULL,
container_type varchar(16) NOT NULL,
asset_id varchar(128) NOT NULL,
name varchar(256) NOT NULL,
description text,
asset_type varchar(256) NOT NULL,
owner varchar(128) NOT NULL,
source text,
source_additional_info text,
resource_key varchar(256),
asset_category varchar(64),
rov integer NOT NULL,
asset_state varchar(32) NOT NULL,
format varchar(128),
asset_size varchar(36) NOT NULL,
created_on timestamp(6) NOT NULL,
modified_by varchar(128),
modified_on timestamp(6),
origin_country varchar(128) NOT NULL,
rating float,
total_ratings integer,
parent_folder_id varchar(36),
identity_key varchar(256),
tech_start TIMESTAMP(6) NOT NULL DEFAULT CURRENT_TIMESTAMP,
tech_end TIMESTAMP(6) NOT NULL DEFAULT to_timestamp('9999-12-30', 'YYYY-MM-DD'),
ts_id TIMESTAMP(6) NOT NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY(container_id, asset_id)
)
ALTER TABLE statement:
alter table container_assets add constraint fk_container_assets_containers_2 foreign key (container_id) references containers(container_id) on
delete
cascade on
update
no action
Db2
CREATE TABLE statement:
CREATE TABLE "globalschema".container_assets(
container_id varchar(36) NOT NULL,
container_type varchar(16) NOT NULL,
asset_id varchar(128) NOT NULL,
name varchar(256) NOT NULL,
description clob,
asset_type varchar(256) NOT NULL,
owner varchar(128) NOT NULL,
source clob,
source_additional_info clob,
resource_key varchar(256),
asset_category varchar(64),
rov integer NOT NULL,
asset_state varchar(32) NOT NULL,
format varchar(128),
asset_size varchar(36) NOT NULL,
created_on timestamp(12) NOT NULL,
modified_by varchar(128),
modified_on timestamp(12),
origin_country varchar(128) NOT NULL,
rating float,
total_ratings integer,
parent_folder_id varchar(36),
identity_key varchar(256),
tech_start TIMESTAMP(12) NOT NULL IMPLICITLY HIDDEN GENERATED ALWAYS AS ROW BEGIN,
tech_end TIMESTAMP(12) NOT NULL IMPLICITLY HIDDEN GENERATED ALWAYS AS ROW END,
ts_id TIMESTAMP(12) NOT NULL GENERATED ALWAYS AS TRANSACTION START ID,
PRIMARY KEY(container_id, asset_id),
PERIOD SYSTEM_TIME (tech_start, tech_end)
)
ALTER TABLE statement:
alter table container_assets add constraint fk_container_assets_containers_2 foreign key (container_id) references containers(container_id) on
delete
cascade on
update
no action
MS SQL Server
CREATE TABLE statement:
CREATE TABLE "globalschema".container_assets(
container_id varchar(36) NOT NULL,
container_type varchar(16) NOT NULL,
asset_id varchar(128) NOT NULL,
name varchar(256) NOT NULL,
description varchar(MAX),
asset_type varchar(256) NOT NULL,
owner varchar(128) NOT NULL,
source varchar(MAX),
source_additional_info varchar(MAX),
resource_key varchar(256),
asset_category varchar(64),
rov integer NOT NULL,
asset_state varchar(32) NOT NULL,
format varchar(128),
asset_size varchar(36) NOT NULL,
created_on DATETIME2 NOT NULL,
modified_by varchar(128),
modified_on DATETIME2,
origin_country varchar(128) NOT NULL,
rating float,
total_ratings integer,
parent_folder_id varchar(36),
identity_key varchar(256),
tech_start DATETIME2 GENERATED ALWAYS AS ROW START NOT NULL,
tech_end DATETIME2 GENERATED ALWAYS AS ROW END NOT NULL,
ts_id DATETIME2 DEFAULT CURRENT_TIMESTAMP NOT NULL,
CONSTRAINT PK_container_assets_globalschema PRIMARY KEY(container_id, asset_id),
PERIOD FOR SYSTEM_TIME (tech_start, tech_end)
) WITH (
SYSTEM_VERSIONING = ON (
HISTORY_TABLE = "globalschema".hist_container_assets
)
)
container_data_assets table
This table contains information about the data assets that are defined in a catalog or project.
This table has the following columns:
asset_id- The identifier of the asset.container_id- The identifier of the catalog or project.quality_score- The quality score of the asset as determined by profiling.attachments- The additional information that is associated with the data asset. For example, the connection information if some connected assets are present.metadata_import_id- Identifier for the metadata import.metadata_enrichment_id- Identifier for the metadata enrichment.reviewed_on- The assets on reviewed on data.connection_path- The relative connection path.published_to_container_id- The identifier of the target project.source_container_id- Identifier of the source workspace.source_asset_id- Identifier of the source asset.select_statement- The SQL statement to get SQL query type data asset.number_of_records- The total number of records present in the dataset.
Postgres
CREATE TABLE statement:
CREATE TABLE "globalschema".container_data_assets(
asset_id varchar(128) NOT NULL,
container_id varchar(36) NOT NULL,
attachments text,
quality_score float,
metadata_enrichment_id varchar(128),
metadata_import_id varchar(128),
reviewed_on timestamp(6),
connection_path varchar(256),
published_to_container_id varchar(36),
source_container_id varchar(36),
source_asset_id varchar(128),
num_columns bigint DEFAULT 0 NOT NULL,
num_rows_analysed bigint DEFAULT 0 NOT NULL,
last_profile_time timestamp(6),
table_schema varchar(256),
table_type varchar(16),
mdi_job_run_id varchar(128),
mdi_job_action varchar(16),
select_statement text,
number_of_records bigint DEFAULT 0 NOT NULL,
tech_start TIMESTAMP(6) NOT NULL DEFAULT CURRENT_TIMESTAMP,
tech_end TIMESTAMP(6) NOT NULL DEFAULT to_timestamp('9999-12-30', 'YYYY-MM-DD'),
ts_id TIMESTAMP(6) NOT NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY(container_id, asset_id)
)
ALTER TABLE statement:
alter table catalog_data_assets add constraint fk_catalog_data_assets_container_assets_12 foreign key (container_id,
asset_id) references container_assets(container_id,
asset_id) on
delete
cascade on
update
no action
Db2
CREATE TABLE statement:
CREATE TABLE "globalschema".container_data_assets(
asset_id varchar(128) NOT NULL,
container_id varchar(36) NOT NULL,
attachments text,
quality_score float,
metadata_enrichment_id varchar(128),
metadata_import_id varchar(128),
reviewed_on timestamp(6),
connection_path varchar(256),
published_to_container_id varchar(36),
source_container_id varchar(36),
source_asset_id varchar(128),
num_columns bigint DEFAULT 0 NOT NULL,
num_rows_analysed bigint DEFAULT 0 NOT NULL,
last_profile_time timestamp(6),
table_schema varchar(256),
table_type varchar(16),
mdi_job_run_id varchar(128),
mdi_job_action varchar(16),
select_statement text,
number_of_records bigint DEFAULT 0 NOT NULL,
tech_start TIMESTAMP(6) NOT NULL DEFAULT CURRENT_TIMESTAMP,
tech_end TIMESTAMP(6) NOT NULL DEFAULT to_timestamp('9999-12-30', 'YYYY-MM-DD'),
ts_id TIMESTAMP(6) NOT NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY(container_id, asset_id)
)
ALTER TABLE statement:
alter table catalog_data_assets add constraint fk_catalog_data_assets_container_assets_12 foreign key (container_id,
asset_id) references container_assets(container_id,
asset_id) on
delete
cascade on
update
no action
MS SQL Server
CREATE TABLE statement:
CREATE TABLE "globalschema".container_data_assets(
asset_id varchar(128) NOT NULL,
container_id varchar(36) NOT NULL,
attachments varchar(MAX),
quality_score float,
metadata_enrichment_id varchar(128),
metadata_import_id varchar(128),
reviewed_on DATETIME2,
connection_path varchar(256),
published_to_container_id varchar(36),
source_container_id varchar(36),
source_asset_id varchar(128),
num_columns bigint DEFAULT 0 NOT NULL,
num_rows_analysed bigint DEFAULT 0 NOT NULL,
last_profile_time DATETIME2,
table_schema varchar(256),
table_type varchar(16),
mdi_job_run_id varchar(128),
mdi_job_action varchar(16),
select_statement varchar(MAX),
number_of_records bigint DEFAULT 0 NOT NULL,
tech_start DATETIME2 GENERATED ALWAYS AS ROW START NOT NULL,
tech_end DATETIME2 GENERATED ALWAYS AS ROW END NOT NULL,
ts_id DATETIME2 DEFAULT CURRENT_TIMESTAMP NOT NULL,
CONSTRAINT PK_container_data_assets_globalschema PRIMARY KEY(container_id, asset_id),
PERIOD FOR SYSTEM_TIME (tech_start, tech_end)
) WITH (
SYSTEM_VERSIONING = ON (
HISTORY_TABLE = "globalschema".hist_container_data_assets
)
)
container_data_asset_columns table
This table contains information about the individual columns in a data asset.
This table has the following columns:
asset_id- The identifier of the asset.container_id- The identifier of the catalog or project.name- The name of the column.column_id- The identifier that is associated with the column, when the identifier is available.quality_score- The quality score of the column as determined by profiling.description- The description of the column.reviewed_on- The assets on reviewed on data.is_nullable- Identifies if the column is nullable.mean- Mean of values in the column.native_data_type- Native data type of the column.inferred_data_type- Inferred data type of the column.number_of_records- The total number of records present in the dataset.
Postgres
CREATE TABLE statement:
CREATE TABLE container_data_asset_columns(
asset_id varchar(128) NOT NULL,
container_id varchar(36) NOT NULL,
column_id varchar(128),
name varchar(256) NOT NULL,
description text,
quality_score float,
reviewed_on timestamp(6),
source_data_type varchar(32),
distinct_count bigint DEFAULT 0 NOT NULL,
unique_count bigint DEFAULT 0 NOT NULL,
null_count bigint DEFAULT 0 NOT NULL,
empty_count bigint DEFAULT 0 NOT NULL,
min_length bigint DEFAULT 0 NOT NULL,
max_length bigint DEFAULT 0 NOT NULL,
mean_length bigint DEFAULT 0 NOT NULL,
std_deviation float DEFAULT 0 NOT NULL,
is_nullable decimal(1) CHECK (
is_nullable in (0, 1)
) DEFAULT 1 NOT NULL,
mdi_job_run_id varchar(128),
mean float,
native_data_type varchar(256),
inferred_data_type varchar(128),
tech_start TIMESTAMP(6) NOT NULL DEFAULT CURRENT_TIMESTAMP,
tech_end TIMESTAMP(6) NOT NULL DEFAULT to_timestamp('9999-12-30', 'YYYY-MM-DD'),
ts_id TIMESTAMP(6) NOT NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY(container_id, asset_id, name)
)
ALTER TABLE statement:
alter table catalog_data_asset_columns add constraint fk_catalog_data_asset_columns_catalog_data_assets_13 foreign key (container_id,
asset_id) references catalog_data_assets(container_id,
asset_id) on
delete
cascade on
update
no action
Db2
CREATE TABLE statement:
CREATE TABLE container_data_asset_columns(
asset_id varchar(128) NOT NULL,
container_id varchar(36) NOT NULL,
column_id varchar(128),
name varchar(256) NOT NULL,
description text,
quality_score float,
reviewed_on timestamp(6),
source_data_type varchar(32),
distinct_count bigint DEFAULT 0 NOT NULL,
unique_count bigint DEFAULT 0 NOT NULL,
null_count bigint DEFAULT 0 NOT NULL,
empty_count bigint DEFAULT 0 NOT NULL,
min_length bigint DEFAULT 0 NOT NULL,
max_length bigint DEFAULT 0 NOT NULL,
mean_length bigint DEFAULT 0 NOT NULL,
std_deviation float DEFAULT 0 NOT NULL,
is_nullable decimal(1) CHECK (
is_nullable in (0, 1)
) DEFAULT 1 NOT NULL,
mdi_job_run_id varchar(128),
mean float,
native_data_type varchar(256),
inferred_data_type varchar(128),
tech_start TIMESTAMP(6) NOT NULL DEFAULT CURRENT_TIMESTAMP,
tech_end TIMESTAMP(6) NOT NULL DEFAULT to_timestamp('9999-12-30', 'YYYY-MM-DD'),
ts_id TIMESTAMP(6) NOT NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY(container_id, asset_id, name)
)
ALTER TABLE statement:
alter table catalog_data_asset_columns add constraint fk_catalog_data_asset_columns_catalog_data_assets_13 foreign key (container_id,
asset_id) references catalog_data_assets(container_id,
asset_id) on
delete
cascade on
update
no action
data_asset_column_tags table
This table contains information about the tags that are associated with the columns.
This table has the following columns:
column_name- The name of the column.asset_id- The identifier of the data asset.container_id- The identifier of the catalog or project.tagname- The name of the associated tag.
Postgres
CREATE TABLE statement:
create table data_asset_column_tags(asset_id varchar(128) not null,
container_id varchar(36) not null,
tag_name varchar(256) not null,
column_name varchar(256) not null,
tech_start TIMESTAMP(6) not null default CURRENT_TIMESTAMP,
tech_end TIMESTAMP(6) not null default to_timestamp('9999-12-30', 'YYYY-MM-DD'),
ts_id TIMESTAMP(6) not null default CURRENT_TIMESTAMP,
primary key(container_id,
asset_id,
column_name,
tag_name));
ALTER TABLE statements:
alter table data_asset_column_tags add constraint fk_data_asset_column_tags_tags_5 foreign key (tag_name) references tags(tag_name) on
delete
cascade on
update
no action
alter table data_asset_column_tags add constraint fk_data_asset_column_tags_catalog_data_asset_columns_15 foreign key (container_id,
asset_id,
column_name) references catalog_data_asset_columns(container_id,
asset_id,
name) on
delete
cascade on
update
no action
Db2
CREATE TABLE statement:
create table data_asset_column_tags(asset_id varchar(128) not null,
container_id varchar(36) not null,
tag_name varchar(256) not null,
column_name varchar(256) not null,
tech_start TIMESTAMP(12) not null IMPLICITLY HIDDEN generated always as row begin,
tech_end TIMESTAMP(12) not null IMPLICITLY HIDDEN generated always as row
end,
ts_id TIMESTAMP(12) not null generated always as transaction start ID,
primary key(container_id,
asset_id,
column_name,
tag_name),
period SYSTEM_TIME (tech_start,
tech_end) )
ALTER TABLE statements:
alter table data_asset_column_tags add constraint fk_data_asset_column_tags_tags_5 foreign key (tag_name) references tags(tag_name) on
delete
cascade on
update
no action
alter table data_asset_column_tags add constraint fk_data_asset_column_tags_catalog_data_asset_columns_15 foreign key (container_id,
asset_id,
column_name) references catalog_data_asset_columns(container_id,
asset_id,
name) on
delete
cascade on
update
no action
asset_collaborators table
This table contains a list of all the members of an asset.
This table has the following columns:
asset_id- The identifier of the asset.container_id- The identifier of the catalog.user_id- The identifier of the collaborator.role- The roles that are assigned to the user, for example: owner, editor, or viewer.is_group- This column defines whether asset collaborator is a group or a user.
Postgres
CREATE TABLE statement:
CREATE TABLE asset_collaborators(asset_id varchar(128) NOT NULL,
container_id varchar(36) NOT NULL,user_id varchar(128) NOT NULL,
role varchar(128) DEFAULT 'na' NOT NULL,is_group decimal(1) CHECK
(is_group in (0,1)) DEFAULT 0 NOT NULL,tech_start TIMESTAMP(6)
NOT NULL DEFAULT CURRENT_TIMESTAMP,tech_end TIMESTAMP(6) NOT NULL DEFAULT
to_timestamp('9999-12-30','YYYY-MM-DD'),ts_id TIMESTAMP(6)
NOT NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY(container_id,asset_id,user_id,role))
ALTER TABLE statement:
alter table asset_collaborators add constraint fk_asset_collaborators_container_assets_6 foreign key (container_id,
asset_id) references container_assets(container_id,
asset_id) on
delete
cascade on
update
no action
Db2
CREATE TABLE statement:
CREATE TABLE asset_collaborators(asset_id varchar(128) NOT NULL,
container_id varchar(36) NOT NULL,user_id varchar(128) NOT NULL,
role varchar(128) DEFAULT 'na' NOT NULL,is_group decimal(1)
CHECK (is_group in (0,1)) DEFAULT 0 NOT NULL,tech_start TIMESTAMP(6)
NOT NULL DEFAULT CURRENT_TIMESTAMP,tech_end TIMESTAMP(6)
NOT NULL DEFAULT to_timestamp('9999-12-30','YYYY-MM-DD'),
ts_id TIMESTAMP(6) NOT NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY(container_id,asset_id,user_id,role))
ALTER TABLE statement:
alter table asset_collaborators add constraint fk_asset_collaborators_container_assets_6 foreign key (container_id,
asset_id) references container_assets(container_id,
asset_id) on
delete
cascade on
update
no action
container_members table
This table contains a list of all members of a catalog or project.
This table has the following columns:
container_id- The identifier of the catalog or project.member_id- The identifier of the member or group.is_group- Specifies whether the member is a group. If the value is true, the value of the member_id column is the identifier of the group.role- The roles that are assigned to the user or user group, for example Admin, Editor or Viewer.
Postgres
CREATE TABLE statement:
create table container_members(container_id varchar(36) not null,
is_group boolean not null,
member_id varchar(64) not null,
role varchar(32) not null,
tech_start TIMESTAMP(6) not null default CURRENT_TIMESTAMP,
tech_end TIMESTAMP(6) not null default to_timestamp('9999-12-30', 'YYYY-MM-DD'),
ts_id TIMESTAMP(6) not null default CURRENT_TIMESTAMP,
primary key(container_id,
member_id));
ALTER TABLE statement:
alter table container_members add constraint fk_container_members_containers_1 foreign key (container_id) references containers(container_id) on
delete
cascade on
update
no action
Db2
CREATE TABLE statement:
create table container_members(container_id varchar(36) not null,
is_group boolean not null,
member_id varchar(64) not null,
role varchar(32) not null,
tech_start TIMESTAMP(12) not null IMPLICITLY HIDDEN generated always as row begin,
tech_end TIMESTAMP(12) not null IMPLICITLY HIDDEN generated always as row
end,
ts_id TIMESTAMP(12) not null generated always as transaction start ID,
primary key(container_id,
member_id),
period SYSTEM_TIME (tech_start,
tech_end) )
ALTER TABLE statement:
alter table container_members add constraint fk_container_members_containers_1 foreign key (container_id) references containers(container_id) on
delete
cascade on
update
no action
data_asset_column_class_distribution table
This table has the following columns:
issue_id- The identifier for the asset issue.asset_id- The asset identifier.container_id- The identifier of the project.column_name- The name of the column for which you are running the data quality rules and analysis.data_class_artifact_id- The identifier of the data class artifact.
Postgres
CREATE TABLE statement:
CREATE TABLE IF NOT EXISTS data_asset_column_class_distribution (
asset_id character varying(36) COLLATE pg_catalog.default NOT NULL,
container_id character varying(36) COLLATE pg_catalog.default NOT NULL,
column_name character varying(256) COLLATE pg_catalog.default NOT NULL,
data_class_artifact_id character varying(128) COLLATE pg_catalog.default NOT NULL,
confidence double precision,
count bigint NOT NULL DEFAULT 0,
tech_start timestamp(6) without time zone NOT NULL DEFAULT CURRENT_TIMESTAMP,
tech_end timestamp(6) without time zone NOT NULL DEFAULT to_timestamp('9999-12-30' :: text, 'YYYY-MM-DD' :: text),
ts_id timestamp(6) without time zone NOT NULL DEFAULT CURRENT_TIMESTAMP,
CONSTRAINT data_asset_column_class_distribution_pkey PRIMARY KEY (
asset_id,
container_id,
column_name,
data_class_artifact_id
),
CONSTRAINT fk_data_asset_column_class_distribution_container_data_asset_co FOREIGN KEY (container_id, asset_id, column_name) REFERENCES container_data_asset_columns (container_id, asset_id, name) MATCH SIMPLE ON UPDATE NO ACTION ON DELETE CASCADE,
CONSTRAINT fk_data_asset_column_class_distribution_governance_artifacts_8 FOREIGN KEY (data_class_artifact_id) REFERENCES governance_artifacts (artifact_id) MATCH SIMPLE ON UPDATE NO ACTION ON DELETE CASCADE
)
Db2
CREATE TABLE statement:
CREATE TABLE DATA_ASSET_COLUMN_CLASS_DISTRIBUTION (
ASSET_ID VARCHAR(36 OCTETS) NOT NULL,
CONTAINER_ID VARCHAR(36 OCTETS) NOT NULL,
COLUMN_NAME VARCHAR(256 OCTETS) NOT NULL,
DATA_CLASS_ARTIFACT_ID VARCHAR(128 OCTETS) NOT NULL,
CONFIDENCE DOUBLE,
COUNT BIGINT NOT NULL WITH DEFAULT 0,
TECH_START TIMESTAMP(12) NOT NULL IMPLICITLY HIDDEN GENERATED ALWAYS AS ROW BEGIN,
TECH_END TIMESTAMP(12) NOT NULL IMPLICITLY HIDDEN GENERATED ALWAYS AS ROW END,
TS_ID TIMESTAMP(12) NOT NULL GENERATED ALWAYS AS TRANSACTION START ID,
PERIOD SYSTEM_TIME (TECH_START, TECH_END)
) IN USERSPACE1 ORGANIZE BY ROW;
ALTER TABLE statements:
ALTER TABLE
DATA_ASSET_COLUMN_CLASS_DISTRIBUTION
ADD
PRIMARY KEY (
ASSET_ID,
CONTAINER_ID,
COLUMN_NAME,
DATA_CLASS_ARTIFACT_ID
) ENFORCED;
ALTER TABLE
DATA_ASSET_COLUMN_CLASS_DISTRIBUTION
ADD
VERSIONING USE HISTORY TABLE HIST_DATA_ASSET_COLUMN_CLASS_DISTRIBUTION;
ALTER TABLE
DATA_ASSET_COLUMN_CLASS_DISTRIBUTION
ADD
CONSTRAINT FK_DATA_ASSET_COLUMN_CLASS_DISTRIBUTION_CONTAINER_DATA_ASSET_COLUMNS_7 FOREIGN KEY (CONTAINER_ID, ASSET_ID, COLUMN_NAME) REFERENCES CONTAINER_DATA_ASSET_COLUMNS (CONTAINER_ID, ASSET_ID, NAME) ON DELETE CASCADE ON UPDATE NO ACTION ENFORCED ENABLE QUERY OPTIMIZATION;
ALTER TABLE
DATA_ASSET_COLUMN_CLASS_DISTRIBUTION
ADD
CONSTRAINT FK_DATA_ASSET_COLUMN_CLASS_DISTRIBUTION_GOVERNANCE_ARTIFACTS_8 FOREIGN KEY (DATA_CLASS_ARTIFACT_ID) REFERENCES GOVERNANCE_ARTIFACTS (ARTIFACT_ID) ON DELETE CASCADE ON UPDATE NO ACTION ENFORCED ENABLE QUERY OPTIMIZATION;
data_asset_column_prop_values table
This table has the following columns:
column_name-container_id- Specifies the identifier of the catalog/project.asset_id- The identifier for an asset to which the custom property is associated with.property_id- The identifier for a custom property.property_group_id- The identifier for the group that has a property defined below it.value_id- The value identifier for a given column property.asset_type- The type of asset for which the custom property is applicable to.property_text_value- The value of the text custom property.property_num_value- The value of the number custom property.property_date_value- The value of the date custom property.property_value_type- The type of the value i.e. string, number, date, user, group.
Postgres
CREATE TABLE statement:
CREATE TABLE data_asset_column_prop_values(
column_name varchar(256) NOT NULL,
container_id varchar(36) NOT NULL,
asset_id varchar(128) NOT NULL,
property_id varchar(256) NOT NULL,
property_group_id varchar(256) NOT NULL,
value_id varchar(256) NOT NULL,
asset_type varchar(256) NOT NULL,
property_text_value text,
property_num_value double precision,
property_date_value timestamp(6),
property_value_type varchar(16),
tech_start TIMESTAMP(6) NOT NULL DEFAULT CURRENT_TIMESTAMP,
tech_end TIMESTAMP(6) NOT NULL DEFAULT to_timestamp('9999-12-30', 'YYYY-MM-DD'),
ts_id TIMESTAMP(6) NOT NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY(
column_name, container_id, asset_id,
property_id, property_group_id,
value_id, asset_type
)
)
Db2
CREATE TABLE statement:
CREATE TABLE data_asset_column_prop_values(
column_name varchar(256) NOT NULL,
container_id varchar(36) NOT NULL,
asset_id varchar(128) NOT NULL,
property_id varchar(256) NOT NULL,
property_group_id varchar(256) NOT NULL,
value_id varchar(256) NOT NULL,
asset_type varchar(256) NOT NULL,
property_text_value text,
property_num_value double precision,
property_date_value timestamp(6),
property_value_type varchar(16),
tech_start TIMESTAMP(6) NOT NULL DEFAULT CURRENT_TIMESTAMP,
tech_end TIMESTAMP(6) NOT NULL DEFAULT to_timestamp('9999-12-30', 'YYYY-MM-DD'),
ts_id TIMESTAMP(6) NOT NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY(
column_name, container_id, asset_id,
property_id, property_group_id,
value_id, asset_type
)
)
ALTER TABLE statements:
ALTER TABLE data_asset_column_prop_values ADD CONSTRAINT fk_data_asset_column_prop_values_container_data_asset_columns_1 FOREIGN KEY (column_name,
asset_id,
container_id) REFERENCES container_data_asset_columns(name,
asset_id,
container_id) ON
DELETE
CASCADE ON
UPDATE
NO ACTION
ALTER TABLE data_asset_column_prop_values ADD CONSTRAINT fk_data_asset_column_prop_values_asset_type_custom_properties_2 FOREIGN KEY (property_id,
property_group_id,
asset_type) REFERENCES asset_type_custom_properties(property_id,
property_group_id,
asset_type) ON
DELETE
CASCADE ON
UPDATE
NO ACTION
data_source_definitions table
This table includes details about the data source definitions.
This table has the following columns:
dsd_container_id- The identifier of the container ( catalog ) in which the DSD asset resides. This is mostly the platform asset catalog.dsd_asset_id- The identifier of the DSD asset.dsd_asset_name- The name of the DSD asset.data_source_type_id- Type Identifier for the underlying datasource type.data_source_type- The name of the underlying datasource type.data_source_state- Defines the datasource state.data_source_encoding- Defines encoding used for datasource.data_source_protection_method- Datasource protection method which is dependent on the underlying connection type.
Postgres
CREATE TABLE statement:
CREATE TABLE "globalschema".data_source_definitions(
dsd_container_id varchar(36) NOT NULL,
dsd_asset_id varchar(128) NOT NULL,
dsd_asset_name varchar(128),
data_source_type_id varchar(36),
data_source_type varchar(128),
data_source_state varchar(16),
data_source_encoding varchar(16),
data_source_protection_method varchar(32),
tech_start TIMESTAMP(6) NOT NULL DEFAULT CURRENT_TIMESTAMP,
tech_end TIMESTAMP(6) NOT NULL DEFAULT to_timestamp('9999-12-30', 'YYYY-MM-DD'),
ts_id TIMESTAMP(6) NOT NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY(dsd_container_id, dsd_asset_id)
)
Db2
CREATE TABLE statement:
CREATE TABLE "globalschema".data_source_definitions(
dsd_container_id varchar(36) NOT NULL,
dsd_asset_id varchar(128) NOT NULL,
dsd_asset_name varchar(128),
data_source_type_id varchar(36),
data_source_type varchar(128),
data_source_state varchar(16),
data_source_encoding varchar(16),
data_source_protection_method varchar(32),
tech_start DATETIME2 GENERATED ALWAYS AS ROW START NOT NULL,
tech_end DATETIME2 GENERATED ALWAYS AS ROW END NOT NULL,
ts_id DATETIME2 DEFAULT CURRENT_TIMESTAMP NOT NULL,
CONSTRAINT PK_data_source_definitions_globalschema PRIMARY KEY(dsd_container_id, dsd_asset_id),
PERIOD FOR SYSTEM_TIME (tech_start, tech_end)
) WITH (
SYSTEM_VERSIONING = ON (
HISTORY_TABLE = "globalschema".hist_data_source_definitions
)
)
MS SQL Server
CREATE TABLE statement:
CREATE TABLE "globalschema".data_source_definitions(
dsd_container_id varchar(36) NOT NULL,
dsd_asset_id varchar(128) NOT NULL,
dsd_asset_name varchar(128),
data_source_type_id varchar(36),
data_source_type varchar(128),
data_source_state varchar(16),
data_source_encoding varchar(16),
data_source_protection_method varchar(32),
tech_start DATETIME2 GENERATED ALWAYS AS ROW START NOT NULL,
tech_end DATETIME2 GENERATED ALWAYS AS ROW END NOT NULL,
ts_id DATETIME2 DEFAULT CURRENT_TIMESTAMP NOT NULL,
CONSTRAINT PK_data_source_definitions_globalschema PRIMARY KEY(dsd_container_id, dsd_asset_id),
PERIOD FOR SYSTEM_TIME (tech_start, tech_end)
) WITH (
SYSTEM_VERSIONING = ON (
HISTORY_TABLE = "globalschema".hist_data_source_definitions
)
)
connection_dsd_assignments table
This table includes details about the connections assigned to data source definitions.
This table has the following columns:
container_id- The identifier of container in which the connection asset exists.connection_id- The identifier of the connection asset.dsd_container_id- Container/Catalog that contains the DSD asset related to this connection.dsd_asset_id- The identifier of the DSD asset.data_source_type_id- Type Identifier for the underlying datasource type.data_source_type- Name of the underlying datasource type.dsd_asset_name- Asset name of the DSD asset.dsd_connection_name- Connection asset name that is attached part of the linked datasource.
Postgres
CREATE TABLE statement:
CREATE TABLE "globalschema".connection_dsd_assignments(
container_id varchar(36) NOT NULL,
connection_id varchar(128) NOT NULL,
dsd_container_id varchar(36) NOT NULL,
dsd_asset_id varchar(128) NOT NULL,
data_source_type_id varchar(36),
data_source_type varchar(128),
dsd_asset_name varchar(128),
dsd_connection_name varchar(128),
tech_start TIMESTAMP(6) NOT NULL DEFAULT CURRENT_TIMESTAMP,
tech_end TIMESTAMP(6) NOT NULL DEFAULT to_timestamp('9999-12-30', 'YYYY-MM-DD'),
ts_id TIMESTAMP(6) NOT NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY(
container_id, connection_id, dsd_container_id,
dsd_asset_id
)
)
Db2
CREATE TABLE statement:
CREATE TABLE "globalschema".connection_dsd_assignments(
container_id varchar(36) NOT NULL,
connection_id varchar(128) NOT NULL,
dsd_container_id varchar(36) NOT NULL,
dsd_asset_id varchar(128) NOT NULL,
data_source_type_id varchar(36),
data_source_type varchar(128),
dsd_asset_name varchar(128),
dsd_connection_name varchar(128),
tech_start TIMESTAMP(6) NOT NULL DEFAULT CURRENT_TIMESTAMP,
tech_end TIMESTAMP(6) NOT NULL DEFAULT to_timestamp('9999-12-30', 'YYYY-MM-DD'),
ts_id TIMESTAMP(6) NOT NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY(
container_id, connection_id, dsd_container_id,
dsd_asset_id
)
)
MS SQL Server
CREATE TABLE statement:
CREATE TABLE "globalschema".connection_dsd_assignments(
container_id varchar(36) NOT NULL,
connection_id varchar(128) NOT NULL,
dsd_container_id varchar(36) NOT NULL,
dsd_asset_id varchar(128) NOT NULL,
data_source_type_id varchar(36),
data_source_type varchar(128),
dsd_asset_name varchar(128),
dsd_connection_name varchar(128),
tech_start DATETIME2 GENERATED ALWAYS AS ROW START NOT NULL,
tech_end DATETIME2 GENERATED ALWAYS AS ROW END NOT NULL,
ts_id DATETIME2 DEFAULT CURRENT_TIMESTAMP NOT NULL,
CONSTRAINT PK_connection_dsd_assignments_globalschema PRIMARY KEY(
container_id, connection_id, dsd_container_id,
dsd_asset_id
),
PERIOD FOR SYSTEM_TIME (tech_start, tech_end)
) WITH (
SYSTEM_VERSIONING = ON (
HISTORY_TABLE = "globalschema".hist_connection_dsd_assignments
)
)
Learn more
Parent topic: Reporting tables