Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

Sign up for GitHub

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Jump to bottom

Clickhouse support DDL #630

Open
dridk opened this issue Apr 2, 2024 · 3 comments
Open

Clickhouse support DDL #630

dridk opened this issue Apr 2, 2024 · 3 comments

Comments

@dridk
Copy link

dridk commented Apr 2, 2024

Hi,

it would be greate to have an omop shema for clickhouse. It is a column oriented sgbd very suitable for analytical

https://clickhouse.com/
@dridk dridk changed the title Clickhouse support Clickhouse support DDL Apr 2, 2024
@dridk
Copy link
Author

dridk commented Apr 3, 2024

Let me work on this

@dridk
Copy link
Author

dridk commented Apr 3, 2024

Ok.. it looks like you generate DDL automatically from a script .
I wonder how can I optimize shema data type. For instance using Uint8 for age and Uint32 for index.

@dridk
Copy link
Author

dridk commented Apr 3, 2024 •
edited

Just for sharing mine ..
Note: clickhouse doesn't have concept of Foreign key or index. It is a column oriented database.

--clickhouse CDM DDL Specification for OMOP Common Data Model 5.4
--HINT DISTRIBUTE ON KEY (person_id)
CREATE TABLE cdmDatabaseSchema.person (
			person_id UInt32 NOT NULL,
			gender_concept_id UInt32 NOT NULL,
			year_of_birth UInt16 NOT NULL,
			month_of_birth UInt16 NULL,
			day_of_birth UInt16 NULL,
			birth_datetime DateTime NULL,
			race_concept_id UInt32 NOT NULL,
			ethnicity_concept_id UInt32 NOT NULL,
			location_id UInt32 NULL,
			provider_id UInt32 NULL,
			care_site_id UInt32 NULL,
			person_source_value String NULL,
			gender_source_value String NULL,
			gender_source_concept_id UInt32 NULL,
			race_source_value String NULL,
			race_source_concept_id UInt32 NULL,
			ethnicity_source_value String NULL,
			ethnicity_source_concept_id UInt32 NULL )
			ENGINE = MergeTree()
			PRIMARY KEY (person_id);

--HINT DISTRIBUTE ON KEY (person_id)
CREATE TABLE cdmDatabaseSchema.observation_period (
			observation_period_id UInt32 NOT NULL,
			person_id UInt32 NOT NULL,
			observation_period_start_date Date NOT NULL,
			observation_period_end_date Date NOT NULL,
			period_type_concept_id UInt32 NOT NULL )
			ENGINE = MergeTree()
			PRIMARY KEY (observation_period_id);

--HINT DISTRIBUTE ON KEY (person_id)
CREATE TABLE cdmDatabaseSchema.visit_occurrence (
			visit_occurrence_id UInt32 NOT NULL,
			person_id UInt32 NOT NULL,
			visit_concept_id UInt32 NOT NULL,
			visit_start_date Date NOT NULL,
			visit_start_datetime DateTime NULL,
			visit_end_date Date NOT NULL,
			visit_end_datetime DateTime NULL,
			visit_type_concept_id UInt32 NOT NULL,
			provider_id UInt32 NULL,
			care_site_id UInt32 NULL,
			visit_source_value String NULL,
			visit_source_concept_id UInt32 NULL,
			admitted_from_concept_id UInt32 NULL,
			admitted_from_source_value String NULL,
			discharged_to_concept_id UInt32 NULL,
			discharged_to_source_value String NULL,
			preceding_visit_occurrence_id UInt32 NULL )
			ENGINE = MergeTree()
			PRIMARY KEY (visit_occurrence_id);
