Creating the Vocabulary (Workflow) - dbmi-pitt/np-terminology-imports GitHub Wiki

Workflow to add NapDI project concepts and relationships to the OMOP Standard Vocabulary

This process assumes that you have reserved (-9999999, -7000000) for custom concept ids. The script will create the concept ids as it generates the insert statements. The assumption is that you will delete all custom concepts and re-run the entire process every time you add or modify the custom concepts. This is important because, if you don't completely replace the concepts every time, you will get identifier issues.

START: IF you are rerunning this, use these queries to delete the existing custom concepts. If you ever need to change the range of concepts edit the range in the python script and uncomment the line in the main method to generate the delete queries.

   There are two approaches depending on if the DB is AWS or not
--- APPROACH FOR NON-AWS --- RUN AS SUPER USER ON THE DATABASE OF INTEREST
alter table staging_vocabulary.concept disable trigger all;
alter table staging_vocabulary.vocabulary disable trigger all;
alter table staging_vocabulary.domain  disable trigger all;
alter table staging_vocabulary.concept_class disable trigger all;
alter table staging_vocabulary.relationship disable trigger all;
alter table staging_vocabulary.concept_relationship disable trigger all;

DELETE FROM staging_vocabulary.concept cascade WHERE concept_id BETWEEN -9999999 AND -7000000 ;
DELETE FROM staging_vocabulary.vocabulary cascade WHERE vocabulary_concept_id BETWEEN -9999999 AND -7000000 ;
DELETE FROM staging_vocabulary.domain cascade WHERE domain_concept_id BETWEEN -9999999 AND -7000000 ;
DELETE FROM staging_vocabulary.concept_class cascade WHERE concept_class_concept_id BETWEEN -9999999 AND -7000000 ;
DELETE FROM staging_vocabulary.relationship cascade WHERE relationship_concept_id BETWEEN -9999999 AND -7000000 ;
DELETE FROM staging_vocabulary.concept_relationship cascade WHERE relationship_id like 'napdi_%';

alter table staging_vocabulary.concept enable trigger all;
alter table staging_vocabulary.vocabulary enable trigger all;
alter table staging_vocabulary.domain  enable trigger all;
alter table staging_vocabulary.concept_class enable trigger all;
alter table staging_vocabulary.relationship enable trigger all;
alter table staging_vocabulary.concept_relationship enable trigger all;

-----------------
--- APROACH FOR AWS --- RUN AS RDS_SUPERUSER ON THE DATABASE OF INTEREST (and be prepared to wait a long while because it has to reset constraints!)
alter table staging_vocabulary.concept ALTER CONSTRAINT "fpk_concept_class" DEFERRABLE INITIALLY DEFERRED;
alter table staging_vocabulary.concept ALTER CONSTRAINT "fpk_concept_domain" DEFERRABLE INITIALLY DEFERRED;
alter table staging_vocabulary.concept ALTER CONSTRAINT "fpk_concept_vocabulary"  DEFERRABLE INITIALLY DEFERRED;
alter table staging_vocabulary.concept_ancestor ALTER CONSTRAINT "fpk_concept_ancestor_concept_1" DEFERRABLE INITIALLY DEFERRED;
alter table staging_vocabulary.concept_ancestor ALTER CONSTRAINT "fpk_concept_ancestor_concept_2" DEFERRABLE INITIALLY DEFERRED;
alter table staging_vocabulary.concept_class ALTER CONSTRAINT "fpk_concept_class_concept" DEFERRABLE INITIALLY DEFERRED;
alter table staging_vocabulary.vocabulary ALTER CONSTRAINT "fpk_vocabulary_concept" DEFERRABLE INITIALLY DEFERRED;
alter table staging_vocabulary.domain ALTER CONSTRAINT "fpk_domain_concept"  DEFERRABLE INITIALLY DEFERRED;
alter table staging_vocabulary.concept_relationship ALTER CONSTRAINT "fpk_concept_relationship_c_1" DEFERRABLE INITIALLY DEFERRED;
alter table staging_vocabulary.concept_relationship ALTER CONSTRAINT "fpk_conlcept_relationship_c_2" DEFERRABLE INITIALLY DEFERRED;
alter table staging_vocabulary.concept_synonymn ALTER CONSTRAINT "fpk_concept_synonym_concept" DEFERRABLE INITIALLY DEFERRED;
alter table staging_vocabulary.drug_strength ALTER CONSTRAINT "fpk_drug_strength_concept_1"  DEFERRABLE INITIALLY DEFERRED;
alter table staging_vocabulary.drug_strength ALTER CONSTRAINT "fpk_drug_strength_concept_2" DEFERRABLE INITIALLY DEFERRED;
alter table staging_vocabulary.drug_strength ALTER CONSTRAINT "fpk_drug_strength_unit_1"  DEFERRABLE INITIALLY DEFERRED;
alter table staging_vocabulary.drug_strength ALTER CONSTRAINT "fpk_drug_strength_unit_2" DEFERRABLE INITIALLY DEFERRED;
alter table staging_vocabulary.drug_strength ALTER CONSTRAINT "fpk_drug_strength_unit_3" DEFERRABLE INITIALLY DEFERRED;
alter table staging_vocabulary.relationship ALTER CONSTRAINT "fpk_relationship_concept" DEFERRABLE INITIALLY DEFERRED;
alter table staging_vocabulary.source_to_concept_map ALTER CONSTRAINT "fpk_source_to_concept_map_c_1" DEFERRABLE INITIALLY DEFERRED;


