How to Initiate a Request to Become a Contributor

Data sources that wish to onboard datasets into the CDR and become a contributor should submit a request via the CCSQ Data and Analytics Request Form.  The full list of current CDR data contributors and available datasets can be found on the CDR Data Catalog.  Once a request is approved, contributors can expect a kick-off meeting with an overview of the integration process. A collaboration Confluence page will be setup which outlines the onboarding steps, responsible parties, datasets, and timeline for the integration. After this phase, contributors and the CCSQ Data & Analytics Modernization team will collaborate on making the data available based on approved timelines. Once an integration is scheduled and a contributor is able to commit the required resources to the effort, the setup will typically take less than one programming iteration or two weeks.  

Contributor Responsibilities include:

• Contributors must providing supporting documentation for their data sources. The minimum requested documentation for contributors can be found on our CDR Contributor Resources page.  It is recommended for documentation to be provided at least 2 weeks prior to the data access being granted so users have advanced notice of the data that will be available. CMS may delay granting access to data if minimum required documentation is not available and/or a plan is not established to provide it in the immediate future.  
• Contributors have the responsibility to answer questions on their data if the DAMOD team does not have the information to answer a data question.
• Contributors have the responsibility to keep the DAMOD team informed of data issues so proper communication can occur to the user community.
• Contributors cannot make changes to data without following the communication process outlined in the document below.

How to Create Source Parquet Files in S3

Data may be stored in many different database formats (Postgres, Redshift, Aurora, etc.) depending on the partnering organization. In order to make this data available in the CDR (Hive), the data must be in an approved format in order to be read by Hive. The preferred data format for partnering systems is parquet due to superior performance and lower storage cost however .CSV may also be used.  This data should be stored in the partnering ADO's S3 bucket.  It is also recommended to create directory versioning so that if parquet files need to be re-created, they can be created in a different version directory and not impact the current parquet files. The below example provides code of how data may be extracted from a Postgres database and formatted into parquet files located in S3.   

We recommend following Apache best practices with parquet row groups between 512MB-1GB in size.  

# Specifying dataframe column data types on read
jdbcDF3 = spark.read \
    .format("jdbc") \
    .option("url", "jdbc:postgresql:dbserver") \
    .option("dbtable", "schema.tablename") \
    .option("user", "username") \
    .option("password", "password") \
    .option("customSchema", "id DECIMAL(38, 0), name STRING") \
    .load()

# Set DataFrame output path to targeted data bucket and saved as Spark Table
jdbcDF3.write.option("path","/data/home/schem_name/table_name/").saveAsTable("table_name")

# export data to the targeted data bucket as parquet
jdbcDF3.write.format("parquet").save("jdbcDF3.parquet")

How to Configure Bucket Policy to allow for Reading of Data

Since the partnering ADO source data (parquet files) are stored in their own S3 bucket, cross-account access must be established to allow CDR necessary access to the S3 bucket. CDR adapted the resource-based policies and AWS identity and access management (IAM) policies method for accessing cross-account S3 bucket documented on AWS Support. In this case, partnering ADO is "Account A", and CDR is "Account B".  Data encryption is highly recommended either with the standard AWS server-side encryption or custom key stores. 

Below is an example of a resource-based policy with custom key stores configuration from partnering ADO.

{
    "Version": "2012-10-17",
    "Statement": [
        {
            "Effect": "Allow",
	        "Sid": "GrantS3BucketAccessToCDR", 
            "Action": [
                "s3:GetObject*",
				"s3:List*",
  				"s3:SelectObjectContent",
    			"kms:Encrypt",
    			"kms:Decrypt",
    			"kms:ReEncrypt*",
    			"kms:GenerateDataKey*",
    			"kms:DescribeKey"],
            "Resource": [
				"arn:aws:s3:::AccountABucketName/*",
				"arn:aws:s3:::AccountABucketName/",
				"arn:aws:s3:::AccoutA:key/AccountAKMSKey"]
        }
    ]
}

How to Request Creation of Hive Service Account and Databases in the CDR