--HINT DISTRIBUTE ON KEY (person_id)
CREATE TABLE cdmDatabaseSchema.visit_detail (
			visit_detail_id UInt32 NOT NULL,
			person_id UInt32 NOT NULL,
			visit_detail_concept_id UInt32 NOT NULL,
			visit_detail_start_date Date NOT NULL,
			visit_detail_start_datetime DateTime NULL,
			visit_detail_end_date Date NOT NULL,
			visit_detail_end_datetime DateTime NULL,
			visit_detail_type_concept_id UInt32 NOT NULL,
			provider_id UInt32 NULL,
			care_site_id UInt32 NULL,
			visit_detail_source_value String NULL,
			visit_detail_source_concept_id UInt32 NULL,
			admitted_from_concept_id UInt32 NULL,
			admitted_from_source_value String NULL,
			discharged_to_source_value String NULL,
			discharged_to_concept_id UInt32 NULL,
			preceding_visit_detail_id UInt32 NULL,
			parent_visit_detail_id UInt32 NULL,
			visit_occurrence_id UInt32 NOT NULL )
			ENGINE = MergeTree()
			PRIMARY KEY (visit_detail_id);

--HINT DISTRIBUTE ON KEY (person_id)
CREATE TABLE cdmDatabaseSchema.condition_occurrence (
			condition_occurrence_id UInt32 NOT NULL,
			person_id UInt32 NOT NULL,
			condition_concept_id UInt32 NOT NULL,
			condition_start_date Date NOT NULL,
			condition_start_datetime DateTime NULL,
			condition_end_date Date NULL,
			condition_end_datetime DateTime NULL,
			condition_type_concept_id UInt32 NOT NULL,
			condition_status_concept_id UInt32 NULL,
			stop_reason String NULL,
			provider_id UInt32 NULL,
			visit_occurrence_id UInt32 NULL,
			visit_detail_id UInt32 NULL,
			condition_source_value String NULL,
			condition_source_concept_id UInt32 NULL,
			condition_status_source_value String NULL )
			ENGINE = MergeTree()
			PRIMARY KEY (condition_occurrence_id);

--HINT DISTRIBUTE ON KEY (person_id)
CREATE TABLE cdmDatabaseSchema.drug_exposure (
			drug_exposure_id UInt32 NOT NULL,
			person_id UInt32 NOT NULL,
			drug_concept_id UInt32 NOT NULL,
			drug_exposure_start_date Date NOT NULL,
			drug_exposure_start_datetime DateTime NULL,
			drug_exposure_end_date Date NOT NULL,
			drug_exposure_end_datetime DateTime NULL,
			verbatim_end_date Date NULL,
			drug_type_concept_id UInt32 NOT NULL,
			stop_reason String NULL,
			refills UInt32 NULL,
			quantity Float64 NULL,
			days_supply UInt32 NULL,
			sig String NULL,
			route_concept_id UInt32 NULL,
			lot_number String NULL,
			provider_id UInt32 NULL,
			visit_occurrence_id UInt32 NULL,
			visit_detail_id UInt32 NULL,
			drug_source_value String NULL,
			drug_source_concept_id UInt32 NULL,
			route_source_value String NULL,
			dose_unit_source_value String NULL )
			ENGINE = MergeTree()
			PRIMARY KEY (drug_exposure_id);

--HINT DISTRIBUTE ON KEY (person_id)
CREATE TABLE cdmDatabaseSchema.procedure_occurrence (
			procedure_occurrence_id UInt32 NOT NULL,
			person_id UInt32 NOT NULL,
			procedure_concept_id UInt32 NOT NULL,
			procedure_date Date NOT NULL,
			procedure_datetime DateTime NULL,
			procedure_end_date Date NULL,
			procedure_end_datetime DateTime NULL,
			procedure_type_concept_id UInt32 NOT NULL,
			modifier_concept_id UInt32 NULL,
			quantity UInt32 NULL,
			provider_id UInt32 NULL,
			visit_occurrence_id UInt32 NULL,
			visit_detail_id UInt32 NULL,
			procedure_source_value String NULL,
			procedure_source_concept_id UInt32 NULL,
			modifier_source_value String NULL )
			ENGINE = MergeTree()
			PRIMARY KEY (procedure_occurrence_id);

