Вовед

Во рамки на првата фаза, фокусот е ставен на:

• генерирање на податоци
• креирање на база и табели
• пополнување на табелите со реалистични податоци.

Синтетички податоци

Поради чувствителноста на здравствените податоци и регулативите за приватност, во проектот не се користат реални пациентски податоци.

Наместо тоа, се користат синтетички податоци кои

• ја задржуваат статистичката структура на реални податоци
• не содржат лични или доверливи информации
• се погодни за тестирање и анализа

Synthea алатка

​Synthea е open-source алатка за генерирање на синтетички здравствени податоци.

Алатката генерира податоци за пациенти, прегледи, дијагнози, терапии и интервенции организирани во CSV формат.

Податоците се структурирани така што можат директно да се внесат во релациона база на податоци.

Генерирање на податоци

Податоците се генерирани со користење на Synthea, при што е симулирана популација од 250 000 пациенти.

./run_synthea.bat -p 250000

Креирање на табели

По генерирањето на CSV датотеките, следен чекор е подготовка на релационата база на податоци.

Ова опфаќа:

• креирање на база на податоци
• дефинирање на табели
• избор на соодветни типови на податоци
• подготовка за ефикасен увоз на податоци

Креирање на база на податоци

create database HospitalSyntheaDB
on primary
(name = 'HospitalSyntheaDB', filename = 'C:\Path\To\Data\HospitalSyntheaDB.mdf', size = 1024MB, filegrowth = 256MB)
log on
(name = 'HospitalSyntheaDB_log', filename = 'C:\Path\To\Data\HospitalSyntheaDB_log.ldf', size = 512MB, filegrowth = 128MB);
go

Patients табела

create table patients (
    id uniqueidentifier primary key,
    birthdate date not null,
    deathdate date null,
    ssn varchar(20) null,
    drivers varchar(50) null,
    passport varchar(50) null,
    prefix varchar(20) null,
    first varchar(100) not null,
    middle varchar(100) null,
    last varchar(100) not null,
    suffix varchar(20) null,
    maiden varchar(100) null,
    marital varchar(20) null,
    race varchar(50) not null,
    ethnicity varchar(50) not null,
    gender char(1) not null,
    birthplace varchar(200) null,
    address varchar(200) not null,
    city varchar(100) not null,
    state varchar(50) not null,
    county varchar(100) null,
    fips varchar(10) null,
    zip varchar(20) null,
    lat decimal(9,6) null,
    lon decimal(9,6) null,
    healthcare_expenses decimal(18,2) null,
    healthcare_coverage decimal(18,2) null,
    income decimal(18,2) null
);
go

Encounters табела

create table encounters (
    id uniqueidentifier primary key,
    start datetime2 not null,
    stop datetime2 null,
    patient uniqueidentifier not null,
    organization uniqueidentifier not null,
    provider uniqueidentifier not null,
    payer uniqueidentifier not null,
    encounterclass varchar(50) not null,
    code varchar(50) not null,
    description varchar(500) not null,
    base_encounter_cost decimal(18,2) not null,
    total_claim_cost decimal(18,2) not null,
    payer_coverage decimal(18,2) not null,
    reasoncode varchar(50) null,
    reasondescription varchar(500) null
);
go

Conditions табела

create table conditions (
    start date not null,
    stop date null,
    patient uniqueidentifier not null,
    encounter uniqueidentifier not null,
    system varchar(100) not null,
    code varchar(50) not null,
    description varchar(500) not null
);
go

Observations

create table observations (
    date datetime2 not null,
    patient uniqueidentifier not null,
    encounter uniqueidentifier null,
    category varchar(100) null,
    code varchar(50) not null,
    description varchar(500) not null,
    value varchar(max) not null,
    units varchar(50) null,
    type varchar(50) not null
);
go

Medications

create table medications (
    start datetime2 not null,
    stop datetime2 null,
    patient uniqueidentifier not null,
    payer uniqueidentifier not null,
    encounter uniqueidentifier not null,
    code varchar(50) not null,
    description varchar(500) not null,
    base_cost decimal(18,2) not null,
    payer_coverage decimal(18,2) not null,
    dispenses int not null,
    totalcost decimal(18,2) not null,
    reasoncode varchar(50) null,
    reasondescription varchar(500) null
);
go

Allergies

create table allergies (
    start date null,
    stop date null,
    patient uniqueidentifier not null,
    encounter uniqueidentifier null,
    code varchar(50) not null,
    system varchar(100) not null,
    description varchar(500) not null,
    type varchar(50) null,
    category varchar(50) null,
    reaction1 varchar(100) null,
    description1 varchar(500) null,
    severity1 varchar(50) null,
    reaction2 varchar(100) null,
    description2 varchar(500) null,
    severity2 varchar(50) null
);
go