In order for the partnering ADO to be able to register tables in the CDR, a service account and source hive database must be created. There is a standard format for all database names that is based off the CMS Data Taxonomy. The hive database name consists of (Taxonomy)_(Line of Business)_(Dataset Name). We rely on data contributors for input on the database names however all data sources should follow this same standardized format. The Hive service account will contain read/write permissions to the databases specified in the request.  As an initial part of the integration, a request should be submitted for the creation of the service account and necessary hive databases. 

1. Ensure that any developer(s) needing access to Hive have requested and been approved for the Quality Analytics Role.  This is recommended so contributors can validate that their data is made available correctly. The Service account is for automated, system to system connections only.  
2. Submit a ServiceNow Request to ADO-CDR-Support for the creation of a Hive Service Account.  

How to Register Tables in the CDR (Hive)

Once a service account and necessary hive databases are created, data contributors will be able to register tables in the CDR. Partnering organizations can make a JDBC connection to the CDR (Hive) and execute queries from their service account. Certain commands should not be run in production during working hours due to the possibility for user impact. Please see below for authorized times for command execution.  Comments are recommended when creating tables/columns however are not required. Depending on the individual AWS account's location, VPC Peering may be necessary. For certain accounts, a transit gateway is already created. 

Create Table:

--DROP TABLE IF EXITS <Table_name>;
CREATE EXTERNAL TABLE <Table_name> 
( 
Column_1 datatype Comment 'name string',
Column_2 datatype Comment 'age int',
column_3 datatype Comment '',
column_n datatype Comment ''
)
COMMENT 'Table Description'
PARTITIONED BY (partition_column data_type, month int, day int)
STORED AS PARQUET 
LOCATION 's3a://myBucket/myParquet/';

Alter Table:

1. ALTER TABLE table_name RENAME TO new_table_name;

2. ALTER TABLE table_name
[PARTITION partition_spec]

3. ALTER TABLE table_name SET TBLPROPERTIES table_properties;
table_properties:
: (property_name = property_value, property_name = property_value, ... );

MSCK [REPAIR] TABLE table_name [ADD/DROP/SYNC PARTITIONS];

How to Grant Access to Data for CDR Users

Once tables are registered in production, the partnering ADO should validate the data is displaying as expected. Once the partnering ADO is prepared to make the data available to CDR users, a notification should be submitted to grant access to users. Access to the data is controlled based on DUA. If there is a specific list of organizations that should have access granted, the partnering ADO can specify this list in the request however organizations must have a valid DUA. Organizations are expected to provide a list of valid EPPE entries for their dataset(s) that authorize organizations to receive access.     

1. Submit a ServiceNow Request to ADO-CDR-Support to grant access to users for specified hive database(s)
2. Submit all required CDR Contributor Source Documentation in the request so it can be uploaded to the CDR Data Catalog

How to Notify DAMOD Team of Changes to Data Definition Language

Once users are accessing your data in the CDR, any changes to data definitions will cause an impact to their analysis. It’s important for data contributors to ensure that DDL is not updated without proper communication to the DAMOD team. Since data contributors have full read/write access via their hive service account, they have the ability to make updates at any time however we request contributors follow documented processes.  For any changes that impact existing user code/processes, we request 10 business days notification. For any other changes such as new columns or new tables, we request 2 business days notification. The DAMOD team recommends the following guidance for communications:

How to Notify DAMOD Team of Data Refreshes

All data refresh dates are tracked on the CDR Data Catalog.  It is essential for data contributors to establish a process to communicate when data is refreshed.  Once a notification occurs, the data modernization team updates the CDR Data Catalog to reflect the availability of the refreshed data. 

1. Email notification to CDR data-loads <da-data-aaaafckf6equhoywenqh3uvaiu@hcqis.slack.com> when data is refreshed and when the next data refresh is scheduled to occur (if not on a recurrent schedule)
2. AWS Simple Notification Service (SNS) can also be setup to automate the notification into DAMOD Slack or email.