- Created by Rachel Dodge, last modified by Susan Pagan on Mar 02, 2021
You are viewing an old version of this page. View the current version.
Compare with Current View Page History
« Previous Version 18 Next »
CCSQ D&A HOME | CDR Data Catalog | Data Roadmap | CDR Contributor Resources | Mapping DUAs to CDR | Archive
Error rendering macro 'excerpt-include'
No link could be created for 'Requesting & Getting Access'.
CDR Contributor Resources
This page provides resources and documentation to the Centralized Data Repository (CDR) contributors. Please explore the tabs below for information regarding Data Issue Reporting as well as CDR Data Catalog Documentation Requirements for data contributors.
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
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. |
|
|
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). |
|
|
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
Expectations
- Data Contributors provide updated documentation when source data changes
- Data Contributors provide consistent versioning on documents
- Data Contributors provide accurate documentation for users
- Data Contributors support questions about the data and documentation
As a data contributor, you are responsible to inform the Data & Analytics team when there is an issue with your data. Please submit this form below within one business day when an issue is identified with your data. Please note that the following information will be needed in order to complete this form:
- Impact Data
- Issue Description
- Current Actions Taken to Resolve
- Planned Resolution Date
- Data Owner Technical Point of Contact
Submit this form to the Data & Analytics team by emailing it to us at HCQIS_Data@hcqis.org. Please note that you are also responsible to send follow-ups and updates via email after this form is submitted.
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
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")
Caution
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"] } ] }
Caution
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.
- 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.
- Submit a ServiceNow Request to ADO-CDR-Support for the creation of a Hive Service Account.
Caution
Once data is made available in Production and access is granted to users, data contributors can still make changes to data definitions. Any changes such as altering tables, new tables, or dropping tables will impact users.
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.
Example Commands | Authorized Time to Run Commands (EST) |
Create Table Expand source
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/'; Reference URL: https://spark.apache.org/docs/latest/sql-data-sources-hive-tables.html#specifying-storage-format-for-hive-tables | Anytime |
Alter Table Expand source
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, ... ); Reference URL: https://cwiki.apache.org/confluence/display/Hive/LanguageManual+DDL | 0000 - 0600 |
MSCK Repair Expand source
MSCK [REPAIR] TABLE table_name [ADD/DROP/SYNC PARTITIONS]; | 0000 - 0600 |
Partitioning Guidance
Note
The LOCATION statement must contain "s3a" in order to specify the proper file system protocol. To read more about "s3a", please find details on the Apache Hadoop support.
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.
- Submit a ServiceNow Request to ADO-CDR-Support to grant access to users for specified hive database(s)
- 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:
Data Definition Change | Notification Process | Notification Timeframe |
---|---|---|
| Email notification to CDR data-loads <data-loads@cvpcorp.com> | At least 10 Business Days |
| Email notification to CDR data-loads <data-loads@cvpcorp.com> | At least 2 Business Days |
All Data Definition Updates | Email notification to CDR data-loads <data-loads@cvpcorp.com> | Upon Code Execution in PROD |
Note
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.
- 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)
- AWS Simple Notification Service (SNS) can also be setup to automate the notification into DAMOD Slack or email.
- No labels