search

OHDSI / CommonDataModel

Definition and DDLs for the OMOP Common Data Model (CDM)
https://ohdsi.github.io/CommonDataModel
877 stars 448 forks source link

Error at running OMOP_CDM_impala_ddl.sql #292

Closed jcabrerazuniga closed 3 years ago

jcabrerazuniga commented 5 years ago

I executed:

impala-shell -d omop_cdm -f OMOP_CDM_impala_ddl.sql -i impalaserver Starting Impala Shell without Kerberos authentication Connected to impalaserver:21000

and saw:

Query: insert INTO metadata (metadata_concept_id, metadata_type_concept_id, name, value_as_string, value_as_concept_id, metadata_date, metadata_datetime) VALUES (0,0,'CDM Version', '6.0',0,NULL,NULL) ERROR: AnalysisException: Possible loss of precision for target table 'omop_cdm.metadata'. Expression ''CDM Version'' (type: STRING) would need to be cast to VARCHAR(250) for column 'name'

Could not execute command: insert INTO metadata (metadata_concept_id, metadata_type_concept_id, name, value_as_string, value_as_concept_id, metadata_date, metadata_datetime) VALUES (0,0,'CDM Version', '6.0',0,NULL,NULL)

What could be the problem? Next is the whole log:

/HostShared# impala-shell -q 'CREATE DATABASE omop_cdm' -i impalaserver Starting Impala Shell without Kerberos authentication Connected to impalaserver:21000 Server version: impalad version 3.4.0-SNAPSHOT DEBUG (build 983e3a66deef5bce52e9ebe74db8e2f86233d036) Query: create DATABASE omop_cdm +----------------------------+ | summary | +----------------------------+ | Database has been created. | +----------------------------+ Returned 1 row(s) in 0.35s

