search

OHDSI / ETL-CDMBuilder

ETL-CDMBuilder is a repo containing a .NET Core application to perform ETL to OMOP CDM for multiple databases
Apache License 2.0
50 stars 32 forks source link

Incompatible Data types error on CDM mapping #59

Closed angujo closed 2 years ago

angujo commented 2 years ago

Hello, Thanks for such a wonderfull solution. I am encountering below error during lookup creation stage, precisly when providers are being created;

  at org.ohdsi.cdm.presentation.builder.Controllers.BuilderController.PerformAction(Action act) in D:\VSProjects\ETL-CDMBuilder\source\org.ohdsi.cdm.presentation.builder\Controllers\BuilderController.cs:line 55
   at org.ohdsi.cdm.presentation.builder.Controllers.BuilderController.<>c__DisplayClass16_0.<CreateLookup>b__0() in D:\VSProjects\ETL-CDMBuilder\source\org.ohdsi.cdm.presentation.builder\Controllers\BuilderController.cs:line 176
   at org.ohdsi.cdm.framework.desktop.Savers.Saver.SaveEntityLookup(CdmVersions cdmVersions, List`1 location, List`1 careSite, List`1 provider, List`1 cohortDefinition) in D:\VSProjects\ETL-CDMBuilder\source\org.ohdsi.cdm.framework.desktop\Savers\Saver.cs:line 553
Stacktrace:    at org.ohdsi.cdm.framework.desktop.Savers.PostgreSaver.Write(Nullable`1 chunkId, Nullable`1 subChunkId, IDataReader reader, String tableName) in D:\VSProjects\ETL-CDMBuilder\source\org.ohdsi.cdm.framework.desktop\Savers\PostgreSaver.cs:line 95
Source: org.ohdsi.cdm.framework.desktop
22P03: incorrect binary data format
Message: COPY public.PROVIDER (provider_id,provider_name,npi,dea,specialty_concept_id,care_site_id,year_of_birth,gender_concept_id,provider_source_value,specialty_source_value,specialty_source_concept_id,gender_source_value,gender_source_concept_id) from STDIN (FORMAT BINARY)
Type: System.Exception

The DB (postgresql) log points to the issue on the column _care_siteid

2021-12-10 15:41:44.814 GMT [20592] LOG:  statement: COPY public.PROVIDER (provider_id,provider_name,npi,dea,specialty_concept_id,care_site_id,year_of_birth,gender_concept_id,provider_source_value,specialty_source_value,specialty_source_concept_id,gender_source_value,gender_source_concept_id) from STDIN (FORMAT BINARY)
2021-12-10 15:41:44.823 GMT [20592] ERROR:  incorrect binary data format
2021-12-10 15:41:44.823 GMT [20592] CONTEXT:  COPY provider, line 1, column care_site_id
2021-12-10 15:41:44.823 GMT [20592] STATEMENT:  COPY public.PROVIDER (provider_id,provider_name,npi,dea,specialty_concept_id,care_site_id,year_of_birth,gender_concept_id,provider_source_value,specialty_source_value,specialty_source_concept_id,gender_source_value,gender_source_concept_id) from STDIN (FORMAT BINARY)

Have tried to match the column types for the provider table with the populating query at org.ohdsi.cdm.framework.etl/org.ohdsi.cdm.framework.etl.cprd/ETL/CPRD/Definitions/L_PROVIDER.xml. Have changed the cast data type, for the column, to integer to no resolution.

Any assistance will be appreciated.

bradanton commented 2 years ago

Hello @angujo,

Try to change column type in provider table, like below: ALTER TABLE provider ALTER COLUMN care_site_id TYPE bigint;

Thanks, Anton

angujo commented 2 years ago

Thanks @bradanton, This has helped and the issue is resolved.