CDR Contributor Resources

CDR contributors to evaluate partitioning attribute(s) and assess performance before submitting a request to grant data access to users. CDR contributors should investigate use cases for their data and determine what partitioning scheme is best, based upon filters commonly used and expected query patterns. Data that has too many partitions can result in parquet file sizes that are too small and will greatly impact query performance even on small tables. Generally, it is also recommended that chosen attributes for partitioning have low cardinality. For example, data may be partitioned by a date (e.g., MM/YYYY) attribute if users commonly query for data within a certain date attribute range.

This section provides information regarding CDR documentation requirements for CDR Contributors. Please explore this tab for an overview of the CDR Data Catalog, minimum requested documentation from Data Contributors, an example of an approved data dictionary documentation, as well as expectations for data contributors. 

Overview of CDR Data Catalog

The CDR Data Catalog provides a location for data contributors to make documentation available to CDR users.  The DAMOD team provides a general layout document of each schema that includes table names, column names, and datatypes for every source in Hive. This document (located under the CDR Table Layouts column) is produced when the data is made available in the CDR or if data definitions are updated. Data contributors to the CDR are responsible for providing supplemental documentation necessary to support end-users of their data.  Such examples of documentation may include:

• Data Dictionaries

• Data Models

• User Guides

• Training Documents

Image Removed

Minimum Requested Documentation from Data Contributors 

Available documentation on the CDR Data Catalog will vary from source to source.  Some mature data sources will have excellent existing documentation/artifacts while newer sources may provide less.  At a minimum, the DAMOD team recommends data contributors to provide the following documents:

Document Name

Description

Necessary Information

Other Information

Data Dictionary

Data dictionaries can be various formats including Excel, PDF, and Word.  We recommend all documents provided to be 508 compliant for users.  We strongly recommend that data for columns that contain discrete, non-inferable or computed values have clear and detailed documentation.  

• Table Name

• Column Name

• Column Description

• Where Relevant - Domains of values, either listed in their entirety, or a link to a reference document

• Data Types

• Examples

• Other relevant information

• Null Option

Data Model

Data Models provide the keys for each table.  This provides users a mechanism for understanding how to join data across tables and identifying unique records (if applicable to your source).  

• Surrogate Keys

• Foreign Keys

• Primary Keys

• Indexes

Example Approved Data Dictionary

The example below meets all criteria including:

• Table Name

• Column Name

• Column Description (Comment)

• Code values for non-inferable data

• Null Option

• Data Type

Image Removed

Expectations 

1. Data Contributors provide updated documentation when source data changes
2. Data Contributors provide consistent versioning on documents 
3. Data Contributors provide accurate documentation for users
4. Data Contributors support questions about the data and documentation

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.  

Image Removed

How to Create Source Parquet Files 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

{
    "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. 

<style>#title-text {
		display:none !important;
	}
.aui-navgroup-primary {  
                font-size: 1.25em;  
                font-weight:  bold;  
    }  
.page-metadata-modification-info {
		display:none !important;
	}
</style>

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];