--HINT DISTRIBUTE ON KEY (person_id)
CREATE TABLE cdmDatabaseSchema.device_exposure (
			device_exposure_id UInt32 NOT NULL,
			person_id UInt32 NOT NULL,
			device_concept_id UInt32 NOT NULL,
			device_exposure_start_date Date NOT NULL,
			device_exposure_start_datetime DateTime NULL,
			device_exposure_end_date Date NULL,
			device_exposure_end_datetime DateTime NULL,
			device_type_concept_id UInt32 NOT NULL,
			unique_device_id String NULL,
			production_id String NULL,
			quantity UInt32 NULL,
			provider_id UInt32 NULL,
			visit_occurrence_id UInt32 NULL,
			visit_detail_id UInt32 NULL,
			device_source_value String NULL,
			device_source_concept_id UInt32 NULL,
			unit_concept_id UInt32 NULL,
			unit_source_value String NULL,
			unit_source_concept_id UInt32 NULL )
			ENGINE = MergeTree()
			PRIMARY KEY (device_exposure_id);

--HINT DISTRIBUTE ON KEY (person_id)
CREATE TABLE cdmDatabaseSchema.measurement (
			measurement_id UInt32 NOT NULL,
			person_id UInt32 NOT NULL,
			measurement_concept_id UInt32 NOT NULL,
			measurement_date Date NOT NULL,
			measurement_datetime DateTime NULL,
			measurement_time String NULL,
			measurement_type_concept_id UInt32 NOT NULL,
			operator_concept_id UInt32 NULL,
			value_as_number Float64 NULL,
			value_as_concept_id UInt32 NULL,
			unit_concept_id UInt32 NULL,
			range_low Float64 NULL,
			range_high Float64 NULL,
			provider_id UInt32 NULL,
			visit_occurrence_id UInt32 NULL,
			visit_detail_id UInt32 NULL,
			measurement_source_value String NULL,
			measurement_source_concept_id UInt32 NULL,
			unit_source_value String NULL,
			unit_source_concept_id UInt32 NULL,
			value_source_value String NULL,
			measurement_event_id UInt32 NULL,
			meas_event_field_concept_id UInt32 NULL )
			ENGINE = MergeTree()
			PRIMARY KEY (measurement_id);

--HINT DISTRIBUTE ON KEY (person_id)
CREATE TABLE cdmDatabaseSchema.observation (
			observation_id UInt32 NOT NULL,
			person_id UInt32 NOT NULL,
			observation_concept_id UInt32 NOT NULL,
			observation_date Date NOT NULL,
			observation_datetime DateTime NULL,
			observation_type_concept_id UInt32 NOT NULL,
			value_as_number Float64 NULL,
			value_as_string String NULL,
			value_as_concept_id UInt32 NULL,
			qualifier_concept_id UInt32 NULL,
			unit_concept_id UInt32 NULL,
			provider_id UInt32 NULL,
			visit_occurrence_id UInt32 NULL,
			visit_detail_id UInt32 NULL,
			observation_source_value String NULL,
			observation_source_concept_id UInt32 NULL,
			unit_source_value String NULL,
			qualifier_source_value String NULL,
			value_source_value String NULL,
			observation_event_id UInt32 NULL,
			obs_event_field_concept_id UInt32 NULL )
			ENGINE = MergeTree()
			PRIMARY KEY (observation_id);

--HINT DISTRIBUTE ON KEY (person_id)
CREATE TABLE cdmDatabaseSchema.death (
			person_id UInt32 NOT NULL,
			death_date Date NOT NULL,
			death_datetime DateTime NULL,
			death_type_concept_id UInt32 NULL,
			cause_concept_id UInt32 NULL,
			cause_source_value String NULL,
			cause_source_concept_id UInt32 NULL )
			ENGINE = MergeTree()
			PRIMARY KEY (person_id);

--HINT DISTRIBUTE ON KEY (person_id)
CREATE TABLE cdmDatabaseSchema.note (
			note_id UInt32 NOT NULL,
			person_id UInt32 NOT NULL,
			note_date Date NOT NULL,
			note_datetime DateTime NULL,
			note_type_concept_id UInt32 NOT NULL,
			note_class_concept_id UInt32 NOT NULL,
			note_title String NULL,
			note_String String NOT NULL,
			encoding_concept_id UInt32 NOT NULL,
			language_concept_id UInt32 NOT NULL,
			provider_id UInt32 NULL,
			visit_occurrence_id UInt32 NULL,
			visit_detail_id UInt32 NULL,
			note_source_value String NULL,
			note_event_id UInt32 NULL,
			note_event_field_concept_id UInt32 NULL )
			ENGINE = MergeTree()
			PRIMARY KEY (note_id);

