omop_duckdb/omop_duckdb.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "0e703218-a45a-4446-b60e-f24b932b51bf",
   "metadata": {},
   "outputs": [],
   "source": [
    "pip install duckdb"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "662367b0-19ed-4bbd-99cc-cdb307c85c48",
   "metadata": {},
   "outputs": [],
   "source": [
    "pip install pandas  "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "c08cf80e-9e98-4a67-8e93-13684c7347b1",
   "metadata": {},
   "outputs": [],
   "source": [
    "pip install ipython-sql"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "277f9ac0-8c98-417e-a717-a559b569f83a",
   "metadata": {},
   "outputs": [],
   "source": [
    "pip install SQLAlchemy"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "e9dba172-93a6-4411-858f-bff79c0bfa6e",
   "metadata": {},
   "outputs": [],
   "source": [
    "pip install duckdb-engine"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "00607ece-c8c8-48fa-85b6-2abd1d3867ce",
   "metadata": {},
   "outputs": [],
   "source": [
    "import duckdb\n",
    "import pandas as pd\n",
    "import sqlalchemy\n",
    "# No need to import duckdb_engine\n",
    "#  SQLAlchemy will auto-detect the driver needed based on your connection string!\n",
    "\n",
    "# Import ipython-sql Jupyter extension to create SQL cells\n",
    "%load_ext sql"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "a65fef50-5c7c-4756-a9a4-e475985e6f5f",
   "metadata": {},
   "outputs": [],
   "source": [
    "%config SqlMagic.autopandas = True\n",
    "%config SqlMagic.feedback = False\n",
    "%config SqlMagic.displaycon = False"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "44bc2cd0-eef3-4ae5-98df-7de742eb8ce3",
   "metadata": {},
   "outputs": [],
   "source": [
    "%sql duckdb:///:memory:\n",
    "# %sql duckdb:///./resource_db"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "8ab1b349-0da8-40d4-b64a-c1bee829a12c",
   "metadata": {},
   "outputs": [],
   "source": [
    "%%sql\n",
    "\n",
    "install 'json';\n",
    "load 'json';"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "6b589222-a7b3-44ce-8b61-396f326e18ac",
   "metadata": {},
   "source": [
    "# Create OMOP CDM model\n",
    "\n",
    "## OMOP model overview\n",
    "\n",
    "![image ](https://ohdsi.github.io/CommonDataModel/images/cdm54.png)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "a3e5a6a8-331b-4fce-b2d4-9dd9f0844145",
   "metadata": {},
   "source": [
    "## Standardized vocabularies"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "e00fb0c6-abfd-4f39-851a-4d68f29210ed",
   "metadata": {},
   "outputs": [],
   "source": [
    "%%sql\n",
    "\n",
    "CREATE\n",
    "OR REPLACE TABLE CONCEPT (\n",
    "  CONCEPT_ID LONG,\n",
    "  CONCEPT_NAME STRING,\n",
    "  DOMAIN_ID STRING,\n",
    "  VOCABULARY_ID STRING,\n",
    "  CONCEPT_CLASS_ID STRING,\n",
    "  STANDARD_CONCEPT STRING,\n",
    "  CONCEPT_CODE STRING,\n",
    "  VALID_START_DATE DATE,\n",
    "  VALID_END_DATE DATE,\n",
    "  INVALID_REASON STRING\n",
    ");"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "d4cce454-fe46-45a4-ba57-788f12842322",
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "%sql describe table concept"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "f38e1f04-a7cf-4a61-9061-3b6cfa1a4e97",
   "metadata": {},
   "outputs": [],
   "source": [
    "%%sql\n",
    "CREATE\n",
    "OR REPLACE TABLE VOCABULARY (\n",
    "  VOCABULARY_ID STRING,\n",
    "  VOCABULARY_NAME STRING,\n",
    "  VOCABULARY_REFERENCE STRING,\n",
    "  VOCABULARY_VERSION STRING,\n",
    "  VOCABULARY_CONCEPT_ID LONG\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "0debb101-e7fd-46c3-9efd-db5aa064fe26",
   "metadata": {},
   "outputs": [],
   "source": [
    "%%sql\n",
    "CREATE\n",
    "OR REPLACE TABLE DOMAIN (\n",
    "  DOMAIN_ID STRING,\n",
    "  DOMAIN_NAME STRING,\n",
    "  DOMAIN_CONCEPT_ID LONG\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "e3304cd2-c68a-40f3-8a55-8ccddb1dc3e2",
   "metadata": {},
   "outputs": [],
   "source": [
    "%%sql\n",
    "\n",
    "CREATE\n",
    "OR REPLACE TABLE CONCEPT_CLASS (\n",
    "  CONCEPT_CLASS_ID STRING,\n",
    "  CONCEPT_CLASS_NAME STRING,\n",
    "  CONCEPT_CLASS_CONCEPT_ID LONG\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "afaad43d-8d06-443f-a080-06696deb81dc",
   "metadata": {},
   "outputs": [],
   "source": [
    "%%sql\n",
    "CREATE\n",
    "OR REPLACE TABLE CONCEPT_RELATIONSHIP (\n",
    "  CONCEPT_ID_1 LONG,\n",
    "  CONCEPT_ID_2 LONG,\n",
    "  RELATIONSHIP_ID STRING,\n",
    "  VALID_START_DATE DATE,\n",
    "  VALID_END_DATE DATE,\n",
    "  INVALID_REASON STRING\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "9cd1ca3a-f88e-4d9a-aaff-4984e01bfca4",
   "metadata": {},
   "outputs": [],
   "source": [
    "%%sql\n",
    "CREATE\n",
    "OR REPLACE TABLE RELATIONSHIP (\n",
    "  RELATIONSHIP_ID STRING,\n",
    "  RELATIONSHIP_NAME STRING,\n",
    "  IS_HIERARCHICAL STRING,\n",
    "  DEFINES_ANCESTRY STRING,\n",
    "  REVERSE_RELATIONSHIP_ID STRING,\n",
    "  RELATIONSHIP_CONCEPT_ID LONG\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "0c77674f-70db-4e05-bc5c-f98dca27d9d0",
   "metadata": {},
   "outputs": [],
   "source": [
    "%%sql\n",
    "CREATE\n",
    "OR REPLACE TABLE CONCEPT_SYNONYM (\n",
    "  CONCEPT_ID LONG,\n",
    "  CONCEPT_SYNONYM_NAME STRING,\n",
    "  LANGUAGE_CONCEPT_ID LONG\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "d4bc7f06-6168-4f44-a37b-3c47cce006c3",
   "metadata": {},
   "outputs": [],
   "source": [
    "%%sql\n",
    "CREATE\n",
    "OR REPLACE TABLE CONCEPT_ANCESTOR (\n",
    "  ANCESTOR_CONCEPT_ID LONG,\n",
    "  DESCENDANT_CONCEPT_ID LONG,\n",
    "  MIN_LEVELS_OF_SEPARATION LONG,\n",
    "  MAX_LEVELS_OF_SEPARATION LONG\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "7757be0e-bd75-46f6-a137-e3511cc0d885",
   "metadata": {},
   "outputs": [],
   "source": [
    "%%sql\n",
    "CREATE\n",
    "OR REPLACE TABLE SOURCE_TO_CONCEPT_MAP (\n",
    "  SOURCE_CODE STRING,\n",
    "  SOURCE_CONCEPT_ID LONG,\n",
    "  SOURCE_VOCABULARY_ID STRING,\n",
    "  SOURCE_CODE_DESCRIPTION STRING,\n",
    "  TARGET_CONCEPT_ID LONG,\n",
    "  TARGET_VOCABULARY_ID STRING,\n",
    "  VALID_START_DATE DATE,\n",
    "  VALID_END_DATE DATE,\n",
    "  INVALID_REASON STRING\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "f52715b5-d50b-401c-839b-85bfb07f46e7",
   "metadata": {},
   "outputs": [],
   "source": [
    "%%sql\n",
    "CREATE\n",
    "OR REPLACE TABLE DRUG_STRENGTH (\n",
    "  DRUG_CONCEPT_ID LONG,\n",
    "  INGREDIENT_CONCEPT_ID LONG,\n",
    "  AMOUNT_VALUE DOUBLE,\n",
    "  AMOUNT_UNIT_CONCEPT_ID LONG,\n",
    "  NUMERATOR_VALUE DOUBLE,\n",
    "  NUMERATOR_UNIT_CONCEPT_ID LONG,\n",
    "  DENOMINATOR_VALUE DOUBLE,\n",
    "  DENOMINATOR_UNIT_CONCEPT_ID LONG,\n",
    "  BOX_SIZE LONG,\n",
    "  VALID_START_DATE DATE,\n",
    "  VALID_END_DATE DATE,\n",
    "  INVALID_REASON STRING\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "ad66bc55-6b4a-4c65-b97d-13466eebe535",
   "metadata": {},
   "outputs": [],
   "source": [
    "%%sql\n",
    "CREATE\n",
    "OR REPLACE TABLE ATTRIBUTE_DEFINITION (\n",
    "  ATTRIBUTE_DEFINITION_ID LONG,\n",
    "  ATTRIBUTE_NAME STRING,\n",
    "  ATTRIBUTE_DESCRIPTION STRING,\n",
    "  ATTRIBUTE_TYPE_CONCEPT_ID LONG,\n",
    "  ATTRIBUTE_SYNTAX STRING\n",
    ") "
   ]
  },
  {
   "cell_type": "markdown",
   "id": "7146dd5f-1d69-496d-9936-8dedb0406a15",
   "metadata": {},
   "source": [
    "## Standardized Metadata"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "ba2cd9a6-aa26-4f95-931e-0db0c2a481c9",
   "metadata": {},
   "outputs": [],
   "source": [
    "%%sql\n",
    "CREATE\n",
    "OR REPLACE TABLE CDM_SOURCE (\n",
    "  CDM_SOURCE_NAME STRING,\n",
    "  CDM_SOURCE_ABBREVIATION STRING,\n",
    "  CDM_HOLDER STRING,\n",
    "  SOURCE_DESCRIPTION STRING,\n",
    "  SOURCE_DOCUMENTATION_REFERENCE STRING,\n",
    "  CDM_ETL_REFERENCE STRING,\n",
    "  SOURCE_RELEASE_DATE DATE,\n",
    "  CDM_RELEASE_DATE DATE,\n",
    "  CDM_VERSION STRING,\n",
    "  VOCABULARY_VERSION STRING\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "011baa8e-3b92-4de8-910e-49e16fef7e1a",
   "metadata": {},
   "outputs": [],
   "source": [
    "%%sql\n",
    "CREATE\n",
    "OR REPLACE TABLE METADATA (\n",
    "  METADATA_CONCEPT_ID LONG,\n",
    "  METADATA_TYPE_CONCEPT_ID LONG,\n",
    "  NAME STRING,\n",
    "  VALUE_AS_STRING STRING,\n",
    "  VALUE_AS_CONCEPT_ID LONG,\n",
    "  METADATA_DATE DATE,\n",
    "  METADATA_DATETIME TIMESTAMP\n",
    ")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "619433b5-7014-45e8-b21b-1955a297242c",
   "metadata": {},
   "source": [
    "## Standardized Clinical Data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "84a1578e-f269-4b2a-803e-154737665308",
   "metadata": {},
   "outputs": [],
   "source": [
    "%%sql\n",
    "CREATE\n",
    "OR REPLACE TABLE PERSON (\n",
    "  PERSON_ID LONG,\n",
    "  GENDER_CONCEPT_ID LONG,\n",
    "  YEAR_OF_BIRTH LONG,\n",
    "  MONTH_OF_BIRTH LONG,\n",
    "  DAY_OF_BIRTH LONG,\n",
    "  BIRTH_DATETIME TIMESTAMP,\n",
    "  RACE_CONCEPT_ID LONG,\n",
    "  ETHNICITY_CONCEPT_ID LONG,\n",
    "  LOCATION_ID LONG,\n",
    "  PROVIDER_ID LONG,\n",
    "  CARE_SITE_ID LONG,\n",
    "  PERSON_SOURCE_VALUE STRING,\n",
    "  GENDER_SOURCE_VALUE STRING,\n",
    "  GENDER_SOURCE_CONCEPT_ID LONG,\n",
    "  RACE_SOURCE_VALUE STRING,\n",
    "  RACE_SOURCE_CONCEPT_ID LONG,\n",
    "  ETHNICITY_SOURCE_VALUE STRING,\n",
    "  ETHNICITY_SOURCE_CONCEPT_ID LONG\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "7b9573e9-8d25-4dc8-a684-59e38376b259",
   "metadata": {},
   "outputs": [],
   "source": [
    "%%sql\n",
    "CREATE\n",
    "OR REPLACE TABLE OBSERVATION_PERIOD (\n",
    "  OBSERVATION_PERIOD_ID LONG,\n",
    "  PERSON_ID LONG,\n",
    "  OBSERVATION_PERIOD_START_DATE DATE,\n",
    "  OBSERVATION_PERIOD_END_DATE DATE,\n",
    "  PERIOD_TYPE_CONCEPT_ID LONG\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "18c5fb6d-a530-40e1-9264-bcfa17f48dd0",
   "metadata": {},
   "outputs": [],
   "source": [
    "%%sql\n",
    "CREATE\n",
    "OR REPLACE TABLE VISIT_OCCURRENCE (\n",
    "  VISIT_OCCURRENCE_ID LONG,\n",
    "  PERSON_ID LONG,\n",
    "  VISIT_CONCEPT_ID LONG,\n",
    "  VISIT_START_DATE DATE,\n",
    "  VISIT_START_DATETIME TIMESTAMP,\n",
    "  VISIT_END_DATE DATE,\n",
    "  VISIT_END_DATETIME TIMESTAMP,\n",
    "  VISIT_TYPE_CONCEPT_ID LONG,\n",
    "  PROVIDER_ID LONG,\n",
    "  CARE_SITE_ID LONG,\n",
    "  VISIT_SOURCE_VALUE STRING,\n",
    "  VISIT_SOURCE_CONCEPT_ID LONG,\n",
    "  ADMITTING_SOURCE_CONCEPT_ID LONG,\n",
    "  ADMITTING_SOURCE_VALUE STRING,\n",
    "  DISCHARGE_TO_CONCEPT_ID LONG,\n",
    "  DISCHARGE_TO_SOURCE_VALUE STRING,\n",
    "  PRECEDING_VISIT_OCCURRENCE_ID LONG\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "18a793a1-361d-41fd-ba91-691fee8d14eb",
   "metadata": {},
   "outputs": [],
   "source": [
    "%%sql\n",
    "CREATE\n",
    "OR REPLACE TABLE VISIT_DETAIL (\n",
    "  VISIT_DETAIL_ID LONG,\n",
    "  PERSON_ID LONG,\n",
    "  VISIT_DETAIL_CONCEPT_ID LONG,\n",
    "  VISIT_DETAIL_START_DATE DATE,\n",
    "  VISIT_DETAIL_START_DATETIME TIMESTAMP,\n",
    "  VISIT_DETAIL_END_DATE DATE,\n",
    "  VISIT_DETAIL_END_DATETIME TIMESTAMP,\n",
    "  VISIT_DEATIL_TYPE_CONCEPT_ID LONG,\n",
    "  PROVIDER_ID LONG,\n",
    "  CARE_SITE_ID LONG,\n",
    "  ADMITTING_SOURCE_CONCEPT_ID LONG,\n",
    "  DISCHARGE_TO_CONCEPT_ID LONG,\n",
    "  PRECEDING_VISIT_DETAIL_ID LONG,\n",
    "  VISIT_DETAIL_SOURCE_VALUE STRING,\n",
    "  VISIT_DEATIL_SOURCE_CONCEPT_ID LONG,\n",
    "  ADMITTING_SOURCE_VALUE STRING,\n",
    "  DISCHARGE_TO_SOURCE_VALUE STRING,\n",
    "  VISIT_DETAIL_PARENT_ID LONG,\n",
    "  VISIT_OCCURRENCE_ID LONG\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "4fa35bc6-14ec-48fd-a203-0cd13bff657c",
   "metadata": {},
   "outputs": [],
   "source": [
    "%%sql\n",
    "CREATE\n",
    "OR REPLACE TABLE CONDITION_OCCURRENCE (\n",
    "  CONDITION_OCCURRENCE_ID LONG,\n",
    "  PERSON_ID LONG,\n",
    "  CONDITION_CONCEPT_ID LONG,\n",
    "  CONDITION_START_DATE DATE,\n",
    "  CONDITION_START_DATETIME TIMESTAMP,\n",
    "  CONDITION_END_DATE DATE,\n",
    "  CONDITION_END_DATETIME TIMESTAMP,\n",
    "  CONDITION_TYPE_CONCEPT_ID LONG,\n",
    "  STOP_REASON STRING,\n",
    "  PROVIDER_ID LONG,\n",
    "  VISIT_OCCURRENCE_ID LONG,\n",
    "  VISIT_DETAIL_ID LONG,\n",
    "  CONDITION_SOURCE_VALUE STRING,\n",
    "  CONDITION_SOURCE_CONCEPT_ID LONG,\n",
    "  CONDITION_STATUS_SOURCE_VALUE STRING,\n",
    "  CONDITION_STATUS_CONCEPT_ID LONG\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "b41bccfe-088d-4c41-a80b-3073dd8e54b4",
   "metadata": {},
   "outputs": [],
   "source": [
    "%%sql\n",
    "CREATE\n",
    "OR REPLACE TABLE DRUG_EXPOSURE (\n",
    "  DRUG_EXPOSURE_ID LONG,\n",
    "  PERSON_ID LONG,\n",
    "  DRUG_CONCEPT_ID LONG,\n",
    "  DRUG_EXPOSURE_START_DATE DATE,\n",
    "  DRUG_EXPOSURE_START_DATETIME TIMESTAMP,\n",
    "  DRUG_EXPOSURE_END_DATE DATE,\n",
    "  DRUG_EXPOSURE_END_DATETIME TIMESTAMP,\n",
    "  VERBATIM_END_DATE DATE,\n",
    "  DRUG_TYPE_CONCEPT_ID LONG,\n",
    "  STOP_REASON STRING,\n",
    "  REFILLS LONG,\n",
    "  QUANTITY DOUBLE,\n",
    "  DAYS_SUPPLY LONG,\n",
    "  SIG STRING,\n",
    "  ROUTE_CONCEPT_ID LONG,\n",
    "  LOT_NUMBER STRING,\n",
    "  PROVIDER_ID LONG,\n",
    "  VISIT_OCCURRENCE_ID LONG,\n",
    "  VISIT_DETAIL_ID LONG,\n",
    "  DRUG_SOURCE_VALUE STRING,\n",
    "  DRUG_SOURCE_CONCEPT_ID LONG,\n",
    "  ROUTE_SOURCE_VALUE STRING,\n",
    "  DOSE_UNIT_SOURCE_VALUE STRING\n",
    ") "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "befe77ab-47b6-4862-9be3-d35f2c99263a",
   "metadata": {},
   "outputs": [],
   "source": [
    "%%sql\n",
    "CREATE\n",
    "OR REPLACE TABLE PROCEDURE_OCCURRENCE (\n",
    "  PROCEDURE_OCCURRENCE_ID LONG,\n",
    "  PERSON_ID LONG,\n",
    "  PROCEDURE_CONCEPT_ID LONG,\n",
    "  PROCEDURE_DATE DATE,\n",
    "  PROCEDURE_DATETIME TIMESTAMP,\n",
    "  PROCEDURE_TYPE_CONCEPT_ID LONG,\n",
    "  MODIFIER_CONCEPT_ID LONG,\n",
    "  QUANTITY LONG,\n",
    "  PROVIDER_ID LONG,\n",
    "  VISIT_OCCURRENCE_ID LONG,\n",
    "  VISIT_DETAIL_ID LONG,\n",
    "  PROCEDURE_SOURCE_VALUE STRING,\n",
    "  PROCEDURE_SOURCE_CONCEPT_ID LONG,\n",
    "  MODIFIER_SOURCE_VALUE STRING\n",
    ") "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "2ab2146d-6ae0-4a14-9dac-9818791c1d84",
   "metadata": {},
   "outputs": [],
   "source": [
    "%%sql\n",
    "CREATE\n",
    "OR REPLACE TABLE DEVICE_EXPOSURE (\n",
    "  DEVICE_EXPOSURE_ID LONG,\n",
    "  PERSON_ID LONG,\n",
    "  DEVICE_CONCEPT_ID LONG,\n",
    "  DEVICE_EXPOSURE_START_DATE DATE,\n",
    "  DEVICE_EXPOSURE_START_DATETIME TIMESTAMP,\n",
    "  DEVICE_EXPOSURE_END_DATE DATE,\n",
    "  DEVICE_EXPOSURE_END_DATETIME TIMESTAMP,\n",
    "  DEVICE_TYPE_CONCEPT_ID LONG,\n",
    "  UNIQUE_DEVICE_ID STRING,\n",
    "  QUANTITY LONG,\n",
    "  PROVIDER_ID LONG,\n",
    "  VISIT_OCCURRENCE_ID LONG,\n",
    "  VISIT_DETAIL_ID LONG,\n",
    "  DEVICE_SOURCE_VALUE STRING,\n",
    "  DEVICE_SOURCE_CONCEPT_ID LONG\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "840c4dc8-32f7-453a-9ac8-676026d0645d",
   "metadata": {},
   "outputs": [],
   "source": [
    "%%sql\n",
    "CREATE\n",
    "OR REPLACE TABLE MEASUREMENT (\n",
    "  MEASUREMENT_ID LONG,\n",
    "  PERSON_ID LONG,\n",
    "  MEASUREMENT_CONCEPT_ID LONG,\n",
    "  MEASUREMENT_DATE DATE,\n",
    "  MEASUREMENT_DATETIME TIMESTAMP,\n",
    "  MEASUREMENT_TIME STRING,\n",
    "  MEASUREMENT_TYPE_CONCEPT_ID LONG,\n",
    "  OPERATOR_CONCEPT_ID LONG,\n",
    "  VALUE_AS_NUMBER DOUBLE,\n",
    "  VALUE_AS_CONCEPT_ID LONG,\n",
    "  UNIT_CONCEPT_ID LONG,\n",
    "  RANGE_LOW DOUBLE,\n",
    "  RANGE_HIGH DOUBLE,\n",
    "  PROVIDER_ID LONG,\n",
    "  VISIT_OCCURRENCE_ID LONG,\n",
    "  VISIT_DETAIL_ID LONG,\n",
    "  MEASUREMENT_SOURCE_VALUE STRING,\n",
    "  MEASUREMENT_SOURCE_CONCEPT_ID LONG,\n",
    "  UNIT_SOURCE_VALUE STRING,\n",
    "  VALUE_SOURCE_VALUE STRING\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "a144d16c-91c1-4316-9ad2-0ec811a819bf",
   "metadata": {},
   "outputs": [],
   "source": [
    "%%sql\n",
    "CREATE\n",
    "OR REPLACE TABLE NOTE (\n",
    "  NOTE_ID LONG,\n",
    "  PERSON_ID LONG,\n",
    "  NOTE_DATE DATE,\n",
    "  NOTE_DATETIME TIMESTAMP,\n",
    "  NOTE_TYPE_CONCEPT_ID LONG,\n",
    "  NOTE_CLASS_CONCEPT_ID LONG,\n",
    "  NOTE_TITLE STRING,\n",
    "  NOTE_TEXT STRING,\n",
    "  ENCODING_CONCEPT_ID LONG,\n",
    "  LANGUAGE_CONCEPT_ID LONG,\n",
    "  PROVIDER_ID LONG,\n",
    "  VISIT_OCCURRENCE_ID LONG,\n",
    "  VISIT_DETAIL_ID LONG,\n",
    "  NOTE_SOURCE_VALUE STRING\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "251254e9-796d-4bf7-a668-a6d3045d3dd9",
   "metadata": {},
   "outputs": [],
   "source": [
    "%%sql\n",
    "/*The field OFFSET in CDM is renamed to NOTE_OFFSET due to errors*/\n",
    "CREATE\n",
    "OR REPLACE TABLE NOTE_NLP (\n",
    "  NOTE_NLP_ID LONG,\n",
    "  NOTE_ID LONG,\n",
    "  SECTION_CONCEPT_ID LONG,\n",
    "  SNIPPET STRING,\n",
    "  NOTE_OFFSET STRING,\n",
    "  LEXICAL_VARIANT STRING,\n",
    "  NOTE_NLP_CONCEPT_ID LONG,\n",
    "  NOTE_NLP_SOURCE_CONCEPT_ID LONG,\n",
    "  NLP_SYSTEM STRING,\n",
    "  NLP_DATE DATE,\n",
    "  NLP_DATETIME TIMESTAMP,\n",
    "  TERM_EXISTS STRING,\n",
    "  TERM_TEMPORAL STRING,\n",
    "  TERM_MODIFIERS STRING\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "351dbc7b-c987-4cb7-9398-d70bd1fe2c0e",
   "metadata": {},
   "outputs": [],
   "source": [
    "%%sql\n",
    "CREATE\n",
    "OR REPLACE TABLE OBSERVATION (\n",
    "  OBSERVATION_ID LONG,\n",
    "  PERSON_ID LONG,\n",
    "  OBSERVATION_CONCEPT_ID LONG,\n",
    "  OBSERVATION_DATE DATE,\n",
    "  OBSERVATION_DATETIME TIMESTAMP,\n",
    "  OBSERVATION_TYPE_CONCEPT_ID LONG,\n",
    "  VALUE_AS_NUMBER DOUBLE,\n",
    "  VALUE_AS_STRING STRING,\n",
    "  VALUE_AS_CONCEPT_ID LONG,\n",
    "  QUALIFIER_CONCEPT_ID LONG,\n",
    "  UNIT_CONCEPT_ID LONG,\n",
    "  PROVIDER_ID LONG,\n",
    "  VISIT_OCCURRENCE_ID LONG,\n",
    "  VISIT_DETAIL_ID LONG,\n",
    "  OBSERVATION_SOURCE_VALUE STRING,\n",
    "  OBSERVATION_SOURCE_CONCEPT_ID LONG,\n",
    "  UNIT_SOURCE_VALUE STRING,\n",
    "  QUALIFIER_SOURCE_VALUE STRING\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "bb219171-ecf0-4ec8-880c-1b6c408d7363",
   "metadata": {},
   "outputs": [],
   "source": [
    "%%sql\n",
    "CREATE\n",
    "OR REPLACE TABLE SPECIMEN (\n",
    "  SPECIMEN_ID LONG,\n",
    "  PERSON_ID LONG,\n",
    "  SPECIMEN_CONCEPT_ID LONG,\n",
    "  SPECIMEN_TYPE_CONCEPT_ID LONG,\n",
    "  SPECIMEN_DATE DATE,\n",
    "  SPECIMEN_DATETIME TIMESTAMP,\n",
    "  QUANTITY DOUBLE,\n",
    "  UNIT_CONCEPT_ID LONG,\n",
    "  ANATOMIC_SITE_CONCEPT_ID LONG,\n",
    "  DISEASE_STATUS_CONCEPT_ID LONG,\n",
    "  SPECIMEN_SOURCE_ID STRING,\n",
    "  SPECIMEN_SOURCE_VALUE STRING,\n",
    "  UNIT_SOURCE_VALUE STRING,\n",
    "  ANATOMIC_SITE_SOURCE_VALUE STRING,\n",
    "  DISEASE_STATUS_SOURCE_VALUE STRING\n",
    ") "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "e58852f6-1de6-42e3-b4e4-fe921627711b",
   "metadata": {},
   "outputs": [],
   "source": [
    "%%sql\n",
    "CREATE\n",
    "OR REPLACE TABLE FACT_RELATIONSHIP (\n",
    "  DOMAIN_CONCEPT_ID_1 LONG,\n",
    "  FACT_ID_1 LONG,\n",
    "  DOMAIN_CONCEPT_ID_2 LONG,\n",
    "  FACT_ID_2 LONG,\n",
    "  RELATIONSHIP_CONCEPT_ID LONG\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "f7c18cbe-2a76-40a8-b954-b156c07ad4c1",
   "metadata": {},
   "outputs": [],
   "source": [
    "%%sql\n",
    "CREATE\n",
    "OR REPLACE TABLE DEATH (\n",
    "  PERSON_ID LONG,\n",
    "  DEATH_DATE DATE,\n",
    "  DEATH_DATETIME TIMESTAMP,\n",
    "  DEATH_TYPE_CONCEPT_ID LONG,\n",
    "  CAUSE_CONCEPT_ID LONG,\n",
    "  CAUSE_SOURCE_VALUE STRING,\n",
    "  CAUSE_SOURCE_CONCEPT_ID LONG\n",
    ")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "dd990fc7-358b-4488-a30a-8236aafee095",
   "metadata": {},
   "source": [
    "## Standardized health system data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "07e08ff8-8a72-4cad-afad-2b82b907358f",
   "metadata": {},
   "outputs": [],
   "source": [
    "%%sql\n",
    "CREATE\n",
    "OR REPLACE TABLE LOCATION (\n",
    "  LOCATION_ID LONG,\n",
    "  ADDRESS_1 STRING,\n",
    "  ADDRESS_2 STRING,\n",
    "  CITY STRING,\n",
    "  STATE STRING,\n",
    "  ZIP STRING,\n",
    "  COUNTY STRING,\n",
    "  LOCATION_SOURCE_VALUE STRING\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "a8c18451-a9f2-45a3-b4a4-05a9feb09308",
   "metadata": {},
   "outputs": [],
   "source": [
    "%%sql\n",
    "CREATE\n",
    "OR REPLACE TABLE CARE_SITE (\n",
    "  CARE_SITE_ID LONG,\n",
    "  CARE_SITE_NAME STRING,\n",
    "  PLACE_OF_SERVICE_CONCEPT_ID LONG,\n",
    "  LOCATION_ID LONG,\n",
    "  CARE_SITE_SOURCE_VALUE STRING,\n",
    "  PLACE_OF_SERVICE_SOURCE_VALUE STRING\n",
    ")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "ef2e2ebe-e484-48e8-987a-b0bb23a0463d",
   "metadata": {},
   "source": [
    "## Standardized Health Economics"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "81fc1dd0-2a8f-4299-971e-37214b77980c",
   "metadata": {},
   "outputs": [],
   "source": [
    "%%sql\n",
    "CREATE\n",
    "OR REPLACE TABLE PROVIDER (\n",
    "  PROVIDER_ID LONG,\n",
    "  PROVIDER_NAME STRING,\n",
    "  NPI STRING,\n",
    "  DEA STRING,\n",
    "  SPECIALTY_CONCEPT_ID LONG,\n",
    "  CARE_SITE_ID LONG,\n",
    "  YEAR_OF_BIRTH LONG,\n",
    "  GENDER_CONCEPT_ID LONG,\n",
    "  PROVIDER_SOURCE_VALUE STRING,\n",
    "  SPECIALTY_SOURCE_VALUE STRING,\n",
    "  SPECIALTY_SOURCE_CONCEPT_ID LONG,\n",
    "  GENDER_SOURCE_VALUE STRING,\n",
    "  GENDER_SOURCE_CONCEPT_ID LONG\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "d5b6f7e1-8735-4181-a1a2-78fd55963cc2",
   "metadata": {},
   "outputs": [],
   "source": [
    "%%sql\n",
    "CREATE\n",
    "OR REPLACE TABLE PAYER_PLAN_PERIOD (\n",
    "  PAYER_PLAN_PERIOD_ID LONG,\n",
    "  PERSON_ID LONG,\n",
    "  PAYER_PLAN_PERIOD_START_DATE DATE,\n",
    "  PAYER_PLAN_PERIOD_END_DATE DATE,\n",
    "  PAYER_CONCEPT_ID LONG,\n",
    "  PAYER_SOURCE_VALUE STRING,\n",
    "  PAYER_SOURCE_CONCEPT_ID LONG,\n",
    "  PLAN_CONCEPT_ID LONG,\n",
    "  PLAN_SOURCE_VALUE STRING,\n",
    "  PLAN_SOURCE_CONCEPT_ID LONG,\n",
    "  SPONSOR_CONCEPT_ID LONG,\n",
    "  SPONSOR_SOURCE_VALUE STRING,\n",
    "  SPONSOR_SOURCE_CONCEPT_ID LONG,\n",
    "  FAMILY_SOURCE_VALUE STRING,\n",
    "  STOP_REASON_CONCEPT_ID LONG,\n",
    "  STOP_REASON_SOURCE_VALUE STRING,\n",
    "  STOP_REASON_SOURCE_CONCEPT_ID LONG\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "1921f3da-585b-41e8-8612-041ccc6ce590",
   "metadata": {},
   "outputs": [],
   "source": [
    "%%sql\n",
    "CREATE\n",
    "OR REPLACE TABLE COST (\n",
    "  COST_ID LONG,\n",
    "  COST_EVENT_ID LONG,\n",
    "  COST_DOMAIN_ID STRING,\n",
    "  COST_TYPE_CONCEPT_ID LONG,\n",
    "  CURRENCY_CONCEPT_ID LONG,\n",
    "  TOTAL_CHARGE DOUBLE,\n",
    "  TOTAL_COST DOUBLE,\n",
    "  TOTAL_PAID DOUBLE,\n",
    "  PAID_BY_PAYER DOUBLE,\n",
    "  PAID_BY_PATIENT DOUBLE,\n",
    "  PAID_PATIENT_COPAY DOUBLE,\n",
    "  PAID_PATIENT_COINSURANCE DOUBLE,\n",
    "  PAID_PATIENT_DEDUCTIBLE DOUBLE,\n",
    "  PAID_BY_PRIMARY DOUBLE,\n",
    "  PAID_INGREDIENT_COST DOUBLE,\n",
    "  PAID_DISPENSING_FEE DOUBLE,\n",
    "  PAYER_PLAN_PERIOD_ID LONG,\n",
    "  AMOUNT_ALLOWED DOUBLE,\n",
    "  REVENUE_CODE_CONCEPT_ID LONG,\n",
    "  REVENUE_CODE_SOURCE_VALUE STRING,\n",
    "  DRG_CONCEPT_ID LONG,\n",
    "  DRG_SOURCE_VALUE STRING\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "562dfd85-2b9a-49f7-9e3c-9c45e856f770",
   "metadata": {},
   "outputs": [],
   "source": [
    "%%sql\n",
    "CREATE\n",
    "OR REPLACE TABLE CONDITION_ERA (\n",
    "  CONDITION_ERA_ID LONG,\n",
    "  PERSON_ID LONG,\n",
    "  CONDITION_CONCEPT_ID LONG,\n",
    "  CONDITION_ERA_START_DATE DATE,\n",
    "  CONDITION_ERA_END_DATE DATE,\n",
    "  CONDITION_OCCURRENCE_COUNT LONG\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "e9450b96-c192-41ab-9c5e-fa500f71f7ad",
   "metadata": {},
   "outputs": [],
   "source": [
    "%%sql\n",
    "CREATE\n",
    "OR REPLACE TABLE DRUG_ERA (\n",
    "  DRUG_ERA_ID LONG,\n",
    "  PERSON_ID LONG,\n",
    "  DRUG_CONCEPT_ID LONG,\n",
    "  DRUG_ERA_START_DATE DATE,\n",
    "  DRUG_ERA_END_DATE DATE,\n",
    "  DRUG_EXPOSURE_COUNT LONG,\n",
    "  GAP_DAYS LONG\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "13eb9d19-afd3-4837-8908-81511db0e431",
   "metadata": {},
   "outputs": [],
   "source": [
    "%%sql\n",
    "CREATE\n",
    "OR REPLACE TABLE DOSE_ERA (\n",
    "  DOSE_ERA_ID LONG,\n",
    "  PERSON_ID LONG,\n",
    "  DRUG_CONCEPT_ID LONG,\n",
    "  UNIT_CONCEPT_ID LONG,\n",
    "  DOSE_VALUE DOUBLE,\n",
    "  DOSE_ERA_START_DATE DATE,\n",
    "  DOSE_ERA_END_DATE DATE\n",
    ")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "fb6ea80f-81fe-4d04-a834-74dee0a5bd88",
   "metadata": {},
   "source": [
    "## Results Schema"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "2202d145-4ca0-4a72-a905-43d36f45ab5b",
   "metadata": {},
   "outputs": [],
   "source": [
    "%%sql\n",
    "CREATE\n",
    "OR REPLACE TABLE cohort (\n",
    "  cohort_definition_id LONG,\n",
    "  subject_id LONG,\n",
    "  cohort_start_date DATE,\n",
    "  cohort_end_date DATE\n",
    ");\n",
    "\n",
    "CREATE\n",
    "OR REPLACE TABLE cohort_definition (\n",
    "  cohort_definition_id LONG,\n",
    "  cohort_definition_name STRING,\n",
    "  cohort_definition_description STRING,\n",
    "  definition_type_concept_id LONG,\n",
    "  cohort_definition_syntax STRING,\n",
    "  subject_concept_id LONG,\n",
    "  cohort_initiation_date DATE\n",
    ");\n",
    "\n",
    "CREATE\n",
    "OR REPLACE TABLE COHORT_ATTRIBUTE (\n",
    "  COHORT_DEFINITION_ID LONG,\n",
    "  SUBJECT_ID LONG,\n",
    "  COHORT_START_DATE DATE,\n",
    "  COHORT_END_DATE DATE,\n",
    "  ATTRIBUTE_DEFINITION_ID LONG,\n",
    "  VALUE_AS_NUMBER DOUBLE,\n",
    "  VALUE_AS_CONCEPT_ID LONG\n",
    ");"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "2dbf5403-17c5-48c7-b78d-5a9b5b160c75",
   "metadata": {},
   "outputs": [],
   "source": [
    "%%sql\n",
    "describe COHORT_ATTRIBUTE;"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "f2194175-b406-42a7-b552-991b06ebfedd",
   "metadata": {},
   "outputs": [],
   "source": [
    "%%sql\n",
    "SHOW tables;"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "aa6b92a0-6774-4b43-b9b3-3fa9161a0f27",
   "metadata": {},
   "source": [
    "## Load synthea data from CSV"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "8dcb79dc-b89a-4ac3-a71d-2ba6ec22379d",
   "metadata": {},
   "source": [
    "For the Clinical data, this example uses the CSV data downloaded form [Synthea website](https://synthetichealth.github.io/synthea/).\n",
    "\n",
    "One of the cool features of DuckDB is that it directly allows import of data from different file formats (CSV, Parquet etc.). Also to use this data one doesnt have to technically IMPORT the data you can use DuckDB to directly query a file as if it was a table. Here we are creating tables so that they can be used downstream for data transforms."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "504a486a-d06f-4137-87cc-065bab553666",
   "metadata": {},
   "outputs": [],
   "source": [
    "%%sql\n",
    "CREATE OR REPLACE TABLE patients AS SELECT * FROM './synthea_sample_data_csv_apr2020/csv/patients.csv';\n",
    "CREATE OR REPLACE TABLE allergies AS SELECT * FROM './synthea_sample_data_csv_apr2020/csv/allergies.csv';\n",
    "CREATE OR REPLACE TABLE careplans AS SELECT * FROM './synthea_sample_data_csv_apr2020/csv/careplans.csv';\n",
    "CREATE OR REPLACE TABLE conditions AS SELECT * FROM './synthea_sample_data_csv_apr2020/csv/conditions.csv';\n",
    "CREATE OR REPLACE TABLE devices AS SELECT * FROM './synthea_sample_data_csv_apr2020/csv/devices.csv';\n",
    "CREATE OR REPLACE TABLE encounters AS SELECT * FROM './synthea_sample_data_csv_apr2020/csv/encounters.csv';\n",
    "CREATE OR REPLACE TABLE imaging_studies AS SELECT * FROM './synthea_sample_data_csv_apr2020/csv/imaging_studies.csv';\n",
    "CREATE OR REPLACE TABLE immunizations AS SELECT * FROM './synthea_sample_data_csv_apr2020/csv/immunizations.csv';\n",
    "CREATE OR REPLACE TABLE medications (START TIMESTAMP, STOP TIMESTAMP, PATIENT VARCHAR, PAYER VARCHAR, ENCOUNTER VARCHAR, CODE INTEGER, DESCRIPTION VARCHAR, BASE_COST DOUBLE, PAYER_COVERAGE DOUBLE, DISPENSES INTEGER,TOTALCOST DOUBLE,REASONCODE LONG, REASONDESCRIPTION VARCHAR);\n",
    "COPY medications FROM './synthea_sample_data_csv_apr2020/csv/medications.csv' (AUTO_DETECT TRUE);\n",
    "CREATE OR REPLACE TABLE observations AS SELECT * FROM './synthea_sample_data_csv_apr2020/csv/observations.csv';\n",
    "CREATE OR REPLACE TABLE organizations AS SELECT * FROM './synthea_sample_data_csv_apr2020/csv/organizations.csv';\n",
    "CREATE OR REPLACE TABLE payer_transitions AS SELECT * FROM './synthea_sample_data_csv_apr2020/csv/payer_transitions.csv';\n",
    "CREATE OR REPLACE TABLE payers AS SELECT * FROM './synthea_sample_data_csv_apr2020/csv/payers.csv';\n",
    "CREATE OR REPLACE TABLE procedures (DATE TIMESTAMP,PATIENT VARCHAR,ENCOUNTER VARCHAR,CODE LONG,DESCRIPTION VARCHAR,BASE_COST DOUBLE,REASONCODE LONG, REASONDESCRIPTION VARCHAR);\n",
    "COPY procedures FROM './synthea_sample_data_csv_apr2020/csv/procedures.csv' (AUTO_DETECT TRUE);\n",
    "CREATE OR REPLACE TABLE providers AS SELECT * FROM './synthea_sample_data_csv_apr2020/csv/providers.csv';\n",
    "CREATE OR REPLACE TABLE supplies AS SELECT * FROM './synthea_sample_data_csv_apr2020/csv/supplies.csv';"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "48df8215-8dcc-4456-830a-35d1ba068e3a",
   "metadata": {},
   "outputs": [],
   "source": [
    "%%sql\n",
    "select * from medications \n",
    "where reasoncode = 44054006\n",
    "using sample 100;"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "3009073d-b20d-4504-ad21-60cca963fa2a",
   "metadata": {},
   "outputs": [],
   "source": [
    "%%sql\n",
    "COPY concept from './vocabulary_download_v5/concept.csv' (AUTO_DETECT TRUE, DATEFORMAT '%Y%m%d')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "72e6086b-5e0f-4fd7-bf6d-21e1b5de5124",
   "metadata": {},
   "outputs": [],
   "source": [
    "%%sql\n",
    "select count(*) from concept;"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "cb0c9e93-1b36-4eb5-a4d1-95d99397561b",
   "metadata": {},
   "outputs": [],
   "source": [
    "%%sql\n",
    "COPY domain from './vocabulary_download_v5/domain.csv' (AUTO_DETECT TRUE, DATEFORMAT '%Y%m%d')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "c65c01e1-4d34-4163-8493-66e32bf24df5",
   "metadata": {},
   "outputs": [],
   "source": [
    "%%sql\n",
    "select * from domain;"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "b27e47e6-5357-4849-bf93-239894f06a0b",
   "metadata": {},
   "outputs": [],
   "source": [
    "%%sql\n",
    "COPY relationship FROM './vocabulary_download_v5/relationship.csv' (AUTO_DETECT TRUE, DATEFORMAT '%Y%m%d')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "d977c1c6-5b40-4ec1-b4cc-5780da2fed4d",
   "metadata": {},
   "outputs": [],
   "source": [
    "%%sql\n",
    "COPY drug_strength FROM './vocabulary_download_v5/drug_strength.csv' (AUTO_DETECT TRUE, DATEFORMAT '%Y%m%d')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "3f855452-3388-43c2-90fb-98a77bcc580b",
   "metadata": {},
   "outputs": [],
   "source": [
    "%%sql\n",
    "COPY vocabulary FROM './vocabulary_download_v5/vocabulary.csv' (AUTO_DETECT TRUE, DATEFORMAT '%Y%m%d')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "3dad492d-50bf-4c6d-917a-20960f4cf296",
   "metadata": {},
   "outputs": [],
   "source": [
    "%%sql\n",
    "COPY concept_synonym FROM './vocabulary_download_v5/concept_synonym - Copy.csv' (SAMPLE_SIZE -1, AUTO_DETECT TRUE, DATEFORMAT '%Y%m%d')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "3165e6e7-1d9b-44df-9112-1db3bef22b5c",
   "metadata": {},
   "outputs": [],
   "source": [
    "%%sql\n",
    "delete from concept_synonym;"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "51f093d7-53b9-4f8f-9665-2d00ef61148d",
   "metadata": {},
   "outputs": [],
   "source": [
    "%%sql\n",
    "COPY concept_relationship FROM './vocabulary_download_v5/concept_relationship.csv' (AUTO_DETECT TRUE, DATEFORMAT '%Y%m%d')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "07769b5c-d7bf-46b8-8999-4fe858ce3268",
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "%%sql\n",
    "select count(*) from concept_relationship;"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "503b0b9b-fc9e-498a-b85b-a11d88ca770a",
   "metadata": {},
   "outputs": [],
   "source": [
    "%%sql\n",
    "select count(*) from './vocabulary_download_v5/concept_relationship.csv'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "56e6bc76-f53f-46e3-a1cd-e798b709e53c",
   "metadata": {},
   "outputs": [],
   "source": [
    "%%sql\n",
    "select count(*) from patients;"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "91d4336b-069c-4288-b733-17c8c1f0b1a3",
   "metadata": {},
   "source": [
    "## TODO - create some analytical views !!"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "9e452edf-9c97-45cc-a46d-289a915d10a6",
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.9.12"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}