BEGIN;
DELETE FROM staging_vocabulary.concept cascade WHERE concept_id BETWEEN -9999999 AND -7000000 ;
DELETE FROM staging_vocabulary.vocabulary cascade WHERE vocabulary_concept_id BETWEEN -9999999 AND -7000000 ;
DELETE FROM staging_vocabulary.domain cascade WHERE domain_concept_id BETWEEN -9999999 AND -7000000 ;
DELETE FROM staging_vocabulary.concept_class cascade WHERE concept_class_concept_id BETWEEN -9999999 AND -7000000 ;
END;

alter table staging_vocabulary.concept ALTER CONSTRAINT "fpk_concept_class" NOT DEFERRABLE;
alter table staging_vocabulary.concept ALTER CONSTRAINT "fpk_concept_domain" NOT DEFERRABLE;
alter table staging_vocabulary.concept ALTER CONSTRAINT "fpk_concept_vocabulary"  NOT DEFERRABLE;
alter table staging_vocabulary.concept_ancestor ALTER CONSTRAINT "fpk_concept_ancestor_concept_1" NOT DEFERRABLE;
alter table staging_vocabulary.concept_ancestor ALTER CONSTRAINT "fpk_concept_ancestor_concept_2" NOT DEFERRABLE;
alter table staging_vocabulary.concept_class ALTER CONSTRAINT "fpk_concept_class_concept" NOT DEFERRABLE;
alter table staging_vocabulary.vocabulary ALTER CONSTRAINT "fpk_vocabulary_concept" NOT DEFERRABLE;
alter table staging_vocabulary.domain ALTER CONSTRAINT "fpk_domain_concept"  NOT DEFERRABLE;
alter table staging_vocabulary.concept_relationship ALTER CONSTRAINT "fpk_concept_relationship_c_1" NOT DEFERRABLE;
alter table staging_vocabulary.concept_relationship ALTER CONSTRAINT "fpk_conlcept_relationship_c_2" NOT DEFERRABLE;
alter table staging_vocabulary.concept_synonymn ALTER CONSTRAINT "fpk_concept_synonym_concept" NOT DEFERRABLE;
alter table staging_vocabulary.drug_strength ALTER CONSTRAINT "fpk_drug_strength_concept_1"  NOT DEFERRABLE;
alter table staging_vocabulary.drug_strength ALTER CONSTRAINT "fpk_drug_strength_concept_2" NOT DEFERRABLE;
alter table staging_vocabulary.drug_strength ALTER CONSTRAINT "fpk_drug_strength_unit_1"  NOT DEFERRABLE;
alter table staging_vocabulary.drug_strength ALTER CONSTRAINT "fpk_drug_strength_unit_2" NOT DEFERRABLE;
alter table staging_vocabulary.drug_strength ALTER CONSTRAINT "fpk_drug_strength_unit_3" NOT DEFERRABLE;
alter table staging_vocabulary.relationship ALTER CONSTRAINT "fpk_relationship_concept" NOT DEFERRABLE;
alter table staging_vocabulary.source_to_concept_map ALTER CONSTRAINT "fpk_source_to_concept_map_c_1" NOT DEFERRABLE;

---