--HINT DISTRIBUTE ON RANDOM
CREATE TABLE cdmDatabaseSchema.note_nlp (
			note_nlp_id UInt32 NOT NULL,
			note_id UInt32 NOT NULL,
			section_concept_id UInt32 NULL,
			snippet String NULL,
			offset String NULL,
			lexical_variant String NOT NULL,
			note_nlp_concept_id UInt32 NULL,
			note_nlp_source_concept_id UInt32 NULL,
			nlp_system String NULL,
			nlp_date Date NOT NULL,
			nlp_datetime DateTime NULL,
			term_exists FixedString(1) NULL,
			term_temporal String NULL,
			term_modifiers String NULL )
			ENGINE = MergeTree()
			PRIMARY KEY (note_nlp_id);

--HINT DISTRIBUTE ON KEY (person_id)
CREATE TABLE cdmDatabaseSchema.specimen (
			specimen_id UInt32 NOT NULL,
			person_id UInt32 NOT NULL,
			specimen_concept_id UInt32 NOT NULL,
			specimen_type_concept_id UInt32 NOT NULL,
			specimen_date Date NOT NULL,
			specimen_datetime DateTime NULL,
			quantity Float64 NULL,
			unit_concept_id UInt32 NULL,
			anatomic_site_concept_id UInt32 NULL,
			disease_status_concept_id UInt32 NULL,
			specimen_source_id String NULL,
			specimen_source_value String NULL,
			unit_source_value String NULL,
			anatomic_site_source_value String NULL,
			disease_status_source_value String NULL )
			ENGINE = MergeTree()
			PRIMARY KEY (specimen_id);

--HINT DISTRIBUTE ON RANDOM
CREATE TABLE cdmDatabaseSchema.fact_relationship (
			domain_concept_id_1 UInt32 NOT NULL,
			fact_id_1 UInt32 NOT NULL,
			domain_concept_id_2 UInt32 NOT NULL,
			fact_id_2 UInt32 NOT NULL,
			relationship_concept_id UInt32 NOT NULL )
			ENGINE = MergeTree()
			PRIMARY KEY (fact_id_1,fact_id_2);

--HINT DISTRIBUTE ON RANDOM
CREATE TABLE cdmDatabaseSchema.location (
			location_id UInt32 NOT NULL,
			address_1 String NULL,
			address_2 String NULL,
			city String NULL,
			state String NULL,
			zip String NULL,
			county String NULL,
			location_source_value String NULL,
			country_concept_id UInt32 NULL,
			country_source_value String NULL,
			latitude Float64 NULL,
			longitude Float64 NULL )
			ENGINE = MergeTree()
			PRIMARY KEY (location_id);

--HINT DISTRIBUTE ON RANDOM
CREATE TABLE cdmDatabaseSchema.care_site (
			care_site_id UInt32 NOT NULL,
			care_site_name String NULL,
			place_of_service_concept_id UInt32 NULL,
			location_id UInt32 NULL,
			care_site_source_value String NULL,
			place_of_service_source_value String NULL )
			ENGINE = MergeTree()
			PRIMARY KEY (care_site_id);

			--HINT DISTRIBUTE ON RANDOM
CREATE TABLE cdmDatabaseSchema.provider (
			provider_id UInt32 NOT NULL,
			provider_name String NULL,
			npi String NULL,
			dea String NULL,
			specialty_concept_id UInt32 NULL,
			care_site_id UInt32 NULL,
			year_of_birth UInt32 NULL,
			gender_concept_id UInt32 NULL,
			provider_source_value String NULL,
			specialty_source_value String NULL,
			specialty_source_concept_id UInt32 NULL,
			gender_source_value String NULL,
			gender_source_concept_id UInt32 NULL )
			ENGINE = MergeTree()
			PRIMARY KEY (provider_id);

