Homec4science
Diffusion SHRINE MedCo Fork (fork/1.22.8-medco) Browse

shrine-medco/shrine-webclient/src/main/tools/create_tables/postgres_load_tables
bc9e81c68695fork/1.22.8-medco

Last Change
Raw File

postgres_load_tables
View Options

create schema shrine_ont
-- --- NEW ONTOLOGY GENOMIC ---
create table shrine_ont.genomic_annotations
(
variant_id character varying(255) NOT NULL PRIMARY KEY,
variant_name character varying(255) NOT NULL,
hugo_gene_symbol character varying(255) NOT NULL,
protein_change character varying(255) NOT NULL,
annotations text NOT NULL
-- t_depth numeric NOT NULL
);
ALTER TABLE shrine_ont.genomic_annotations OWNER TO shrine_ont;
truncate shrine_ont.genomic_annotations;
\copy shrine_ont.genomic_annotations FROM '/home/niccolo/Downloads/new_tables/tables/genomic_annotations.csv' ESCAPE '"' DELIMITER ',' CSV;
-- VVV ON SERVERS DO THIS VVV
-- --- NEW ONTOLOGY GENOMIC ---
create table genomic_annotations
(
variant_id character varying(255) NOT NULL PRIMARY KEY,
variant_name character varying(255) NOT NULL,
hugo_gene_symbol character varying(255) NOT NULL,
protein_change character varying(255) NOT NULL,
annotations text NOT NULL
-- t_depth numeric NOT NULL
);
ALTER TABLE genomic_annotations OWNER TO genomic_annotations;
\copy genomic_annotations FROM '/root/genomic_annotations.csv' ESCAPE '"' DELIMITER ',' CSV;
--- VVV script to create and load the genomic table
#!/usr/bin/env bash
# pass as parameter the server id (e.g. "./dropCreateLoad.sh 82")
PGPASSWORD=prigen2017 psql -v ON_ERROR_STOP=1 -h "iccluster0"$1".iccluster.epfl.ch" -U "postgres" -p 5432 -d "i2b2medco" <<-EOSQL
BEGIN;
DROP TABLE IF EXISTS genomic_annotations.genomic_annotations;
create table genomic_annotations.genomic_annotations
(
variant_id character varying(255) NOT NULL PRIMARY KEY,
variant_name character varying(255) NOT NULL,
protein_change character varying(255) NOT NULL,
hugo_gene_symbol character varying(255) NOT NULL,
annotations text NOT NULL
);
ALTER TABLE genomic_annotations.genomic_annotations OWNER TO genomic_annotations;
--\copy genomic_annotations.genomic_annotations FROM 'files/SHRINE_ONT_GENOMIC_ANNOTATIONS.csv' ESCAPE '"' DELIMITER ',' CSV;
COMMIT;
EOSQL
c4science · Help