WORKFLOW:

  1. update GSRS NPs, common names, and constituents in lb_to_common_names_tsv, test_srs_np, and test_srs_np_constituent (currently in the scratch_sanya schema of the GSRS database). See GSRS database query notes

  2. update the manually curated NP spelling variations in np_faers_reference_set (currently in the scratch_sanya schema of the CEM database)

  3. log into the database in an admin role and drop all prior NAPDI vocabulary concepts, relationships, and concept relationship mappings (see above)

  4. run the latest SQL script from source folder

  5. test that the vocabulary is working and makes sense using queries like the following:

-- Test using Kratom 
select * from staging_vocabulary.concept c where concept_class_id = 'Kratom';
/*
concept_id	concept_name	domain_id	vocabulary_id	concept_class_id	standard_concept	concept_code	valid_start_date	valid_end_date	invalid_reason
-7000583	Kratom[Mitragyna speciosa]	NaPDI research	NAPDI	Kratom		d469b67d-e9a6-459f-b209-c59451936336	2000-01-01	2099-02-22	
-7000584	Kratum[Mitragyna speciosa]	NaPDI research	NAPDI	Kratom		d469b67d-e9a6-459f-b209-c59451936336	2000-01-01	2099-02-22	
-7001354	Mitragyna speciosa[Mitragyna speciosa]	NaPDI research	NAPDI	Kratom		d469b67d-e9a6-459f-b209-c59451936336	2000-01-01	2099-02-22	
*/


select c1.concept_name, c1.concept_id,  c2.concept_name, c2.concept_id
from staging_vocabulary.concept c1 
	 inner join staging_vocabulary.concept_relationship cr on c1.concept_id = cr.concept_id_1  
     inner join staging_vocabulary.concept c2 on c2.concept_id = cr.concept_id_2
where cr.relationship_id = 'napdi_pt'
   and c1.concept_id = '-7000583'
;
/*
Kratom[Mitragyna speciosa]	-7000583	Kratom	-7001114
 */

select c1.concept_name, c1.concept_id,  c2.concept_name, c2.concept_id
from staging_vocabulary.concept c1 
	 inner join staging_vocabulary.concept_relationship cr on c1.concept_id = cr.concept_id_1  
     inner join staging_vocabulary.concept c2 on c2.concept_id = cr.concept_id_2
where cr.relationship_id = 'napdi_is_pt_of'
   and c1.concept_id = '-7001114'
;
/*
Kratom	-7001114	Mitragyna speciosa[Mitragyna speciosa]	-7001354
Kratom	-7001114	Kratum[Mitragyna speciosa]	-7000584
Kratom	-7001114	Kratom[Mitragyna speciosa]	-7000583
 */

select c1.concept_name, c1.concept_id,  c2.concept_name, c2.concept_id
from staging_vocabulary.concept c1 
	 inner join staging_vocabulary.concept_relationship cr on c1.concept_id = cr.concept_id_1  
     inner join staging_vocabulary.concept c2 on c2.concept_id = cr.concept_id_2
where cr.relationship_id = 'napdi_has_const'
   and c1.concept_id = '-7001354'
;
/*
Mitragyna speciosa[Mitragyna speciosa]	-7001354	SPECIOGYNINE	-7002260
Mitragyna speciosa[Mitragyna speciosa]	-7001354	SPECIOCILIATIN	-7002004
Mitragyna speciosa[Mitragyna speciosa]	-7001354	PAYNANTHEINE	-7001982
Mitragyna speciosa[Mitragyna speciosa]	-7001354	MITRAGYNINE	-7001824
Mitragyna speciosa[Mitragyna speciosa]	-7001354	7-HYDROXYMITRAGYNINE	-7001808
Mitragyna speciosa[Mitragyna speciosa]	-7001354	MITRAPHYLLINE	-7001567
*/

select distinct c1.concept_name, c1.concept_id,  c3.concept_name, c3.concept_id
from staging_vocabulary.concept c1 
	 inner join staging_vocabulary.concept_relationship cr1 on c1.concept_id = cr1.concept_id_1  
     inner join staging_vocabulary.concept c2 on c2.concept_id = cr1.concept_id_2
     inner join staging_vocabulary.concept_relationship cr2 on c2.concept_id = cr2.concept_id_1
     inner join staging_vocabulary.concept c3 on c3.concept_id = cr2.concept_id_2
where cr1.relationship_id = 'napdi_is_pt_of'
   and cr2.relationship_id = 'napdi_has_const'
   and c1.concept_id = '-7001114'