--HINT DISTRIBUTE ON KEY (person_id)
CREATE TABLE cdmDatabaseSchema.payer_plan_period (
			payer_plan_period_id UInt32 NOT NULL,
			person_id UInt32 NOT NULL,
			payer_plan_period_start_date Date NOT NULL,
			payer_plan_period_end_date Date NOT NULL,
			payer_concept_id UInt32 NULL,
			payer_source_value String NULL,
			payer_source_concept_id UInt32 NULL,
			plan_concept_id UInt32 NULL,
			plan_source_value String NULL,
			plan_source_concept_id UInt32 NULL,
			sponsor_concept_id UInt32 NULL,
			sponsor_source_value String NULL,
			sponsor_source_concept_id UInt32 NULL,
			family_source_value String NULL,
			stop_reason_concept_id UInt32 NULL,
			stop_reason_source_value String NULL,
			stop_reason_source_concept_id UInt32 NULL )
			ENGINE = MergeTree()
			PRIMARY KEY (payer_plan_period_id);

--HINT DISTRIBUTE ON RANDOM
CREATE TABLE cdmDatabaseSchema.cost (
			cost_id UInt32 NOT NULL,
			cost_event_id UInt32 NOT NULL,
			cost_domain_id String NOT NULL,
			cost_type_concept_id UInt32 NOT NULL,
			currency_concept_id UInt32 NULL,
			total_charge Float64 NULL,
			total_cost Float64 NULL,
			total_paid Float64 NULL,
			paid_by_payer Float64 NULL,
			paid_by_patient Float64 NULL,
			paid_patient_copay Float64 NULL,
			paid_patient_coinsurance Float64 NULL,
			paid_patient_deductible Float64 NULL,
			paid_by_primary Float64 NULL,
			paid_ingredient_cost Float64 NULL,
			paid_dispensing_fee Float64 NULL,
			payer_plan_period_id UInt32 NULL,
			amount_allowed Float64 NULL,
			revenue_code_concept_id UInt32 NULL,
			revenue_code_source_value String NULL,
			drg_concept_id UInt32 NULL,
			drg_source_value String NULL )
			ENGINE = MergeTree()
			PRIMARY KEY (cost_id);

--HINT DISTRIBUTE ON KEY (person_id)
CREATE TABLE cdmDatabaseSchema.drug_era (
			drug_era_id UInt32 NOT NULL,
			person_id UInt32 NOT NULL,
			drug_concept_id UInt32 NOT NULL,
			drug_era_start_date Date NOT NULL,
			drug_era_end_date Date NOT NULL,
			drug_exposure_count UInt32 NULL,
			gap_days UInt32 NULL )
			ENGINE = MergeTree()
			PRIMARY KEY (drug_era_id);

--HINT DISTRIBUTE ON KEY (person_id)
CREATE TABLE cdmDatabaseSchema.dose_era (
			dose_era_id UInt32 NOT NULL,
			person_id UInt32 NOT NULL,
			drug_concept_id UInt32 NOT NULL,
			unit_concept_id UInt32 NOT NULL,
			dose_value Float64 NOT NULL,
			dose_era_start_date Date NOT NULL,
			dose_era_end_date Date NOT NULL )
			ENGINE = MergeTree()
			PRIMARY KEY (dose_era_id);

--HINT DISTRIBUTE ON KEY (person_id)
CREATE TABLE cdmDatabaseSchema.condition_era (
			condition_era_id UInt32 NOT NULL,
			person_id UInt32 NOT NULL,
			condition_concept_id UInt32 NOT NULL,
			condition_era_start_date Date NOT NULL,
			condition_era_end_date Date NOT NULL,
			condition_occurrence_count UInt32 NULL )
			ENGINE = MergeTree()
			PRIMARY KEY (condition_era_id);