root@8cac62917c74:/HostShared# impala-shell -d omop_cdm -f OMOP_CDM_impala_ddl.sql -i impalaserver Starting Impala Shell without Kerberos authentication Connected to impalaserver:21000 Server version: impalad version 3.4.0-SNAPSHOT DEBUG (build 983e3a66deef5bce52e9ebe74db8e2f86233d036) Query: use omop_cdm Query: create TABLE concept ( concept_id INTEGER , concept_name VARCHAR(255) , domain_id VARCHAR(20) , vocabulary_id VARCHAR(20) , concept_class_id VARCHAR(20) , standard_concept VARCHAR(1) , concept_code VARCHAR(50) , valid_start_date TIMESTAMP , valid_end_date TIMESTAMP , invalid_reason VARCHAR(1) ) +-------------------------+ | summary | +-------------------------+ | Table has been created. | +-------------------------+ Returned 1 row(s) in 0.35s Query: create TABLE vocabulary ( vocabulary_id VARCHAR(20), vocabulary_name VARCHAR(255), vocabulary_reference VARCHAR(255), vocabulary_version VARCHAR(255), vocabulary_concept_id INTEGER ) +-------------------------+ | summary | +-------------------------+ | Table has been created. | +-------------------------+ Returned 1 row(s) in 0.50s Query: create TABLE domain ( domain_id VARCHAR(20), domain_name VARCHAR(255), domain_concept_id INTEGER ) +-------------------------+ | summary | +-------------------------+ | Table has been created. | +-------------------------+ Returned 1 row(s) in 0.49s Query: create TABLE concept_class ( concept_class_id VARCHAR(20), concept_class_name VARCHAR(255), concept_class_concept_id INTEGER ) +-------------------------+ | summary | +-------------------------+ | Table has been created. | +-------------------------+ Returned 1 row(s) in 0.48s Query: create TABLE concept_relationship ( concept_id_1 INTEGER , concept_id_2 INTEGER , relationship_id VARCHAR(20), valid_start_date TIMESTAMP, valid_end_date TIMESTAMP, invalid_reason VARCHAR(1) ) +-------------------------+ | summary | +-------------------------+ | Table has been created. | +-------------------------+ Returned 1 row(s) in 0.52s Query: create TABLE relationship ( relationship_id VARCHAR(20), relationship_name VARCHAR(255), is_hierarchical VARCHAR(1), defines_ancestry VARCHAR(1), reverse_relationship_id VARCHAR(20), relationship_concept_id INTEGER ) +-------------------------+ | summary | +-------------------------+ | Table has been created. | +-------------------------+ Returned 1 row(s) in 0.49s Query: create TABLE concept_synonym ( concept_id INTEGER , concept_synonym_name STRING, language_concept_id INTEGER ) +-------------------------+ | summary | +-------------------------+ | Table has been created. | +-------------------------+ Returned 1 row(s) in 0.50s Query: create TABLE concept_ancestor ( ancestor_concept_id INTEGER , descendant_concept_id INTEGER , min_levels_of_separation INTEGER , max_levels_of_separation INTEGER ) +-------------------------+ | summary | +-------------------------+ | Table has been created. | +-------------------------+ Returned 1 row(s) in 0.50s Query: create TABLE source_to_concept_map ( source_code VARCHAR(50), source_concept_id INTEGER , source_vocabulary_id VARCHAR(20), source_code_description VARCHAR(255), target_concept_id INTEGER , target_vocabulary_id VARCHAR(20), valid_start_date TIMESTAMP, valid_end_date TIMESTAMP, invalid_reason VARCHAR(1) ) +-------------------------+ | summary | +-------------------------+ | Table has been created. | +-------------------------+ Returned 1 row(s) in 0.49s Query: create TABLE drug_strength ( drug_concept_id INTEGER , ingredient_concept_id INTEGER , amount_value FLOAT, amount_unit_concept_id INTEGER , numerator_value FLOAT, numerator_unit_concept_id INTEGER , denominator_value FLOAT, denominator_unit_concept_id INTEGER , box_size INTEGER , valid_start_date TIMESTAMP, valid_end_date TIMESTAMP, invalid_reason VARCHAR(1) ) +-------------------------+ | summary | +-------------------------+ | Table has been created. | +-------------------------+ Returned 1 row(s) in 0.49s Query: create TABLE cdm_source ( cdm_source_name VARCHAR(255) , cdm_source_abbreviation VARCHAR(25) , cdm_holder VARCHAR(255) , source_description STRING , source_documentation_reference VARCHAR(255) , cdm_etl_reference VARCHAR(255) , source_release_date TIMESTAMP , cdm_release_date TIMESTAMP , cdm_version VARCHAR(10) , vocabulary_version VARCHAR(20) ) +-------------------------+ | summary | +-------------------------+ | Table has been created. | +-------------------------+ Returned 1 row(s) in 0.49s Query: create TABLE metadata ( metadata_concept_id INTEGER , metadata_type_concept_id INTEGER , name VARCHAR(250) , value_as_string STRING , value_as_concept_id INTEGER , metadata_date TIMESTAMP , metadata_datetime TIMESTAMP ) +-------------------------+ | summary | +-------------------------+ | Table has been created. | +-------------------------+ Returned 1 row(s) in 0.50s Query: insert INTO metadata (metadata_concept_id, metadata_type_concept_id, name, value_as_string, value_as_concept_id, metadata_date, metadata_datetime) VALUES (0,0,'CDM Version', '6.0',0,NULL,NULL) ERROR: AnalysisException: Possible loss of precision for target table 'omop_cdm.metadata'. Expression ''CDM Version'' (type: STRING) would need to be cast to VARCHAR(250) for column 'name'

Could not execute command: insert INTO metadata (metadata_concept_id, metadata_type_concept_id, name, value_as_string, value_as_concept_id, metadata_date, metadata_datetime) VALUES (0,0,'CDM Version', '6.0',0,NULL,NULL)

root@8cac62917c74:/HostShared#

clairblacketer commented 4 years ago

I apologize, I am not as familiar with Impala. It looks like the text 'CDM Version' is being recognized as a string rather than as a varchar value. Is there a way to cast to a varchar as you are inserting the row?

clairblacketer commented 3 years ago

@jcabrerazuniga feel free to reopen if you need but since this has been out here for over a year I am closing it.