;
/*
Kratom	-7001114	7-HYDROXYMITRAGYNINE	-7001808
Kratom	-7001114	MITRAGYNINE	-7001824
Kratom	-7001114	MITRAPHYLLINE	-7001567
Kratom	-7001114	PAYNANTHEINE	-7001982
Kratom	-7001114	SPECIOCILIATIN	-7002004
Kratom	-7001114	SPECIOGYNINE	-7002260 
*/


select c1.concept_name, c1.concept_id,  c2.concept_name, c2.concept_id
from staging_vocabulary.concept c1 
	 inner join staging_vocabulary.concept_relationship cr on c1.concept_id = cr.concept_id_1  
     inner join staging_vocabulary.concept c2 on c2.concept_id = cr.concept_id_2
where cr.relationship_id = 'napdi_is_const_of'
   and c1.concept_id = '-7001824'
;
/*
MITRAGYNINE	-7001824	Mitragyna speciosa[Mitragyna speciosa]	-7001354
MITRAGYNINE	-7001824	Kratum[Mitragyna speciosa]	-7000584
MITRAGYNINE	-7001824	Kratom[Mitragyna speciosa]	-7000583
 */

select distinct c1.concept_name, c1.concept_id,  c3.concept_name, c3.concept_id
from staging_vocabulary.concept c1 
	 inner join staging_vocabulary.concept_relationship cr1 on c1.concept_id = cr1.concept_id_1  
     inner join staging_vocabulary.concept c2 on c2.concept_id = cr1.concept_id_2
     inner join staging_vocabulary.concept_relationship cr2 on c2.concept_id = cr2.concept_id_1
     inner join staging_vocabulary.concept c3 on c3.concept_id = cr2.concept_id_2
where cr1.relationship_id = 'napdi_is_const_of'
   and cr2.relationship_id = 'napdi_pt'
   and c1.concept_id = '-7001824'
;
/*
MITRAGYNINE	-7001824	Kratom	-7001114
*/


select c1.concept_name, c1.concept_id,  c2.concept_name, c2.concept_id
from staging_vocabulary.concept c1 
	 inner join staging_vocabulary.concept_relationship cr on c1.concept_id = cr.concept_id_1  
     inner join staging_vocabulary.concept c2 on c2.concept_id = cr.concept_id_2
where cr.relationship_id = 'napdi_spell_vr'
   and c1.concept_id = '-7000583'