--HINT DISTRIBUTE ON KEY (person_id)
CREATE TABLE cdmDatabaseSchema.episode (
			episode_id UInt32 NOT NULL,
			person_id UInt32 NOT NULL,
			episode_concept_id UInt32 NOT NULL,
			episode_start_date Date NOT NULL,
			episode_start_datetime DateTime NULL,
			episode_end_date Date NULL,
			episode_end_datetime DateTime NULL,
			episode_parent_id UInt32 NULL,
			episode_number UInt32 NULL,
			episode_object_concept_id UInt32 NOT NULL,
			episode_type_concept_id UInt32 NOT NULL,
			episode_source_value String NULL,
			episode_source_concept_id UInt32 NULL )
			ENGINE = MergeTree()
			PRIMARY KEY (episode_id);

--HINT DISTRIBUTE ON RANDOM
CREATE TABLE cdmDatabaseSchema.episode_event (
			episode_id UInt32 NOT NULL,
			event_id UInt32 NOT NULL,
			episode_event_field_concept_id UInt32 NOT NULL )
			ENGINE = MergeTree()
			PRIMARY KEY (episode_id);

--HINT DISTRIBUTE ON RANDOM
CREATE TABLE cdmDatabaseSchema.metadata (
			metadata_id UInt32 NOT NULL,
			metadata_concept_id UInt32 NOT NULL,
			metadata_type_concept_id UInt32 NOT NULL,
			name String NOT NULL,
			value_as_string String NULL,
			value_as_concept_id UInt32 NULL,
			value_as_number Float64 NULL,
			metadata_date Date NULL,
			metadata_datetime DateTime NULL )
			ENGINE = MergeTree()
			PRIMARY KEY (metadata_id);

--HINT DISTRIBUTE ON RANDOM
CREATE TABLE cdmDatabaseSchema.cdm_source (
			cdm_source_name String NOT NULL,
			cdm_source_abbreviation String NOT NULL,
			cdm_holder String NOT NULL,
			source_description String NULL,
			source_documentation_reference String NULL,
			cdm_etl_reference String NULL,
			source_release_date Date NOT NULL,
			cdm_release_date Date NOT NULL,
			cdm_version String NULL,
			cdm_version_concept_id UInt32 NOT NULL,
			vocabulary_version String NOT NULL )
			ENGINE = MergeTree()
			PRIMARY KEY (cdm_source_name);

--HINT DISTRIBUTE ON RANDOM
CREATE TABLE cdmDatabaseSchema.concept (
			concept_id UInt32 NOT NULL,
			concept_name String NOT NULL,
			domain_id String NOT NULL,
			vocabulary_id String NOT NULL,
			concept_class_id String NOT NULL,
			standard_concept String NULL,
			concept_code String NOT NULL,
			valid_start_date Date NOT NULL,
			valid_end_date Date NOT NULL,
			invalid_reason FixedString(1) NULL )
			ENGINE = MergeTree()
			PRIMARY KEY (concept_id);

--HINT DISTRIBUTE ON RANDOM
CREATE TABLE cdmDatabaseSchema.vocabulary (
			vocabulary_id String NOT NULL,
			vocabulary_name String NOT NULL,
			vocabulary_reference String NULL,
			vocabulary_version String NULL,
			vocabulary_concept_id UInt32 NOT NULL )
			ENGINE = MergeTree()
			PRIMARY KEY (vocabulary_id);

--HINT DISTRIBUTE ON RANDOM
CREATE TABLE cdmDatabaseSchema.domain (
			domain_id String NOT NULL,
			domain_name String NOT NULL,
			domain_concept_id UInt32 NOT NULL )
			ENGINE = MergeTree()
			PRIMARY KEY (domain_id);

--HINT DISTRIBUTE ON RANDOM
CREATE TABLE cdmDatabaseSchema.concept_class (
			concept_class_id String NOT NULL,
			concept_class_name String NOT NULL,
			concept_class_concept_id UInt32 NOT NULL )
			ENGINE = MergeTree()
			PRIMARY KEY (concept_class_id);

