Skip to main content

Datometry Documentation

Setup Datometry Metadata Store

Hyper-Q maintains Metadata Store in Azure Synapse. Metadata Store contains annotations for advanced emulations of complex SQL constructs.

Create the Metadata Store Schema

Using a query editor such as Synapse Studio, create the following objects in the Azure Synapse instance.

CREATE SCHEMA [__DTM_MDSTORE];
CREATE TABLE [__DTM_MDSTORE].[MDSTORE_TBL] (
   [schemaname] VARCHAR (256),
   [objectname] VARCHAR (256),
   [colname] VARCHAR (256),
   [propname] VARCHAR (256),
   [propvalue] VARCHAR (MAX),
   [seqno] INT 
) WITH (DISTRIBUTION = ROUND_ROBIN, CLUSTERED INDEX (Objectname));
CREATE TABLE [__DTM_MDSTORE].[MDSTORE_WRKTBL] (
   [schemaname] VARCHAR (256),
   [objectname] VARCHAR (256),
   [colname] VARCHAR (256),
   [propname] VARCHAR (256),
   [propvalue] VARCHAR (MAX),
   [seqno] INT 
) WITH (DISTRIBUTION = ROUND_ROBIN, HEAP);
GRANT SELECT ON SCHEMA::[__DTM_MDSTORE] TO PUBLIC;

Note

Granting SELECT access to PUBLIC on the schema of Metadata Store is critical for the emulation of various Teradata functions, including those using system tables so that Hyper-Q can properly process queries and return result sets.

Create a Dedicated User to Access Metadata Store

Metadata Store is maintained and accessed using a specific user ID. The user ID to be used for this purpose is specified in the dtm.ini configuration file. To learn more, see Example Hyper-Q Configuration File for Azure Synapse

To set up a dedicated user ID for accessing Metadata Store, first create a login with a valid password.

  1. Connect to the master database of the Azure Synapse instance.

    CREATE LOGIN [DTM_MDS_LOGIN] WITH PASSWORD = 'password';

  2. Connect to the default database of the Azure Synapse instance and execute the following commands to associate the login accordingly.

    CREATE USER [DTM_MDS_USER] FOR LOGIN [DTM_MDS_LOGIN];
CREATE ROLE [DTM_MDS_ROLE];
ALTER AUTHORIZATION ON SCHEMA::[__DTM_MDSTORE] TO [DTM_MDS_ROLE];
GRANT SELECT, INSERT, UPDATE, DELETE, ALTER ON
   SCHEMA::[__DTM_MDSTORE] TO [DTM_MDS_ROLE];
GRANT CREATE TABLE TO [DTM_MDS_ROLE];
GRANT CREATE VIEW TO [DTM_MDS_ROLE];
GRANT CREATE PROCEDURE TO [DTM_MDS_ROLE];
EXEC sp_addrolemember 'DTM_MDS_ROLE', 'DTM_MDS_USER';
EXEC sp_addrolemember 'staticrc20', 'DTM_MDS_USER';
CREATE WORKLOAD CLASSIFIER [wgcDTMMD]
WITH (WORKLOAD_GROUP='staticrc20'
, MEMBERNAME = 'DTM_MDS_USER'
, IMPORTANCE = HIGH);