;
/*
Kratom[Mitragyna speciosa]	-7000583	WHOLE HERBS PREMIUM MAENG DA KRATOM	-7003825
Kratom[Mitragyna speciosa]	-7000583	WHITE MAENG DA KRATOM 250G	-7003824
Kratom[Mitragyna speciosa]	-7000583	WHITE MAENG DA HERBAL TEA KRATOM	-7003823
Kratom[Mitragyna speciosa]	-7000583	VIVA ZEN KRATOM	-7003822
Kratom[Mitragyna speciosa]	-7000583	VIVAZEN BOTANICALS MAENG DA KRATOM	-7003821
Kratom[Mitragyna speciosa]	-7000583	UNICORN DUST STRAIN OF KRATOM	-7003820
Kratom[Mitragyna speciosa]	-7000583	TRAIN WRECK KRATOM	-7003819
Kratom[Mitragyna speciosa]	-7000583	TAUNTON BAY SOAP COMPANY RED VEIN TEA-1LB. PACKAGE (KRATOM)	-7003818
Kratom[Mitragyna speciosa]	-7000583	SUPERIOR RED DRAGON KRATOM	-7003817
Kratom[Mitragyna speciosa]	-7000583	SUPER GREEN KRATOM POWDER	-7003816
Kratom[Mitragyna speciosa]	-7000583	SUPER GREEN HORN KRATOM	-7003815
Kratom[Mitragyna speciosa]	-7000583	SLOW-MO HIPPO KRATOM	-7003814
Kratom[Mitragyna speciosa]	-7000583	R.H. NATURAL PRODUCTS KRATOM	-7003813
Kratom[Mitragyna speciosa]	-7000583	RED VEIN MAENG DA (KRATOM)	-7003812
Kratom[Mitragyna speciosa]	-7000583	RED VEIN KRATOM	-7003811
Kratom[Mitragyna speciosa]	-7000583	RED VEIN BORNEO KRATOM	-7003810
Kratom[Mitragyna speciosa]	-7000583	RED THAI KRATOM	-7003809
Kratom[Mitragyna speciosa]	-7000583	RED MAENG DA KRATOM (MITRAGYNA SPECIOSA)	-7003808
Kratom[Mitragyna speciosa]	-7000583	RED DEVIL KRATOM WATER SOLUBLE CBD	-7003807
Kratom[Mitragyna speciosa]	-7000583	RED BORNEO KRATOM BUMBLE BEE	-7003806
Kratom[Mitragyna speciosa]	-7000583	RED BALI KRATOM	-7003805
Kratom[Mitragyna speciosa]	-7000583	RAW FORM ORGANICS MAENG DA 150 RUBY CAPSULES KRATOM	-7003804
Kratom[Mitragyna speciosa]	-7000583	PREMIUM RED MAENG DA KRATOM	-7003803
Kratom[Mitragyna speciosa]	-7000583	PREMIUM RED MAENG DA CRAZY KRATOM	-7003802
Kratom[Mitragyna speciosa]	-7000583	PREMIUM KRATOM PHOENIX RED VEIN BALI	-7003801
Kratom[Mitragyna speciosa]	-7000583	POWDERED KRATOM	-7003800
Kratom[Mitragyna speciosa]	-7000583	O.P.M.S. LIQUID KRATOM	-7003799
Kratom[Mitragyna speciosa]	-7000583	O.P.M.S. KRATOM	-7003798
Kratom[Mitragyna speciosa]	-7000583	NUTRIZONE KRATOM PAIN OUT MAENG DA	-7003797
Kratom[Mitragyna speciosa]	-7000583	NATURE'S REMEDY KRATOM	-7003796
Kratom[Mitragyna speciosa]	-7000583	MITRAGYNINE (KRATOM)	-7003795
Kratom[Mitragyna speciosa]	-7000583	MITRAGYNINE KRATOM	-7003794
Kratom[Mitragyna speciosa]	-7000583	MITRAGYNA SPECIOSA (MITRAGYNINE)	-7003793
Kratom[Mitragyna speciosa]	-7000583	MITRAGYNA SPECIOSA LEAF	-7003792
Kratom[Mitragyna speciosa]	-7000583	MITRAGYNA SPECIOSA KORTHALS (BOTANIC NAME) (KRATOM)	-7003791
Kratom[Mitragyna speciosa]	-7000583	MITRAGYNA SPECIOSA KORTHALS (BOTANIC NAME)	-7003790
Kratom[Mitragyna speciosa]	-7000583	MITRAGYNA SPECIOSA KORTHALS	-7003789
Kratom[Mitragyna speciosa]	-7000583	MITRAGYNA SPECIOSA	-7003788
Kratom[Mitragyna speciosa]	-7000583	MAENG DA POWDER KRATOM HERBAL DIETARY SUPPLEMENT	-7003787
Kratom[Mitragyna speciosa]	-7000583	MAENG DA KRATOM MITROGYNA SPECIOSA	-7003786
Kratom[Mitragyna speciosa]	-7000583	MAENG DA KRATOM	-7003785
Kratom[Mitragyna speciosa]	-7000583	LYFT PREMIUM BALI KRATOM HERBAL SUPPLEMENT POWDER	-7003784
Kratom[Mitragyna speciosa]	-7000583	LUCKY SEVEN KRATOM	-7003783
Kratom[Mitragyna speciosa]	-7000583	KRAVE KRATOM	-7003782
Kratom[Mitragyna speciosa]	-7000583	KRAVE, BLUE MAGIC KRATOM	-7003781
Kratom[Mitragyna speciosa]	-7000583	KRATOM- USED SUPER GREEN, GREEN BALI, RED MAGNEA DA	-7003780
Kratom[Mitragyna speciosa]	-7000583	KRATOM SUPPLEMENT	-7003779
Kratom[Mitragyna speciosa]	-7000583	KRATOM - SPECIFICALLY ^GREEN MALAYSIAN^	-7003778
Kratom[Mitragyna speciosa]	-7000583	KRATOM SILVER THAI	-7003777
Kratom[Mitragyna speciosa]	-7000583	KRATOM RED DRAGON	-7003776
Kratom[Mitragyna speciosa]	-7000583	KRATOM POWDER	-7003775
Kratom[Mitragyna speciosa]	-7000583	KRATOM (MITRAGYNINE)	-7003774
Kratom[Mitragyna speciosa]	-7000583	KRATOM (MITRAGYNA SPECIOSA LEAF)	-7003773
Kratom[Mitragyna speciosa]	-7000583	KRATOM MITRAGYNA SPECIOSA	-7003772
Kratom[Mitragyna speciosa]	-7000583	KRATOM (MITRAGYNA SPECIOSA)	-7003771
Kratom[Mitragyna speciosa]	-7000583	KRATOM (MITRAGYNA) (MITRAGYNINE)	-7003770
Kratom[Mitragyna speciosa]	-7000583	KRATOM (MITRAGYNA)	-7003769
Kratom[Mitragyna speciosa]	-7000583	KRATOM MAGNA RED	-7003768
Kratom[Mitragyna speciosa]	-7000583	(KRATOM) KRAOMA.COM TRANQUIL KRAOMA	-7003767
Kratom[Mitragyna speciosa]	-7000583	KRATOM INDO	-7003766
Kratom[Mitragyna speciosa]	-7000583	KRATOM IN A UNMARKED BAG	-7003765
Kratom[Mitragyna speciosa]	-7000583	KRATOM HERBAL SUPPLEMENT	-7003764
Kratom[Mitragyna speciosa]	-7000583	KRATOM GREEN MAGNA DA	-7003763
Kratom[Mitragyna speciosa]	-7000583	KRATOM EXTRACT	-7003762
Kratom[Mitragyna speciosa]	-7000583	KRATOM ELEPHANT WHITE THAI	-7003761
Kratom[Mitragyna speciosa]	-7000583	KRATOM CAPSULES	-7003760
Kratom[Mitragyna speciosa]	-7000583	KRATOM 3 OZ.	-7003759
Kratom[Mitragyna speciosa]	-7000583	KRATOM	-7003758
Kratom[Mitragyna speciosa]	-7000583	KRAKEN KRATOM	-7003757
Kratom[Mitragyna speciosa]	-7000583	KRABOT KRATOM FINELY GROUND POWDER	-7003756
Kratom[Mitragyna speciosa]	-7000583	KLARITY KRATOM: MAENG DA CAPSULES	-7003755
Kratom[Mitragyna speciosa]	-7000583	INDO KRATOM	-7003754
Kratom[Mitragyna speciosa]	-7000583	HERBAL SUBSTANCE KRATOM	-7003753
Kratom[Mitragyna speciosa]	-7000583	HERBAL SALVATION KRATOM	-7003752
Kratom[Mitragyna speciosa]	-7000583	GREEN STRAIN TROPICAL KRATOM	-7003751
Kratom[Mitragyna speciosa]	-7000583	GREEN M BATIK AND RED BATIK KRATOM	-7003750
Kratom[Mitragyna speciosa]	-7000583	GREEN MALAY KRATOM	-7003749
Kratom[Mitragyna speciosa]	-7000583	GREEN BORNEO KRATOM	-7003748
Kratom[Mitragyna speciosa]	-7000583	FEELIN' GROOVY KRATOM	-7003747
Kratom[Mitragyna speciosa]	-7000583	EMERALD LEAF BALI KRATOM (HERBALSMITRAGYNINE)	-7003746
Kratom[Mitragyna speciosa]	-7000583	EMERALD KRATOM POWDER	-7003745
Kratom[Mitragyna speciosa]	-7000583	EARTH KRATOM ORGANIC RED MAENG DA	-7003744
Kratom[Mitragyna speciosa]	-7000583	CLUB 13 KRATOM MAENG DA RED 90GM	-7003743
Kratom[Mitragyna speciosa]	-7000583	CAROLINA KRATOM RED JONGKONG 100 GRAM POWDER	-7003742
Kratom[Mitragyna speciosa]	-7000583	CALCIUM KRATOMOS	-7003741
Kratom[Mitragyna speciosa]	-7000583	BRILLIANT ELIXIR, CHOCOLATE LOVER W/ KRATOM	-7003740
Kratom[Mitragyna speciosa]	-7000583	BLUE MAGIC KRAVE KRATOM	-7003739
Kratom[Mitragyna speciosa]	-7000583	BALI KRATOM	-7003738
*/