Procedures

create table procedures (
    start datetime2 not null,
    stop datetime2 null,
    patient uniqueidentifier not null,
    encounter uniqueidentifier not null,
    system varchar(100) null,
    code varchar(50) not null,
    description varchar(500) not null,
    base_cost decimal(18,2) null,
    reasoncode varchar(50) null,
    reasondescription varchar(500) null
);
go

Immunizations

create table immunizations (
    date datetime2 not null,
    patient uniqueidentifier not null,
    encounter uniqueidentifier null,
    code varchar(50) not null,
    description varchar(500) not null,
    base_cost decimal(18,2) not null
);
go

Пополнување на табели

Податоците ги увезуваме од CSV датотеки преку staging табели, со цел:

• да се овозможи контрола и чистење на податоците
• да се избегнат грешки при директен внес на финалните табели
• да се овозможи безбедно кастирање на типови (dates, decimals, uniqueidentifier)

Staging табели

За секоја финална табела е креирана соодветна staging табела. Во овие табели сите колони се дефинирани како VARCHAR, со што:

• се избегнуваат грешки при BULK INSERT
• се овозможува чистење на податоци (отстранување на наводници)
• се користи TRY_CAST за безбедна конверзија кон финалните типови.

Staging табели:

• patients_staging
• encounters_staging
• conditions_staging
• observations_staging
• medications_staging
• allergies_staging
• procedures_staging
• immunizations_staging

Процес на увоз на податоци

Процесот на пополнување за секоја табела се состои од следните чекори:

1. Празнење на staging табелата (truncate).
2. BULK INSERT од CSV датотека
3. Проверка на број на редови во staging табелата
4. INSERT во финалната табела со CAST / TRY_CAST
5. Проверка на број на редови во финалната табела.

Пример: Пополнување на табелата Patients

Податоците за пациенти се увезуваат од датотеката patients.csv. При увозот:

• ID полињата се конвертираат во UNIQUEIDENTIFIER
• датумите се конвертираат во DATE
• нумеричките вредности се конвертираат во DECIMAL
• се користи TRY_CAST за справување со недостасувачки вредности

use HospitalSyntheaDB;
go

if object_id('dbo.patients_staging') is not null drop table dbo.patients_staging;
go

create table patients_staging(
    id varchar(100), birthdate varchar(50), deathdate varchar(50), ssn varchar(50), drivers varchar(50),
    passport varchar(50), prefix varchar(50), first varchar(100), middle varchar(100), last varchar(100),
    suffix varchar(50), maiden varchar(100), marital varchar(50), race varchar(50), ethnicity varchar(50),
    gender varchar(10), birthplace varchar(200), address varchar(200), city varchar(100), state varchar(50),
    county varchar(100), fips varchar(20), zip varchar(20), lat varchar(20), lon varchar(20),
    healthcare_expenses varchar(50), healthcare_coverage varchar(50), income varchar(50)
);
go

truncate patients_staging;
bulk insert patients_staging
from 'C:\Users\user\Documents\DBA\synthea\output\csv\patients.csv'
with (fieldterminator = ',', rowterminator = '\n', firstrow = 2, codepage = '65001', tablock);
go

print 'Patients staging row count:';
select count(*) as patients_staging_count from patients_staging;
go

insert into patients
select
    cast(replace(id,'"','') as uniqueidentifier),
    cast(replace(birthdate,'"','') as date),
    try_cast(replace(deathdate,'"','') as date),
    replace(ssn,'"',''),
    replace(drivers,'"',''),
    replace(passport,'"',''),
    replace(prefix,'"',''),
    replace(first,'"',''),
    replace(middle,'"',''),
    replace(last,'"',''),
    replace(suffix,'"',''),
    replace(maiden,'"',''),
    replace(marital,'"',''),
    replace(race,'"',''),
    replace(ethnicity,'"',''),
    upper(replace(gender,'"','')),
    replace(birthplace,'"',''),
    replace(address,'"',''),
    replace(city,'"',''),
    replace(state,'"',''),
    replace(county,'"',''),
    replace(fips,'"',''),
    replace(zip,'"',''),
    try_cast(replace(lat,'"','') as decimal(9,6)),
    try_cast(replace(lon,'"','') as decimal(9,6)),
    try_cast(replace(healthcare_expenses,'"','') as decimal(18,2)),
    try_cast(replace(healthcare_coverage,'"','') as decimal(18,2)),
    try_cast(replace(income,'"','') as decimal(18,2))
from patients_staging;
go

print 'Patients final row count:';
select count(*) as patients_count from patients;
go

Чистење по увоз

По успешниот увоз на податоци, staging табелите е бришат.

Ова спречува непотребно користење простор и ја задржува базата чиста за понатамошни фази од проектот.

drop table patients_staging;
go