<commit_before>#!/bin/bash

# This generates big query count databases cdr that get put in cloudsql for workbench

set -e

export BQ_PROJECT=$1  # project
export BQ_DATASET=$2  # dataset
export OUTPUT_PROJECT=$3 # output project
export OUTPUT_DATASET=$4 # output dataset

# Check that bq_dataset exists and exit if not
datasets=$(bq --project_id="$BQ_PROJECT" ls --max_results=1000)
if [ -z "$datasets" ]
then
  echo "$BQ_PROJECT.$BQ_DATASET does not exist. Please specify a valid project and dataset."
  exit 1
fi
re=\\b$BQ_DATASET\\b
if [[ $datasets =~ $re ]]; then
  echo "$BQ_PROJECT.$BQ_DATASET exists. Good. Carrying on."
else
  echo "$BQ_PROJECT.$BQ_DATASET does not exist. Please specify a valid project and dataset."
  exit 1
fi

# Make dataset for cdr cloudsql tables
datasets=$(bq --project_id="$OUTPUT_PROJECT" ls --max_results=1000)
re=\\b$OUTPUT_DATASET\\b
if [[ $datasets =~ $re ]]; then
  echo "$OUTPUT_DATASET exists"
else
  echo "Creating $OUTPUT_DATASET"
  bq --project_id="$OUTPUT_PROJECT" mk "$OUTPUT_DATASET"
fi

#Check if tables to be copied over exists in bq project dataset
tables=$(bq --project_id="$BQ_PROJECT" --dataset="$BQ_DATASET" ls --max_results=1000)
cb_cri_table_check=\\bcb_criteria\\b
cb_cri_attr_table_check=\\bcb_criteria_attribute\\b
cb_cri_rel_table_check=\\bcb_criteria_relationship\\b
cb_cri_anc_table_check=\\bcb_criteria_ancestor\\b

# Create bq tables we have json schema for
schema_path=generate-cdr/bq-schemas
create_tables=(cb_survey_attribute cb_survey_version cb_criteria cb_criteria_attribute cb_criteria_relationship cb_criteria_ancestor ds_linking ds_data_dictionary domain_card survey_module cb_person cb_data_filter cb_criteria_menu)

for t in "${create_tables[@]}"
do
    bq --project_id="$OUTPUT_PROJECT" rm -f "$OUTPUT_DATASET.$t"
    bq --quiet --project_id="$OUTPUT_PROJECT" mk --schema="$schema_path/$t.json" "$OUTPUT_DATASET.$t"
done

