Background

The Centralized Data Repository (CDR) provides access to CCSQ data including claims, provider, beneficiary, and other data within a secure HCQIS cloud environment. The CDR increases the accessibility, security, quality, and timeliness of data. The goal of the CDR is to make data available from source systems with less transformations and better quality data. The result is a reduction in data duplication and data conflict since all CCSQ/HCQIS users use the same data from the same source. The goal of Bring Your own Data (BYOD) is to allow CDR data contributors to make data available directly to users with less copying of data.  

Data Onboarding Overview

This section describes how to onboard new datasets into the CDR and share with other organizations. To initiate a request, please submit a CCSQ Data and Analytics Request Form.  

• How to Create Source Parquet Files in S3
• How to Configure Bucket Policy to allow for Reading of Data
• How to Request Creation of Hive Service Account and Databases in the CDR
• How to Register Tables in the CDR (Hive)
• How to Grant Access to Data for CDR Users
• How to Notify DAMOD Team of Changes to Data Definition Language
• How to Notify DAMOD Team of Data Refreshes

Anchor
#SourceParquetFiles #SourceParquetFiles

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 CMS Data Reference Model. 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 ServiceNow request 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.    

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 <data-loads@cvpcorp.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.