select c1.concept_name, c1.concept_id,  c2.concept_name, c2.concept_id
from staging_vocabulary.concept c1 
	 inner join staging_vocabulary.concept_relationship cr on c1.concept_id = cr.concept_id_1  
     inner join staging_vocabulary.concept c2 on c2.concept_id = cr.concept_id_2
where cr.relationship_id = 'napdi_is_spell_vr_of'
   and c1.concept_id = '-7003743'
;
/*
CLUB 13 KRATOM MAENG DA RED 90GM	-7003743	Mitragyna speciosa[Mitragyna speciosa]	-7001354
CLUB 13 KRATOM MAENG DA RED 90GM	-7003743	Kratum[Mitragyna speciosa]	-7000584
CLUB 13 KRATOM MAENG DA RED 90GM	-7003743	Kratom[Mitragyna speciosa]	-7000583
 */

select distinct c1.concept_name, c1.concept_id,  c3.concept_name, c3.concept_id
from staging_vocabulary.concept c1 
	 inner join staging_vocabulary.concept_relationship cr1 on c1.concept_id = cr1.concept_id_1  
     inner join staging_vocabulary.concept c2 on c2.concept_id = cr1.concept_id_2
     inner join staging_vocabulary.concept_relationship cr2 on c2.concept_id = cr2.concept_id_1
     inner join staging_vocabulary.concept c3 on c3.concept_id = cr2.concept_id_2
