CREATE TABLE public.gene_copy_number_rs (
    cnv_id character varying(16383) ENCODE lzo,
    gene_id character varying(16383) ENCODE lzo,
    gene_name character varying(16383) ENCODE lzo,
    chromosome character varying(16383) ENCODE lzo,
    "start" character varying(16383) ENCODE lzo,
    "end" character varying(16383) ENCODE lzo,
    copy_number character varying(16383) ENCODE lzo,
    min_copy_number character varying(16383) ENCODE lzo,
    max_copy_number character varying(16383) ENCODE lzo,
    fullpath character varying(16383) ENCODE lzo,
    file character varying(16383) ENCODE lzo,
    ensemblgene character varying(16383) ENCODE lzo,
    id character varying(16383) ENCODE lzo,
    submitter_id super,
    result character varying(16383) ENCODE lzo
);


CREATE TABLE public.gene_mutation_rs (
    id character varying(16383) ENCODE lzo,
    hugo_symbol character varying(16383) ENCODE lzo,
    entrez_gene_id integer ENCODE az64,
    center character varying(16383) ENCODE lzo,
    ncbi_build character varying(16383) ENCODE lzo,
    chromosome character varying(16383) ENCODE lzo,
    start_position integer ENCODE az64,
    end_position integer ENCODE az64,
    strand character varying(16383) ENCODE lzo,
    variant_classification character varying(16383) ENCODE lzo,
    variant_type character varying(16383) ENCODE lzo,
    reference_allele character varying(16383) ENCODE lzo,
    tumor_seq_allele1 character varying(16383) ENCODE lzo,
    tumor_seq_allele2 character varying(16383) ENCODE lzo,
    dbsnp_rs character varying(16383) ENCODE lzo,
    dbsnp_val_status character varying(16383) ENCODE lzo,
    tumor_sample_barcode character varying(16383) ENCODE lzo,
    matched_norm_sample_barcode character varying(16383) ENCODE lzo,
    match_norm_seq_allele1 character varying(16383) ENCODE lzo,
    match_norm_seq_allele2 character varying(16383) ENCODE lzo,
    tumor_validation_allele1 character varying(16383) ENCODE lzo,
    tumor_validation_allele2 character varying(16383) ENCODE lzo,
    match_norm_validation_allele1 character varying(16383) ENCODE lzo,
    match_norm_validation_allele2 character varying(16383) ENCODE lzo,
    verification_status character varying(16383) ENCODE lzo,
    validation_status character varying(16383) ENCODE lzo,
    mutation_status character varying(16383) ENCODE lzo,
    sequencing_phase character varying(16383) ENCODE lzo,
    sequence_source character varying(16383) ENCODE lzo,
    validation_method character varying(16383) ENCODE lzo,
    score character varying(16383) ENCODE lzo,
    bam_file character varying(16383) ENCODE lzo,
    sequencer character varying(16383) ENCODE lzo,
    tumor_sample_uuid character varying(16383) ENCODE lzo,
    matched_norm_sample_uuid character varying(16383) ENCODE lzo,
    hgvsc character varying(16383) ENCODE lzo,
    hgvsp character varying(16383) ENCODE lzo,
    hgvsp_short character varying(16383) ENCODE lzo,
    transcript_id character varying(16383) ENCODE lzo,
    exon_number character varying(16383) ENCODE lzo,
    t_depth integer ENCODE az64,
    t_ref_count integer ENCODE az64,
    t_alt_count integer ENCODE az64,
    n_depth integer ENCODE az64,
    n_ref_count character varying(16383) ENCODE lzo,
    n_alt_count character varying(16383) ENCODE lzo,
    all_effects character varying(16383) ENCODE lzo,
    allele character varying(16383) ENCODE lzo,
    gene character varying(16383) ENCODE lzo,
    feature character varying(16383) ENCODE lzo,
    feature_type character varying(16383) ENCODE lzo,
    one_consequence character varying(16383) ENCODE lzo,
    consequence character varying(16383) ENCODE lzo,
    cdna_position character varying(16383) ENCODE lzo,
    cds_position character varying(16383) ENCODE lzo,
    protein_position character varying(16383) ENCODE lzo,
    amino_acids character varying(16383) ENCODE lzo,
    codons character varying(16383) ENCODE lzo,
    existing_variation character varying(16383) ENCODE lzo,
    distance integer ENCODE az64,
    transcript_strand integer ENCODE az64,
    symbol character varying(16383) ENCODE lzo,
    symbol_source character varying(16383) ENCODE lzo,
    hgnc_id character varying(16383) ENCODE lzo,
    biotype character varying(16383) ENCODE lzo,
    canonical character varying(16383) ENCODE lzo,
    ccds character varying(16383) ENCODE lzo,
    ensp character varying(16383) ENCODE lzo,
    swissprot character varying(16383) ENCODE lzo,
    trembl character varying(16383) ENCODE lzo,
    uniparc character varying(16383) ENCODE lzo,
    uniprot_isoform character varying(16383) ENCODE lzo,
    refseq character varying(16383) ENCODE lzo,
    mane character varying(16383) ENCODE lzo,
    appris character varying(16383) ENCODE lzo,
    flags character varying(16383) ENCODE lzo,
    sift character varying(16383) ENCODE lzo,
    polyphen character varying(16383) ENCODE lzo,
    exon character varying(16383) ENCODE lzo,
    intron character varying(16383) ENCODE lzo,
    domains character varying(16383) ENCODE lzo,
    "1000g_af" double precision ENCODE raw,
    "1000g_afr_af" double precision ENCODE raw,
    "1000g_amr_af" double precision ENCODE raw,
    "1000g_eas_af" double precision ENCODE raw,
    "1000g_eur_af" double precision ENCODE raw,
    "1000g_sas_af" double precision ENCODE raw,
    esp_aa_af double precision ENCODE raw,
    esp_ea_af double precision ENCODE raw,
    gnomad_af double precision ENCODE raw,
    gnomad_afr_af double precision ENCODE raw,
    gnomad_amr_af double precision ENCODE raw,
    gnomad_asj_af double precision ENCODE raw,
    gnomad_eas_af double precision ENCODE raw,
    gnomad_fin_af double precision ENCODE raw,
    gnomad_nfe_af double precision ENCODE raw,
    gnomad_oth_af double precision ENCODE raw,
    gnomad_sas_af double precision ENCODE raw,
    max_af double precision ENCODE raw,
    max_af_pops character varying(16383) ENCODE lzo,
    gnomad_non_cancer_af double precision ENCODE raw,
    gnomad_non_cancer_afr_af double precision ENCODE raw,
    gnomad_non_cancer_ami_af double precision ENCODE raw,
    gnomad_non_cancer_amr_af double precision ENCODE raw,
    gnomad_non_cancer_asj_af double precision ENCODE raw,
    gnomad_non_cancer_eas_af double precision ENCODE raw,
    gnomad_non_cancer_fin_af double precision ENCODE raw,
    gnomad_non_cancer_mid_af double precision ENCODE raw,
    gnomad_non_cancer_nfe_af double precision ENCODE raw,
    gnomad_non_cancer_oth_af double precision ENCODE raw,
    gnomad_non_cancer_sas_af double precision ENCODE raw,
    gnomad_non_cancer_max_af_adj double precision ENCODE raw,
    gnomad_non_cancer_max_af_pops_adj character varying(16383) ENCODE lzo,
    clin_sig character varying(16383) ENCODE lzo,
    somatic character varying(16383) ENCODE lzo,
    pubmed character varying(16383) ENCODE lzo,
    transcription_factors character varying(16383) ENCODE lzo,
    motif_name character varying(16383) ENCODE lzo,
    motif_pos character varying(16383) ENCODE lzo,
    high_inf_pos character varying(16383) ENCODE lzo,
    motif_score_change character varying(16383) ENCODE lzo,
    mirna character varying(16383) ENCODE lzo,
    impact character varying(16383) ENCODE lzo,
    pick integer ENCODE az64,
    variant_class character varying(16383) ENCODE lzo,
    tsl integer ENCODE az64,
    hgvs_offset integer ENCODE az64,
    pheno character varying(16383) ENCODE lzo,
    gene_pheno integer ENCODE az64,
    context character varying(16383) ENCODE lzo,
    tumor_bam_uuid character varying(16383) ENCODE lzo,
    normal_bam_uuid character varying(16383) ENCODE lzo,
    case_id character varying(16383) ENCODE lzo,
    gdc_filter character varying(16383) ENCODE lzo,
    cosmic character varying(16383) ENCODE lzo,
    hotspot character varying(16383) ENCODE lzo,
    rna_support character varying(16383) ENCODE lzo,
    rna_depth character varying(16383) ENCODE lzo,
    rna_ref_count character varying(16383) ENCODE lzo,
    rna_alt_count character varying(16383) ENCODE lzo,
    callers character varying(16383) ENCODE lzo,
    submitter_id character varying(16383) ENCODE lzo,
    filename character varying(16383) ENCODE lzo
) ;

