CommonDataModel Clickhouse support DDL

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/
Apr 02 '24 08:04 dridk
Let me work on this
Apr 03 '24 18:04 dridk
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.
Apr 03 '24 19:04 dridk
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);
Apr 03 '24 21:04 dridk
Hi, thank you so much for this! Have you had any experience running any OHDSI tools like the HADES stack https://ohdsi.github.io/Hades/ on clickhouse?
Sep 03 '24 14:09 clairblacketer