where cr1.relationship_id = 'napdi_is_pt_of'
   and cr2.relationship_id = 'napdi_spell_vr'
   and c1.concept_id = '-7001114'
;
/*
Kratom	-7001114	BALI KRATOM	-7003738
Kratom	-7001114	BLUE MAGIC KRAVE KRATOM	-7003739
Kratom	-7001114	BRILLIANT ELIXIR, CHOCOLATE LOVER W/ KRATOM	-7003740
Kratom	-7001114	CALCIUM KRATOMOS	-7003741
Kratom	-7001114	CAROLINA KRATOM RED JONGKONG 100 GRAM POWDER	-7003742
Kratom	-7001114	CLUB 13 KRATOM MAENG DA RED 90GM	-7003743
Kratom	-7001114	EARTH KRATOM ORGANIC RED MAENG DA	-7003744
Kratom	-7001114	EMERALD KRATOM POWDER	-7003745
Kratom	-7001114	EMERALD LEAF BALI KRATOM (HERBALSMITRAGYNINE)	-7003746
Kratom	-7001114	FEELIN' GROOVY KRATOM	-7003747
Kratom	-7001114	GREEN BORNEO KRATOM	-7003748
Kratom	-7001114	GREEN MALAY KRATOM	-7003749
Kratom	-7001114	GREEN M BATIK AND RED BATIK KRATOM	-7003750
Kratom	-7001114	GREEN STRAIN TROPICAL KRATOM	-7003751
Kratom	-7001114	HERBAL SALVATION KRATOM	-7003752
Kratom	-7001114	HERBAL SUBSTANCE KRATOM	-7003753
Kratom	-7001114	INDO KRATOM	-7003754
Kratom	-7001114	KLARITY KRATOM: MAENG DA CAPSULES	-7003755
Kratom	-7001114	KRABOT KRATOM FINELY GROUND POWDER	-7003756
Kratom	-7001114	KRAKEN KRATOM	-7003757
Kratom	-7001114	KRATOM	-7003758
Kratom	-7001114	KRATOM 3 OZ.	-7003759
Kratom	-7001114	KRATOM CAPSULES	-7003760
Kratom	-7001114	KRATOM ELEPHANT WHITE THAI	-7003761
Kratom	-7001114	KRATOM EXTRACT	-7003762
Kratom	-7001114	KRATOM GREEN MAGNA DA	-7003763
Kratom	-7001114	KRATOM HERBAL SUPPLEMENT	-7003764
Kratom	-7001114	KRATOM IN A UNMARKED BAG	-7003765
Kratom	-7001114	KRATOM INDO	-7003766
Kratom	-7001114	(KRATOM) KRAOMA.COM TRANQUIL KRAOMA	-7003767
Kratom	-7001114	KRATOM MAGNA RED	-7003768
Kratom	-7001114	KRATOM (MITRAGYNA)	-7003769
Kratom	-7001114	KRATOM (MITRAGYNA) (MITRAGYNINE)	-7003770
Kratom	-7001114	KRATOM (MITRAGYNA SPECIOSA)	-7003771
Kratom	-7001114	KRATOM MITRAGYNA SPECIOSA	-7003772
Kratom	-7001114	KRATOM (MITRAGYNA SPECIOSA LEAF)	-7003773
Kratom	-7001114	KRATOM (MITRAGYNINE)	-7003774
Kratom	-7001114	KRATOM POWDER	-7003775
Kratom	-7001114	KRATOM RED DRAGON	-7003776
Kratom	-7001114	KRATOM SILVER THAI	-7003777
Kratom	-7001114	KRATOM - SPECIFICALLY ^GREEN MALAYSIAN^	-7003778
Kratom	-7001114	KRATOM SUPPLEMENT	-7003779
Kratom	-7001114	KRATOM- USED SUPER GREEN, GREEN BALI, RED MAGNEA DA	-7003780
Kratom	-7001114	KRAVE, BLUE MAGIC KRATOM	-7003781
Kratom	-7001114	KRAVE KRATOM	-7003782
Kratom	-7001114	LUCKY SEVEN KRATOM	-7003783
Kratom	-7001114	LYFT PREMIUM BALI KRATOM HERBAL SUPPLEMENT POWDER	-7003784
Kratom	-7001114	MAENG DA KRATOM	-7003785
Kratom	-7001114	MAENG DA KRATOM MITROGYNA SPECIOSA	-7003786
Kratom	-7001114	MAENG DA POWDER KRATOM HERBAL DIETARY SUPPLEMENT	-7003787
Kratom	-7001114	MITRAGYNA SPECIOSA	-7003788
Kratom	-7001114	MITRAGYNA SPECIOSA KORTHALS	-7003789
Kratom	-7001114	MITRAGYNA SPECIOSA KORTHALS (BOTANIC NAME)	-7003790
Kratom	-7001114	MITRAGYNA SPECIOSA KORTHALS (BOTANIC NAME) (KRATOM)	-7003791
Kratom	-7001114	MITRAGYNA SPECIOSA LEAF	-7003792
Kratom	-7001114	MITRAGYNA SPECIOSA (MITRAGYNINE)	-7003793
Kratom	-7001114	MITRAGYNINE KRATOM	-7003794
Kratom	-7001114	MITRAGYNINE (KRATOM)	-7003795
Kratom	-7001114	NATURE'S REMEDY KRATOM	-7003796
Kratom	-7001114	NUTRIZONE KRATOM PAIN OUT MAENG DA	-7003797
Kratom	-7001114	O.P.M.S. KRATOM	-7003798
Kratom	-7001114	O.P.M.S. LIQUID KRATOM	-7003799
Kratom	-7001114	POWDERED KRATOM	-7003800
Kratom	-7001114	PREMIUM KRATOM PHOENIX RED VEIN BALI	-7003801
Kratom	-7001114	PREMIUM RED MAENG DA CRAZY KRATOM	-7003802
Kratom	-7001114	PREMIUM RED MAENG DA KRATOM	-7003803
Kratom	-7001114	RAW FORM ORGANICS MAENG DA 150 RUBY CAPSULES KRATOM	-7003804
Kratom	-7001114	RED BALI KRATOM	-7003805
Kratom	-7001114	RED BORNEO KRATOM BUMBLE BEE	-7003806
Kratom	-7001114	RED DEVIL KRATOM WATER SOLUBLE CBD	-7003807
Kratom	-7001114	RED MAENG DA KRATOM (MITRAGYNA SPECIOSA)	-7003808
Kratom	-7001114	RED THAI KRATOM	-7003809
Kratom	-7001114	RED VEIN BORNEO KRATOM	-7003810
Kratom	-7001114	RED VEIN KRATOM	-7003811
Kratom	-7001114	RED VEIN MAENG DA (KRATOM)	-7003812
Kratom	-7001114	R.H. NATURAL PRODUCTS KRATOM	-7003813
Kratom	-7001114	SLOW-MO HIPPO KRATOM	-7003814
Kratom	-7001114	SUPER GREEN HORN KRATOM	-7003815
Kratom	-7001114	SUPER GREEN KRATOM POWDER	-7003816
Kratom	-7001114	SUPERIOR RED DRAGON KRATOM	-7003817
Kratom	-7001114	TAUNTON BAY SOAP COMPANY RED VEIN TEA-1LB. PACKAGE (KRATOM)	-7003818
Kratom	-7001114	TRAIN WRECK KRATOM	-7003819
Kratom	-7001114	UNICORN DUST STRAIN OF KRATOM	-7003820
Kratom	-7001114	VIVAZEN BOTANICALS MAENG DA KRATOM	-7003821
Kratom	-7001114	VIVA ZEN KRATOM	-7003822
Kratom	-7001114	WHITE MAENG DA HERBAL TEA KRATOM	-7003823
Kratom	-7001114	WHITE MAENG DA KRATOM 250G	-7003824
Kratom	-7001114	WHOLE HERBS PREMIUM MAENG DA KRATOM	-7003825
*/