CREATE TABLE public.image_data_rs (
    access character varying(16383) ENCODE lzo,
    data_category character varying(16383) ENCODE lzo,
    data_format character varying(16383) ENCODE lzo,
    data_type character varying(16383) ENCODE lzo,
    experimental_strategy character varying(16383) ENCODE lzo,
    file_id character varying(16383) ENCODE lzo,
    file_name character varying(16383) ENCODE lzo,
    file_size integer ENCODE az64,
    md5sum character varying(16383) ENCODE lzo,
    s3_uri character varying(16383) ENCODE lzo,
    state character varying(16383) ENCODE lzo,
    submitter_id character varying(16383) ENCODE lzo
);

CREATE TABLE public.patient_reference_data_rs (
    row_id bigint ENCODE az64,
    patientid character varying(16383) ENCODE lzo,
    mutationid character varying(16383) ENCODE lzo,
    cnv_id character varying(16383) ENCODE lzo,
    image_id character varying(16383) ENCODE lzo
);

CREATE TABLE public.patients_rs (
    id character varying(16383) ENCODE lzo,
    resourcetype character varying(16383) ENCODE lzo,
    firstname character varying(16383) ENCODE lzo,
    lastname character varying(16383) ENCODE lzo,
    gender character varying(16383) ENCODE lzo,
    birthdate character varying(16383) ENCODE lzo,
    address_line1 character varying(16383) ENCODE lzo,
    city character varying(16383) ENCODE lzo,
    state character varying(16383) ENCODE lzo,
    postalcode bigint ENCODE az64,
    country character varying(16383) ENCODE lzo,
    diagnostics_concept double precision ENCODE raw,
    diagnostics_code character varying(16383) ENCODE lzo,
    diagnostic_desc character varying(16383) ENCODE lzo
);



copy gene_copy_number_rs 
from 's3://redshift-kb-bedrock-logdata/data/gene_copy_number_rs/'
iam_role default
format as csv;

copy gene_mutation_rs 
from 's3://redshift-kb-bedrock-logdata/data/gene_mutation_rs/'
iam_role default
format as csv;

copy image_data_rs 
from 's3://redshift-kb-bedrock-logdata/data/image_data_rs/'
iam_role default
format as csv;

copy patient_reference_data_rs
from 's3://redshift-kb-bedrock-logdata/data/patient_reference_data_rs/'
iam_role default
format as csv;

copy patients_rs 
from 's3://redshift-kb-bedrock-logdata/data/patients_rs/'
iam_role default
format as csv;