# Populate ds_data_dictionary
#######################
# ds_data_dictionary #
#######################
echo "Inserting ds_data_dictionary"
bq --quiet --project_id="$BQ_PROJECT" query --nouse_legacy_sql \
"INSERT INTO \`$OUTPUT_PROJECT.$OUTPUT_DATASET.ds_data_dictionary\`
(id,field_name,relevant_omop_table,description,field_type,omop_cdm_standard_or_custom_field,data_provenance,source_ppi_module,domain)
SELECT id,field_name,relevant_omop_table,description,field_type,omop_cdm_standard_or_custom_field,data_provenance,source_ppi_module,domain
FROM \`$BQ_PROJECT.$BQ_DATASET.ds_data_dictionary\`"

# Populate cb_criteria_menu
#######################
#  cb_criteria_menu   #
#######################
echo "Inserting cb_criteria_menu"
bq --quiet --project_id="$BQ_PROJECT" query --nouse_legacy_sql \
"INSERT INTO \`$OUTPUT_PROJECT.$OUTPUT_DATASET.cb_criteria_menu\`
(id,parent_id,category,domain_id,type,name,is_group,sort_order)
SELECT id,parent_id,category,domain_id,type,name,is_group,sort_order
FROM \`$BQ_PROJECT.$BQ_DATASET.cb_criteria_menu\`"

# Populate cb_survey_attribute
#######################
# cb_survey_attribute #
#######################
echo "Inserting cb_survey_attribute"
bq --quiet --project_id="$BQ_PROJECT" query --nouse_legacy_sql \
"INSERT INTO \`$OUTPUT_PROJECT.$OUTPUT_DATASET.cb_survey_attribute\`
(id,question_concept_id,answer_concept_id,survey_version_concept_id,item_count)
SELECT id,question_concept_id,answer_concept_id,survey_version_concept_id,item_count
FROM \`$BQ_PROJECT.$BQ_DATASET.cb_survey_attribute\`"

# Populate cb_survey_version
#####################
# cb_survey_version #
#####################
echo "Inserting cb_survey_version"
bq --quiet --project_id="$BQ_PROJECT" query --nouse_legacy_sql \
"INSERT INTO \`$OUTPUT_PROJECT.$OUTPUT_DATASET.cb_survey_version\`
(survey_version_concept_id,survey_concept_id,display_name,display_order)
SELECT survey_version_concept_id,survey_concept_id,display_name,display_order
FROM \`$BQ_PROJECT.$BQ_DATASET.cb_survey_version\`"

# Populate domain_card
###############
# domain_card #
###############
echo "Inserting domain_card"
bq --quiet --project_id="$BQ_PROJECT" query --nouse_legacy_sql \
"INSERT INTO \`$OUTPUT_PROJECT.$OUTPUT_DATASET.domain_card\`
(id,category,domain,name,description,concept_count,participant_count,is_standard,sort_order)
VALUES
(1,'Domains',0,'Conditions','Conditions',0,0,1,1),
(2,'Domains',3,'Drug Exposures','Drug Exposures',0,0,1,2),
(3,'Domains',4,'Labs and Measurements','Labs and Measurements',0,0,1,3),
(4,'Domains',5,'Observations','Observations',0,0,1,4),
(5,'Domains',6,'Procedures','Procedures',0,0,1,5),
(6,'Program Physical Measurements',19,'Physical Measurements','Participants have the option to provide a standard set of physical measurements as part of the enrollment process(program physical measurements)',0,0,0,8)"

# Populate survey_module
#################
# survey_module #
#################
echo "Inserting survey_module"
bq --quiet --project_id="$BQ_PROJECT" query --nouse_legacy_sql \
"INSERT INTO \`$OUTPUT_PROJECT.$OUTPUT_DATASET.survey_module\`
(concept_id,name,description,question_count,participant_count,order_number)
VALUES
(1585855,'','Survey includes information on participant smoking, alcohol and recreational drug use.',0,0,3),
(1585710,'','Survey provides information about how participants report levels of individual health.',0,0,2),
(1586134,'','Survey includes participant demographic information.',0,0,1),
(43529712,'','This survey includes information about past medical history, including medical conditions and approximate age of diagnosis.',0,0,4),
(43528895,'','Survey includes information about a participants access to and use of health care.',0,0,5),
(43528698,'','Survey includes information about the medical history of a participants immediate biological family members.',0,0,6),
(1333342,'','COVID-19 Participant Experience (COPE) Survey.',0,0,7)"

echo "Updating survey names on survey_module"
bq --quiet --project_id="$BQ_PROJECT" query --nouse_legacy_sql \
"UPDATE \`$OUTPUT_PROJECT.$OUTPUT_DATASET.survey_module\` sm
SET sm.name = c.concept_name
FROM (
  SELECT concept_name, concept_id
  FROM \`$BQ_PROJECT.$BQ_DATASET.concept\`
  WHERE domain_id = 'Observation'
  AND vocabulary_id = 'PPI'
  AND concept_class_id = 'Module'
) c
WHERE sm.concept_id = c.concept_id"

# Populate cb_person table
echo "Inserting cb_person"
bq --quiet --project_id="$BQ_PROJECT" query --nouse_legacy_sql \
"INSERT INTO \`$OUTPUT_PROJECT.$OUTPUT_DATASET.cb_person\`
(person_id, dob, age_at_consent, age_at_cdr, is_deceased)
SELECT person_id, dob, age_at_consent, age_at_cdr, is_deceased
FROM \`$BQ_PROJECT.$BQ_DATASET.cb_search_person\`"

# Populate cb_data_filter table
echo "Inserting cb_data_filter"
bq --quiet --project_id="$BQ_PROJECT" query --nouse_legacy_sql \
"INSERT INTO \`$OUTPUT_PROJECT.$OUTPUT_DATASET.cb_data_filter\`<commit_msg>RW-8060 adding domain card for new surveys (#6506)<commit_after>#!/bin/bash

# This generates big query count databases cdr that get put in cloudsql for workbench

set -e

export BQ_PROJECT=$1  # project
export BQ_DATASET=$2  # dataset
export OUTPUT_PROJECT=$3 # output project
export OUTPUT_DATASET=$4 # output dataset

# Check that bq_dataset exists and exit if not
datasets=$(bq --project_id="$BQ_PROJECT" ls --max_results=1000)
if [ -z "$datasets" ]
then
  echo "$BQ_PROJECT.$BQ_DATASET does not exist. Please specify a valid project and dataset."
  exit 1
fi
re=\\b$BQ_DATASET\\b
if [[ $datasets =~ $re ]]; then
  echo "$BQ_PROJECT.$BQ_DATASET exists. Good. Carrying on."
else
  echo "$BQ_PROJECT.$BQ_DATASET does not exist. Please specify a valid project and dataset."
  exit 1
fi

# Make dataset for cdr cloudsql tables
datasets=$(bq --project_id="$OUTPUT_PROJECT" ls --max_results=1000)
re=\\b$OUTPUT_DATASET\\b
if [[ $datasets =~ $re ]]; then
  echo "$OUTPUT_DATASET exists"
else
  echo "Creating $OUTPUT_DATASET"
  bq --project_id="$OUTPUT_PROJECT" mk "$OUTPUT_DATASET"
fi

#Check if tables to be copied over exists in bq project dataset
tables=$(bq --project_id="$BQ_PROJECT" --dataset="$BQ_DATASET" ls --max_results=1000)
cb_cri_table_check=\\bcb_criteria\\b
cb_cri_attr_table_check=\\bcb_criteria_attribute\\b
cb_cri_rel_table_check=\\bcb_criteria_relationship\\b
cb_cri_anc_table_check=\\bcb_criteria_ancestor\\b

# Create bq tables we have json schema for
schema_path=generate-cdr/bq-schemas
create_tables=(cb_survey_attribute cb_survey_version cb_criteria cb_criteria_attribute cb_criteria_relationship cb_criteria_ancestor ds_linking ds_data_dictionary domain_card survey_module cb_person cb_data_filter cb_criteria_menu)

for t in "${create_tables[@]}"
do
    bq --project_id="$OUTPUT_PROJECT" rm -f "$OUTPUT_DATASET.$t"
    bq --quiet --project_id="$OUTPUT_PROJECT" mk --schema="$schema_path/$t.json" "$OUTPUT_DATASET.$t"
done

# Populate ds_data_dictionary
#######################
# ds_data_dictionary #
#######################
echo "Inserting ds_data_dictionary"
bq --quiet --project_id="$BQ_PROJECT" query --nouse_legacy_sql \
"INSERT INTO \`$OUTPUT_PROJECT.$OUTPUT_DATASET.ds_data_dictionary\`
(id,field_name,relevant_omop_table,description,field_type,omop_cdm_standard_or_custom_field,data_provenance,source_ppi_module,domain)
SELECT id,field_name,relevant_omop_table,description,field_type,omop_cdm_standard_or_custom_field,data_provenance,source_ppi_module,domain
FROM \`$BQ_PROJECT.$BQ_DATASET.ds_data_dictionary\`"

# Populate cb_criteria_menu
#######################
#  cb_criteria_menu   #
#######################
echo "Inserting cb_criteria_menu"
bq --quiet --project_id="$BQ_PROJECT" query --nouse_legacy_sql \
"INSERT INTO \`$OUTPUT_PROJECT.$OUTPUT_DATASET.cb_criteria_menu\`
(id,parent_id,category,domain_id,type,name,is_group,sort_order)
SELECT id,parent_id,category,domain_id,type,name,is_group,sort_order
FROM \`$BQ_PROJECT.$BQ_DATASET.cb_criteria_menu\`"

# Populate cb_survey_attribute
#######################
# cb_survey_attribute #
#######################
echo "Inserting cb_survey_attribute"
bq --quiet --project_id="$BQ_PROJECT" query --nouse_legacy_sql \
"INSERT INTO \`$OUTPUT_PROJECT.$OUTPUT_DATASET.cb_survey_attribute\`
(id,question_concept_id,answer_concept_id,survey_version_concept_id,item_count)
SELECT id,question_concept_id,answer_concept_id,survey_version_concept_id,item_count
FROM \`$BQ_PROJECT.$BQ_DATASET.cb_survey_attribute\`"

# Populate cb_survey_version
#####################
# cb_survey_version #
#####################
echo "Inserting cb_survey_version"
bq --quiet --project_id="$BQ_PROJECT" query --nouse_legacy_sql \
"INSERT INTO \`$OUTPUT_PROJECT.$OUTPUT_DATASET.cb_survey_version\`
(survey_version_concept_id,survey_concept_id,display_name,display_order)
SELECT survey_version_concept_id,survey_concept_id,display_name,display_order
FROM \`$BQ_PROJECT.$BQ_DATASET.cb_survey_version\`"

# Populate domain_card
###############
# domain_card #
###############
echo "Inserting domain_card"
bq --quiet --project_id="$BQ_PROJECT" query --nouse_legacy_sql \
"INSERT INTO \`$OUTPUT_PROJECT.$OUTPUT_DATASET.domain_card\`
(id,category,domain,name,description,concept_count,participant_count,is_standard,sort_order)
VALUES
(1,'Domains',0,'Conditions','Conditions',0,0,1,1),
(2,'Domains',3,'Drug Exposures','Drug Exposures',0,0,1,2),
(3,'Domains',4,'Labs and Measurements','Labs and Measurements',0,0,1,3),
(4,'Domains',5,'Observations','Observations',0,0,1,4),
(5,'Domains',6,'Procedures','Procedures',0,0,1,5),
(6,'Program Physical Measurements',19,'Physical Measurements','Participants have the option to provide a standard set of physical measurements as part of the enrollment process(program physical measurements)',0,0,0,8)"

# Populate survey_module
#################
# survey_module #
#################
echo "Inserting survey_module"
bq --quiet --project_id="$BQ_PROJECT" query --nouse_legacy_sql \
"INSERT INTO \`$OUTPUT_PROJECT.$OUTPUT_DATASET.survey_module\`
(concept_id,name,description,question_count,participant_count,order_number)
VALUES
(1585855,'','Survey includes information on participant smoking, alcohol and recreational drug use.',0,0,3),
(1585710,'','Survey provides information about how participants report levels of individual health.',0,0,2),
(1586134,'','Survey includes participant demographic information.',0,0,1),
(43529712,'','Survey includes information about past medical history, including medical conditions and approximate age of diagnosis.',0,0,4),
(43528895,'','Survey includes information about a participants access to and use of health care.',0,0,5),
(43528698,'','Survey includes information about the medical history of a participants immediate biological family members.',0,0,6),
(1333342,'','Survey includes information about the impact of COVID-19 on participant mental and physical health.',0,0,7)"

#  Getting count for SDOH Survey
query="select count(*) as count from \`$BQ_PROJECT.$BQ_DATASET.concept\`
where concept_id = 40192389"
sdohCount=$(bq --quiet --project_id="$BQ_PROJECT" query --nouse_legacy_sql "$query" | tr -dc '0-9')
if [[ "$sdohCount" > 0 ]]; then
  # Insert row for SDOH Survey
  bq --quiet --project_id="$BQ_PROJECT" query --nouse_legacy_sql \
  "INSERT INTO \`$OUTPUT_PROJECT.$OUTPUT_DATASET.survey_module\`
  (concept_id,name,description,question_count,participant_count,order_number)
  VALUES
  (40192389,'','Survey includes information to help better understand the connection between social environmental factors and overall health.',0,0,8)"
fi

#  Getting count for Cope Minute Survey
query="select count(*) as count from \`$BQ_PROJECT.$BQ_DATASET.concept\`
where concept_id = 765936"
minuteCount=$(bq --quiet --project_id="$BQ_PROJECT" query --nouse_legacy_sql "$query" | tr -dc '0-9')
if [[ "$minuteCount" > 0 ]]; then
  # Insert row for Cope Minute Survey
  bq --quiet --project_id="$BQ_PROJECT" query --nouse_legacy_sql \
  "INSERT INTO \`$OUTPUT_PROJECT.$OUTPUT_DATASET.survey_module\`
  (concept_id,name,description,question_count,participant_count,order_number)
  VALUES
  (765936,'','Survey includes information about participant COVID-19 Vaccinations.',0,0,9)"
fi

echo "Updating survey names on survey_module"
bq --quiet --project_id="$BQ_PROJECT" query --nouse_legacy_sql \
"UPDATE \`$OUTPUT_PROJECT.$OUTPUT_DATASET.survey_module\` sm
SET sm.name = c.concept_name
FROM (
  SELECT concept_name, concept_id
  FROM \`$BQ_PROJECT.$BQ_DATASET.concept\`
  WHERE domain_id = 'Observation'
  AND vocabulary_id = 'PPI'
  AND concept_class_id = 'Module'
) c
WHERE sm.concept_id = c.concept_id"

# Populate cb_person table
echo "Inserting cb_person"
bq --quiet --project_id="$BQ_PROJECT" query --nouse_legacy_sql \
"INSERT INTO \`$OUTPUT_PROJECT.$OUTPUT_DATASET.cb_person\`
(person_id, dob, age_at_consent, age_at_cdr, is_deceased)
SELECT person_id, dob, age_at_consent, age_at_cdr, is_deceased
FROM \`$BQ_PROJECT.$BQ_DATASET.cb_search_person\`"

# Populate cb_data_filter table
echo "Inserting cb_data_filter"
bq --quiet --project_id="$BQ_PROJECT" query --nouse_legacy_sql \
"INSERT INTO \`$OUTPUT_PROJECT.$OUTPUT_DATASET.cb_data_filter\`
{'fraction_non_alphanumeric': 0.14901724829522664, 'fraction_numerical': 0.020724695814948523, 'mean_word_length': 7.269209508015479, 'pattern_counts': {'":': 0, '<': 3, '<?xml version=': 0, '>': 5, 'https://': 0, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 0, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'dir': 'git-commits-cleaned', 'id': '1684073', 'n_tokens_mistral': 5414, 'n_tokens_neox': 5203, 'n_words': 1065}