--HINT DISTRIBUTE ON RANDOM
CREATE TABLE cdmDatabaseSchema.concept_relationship (
			concept_id_1 UInt32 NOT NULL,
			concept_id_2 UInt32 NOT NULL,
			relationship_id String NOT NULL,
			valid_start_date Date NOT NULL,
			valid_end_date Date NOT NULL,
			invalid_reason String NULL )
			ENGINE = MergeTree()
			PRIMARY KEY (concept_id_1,concept_id_2);

--HINT DISTRIBUTE ON RANDOM
CREATE TABLE cdmDatabaseSchema.relationship (
			relationship_id String NOT NULL,
			relationship_name String NOT NULL,
			is_hierarchical String NOT NULL,
			defines_ancestry String NOT NULL,
			reverse_relationship_id String NOT NULL,
			relationship_concept_id UInt32 NOT NULL )
			ENGINE = MergeTree()
			PRIMARY KEY (relationship_id);

--HINT DISTRIBUTE ON RANDOM
CREATE TABLE cdmDatabaseSchema.concept_synonym (
			concept_id UInt32 NOT NULL,
			concept_synonym_name String NOT NULL,
			language_concept_id UInt32 NOT NULL )
			ENGINE = MergeTree()
			PRIMARY KEY (concept_id);

--HINT DISTRIBUTE ON RANDOM
CREATE TABLE cdmDatabaseSchema.concept_ancestor (
			ancestor_concept_id UInt32 NOT NULL,
			descendant_concept_id UInt32 NOT NULL,
			min_levels_of_separation UInt32 NOT NULL,
			max_levels_of_separation UInt32 NOT NULL )
			ENGINE = MergeTree()
			PRIMARY KEY (ancestor_concept_id,descendant_concept_id);

--HINT DISTRIBUTE ON RANDOM
CREATE TABLE cdmDatabaseSchema.source_to_concept_map (
			source_code String NOT NULL,
			source_concept_id UInt32 NOT NULL,
			source_vocabulary_id String NOT NULL,
			source_code_description String NULL,
			target_concept_id UInt32 NOT NULL,
			target_vocabulary_id String NOT NULL,
			valid_start_date Date NOT NULL,
			valid_end_date Date NOT NULL,
			invalid_reason String NULL )
			ENGINE = MergeTree()
			PRIMARY KEY (source_code);

--HINT DISTRIBUTE ON RANDOM
CREATE TABLE cdmDatabaseSchema.drug_strength (
			drug_concept_id UInt32 NOT NULL,
			ingredient_concept_id UInt32 NOT NULL,
			amount_value Float64 NULL,
			amount_unit_concept_id UInt32 NULL,
			numerator_value Float64 NULL,
			numerator_unit_concept_id UInt32 NULL,
			denominator_value Float64 NULL,
			denominator_unit_concept_id UInt32 NULL,
			box_size UInt32 NULL,
			valid_start_date Date NOT NULL,
			valid_end_date Date NOT NULL,
			invalid_reason String NULL )
			ENGINE = MergeTree()
			PRIMARY KEY (drug_concept_id);

--HINT DISTRIBUTE ON RANDOM
CREATE TABLE cdmDatabaseSchema.cohort (
			cohort_definition_id UInt32 NOT NULL,
			subject_id UInt32 NOT NULL,
			cohort_start_date Date NOT NULL,
			cohort_end_date Date NOT NULL )
			ENGINE = MergeTree()
			PRIMARY KEY (cohort_definition_id);

--HINT DISTRIBUTE ON RANDOM
CREATE TABLE cdmDatabaseSchema.cohort_definition (
			cohort_definition_id UInt32 NOT NULL,
			cohort_definition_name String NOT NULL,
			cohort_definition_description String NULL,
			definition_type_concept_id UInt32 NOT NULL,
			cohort_definition_syntax String NULL,
			subject_concept_id UInt32 NOT NULL,
			cohort_initiation_date Date NULL )
			ENGINE = MergeTree()
			PRIMARY KEY (cohort_definition_id);

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Assignees
No one assigned
Labels
None yet
Projects
None yet
Milestone
No milestone
Development

No branches or pull requests
1 participant
@dridk