10:24:21,869 |-INFO in ch.qos.logback.classic.LoggerContext[default] - Could NOT find resource [logback-test.xml] 10:24:21,869 |-INFO in ch.qos.logback.classic.LoggerContext[default] - Could NOT find resource [logback.groovy] 10:24:21,870 |-INFO in ch.qos.logback.classic.LoggerContext[default] - Found resource [logback.xml] at [file:/D:/_YES_2023-05-28/workspace/fhir-to-omop/target/classes/logback.xml] 10:24:21,870 |-WARN in ch.qos.logback.classic.LoggerContext[default] - Resource [logback.xml] occurs multiple times on the classpath. 10:24:21,870 |-WARN in ch.qos.logback.classic.LoggerContext[default] - Resource [logback.xml] occurs at [jar:file:/C:/Users/gresh/.m2/repository/org/nachc/cad/tools/thread-tool/1.1.011/thread-tool-1.1.011.jar!/logback.xml] 10:24:21,870 |-WARN in ch.qos.logback.classic.LoggerContext[default] - Resource [logback.xml] occurs at [file:/D:/_YES_2023-05-28/workspace/fhir-to-omop/target/classes/logback.xml] 10:24:21,982 |-INFO in ch.qos.logback.classic.joran.action.ConfigurationAction - debug attribute not set 10:24:21,984 |-INFO in ch.qos.logback.core.joran.action.AppenderAction - About to instantiate appender of type [ch.qos.logback.core.ConsoleAppender] 10:24:21,986 |-INFO in ch.qos.logback.core.joran.action.AppenderAction - Naming appender as [STDOUT] 10:24:21,990 |-INFO in ch.qos.logback.core.joran.action.NestedComplexPropertyIA - Assuming default type [ch.qos.logback.classic.encoder.PatternLayoutEncoder] for [encoder] property 10:24:22,031 |-INFO in ch.qos.logback.classic.joran.action.LoggerAction - Setting level of logger [ch.qos.logback] to ERROR 10:24:22,031 |-INFO in ch.qos.logback.classic.joran.action.LoggerAction - Setting level of logger [ca.uhn.fhir] to ERROR 10:24:22,031 |-INFO in ch.qos.logback.classic.joran.action.RootLoggerAction - Setting level of ROOT logger to INFO 10:24:22,031 |-INFO in ch.qos.logback.core.joran.action.AppenderRefAction - Attaching appender named [STDOUT] to Logger[ROOT] 10:24:22,031 |-INFO in ch.qos.logback.classic.joran.action.ConfigurationAction - End of configuration. 10:24:22,033 |-INFO in ch.qos.logback.classic.joran.JoranConfigurator@385c9627 - Registering current configuration as safe fallback point 2023-06-01 10:24:22,037 10:24:22.037 [main] INFO (DatabricksProperties.java:25) - File: C:\_WORKSPACES\nachc\_CURRENT\KEYS\application-auth\fhir-to-omop\build\databricks\databricks-demo-auth.properties 2023-06-01 10:24:22,039 10:24:22.039 [main] INFO (DatabricksProperties.java:26) - Exists: true 2023-06-01 10:24:22,039 10:24:22.039 [main] INFO (DatabricksConnectionFactory.java:17) - URL: jdbc:spark://nachc-databricks.cloud.databricks.com:443/default;transportMode=http;ssl=1;httpPath=sql/protocolv1/o/3956472157536757/0123-223459-leafy532;AuthMech=3;UID=token;PWD= WARNING: sun.reflect.Reflection.getCallerClass is not supported. This will impact performance. getEngineName() called getEngineName() called WARNING: An illegal reflective access operation has occurred WARNING: Illegal reflective access by com.simba.spark.jdbc42.internal.io.netty.util.internal.ReflectionUtil (file:/D:/_YES_2023-05-28/workspace/fhir-to-omop/lib/SimbaSparkJDBC42-2.6.16.1020/SparkJDBC42.jar) to constructor java.nio.DirectByteBuffer(long,int) WARNING: Please consider reporting this to the maintainers of com.simba.spark.jdbc42.internal.io.netty.util.internal.ReflectionUtil WARNING: Use --illegal-access=warn to enable warnings of further illegal reflective access operations WARNING: All illegal access operations will be denied in a future release 2023-06-01 10:24:24,013 10:24:24.013 [main] INFO (DatabricksConnectionFactory.java:20) - Got connection 2023-06-01 10:24:24,013 10:24:24.013 [main] INFO (DatabricksDatabase.java:41) - * * * CHECKING DATABRICKS CONNECTION * * * 2023-06-01 10:24:24,162 10:24:24.162 [main] INFO (DatabricksDatabase.java:44) - Connection is good. 2023-06-01 10:24:24,162 10:24:24.162 [main] INFO (A05_CreateAchillesDatabaseObjectsDatabricks.java:51) - ------------------------------- 2023-06-01 10:24:24,162 10:24:24.162 [main] INFO (A05_CreateAchillesDatabaseObjectsDatabricks.java:52) - START: Creating Achilles Databases (vocab, temp, and results): demo_cdm, demo_cdm_ach_tmp, demo_cdm_ach_res 2023-06-01 10:24:24,162 10:24:24.162 [main] INFO (A05_CreateAchillesDatabaseObjectsDatabricks.java:53) - ------------------------------- 2023-06-01 10:24:24,163 10:24:24.163 [main] INFO (A05_CreateAchillesDatabaseObjectsDatabricks.java:55) - Getting ddl file... 2023-06-01 10:24:24,165 10:24:24.165 [main] INFO (A05_CreateAchillesDatabaseObjectsDatabricks.java:65) - Creating database objects... /** * * This file was created by deploying an instance of Atlas and then running this in a browser: * http://localhost:8080/WebAPI/ddl/results?dialect=spark&schema=demo_cdm_ach_res&vocabSchema=demo_cdm&tempSchema=demo_cdm_ach_tmp&initConceptHierarchy=true * */ CREATE TABLE IF NOT EXISTS demo_cdm_ach_res.cohort USING DELTA AS SELECT CAST(NULL AS int) AS cohort_definition_id, CAST(NULL AS bigint) AS subject_id, CAST(NULL AS date) AS cohort_start_date, CAST(NULL AS date) AS cohort_end_date WHERE 1 = 0 CREATE TABLE IF NOT EXISTS demo_cdm_ach_res.cohort_censor_stats USING DELTA AS SELECT CAST(NULL AS int) AS cohort_definition_id, CAST(NULL AS bigint) AS lost_count WHERE 1 = 0 CREATE TABLE IF NOT EXISTS demo_cdm_ach_res.cohort_inclusion USING DELTA AS SELECT CAST(NULL AS int) AS cohort_definition_id, CAST(NULL AS int) AS design_hash, CAST(NULL AS int) AS rule_sequence, CAST(NULL AS STRING) AS name, CAST(NULL AS STRING) AS description WHERE 1 = 0 CREATE TABLE IF NOT EXISTS demo_cdm_ach_res.cohort_inclusion_result USING DELTA AS SELECT CAST(NULL AS int) AS cohort_definition_id, CAST(NULL AS int) AS mode_id, CAST(NULL AS bigint) AS inclusion_rule_mask, CAST(NULL AS bigint) AS person_count WHERE 1 = 0 CREATE TABLE IF NOT EXISTS demo_cdm_ach_res.cohort_inclusion_stats USING DELTA AS SELECT CAST(NULL AS int) AS cohort_definition_id, CAST(NULL AS int) AS rule_sequence, CAST(NULL AS int) AS mode_id, CAST(NULL AS bigint) AS person_count, CAST(NULL AS bigint) AS gain_count, CAST(NULL AS bigint) AS person_total WHERE 1 = 0 CREATE TABLE IF NOT EXISTS demo_cdm_ach_res.cohort_summary_stats USING DELTA AS SELECT CAST(NULL AS int) AS cohort_definition_id, CAST(NULL AS int) AS mode_id, CAST(NULL AS bigint) AS base_count, CAST(NULL AS bigint) AS final_count WHERE 1 = 0 CREATE TABLE IF NOT EXISTS demo_cdm_ach_res.cohort_cache USING DELTA AS SELECT CAST(NULL AS int) AS design_hash, CAST(NULL AS bigint) AS subject_id, CAST(NULL AS date) AS cohort_start_date, CAST(NULL AS date) AS cohort_end_date WHERE 1 = 0 CREATE TABLE IF NOT EXISTS demo_cdm_ach_res.cohort_censor_stats_cache USING DELTA AS SELECT CAST(NULL AS int) AS design_hash, CAST(NULL AS bigint) AS lost_count WHERE 1 = 0 CREATE TABLE IF NOT EXISTS demo_cdm_ach_res.cohort_inclusion_result_cache USING DELTA AS SELECT CAST(NULL AS int) AS design_hash, CAST(NULL AS int) AS mode_id, CAST(NULL AS bigint) AS inclusion_rule_mask, CAST(NULL AS bigint) AS person_count WHERE 1 = 0 CREATE TABLE IF NOT EXISTS demo_cdm_ach_res.cohort_inclusion_stats_cache USING DELTA AS SELECT CAST(NULL AS int) AS design_hash, CAST(NULL AS int) AS rule_sequence, CAST(NULL AS int) AS mode_id, CAST(NULL AS bigint) AS person_count, CAST(NULL AS bigint) AS gain_count, CAST(NULL AS bigint) AS person_total WHERE 1 = 0 CREATE TABLE IF NOT EXISTS demo_cdm_ach_res.cohort_summary_stats_cache USING DELTA AS SELECT CAST(NULL AS int) AS design_hash, CAST(NULL AS int) AS mode_id, CAST(NULL AS bigint) AS base_count, CAST(NULL AS bigint) AS final_count WHERE 1 = 0 CREATE TABLE IF NOT EXISTS demo_cdm_ach_res.feas_study_inclusion_stats USING DELTA AS SELECT CAST(NULL AS int) AS study_id, CAST(NULL AS int) AS rule_sequence, CAST(NULL AS STRING) AS name, CAST(NULL AS bigint) AS person_count, CAST(NULL AS bigint) AS gain_count, CAST(NULL AS bigint) AS person_total WHERE 1 = 0 CREATE TABLE IF NOT EXISTS demo_cdm_ach_res.feas_study_index_stats USING DELTA AS SELECT CAST(NULL AS int) AS study_id, CAST(NULL AS bigint) AS person_count, CAST(NULL AS bigint) AS match_count WHERE 1 = 0 CREATE TABLE IF NOT EXISTS demo_cdm_ach_res.feas_study_result USING DELTA AS SELECT CAST(NULL AS int) AS study_id, CAST(NULL AS bigint) AS inclusion_rule_mask, CAST(NULL AS bigint) AS person_count WHERE 1 = 0 CREATE TABLE IF NOT EXISTS demo_cdm_ach_res.heracles_analysis USING DELTA AS SELECT CAST(NULL AS int) AS analysis_id, CAST(NULL AS STRING) AS analysis_name, CAST(NULL AS STRING) AS stratum_1_name, CAST(NULL AS STRING) AS stratum_2_name, CAST(NULL AS STRING) AS stratum_3_name, CAST(NULL AS STRING) AS stratum_4_name, CAST(NULL AS STRING) AS stratum_5_name, CAST(NULL AS STRING) AS analysis_type WHERE 1 = 0 CREATE TABLE IF NOT EXISTS demo_cdm_ach_res.HERACLES_HEEL_results USING DELTA AS SELECT CAST(NULL AS int) AS cohort_definition_id, CAST(NULL AS int) AS analysis_id, CAST(NULL AS STRING) AS heracles_heel_warning WHERE 1 = 0 --HINT PARTITION(cohort_definition_id int) CREATE TABLE IF NOT EXISTS demo_cdm_ach_res.heracles_results USING DELTA AS SELECT CAST(NULL AS int) AS cohort_definition_id, CAST(NULL AS int) AS analysis_id, CAST(NULL AS STRING) AS stratum_1, CAST(NULL AS STRING) AS stratum_2, CAST(NULL AS STRING) AS stratum_3, CAST(NULL AS STRING) AS stratum_4, CAST(NULL AS STRING) AS stratum_5, CAST(NULL AS bigint) AS count_value, CAST(NULL AS TIMESTAMP) AS last_update_time WHERE 1 = 0 --HINT PARTITION(cohort_definition_id int) CREATE TABLE IF NOT EXISTS demo_cdm_ach_res.heracles_results_dist USING DELTA AS SELECT CAST(NULL AS int) AS cohort_definition_id, CAST(NULL AS int) AS analysis_id, CAST(NULL AS STRING) AS stratum_1, CAST(NULL AS STRING) AS stratum_2, CAST(NULL AS STRING) AS stratum_3, CAST(NULL AS STRING) AS stratum_4, CAST(NULL AS STRING) AS stratum_5, CAST(NULL AS bigint) AS count_value, CAST(NULL AS float) AS min_value, CAST(NULL AS float) AS max_value, CAST(NULL AS float) AS avg_value, CAST(NULL AS float) AS stdev_value, CAST(NULL AS float) AS median_value, CAST(NULL AS float) AS p10_value, CAST(NULL AS float) AS p25_value, CAST(NULL AS float) AS p75_value, CAST(NULL AS float) AS p90_value, CAST(NULL AS TIMESTAMP) AS last_update_time WHERE 1 = 0 CREATE TABLE IF NOT EXISTS demo_cdm_ach_res.heracles_periods USING DELTA AS SELECT CAST(NULL AS int) AS period_id, CAST(NULL AS int) AS period_order, CAST(NULL AS STRING) AS period_name, CAST(NULL AS STRING) AS period_type, CAST(NULL AS date) AS period_start_date, CAST(NULL AS date) AS period_end_date WHERE 1 = 0 CREATE TABLE IF NOT EXISTS demo_cdm_ach_res.cohort_sample_element USING DELTA AS SELECT CAST(NULL AS int) AS cohort_sample_id, CAST(NULL AS int) AS rank_value, CAST(NULL AS bigint) AS person_id, CAST(NULL AS int) AS age, CAST(NULL AS int) AS gender_concept_id WHERE 1 = 0 CREATE TABLE IF NOT EXISTS demo_cdm_ach_res.ir_analysis_dist USING DELTA AS SELECT CAST(NULL AS int) AS analysis_id, CAST(NULL AS int) AS target_id, CAST(NULL AS int) AS outcome_id, CAST(NULL AS int) AS strata_sequence, CAST(NULL AS int) AS dist_type, CAST(NULL AS bigint) AS total, CAST(NULL AS float) AS avg_value, CAST(NULL AS float) AS std_dev, CAST(NULL AS int) AS min_value, CAST(NULL AS int) AS p10_value, CAST(NULL AS int) AS p25_value, CAST(NULL AS int) AS median_value, CAST(NULL AS int) AS p75_value, CAST(NULL AS int) AS p90_value, CAST(NULL AS int) AS max_value WHERE 1 = 0 CREATE TABLE IF NOT EXISTS demo_cdm_ach_res.ir_analysis_result USING DELTA AS SELECT CAST(NULL AS int) AS analysis_id, CAST(NULL AS int) AS target_id, CAST(NULL AS int) AS outcome_id, CAST(NULL AS bigint) AS strata_mask, CAST(NULL AS bigint) AS person_count, CAST(NULL AS bigint) AS time_at_risk, CAST(NULL AS bigint) AS cases WHERE 1 = 0 CREATE TABLE IF NOT EXISTS demo_cdm_ach_res.ir_analysis_strata_stats USING DELTA AS SELECT CAST(NULL AS int) AS analysis_id, CAST(NULL AS int) AS target_id, CAST(NULL AS int) AS outcome_id, CAST(NULL AS int) AS strata_sequence, CAST(NULL AS bigint) AS person_count, CAST(NULL AS bigint) AS time_at_risk, CAST(NULL AS bigint) AS cases WHERE 1 = 0 CREATE TABLE IF NOT EXISTS demo_cdm_ach_res.ir_strata USING DELTA AS SELECT CAST(NULL AS int) AS analysis_id, CAST(NULL AS int) AS strata_sequence, CAST(NULL AS STRING) AS name, CAST(NULL AS STRING) AS description WHERE 1 = 0 CREATE TABLE IF NOT EXISTS demo_cdm_ach_res.cc_results USING DELTA AS SELECT CAST(NULL AS STRING) AS type, CAST(NULL AS STRING) AS fa_type, CAST(NULL AS bigint) AS cc_generation_id, CAST(NULL AS integer) AS analysis_id, CAST(NULL AS STRING) AS analysis_name, CAST(NULL AS bigint) AS covariate_id, CAST(NULL AS STRING) AS covariate_name, CAST(NULL AS bigint) AS strata_id, CAST(NULL AS STRING) AS strata_name, CAST(NULL AS STRING) AS time_window, CAST(NULL AS integer) AS concept_id, CAST(NULL AS bigint) AS count_value, CAST(NULL AS double) AS avg_value, CAST(NULL AS double) AS stdev_value, CAST(NULL AS double) AS min_value, CAST(NULL AS double) AS p10_value, CAST(NULL AS double) AS p25_value, CAST(NULL AS double) AS median_value, CAST(NULL AS double) AS p75_value, CAST(NULL AS double) AS p90_value, CAST(NULL AS double) AS max_value, CAST(NULL AS bigint) AS cohort_definition_id, CAST(NULL AS integer) AS aggregate_id, CAST(NULL AS STRING) AS aggregate_name, CAST(NULL AS integer) AS missing_means_zero WHERE 1 = 0 CREATE TABLE IF NOT EXISTS demo_cdm_ach_res.pathway_analysis_codes USING DELTA AS SELECT CAST(NULL AS bigint) AS pathway_analysis_generation_id, CAST(NULL AS bigint) AS code, CAST(NULL AS STRING) AS name, CAST(NULL AS int) AS is_combo WHERE 1 = 0 CREATE TABLE IF NOT EXISTS demo_cdm_ach_res.pathway_analysis_events USING DELTA AS SELECT CAST(NULL AS bigint) AS pathway_analysis_generation_id, CAST(NULL AS integer) AS target_cohort_id, CAST(NULL AS bigint) AS combo_id, CAST(NULL AS bigint) AS subject_id, CAST(NULL AS integer) AS ordinal, CAST(NULL AS TIMESTAMP) AS cohort_start_date, CAST(NULL AS TIMESTAMP) AS cohort_end_date WHERE 1 = 0 CREATE TABLE IF NOT EXISTS demo_cdm_ach_res.pathway_analysis_paths USING DELTA AS SELECT CAST(NULL AS bigint) AS pathway_analysis_generation_id, CAST(NULL AS integer) AS target_cohort_id, CAST(NULL AS bigint) AS step_1, CAST(NULL AS bigint) AS step_2, CAST(NULL AS bigint) AS step_3, CAST(NULL AS bigint) AS step_4, CAST(NULL AS bigint) AS step_5, CAST(NULL AS bigint) AS step_6, CAST(NULL AS bigint) AS step_7, CAST(NULL AS bigint) AS step_8, CAST(NULL AS bigint) AS step_9, CAST(NULL AS bigint) AS step_10, CAST(NULL AS bigint) AS count_value WHERE 1 = 0 CREATE TABLE IF NOT EXISTS demo_cdm_ach_res.pathway_analysis_stats USING DELTA AS SELECT CAST(NULL AS bigint) AS pathway_analysis_generation_id, CAST(NULL AS integer) AS target_cohort_id, CAST(NULL AS bigint) AS target_cohort_count, CAST(NULL AS bigint) AS pathways_count WHERE 1 = 0 CREATE TABLE IF NOT EXISTS demo_cdm_ach_res.concept_hierarchy USING DELTA AS SELECT CAST(NULL AS int) AS concept_id, CAST(NULL AS STRING) AS concept_name, CAST(NULL AS STRING) AS treemap, CAST(NULL AS STRING) AS concept_hierarchy_type, CAST(NULL AS STRING) AS level1_concept_name, CAST(NULL AS STRING) AS level2_concept_name, CAST(NULL AS STRING) AS level3_concept_name, CAST(NULL AS STRING) AS level4_concept_name WHERE 1 = 0 TRUNCATE TABLE demo_cdm_ach_res.concept_hierarchy WITH insertion_temp AS ( (SELECT snomed.concept_id, snomed.concept_name AS concept_name, CAST('Condition' AS STRING) AS treemap, CAST(NULL AS STRING) as concept_hierarchy_type, pt_to_hlt.pt_concept_name as level1_concept_name, hlt_to_hlgt.hlt_concept_name as level2_concept_name, hlgt_to_soc.hlgt_concept_name as level3_concept_name, soc.concept_name AS level4_concept_name FROM ( SELECT concept_id, concept_name FROM demo_cdm.concept WHERE domain_id = 'Condition' ) snomed LEFT JOIN ( SELECT c1.concept_id AS snomed_concept_id, max(c2.concept_id) AS pt_concept_id FROM demo_cdm.concept c1 INNER JOIN demo_cdm.concept_ancestor ca1 ON c1.concept_id = ca1.descendant_concept_id AND c1.domain_id = 'Condition' AND ca1.min_levels_of_separation = 1 INNER JOIN demo_cdm.concept c2 ON ca1.ancestor_concept_id = c2.concept_id AND c2.vocabulary_id = 'MedDRA' GROUP BY c1.concept_id ) snomed_to_pt ON snomed.concept_id = snomed_to_pt.snomed_concept_id LEFT JOIN ( SELECT c1.concept_id AS pt_concept_id, c1.concept_name AS pt_concept_name, max(c2.concept_id) AS hlt_concept_id FROM demo_cdm.concept c1 INNER JOIN demo_cdm.concept_ancestor ca1 ON c1.concept_id = ca1.descendant_concept_id AND c1.vocabulary_id = 'MedDRA' AND ca1.min_levels_of_separation = 1 INNER JOIN demo_cdm.concept c2 ON ca1.ancestor_concept_id = c2.concept_id AND c2.vocabulary_id = 'MedDRA' GROUP BY c1.concept_id, c1.concept_name ) pt_to_hlt ON snomed_to_pt.pt_concept_id = pt_to_hlt.pt_concept_id LEFT JOIN ( SELECT c1.concept_id AS hlt_concept_id, c1.concept_name AS hlt_concept_name, max(c2.concept_id) AS hlgt_concept_id FROM demo_cdm.concept c1 INNER JOIN demo_cdm.concept_ancestor ca1 ON c1.concept_id = ca1.descendant_concept_id AND c1.vocabulary_id = 'MedDRA' AND ca1.min_levels_of_separation = 1 INNER JOIN demo_cdm.concept c2 ON ca1.ancestor_concept_id = c2.concept_id AND c2.vocabulary_id = 'MedDRA' GROUP BY c1.concept_id, c1.concept_name ) hlt_to_hlgt ON pt_to_hlt.hlt_concept_id = hlt_to_hlgt.hlt_concept_id LEFT JOIN ( SELECT c1.concept_id AS hlgt_concept_id, c1.concept_name AS hlgt_concept_name, max(c2.concept_id) AS soc_concept_id FROM demo_cdm.concept c1 INNER JOIN demo_cdm.concept_ancestor ca1 ON c1.concept_id = ca1.descendant_concept_id AND c1.vocabulary_id = 'MedDRA' AND ca1.min_levels_of_separation = 1 INNER JOIN demo_cdm.concept c2 ON ca1.ancestor_concept_id = c2.concept_id AND c2.vocabulary_id = 'MedDRA' GROUP BY c1.concept_id, c1.concept_name ) hlgt_to_soc ON hlt_to_hlgt.hlgt_concept_id = hlgt_to_soc.hlgt_concept_id LEFT JOIN demo_cdm.concept soc ON hlgt_to_soc.soc_concept_id = soc.concept_id) UNION ALL (SELECT concept_id, concept_name, treemap, concept_hierarchy_type, level1_concept_name, level2_concept_name, level3_concept_name, level4_concept_name FROM demo_cdm_ach_res.concept_hierarchy )) INSERT OVERWRITE TABLE demo_cdm_ach_res.concept_hierarchy SELECT * FROM insertion_temp num_affected_rows num_inserted_rows 86 86 WITH insertion_temp AS ( (SELECT rxnorm.concept_id, rxnorm.concept_name AS concept_name, CAST('Drug' AS STRING) AS treemap, CAST(NULL AS STRING) as concept_hierarchy_type, rxnorm.rxnorm_ingredient_concept_name as level1_concept_name, atc5_to_atc3.atc5_concept_name as level2_concept_name, atc3_to_atc1.atc3_concept_name as level3_concept_name, atc1.concept_name AS level4_concept_name FROM ( SELECT c1.concept_id, c1.concept_name, c2.concept_id AS rxnorm_ingredient_concept_id, c2.concept_name AS RxNorm_ingredient_concept_name FROM demo_cdm.concept c1 INNER JOIN demo_cdm.concept_ancestor ca1 ON c1.concept_id = ca1.descendant_concept_id AND c1.domain_id = 'Drug' INNER JOIN demo_cdm.concept c2 ON ca1.ancestor_concept_id = c2.concept_id AND c2.domain_id = 'Drug' AND c2.concept_class_id = 'Ingredient' ) rxnorm LEFT JOIN ( SELECT c1.concept_id AS rxnorm_ingredient_concept_id, max(c2.concept_id) AS atc5_concept_id FROM demo_cdm.concept c1 INNER JOIN demo_cdm.concept_ancestor ca1 ON c1.concept_id = ca1.descendant_concept_id AND c1.domain_id = 'Drug' AND c1.concept_class_id = 'Ingredient' INNER JOIN demo_cdm.concept c2 ON ca1.ancestor_concept_id = c2.concept_id AND c2.vocabulary_id = 'ATC' AND c2.concept_class_id = 'ATC 4th' GROUP BY c1.concept_id ) rxnorm_to_atc5 ON rxnorm.rxnorm_ingredient_concept_id = rxnorm_to_atc5.rxnorm_ingredient_concept_id LEFT JOIN ( SELECT c1.concept_id AS atc5_concept_id, c1.concept_name AS atc5_concept_name, max(c2.concept_id) AS atc3_concept_id FROM demo_cdm.concept c1 INNER JOIN demo_cdm.concept_ancestor ca1 ON c1.concept_id = ca1.descendant_concept_id AND c1.vocabulary_id = 'ATC' AND c1.concept_class_id = 'ATC 4th' INNER JOIN demo_cdm.concept c2 ON ca1.ancestor_concept_id = c2.concept_id AND c2.vocabulary_id = 'ATC' AND c2.concept_class_id = 'ATC 2nd' GROUP BY c1.concept_id, c1.concept_name ) atc5_to_atc3 ON rxnorm_to_atc5.atc5_concept_id = atc5_to_atc3.atc5_concept_id LEFT JOIN ( SELECT c1.concept_id AS atc3_concept_id, c1.concept_name AS atc3_concept_name, max(c2.concept_id) AS atc1_concept_id FROM demo_cdm.concept c1 INNER JOIN demo_cdm.concept_ancestor ca1 ON c1.concept_id = ca1.descendant_concept_id AND c1.vocabulary_id = 'ATC' AND c1.concept_class_id = 'ATC 2nd' INNER JOIN demo_cdm.concept c2 ON ca1.ancestor_concept_id = c2.concept_id AND c2.vocabulary_id = 'ATC' AND c2.concept_class_id = 'ATC 1st' GROUP BY c1.concept_id, c1.concept_name ) atc3_to_atc1 ON atc5_to_atc3.atc3_concept_id = atc3_to_atc1.atc3_concept_id LEFT JOIN demo_cdm.concept atc1 ON atc3_to_atc1.atc1_concept_id = atc1.concept_id) UNION ALL (SELECT concept_id, concept_name, treemap, concept_hierarchy_type, level1_concept_name, level2_concept_name, level3_concept_name, level4_concept_name FROM demo_cdm_ach_res.concept_hierarchy )) INSERT OVERWRITE TABLE demo_cdm_ach_res.concept_hierarchy SELECT * FROM insertion_temp num_affected_rows num_inserted_rows 310 310 WITH insertion_temp AS ( (SELECT rxnorm.rxnorm_ingredient_concept_id as concept_id, rxnorm.rxnorm_ingredient_concept_name as concept_name, CAST('Drug Era' AS STRING) AS treemap, CAST(NULL AS STRING) as concept_hierarchy_type, atc5_to_atc3.atc5_concept_name as level1_concept_name, atc3_to_atc1.atc3_concept_name as level2_concept_name, atc1.concept_name AS level3_concept_name, CAST(NULL AS STRING) as level4_concept_name FROM ( SELECT c2.concept_id AS rxnorm_ingredient_concept_id, c2.concept_name AS RxNorm_ingredient_concept_name FROM demo_cdm.concept c2 WHERE c2.domain_id = 'Drug' AND c2.concept_class_id = 'Ingredient' ) rxnorm LEFT JOIN ( SELECT c1.concept_id AS rxnorm_ingredient_concept_id, max(c2.concept_id) AS atc5_concept_id FROM demo_cdm.concept c1 INNER JOIN demo_cdm.concept_ancestor ca1 ON c1.concept_id = ca1.descendant_concept_id AND c1.domain_id = 'Drug' AND c1.concept_class_id = 'Ingredient' INNER JOIN demo_cdm.concept c2 ON ca1.ancestor_concept_id = c2.concept_id AND c2.vocabulary_id = 'ATC' AND c2.concept_class_id = 'ATC 4th' GROUP BY c1.concept_id ) rxnorm_to_atc5 ON rxnorm.rxnorm_ingredient_concept_id = rxnorm_to_atc5.rxnorm_ingredient_concept_id LEFT JOIN ( SELECT c1.concept_id AS atc5_concept_id, c1.concept_name AS atc5_concept_name, max(c2.concept_id) AS atc3_concept_id FROM demo_cdm.concept c1 INNER JOIN demo_cdm.concept_ancestor ca1 ON c1.concept_id = ca1.descendant_concept_id AND c1.vocabulary_id = 'ATC' AND c1.concept_class_id = 'ATC 4th' INNER JOIN demo_cdm.concept c2 ON ca1.ancestor_concept_id = c2.concept_id AND c2.vocabulary_id = 'ATC' AND c2.concept_class_id = 'ATC 2nd' GROUP BY c1.concept_id, c1.concept_name ) atc5_to_atc3 ON rxnorm_to_atc5.atc5_concept_id = atc5_to_atc3.atc5_concept_id LEFT JOIN ( SELECT c1.concept_id AS atc3_concept_id, c1.concept_name AS atc3_concept_name, max(c2.concept_id) AS atc1_concept_id FROM demo_cdm.concept c1 INNER JOIN demo_cdm.concept_ancestor ca1 ON c1.concept_id = ca1.descendant_concept_id AND c1.vocabulary_id = 'ATC' AND c1.concept_class_id = 'ATC 2nd' INNER JOIN demo_cdm.concept c2 ON ca1.ancestor_concept_id = c2.concept_id AND c2.vocabulary_id = 'ATC' AND c2.concept_class_id = 'ATC 1st' GROUP BY c1.concept_id, c1.concept_name ) atc3_to_atc1 ON atc5_to_atc3.atc3_concept_id = atc3_to_atc1.atc3_concept_id LEFT JOIN demo_cdm.concept atc1 ON atc3_to_atc1.atc1_concept_id = atc1.concept_id) UNION ALL (SELECT concept_id, concept_name, treemap, concept_hierarchy_type, level1_concept_name, level2_concept_name, level3_concept_name, level4_concept_name FROM demo_cdm_ach_res.concept_hierarchy )) INSERT OVERWRITE TABLE demo_cdm_ach_res.concept_hierarchy SELECT * FROM insertion_temp num_affected_rows num_inserted_rows 401 401 WITH insertion_temp AS ( (SELECT m.concept_id, m.concept_name AS concept_name, CAST('Measurement' AS STRING) AS treemap, CAST(NULL AS STRING) as concept_hierarchy_type, CAST(max(c1.concept_name) AS STRING) AS level1_concept_name, CAST(max(c2.concept_name) AS STRING) AS level2_concept_name, CAST(max(c3.concept_name) AS STRING) AS level3_concept_name, CAST(NULL AS STRING) as level4_concept_name FROM ( SELECT DISTINCT concept_id, concept_name FROM demo_cdm.concept c WHERE domain_id = 'Measurement' ) m LEFT JOIN demo_cdm.concept_ancestor ca1 ON M.concept_id = ca1.DESCENDANT_CONCEPT_ID AND ca1.min_levels_of_separation = 1 LEFT JOIN demo_cdm.concept c1 ON ca1.ANCESTOR_CONCEPT_ID = c1.concept_id LEFT JOIN demo_cdm.concept_ancestor ca2 ON c1.concept_id = ca2.DESCENDANT_CONCEPT_ID AND ca2.min_levels_of_separation = 1 LEFT JOIN demo_cdm.concept c2 ON ca2.ANCESTOR_CONCEPT_ID = c2.concept_id LEFT JOIN demo_cdm.concept_ancestor ca3 ON c2.concept_id = ca3.DESCENDANT_CONCEPT_ID AND ca3.min_levels_of_separation = 1 LEFT JOIN demo_cdm.concept c3 ON ca3.ANCESTOR_CONCEPT_ID = c3.concept_id GROUP BY M.concept_id, M.concept_name) UNION ALL (SELECT concept_id, concept_name, treemap, concept_hierarchy_type, level1_concept_name, level2_concept_name, level3_concept_name, level4_concept_name FROM demo_cdm_ach_res.concept_hierarchy )) INSERT OVERWRITE TABLE demo_cdm_ach_res.concept_hierarchy SELECT * FROM insertion_temp num_affected_rows num_inserted_rows 460 460 WITH insertion_temp AS ( (SELECT obs.concept_id, obs.concept_name AS concept_name, CAST('Observation' AS STRING) AS treemap, CAST(NULL AS STRING) as concept_hierarchy_type, CAST(max(c1.concept_name) AS STRING) AS level1_concept_name, CAST(max(c2.concept_name) AS STRING) AS level2_concept_name, CAST(max(c3.concept_name) AS STRING) AS level3_concept_name, CAST(NULL AS STRING) as level4_concept_name FROM ( SELECT concept_id, concept_name FROM demo_cdm.concept WHERE domain_id = 'Observation' ) obs LEFT JOIN demo_cdm.concept_ancestor ca1 ON obs.concept_id = ca1.DESCENDANT_CONCEPT_ID AND ca1.min_levels_of_separation = 1 LEFT JOIN demo_cdm.concept c1 ON ca1.ANCESTOR_CONCEPT_ID = c1.concept_id LEFT JOIN demo_cdm.concept_ancestor ca2 ON c1.concept_id = ca2.DESCENDANT_CONCEPT_ID AND ca2.min_levels_of_separation = 1 LEFT JOIN demo_cdm.concept c2 ON ca2.ANCESTOR_CONCEPT_ID = c2.concept_id LEFT JOIN demo_cdm.concept_ancestor ca3 ON c2.concept_id = ca3.DESCENDANT_CONCEPT_ID AND ca3.min_levels_of_separation = 1 LEFT JOIN demo_cdm.concept c3 ON ca3.ANCESTOR_CONCEPT_ID = c3.concept_id GROUP BY obs.concept_id, obs.concept_name) UNION ALL (SELECT concept_id, concept_name, treemap, concept_hierarchy_type, level1_concept_name, level2_concept_name, level3_concept_name, level4_concept_name FROM demo_cdm_ach_res.concept_hierarchy )) INSERT OVERWRITE TABLE demo_cdm_ach_res.concept_hierarchy SELECT * FROM insertion_temp num_affected_rows num_inserted_rows 481 481 WITH insertion_temp AS ( (SELECT procs.concept_id, CAST(procs.proc_concept_name AS STRING) AS concept_name, CAST('Procedure' AS STRING) AS treemap, CAST(NULL AS STRING) as concept_hierarchy_type, CAST(max(proc_hierarchy.os3_concept_name) AS STRING) AS level1_concept_name, CAST(max(proc_hierarchy.os2_concept_name) AS STRING) AS level2_concept_name, CAST(max(proc_hierarchy.os1_concept_name) AS STRING) AS level3_concept_name, CAST(NULL AS STRING) as level4_concept_name FROM ( SELECT c1.concept_id, CONCAT(v1.vocabulary_name, ' ', c1.concept_code, ': ', c1.concept_name) AS proc_concept_name FROM demo_cdm.concept c1 INNER JOIN demo_cdm.vocabulary v1 ON c1.vocabulary_id = v1.vocabulary_id WHERE c1.domain_id = 'Procedure' ) procs LEFT JOIN ( SELECT ca0.DESCENDANT_CONCEPT_ID, max(ca0.ancestor_concept_id) AS ancestor_concept_id FROM demo_cdm.concept_ancestor ca0 INNER JOIN ( SELECT DISTINCT c2.concept_id AS os3_concept_id FROM demo_cdm.concept_ancestor ca1 INNER JOIN demo_cdm.concept c1 ON ca1.DESCENDANT_CONCEPT_ID = c1.concept_id INNER JOIN demo_cdm.concept_ancestor ca2 ON c1.concept_id = ca2.ANCESTOR_CONCEPT_ID INNER JOIN demo_cdm.concept c2 ON ca2.DESCENDANT_CONCEPT_ID = c2.concept_id WHERE ca1.ancestor_concept_id = 4040390 AND ca1.Min_LEVELS_OF_SEPARATION = 2 AND ca2.MIN_LEVELS_OF_SEPARATION = 1 ) t1 ON ca0.ANCESTOR_CONCEPT_ID = t1.os3_concept_id GROUP BY ca0.descendant_concept_id ) ca1 ON procs.concept_id = ca1.DESCENDANT_CONCEPT_ID LEFT JOIN ( SELECT proc_by_os1.os1_concept_name, proc_by_os2.os2_concept_name, proc_by_os3.os3_concept_name, proc_by_os3.os3_concept_id FROM ( SELECT DESCENDANT_CONCEPT_ID AS os1_concept_id, concept_name AS os1_concept_name FROM demo_cdm.concept_ancestor ca1 INNER JOIN demo_cdm.concept c1 ON ca1.DESCENDANT_CONCEPT_ID = c1.concept_id WHERE ancestor_concept_id = 4040390 AND Min_LEVELS_OF_SEPARATION = 1 ) proc_by_os1 INNER JOIN ( SELECT max(c1.CONCEPT_ID) AS os1_concept_id, c2.concept_id AS os2_concept_id, c2.concept_name AS os2_concept_name FROM demo_cdm.concept_ancestor ca1 INNER JOIN demo_cdm.concept c1 ON ca1.DESCENDANT_CONCEPT_ID = c1.concept_id INNER JOIN demo_cdm.concept_ancestor ca2 ON c1.concept_id = ca2.ANCESTOR_CONCEPT_ID INNER JOIN demo_cdm.concept c2 ON ca2.DESCENDANT_CONCEPT_ID = c2.concept_id WHERE ca1.ancestor_concept_id = 4040390 AND ca1.Min_LEVELS_OF_SEPARATION = 1 AND ca2.MIN_LEVELS_OF_SEPARATION = 1 GROUP BY c2.concept_id, c2.concept_name ) proc_by_os2 ON proc_by_os1.os1_concept_id = proc_by_os2.os1_concept_id INNER JOIN ( SELECT max(c1.CONCEPT_ID) AS os2_concept_id, c2.concept_id AS os3_concept_id, c2.concept_name AS os3_concept_name FROM demo_cdm.concept_ancestor ca1 INNER JOIN demo_cdm.concept c1 ON ca1.DESCENDANT_CONCEPT_ID = c1.concept_id INNER JOIN demo_cdm.concept_ancestor ca2 ON c1.concept_id = ca2.ANCESTOR_CONCEPT_ID INNER JOIN demo_cdm.concept c2 ON ca2.DESCENDANT_CONCEPT_ID = c2.concept_id WHERE ca1.ancestor_concept_id = 4040390 AND ca1.Min_LEVELS_OF_SEPARATION = 2 AND ca2.MIN_LEVELS_OF_SEPARATION = 1 GROUP BY c2.concept_id, c2.concept_name ) proc_by_os3 ON proc_by_os2.os2_concept_id = proc_by_os3.os2_concept_id ) proc_hierarchy ON ca1.ancestor_concept_id = proc_hierarchy.os3_concept_id GROUP BY procs.concept_id, procs.proc_concept_name) UNION ALL (SELECT concept_id, concept_name, treemap, concept_hierarchy_type, level1_concept_name, level2_concept_name, level3_concept_name, level4_concept_name FROM demo_cdm_ach_res.concept_hierarchy )) INSERT OVERWRITE TABLE demo_cdm_ach_res.concept_hierarchy SELECT * FROM insertion_temp num_affected_rows num_inserted_rows 535 535 TRUNCATE TABLE demo_cdm_ach_res.heracles_analysis WITH insertion_temp AS ( (SELECT 0 as analysis_id, CAST('Source name' as STRING) as analysis_name, CAST(NULL AS STRING) as stratum_1_name, CAST(NULL AS STRING) as stratum_2_name, CAST(NULL AS STRING) as stratum_3_name, CAST(NULL AS STRING) as stratum_4_name, CAST(NULL AS STRING) as stratum_5_name, CAST('PERSON' as STRING) as analysis_type union all select 1 as analysis_id, CAST('Number of persons' as STRING) as analysis_name, CAST(NULL AS STRING) as stratum_1_name, CAST(NULL AS STRING) as stratum_2_name, CAST(NULL AS STRING) as stratum_3_name, CAST(NULL AS STRING) as stratum_4_name, CAST(NULL AS STRING) as stratum_5_name, CAST('PERSON' as STRING) as analysis_type union all select 2 as analysis_id, CAST('Number of persons by gender' as STRING) as analysis_name, CAST('gender_concept_id' as STRING) as stratum_1_name, CAST(NULL AS STRING) as stratum_2_name, CAST(NULL AS STRING) as stratum_3_name, CAST(NULL AS STRING) as stratum_4_name, CAST(NULL AS STRING) as stratum_5_name, CAST('PERSON' as STRING) as analysis_type union all select 3 as analysis_id, CAST('Number of persons by year of birth' as STRING) as analysis_name, CAST('year_of_birth' as STRING) as stratum_1_name, CAST(NULL AS STRING) as stratum_2_name, CAST(NULL AS STRING) as stratum_3_name, CAST(NULL AS STRING) as stratum_4_name, CAST(NULL AS STRING) as stratum_5_name, CAST('PERSON' as STRING) as analysis_type union all select 4 as analysis_id, CAST('Number of persons by race' as STRING) as analysis_name, CAST('race_concept_id' as STRING) as stratum_1_name, CAST(NULL AS STRING) as stratum_2_name, CAST(NULL AS STRING) as stratum_3_name, CAST(NULL AS STRING) as stratum_4_name, CAST(NULL AS STRING) as stratum_5_name, CAST('PERSON' as STRING) as analysis_type union all select 5 as analysis_id, CAST('Number of persons by ethnicity' as STRING) as analysis_name, CAST('ethnicity_concept_id' as STRING) as stratum_1_name, CAST(NULL AS STRING) as stratum_2_name, CAST(NULL AS STRING) as stratum_3_name, CAST(NULL AS STRING) as stratum_4_name, CAST(NULL AS STRING) as stratum_5_name, CAST('PERSON' as STRING) as analysis_type union all select 7 as analysis_id, CAST('Number of persons with invalid provider_id' as STRING) as analysis_name, CAST(NULL AS STRING) as stratum_1_name, CAST(NULL AS STRING) as stratum_2_name, CAST(NULL AS STRING) as stratum_3_name, CAST(NULL AS STRING) as stratum_4_name, CAST(NULL AS STRING) as stratum_5_name, CAST('PERSON' as STRING) as analysis_type union all select 8 as analysis_id, CAST('Number of persons with invalid location_id' as STRING) as analysis_name, CAST(NULL AS STRING) as stratum_1_name, CAST(NULL AS STRING) as stratum_2_name, CAST(NULL AS STRING) as stratum_3_name, CAST(NULL AS STRING) as stratum_4_name, CAST(NULL AS STRING) as stratum_5_name, CAST('PERSON' as STRING) as analysis_type union all select 9 as analysis_id, CAST('Number of persons with invalid care_site_id' as STRING) as analysis_name, CAST(NULL AS STRING) as stratum_1_name, CAST(NULL AS STRING) as stratum_2_name, CAST(NULL AS STRING) as stratum_3_name, CAST(NULL AS STRING) as stratum_4_name, CAST(NULL AS STRING) as stratum_5_name, CAST('PERSON' as STRING) as analysis_type union all select 101 as analysis_id, CAST('Number of persons by age, with age at first observation period' as STRING) as analysis_name, CAST('age' as STRING) as stratum_1_name, CAST(NULL AS STRING) as stratum_2_name, CAST(NULL AS STRING) as stratum_3_name, CAST(NULL AS STRING) as stratum_4_name, CAST(NULL AS STRING) as stratum_5_name, CAST('OBSERVATION' as STRING) as analysis_type union all select 102 as analysis_id, CAST('Number of persons by gender by age, with age at first observation period' as STRING) as analysis_name, CAST('gender_concept_id' as STRING) as stratum_1_name, CAST('age' as STRING) as stratum_2_name, CAST(NULL AS STRING) as stratum_3_name, CAST(NULL AS STRING) as stratum_4_name, CAST(NULL AS STRING) as stratum_5_name, CAST('OBSERVATION' as STRING) as analysis_type union all select 103 as analysis_id, CAST('Distribution of age at first observation period' as STRING) as analysis_name, CAST(NULL AS STRING) as stratum_1_name, CAST(NULL AS STRING) as stratum_2_name, CAST(NULL AS STRING) as stratum_3_name, CAST(NULL AS STRING) as stratum_4_name, CAST(NULL AS STRING) as stratum_5_name, CAST('OBSERVATION' as STRING) as analysis_type union all select 104 as analysis_id, CAST('Distribution of age at first observation period by gender' as STRING) as analysis_name, CAST('gender_concept_id' as STRING) as stratum_1_name, CAST(NULL AS STRING) as stratum_2_name, CAST(NULL AS STRING) as stratum_3_name, CAST(NULL AS STRING) as stratum_4_name, CAST(NULL AS STRING) as stratum_5_name, CAST('OBSERVATION' as STRING) as analysis_type union all select 105 as analysis_id, CAST('Length of observation (days) of first observation period' as STRING) as analysis_name, CAST(NULL AS STRING) as stratum_1_name, CAST(NULL AS STRING) as stratum_2_name, CAST(NULL AS STRING) as stratum_3_name, CAST(NULL AS STRING) as stratum_4_name, CAST(NULL AS STRING) as stratum_5_name, CAST('OBSERVATION' as STRING) as analysis_type union all select 106 as analysis_id, CAST('Length of observation (days) of first observation period by gender' as STRING) as analysis_name, CAST('gender_concept_id' as STRING) as stratum_1_name, CAST(NULL AS STRING) as stratum_2_name, CAST(NULL AS STRING) as stratum_3_name, CAST(NULL AS STRING) as stratum_4_name, CAST(NULL AS STRING) as stratum_5_name, CAST('OBSERVATION' as STRING) as analysis_type union all select 107 as analysis_id, CAST('Length of observation (days) of first observation period by age decile' as STRING) as analysis_name, CAST('age decile' as STRING) as stratum_1_name, CAST(NULL AS STRING) as stratum_2_name, CAST(NULL AS STRING) as stratum_3_name, CAST(NULL AS STRING) as stratum_4_name, CAST(NULL AS STRING) as stratum_5_name, CAST('OBSERVATION' as STRING) as analysis_type union all select 108 as analysis_id, CAST('Number of persons by length of first observation period, in 30d increments' as STRING) as analysis_name, CAST('Observation period length 30d increments' as STRING) as stratum_1_name, CAST(NULL AS STRING) as stratum_2_name, CAST(NULL AS STRING) as stratum_3_name, CAST(NULL AS STRING) as stratum_4_name, CAST(NULL AS STRING) as stratum_5_name, CAST('OBSERVATION' as STRING) as analysis_type union all select 109 as analysis_id, CAST('Number of persons with continuous observation in each year' as STRING) as analysis_name, CAST('calendar year' as STRING) as stratum_1_name, CAST(NULL AS STRING) as stratum_2_name, CAST(NULL AS STRING) as stratum_3_name, CAST(NULL AS STRING) as stratum_4_name, CAST(NULL AS STRING) as stratum_5_name, CAST('OBSERVATION' as STRING) as analysis_type union all select 110 as analysis_id, CAST('Number of persons with continuous observation in each month' as STRING) as analysis_name, CAST('calendar month' as STRING) as stratum_1_name, CAST(NULL AS STRING) as stratum_2_name, CAST(NULL AS STRING) as stratum_3_name, CAST(NULL AS STRING) as stratum_4_name, CAST(NULL AS STRING) as stratum_5_name, CAST('OBSERVATION' as STRING) as analysis_type union all select 111 as analysis_id, CAST('Number of persons by observation period start month' as STRING) as analysis_name, CAST('calendar month' as STRING) as stratum_1_name, CAST(NULL AS STRING) as stratum_2_name, CAST(NULL AS STRING) as stratum_3_name, CAST(NULL AS STRING) as stratum_4_name, CAST(NULL AS STRING) as stratum_5_name, CAST('OBSERVATION' as STRING) as analysis_type union all select 112 as analysis_id, CAST('Number of persons by observation period end month' as STRING) as analysis_name, CAST('calendar month' as STRING) as stratum_1_name, CAST(NULL AS STRING) as stratum_2_name, CAST(NULL AS STRING) as stratum_3_name, CAST(NULL AS STRING) as stratum_4_name, CAST(NULL AS STRING) as stratum_5_name, CAST('OBSERVATION' as STRING) as analysis_type union all select 113 as analysis_id, CAST('Number of persons by number of observation periods' as STRING) as analysis_name, CAST('number of observation periods' as STRING) as stratum_1_name, CAST(NULL AS STRING) as stratum_2_name, CAST(NULL AS STRING) as stratum_3_name, CAST(NULL AS STRING) as stratum_4_name, CAST(NULL AS STRING) as stratum_5_name, CAST('OBSERVATION' as STRING) as analysis_type union all select 114 as analysis_id, CAST('Number of persons with observation period before year-of-birth' as STRING) as analysis_name, CAST(NULL AS STRING) as stratum_1_name, CAST(NULL AS STRING) as stratum_2_name, CAST(NULL AS STRING) as stratum_3_name, CAST(NULL AS STRING) as stratum_4_name, CAST(NULL AS STRING) as stratum_5_name, CAST('OBSERVATION' as STRING) as analysis_type union all select 115 as analysis_id, CAST('Number of persons with observation period end < observation period start' as STRING) as analysis_name, CAST(NULL AS STRING) as stratum_1_name, CAST(NULL AS STRING) as stratum_2_name, CAST(NULL AS STRING) as stratum_3_name, CAST(NULL AS STRING) as stratum_4_name, CAST(NULL AS STRING) as stratum_5_name, CAST('OBSERVATION' as STRING) as analysis_type union all select 116 as analysis_id, CAST('Number of persons with at least one day of observation in each year by gender and age decile' as STRING) as analysis_name, CAST('calendar year' as STRING) as stratum_1_name, CAST('gender_concept_id' as STRING) as stratum_2_name, CAST('age decile' as STRING) as stratum_3_name, CAST(NULL AS STRING) as stratum_4_name, CAST(NULL AS STRING) as stratum_5_name, CAST('OBSERVATION' as STRING) as analysis_type union all select 117 as analysis_id, CAST('Number of persons with at least one day of observation in each month' as STRING) as analysis_name, CAST('calendar month' as STRING) as stratum_1_name, CAST(NULL AS STRING) as stratum_2_name, CAST(NULL AS STRING) as stratum_3_name, CAST(NULL AS STRING) as stratum_4_name, CAST(NULL AS STRING) as stratum_5_name, CAST('OBSERVATION' as STRING) as analysis_type union all select 200 as analysis_id, CAST('Number of persons with at least one visit occurrence, by visit_concept_id' as STRING) as analysis_name, CAST('visit_concept_id' as STRING) as stratum_1_name, CAST(NULL AS STRING) as stratum_2_name, CAST(NULL AS STRING) as stratum_3_name, CAST(NULL AS STRING) as stratum_4_name, CAST(NULL AS STRING) as stratum_5_name, CAST('VISITS' as STRING) as analysis_type union all select 201 as analysis_id, CAST('Number of visit occurrence records, by visit_concept_id' as STRING) as analysis_name, CAST('visit_concept_id' as STRING) as stratum_1_name, CAST(NULL AS STRING) as stratum_2_name, CAST(NULL AS STRING) as stratum_3_name, CAST(NULL AS STRING) as stratum_4_name, CAST(NULL AS STRING) as stratum_5_name, CAST('VISITS' as STRING) as analysis_type union all select 202 as analysis_id, CAST('Number of persons by visit occurrence start month, by visit_concept_id' as STRING) as analysis_name, CAST('visit_concept_id' as STRING) as stratum_1_name, CAST('calendar month' as STRING) as stratum_2_name, CAST(NULL AS STRING) as stratum_3_name, CAST(NULL AS STRING) as stratum_4_name, CAST(NULL AS STRING) as stratum_5_name, CAST('VISITS' as STRING) as analysis_type union all select 203 as analysis_id, CAST('Number of distinct visit occurrence concepts per person' as STRING) as analysis_name, CAST(NULL AS STRING) as stratum_1_name, CAST(NULL AS STRING) as stratum_2_name, CAST(NULL AS STRING) as stratum_3_name, CAST(NULL AS STRING) as stratum_4_name, CAST(NULL AS STRING) as stratum_5_name, CAST('VISITS' as STRING) as analysis_type union all select 204 as analysis_id, CAST('Number of persons with at least one visit occurrence, by visit_concept_id by calendar year by gender by age decile' as STRING) as analysis_name, CAST('visit_concept_id' as STRING) as stratum_1_name, CAST('calendar year' as STRING) as stratum_2_name, CAST('gender_concept_id' as STRING) as stratum_3_name, CAST('age decile' as STRING) as stratum_4_name, CAST(NULL AS STRING) as stratum_5_name, CAST('VISITS' as STRING) as analysis_type union all select 206 as analysis_id, CAST('Distribution of age by visit_concept_id' as STRING) as analysis_name, CAST('visit_concept_id' as STRING) as stratum_1_name, CAST('gender_concept_id' as STRING) as stratum_2_name, CAST(NULL AS STRING) as stratum_3_name, CAST(NULL AS STRING) as stratum_4_name, CAST(NULL AS STRING) as stratum_5_name, CAST('VISITS' as STRING) as analysis_type union all select 207 as analysis_id, CAST('Number of visit records with invalid person_id' as STRING) as analysis_name, CAST(NULL AS STRING) as stratum_1_name, CAST(NULL AS STRING) as stratum_2_name, CAST(NULL AS STRING) as stratum_3_name, CAST(NULL AS STRING) as stratum_4_name, CAST(NULL AS STRING) as stratum_5_name, CAST('VISITS' as STRING) as analysis_type union all select 208 as analysis_id, CAST('Number of visit records outside valid observation period' as STRING) as analysis_name, CAST(NULL AS STRING) as stratum_1_name, CAST(NULL AS STRING) as stratum_2_name, CAST(NULL AS STRING) as stratum_3_name, CAST(NULL AS STRING) as stratum_4_name, CAST(NULL AS STRING) as stratum_5_name, CAST('VISITS' as STRING) as analysis_type union all select 209 as analysis_id, CAST('Number of visit records with end date < start date' as STRING) as analysis_name, CAST(NULL AS STRING) as stratum_1_name, CAST(NULL AS STRING) as stratum_2_name, CAST(NULL AS STRING) as stratum_3_name, CAST(NULL AS STRING) as stratum_4_name, CAST(NULL AS STRING) as stratum_5_name, CAST('VISITS' as STRING) as analysis_type union all select 210 as analysis_id, CAST('Number of visit records with invalid care_site_id' as STRING) as analysis_name, CAST(NULL AS STRING) as stratum_1_name, CAST(NULL AS STRING) as stratum_2_name, CAST(NULL AS STRING) as stratum_3_name, CAST(NULL AS STRING) as stratum_4_name, CAST(NULL AS STRING) as stratum_5_name, CAST('VISITS' as STRING) as analysis_type union all select 211 as analysis_id, CAST('Distribution of length of stay by visit_concept_id' as STRING) as analysis_name, CAST('visit_concept_id' as STRING) as stratum_1_name, CAST(NULL AS STRING) as stratum_2_name, CAST(NULL AS STRING) as stratum_3_name, CAST(NULL AS STRING) as stratum_4_name, CAST(NULL AS STRING) as stratum_5_name, CAST('VISITS' as STRING) as analysis_type union all select 220 as analysis_id, CAST('Number of visit occurrence records by visit occurrence start month' as STRING) as analysis_name, CAST('calendar month' as STRING) as stratum_1_name, CAST(NULL AS STRING) as stratum_2_name, CAST(NULL AS STRING) as stratum_3_name, CAST(NULL AS STRING) as stratum_4_name, CAST(NULL AS STRING) as stratum_5_name, CAST('VISITS' as STRING) as analysis_type union all select 400 as analysis_id, CAST('Number of persons with at least one condition occurrence, by condition_concept_id' as STRING) as analysis_name, CAST('condition_concept_id' as STRING) as stratum_1_name, CAST(NULL AS STRING) as stratum_2_name, CAST(NULL AS STRING) as stratum_3_name, CAST(NULL AS STRING) as stratum_4_name, CAST(NULL AS STRING) as stratum_5_name, CAST('CONDITION' as STRING) as analysis_type union all select 401 as analysis_id, CAST('Number of condition occurrence records, by condition_concept_id' as STRING) as analysis_name, CAST('condition_concept_id' as STRING) as stratum_1_name, CAST(NULL AS STRING) as stratum_2_name, CAST(NULL AS STRING) as stratum_3_name, CAST(NULL AS STRING) as stratum_4_name, CAST(NULL AS STRING) as stratum_5_name, CAST('CONDITION' as STRING) as analysis_type union all select 402 as analysis_id, CAST('Number of persons by condition occurrence start month, by condition_concept_id' as STRING) as analysis_name, CAST('condition_concept_id' as STRING) as stratum_1_name, CAST('calendar month' as STRING) as stratum_2_name, CAST(NULL AS STRING) as stratum_3_name, CAST(NULL AS STRING) as stratum_4_name, CAST(NULL AS STRING) as stratum_5_name, CAST('CONDITION' as STRING) as analysis_type union all select 403 as analysis_id, CAST('Number of distinct condition occurrence concepts per person' as STRING) as analysis_name, CAST(NULL AS STRING) as stratum_1_name, CAST(NULL AS STRING) as stratum_2_name, CAST(NULL AS STRING) as stratum_3_name, CAST(NULL AS STRING) as stratum_4_name, CAST(NULL AS STRING) as stratum_5_name, CAST('CONDITION' as STRING) as analysis_type union all select 404 as analysis_id, CAST('Number of persons with at least one condition occurrence, by condition_concept_id by calendar year by gender by age decile' as STRING) as analysis_name, CAST('condition_concept_id' as STRING) as stratum_1_name, CAST('calendar year' as STRING) as stratum_2_name, CAST('gender_concept_id' as STRING) as stratum_3_name, CAST('age decile' as STRING) as stratum_4_name, CAST(NULL AS STRING) as stratum_5_name, CAST('CONDITION' as STRING) as analysis_type union all select 405 as analysis_id, CAST('Number of condition occurrence records, by condition_concept_id by condition_type_concept_id' as STRING) as analysis_name, CAST('condition_concept_id' as STRING) as stratum_1_name, CAST('condition_type_concept_id' as STRING) as stratum_2_name, CAST(NULL AS STRING) as stratum_3_name, CAST(NULL AS STRING) as stratum_4_name, CAST(NULL AS STRING) as stratum_5_name, CAST('CONDITION' as STRING) as analysis_type union all select 406 as analysis_id, CAST('Distribution of age by condition_concept_id' as STRING) as analysis_name, CAST('condition_concept_id' as STRING) as stratum_1_name, CAST('gender_concept_id' as STRING) as stratum_2_name, CAST(NULL AS STRING) as stratum_3_name, CAST(NULL AS STRING) as stratum_4_name, CAST(NULL AS STRING) as stratum_5_name, CAST('CONDITION' as STRING) as analysis_type union all select 409 as analysis_id, CAST('Number of condition occurrence records with invalid person_id' as STRING) as analysis_name, CAST(NULL AS STRING) as stratum_1_name, CAST(NULL AS STRING) as stratum_2_name, CAST(NULL AS STRING) as stratum_3_name, CAST(NULL AS STRING) as stratum_4_name, CAST(NULL AS STRING) as stratum_5_name, CAST('CONDITION' as STRING) as analysis_type union all select 410 as analysis_id, CAST('Number of condition occurrence records outside valid observation period' as STRING) as analysis_name, CAST(NULL AS STRING) as stratum_1_name, CAST(NULL AS STRING) as stratum_2_name, CAST(NULL AS STRING) as stratum_3_name, CAST(NULL AS STRING) as stratum_4_name, CAST(NULL AS STRING) as stratum_5_name, CAST('CONDITION' as STRING) as analysis_type union all select 411 as analysis_id, CAST('Number of condition occurrence records with end date < start date' as STRING) as analysis_name, CAST(NULL AS STRING) as stratum_1_name, CAST(NULL AS STRING) as stratum_2_name, CAST(NULL AS STRING) as stratum_3_name, CAST(NULL AS STRING) as stratum_4_name, CAST(NULL AS STRING) as stratum_5_name, CAST('CONDITION' as STRING) as analysis_type union all select 412 as analysis_id, CAST('Number of condition occurrence records with invalid provider_id' as STRING) as analysis_name, CAST(NULL AS STRING) as stratum_1_name, CAST(NULL AS STRING) as stratum_2_name, CAST(NULL AS STRING) as stratum_3_name, CAST(NULL AS STRING) as stratum_4_name, CAST(NULL AS STRING) as stratum_5_name, CAST('CONDITION' as STRING) as analysis_type union all select 413 as analysis_id, CAST('Number of condition occurrence records with invalid visit_id' as STRING) as analysis_name, CAST(NULL AS STRING) as stratum_1_name, CAST(NULL AS STRING) as stratum_2_name, CAST(NULL AS STRING) as stratum_3_name, CAST(NULL AS STRING) as stratum_4_name, CAST(NULL AS STRING) as stratum_5_name, CAST('CONDITION' as STRING) as analysis_type union all select 420 as analysis_id, CAST('Number of condition occurrence records by condition occurrence start month' as STRING) as analysis_name, CAST('calendar month' as STRING) as stratum_1_name, CAST(NULL AS STRING) as stratum_2_name, CAST(NULL AS STRING) as stratum_3_name, CAST(NULL AS STRING) as stratum_4_name, CAST(NULL AS STRING) as stratum_5_name, CAST('CONDITION' as STRING) as analysis_type union all select 500 as analysis_id, CAST('Number of persons with death, by cause_of_death_concept_id' as STRING) as analysis_name, CAST('cause_of_death_concept_id' as STRING) as stratum_1_name, CAST(NULL AS STRING) as stratum_2_name, CAST(NULL AS STRING) as stratum_3_name, CAST(NULL AS STRING) as stratum_4_name, CAST(NULL AS STRING) as stratum_5_name, CAST('DEATH' as STRING) as analysis_type union all select 501 as analysis_id, CAST('Number of records of death, by cause_of_death_concept_id' as STRING) as analysis_name, CAST('cause_of_death_concept_id' as STRING) as stratum_1_name, CAST(NULL AS STRING) as stratum_2_name, CAST(NULL AS STRING) as stratum_3_name, CAST(NULL AS STRING) as stratum_4_name, CAST(NULL AS STRING) as stratum_5_name, CAST('DEATH' as STRING) as analysis_type union all select 502 as analysis_id, CAST('Number of persons by death month' as STRING) as analysis_name, CAST('calendar month' as STRING) as stratum_1_name, CAST(NULL AS STRING) as stratum_2_name, CAST(NULL AS STRING) as stratum_3_name, CAST(NULL AS STRING) as stratum_4_name, CAST(NULL AS STRING) as stratum_5_name, CAST('DEATH' as STRING) as analysis_type union all select 504 as analysis_id, CAST('Number of persons with a death, by calendar year by gender by age decile' as STRING) as analysis_name, CAST('calendar year' as STRING) as stratum_1_name, CAST('gender_concept_id' as STRING) as stratum_2_name, CAST('age decile' as STRING) as stratum_3_name, CAST(NULL AS STRING) as stratum_4_name, CAST(NULL AS STRING) as stratum_5_name, CAST('DEATH' as STRING) as analysis_type union all select 505 as analysis_id, CAST('Number of death records, by death_type_concept_id' as STRING) as analysis_name, CAST('death_type_concept_id' as STRING) as stratum_1_name, CAST(NULL AS STRING) as stratum_2_name, CAST(NULL AS STRING) as stratum_3_name, CAST(NULL AS STRING) as stratum_4_name, CAST(NULL AS STRING) as stratum_5_name, CAST('DEATH' as STRING) as analysis_type union all select 506 as analysis_id, CAST('Distribution of age at death by gender' as STRING) as analysis_name, CAST('gender_concept_id' as STRING) as stratum_1_name, CAST(NULL AS STRING) as stratum_2_name, CAST(NULL AS STRING) as stratum_3_name, CAST(NULL AS STRING) as stratum_4_name, CAST(NULL AS STRING) as stratum_5_name, CAST('DEATH' as STRING) as analysis_type union all select 509 as analysis_id, CAST('Number of death records with invalid person_id' as STRING) as analysis_name, CAST(NULL AS STRING) as stratum_1_name, CAST(NULL AS STRING) as stratum_2_name, CAST(NULL AS STRING) as stratum_3_name, CAST(NULL AS STRING) as stratum_4_name, CAST(NULL AS STRING) as stratum_5_name, CAST('DEATH' as STRING) as analysis_type union all select 510 as analysis_id, CAST('Number of death records outside valid observation period' as STRING) as analysis_name, CAST(NULL AS STRING) as stratum_1_name, CAST(NULL AS STRING) as stratum_2_name, CAST(NULL AS STRING) as stratum_3_name, CAST(NULL AS STRING) as stratum_4_name, CAST(NULL AS STRING) as stratum_5_name, CAST('DEATH' as STRING) as analysis_type union all select 511 as analysis_id, CAST('Distribution of time from death to last condition' as STRING) as analysis_name, CAST(NULL AS STRING) as stratum_1_name, CAST(NULL AS STRING) as stratum_2_name, CAST(NULL AS STRING) as stratum_3_name, CAST(NULL AS STRING) as stratum_4_name, CAST(NULL AS STRING) as stratum_5_name, CAST('DEATH' as STRING) as analysis_type union all select 512 as analysis_id, CAST('Distribution of time from death to last drug' as STRING) as analysis_name, CAST(NULL AS STRING) as stratum_1_name, CAST(NULL AS STRING) as stratum_2_name, CAST(NULL AS STRING) as stratum_3_name, CAST(NULL AS STRING) as stratum_4_name, CAST(NULL AS STRING) as stratum_5_name, CAST('DEATH' as STRING) as analysis_type union all select 513 as analysis_id, CAST('Distribution of time from death to last visit' as STRING) as analysis_name, CAST(NULL AS STRING) as stratum_1_name, CAST(NULL AS STRING) as stratum_2_name, CAST(NULL AS STRING) as stratum_3_name, CAST(NULL AS STRING) as stratum_4_name, CAST(NULL AS STRING) as stratum_5_name, CAST('DEATH' as STRING) as analysis_type union all select 514 as analysis_id, CAST('Distribution of time from death to last procedure' as STRING) as analysis_name, CAST(NULL AS STRING) as stratum_1_name, CAST(NULL AS STRING) as stratum_2_name, CAST(NULL AS STRING) as stratum_3_name, CAST(NULL AS STRING) as stratum_4_name, CAST(NULL AS STRING) as stratum_5_name, CAST('DEATH' as STRING) as analysis_type union all select 515 as analysis_id, CAST('Distribution of time from death to last observation' as STRING) as analysis_name, CAST(NULL AS STRING) as stratum_1_name, CAST(NULL AS STRING) as stratum_2_name, CAST(NULL AS STRING) as stratum_3_name, CAST(NULL AS STRING) as stratum_4_name, CAST(NULL AS STRING) as stratum_5_name, CAST('DEATH' as STRING) as analysis_type union all select 600 as analysis_id, CAST('Number of persons with at least one procedure occurrence, by procedure_concept_id' as STRING) as analysis_name, CAST('procedure_concept_id' as STRING) as stratum_1_name, CAST(NULL AS STRING) as stratum_2_name, CAST(NULL AS STRING) as stratum_3_name, CAST(NULL AS STRING) as stratum_4_name, CAST(NULL AS STRING) as stratum_5_name, CAST('PROCEDURE' as STRING) as analysis_type union all select 601 as analysis_id, CAST('Number of procedure occurrence records, by procedure_concept_id' as STRING) as analysis_name, CAST('procedure_concept_id' as STRING) as stratum_1_name, CAST(NULL AS STRING) as stratum_2_name, CAST(NULL AS STRING) as stratum_3_name, CAST(NULL AS STRING) as stratum_4_name, CAST(NULL AS STRING) as stratum_5_name, CAST('PROCEDURE' as STRING) as analysis_type union all select 602 as analysis_id, CAST('Number of persons by procedure occurrence start month, by procedure_concept_id' as STRING) as analysis_name, CAST('procedure_concept_id' as STRING) as stratum_1_name, CAST('calendar month' as STRING) as stratum_2_name, CAST(NULL AS STRING) as stratum_3_name, CAST(NULL AS STRING) as stratum_4_name, CAST(NULL AS STRING) as stratum_5_name, CAST('PROCEDURE' as STRING) as analysis_type union all select 603 as analysis_id, CAST('Number of distinct procedure occurrence concepts per person' as STRING) as analysis_name, CAST(NULL AS STRING) as stratum_1_name, CAST(NULL AS STRING) as stratum_2_name, CAST(NULL AS STRING) as stratum_3_name, CAST(NULL AS STRING) as stratum_4_name, CAST(NULL AS STRING) as stratum_5_name, CAST('PROCEDURE' as STRING) as analysis_type union all select 604 as analysis_id, CAST('Number of persons with at least one procedure occurrence, by procedure_concept_id by calendar year by gender by age decile' as STRING) as analysis_name, CAST('procedure_concept_id' as STRING) as stratum_1_name, CAST('calendar year' as STRING) as stratum_2_name, CAST('gender_concept_id' as STRING) as stratum_3_name, CAST('age decile' as STRING) as stratum_4_name, CAST(NULL AS STRING) as stratum_5_name, CAST('PROCEDURE' as STRING) as analysis_type union all select 605 as analysis_id, CAST('Number of procedure occurrence records, by procedure_concept_id by procedure_type_concept_id' as STRING) as analysis_name, CAST('procedure_concept_id' as STRING) as stratum_1_name, CAST('procedure_type_concept_id' as STRING) as stratum_2_name, CAST(NULL AS STRING) as stratum_3_name, CAST(NULL AS STRING) as stratum_4_name, CAST(NULL AS STRING) as stratum_5_name, CAST('PROCEDURE' as STRING) as analysis_type union all select 606 as analysis_id, CAST('Distribution of age by procedure_concept_id' as STRING) as analysis_name, CAST('procedure_concept_id' as STRING) as stratum_1_name, CAST('gender_concept_id' as STRING) as stratum_2_name, CAST(NULL AS STRING) as stratum_3_name, CAST(NULL AS STRING) as stratum_4_name, CAST(NULL AS STRING) as stratum_5_name, CAST('PROCEDURE' as STRING) as analysis_type union all select 609 as analysis_id, CAST('Number of procedure occurrence records with invalid person_id' as STRING) as analysis_name, CAST(NULL AS STRING) as stratum_1_name, CAST(NULL AS STRING) as stratum_2_name, CAST(NULL AS STRING) as stratum_3_name, CAST(NULL AS STRING) as stratum_4_name, CAST(NULL AS STRING) as stratum_5_name, CAST('PROCEDURE' as STRING) as analysis_type union all select 610 as analysis_id, CAST('Number of procedure occurrence records outside valid observation period' as STRING) as analysis_name, CAST(NULL AS STRING) as stratum_1_name, CAST(NULL AS STRING) as stratum_2_name, CAST(NULL AS STRING) as stratum_3_name, CAST(NULL AS STRING) as stratum_4_name, CAST(NULL AS STRING) as stratum_5_name, CAST('PROCEDURE' as STRING) as analysis_type union all select 612 as analysis_id, CAST('Number of procedure occurrence records with invalid provider_id' as STRING) as analysis_name, CAST(NULL AS STRING) as stratum_1_name, CAST(NULL AS STRING) as stratum_2_name, CAST(NULL AS STRING) as stratum_3_name, CAST(NULL AS STRING) as stratum_4_name, CAST(NULL AS STRING) as stratum_5_name, CAST('PROCEDURE' as STRING) as analysis_type union all select 613 as analysis_id, CAST('Number of procedure occurrence records with invalid visit_id' as STRING) as analysis_name, CAST(NULL AS STRING) as stratum_1_name, CAST(NULL AS STRING) as stratum_2_name, CAST(NULL AS STRING) as stratum_3_name, CAST(NULL AS STRING) as stratum_4_name, CAST(NULL AS STRING) as stratum_5_name, CAST('PROCEDURE' as STRING) as analysis_type union all select 620 as analysis_id, CAST('Number of procedure occurrence records by procedure occurrence start month' as STRING) as analysis_name, CAST('calendar month' as STRING) as stratum_1_name, CAST(NULL AS STRING) as stratum_2_name, CAST(NULL AS STRING) as stratum_3_name, CAST(NULL AS STRING) as stratum_4_name, CAST(NULL AS STRING) as stratum_5_name, CAST('PROCEDURE' as STRING) as analysis_type union all select 700 as analysis_id, CAST('Number of persons with at least one drug exposure, by drug_concept_id' as STRING) as analysis_name, CAST('drug_concept_id' as STRING) as stratum_1_name, CAST(NULL AS STRING) as stratum_2_name, CAST(NULL AS STRING) as stratum_3_name, CAST(NULL AS STRING) as stratum_4_name, CAST(NULL AS STRING) as stratum_5_name, CAST('DRUG' as STRING) as analysis_type union all select 701 as analysis_id, CAST('Number of drug exposure records, by drug_concept_id' as STRING) as analysis_name, CAST('drug_concept_id' as STRING) as stratum_1_name, CAST(NULL AS STRING) as stratum_2_name, CAST(NULL AS STRING) as stratum_3_name, CAST(NULL AS STRING) as stratum_4_name, CAST(NULL AS STRING) as stratum_5_name, CAST('DRUG' as STRING) as analysis_type union all select 702 as analysis_id, CAST('Number of persons by drug exposure start month, by drug_concept_id' as STRING) as analysis_name, CAST('drug_concept_id' as STRING) as stratum_1_name, CAST('calendar month' as STRING) as stratum_2_name, CAST(NULL AS STRING) as stratum_3_name, CAST(NULL AS STRING) as stratum_4_name, CAST(NULL AS STRING) as stratum_5_name, CAST('DRUG' as STRING) as analysis_type union all select 703 as analysis_id, CAST('Number of distinct drug exposure concepts per person' as STRING) as analysis_name, CAST(NULL AS STRING) as stratum_1_name, CAST(NULL AS STRING) as stratum_2_name, CAST(NULL AS STRING) as stratum_3_name, CAST(NULL AS STRING) as stratum_4_name, CAST(NULL AS STRING) as stratum_5_name, CAST('DRUG' as STRING) as analysis_type union all select 704 as analysis_id, CAST('Number of persons with at least one drug exposure, by drug_concept_id by calendar year by gender by age decile' as STRING) as analysis_name, CAST('drug_concept_id' as STRING) as stratum_1_name, CAST('calendar year' as STRING) as stratum_2_name, CAST('gender_concept_id' as STRING) as stratum_3_name, CAST('age decile' as STRING) as stratum_4_name, CAST(NULL AS STRING) as stratum_5_name, CAST('DRUG' as STRING) as analysis_type union all select 705 as analysis_id, CAST('Number of drug exposure records, by drug_concept_id by drug_type_concept_id' as STRING) as analysis_name, CAST('drug_concept_id' as STRING) as stratum_1_name, CAST('drug_type_concept_id' as STRING) as stratum_2_name, CAST(NULL AS STRING) as stratum_3_name, CAST(NULL AS STRING) as stratum_4_name, CAST(NULL AS STRING) as stratum_5_name, CAST('DRUG' as STRING) as analysis_type union all select 706 as analysis_id, CAST('Distribution of age by drug_concept_id' as STRING) as analysis_name, CAST('drug_concept_id' as STRING) as stratum_1_name, CAST('gender_concept_id' as STRING) as stratum_2_name, CAST(NULL AS STRING) as stratum_3_name, CAST(NULL AS STRING) as stratum_4_name, CAST(NULL AS STRING) as stratum_5_name, CAST('DRUG' as STRING) as analysis_type union all select 709 as analysis_id, CAST('Number of drug exposure records with invalid person_id' as STRING) as analysis_name, CAST(NULL AS STRING) as stratum_1_name, CAST(NULL AS STRING) as stratum_2_name, CAST(NULL AS STRING) as stratum_3_name, CAST(NULL AS STRING) as stratum_4_name, CAST(NULL AS STRING) as stratum_5_name, CAST('DRUG' as STRING) as analysis_type union all select 710 as analysis_id, CAST('Number of drug exposure records outside valid observation period' as STRING) as analysis_name, CAST(NULL AS STRING) as stratum_1_name, CAST(NULL AS STRING) as stratum_2_name, CAST(NULL AS STRING) as stratum_3_name, CAST(NULL AS STRING) as stratum_4_name, CAST(NULL AS STRING) as stratum_5_name, CAST('DRUG' as STRING) as analysis_type union all select 711 as analysis_id, CAST('Number of drug exposure records with end date < start date' as STRING) as analysis_name, CAST(NULL AS STRING) as stratum_1_name, CAST(NULL AS STRING) as stratum_2_name, CAST(NULL AS STRING) as stratum_3_name, CAST(NULL AS STRING) as stratum_4_name, CAST(NULL AS STRING) as stratum_5_name, CAST('DRUG' as STRING) as analysis_type union all select 712 as analysis_id, CAST('Number of drug exposure records with invalid provider_id' as STRING) as analysis_name, CAST(NULL AS STRING) as stratum_1_name, CAST(NULL AS STRING) as stratum_2_name, CAST(NULL AS STRING) as stratum_3_name, CAST(NULL AS STRING) as stratum_4_name, CAST(NULL AS STRING) as stratum_5_name, CAST('DRUG' as STRING) as analysis_type union all select 713 as analysis_id, CAST('Number of drug exposure records with invalid visit_id' as STRING) as analysis_name, CAST(NULL AS STRING) as stratum_1_name, CAST(NULL AS STRING) as stratum_2_name, CAST(NULL AS STRING) as stratum_3_name, CAST(NULL AS STRING) as stratum_4_name, CAST(NULL AS STRING) as stratum_5_name, CAST('DRUG' as STRING) as analysis_type union all select 715 as analysis_id, CAST('Distribution of days_supply by drug_concept_id' as STRING) as analysis_name, CAST('drug_concept_id' as STRING) as stratum_1_name, CAST(NULL AS STRING) as stratum_2_name, CAST(NULL AS STRING) as stratum_3_name, CAST(NULL AS STRING) as stratum_4_name, CAST(NULL AS STRING) as stratum_5_name, CAST('DRUG' as STRING) as analysis_type union all select 716 as analysis_id, CAST('Distribution of refills by drug_concept_id' as STRING) as analysis_name, CAST('drug_concept_id' as STRING) as stratum_1_name, CAST(NULL AS STRING) as stratum_2_name, CAST(NULL AS STRING) as stratum_3_name, CAST(NULL AS STRING) as stratum_4_name, CAST(NULL AS STRING) as stratum_5_name, CAST('DRUG' as STRING) as analysis_type union all select 717 as analysis_id, CAST('Distribution of quantity by drug_concept_id' as STRING) as analysis_name, CAST('drug_concept_id' as STRING) as stratum_1_name, CAST(NULL AS STRING) as stratum_2_name, CAST(NULL AS STRING) as stratum_3_name, CAST(NULL AS STRING) as stratum_4_name, CAST(NULL AS STRING) as stratum_5_name, CAST('DRUG' as STRING) as analysis_type union all select 720 as analysis_id, CAST('Number of drug exposure records by drug exposure start month' as STRING) as analysis_name, CAST('calendar month' as STRING) as stratum_1_name, CAST(NULL AS STRING) as stratum_2_name, CAST(NULL AS STRING) as stratum_3_name, CAST(NULL AS STRING) as stratum_4_name, CAST(NULL AS STRING) as stratum_5_name, CAST('DRUG' as STRING) as analysis_type union all select 800 as analysis_id, CAST('Number of persons with at least one observation occurrence, by observation_concept_id' as STRING) as analysis_name, CAST('observation_concept_id' as STRING) as stratum_1_name, CAST(NULL AS STRING) as stratum_2_name, CAST(NULL AS STRING) as stratum_3_name, CAST(NULL AS STRING) as stratum_4_name, CAST(NULL AS STRING) as stratum_5_name, CAST('OBSERVATION' as STRING) as analysis_type union all select 801 as analysis_id, CAST('Number of observation occurrence records, by observation_concept_id' as STRING) as analysis_name, CAST('observation_concept_id' as STRING) as stratum_1_name, CAST(NULL AS STRING) as stratum_2_name, CAST(NULL AS STRING) as stratum_3_name, CAST(NULL AS STRING) as stratum_4_name, CAST(NULL AS STRING) as stratum_5_name, CAST('OBSERVATION' as STRING) as analysis_type union all select 802 as analysis_id, CAST('Number of persons by observation occurrence start month, by observation_concept_id' as STRING) as analysis_name, CAST('observation_concept_id' as STRING) as stratum_1_name, CAST('calendar month' as STRING) as stratum_2_name, CAST(NULL AS STRING) as stratum_3_name, CAST(NULL AS STRING) as stratum_4_name, CAST(NULL AS STRING) as stratum_5_name, CAST('OBSERVATION' as STRING) as analysis_type union all select 803 as analysis_id, CAST('Number of distinct observation occurrence concepts per person' as STRING) as analysis_name, CAST(NULL AS STRING) as stratum_1_name, CAST(NULL AS STRING) as stratum_2_name, CAST(NULL AS STRING) as stratum_3_name, CAST(NULL AS STRING) as stratum_4_name, CAST(NULL AS STRING) as stratum_5_name, CAST('OBSERVATION' as STRING) as analysis_type union all select 804 as analysis_id, CAST('Number of persons with at least one observation occurrence, by observation_concept_id by calendar year by gender by age decile' as STRING) as analysis_name, CAST('observation_concept_id' as STRING) as stratum_1_name, CAST('calendar year' as STRING) as stratum_2_name, CAST('gender_concept_id' as STRING) as stratum_3_name, CAST('age decile' as STRING) as stratum_4_name, CAST(NULL AS STRING) as stratum_5_name, CAST('OBSERVATION' as STRING) as analysis_type union all select 805 as analysis_id, CAST('Number of observation occurrence records, by observation_concept_id by observation_type_concept_id' as STRING) as analysis_name, CAST('observation_concept_id' as STRING) as stratum_1_name, CAST('observation_type_concept_id' as STRING) as stratum_2_name, CAST(NULL AS STRING) as stratum_3_name, CAST(NULL AS STRING) as stratum_4_name, CAST(NULL AS STRING) as stratum_5_name, CAST('OBSERVATION' as STRING) as analysis_type union all select 806 as analysis_id, CAST('Distribution of age by observation_concept_id' as STRING) as analysis_name, CAST('observation_concept_id' as STRING) as stratum_1_name, CAST('gender_concept_id' as STRING) as stratum_2_name, CAST(NULL AS STRING) as stratum_3_name, CAST(NULL AS STRING) as stratum_4_name, CAST(NULL AS STRING) as stratum_5_name, CAST('OBSERVATION' as STRING) as analysis_type union all select 807 as analysis_id, CAST('Number of observation occurrence records, by observation_concept_id and unit_concept_id' as STRING) as analysis_name, CAST('observation_concept_id' as STRING) as stratum_1_name, CAST('unit_concept_id' as STRING) as stratum_2_name, CAST(NULL AS STRING) as stratum_3_name, CAST(NULL AS STRING) as stratum_4_name, CAST(NULL AS STRING) as stratum_5_name, CAST('OBSERVATION' as STRING) as analysis_type union all select 809 as analysis_id, CAST('Number of observation records with invalid person_id' as STRING) as analysis_name, CAST(NULL AS STRING) as stratum_1_name, CAST(NULL AS STRING) as stratum_2_name, CAST(NULL AS STRING) as stratum_3_name, CAST(NULL AS STRING) as stratum_4_name, CAST(NULL AS STRING) as stratum_5_name, CAST('OBSERVATION' as STRING) as analysis_type union all select 810 as analysis_id, CAST('Number of observation records outside valid observation period' as STRING) as analysis_name, CAST(NULL AS STRING) as stratum_1_name, CAST(NULL AS STRING) as stratum_2_name, CAST(NULL AS STRING) as stratum_3_name, CAST(NULL AS STRING) as stratum_4_name, CAST(NULL AS STRING) as stratum_5_name, CAST('OBSERVATION' as STRING) as analysis_type union all select 812 as analysis_id, CAST('Number of observation records with invalid provider_id' as STRING) as analysis_name, CAST(NULL AS STRING) as stratum_1_name, CAST(NULL AS STRING) as stratum_2_name, CAST(NULL AS STRING) as stratum_3_name, CAST(NULL AS STRING) as stratum_4_name, CAST(NULL AS STRING) as stratum_5_name, CAST('OBSERVATION' as STRING) as analysis_type union all select 813 as analysis_id, CAST('Number of observation records with invalid visit_id' as STRING) as analysis_name, CAST(NULL AS STRING) as stratum_1_name, CAST(NULL AS STRING) as stratum_2_name, CAST(NULL AS STRING) as stratum_3_name, CAST(NULL AS STRING) as stratum_4_name, CAST(NULL AS STRING) as stratum_5_name, CAST('OBSERVATION' as STRING) as analysis_type union all select 814 as analysis_id, CAST('Number of observation records with no value (numeric, string, or concept)' as STRING) as analysis_name, CAST(NULL AS STRING) as stratum_1_name, CAST(NULL AS STRING) as stratum_2_name, CAST(NULL AS STRING) as stratum_3_name, CAST(NULL AS STRING) as stratum_4_name, CAST(NULL AS STRING) as stratum_5_name, CAST('OBSERVATION' as STRING) as analysis_type union all select 815 as analysis_id, CAST('Distribution of numeric values, by observation_concept_id and unit_concept_id' as STRING) as analysis_name, CAST(NULL AS STRING) as stratum_1_name, CAST(NULL AS STRING) as stratum_2_name, CAST(NULL AS STRING) as stratum_3_name, CAST(NULL AS STRING) as stratum_4_name, CAST(NULL AS STRING) as stratum_5_name, CAST('OBSERVATION' as STRING) as analysis_type union all select 816 as analysis_id, CAST('Distribution of low range, by observation_concept_id and unit_concept_id' as STRING) as analysis_name, CAST(NULL AS STRING) as stratum_1_name, CAST(NULL AS STRING) as stratum_2_name, CAST(NULL AS STRING) as stratum_3_name, CAST(NULL AS STRING) as stratum_4_name, CAST(NULL AS STRING) as stratum_5_name, CAST('OBSERVATION' as STRING) as analysis_type union all select 817 as analysis_id, CAST('Distribution of high range, by observation_concept_id and unit_concept_id' as STRING) as analysis_name, CAST(NULL AS STRING) as stratum_1_name, CAST(NULL AS STRING) as stratum_2_name, CAST(NULL AS STRING) as stratum_3_name, CAST(NULL AS STRING) as stratum_4_name, CAST(NULL AS STRING) as stratum_5_name, CAST('OBSERVATION' as STRING) as analysis_type union all select 818 as analysis_id, CAST('Number of observation records below/within/above normal range, by observation_concept_id and unit_concept_id' as STRING) as analysis_name, CAST(NULL AS STRING) as stratum_1_name, CAST(NULL AS STRING) as stratum_2_name, CAST(NULL AS STRING) as stratum_3_name, CAST(NULL AS STRING) as stratum_4_name, CAST(NULL AS STRING) as stratum_5_name, CAST('OBSERVATION' as STRING) as analysis_type union all select 820 as analysis_id, CAST('Number of observation records by observation start month' as STRING) as analysis_name, CAST('calendar month' as STRING) as stratum_1_name, CAST(NULL AS STRING) as stratum_2_name, CAST(NULL AS STRING) as stratum_3_name, CAST(NULL AS STRING) as stratum_4_name, CAST(NULL AS STRING) as stratum_5_name, CAST('OBSERVATION' as STRING) as analysis_type union all select 900 as analysis_id, CAST('Number of persons with at least one drug era, by drug_concept_id' as STRING) as analysis_name, CAST('drug_concept_id' as STRING) as stratum_1_name, CAST(NULL AS STRING) as stratum_2_name, CAST(NULL AS STRING) as stratum_3_name, CAST(NULL AS STRING) as stratum_4_name, CAST(NULL AS STRING) as stratum_5_name, CAST('DRUG_ERA' as STRING) as analysis_type union all select 901 as analysis_id, CAST('Number of drug era records, by drug_concept_id' as STRING) as analysis_name, CAST('drug_concept_id' as STRING) as stratum_1_name, CAST(NULL AS STRING) as stratum_2_name, CAST(NULL AS STRING) as stratum_3_name, CAST(NULL AS STRING) as stratum_4_name, CAST(NULL AS STRING) as stratum_5_name, CAST('DRUG_ERA' as STRING) as analysis_type union all select 902 as analysis_id, CAST('Number of persons by drug era start month, by drug_concept_id' as STRING) as analysis_name, CAST('drug_concept_id' as STRING) as stratum_1_name, CAST('calendar month' as STRING) as stratum_2_name, CAST(NULL AS STRING) as stratum_3_name, CAST(NULL AS STRING) as stratum_4_name, CAST(NULL AS STRING) as stratum_5_name, CAST('DRUG_ERA' as STRING) as analysis_type union all select 903 as analysis_id, CAST('Number of distinct drug era concepts per person' as STRING) as analysis_name, CAST(NULL AS STRING) as stratum_1_name, CAST(NULL AS STRING) as stratum_2_name, CAST(NULL AS STRING) as stratum_3_name, CAST(NULL AS STRING) as stratum_4_name, CAST(NULL AS STRING) as stratum_5_name, CAST('DRUG_ERA' as STRING) as analysis_type union all select 904 as analysis_id, CAST('Number of persons with at least one drug era, by drug_concept_id by calendar year by gender by age decile' as STRING) as analysis_name, CAST('drug_concept_id' as STRING) as stratum_1_name, CAST('calendar year' as STRING) as stratum_2_name, CAST('gender_concept_id' as STRING) as stratum_3_name, CAST('age decile' as STRING) as stratum_4_name, CAST(NULL AS STRING) as stratum_5_name, CAST('DRUG_ERA' as STRING) as analysis_type union all select 906 as analysis_id, CAST('Distribution of age by drug_concept_id' as STRING) as analysis_name, CAST('drug_concept_id' as STRING) as stratum_1_name, CAST('gender_concept_id' as STRING) as stratum_2_name, CAST(NULL AS STRING) as stratum_3_name, CAST(NULL AS STRING) as stratum_4_name, CAST(NULL AS STRING) as stratum_5_name, CAST('DRUG_ERA' as STRING) as analysis_type union all select 907 as analysis_id, CAST('Distribution of drug era length, by drug_concept_id' as STRING) as analysis_name, CAST('drug_concept_id' as STRING) as stratum_1_name, CAST(NULL AS STRING) as stratum_2_name, CAST(NULL AS STRING) as stratum_3_name, CAST(NULL AS STRING) as stratum_4_name, CAST(NULL AS STRING) as stratum_5_name, CAST('DRUG_ERA' as STRING) as analysis_type union all select 908 as analysis_id, CAST('Number of drug eras without valid person' as STRING) as analysis_name, CAST(NULL AS STRING) as stratum_1_name, CAST(NULL AS STRING) as stratum_2_name, CAST(NULL AS STRING) as stratum_3_name, CAST(NULL AS STRING) as stratum_4_name, CAST(NULL AS STRING) as stratum_5_name, CAST('DRUG_ERA' as STRING) as analysis_type union all select 909 as analysis_id, CAST('Number of drug eras outside valid observation period' as STRING) as analysis_name, CAST(NULL AS STRING) as stratum_1_name, CAST(NULL AS STRING) as stratum_2_name, CAST(NULL AS STRING) as stratum_3_name, CAST(NULL AS STRING) as stratum_4_name, CAST(NULL AS STRING) as stratum_5_name, CAST('DRUG_ERA' as STRING) as analysis_type union all select 910 as analysis_id, CAST('Number of drug eras with end date < start date' as STRING) as analysis_name, CAST(NULL AS STRING) as stratum_1_name, CAST(NULL AS STRING) as stratum_2_name, CAST(NULL AS STRING) as stratum_3_name, CAST(NULL AS STRING) as stratum_4_name, CAST(NULL AS STRING) as stratum_5_name, CAST('DRUG_ERA' as STRING) as analysis_type union all select 920 as analysis_id, CAST('Number of drug era records by drug era start month' as STRING) as analysis_name, CAST('calendar month' as STRING) as stratum_1_name, CAST(NULL AS STRING) as stratum_2_name, CAST(NULL AS STRING) as stratum_3_name, CAST(NULL AS STRING) as stratum_4_name, CAST(NULL AS STRING) as stratum_5_name, CAST('DRUG_ERA' as STRING) as analysis_type union all select 1000 as analysis_id, CAST('Number of persons with at least one condition era, by condition_concept_id' as STRING) as analysis_name, CAST('condition_concept_id' as STRING) as stratum_1_name, CAST(NULL AS STRING) as stratum_2_name, CAST(NULL AS STRING) as stratum_3_name, CAST(NULL AS STRING) as stratum_4_name, CAST(NULL AS STRING) as stratum_5_name, CAST('CONDITION_ERA' as STRING) as analysis_type union all select 1001 as analysis_id, CAST('Number of condition era records, by condition_concept_id' as STRING) as analysis_name, CAST('condition_concept_id' as STRING) as stratum_1_name, CAST(NULL AS STRING) as stratum_2_name, CAST(NULL AS STRING) as stratum_3_name, CAST(NULL AS STRING) as stratum_4_name, CAST(NULL AS STRING) as stratum_5_name, CAST('CONDITION_ERA' as STRING) as analysis_type union all select 1002 as analysis_id, CAST('Number of persons by condition era start month, by condition_concept_id' as STRING) as analysis_name, CAST('condition_concept_id' as STRING) as stratum_1_name, CAST('calendar month' as STRING) as stratum_2_name, CAST(NULL AS STRING) as stratum_3_name, CAST(NULL AS STRING) as stratum_4_name, CAST(NULL AS STRING) as stratum_5_name, CAST('CONDITION_ERA' as STRING) as analysis_type union all select 1003 as analysis_id, CAST('Number of distinct condition era concepts per person' as STRING) as analysis_name, CAST(NULL AS STRING) as stratum_1_name, CAST(NULL AS STRING) as stratum_2_name, CAST(NULL AS STRING) as stratum_3_name, CAST(NULL AS STRING) as stratum_4_name, CAST(NULL AS STRING) as stratum_5_name, CAST('CONDITION_ERA' as STRING) as analysis_type union all select 1004 as analysis_id, CAST('Number of persons with at least one condition era, by condition_concept_id by calendar year by gender by age decile' as STRING) as analysis_name, CAST('condition_concept_id' as STRING) as stratum_1_name, CAST('calendar year' as STRING) as stratum_2_name, CAST('gender_concept_id' as STRING) as stratum_3_name, CAST('age decile' as STRING) as stratum_4_name, CAST(NULL AS STRING) as stratum_5_name, CAST('CONDITION_ERA' as STRING) as analysis_type union all select 1006 as analysis_id, CAST('Distribution of age by condition_concept_id' as STRING) as analysis_name, CAST('condition_concept_id' as STRING) as stratum_1_name, CAST('gender_concept_id' as STRING) as stratum_2_name, CAST(NULL AS STRING) as stratum_3_name, CAST(NULL AS STRING) as stratum_4_name, CAST(NULL AS STRING) as stratum_5_name, CAST('CONDITION_ERA' as STRING) as analysis_type union all select 1007 as analysis_id, CAST('Distribution of condition era length, by condition_concept_id' as STRING) as analysis_name, CAST('condition_concept_id' as STRING) as stratum_1_name, CAST(NULL AS STRING) as stratum_2_name, CAST(NULL AS STRING) as stratum_3_name, CAST(NULL AS STRING) as stratum_4_name, CAST(NULL AS STRING) as stratum_5_name, CAST('CONDITION_ERA' as STRING) as analysis_type union all select 1008 as analysis_id, CAST('Number of condition eras without valid person' as STRING) as analysis_name, CAST(NULL AS STRING) as stratum_1_name, CAST(NULL AS STRING) as stratum_2_name, CAST(NULL AS STRING) as stratum_3_name, CAST(NULL AS STRING) as stratum_4_name, CAST(NULL AS STRING) as stratum_5_name, CAST('CONDITION_ERA' as STRING) as analysis_type union all select 1009 as analysis_id, CAST('Number of condition eras outside valid observation period' as STRING) as analysis_name, CAST(NULL AS STRING) as stratum_1_name, CAST(NULL AS STRING) as stratum_2_name, CAST(NULL AS STRING) as stratum_3_name, CAST(NULL AS STRING) as stratum_4_name, CAST(NULL AS STRING) as stratum_5_name, CAST('CONDITION_ERA' as STRING) as analysis_type union all select 1010 as analysis_id, CAST('Number of condition eras with end date < start date' as STRING) as analysis_name, CAST(NULL AS STRING) as stratum_1_name, CAST(NULL AS STRING) as stratum_2_name, CAST(NULL AS STRING) as stratum_3_name, CAST(NULL AS STRING) as stratum_4_name, CAST(NULL AS STRING) as stratum_5_name, CAST('CONDITION_ERA' as STRING) as analysis_type union all select 1020 as analysis_id, CAST('Number of condition era records by condition era start month' as STRING) as analysis_name, CAST('calendar month' as STRING) as stratum_1_name, CAST(NULL AS STRING) as stratum_2_name, CAST(NULL AS STRING) as stratum_3_name, CAST(NULL AS STRING) as stratum_4_name, CAST(NULL AS STRING) as stratum_5_name, CAST('CONDITION_ERA' as STRING) as analysis_type union all select 1100 as analysis_id, CAST('Number of persons by location 3-digit zip' as STRING) as analysis_name, CAST('3-digit zip' as STRING) as stratum_1_name, CAST(NULL AS STRING) as stratum_2_name, CAST(NULL AS STRING) as stratum_3_name, CAST(NULL AS STRING) as stratum_4_name, CAST(NULL AS STRING) as stratum_5_name, CAST('LOCATION' as STRING) as analysis_type union all select 1101 as analysis_id, CAST('Number of persons by location state' as STRING) as analysis_name, CAST('state' as STRING) as stratum_1_name, CAST(NULL AS STRING) as stratum_2_name, CAST(NULL AS STRING) as stratum_3_name, CAST(NULL AS STRING) as stratum_4_name, CAST(NULL AS STRING) as stratum_5_name, CAST('LOCATION' as STRING) as analysis_type union all select 1200 as analysis_id, CAST('Number of persons by place of service' as STRING) as analysis_name, CAST('place_of_service_concept_id' as STRING) as stratum_1_name, CAST(NULL AS STRING) as stratum_2_name, CAST(NULL AS STRING) as stratum_3_name, CAST(NULL AS STRING) as stratum_4_name, CAST(NULL AS STRING) as stratum_5_name, CAST('CARE_SITE' as STRING) as analysis_type union all select 1201 as analysis_id, CAST('Number of visits by place of service' as STRING) as analysis_name, CAST('place_of_service_concept_id' as STRING) as stratum_1_name, CAST(NULL AS STRING) as stratum_2_name, CAST(NULL AS STRING) as stratum_3_name, CAST(NULL AS STRING) as stratum_4_name, CAST(NULL AS STRING) as stratum_5_name, CAST('CARE_SITE' as STRING) as analysis_type union all select 1300 as analysis_id, CAST('Number of persons with at least one measurement occurrence, by measurement_concept_id' as STRING) as analysis_name, CAST('measurement_concept_id' as STRING) as stratum_1_name, CAST(NULL AS STRING) as stratum_2_name, CAST(NULL AS STRING) as stratum_3_name, CAST(NULL AS STRING) as stratum_4_name, CAST(NULL AS STRING) as stratum_5_name, CAST('MEASUREMENT' as STRING) as analysis_type union all select 1301 as analysis_id, CAST('Number of measurement occurrence records, by measurement_concept_id' as STRING) as analysis_name, CAST('measurement_concept_id' as STRING) as stratum_1_name, CAST(NULL AS STRING) as stratum_2_name, CAST(NULL AS STRING) as stratum_3_name, CAST(NULL AS STRING) as stratum_4_name, CAST(NULL AS STRING) as stratum_5_name, CAST('MEASUREMENT' as STRING) as analysis_type union all select 1302 as analysis_id, CAST('Number of persons by measurement occurrence start month, by measurement_concept_id' as STRING) as analysis_name, CAST('measurement_concept_id' as STRING) as stratum_1_name, CAST('calendar month' as STRING) as stratum_2_name, CAST(NULL AS STRING) as stratum_3_name, CAST(NULL AS STRING) as stratum_4_name, CAST(NULL AS STRING) as stratum_5_name, CAST('MEASUREMENT' as STRING) as analysis_type union all select 1303 as analysis_id, CAST('Number of distinct measurement occurrence concepts per person' as STRING) as analysis_name, CAST(NULL AS STRING) as stratum_1_name, CAST(NULL AS STRING) as stratum_2_name, CAST(NULL AS STRING) as stratum_3_name, CAST(NULL AS STRING) as stratum_4_name, CAST(NULL AS STRING) as stratum_5_name, CAST('MEASUREMENT' as STRING) as analysis_type union all select 1304 as analysis_id, CAST('Number of persons with at least one measurement occurrence, by measurement_concept_id by calendar year by gender by age decile' as STRING) as analysis_name, CAST('measurement_concept_id' as STRING) as stratum_1_name, CAST('calendar year' as STRING) as stratum_2_name, CAST('gender_concept_id' as STRING) as stratum_3_name, CAST('age decile' as STRING) as stratum_4_name, CAST(NULL AS STRING) as stratum_5_name, CAST('MEASUREMENT' as STRING) as analysis_type union all select 1305 as analysis_id, CAST('Number of measurement occurrence records, by measurement_concept_id by measurement_type_concept_id' as STRING) as analysis_name, CAST('measurement_concept_id' as STRING) as stratum_1_name, CAST('measurement_type_concept_id' as STRING) as stratum_2_name, CAST(NULL AS STRING) as stratum_3_name, CAST(NULL AS STRING) as stratum_4_name, CAST(NULL AS STRING) as stratum_5_name, CAST('MEASUREMENT' as STRING) as analysis_type union all select 1306 as analysis_id, CAST('Distribution of age by measurement_concept_id' as STRING) as analysis_name, CAST('measurement_concept_id' as STRING) as stratum_1_name, CAST('gender_concept_id' as STRING) as stratum_2_name, CAST(NULL AS STRING) as stratum_3_name, CAST(NULL AS STRING) as stratum_4_name, CAST(NULL AS STRING) as stratum_5_name, CAST('MEASUREMENT' as STRING) as analysis_type union all select 1307 as analysis_id, CAST('Number of measurement occurrence records, by measurement_concept_id and unit_concept_id' as STRING) as analysis_name, CAST('measurement_concept_id' as STRING) as stratum_1_name, CAST('unit_concept_id' as STRING) as stratum_2_name, CAST(NULL AS STRING) as stratum_3_name, CAST(NULL AS STRING) as stratum_4_name, CAST(NULL AS STRING) as stratum_5_name, CAST('MEASUREMENT' as STRING) as analysis_type union all select 1309 as analysis_id, CAST('Number of measurement records with invalid person_id' as STRING) as analysis_name, CAST(NULL AS STRING) as stratum_1_name, CAST(NULL AS STRING) as stratum_2_name, CAST(NULL AS STRING) as stratum_3_name, CAST(NULL AS STRING) as stratum_4_name, CAST(NULL AS STRING) as stratum_5_name, CAST('MEASUREMENT' as STRING) as analysis_type union all select 1310 as analysis_id, CAST('Number of measurement records outside valid measurement period' as STRING) as analysis_name, CAST(NULL AS STRING) as stratum_1_name, CAST(NULL AS STRING) as stratum_2_name, CAST(NULL AS STRING) as stratum_3_name, CAST(NULL AS STRING) as stratum_4_name, CAST(NULL AS STRING) as stratum_5_name, CAST('MEASUREMENT' as STRING) as analysis_type union all select 1312 as analysis_id, CAST('Number of measurement records with invalid provider_id' as STRING) as analysis_name, CAST(NULL AS STRING) as stratum_1_name, CAST(NULL AS STRING) as stratum_2_name, CAST(NULL AS STRING) as stratum_3_name, CAST(NULL AS STRING) as stratum_4_name, CAST(NULL AS STRING) as stratum_5_name, CAST('MEASUREMENT' as STRING) as analysis_type union all select 1313 as analysis_id, CAST('Number of measurement records with invalid visit_id' as STRING) as analysis_name, CAST(NULL AS STRING) as stratum_1_name, CAST(NULL AS STRING) as stratum_2_name, CAST(NULL AS STRING) as stratum_3_name, CAST(NULL AS STRING) as stratum_4_name, CAST(NULL AS STRING) as stratum_5_name, CAST('MEASUREMENT' as STRING) as analysis_type union all select 1314 as analysis_id, CAST('Number of measurement records with no value (numeric, string, or concept)' as STRING) as analysis_name, CAST(NULL AS STRING) as stratum_1_name, CAST(NULL AS STRING) as stratum_2_name, CAST(NULL AS STRING) as stratum_3_name, CAST(NULL AS STRING) as stratum_4_name, CAST(NULL AS STRING) as stratum_5_name, CAST('MEASUREMENT' as STRING) as analysis_type union all select 1315 as analysis_id, CAST('Distribution of numeric values, by measurement_concept_id and unit_concept_id' as STRING) as analysis_name, CAST(NULL AS STRING) as stratum_1_name, CAST(NULL AS STRING) as stratum_2_name, CAST(NULL AS STRING) as stratum_3_name, CAST(NULL AS STRING) as stratum_4_name, CAST(NULL AS STRING) as stratum_5_name, CAST('MEASUREMENT' as STRING) as analysis_type union all select 1316 as analysis_id, CAST('Distribution of low range, by measurement_concept_id and unit_concept_id' as STRING) as analysis_name, CAST(NULL AS STRING) as stratum_1_name, CAST(NULL AS STRING) as stratum_2_name, CAST(NULL AS STRING) as stratum_3_name, CAST(NULL AS STRING) as stratum_4_name, CAST(NULL AS STRING) as stratum_5_name, CAST('MEASUREMENT' as STRING) as analysis_type union all select 1317 as analysis_id, CAST('Distribution of high range, by measurement_concept_id and unit_concept_id' as STRING) as analysis_name, CAST(NULL AS STRING) as stratum_1_name, CAST(NULL AS STRING) as stratum_2_name, CAST(NULL AS STRING) as stratum_3_name, CAST(NULL AS STRING) as stratum_4_name, CAST(NULL AS STRING) as stratum_5_name, CAST('MEASUREMENT' as STRING) as analysis_type union all select 1318 as analysis_id, CAST('Number of measurement records below/within/above normal range, by measurement_concept_id and unit_concept_id' as STRING) as analysis_name, CAST(NULL AS STRING) as stratum_1_name, CAST(NULL AS STRING) as stratum_2_name, CAST(NULL AS STRING) as stratum_3_name, CAST(NULL AS STRING) as stratum_4_name, CAST(NULL AS STRING) as stratum_5_name, CAST('MEASUREMENT' as STRING) as analysis_type union all select 1320 as analysis_id, CAST('Number of measurement records by measurement start month' as STRING) as analysis_name, CAST('calendar month' as STRING) as stratum_1_name, CAST(NULL AS STRING) as stratum_2_name, CAST(NULL AS STRING) as stratum_3_name, CAST(NULL AS STRING) as stratum_4_name, CAST(NULL AS STRING) as stratum_5_name, CAST('MEASUREMENT' as STRING) as analysis_type union all select 1700 as analysis_id, CAST('Number of records by cohort_definition_id' as STRING) as analysis_name, CAST('cohort_definition_id' as STRING) as stratum_1_name, CAST(NULL AS STRING) as stratum_2_name, CAST(NULL AS STRING) as stratum_3_name, CAST(NULL AS STRING) as stratum_4_name, CAST(NULL AS STRING) as stratum_5_name, CAST('COHORT' as STRING) as analysis_type union all select 1701 as analysis_id, CAST('Number of records with cohort end date < cohort start date' as STRING) as analysis_name, CAST(NULL AS STRING) as stratum_1_name, CAST(NULL AS STRING) as stratum_2_name, CAST(NULL AS STRING) as stratum_3_name, CAST(NULL AS STRING) as stratum_4_name, CAST(NULL AS STRING) as stratum_5_name, CAST('COHORT' as STRING) as analysis_type union all select 1800 as analysis_id, CAST('Number of persons by age, with age at cohort start' as STRING) as analysis_name, CAST('age' as STRING) as stratum_1_name, CAST(NULL AS STRING) as stratum_2_name, CAST(NULL AS STRING) as stratum_3_name, CAST(NULL AS STRING) as stratum_4_name, CAST(NULL AS STRING) as stratum_5_name, CAST('COHORT_SPECIFIC_ANALYSES' as STRING) as analysis_type union all select 1801 as analysis_id, CAST('Distribution of age at cohort start' as STRING) as analysis_name, CAST(NULL AS STRING) as stratum_1_name, CAST(NULL AS STRING) as stratum_2_name, CAST(NULL AS STRING) as stratum_3_name, CAST(NULL AS STRING) as stratum_4_name, CAST(NULL AS STRING) as stratum_5_name, CAST('COHORT_SPECIFIC_ANALYSES' as STRING) as analysis_type union all select 1802 as analysis_id, CAST('Distribution of age at cohort start by gender' as STRING) as analysis_name, CAST('gender_concept_id' as STRING) as stratum_1_name, CAST(NULL AS STRING) as stratum_2_name, CAST(NULL AS STRING) as stratum_3_name, CAST(NULL AS STRING) as stratum_4_name, CAST(NULL AS STRING) as stratum_5_name, CAST('COHORT_SPECIFIC_ANALYSES' as STRING) as analysis_type union all select 1803 as analysis_id, CAST('Distribution of age at cohort start by cohort start year' as STRING) as analysis_name, CAST('calendar year' as STRING) as stratum_1_name, CAST(NULL AS STRING) as stratum_2_name, CAST(NULL AS STRING) as stratum_3_name, CAST(NULL AS STRING) as stratum_4_name, CAST(NULL AS STRING) as stratum_5_name, CAST('COHORT_SPECIFIC_ANALYSES' as STRING) as analysis_type union all select 1804 as analysis_id, CAST('Number of persons by duration from cohort start to cohort end, in 30d increments' as STRING) as analysis_name, CAST('Cohort period length 30d increments' as STRING) as stratum_1_name, CAST(NULL AS STRING) as stratum_2_name, CAST(NULL AS STRING) as stratum_3_name, CAST(NULL AS STRING) as stratum_4_name, CAST(NULL AS STRING) as stratum_5_name, CAST('COHORT_SPECIFIC_ANALYSES' as STRING) as analysis_type union all select 1805 as analysis_id, CAST('Number of persons by duration from observation start to cohort start, in 30d increments' as STRING) as analysis_name, CAST('Baseline period length 30d increments' as STRING) as stratum_1_name, CAST(NULL AS STRING) as stratum_2_name, CAST(NULL AS STRING) as stratum_3_name, CAST(NULL AS STRING) as stratum_4_name, CAST(NULL AS STRING) as stratum_5_name, CAST('COHORT_SPECIFIC_ANALYSES' as STRING) as analysis_type union all select 1806 as analysis_id, CAST('Number of persons by duration from cohort start to observation end, in 30d increments' as STRING) as analysis_name, CAST('Follow-up period length 30d increments' as STRING) as stratum_1_name, CAST(NULL AS STRING) as stratum_2_name, CAST(NULL AS STRING) as stratum_3_name, CAST(NULL AS STRING) as stratum_4_name, CAST(NULL AS STRING) as stratum_5_name, CAST('COHORT_SPECIFIC_ANALYSES' as STRING) as analysis_type union all select 1807 as analysis_id, CAST('Number of persons by duration from cohort end to observation end, in 30d increments' as STRING) as analysis_name, CAST('Post-cohort period length 30d increments' as STRING) as stratum_1_name, CAST(NULL AS STRING) as stratum_2_name, CAST(NULL AS STRING) as stratum_3_name, CAST(NULL AS STRING) as stratum_4_name, CAST(NULL AS STRING) as stratum_5_name, CAST('COHORT_SPECIFIC_ANALYSES' as STRING) as analysis_type union all select 1808 as analysis_id, CAST('Distribution of duration (days) from cohort start to cohort end' as STRING) as analysis_name, CAST(NULL AS STRING) as stratum_1_name, CAST(NULL AS STRING) as stratum_2_name, CAST(NULL AS STRING) as stratum_3_name, CAST(NULL AS STRING) as stratum_4_name, CAST(NULL AS STRING) as stratum_5_name, CAST('COHORT_SPECIFIC_ANALYSES' as STRING) as analysis_type union all select 1809 as analysis_id, CAST('Distribution of duration (days) from cohort start to cohort end, by gender' as STRING) as analysis_name, CAST('gender_concept_id' as STRING) as stratum_1_name, CAST(NULL AS STRING) as stratum_2_name, CAST(NULL AS STRING) as stratum_3_name, CAST(NULL AS STRING) as stratum_4_name, CAST(NULL AS STRING) as stratum_5_name, CAST('COHORT_SPECIFIC_ANALYSES' as STRING) as analysis_type union all select 1810 as analysis_id, CAST('Distribution of duration (days) from cohort start to cohort end, by age decile' as STRING) as analysis_name, CAST('age decile' as STRING) as stratum_1_name, CAST(NULL AS STRING) as stratum_2_name, CAST(NULL AS STRING) as stratum_3_name, CAST(NULL AS STRING) as stratum_4_name, CAST(NULL AS STRING) as stratum_5_name, CAST('COHORT_SPECIFIC_ANALYSES' as STRING) as analysis_type union all select 1811 as analysis_id, CAST('Distribution of duration (days) from observation start to cohort start' as STRING) as analysis_name, CAST(NULL AS STRING) as stratum_1_name, CAST(NULL AS STRING) as stratum_2_name, CAST(NULL AS STRING) as stratum_3_name, CAST(NULL AS STRING) as stratum_4_name, CAST(NULL AS STRING) as stratum_5_name, CAST('COHORT_SPECIFIC_ANALYSES' as STRING) as analysis_type union all select 1812 as analysis_id, CAST('Distribution of duration (days) from cohort start to observation end' as STRING) as analysis_name, CAST(NULL AS STRING) as stratum_1_name, CAST(NULL AS STRING) as stratum_2_name, CAST(NULL AS STRING) as stratum_3_name, CAST(NULL AS STRING) as stratum_4_name, CAST(NULL AS STRING) as stratum_5_name, CAST('COHORT_SPECIFIC_ANALYSES' as STRING) as analysis_type union all select 1813 as analysis_id, CAST('Distribution of duration (days) from cohort end to observation end' as STRING) as analysis_name, CAST(NULL AS STRING) as stratum_1_name, CAST(NULL AS STRING) as stratum_2_name, CAST(NULL AS STRING) as stratum_3_name, CAST(NULL AS STRING) as stratum_4_name, CAST(NULL AS STRING) as stratum_5_name, CAST('COHORT_SPECIFIC_ANALYSES' as STRING) as analysis_type union all select 1814 as analysis_id, CAST('Number of persons by cohort start year by gender by age decile' as STRING) as analysis_name, CAST('calendar year' as STRING) as stratum_1_name, CAST('gender_concept_id' as STRING) as stratum_2_name, CAST('age decile' as STRING) as stratum_3_name, CAST(NULL AS STRING) as stratum_4_name, CAST(NULL AS STRING) as stratum_5_name, CAST('COHORT_SPECIFIC_ANALYSES' as STRING) as analysis_type union all select 1815 as analysis_id, CAST('Number of persons by cohort start month' as STRING) as analysis_name, CAST('calendar month' as STRING) as stratum_1_name, CAST(NULL AS STRING) as stratum_2_name, CAST(NULL AS STRING) as stratum_3_name, CAST(NULL AS STRING) as stratum_4_name, CAST(NULL AS STRING) as stratum_5_name, CAST('COHORT_SPECIFIC_ANALYSES' as STRING) as analysis_type union all select 1816 as analysis_id, CAST('Number of persons by number of cohort periods' as STRING) as analysis_name, CAST('number of cohort periods' as STRING) as stratum_1_name, CAST(NULL AS STRING) as stratum_2_name, CAST(NULL AS STRING) as stratum_3_name, CAST(NULL AS STRING) as stratum_4_name, CAST(NULL AS STRING) as stratum_5_name, CAST('COHORT_SPECIFIC_ANALYSES' as STRING) as analysis_type union all select 1820 as analysis_id, CAST('Number of persons by duration from cohort start to first occurrence of condition occurrence, by condition_concept_id' as STRING) as analysis_name, CAST('condition_concept_id' as STRING) as stratum_1_name, CAST('time-to-event 30d increments' as STRING) as stratum_2_name, CAST(NULL AS STRING) as stratum_3_name, CAST(NULL AS STRING) as stratum_4_name, CAST(NULL AS STRING) as stratum_5_name, CAST('COHORT_SPECIFIC_ANALYSES' as STRING) as analysis_type union all select 1821 as analysis_id, CAST('Number of events by duration from cohort start to all occurrences of condition occurrence, by condition_concept_id' as STRING) as analysis_name, CAST('condition_concept_id' as STRING) as stratum_1_name, CAST('time-to-event 30d increments' as STRING) as stratum_2_name, CAST(NULL AS STRING) as stratum_3_name, CAST(NULL AS STRING) as stratum_4_name, CAST(NULL AS STRING) as stratum_5_name, CAST('COHORT_SPECIFIC_ANALYSES' as STRING) as analysis_type union all select 1830 as analysis_id, CAST('Number of persons by duration from cohort start to first occurrence of procedure occurrence, by procedure_concept_id' as STRING) as analysis_name, CAST('procedure_concept_id' as STRING) as stratum_1_name, CAST('time-to-event 30d increments' as STRING) as stratum_2_name, CAST(NULL AS STRING) as stratum_3_name, CAST(NULL AS STRING) as stratum_4_name, CAST(NULL AS STRING) as stratum_5_name, CAST('COHORT_SPECIFIC_ANALYSES' as STRING) as analysis_type union all select 1831 as analysis_id, CAST('Number of events by duration from cohort start to all occurrences of procedure occurrence, by procedure_concept_id' as STRING) as analysis_name, CAST('procedure_concept_id' as STRING) as stratum_1_name, CAST('time-to-event 30d increments' as STRING) as stratum_2_name, CAST(NULL AS STRING) as stratum_3_name, CAST(NULL AS STRING) as stratum_4_name, CAST(NULL AS STRING) as stratum_5_name, CAST('COHORT_SPECIFIC_ANALYSES' as STRING) as analysis_type union all select 1840 as analysis_id, CAST('Number of persons by duration from cohort start to first occurrence of drug exposure, by drug_concept_id' as STRING) as analysis_name, CAST('drug_concept_id' as STRING) as stratum_1_name, CAST('time-to-event 30d increments' as STRING) as stratum_2_name, CAST(NULL AS STRING) as stratum_3_name, CAST(NULL AS STRING) as stratum_4_name, CAST(NULL AS STRING) as stratum_5_name, CAST('COHORT_SPECIFIC_ANALYSES' as STRING) as analysis_type union all select 1841 as analysis_id, CAST('Number of events by duration from cohort start to all occurrences of drug exposure, by drug_concept_id' as STRING) as analysis_name, CAST('drug_concept_id' as STRING) as stratum_1_name, CAST('time-to-event 30d increments' as STRING) as stratum_2_name, CAST(NULL AS STRING) as stratum_3_name, CAST(NULL AS STRING) as stratum_4_name, CAST(NULL AS STRING) as stratum_5_name, CAST('COHORT_SPECIFIC_ANALYSES' as STRING) as analysis_type union all select 1850 as analysis_id, CAST('Number of persons by duration from cohort start to first occurrence of observation, by observation_concept_id' as STRING) as analysis_name, CAST('observation_concept_id' as STRING) as stratum_1_name, CAST('time-to-event 30d increments' as STRING) as stratum_2_name, CAST(NULL AS STRING) as stratum_3_name, CAST(NULL AS STRING) as stratum_4_name, CAST(NULL AS STRING) as stratum_5_name, CAST('COHORT_SPECIFIC_ANALYSES' as STRING) as analysis_type union all select 1851 as analysis_id, CAST('Number of events by duration from cohort start to all occurrences of observation, by observation_concept_id' as STRING) as analysis_name, CAST('observation_concept_id' as STRING) as stratum_1_name, CAST('time-to-event 30d increments' as STRING) as stratum_2_name, CAST(NULL AS STRING) as stratum_3_name, CAST(NULL AS STRING) as stratum_4_name, CAST(NULL AS STRING) as stratum_5_name, CAST('COHORT_SPECIFIC_ANALYSES' as STRING) as analysis_type union all select 1860 as analysis_id, CAST('Number of persons by duration from cohort start to first occurrence of condition era, by condition_concept_id' as STRING) as analysis_name, CAST('condition_concept_id' as STRING) as stratum_1_name, CAST('time-to-event 30d increments' as STRING) as stratum_2_name, CAST(NULL AS STRING) as stratum_3_name, CAST(NULL AS STRING) as stratum_4_name, CAST(NULL AS STRING) as stratum_5_name, CAST('COHORT_SPECIFIC_ANALYSES' as STRING) as analysis_type union all select 1861 as analysis_id, CAST('Number of events by duration from cohort start to all occurrences of condition era, by condition_concept_id' as STRING) as analysis_name, CAST('condition_concept_id' as STRING) as stratum_1_name, CAST('time-to-event 30d increments' as STRING) as stratum_2_name, CAST(NULL AS STRING) as stratum_3_name, CAST(NULL AS STRING) as stratum_4_name, CAST(NULL AS STRING) as stratum_5_name, CAST('COHORT_SPECIFIC_ANALYSES' as STRING) as analysis_type union all select 1870 as analysis_id, CAST('Number of persons by duration from cohort start to first occurrence of drug era, by drug_concept_id' as STRING) as analysis_name, CAST('drug_concept_id' as STRING) as stratum_1_name, CAST('time-to-event 30d increments' as STRING) as stratum_2_name, CAST(NULL AS STRING) as stratum_3_name, CAST(NULL AS STRING) as stratum_4_name, CAST(NULL AS STRING) as stratum_5_name, CAST('COHORT_SPECIFIC_ANALYSES' as STRING) as analysis_type union all select 1871 as analysis_id, CAST('Number of events by duration from cohort start to all occurrences of drug era, by drug_concept_id' as STRING) as analysis_name, CAST('drug_concept_id' as STRING) as stratum_1_name, CAST('time-to-event 30d increments' as STRING) as stratum_2_name, CAST(NULL AS STRING) as stratum_3_name, CAST(NULL AS STRING) as stratum_4_name, CAST(NULL AS STRING) as stratum_5_name, CAST('COHORT_SPECIFIC_ANALYSES' as STRING) as analysis_type union all select 4000 as analysis_id, CAST('Distribution of observation period days by period_id in the 365 days prior to first cohort_start_date' as STRING) as analysis_name, NULL as stratum_1_name, NULL as stratum_2_name, NULL as stratum_3_name, NULL as stratum_4_name, NULL as stratum_5_name, CAST('HEALTHCARE_UTILIZATION' as STRING) as analysis_type union all select 4001 as analysis_id, CAST('Number of subjects with visits by period_id, by visit_concept_id, by visit_type_concept_id in the 365d prior to first cohort start date' as STRING) as analysis_name, NULL as stratum_1_name, NULL as stratum_2_name, NULL as stratum_3_name, NULL as stratum_4_name, NULL as stratum_5_name, CAST('HEALTHCARE_UTILIZATION' as STRING) as analysis_type union all select 4002 as analysis_id, CAST('Distribution of number of visit occurrence records per subject by period_id, by visit_concept_id, by visit_type_concept_id in 365d prior to cohort start date' as STRING) as analysis_name, NULL as stratum_1_name, NULL as stratum_2_name, NULL as stratum_3_name, NULL as stratum_4_name, NULL as stratum_5_name, CAST('HEALTHCARE_UTILIZATION' as STRING) as analysis_type union all select 4003 as analysis_id, CAST('Distribution of number of visit dates per subject by period_id, by visit_concept_id, by visit_type_concept_id in 365d prior to first cohort start date' as STRING) as analysis_name, NULL as stratum_1_name, NULL as stratum_2_name, NULL as stratum_3_name, NULL as stratum_4_name, NULL as stratum_5_name, CAST('HEALTHCARE_UTILIZATION' as STRING) as analysis_type union all select 4003 as analysis_id, CAST('Distribution of number of visit dates per subject by period_id, by visit_concept_id, by visit_type_concept_id in 365d prior to first cohort start date' as STRING) as analysis_name, NULL as stratum_1_name, NULL as stratum_2_name, NULL as stratum_3_name, NULL as stratum_4_name, NULL as stratum_5_name, CAST('HEALTHCARE_UTILIZATION' as STRING) as analysis_type union all select 4004 as analysis_id, CAST('Distribution of number of care_site+visit dates per subject by period_id, by visit_concept_id, by visit_type_concept_id in 365d prior to first cohort start date' as STRING) as analysis_name, NULL as stratum_1_name, NULL as stratum_2_name, NULL as stratum_3_name, NULL as stratum_4_name, NULL as stratum_5_name, CAST('HEALTHCARE_UTILIZATION' as STRING) as analysis_type union all select 4005 as analysis_id, CAST('Distribution of length of stay for inpatient visits per subject by period_id, by visit_concept_id, by visit_type_concept_id in the 365 days prior to first cohort_start_date' as STRING) as analysis_name, NULL as stratum_1_name, NULL as stratum_2_name, NULL as stratum_3_name, NULL as stratum_4_name, NULL as stratum_5_name, CAST('HEALTHCARE_UTILIZATION' as STRING) as analysis_type union all select 4006 as analysis_id, CAST('Distribution of observation period days per subject, by period_id during cohort period' as STRING) as analysis_name, NULL as stratum_1_name, NULL as stratum_2_name, NULL as stratum_3_name, NULL as stratum_4_name, NULL as stratum_5_name, CAST('HEALTHCARE_UTILIZATION' as STRING) as analysis_type union all select 4007 as analysis_id, CAST('Number of subjects with visits by period_id, by visit_concept_id, by visit_type_concept_id during the cohort period' as STRING) as analysis_name, NULL as stratum_1_name, NULL as stratum_2_name, NULL as stratum_3_name, NULL as stratum_4_name, NULL as stratum_5_name, CAST('HEALTHCARE_UTILIZATION' as STRING) as analysis_type union all select 4008 as analysis_id, CAST('Distribution of number of visit occurrence records per subject by period_id, by visit_concept_id, by visit_type_concept_id during the cohort period' as STRING) as analysis_name, NULL as stratum_1_name, NULL as stratum_2_name, NULL as stratum_3_name, NULL as stratum_4_name, NULL as stratum_5_name, CAST('HEALTHCARE_UTILIZATION' as STRING) as analysis_type union all select 4009 as analysis_id, CAST('Distribution of number of visit dates per subject by period_id, by visit_concept_id, by visit_type_concept_id during the cohort period' as STRING) as analysis_name, NULL as stratum_1_name, NULL as stratum_2_name, NULL as stratum_3_name, NULL as stratum_4_name, NULL as stratum_5_name, CAST('HEALTHCARE_UTILIZATION' as STRING) as analysis_type union all select 4010 as analysis_id, CAST('Distribution of number of care_site+visit dates per subject by period_id, by visit_concept_id, by visit_type_concept_id during the cohort period' as STRING) as analysis_name, NULL as stratum_1_name, NULL as stratum_2_name, NULL as stratum_3_name, NULL as stratum_4_name, NULL as stratum_5_name, CAST('HEALTHCARE_UTILIZATION' as STRING) as analysis_type union all select 4011 as analysis_id, CAST('Distribution of length of stay for inpatient visits per subject by period_id, by visit_concept_id, by visit_type_concept_id during cohort period' as STRING) as analysis_name, NULL as stratum_1_name, NULL as stratum_2_name, NULL as stratum_3_name, NULL as stratum_4_name, NULL as stratum_5_name, CAST('HEALTHCARE_UTILIZATION' as STRING) as analysis_type union all select 4012 as analysis_id, CAST('Number of subjects with Drug Exposure by period_id, by drug_concept_id, by drug_type_concept_id in the 365d prior to first cohort start date' as STRING) as analysis_name, NULL as stratum_1_name, NULL as stratum_2_name, NULL as stratum_3_name, NULL as stratum_4_name, NULL as stratum_5_name, CAST('HEALTHCARE_UTILIZATION' as STRING) as analysis_type union all select 4013 as analysis_id, CAST('Distribution of number of Drug Exposure records per subject, by period_id, by drug_concept_id in 365d prior to first cohort start date' as STRING) as analysis_name, NULL as stratum_1_name, NULL as stratum_2_name, NULL as stratum_3_name, NULL as stratum_4_name, NULL as stratum_5_name, CAST('HEALTHCARE_UTILIZATION' as STRING) as analysis_type union all select 4014 as analysis_id, CAST('Distribution of greater than 0 drug day supply per subject by period_id, by drug_concept_id in the 365d prior to first cohort start date' as STRING) as analysis_name, NULL as stratum_1_name, NULL as stratum_2_name, NULL as stratum_3_name, NULL as stratum_4_name, NULL as stratum_5_name, CAST('HEALTHCARE_UTILIZATION' as STRING) as analysis_type union all select 4015 as analysis_id, CAST('Distribution of greater than 0 drug quantity per subject by period_id, by drug_concept_id in the 365d prior to first cohort start date' as STRING) as analysis_name, NULL as stratum_1_name, NULL as stratum_2_name, NULL as stratum_3_name, NULL as stratum_4_name, NULL as stratum_5_name, CAST('HEALTHCARE_UTILIZATION' as STRING) as analysis_type union all select 4016 as analysis_id, CAST('Number of subjects with Drug Exposure by period_id, by drug_concept_id, by drug_type_concept_id during the cohort period' as STRING) as analysis_name, NULL as stratum_1_name, NULL as stratum_2_name, NULL as stratum_3_name, NULL as stratum_4_name, NULL as stratum_5_name, CAST('HEALTHCARE_UTILIZATION' as STRING) as analysis_type union all select 4017 as analysis_id, CAST('Distribution of number of Drug Exposure records per subject, by period_id, by drug_concept_id during the cohort period' as STRING) as analysis_name, NULL as stratum_1_name, NULL as stratum_2_name, NULL as stratum_3_name, NULL as stratum_4_name, NULL as stratum_5_name, CAST('HEALTHCARE_UTILIZATION' as STRING) as analysis_type union all select 4018 as analysis_id, CAST('Distribution of greater than 0 drug day supply per subject by period_id, by drug_concept_id during the cohort period' as STRING) as analysis_name, NULL as stratum_1_name, NULL as stratum_2_name, NULL as stratum_3_name, NULL as stratum_4_name, NULL as stratum_5_name, CAST('HEALTHCARE_UTILIZATION' as STRING) as analysis_type union all select 4019 as analysis_id, CAST('Distribution of greater than 0 drug quantity per subject by period_id, by drug_concept_id during the cohort period' as STRING) as analysis_name, NULL as stratum_1_name, NULL as stratum_2_name, NULL as stratum_3_name, NULL as stratum_4_name, NULL as stratum_5_name, CAST('HEALTHCARE_UTILIZATION' as STRING) as analysis_type union all select 4020 as analysis_id, CAST('Distribution of greater than 0 US$ cost per subject by period_id, by visit_concept_id, by visit_type_concept_id, by cost_concept_id, by cost_type_concept_id in the 365d prior to first cohort start date' as STRING) as analysis_name, NULL as stratum_1_name, NULL as stratum_2_name, NULL as stratum_3_name, NULL as stratum_4_name, NULL as stratum_5_name, CAST('HEALTHCARE_UTILIZATION' as STRING) as analysis_type union all select 4021 as analysis_id, CAST('Distribution of greater than 0 US$ cost per subject by period_id, by visit_concept_id, by visit_type_concept_id, by cost_concept_id, by cost_type_concept_id during the cohort period' as STRING) as analysis_name, NULL as stratum_1_name, NULL as stratum_2_name, NULL as stratum_3_name, NULL as stratum_4_name, NULL as stratum_5_name, CAST('HEALTHCARE_UTILIZATION' as STRING) as analysis_type union all select 4022 as analysis_id, CAST('Distribution of greater than 0 US$ cost per subject by period_id, by drug_concept_id, by drug_type_concept_id, by cost_concept_id, by cost_type_concept_id in the 365d prior to first cohort start date' as STRING) as analysis_name, NULL as stratum_1_name, NULL as stratum_2_name, NULL as stratum_3_name, NULL as stratum_4_name, NULL as stratum_5_name, CAST('HEALTHCARE_UTILIZATION' as STRING) as analysis_type union all select 4023 as analysis_id, CAST('Distribution of greater than 0 US$ cost per subject by period_id, by drug_concept_id, by drug_type_concept_id, by cost_concept_id, by cost_type_concept_id, by cost_type_concept_id during the cohort period' as STRING) as analysis_name, NULL as stratum_1_name, NULL as stratum_2_name, NULL as stratum_3_name, NULL as stratum_4_name, NULL as stratum_5_name, CAST('HEALTHCARE_UTILIZATION' as STRING) as analysis_type ) UNION ALL (SELECT analysis_id,analysis_name,stratum_1_name,stratum_2_name,stratum_3_name,stratum_4_name,stratum_5_name,analysis_type FROM demo_cdm_ach_res.heracles_analysis )) INSERT OVERWRITE TABLE demo_cdm_ach_res.heracles_analysis SELECT * FROM insertion_temp num_affected_rows num_inserted_rows 210 210 CREATE TABLE demo_cdm_ach_tmp.f6rqimkudigits USING DELTA AS SELECT digits.n FROM ( select 0 as n union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9 ) digits CREATE TABLE demo_cdm_ach_tmp.f6rqimkugenerate_dates USING DELTA AS SELECT y1.n + (10*y10.n) + (100*y100.n) + (1000*y1000.n) AS d_years, mths.n as d_months FROM demo_cdm_ach_tmp.f6rqimkudigits y1, demo_cdm_ach_tmp.f6rqimkudigits y10, (select 0 n union all select 1 union all select 9) y100, (select 1 n union all select 2) y1000, (select 1 n union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9 union all select 10 union all select 11 union all select 12) mths where y1.n + (10*y10.n) + (100*y100.n) + (1000*y1000.n) >= 1900 and y1.n + (10*y10.n) + (100*y100.n) + (1000*y1000.n) < 2100 CREATE TABLE demo_cdm_ach_tmp.f6rqimkuyearly_dates USING DELTA AS SELECT to_date(cast(d_years as string) || '-' || cast(d_months as string) || '-' || cast(01 as string)) as generated_date FROM demo_cdm_ach_tmp.f6rqimkugenerate_dates where d_months = 1 CREATE TABLE demo_cdm_ach_tmp.f6rqimkumonthly_dates USING DELTA AS SELECT to_date(cast(d_years as string) || '-' || cast(d_months as string) || '-' || cast(01 as string)) as generated_date FROM demo_cdm_ach_tmp.f6rqimkugenerate_dates CREATE TABLE demo_cdm_ach_tmp.f6rqimkuweekly_dates USING DELTA AS SELECT date_add(to_date(cast(1900 as string) || '-' || cast(1 as string) || '-' || cast(7 as string)), (7 * seq.rn)) as generated_date -- first sunday in 1900 FROM ( select d1.n + (10 * d10.n) + (100 * d100.n) + (1000 * d1000.n) as rn from demo_cdm_ach_tmp.f6rqimkudigits d1, demo_cdm_ach_tmp.f6rqimkudigits d10, demo_cdm_ach_tmp.f6rqimkudigits d100, demo_cdm_ach_tmp.f6rqimkudigits d1000 ) seq CREATE TABLE demo_cdm_ach_tmp.f6rqimkuquarterly_dates USING DELTA AS SELECT to_date(cast(d_years as string) || '-' || cast(d_months as string) || '-' || cast(1 as string)) as generated_date FROM demo_cdm_ach_tmp.f6rqimkugenerate_dates where d_months in (1,4,7,10) -- ADD UNION ALLs for additional period definitions CREATE TABLE demo_cdm_ach_tmp.f6rqimkutemp_period USING DELTA AS SELECT * FROM ( select CAST('Monthly' AS STRING) as period_name , 1 as period_order , CAST( 'mm' AS STRING) as period_type , md.generated_date as period_start_date , (md.generated_date + INTERVAL 1 month) as period_end_date from demo_cdm_ach_tmp.f6rqimkumonthly_dates md UNION ALL select CAST('Weekly' AS STRING) as period_name , 2 as period_order , CAST('ww' AS STRING) as period_type , wd.generated_date as period_start_date , date_add(wd.generated_date, 7) as period_end_date from demo_cdm_ach_tmp.f6rqimkuweekly_dates wd where wd.generated_date >= to_date(cast(1900 as string) || '-' || cast(1 as string) || '-' || cast(1 as string)) and wd.generated_date < to_date(cast(2100 as string) || '-' || cast(1 as string) || '-' || cast(1 as string)) UNION ALL select CAST('Quarterly' AS STRING) as period_name , 3 as period_order , CAST('qq' AS STRING) as period_type , qd.generated_date as period_start_date , (qd.generated_date + INTERVAL 3 month) as period_end_date from demo_cdm_ach_tmp.f6rqimkuquarterly_dates qd UNION ALL select CAST('Yearly' AS STRING) as period_name , 4 as period_order , CAST('yy' AS STRING) as period_type , yd.generated_date as period_start_date , (yd.generated_date + INTERVAL 1 year) as period_end_date from demo_cdm_ach_tmp.f6rqimkuyearly_dates yd ) monthlyDates TRUNCATE TABLE demo_cdm_ach_res.heracles_periods WITH insertion_temp AS ( (SELECT CAST(row_number() over (order by period_order, period_start_date) AS INT) as period_id , period_name, period_order, period_type, period_start_date, period_end_date from demo_cdm_ach_tmp.f6rqimkutemp_period) UNION ALL (SELECT period_id, period_name, period_order, period_type, period_start_date, period_end_date FROM demo_cdm_ach_res.heracles_periods )) INSERT OVERWRITE TABLE demo_cdm_ach_res.heracles_periods SELECT * FROM insertion_temp Error executing: WITH insertion_temp AS ( (SELECT CAST(row_number() over (order by period_order, period_start_date) AS INT) as period_id , period_name, period_order, period_type, period_start_date, period_end_date from demo_cdm_ach_tmp.f6rqimkutemp_period) UNION ALL (SELECT period_id, period_name, period_order, period_type, period_start_date, period_end_date FROM demo_cdm_ach_res.heracles_periods )) INSERT OVERWRITE TABLE demo_cdm_ach_res.heracles_periods SELECT * FROM insertion_temp . Cause: java.sql.SQLException: [Simba][SparkJDBCDriver](500051) ERROR processing query/statement. Error Code: 0, SQL state: org.apache.hive.service.cli.HiveSQLException: Error running query: org.apache.spark.SparkException: Job aborted. at org.apache.spark.sql.hive.thriftserver.HiveThriftServerErrors$.runningQueryError(HiveThriftServerErrors.scala:48) at org.apache.spark.sql.hive.thriftserver.SparkExecuteStatementOperation.$anonfun$execute$1(SparkExecuteStatementOperation.scala:498) at scala.runtime.java8.JFunction0$mcV$sp.apply(JFunction0$mcV$sp.java:23) at com.databricks.unity.EmptyHandle$.runWith(UCSHandle.scala:124) at org.apache.spark.sql.hive.thriftserver.SparkExecuteStatementOperation.org$apache$spark$sql$hive$thriftserver$SparkExecuteStatementOperation$$execute(SparkExecuteStatementOperation.scala:410) at org.apache.spark.sql.hive.thriftserver.SparkExecuteStatementOperation$$anon$2$$anon$3.$anonfun$run$2(SparkExecuteStatementOperation.scala:321) at scala.runtime.java8.JFunction0$mcV$sp.apply(JFunction0$mcV$sp.java:23) at org.apache.spark.sql.hive.thriftserver.ThriftLocalProperties.withLocalProperties(ThriftLocalProperties.scala:149) at org.apache.spark.sql.hive.thriftserver.ThriftLocalProperties.withLocalProperties$(ThriftLocalProperties.scala:49) at org.apache.spark.sql.hive.thriftserver.SparkExecuteStatementOperation.withLocalProperties(SparkExecuteStatementOperation.scala:54) at org.apache.spark.sql.hive.thriftserver.SparkExecuteStatementOperation$$anon$2$$anon$3.run(SparkExecuteStatementOperation.scala:299) at org.apache.spark.sql.hive.thriftserver.SparkExecuteStatementOperation$$anon$2$$anon$3.run(SparkExecuteStatementOperation.scala:284) at java.security.AccessController.doPrivileged(Native Method) at javax.security.auth.Subject.doAs(Subject.java:422) at org.apache.hadoop.security.UserGroupInformation.doAs(UserGroupInformation.java:1878) at org.apache.spark.sql.hive.thriftserver.SparkExecuteStatementOperation$$anon$2.run(SparkExecuteStatementOperation.scala:333) at java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:511) at java.util.concurrent.FutureTask.run(FutureTask.java:266) at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149) at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624) at java.lang.Thread.run(Thread.java:750) Caused by: org.apache.spark.SparkException: Job aborted. at org.apache.spark.sql.errors.QueryExecutionErrors$.jobAbortedError(QueryExecutionErrors.scala:882) at org.apache.spark.sql.execution.datasources.FileFormatWriter$.$anonfun$write$1(FileFormatWriter.scala:334) at com.databricks.spark.util.FrameProfiler$.record(FrameProfiler.scala:80) at org.apache.spark.sql.execution.datasources.FileFormatWriter$.write(FileFormatWriter.scala:154) at com.databricks.sql.transaction.tahoe.commands.WriteIntoDeltaCommand.run(WriteIntoDeltaCommand.scala:70) at com.databricks.sql.transaction.tahoe.files.TransactionalWriteEdge.$anonfun$writeFiles$11(TransactionalWriteEdge.scala:564) at org.apache.spark.sql.execution.SQLExecution$.$anonfun$withCustomExecutionEnv$8(SQLExecution.scala:243) at org.apache.spark.sql.execution.SQLExecution$.withSQLConfPropagated(SQLExecution.scala:392) at org.apache.spark.sql.execution.SQLExecution$.$anonfun$withCustomExecutionEnv$1(SQLExecution.scala:188) at org.apache.spark.sql.SparkSession.withActive(SparkSession.scala:985) at org.apache.spark.sql.execution.SQLExecution$.withCustomExecutionEnv(SQLExecution.scala:142) at org.apache.spark.sql.execution.SQLExecution$.withNewExecutionId(SQLExecution.scala:342) at com.databricks.sql.transaction.tahoe.files.TransactionalWriteEdge.$anonfun$writeFiles$1(TransactionalWriteEdge.scala:564) at com.databricks.sql.transaction.tahoe.metering.DeltaLogging.withOperationTypeTag(DeltaLogging.scala:193) at com.databricks.sql.transaction.tahoe.metering.DeltaLogging.withOperationTypeTag$(DeltaLogging.scala:180) at com.databricks.sql.transaction.tahoe.OptimisticTransaction.withOperationTypeTag(OptimisticTransaction.scala:112) at com.databricks.sql.transaction.tahoe.metering.DeltaLogging.$anonfun$recordDeltaOperationInternal$2(DeltaLogging.scala:157) at com.databricks.spark.util.FrameProfiler$.record(FrameProfiler.scala:80) at com.databricks.sql.transaction.tahoe.metering.DeltaLogging.recordFrameProfile(DeltaLogging.scala:262) at com.databricks.sql.transaction.tahoe.metering.DeltaLogging.recordFrameProfile$(DeltaLogging.scala:260) at com.databricks.sql.transaction.tahoe.OptimisticTransaction.recordFrameProfile(OptimisticTransaction.scala:112) at com.databricks.sql.transaction.tahoe.metering.DeltaLogging.$anonfun$recordDeltaOperationInternal$1(DeltaLogging.scala:156) at com.databricks.logging.UsageLogging.$anonfun$recordOperation$1(UsageLogging.scala:541) at com.databricks.logging.UsageLogging.executeThunkAndCaptureResultTags$1(UsageLogging.scala:636) at com.databricks.logging.UsageLogging.$anonfun$recordOperationWithResultTags$4(UsageLogging.scala:657) at com.databricks.logging.UsageLogging.$anonfun$withAttributionContext$1(UsageLogging.scala:398) at scala.util.DynamicVariable.withValue(DynamicVariable.scala:62) at com.databricks.logging.AttributionContext$.withValue(AttributionContext.scala:147) at com.databricks.logging.UsageLogging.withAttributionContext(UsageLogging.scala:396) at com.databricks.logging.UsageLogging.withAttributionContext$(UsageLogging.scala:393) at com.databricks.spark.util.PublicDBLogging.withAttributionContext(DatabricksSparkUsageLogger.scala:22) at com.databricks.logging.UsageLogging.withAttributionTags(UsageLogging.scala:441) at com.databricks.logging.UsageLogging.withAttributionTags$(UsageLogging.scala:426) at com.databricks.spark.util.PublicDBLogging.withAttributionTags(DatabricksSparkUsageLogger.scala:22) at com.databricks.logging.UsageLogging.recordOperationWithResultTags(UsageLogging.scala:631) at com.databricks.logging.UsageLogging.recordOperationWithResultTags$(UsageLogging.scala:550) at com.databricks.spark.util.PublicDBLogging.recordOperationWithResultTags(DatabricksSparkUsageLogger.scala:22) at com.databricks.logging.UsageLogging.recordOperation(UsageLogging.scala:541) at com.databricks.logging.UsageLogging.recordOperation$(UsageLogging.scala:511) at com.databricks.spark.util.PublicDBLogging.recordOperation(DatabricksSparkUsageLogger.scala:22) at com.databricks.spark.util.PublicDBLogging.recordOperation0(DatabricksSparkUsageLogger.scala:62) at com.databricks.spark.util.DatabricksSparkUsageLogger.recordOperation(DatabricksSparkUsageLogger.scala:144) at com.databricks.spark.util.UsageLogger.recordOperation(UsageLogger.scala:72) at com.databricks.spark.util.UsageLogger.recordOperation$(UsageLogger.scala:59) at com.databricks.spark.util.DatabricksSparkUsageLogger.recordOperation(DatabricksSparkUsageLogger.scala:103) at com.databricks.spark.util.UsageLogging.recordOperation(UsageLogger.scala:433) at com.databricks.spark.util.UsageLogging.recordOperation$(UsageLogger.scala:412) at com.databricks.sql.transaction.tahoe.OptimisticTransaction.recordOperation(OptimisticTransaction.scala:112) at com.databricks.sql.transaction.tahoe.metering.DeltaLogging.recordDeltaOperationInternal(DeltaLogging.scala:155) at com.databricks.sql.transaction.tahoe.metering.DeltaLogging.recordDeltaOperation(DeltaLogging.scala:145) at com.databricks.sql.transaction.tahoe.metering.DeltaLogging.recordDeltaOperation$(DeltaLogging.scala:133) at com.databricks.sql.transaction.tahoe.OptimisticTransaction.recordDeltaOperation(OptimisticTransaction.scala:112) at com.databricks.sql.transaction.tahoe.files.TransactionalWriteEdge.$anonfun$recordWriteFilesOperation$1(TransactionalWriteEdge.scala:305) at com.databricks.sql.acl.CheckPermissions$.trusted(CheckPermissions.scala:1816) at com.databricks.sql.transaction.tahoe.files.TransactionalWriteEdge.recordWriteFilesOperation(TransactionalWriteEdge.scala:304) at com.databricks.sql.transaction.tahoe.files.TransactionalWriteEdge.writeFiles(TransactionalWriteEdge.scala:337) at com.databricks.sql.transaction.tahoe.files.TransactionalWriteEdge.writeFiles$(TransactionalWriteEdge.scala:331) at com.databricks.sql.transaction.tahoe.OptimisticTransaction.writeFiles(OptimisticTransaction.scala:112) at com.databricks.sql.transaction.tahoe.files.TransactionalWriteEdge.writeFiles(TransactionalWriteEdge.scala:604) at com.databricks.sql.transaction.tahoe.files.TransactionalWriteEdge.writeFiles$(TransactionalWriteEdge.scala:594) at com.databricks.sql.transaction.tahoe.OptimisticTransaction.writeFiles(OptimisticTransaction.scala:112) at com.databricks.sql.transaction.tahoe.files.TransactionalWrite.writeFiles(TransactionalWrite.scala:222) at com.databricks.sql.transaction.tahoe.files.TransactionalWrite.writeFiles$(TransactionalWrite.scala:219) at com.databricks.sql.transaction.tahoe.OptimisticTransaction.writeFiles(OptimisticTransaction.scala:112) at com.databricks.sql.transaction.tahoe.commands.WriteIntoDelta.write(WriteIntoDelta.scala:366) at com.databricks.sql.transaction.tahoe.commands.WriteIntoDelta.$anonfun$run$2(WriteIntoDelta.scala:110) at com.databricks.sql.transaction.tahoe.commands.WriteIntoDelta.$anonfun$run$2$adapted(WriteIntoDelta.scala:99) at com.databricks.sql.transaction.tahoe.DeltaLog.withNewTransaction(DeltaLog.scala:306) at com.databricks.sql.transaction.tahoe.commands.WriteIntoDelta.$anonfun$run$1(WriteIntoDelta.scala:99) at com.databricks.sql.acl.CheckPermissions$.trusted(CheckPermissions.scala:1816) at com.databricks.sql.transaction.tahoe.commands.WriteIntoDelta.run(WriteIntoDelta.scala:98) at com.databricks.sql.transaction.tahoe.catalog.WriteIntoDeltaBuilder$$anon$1$$anon$2.insert(DeltaTableV2.scala:342) at org.apache.spark.sql.execution.datasources.v2.SupportsV1Write.writeWithV1(V1FallbackWriters.scala:91) at org.apache.spark.sql.execution.datasources.v2.SupportsV1Write.writeWithV1$(V1FallbackWriters.scala:89) at org.apache.spark.sql.execution.datasources.v2.OverwriteByExpressionExecV1.writeWithV1(V1FallbackWriters.scala:53) at org.apache.spark.sql.execution.datasources.v2.V1FallbackWriters.run(V1FallbackWriters.scala:77) at org.apache.spark.sql.execution.datasources.v2.V1FallbackWriters.run$(V1FallbackWriters.scala:76) at org.apache.spark.sql.execution.datasources.v2.OverwriteByExpressionExecV1.run(V1FallbackWriters.scala:53) at org.apache.spark.sql.execution.datasources.v2.V2CommandExec.result$lzycompute(V2CommandExec.scala:43) at org.apache.spark.sql.execution.datasources.v2.V2CommandExec.result(V2CommandExec.scala:43) at org.apache.spark.sql.execution.datasources.v2.V2CommandExec.executeCollect(V2CommandExec.scala:49) at org.apache.spark.sql.execution.QueryExecution$$anonfun$$nestedInanonfun$eagerlyExecuteCommands$1$1.$anonfun$applyOrElse$2(QueryExecution.scala:229) at org.apache.spark.sql.execution.SQLExecution$.$anonfun$withCustomExecutionEnv$8(SQLExecution.scala:243) at org.apache.spark.sql.execution.SQLExecution$.withSQLConfPropagated(SQLExecution.scala:392) at org.apache.spark.sql.execution.SQLExecution$.$anonfun$withCustomExecutionEnv$1(SQLExecution.scala:188) at org.apache.spark.sql.SparkSession.withActive(SparkSession.scala:985) at org.apache.spark.sql.execution.SQLExecution$.withCustomExecutionEnv(SQLExecution.scala:142) at org.apache.spark.sql.execution.SQLExecution$.withNewExecutionId(SQLExecution.scala:342) at org.apache.spark.sql.execution.QueryExecution$$anonfun$$nestedInanonfun$eagerlyExecuteCommands$1$1.$anonfun$applyOrElse$1(QueryExecution.scala:229) at org.apache.spark.sql.execution.QueryExecution.org$apache$spark$sql$execution$QueryExecution$$withMVTagsIfNecessary(QueryExecution.scala:214) at org.apache.spark.sql.execution.QueryExecution$$anonfun$$nestedInanonfun$eagerlyExecuteCommands$1$1.applyOrElse(QueryExecution.scala:227) at org.apache.spark.sql.execution.QueryExecution$$anonfun$$nestedInanonfun$eagerlyExecuteCommands$1$1.applyOrElse(QueryExecution.scala:220) at org.apache.spark.sql.catalyst.trees.TreeNode.$anonfun$transformDownWithPruning$1(TreeNode.scala:512) at org.apache.spark.sql.catalyst.trees.CurrentOrigin$.withOrigin(TreeNode.scala:99) at org.apache.spark.sql.catalyst.trees.TreeNode.transformDownWithPruning(TreeNode.scala:512) at org.apache.spark.sql.catalyst.plans.logical.LogicalPlan.org$apache$spark$sql$catalyst$plans$logical$AnalysisHelper$$super$transformDownWithPruning(LogicalPlan.scala:31) at org.apache.spark.sql.catalyst.plans.logical.AnalysisHelper.transformDownWithPruning(AnalysisHelper.scala:298) at org.apache.spark.sql.catalyst.plans.logical.AnalysisHelper.transformDownWithPruning$(AnalysisHelper.scala:294) at org.apache.spark.sql.catalyst.plans.logical.LogicalPlan.transformDownWithPruning(LogicalPlan.scala:31) at org.apache.spark.sql.catalyst.plans.logical.LogicalPlan.transformDownWithPruning(LogicalPlan.scala:31) at org.apache.spark.sql.catalyst.trees.TreeNode.transformDown(TreeNode.scala:488) at org.apache.spark.sql.execution.QueryExecution.$anonfun$eagerlyExecuteCommands$1(QueryExecution.scala:220) at org.apache.spark.sql.catalyst.plans.logical.AnalysisHelper$.allowInvokingTransformsInAnalyzer(AnalysisHelper.scala:354) at org.apache.spark.sql.execution.QueryExecution.eagerlyExecuteCommands(QueryExecution.scala:220) at org.apache.spark.sql.execution.QueryExecution.commandExecuted$lzycompute(QueryExecution.scala:174) at org.apache.spark.sql.execution.QueryExecution.commandExecuted(QueryExecution.scala:165) at org.apache.spark.sql.Dataset.(Dataset.scala:238) at org.apache.spark.sql.hive.thriftserver.SparkExecuteStatementOperation.$anonfun$analyzeQuery$2(SparkExecuteStatementOperation.scala:404) at org.apache.spark.sql.SparkSession.withActive(SparkSession.scala:985) at org.apache.spark.sql.hive.thriftserver.SparkExecuteStatementOperation.$anonfun$analyzeQuery$1(SparkExecuteStatementOperation.scala:397) at org.apache.spark.sql.hive.thriftserver.SparkExecuteStatementOperation.getOrCreateDF(SparkExecuteStatementOperation.scala:387) at org.apache.spark.sql.hive.thriftserver.SparkExecuteStatementOperation.analyzeQuery(SparkExecuteStatementOperation.scala:397) at org.apache.spark.sql.hive.thriftserver.SparkExecuteStatementOperation.$anonfun$execute$5(SparkExecuteStatementOperation.scala:446) at org.apache.spark.util.Utils$.timeTakenMs(Utils.scala:692) at org.apache.spark.sql.execution.qrc.CacheEventLogger.recordLatency(QueryResultCache.scala:149) at org.apache.spark.sql.execution.qrc.CacheEventLogger.recordLatency$(QueryResultCache.scala:145) at org.apache.spark.sql.hive.thriftserver.SparkExecuteStatementOperation.recordLatency(SparkExecuteStatementOperation.scala:54) at org.apache.spark.sql.hive.thriftserver.SparkExecuteStatementOperation.$anonfun$execute$1(SparkExecuteStatementOperation.scala:446) ... 19 more Caused by: org.apache.spark.SparkException: Job aborted due to stage failure: Task 0 in stage 92321.0 failed 4 times, most recent failure: Lost task 0.3 in stage 92321.0 (TID 140794) (10.22.225.217 executor 262): org.apache.spark.SparkNumberFormatException: [CAST_INVALID_INPUT] The value 'Monthly' of the type "STRING" cannot be cast to "INT" because it is malformed. Correct the value as per the syntax, or change its target type. Use `try_cast` to tolerate malformed input and return NULL instead. If necessary set "spark.sql.ansi.enabled" to "false" to bypass this error. == SQL(line 5, position 1) == INSERT OVERWRITE TABLE demo_cdm_ach_res.heracles_periods SELECT * FROM insertion_temp ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^ at org.apache.spark.sql.errors.QueryExecutionErrors$.invalidInputInCastToNumberError(QueryExecutionErrors.scala:256) at org.apache.spark.sql.catalyst.util.UTF8StringUtils$.withException(UTF8StringUtils.scala:51) at org.apache.spark.sql.catalyst.util.UTF8StringUtils$.toIntExact(UTF8StringUtils.scala:34) at org.apache.spark.sql.catalyst.util.UTF8StringUtils.toIntExact(UTF8StringUtils.scala) at org.apache.spark.sql.catalyst.expressions.GeneratedClass$GeneratedIteratorForCodegenStage2.runningwindowfunction_doConsume_0$(Unknown Source) at org.apache.spark.sql.catalyst.expressions.GeneratedClass$GeneratedIteratorForCodegenStage2.processNext(Unknown Source) at org.apache.spark.sql.execution.BufferedRowIterator.hasNext(BufferedRowIterator.java:43) at org.apache.spark.sql.execution.WholeStageCodegenExec$$anon$1.hasNext(WholeStageCodegenExec.scala:761) at org.apache.spark.sql.execution.datasources.FileFormatWriter$.executeTask(FileFormatWriter.scala:384) at org.apache.spark.sql.execution.datasources.FileFormatWriter$.$anonfun$write$12(FileFormatWriter.scala:310) at org.apache.spark.scheduler.ResultTask.$anonfun$runTask$3(ResultTask.scala:75) at com.databricks.spark.util.ExecutorFrameProfiler$.record(ExecutorFrameProfiler.scala:110) at org.apache.spark.scheduler.ResultTask.$anonfun$runTask$1(ResultTask.scala:75) at com.databricks.spark.util.ExecutorFrameProfiler$.record(ExecutorFrameProfiler.scala:110) at org.apache.spark.scheduler.ResultTask.runTask(ResultTask.scala:55) at org.apache.spark.scheduler.Task.doRunTask(Task.scala:169) at org.apache.spark.scheduler.Task.$anonfun$run$4(Task.scala:137) at com.databricks.unity.EmptyHandle$.runWithAndClose(UCSHandle.scala:125) at org.apache.spark.scheduler.Task.$anonfun$run$1(Task.scala:137) at com.databricks.spark.util.ExecutorFrameProfiler$.record(ExecutorFrameProfiler.scala:110) at org.apache.spark.scheduler.Task.run(Task.scala:96) at org.apache.spark.executor.Executor$TaskRunner.$anonfun$run$13(Executor.scala:902) at org.apache.spark.util.Utils$.tryWithSafeFinally(Utils.scala:1697) at org.apache.spark.executor.Executor$TaskRunner.$anonfun$run$4(Executor.scala:905) at scala.runtime.java8.JFunction0$mcV$sp.apply(JFunction0$mcV$sp.java:23) at com.databricks.spark.util.ExecutorFrameProfiler$.record(ExecutorFrameProfiler.scala:110) at org.apache.spark.executor.Executor$TaskRunner.run(Executor.scala:760) at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149) at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624) at java.lang.Thread.run(Thread.java:750) Driver stacktrace: at org.apache.spark.scheduler.DAGScheduler.failJobAndIndependentStages(DAGScheduler.scala:3376) at org.apache.spark.scheduler.DAGScheduler.$anonfun$abortStage$2(DAGScheduler.scala:3308) at org.apache.spark.scheduler.DAGScheduler.$anonfun$abortStage$2$adapted(DAGScheduler.scala:3299) at scala.collection.mutable.ResizableArray.foreach(ResizableArray.scala:62) at scala.collection.mutable.ResizableArray.foreach$(ResizableArray.scala:55) at scala.collection.mutable.ArrayBuffer.foreach(ArrayBuffer.scala:49) at org.apache.spark.scheduler.DAGScheduler.abortStage(DAGScheduler.scala:3299) at org.apache.spark.scheduler.DAGScheduler.$anonfun$handleTaskSetFailed$1(DAGScheduler.scala:1428) at org.apache.spark.scheduler.DAGScheduler.$anonfun$handleTaskSetFailed$1$adapted(DAGScheduler.scala:1428) at scala.Option.foreach(Option.scala:407) at org.apache.spark.scheduler.DAGScheduler.handleTaskSetFailed(DAGScheduler.scala:1428) at org.apache.spark.scheduler.DAGSchedulerEventProcessLoop.doOnReceive(DAGScheduler.scala:3588) at org.apache.spark.scheduler.DAGSchedulerEventProcessLoop.onReceive(DAGScheduler.scala:3526) at org.apache.spark.scheduler.DAGSchedulerEventProcessLoop.onReceive(DAGScheduler.scala:3514) at org.apache.spark.util.EventLoop$$anon$1.run(EventLoop.scala:51) at org.apache.spark.scheduler.DAGScheduler.$anonfun$runJob$1(DAGScheduler.scala:1177) at scala.runtime.java8.JFunction0$mcV$sp.apply(JFunction0$mcV$sp.java:23) at com.databricks.spark.util.FrameProfiler$.record(FrameProfiler.scala:80) at org.apache.spark.scheduler.DAGScheduler.runJob(DAGScheduler.scala:1165) at org.apache.spark.SparkContext.runJobInternal(SparkContext.scala:2733) at org.apache.spark.SparkContext.runJob(SparkContext.scala:2716) at org.apache.spark.sql.execution.datasources.FileFormatWriter$.$anonfun$write$1(FileFormatWriter.scala:299) ... 135 more Caused by: org.apache.spark.SparkNumberFormatException: [CAST_INVALID_INPUT] The value 'Monthly' of the type "STRING" cannot be cast to "INT" because it is malformed. Correct the value as per the syntax, or change its target type. Use `try_cast` to tolerate malformed input and return NULL instead. If necessary set "spark.sql.ansi.enabled" to "false" to bypass this error. == SQL(line 5, position 1) == INSERT OVERWRITE TABLE demo_cdm_ach_res.heracles_periods SELECT * FROM insertion_temp ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^ at org.apache.spark.sql.errors.QueryExecutionErrors$.invalidInputInCastToNumberError(QueryExecutionErrors.scala:256) at org.apache.spark.sql.catalyst.util.UTF8StringUtils$.withException(UTF8StringUtils.scala:51) at org.apache.spark.sql.catalyst.util.UTF8StringUtils$.toIntExact(UTF8StringUtils.scala:34) at org.apache.spark.sql.catalyst.util.UTF8StringUtils.toIntExact(UTF8StringUtils.scala) at org.apache.spark.sql.catalyst.expressions.GeneratedClass$GeneratedIteratorForCodegenStage2.runningwindowfunction_doConsume_0$(Unknown Source) at org.apache.spark.sql.catalyst.expressions.GeneratedClass$GeneratedIteratorForCodegenStage2.processNext(Unknown Source) at org.apache.spark.sql.execution.BufferedRowIterator.hasNext(BufferedRowIterator.java:43) at org.apache.spark.sql.execution.WholeStageCodegenExec$$anon$1.hasNext(WholeStageCodegenExec.scala:761) at org.apache.spark.sql.execution.datasources.FileFormatWriter$.executeTask(FileFormatWriter.scala:384) at org.apache.spark.sql.execution.datasources.FileFormatWriter$.$anonfun$write$12(FileFormatWriter.scala:310) at org.apache.spark.scheduler.ResultTask.$anonfun$runTask$3(ResultTask.scala:75) at com.databricks.spark.util.ExecutorFrameProfiler$.record(ExecutorFrameProfiler.scala:110) at org.apache.spark.scheduler.ResultTask.$anonfun$runTask$1(ResultTask.scala:75) at com.databricks.spark.util.ExecutorFrameProfiler$.record(ExecutorFrameProfiler.scala:110) at org.apache.spark.scheduler.ResultTask.runTask(ResultTask.scala:55) at org.apache.spark.scheduler.Task.doRunTask(Task.scala:169) at org.apache.spark.scheduler.Task.$anonfun$run$4(Task.scala:137) at com.databricks.unity.EmptyHandle$.runWithAndClose(UCSHandle.scala:125) at org.apache.spark.scheduler.Task.$anonfun$run$1(Task.scala:137) at com.databricks.spark.util.ExecutorFrameProfiler$.record(ExecutorFrameProfiler.scala:110) at org.apache.spark.scheduler.Task.run(Task.scala:96) at org.apache.spark.executor.Executor$TaskRunner.$anonfun$run$13(Executor.scala:902) at org.apache.spark.util.Utils$.tryWithSafeFinally(Utils.scala:1697) at org.apache.spark.executor.Executor$TaskRunner.$anonfun$run$4(Executor.scala:905) at scala.runtime.java8.JFunction0$mcV$sp.apply(JFunction0$mcV$sp.java:23) at com.databricks.spark.util.ExecutorFrameProfiler$.record(ExecutorFrameProfiler.scala:110) at org.apache.spark.executor.Executor$TaskRunner.run(Executor.scala:760) ... 3 more , Query: WITH insertion_temp AS ( (SELECT CAST(row_number() over (order by period_order, period_start_date) AS INT) as period_id , period_name, period_order, period_type, period_start_date, period_end_date from demo_cdm_ach_tmp.f6rqimkutemp_period) UNION ALL (SELECT period_id, period_name, period_order, period_type, period_start_date, period_end_date FROM demo_cdm_ach_res.heracles_periods )) INSERT OVERWRITE TABLE demo_cdm_ach_res.heracles_periods SELECT * FROM insertion_temp. Exception in thread "main" java.lang.RuntimeException: org.apache.ibatis.jdbc.RuntimeSqlException: Error executing: WITH insertion_temp AS ( (SELECT CAST(row_number() over (order by period_order, period_start_date) AS INT) as period_id , period_name, period_order, period_type, period_start_date, period_end_date from demo_cdm_ach_tmp.f6rqimkutemp_period) UNION ALL (SELECT period_id, period_name, period_order, period_type, period_start_date, period_end_date FROM demo_cdm_ach_res.heracles_periods )) INSERT OVERWRITE TABLE demo_cdm_ach_res.heracles_periods SELECT * FROM insertion_temp . Cause: java.sql.SQLException: [Simba][SparkJDBCDriver](500051) ERROR processing query/statement. Error Code: 0, SQL state: org.apache.hive.service.cli.HiveSQLException: Error running query: org.apache.spark.SparkException: Job aborted. at org.apache.spark.sql.hive.thriftserver.HiveThriftServerErrors$.runningQueryError(HiveThriftServerErrors.scala:48) at org.apache.spark.sql.hive.thriftserver.SparkExecuteStatementOperation.$anonfun$execute$1(SparkExecuteStatementOperation.scala:498) at scala.runtime.java8.JFunction0$mcV$sp.apply(JFunction0$mcV$sp.java:23) at com.databricks.unity.EmptyHandle$.runWith(UCSHandle.scala:124) at org.apache.spark.sql.hive.thriftserver.SparkExecuteStatementOperation.org$apache$spark$sql$hive$thriftserver$SparkExecuteStatementOperation$$execute(SparkExecuteStatementOperation.scala:410) at org.apache.spark.sql.hive.thriftserver.SparkExecuteStatementOperation$$anon$2$$anon$3.$anonfun$run$2(SparkExecuteStatementOperation.scala:321) at scala.runtime.java8.JFunction0$mcV$sp.apply(JFunction0$mcV$sp.java:23) at org.apache.spark.sql.hive.thriftserver.ThriftLocalProperties.withLocalProperties(ThriftLocalProperties.scala:149) at org.apache.spark.sql.hive.thriftserver.ThriftLocalProperties.withLocalProperties$(ThriftLocalProperties.scala:49) at org.apache.spark.sql.hive.thriftserver.SparkExecuteStatementOperation.withLocalProperties(SparkExecuteStatementOperation.scala:54) at org.apache.spark.sql.hive.thriftserver.SparkExecuteStatementOperation$$anon$2$$anon$3.run(SparkExecuteStatementOperation.scala:299) at org.apache.spark.sql.hive.thriftserver.SparkExecuteStatementOperation$$anon$2$$anon$3.run(SparkExecuteStatementOperation.scala:284) at java.security.AccessController.doPrivileged(Native Method) at javax.security.auth.Subject.doAs(Subject.java:422) at org.apache.hadoop.security.UserGroupInformation.doAs(UserGroupInformation.java:1878) at org.apache.spark.sql.hive.thriftserver.SparkExecuteStatementOperation$$anon$2.run(SparkExecuteStatementOperation.scala:333) at java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:511) at java.util.concurrent.FutureTask.run(FutureTask.java:266) at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149) at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624) at java.lang.Thread.run(Thread.java:750) Caused by: org.apache.spark.SparkException: Job aborted. at org.apache.spark.sql.errors.QueryExecutionErrors$.jobAbortedError(QueryExecutionErrors.scala:882) at org.apache.spark.sql.execution.datasources.FileFormatWriter$.$anonfun$write$1(FileFormatWriter.scala:334) at com.databricks.spark.util.FrameProfiler$.record(FrameProfiler.scala:80) at org.apache.spark.sql.execution.datasources.FileFormatWriter$.write(FileFormatWriter.scala:154) at com.databricks.sql.transaction.tahoe.commands.WriteIntoDeltaCommand.run(WriteIntoDeltaCommand.scala:70) at com.databricks.sql.transaction.tahoe.files.TransactionalWriteEdge.$anonfun$writeFiles$11(TransactionalWriteEdge.scala:564) at org.apache.spark.sql.execution.SQLExecution$.$anonfun$withCustomExecutionEnv$8(SQLExecution.scala:243) at org.apache.spark.sql.execution.SQLExecution$.withSQLConfPropagated(SQLExecution.scala:392) at org.apache.spark.sql.execution.SQLExecution$.$anonfun$withCustomExecutionEnv$1(SQLExecution.scala:188) at org.apache.spark.sql.SparkSession.withActive(SparkSession.scala:985) at org.apache.spark.sql.execution.SQLExecution$.withCustomExecutionEnv(SQLExecution.scala:142) at org.apache.spark.sql.execution.SQLExecution$.withNewExecutionId(SQLExecution.scala:342) at com.databricks.sql.transaction.tahoe.files.TransactionalWriteEdge.$anonfun$writeFiles$1(TransactionalWriteEdge.scala:564) at com.databricks.sql.transaction.tahoe.metering.DeltaLogging.withOperationTypeTag(DeltaLogging.scala:193) at com.databricks.sql.transaction.tahoe.metering.DeltaLogging.withOperationTypeTag$(DeltaLogging.scala:180) at com.databricks.sql.transaction.tahoe.OptimisticTransaction.withOperationTypeTag(OptimisticTransaction.scala:112) at com.databricks.sql.transaction.tahoe.metering.DeltaLogging.$anonfun$recordDeltaOperationInternal$2(DeltaLogging.scala:157) at com.databricks.spark.util.FrameProfiler$.record(FrameProfiler.scala:80) at com.databricks.sql.transaction.tahoe.metering.DeltaLogging.recordFrameProfile(DeltaLogging.scala:262) at com.databricks.sql.transaction.tahoe.metering.DeltaLogging.recordFrameProfile$(DeltaLogging.scala:260) at com.databricks.sql.transaction.tahoe.OptimisticTransaction.recordFrameProfile(OptimisticTransaction.scala:112) at com.databricks.sql.transaction.tahoe.metering.DeltaLogging.$anonfun$recordDeltaOperationInternal$1(DeltaLogging.scala:156) at com.databricks.logging.UsageLogging.$anonfun$recordOperation$1(UsageLogging.scala:541) at com.databricks.logging.UsageLogging.executeThunkAndCaptureResultTags$1(UsageLogging.scala:636) at com.databricks.logging.UsageLogging.$anonfun$recordOperationWithResultTags$4(UsageLogging.scala:657) at com.databricks.logging.UsageLogging.$anonfun$withAttributionContext$1(UsageLogging.scala:398) at scala.util.DynamicVariable.withValue(DynamicVariable.scala:62) at com.databricks.logging.AttributionContext$.withValue(AttributionContext.scala:147) at com.databricks.logging.UsageLogging.withAttributionContext(UsageLogging.scala:396) at com.databricks.logging.UsageLogging.withAttributionContext$(UsageLogging.scala:393) at com.databricks.spark.util.PublicDBLogging.withAttributionContext(DatabricksSparkUsageLogger.scala:22) at com.databricks.logging.UsageLogging.withAttributionTags(UsageLogging.scala:441) at com.databricks.logging.UsageLogging.withAttributionTags$(UsageLogging.scala:426) at com.databricks.spark.util.PublicDBLogging.withAttributionTags(DatabricksSparkUsageLogger.scala:22) at com.databricks.logging.UsageLogging.recordOperationWithResultTags(UsageLogging.scala:631) at com.databricks.logging.UsageLogging.recordOperationWithResultTags$(UsageLogging.scala:550) at com.databricks.spark.util.PublicDBLogging.recordOperationWithResultTags(DatabricksSparkUsageLogger.scala:22) at com.databricks.logging.UsageLogging.recordOperation(UsageLogging.scala:541) at com.databricks.logging.UsageLogging.recordOperation$(UsageLogging.scala:511) at com.databricks.spark.util.PublicDBLogging.recordOperation(DatabricksSparkUsageLogger.scala:22) at com.databricks.spark.util.PublicDBLogging.recordOperation0(DatabricksSparkUsageLogger.scala:62) at com.databricks.spark.util.DatabricksSparkUsageLogger.recordOperation(DatabricksSparkUsageLogger.scala:144) at com.databricks.spark.util.UsageLogger.recordOperation(UsageLogger.scala:72) at com.databricks.spark.util.UsageLogger.recordOperation$(UsageLogger.scala:59) at com.databricks.spark.util.DatabricksSparkUsageLogger.recordOperation(DatabricksSparkUsageLogger.scala:103) at com.databricks.spark.util.UsageLogging.recordOperation(UsageLogger.scala:433) at com.databricks.spark.util.UsageLogging.recordOperation$(UsageLogger.scala:412) at com.databricks.sql.transaction.tahoe.OptimisticTransaction.recordOperation(OptimisticTransaction.scala:112) at com.databricks.sql.transaction.tahoe.metering.DeltaLogging.recordDeltaOperationInternal(DeltaLogging.scala:155) at com.databricks.sql.transaction.tahoe.metering.DeltaLogging.recordDeltaOperation(DeltaLogging.scala:145) at com.databricks.sql.transaction.tahoe.metering.DeltaLogging.recordDeltaOperation$(DeltaLogging.scala:133) at com.databricks.sql.transaction.tahoe.OptimisticTransaction.recordDeltaOperation(OptimisticTransaction.scala:112) at com.databricks.sql.transaction.tahoe.files.TransactionalWriteEdge.$anonfun$recordWriteFilesOperation$1(TransactionalWriteEdge.scala:305) at com.databricks.sql.acl.CheckPermissions$.trusted(CheckPermissions.scala:1816) at com.databricks.sql.transaction.tahoe.files.TransactionalWriteEdge.recordWriteFilesOperation(TransactionalWriteEdge.scala:304) at com.databricks.sql.transaction.tahoe.files.TransactionalWriteEdge.writeFiles(TransactionalWriteEdge.scala:337) at com.databricks.sql.transaction.tahoe.files.TransactionalWriteEdge.writeFiles$(TransactionalWriteEdge.scala:331) at com.databricks.sql.transaction.tahoe.OptimisticTransaction.writeFiles(OptimisticTransaction.scala:112) at com.databricks.sql.transaction.tahoe.files.TransactionalWriteEdge.writeFiles(TransactionalWriteEdge.scala:604) at com.databricks.sql.transaction.tahoe.files.TransactionalWriteEdge.writeFiles$(TransactionalWriteEdge.scala:594) at com.databricks.sql.transaction.tahoe.OptimisticTransaction.writeFiles(OptimisticTransaction.scala:112) at com.databricks.sql.transaction.tahoe.files.TransactionalWrite.writeFiles(TransactionalWrite.scala:222) at com.databricks.sql.transaction.tahoe.files.TransactionalWrite.writeFiles$(TransactionalWrite.scala:219) at com.databricks.sql.transaction.tahoe.OptimisticTransaction.writeFiles(OptimisticTransaction.scala:112) at com.databricks.sql.transaction.tahoe.commands.WriteIntoDelta.write(WriteIntoDelta.scala:366) at com.databricks.sql.transaction.tahoe.commands.WriteIntoDelta.$anonfun$run$2(WriteIntoDelta.scala:110) at com.databricks.sql.transaction.tahoe.commands.WriteIntoDelta.$anonfun$run$2$adapted(WriteIntoDelta.scala:99) at com.databricks.sql.transaction.tahoe.DeltaLog.withNewTransaction(DeltaLog.scala:306) at com.databricks.sql.transaction.tahoe.commands.WriteIntoDelta.$anonfun$run$1(WriteIntoDelta.scala:99) at com.databricks.sql.acl.CheckPermissions$.trusted(CheckPermissions.scala:1816) at com.databricks.sql.transaction.tahoe.commands.WriteIntoDelta.run(WriteIntoDelta.scala:98) at com.databricks.sql.transaction.tahoe.catalog.WriteIntoDeltaBuilder$$anon$1$$anon$2.insert(DeltaTableV2.scala:342) at org.apache.spark.sql.execution.datasources.v2.SupportsV1Write.writeWithV1(V1FallbackWriters.scala:91) at org.apache.spark.sql.execution.datasources.v2.SupportsV1Write.writeWithV1$(V1FallbackWriters.scala:89) at org.apache.spark.sql.execution.datasources.v2.OverwriteByExpressionExecV1.writeWithV1(V1FallbackWriters.scala:53) at org.apache.spark.sql.execution.datasources.v2.V1FallbackWriters.run(V1FallbackWriters.scala:77) at org.apache.spark.sql.execution.datasources.v2.V1FallbackWriters.run$(V1FallbackWriters.scala:76) at org.apache.spark.sql.execution.datasources.v2.OverwriteByExpressionExecV1.run(V1FallbackWriters.scala:53) at org.apache.spark.sql.execution.datasources.v2.V2CommandExec.result$lzycompute(V2CommandExec.scala:43) at org.apache.spark.sql.execution.datasources.v2.V2CommandExec.result(V2CommandExec.scala:43) at org.apache.spark.sql.execution.datasources.v2.V2CommandExec.executeCollect(V2CommandExec.scala:49) at org.apache.spark.sql.execution.QueryExecution$$anonfun$$nestedInanonfun$eagerlyExecuteCommands$1$1.$anonfun$applyOrElse$2(QueryExecution.scala:229) at org.apache.spark.sql.execution.SQLExecution$.$anonfun$withCustomExecutionEnv$8(SQLExecution.scala:243) at org.apache.spark.sql.execution.SQLExecution$.withSQLConfPropagated(SQLExecution.scala:392) at org.apache.spark.sql.execution.SQLExecution$.$anonfun$withCustomExecutionEnv$1(SQLExecution.scala:188) at org.apache.spark.sql.SparkSession.withActive(SparkSession.scala:985) at org.apache.spark.sql.execution.SQLExecution$.withCustomExecutionEnv(SQLExecution.scala:142) at org.apache.spark.sql.execution.SQLExecution$.withNewExecutionId(SQLExecution.scala:342) at org.apache.spark.sql.execution.QueryExecution$$anonfun$$nestedInanonfun$eagerlyExecuteCommands$1$1.$anonfun$applyOrElse$1(QueryExecution.scala:229) at org.apache.spark.sql.execution.QueryExecution.org$apache$spark$sql$execution$QueryExecution$$withMVTagsIfNecessary(QueryExecution.scala:214) at org.apache.spark.sql.execution.QueryExecution$$anonfun$$nestedInanonfun$eagerlyExecuteCommands$1$1.applyOrElse(QueryExecution.scala:227) at org.apache.spark.sql.execution.QueryExecution$$anonfun$$nestedInanonfun$eagerlyExecuteCommands$1$1.applyOrElse(QueryExecution.scala:220) at org.apache.spark.sql.catalyst.trees.TreeNode.$anonfun$transformDownWithPruning$1(TreeNode.scala:512) at org.apache.spark.sql.catalyst.trees.CurrentOrigin$.withOrigin(TreeNode.scala:99) at org.apache.spark.sql.catalyst.trees.TreeNode.transformDownWithPruning(TreeNode.scala:512) at org.apache.spark.sql.catalyst.plans.logical.LogicalPlan.org$apache$spark$sql$catalyst$plans$logical$AnalysisHelper$$super$transformDownWithPruning(LogicalPlan.scala:31) at org.apache.spark.sql.catalyst.plans.logical.AnalysisHelper.transformDownWithPruning(AnalysisHelper.scala:298) at org.apache.spark.sql.catalyst.plans.logical.AnalysisHelper.transformDownWithPruning$(AnalysisHelper.scala:294) at org.apache.spark.sql.catalyst.plans.logical.LogicalPlan.transformDownWithPruning(LogicalPlan.scala:31) at org.apache.spark.sql.catalyst.plans.logical.LogicalPlan.transformDownWithPruning(LogicalPlan.scala:31) at org.apache.spark.sql.catalyst.trees.TreeNode.transformDown(TreeNode.scala:488) at org.apache.spark.sql.execution.QueryExecution.$anonfun$eagerlyExecuteCommands$1(QueryExecution.scala:220) at org.apache.spark.sql.catalyst.plans.logical.AnalysisHelper$.allowInvokingTransformsInAnalyzer(AnalysisHelper.scala:354) at org.apache.spark.sql.execution.QueryExecution.eagerlyExecuteCommands(QueryExecution.scala:220) at org.apache.spark.sql.execution.QueryExecution.commandExecuted$lzycompute(QueryExecution.scala:174) at org.apache.spark.sql.execution.QueryExecution.commandExecuted(QueryExecution.scala:165) at org.apache.spark.sql.Dataset.(Dataset.scala:238) at org.apache.spark.sql.hive.thriftserver.SparkExecuteStatementOperation.$anonfun$analyzeQuery$2(SparkExecuteStatementOperation.scala:404) at org.apache.spark.sql.SparkSession.withActive(SparkSession.scala:985) at org.apache.spark.sql.hive.thriftserver.SparkExecuteStatementOperation.$anonfun$analyzeQuery$1(SparkExecuteStatementOperation.scala:397) at org.apache.spark.sql.hive.thriftserver.SparkExecuteStatementOperation.getOrCreateDF(SparkExecuteStatementOperation.scala:387) at org.apache.spark.sql.hive.thriftserver.SparkExecuteStatementOperation.analyzeQuery(SparkExecuteStatementOperation.scala:397) at org.apache.spark.sql.hive.thriftserver.SparkExecuteStatementOperation.$anonfun$execute$5(SparkExecuteStatementOperation.scala:446) at org.apache.spark.util.Utils$.timeTakenMs(Utils.scala:692) at org.apache.spark.sql.execution.qrc.CacheEventLogger.recordLatency(QueryResultCache.scala:149) at org.apache.spark.sql.execution.qrc.CacheEventLogger.recordLatency$(QueryResultCache.scala:145) at org.apache.spark.sql.hive.thriftserver.SparkExecuteStatementOperation.recordLatency(SparkExecuteStatementOperation.scala:54) at org.apache.spark.sql.hive.thriftserver.SparkExecuteStatementOperation.$anonfun$execute$1(SparkExecuteStatementOperation.scala:446) ... 19 more Caused by: org.apache.spark.SparkException: Job aborted due to stage failure: Task 0 in stage 92321.0 failed 4 times, most recent failure: Lost task 0.3 in stage 92321.0 (TID 140794) (10.22.225.217 executor 262): org.apache.spark.SparkNumberFormatException: [CAST_INVALID_INPUT] The value 'Monthly' of the type "STRING" cannot be cast to "INT" because it is malformed. Correct the value as per the syntax, or change its target type. Use `try_cast` to tolerate malformed input and return NULL instead. If necessary set "spark.sql.ansi.enabled" to "false" to bypass this error. == SQL(line 5, position 1) == INSERT OVERWRITE TABLE demo_cdm_ach_res.heracles_periods SELECT * FROM insertion_temp ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^ at org.apache.spark.sql.errors.QueryExecutionErrors$.invalidInputInCastToNumberError(QueryExecutionErrors.scala:256) at org.apache.spark.sql.catalyst.util.UTF8StringUtils$.withException(UTF8StringUtils.scala:51) at org.apache.spark.sql.catalyst.util.UTF8StringUtils$.toIntExact(UTF8StringUtils.scala:34) at org.apache.spark.sql.catalyst.util.UTF8StringUtils.toIntExact(UTF8StringUtils.scala) at org.apache.spark.sql.catalyst.expressions.GeneratedClass$GeneratedIteratorForCodegenStage2.runningwindowfunction_doConsume_0$(Unknown Source) at org.apache.spark.sql.catalyst.expressions.GeneratedClass$GeneratedIteratorForCodegenStage2.processNext(Unknown Source) at org.apache.spark.sql.execution.BufferedRowIterator.hasNext(BufferedRowIterator.java:43) at org.apache.spark.sql.execution.WholeStageCodegenExec$$anon$1.hasNext(WholeStageCodegenExec.scala:761) at org.apache.spark.sql.execution.datasources.FileFormatWriter$.executeTask(FileFormatWriter.scala:384) at org.apache.spark.sql.execution.datasources.FileFormatWriter$.$anonfun$write$12(FileFormatWriter.scala:310) at org.apache.spark.scheduler.ResultTask.$anonfun$runTask$3(ResultTask.scala:75) at com.databricks.spark.util.ExecutorFrameProfiler$.record(ExecutorFrameProfiler.scala:110) at org.apache.spark.scheduler.ResultTask.$anonfun$runTask$1(ResultTask.scala:75) at com.databricks.spark.util.ExecutorFrameProfiler$.record(ExecutorFrameProfiler.scala:110) at org.apache.spark.scheduler.ResultTask.runTask(ResultTask.scala:55) at org.apache.spark.scheduler.Task.doRunTask(Task.scala:169) at org.apache.spark.scheduler.Task.$anonfun$run$4(Task.scala:137) at com.databricks.unity.EmptyHandle$.runWithAndClose(UCSHandle.scala:125) at org.apache.spark.scheduler.Task.$anonfun$run$1(Task.scala:137) at com.databricks.spark.util.ExecutorFrameProfiler$.record(ExecutorFrameProfiler.scala:110) at org.apache.spark.scheduler.Task.run(Task.scala:96) at org.apache.spark.executor.Executor$TaskRunner.$anonfun$run$13(Executor.scala:902) at org.apache.spark.util.Utils$.tryWithSafeFinally(Utils.scala:1697) at org.apache.spark.executor.Executor$TaskRunner.$anonfun$run$4(Executor.scala:905) at scala.runtime.java8.JFunction0$mcV$sp.apply(JFunction0$mcV$sp.java:23) at com.databricks.spark.util.ExecutorFrameProfiler$.record(ExecutorFrameProfiler.scala:110) at org.apache.spark.executor.Executor$TaskRunner.run(Executor.scala:760) at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149) at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624) at java.lang.Thread.run(Thread.java:750) Driver stacktrace: at org.apache.spark.scheduler.DAGScheduler.failJobAndIndependentStages(DAGScheduler.scala:3376) at org.apache.spark.scheduler.DAGScheduler.$anonfun$abortStage$2(DAGScheduler.scala:3308) at org.apache.spark.scheduler.DAGScheduler.$anonfun$abortStage$2$adapted(DAGScheduler.scala:3299) at scala.collection.mutable.ResizableArray.foreach(ResizableArray.scala:62) at scala.collection.mutable.ResizableArray.foreach$(ResizableArray.scala:55) at scala.collection.mutable.ArrayBuffer.foreach(ArrayBuffer.scala:49) at org.apache.spark.scheduler.DAGScheduler.abortStage(DAGScheduler.scala:3299) at org.apache.spark.scheduler.DAGScheduler.$anonfun$handleTaskSetFailed$1(DAGScheduler.scala:1428) at org.apache.spark.scheduler.DAGScheduler.$anonfun$handleTaskSetFailed$1$adapted(DAGScheduler.scala:1428) at scala.Option.foreach(Option.scala:407) at org.apache.spark.scheduler.DAGScheduler.handleTaskSetFailed(DAGScheduler.scala:1428) at org.apache.spark.scheduler.DAGSchedulerEventProcessLoop.doOnReceive(DAGScheduler.scala:3588) at org.apache.spark.scheduler.DAGSchedulerEventProcessLoop.onReceive(DAGScheduler.scala:3526) at org.apache.spark.scheduler.DAGSchedulerEventProcessLoop.onReceive(DAGScheduler.scala:3514) at org.apache.spark.util.EventLoop$$anon$1.run(EventLoop.scala:51) at org.apache.spark.scheduler.DAGScheduler.$anonfun$runJob$1(DAGScheduler.scala:1177) at scala.runtime.java8.JFunction0$mcV$sp.apply(JFunction0$mcV$sp.java:23) at com.databricks.spark.util.FrameProfiler$.record(FrameProfiler.scala:80) at org.apache.spark.scheduler.DAGScheduler.runJob(DAGScheduler.scala:1165) at org.apache.spark.SparkContext.runJobInternal(SparkContext.scala:2733) at org.apache.spark.SparkContext.runJob(SparkContext.scala:2716) at org.apache.spark.sql.execution.datasources.FileFormatWriter$.$anonfun$write$1(FileFormatWriter.scala:299) ... 135 more Caused by: org.apache.spark.SparkNumberFormatException: [CAST_INVALID_INPUT] The value 'Monthly' of the type "STRING" cannot be cast to "INT" because it is malformed. Correct the value as per the syntax, or change its target type. Use `try_cast` to tolerate malformed input and return NULL instead. If necessary set "spark.sql.ansi.enabled" to "false" to bypass this error. == SQL(line 5, position 1) == INSERT OVERWRITE TABLE demo_cdm_ach_res.heracles_periods SELECT * FROM insertion_temp ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^ at org.apache.spark.sql.errors.QueryExecutionErrors$.invalidInputInCastToNumberError(QueryExecutionErrors.scala:256) at org.apache.spark.sql.catalyst.util.UTF8StringUtils$.withException(UTF8StringUtils.scala:51) at org.apache.spark.sql.catalyst.util.UTF8StringUtils$.toIntExact(UTF8StringUtils.scala:34) at org.apache.spark.sql.catalyst.util.UTF8StringUtils.toIntExact(UTF8StringUtils.scala) at org.apache.spark.sql.catalyst.expressions.GeneratedClass$GeneratedIteratorForCodegenStage2.runningwindowfunction_doConsume_0$(Unknown Source) at org.apache.spark.sql.catalyst.expressions.GeneratedClass$GeneratedIteratorForCodegenStage2.processNext(Unknown Source) at org.apache.spark.sql.execution.BufferedRowIterator.hasNext(BufferedRowIterator.java:43) at org.apache.spark.sql.execution.WholeStageCodegenExec$$anon$1.hasNext(WholeStageCodegenExec.scala:761) at org.apache.spark.sql.execution.datasources.FileFormatWriter$.executeTask(FileFormatWriter.scala:384) at org.apache.spark.sql.execution.datasources.FileFormatWriter$.$anonfun$write$12(FileFormatWriter.scala:310) at org.apache.spark.scheduler.ResultTask.$anonfun$runTask$3(ResultTask.scala:75) at com.databricks.spark.util.ExecutorFrameProfiler$.record(ExecutorFrameProfiler.scala:110) at org.apache.spark.scheduler.ResultTask.$anonfun$runTask$1(ResultTask.scala:75) at com.databricks.spark.util.ExecutorFrameProfiler$.record(ExecutorFrameProfiler.scala:110) at org.apache.spark.scheduler.ResultTask.runTask(ResultTask.scala:55) at org.apache.spark.scheduler.Task.doRunTask(Task.scala:169) at org.apache.spark.scheduler.Task.$anonfun$run$4(Task.scala:137) at com.databricks.unity.EmptyHandle$.runWithAndClose(UCSHandle.scala:125) at org.apache.spark.scheduler.Task.$anonfun$run$1(Task.scala:137) at com.databricks.spark.util.ExecutorFrameProfiler$.record(ExecutorFrameProfiler.scala:110) at org.apache.spark.scheduler.Task.run(Task.scala:96) at org.apache.spark.executor.Executor$TaskRunner.$anonfun$run$13(Executor.scala:902) at org.apache.spark.util.Utils$.tryWithSafeFinally(Utils.scala:1697) at org.apache.spark.executor.Executor$TaskRunner.$anonfun$run$4(Executor.scala:905) at scala.runtime.java8.JFunction0$mcV$sp.apply(JFunction0$mcV$sp.java:23) at com.databricks.spark.util.ExecutorFrameProfiler$.record(ExecutorFrameProfiler.scala:110) at org.apache.spark.executor.Executor$TaskRunner.run(Executor.scala:760) ... 3 more , Query: WITH insertion_temp AS ( (SELECT CAST(row_number() over (order by period_order, period_start_date) AS INT) as period_id , period_name, period_order, period_type, period_start_date, period_end_date from demo_cdm_ach_tmp.f6rqimkutemp_period) UNION ALL (SELECT period_id, period_name, period_order, period_type, period_start_date, period_end_date FROM demo_cdm_ach_res.heracles_periods )) INSERT OVERWRITE TABLE demo_cdm_ach_res.heracles_periods SELECT * FROM insertion_temp. at org.yaorma.database.Database.executeSqlScript(Database.java:344) at org.yaorma.database.Database.executeSqlScript(Database.java:332) at org.nachc.tools.fhirtoomop.tools.databricks.build.A05_CreateAchillesDatabaseObjectsDatabricks.exec(A05_CreateAchillesDatabaseObjectsDatabricks.java:66) at org.nachc.tools.fhirtoomop.tools.databricks.build.A05_CreateAchillesDatabaseObjectsDatabricks.main(A05_CreateAchillesDatabaseObjectsDatabricks.java:40) Caused by: org.apache.ibatis.jdbc.RuntimeSqlException: Error executing: WITH insertion_temp AS ( (SELECT CAST(row_number() over (order by period_order, period_start_date) AS INT) as period_id , period_name, period_order, period_type, period_start_date, period_end_date from demo_cdm_ach_tmp.f6rqimkutemp_period) UNION ALL (SELECT period_id, period_name, period_order, period_type, period_start_date, period_end_date FROM demo_cdm_ach_res.heracles_periods )) INSERT OVERWRITE TABLE demo_cdm_ach_res.heracles_periods SELECT * FROM insertion_temp . Cause: java.sql.SQLException: [Simba][SparkJDBCDriver](500051) ERROR processing query/statement. Error Code: 0, SQL state: org.apache.hive.service.cli.HiveSQLException: Error running query: org.apache.spark.SparkException: Job aborted. at org.apache.spark.sql.hive.thriftserver.HiveThriftServerErrors$.runningQueryError(HiveThriftServerErrors.scala:48) at org.apache.spark.sql.hive.thriftserver.SparkExecuteStatementOperation.$anonfun$execute$1(SparkExecuteStatementOperation.scala:498) at scala.runtime.java8.JFunction0$mcV$sp.apply(JFunction0$mcV$sp.java:23) at com.databricks.unity.EmptyHandle$.runWith(UCSHandle.scala:124) at org.apache.spark.sql.hive.thriftserver.SparkExecuteStatementOperation.org$apache$spark$sql$hive$thriftserver$SparkExecuteStatementOperation$$execute(SparkExecuteStatementOperation.scala:410) at org.apache.spark.sql.hive.thriftserver.SparkExecuteStatementOperation$$anon$2$$anon$3.$anonfun$run$2(SparkExecuteStatementOperation.scala:321) at scala.runtime.java8.JFunction0$mcV$sp.apply(JFunction0$mcV$sp.java:23) at org.apache.spark.sql.hive.thriftserver.ThriftLocalProperties.withLocalProperties(ThriftLocalProperties.scala:149) at org.apache.spark.sql.hive.thriftserver.ThriftLocalProperties.withLocalProperties$(ThriftLocalProperties.scala:49) at org.apache.spark.sql.hive.thriftserver.SparkExecuteStatementOperation.withLocalProperties(SparkExecuteStatementOperation.scala:54) at org.apache.spark.sql.hive.thriftserver.SparkExecuteStatementOperation$$anon$2$$anon$3.run(SparkExecuteStatementOperation.scala:299) at org.apache.spark.sql.hive.thriftserver.SparkExecuteStatementOperation$$anon$2$$anon$3.run(SparkExecuteStatementOperation.scala:284) at java.security.AccessController.doPrivileged(Native Method) at javax.security.auth.Subject.doAs(Subject.java:422) at org.apache.hadoop.security.UserGroupInformation.doAs(UserGroupInformation.java:1878) at org.apache.spark.sql.hive.thriftserver.SparkExecuteStatementOperation$$anon$2.run(SparkExecuteStatementOperation.scala:333) at java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:511) at java.util.concurrent.FutureTask.run(FutureTask.java:266) at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149) at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624) at java.lang.Thread.run(Thread.java:750) Caused by: org.apache.spark.SparkException: Job aborted. at org.apache.spark.sql.errors.QueryExecutionErrors$.jobAbortedError(QueryExecutionErrors.scala:882) at org.apache.spark.sql.execution.datasources.FileFormatWriter$.$anonfun$write$1(FileFormatWriter.scala:334) at com.databricks.spark.util.FrameProfiler$.record(FrameProfiler.scala:80) at org.apache.spark.sql.execution.datasources.FileFormatWriter$.write(FileFormatWriter.scala:154) at com.databricks.sql.transaction.tahoe.commands.WriteIntoDeltaCommand.run(WriteIntoDeltaCommand.scala:70) at com.databricks.sql.transaction.tahoe.files.TransactionalWriteEdge.$anonfun$writeFiles$11(TransactionalWriteEdge.scala:564) at org.apache.spark.sql.execution.SQLExecution$.$anonfun$withCustomExecutionEnv$8(SQLExecution.scala:243) at org.apache.spark.sql.execution.SQLExecution$.withSQLConfPropagated(SQLExecution.scala:392) at org.apache.spark.sql.execution.SQLExecution$.$anonfun$withCustomExecutionEnv$1(SQLExecution.scala:188) at org.apache.spark.sql.SparkSession.withActive(SparkSession.scala:985) at org.apache.spark.sql.execution.SQLExecution$.withCustomExecutionEnv(SQLExecution.scala:142) at org.apache.spark.sql.execution.SQLExecution$.withNewExecutionId(SQLExecution.scala:342) at com.databricks.sql.transaction.tahoe.files.TransactionalWriteEdge.$anonfun$writeFiles$1(TransactionalWriteEdge.scala:564) at com.databricks.sql.transaction.tahoe.metering.DeltaLogging.withOperationTypeTag(DeltaLogging.scala:193) at com.databricks.sql.transaction.tahoe.metering.DeltaLogging.withOperationTypeTag$(DeltaLogging.scala:180) at com.databricks.sql.transaction.tahoe.OptimisticTransaction.withOperationTypeTag(OptimisticTransaction.scala:112) at com.databricks.sql.transaction.tahoe.metering.DeltaLogging.$anonfun$recordDeltaOperationInternal$2(DeltaLogging.scala:157) at com.databricks.spark.util.FrameProfiler$.record(FrameProfiler.scala:80) at com.databricks.sql.transaction.tahoe.metering.DeltaLogging.recordFrameProfile(DeltaLogging.scala:262) at com.databricks.sql.transaction.tahoe.metering.DeltaLogging.recordFrameProfile$(DeltaLogging.scala:260) at com.databricks.sql.transaction.tahoe.OptimisticTransaction.recordFrameProfile(OptimisticTransaction.scala:112) at com.databricks.sql.transaction.tahoe.metering.DeltaLogging.$anonfun$recordDeltaOperationInternal$1(DeltaLogging.scala:156) at com.databricks.logging.UsageLogging.$anonfun$recordOperation$1(UsageLogging.scala:541) at com.databricks.logging.UsageLogging.executeThunkAndCaptureResultTags$1(UsageLogging.scala:636) at com.databricks.logging.UsageLogging.$anonfun$recordOperationWithResultTags$4(UsageLogging.scala:657) at com.databricks.logging.UsageLogging.$anonfun$withAttributionContext$1(UsageLogging.scala:398) at scala.util.DynamicVariable.withValue(DynamicVariable.scala:62) at com.databricks.logging.AttributionContext$.withValue(AttributionContext.scala:147) at com.databricks.logging.UsageLogging.withAttributionContext(UsageLogging.scala:396) at com.databricks.logging.UsageLogging.withAttributionContext$(UsageLogging.scala:393) at com.databricks.spark.util.PublicDBLogging.withAttributionContext(DatabricksSparkUsageLogger.scala:22) at com.databricks.logging.UsageLogging.withAttributionTags(UsageLogging.scala:441) at com.databricks.logging.UsageLogging.withAttributionTags$(UsageLogging.scala:426) at com.databricks.spark.util.PublicDBLogging.withAttributionTags(DatabricksSparkUsageLogger.scala:22) at com.databricks.logging.UsageLogging.recordOperationWithResultTags(UsageLogging.scala:631) at com.databricks.logging.UsageLogging.recordOperationWithResultTags$(UsageLogging.scala:550) at com.databricks.spark.util.PublicDBLogging.recordOperationWithResultTags(DatabricksSparkUsageLogger.scala:22) at com.databricks.logging.UsageLogging.recordOperation(UsageLogging.scala:541) at com.databricks.logging.UsageLogging.recordOperation$(UsageLogging.scala:511) at com.databricks.spark.util.PublicDBLogging.recordOperation(DatabricksSparkUsageLogger.scala:22) at com.databricks.spark.util.PublicDBLogging.recordOperation0(DatabricksSparkUsageLogger.scala:62) at com.databricks.spark.util.DatabricksSparkUsageLogger.recordOperation(DatabricksSparkUsageLogger.scala:144) at com.databricks.spark.util.UsageLogger.recordOperation(UsageLogger.scala:72) at com.databricks.spark.util.UsageLogger.recordOperation$(UsageLogger.scala:59) at com.databricks.spark.util.DatabricksSparkUsageLogger.recordOperation(DatabricksSparkUsageLogger.scala:103) at com.databricks.spark.util.UsageLogging.recordOperation(UsageLogger.scala:433) at com.databricks.spark.util.UsageLogging.recordOperation$(UsageLogger.scala:412) at com.databricks.sql.transaction.tahoe.OptimisticTransaction.recordOperation(OptimisticTransaction.scala:112) at com.databricks.sql.transaction.tahoe.metering.DeltaLogging.recordDeltaOperationInternal(DeltaLogging.scala:155) at com.databricks.sql.transaction.tahoe.metering.DeltaLogging.recordDeltaOperation(DeltaLogging.scala:145) at com.databricks.sql.transaction.tahoe.metering.DeltaLogging.recordDeltaOperation$(DeltaLogging.scala:133) at com.databricks.sql.transaction.tahoe.OptimisticTransaction.recordDeltaOperation(OptimisticTransaction.scala:112) at com.databricks.sql.transaction.tahoe.files.TransactionalWriteEdge.$anonfun$recordWriteFilesOperation$1(TransactionalWriteEdge.scala:305) at com.databricks.sql.acl.CheckPermissions$.trusted(CheckPermissions.scala:1816) at com.databricks.sql.transaction.tahoe.files.TransactionalWriteEdge.recordWriteFilesOperation(TransactionalWriteEdge.scala:304) at com.databricks.sql.transaction.tahoe.files.TransactionalWriteEdge.writeFiles(TransactionalWriteEdge.scala:337) at com.databricks.sql.transaction.tahoe.files.TransactionalWriteEdge.writeFiles$(TransactionalWriteEdge.scala:331) at com.databricks.sql.transaction.tahoe.OptimisticTransaction.writeFiles(OptimisticTransaction.scala:112) at com.databricks.sql.transaction.tahoe.files.TransactionalWriteEdge.writeFiles(TransactionalWriteEdge.scala:604) at com.databricks.sql.transaction.tahoe.files.TransactionalWriteEdge.writeFiles$(TransactionalWriteEdge.scala:594) at com.databricks.sql.transaction.tahoe.OptimisticTransaction.writeFiles(OptimisticTransaction.scala:112) at com.databricks.sql.transaction.tahoe.files.TransactionalWrite.writeFiles(TransactionalWrite.scala:222) at com.databricks.sql.transaction.tahoe.files.TransactionalWrite.writeFiles$(TransactionalWrite.scala:219) at com.databricks.sql.transaction.tahoe.OptimisticTransaction.writeFiles(OptimisticTransaction.scala:112) at com.databricks.sql.transaction.tahoe.commands.WriteIntoDelta.write(WriteIntoDelta.scala:366) at com.databricks.sql.transaction.tahoe.commands.WriteIntoDelta.$anonfun$run$2(WriteIntoDelta.scala:110) at com.databricks.sql.transaction.tahoe.commands.WriteIntoDelta.$anonfun$run$2$adapted(WriteIntoDelta.scala:99) at com.databricks.sql.transaction.tahoe.DeltaLog.withNewTransaction(DeltaLog.scala:306) at com.databricks.sql.transaction.tahoe.commands.WriteIntoDelta.$anonfun$run$1(WriteIntoDelta.scala:99) at com.databricks.sql.acl.CheckPermissions$.trusted(CheckPermissions.scala:1816) at com.databricks.sql.transaction.tahoe.commands.WriteIntoDelta.run(WriteIntoDelta.scala:98) at com.databricks.sql.transaction.tahoe.catalog.WriteIntoDeltaBuilder$$anon$1$$anon$2.insert(DeltaTableV2.scala:342) at org.apache.spark.sql.execution.datasources.v2.SupportsV1Write.writeWithV1(V1FallbackWriters.scala:91) at org.apache.spark.sql.execution.datasources.v2.SupportsV1Write.writeWithV1$(V1FallbackWriters.scala:89) at org.apache.spark.sql.execution.datasources.v2.OverwriteByExpressionExecV1.writeWithV1(V1FallbackWriters.scala:53) at org.apache.spark.sql.execution.datasources.v2.V1FallbackWriters.run(V1FallbackWriters.scala:77) at org.apache.spark.sql.execution.datasources.v2.V1FallbackWriters.run$(V1FallbackWriters.scala:76) at org.apache.spark.sql.execution.datasources.v2.OverwriteByExpressionExecV1.run(V1FallbackWriters.scala:53) at org.apache.spark.sql.execution.datasources.v2.V2CommandExec.result$lzycompute(V2CommandExec.scala:43) at org.apache.spark.sql.execution.datasources.v2.V2CommandExec.result(V2CommandExec.scala:43) at org.apache.spark.sql.execution.datasources.v2.V2CommandExec.executeCollect(V2CommandExec.scala:49) at org.apache.spark.sql.execution.QueryExecution$$anonfun$$nestedInanonfun$eagerlyExecuteCommands$1$1.$anonfun$applyOrElse$2(QueryExecution.scala:229) at org.apache.spark.sql.execution.SQLExecution$.$anonfun$withCustomExecutionEnv$8(SQLExecution.scala:243) at org.apache.spark.sql.execution.SQLExecution$.withSQLConfPropagated(SQLExecution.scala:392) at org.apache.spark.sql.execution.SQLExecution$.$anonfun$withCustomExecutionEnv$1(SQLExecution.scala:188) at org.apache.spark.sql.SparkSession.withActive(SparkSession.scala:985) at org.apache.spark.sql.execution.SQLExecution$.withCustomExecutionEnv(SQLExecution.scala:142) at org.apache.spark.sql.execution.SQLExecution$.withNewExecutionId(SQLExecution.scala:342) at org.apache.spark.sql.execution.QueryExecution$$anonfun$$nestedInanonfun$eagerlyExecuteCommands$1$1.$anonfun$applyOrElse$1(QueryExecution.scala:229) at org.apache.spark.sql.execution.QueryExecution.org$apache$spark$sql$execution$QueryExecution$$withMVTagsIfNecessary(QueryExecution.scala:214) at org.apache.spark.sql.execution.QueryExecution$$anonfun$$nestedInanonfun$eagerlyExecuteCommands$1$1.applyOrElse(QueryExecution.scala:227) at org.apache.spark.sql.execution.QueryExecution$$anonfun$$nestedInanonfun$eagerlyExecuteCommands$1$1.applyOrElse(QueryExecution.scala:220) at org.apache.spark.sql.catalyst.trees.TreeNode.$anonfun$transformDownWithPruning$1(TreeNode.scala:512) at org.apache.spark.sql.catalyst.trees.CurrentOrigin$.withOrigin(TreeNode.scala:99) at org.apache.spark.sql.catalyst.trees.TreeNode.transformDownWithPruning(TreeNode.scala:512) at org.apache.spark.sql.catalyst.plans.logical.LogicalPlan.org$apache$spark$sql$catalyst$plans$logical$AnalysisHelper$$super$transformDownWithPruning(LogicalPlan.scala:31) at org.apache.spark.sql.catalyst.plans.logical.AnalysisHelper.transformDownWithPruning(AnalysisHelper.scala:298) at org.apache.spark.sql.catalyst.plans.logical.AnalysisHelper.transformDownWithPruning$(AnalysisHelper.scala:294) at org.apache.spark.sql.catalyst.plans.logical.LogicalPlan.transformDownWithPruning(LogicalPlan.scala:31) at org.apache.spark.sql.catalyst.plans.logical.LogicalPlan.transformDownWithPruning(LogicalPlan.scala:31) at org.apache.spark.sql.catalyst.trees.TreeNode.transformDown(TreeNode.scala:488) at org.apache.spark.sql.execution.QueryExecution.$anonfun$eagerlyExecuteCommands$1(QueryExecution.scala:220) at org.apache.spark.sql.catalyst.plans.logical.AnalysisHelper$.allowInvokingTransformsInAnalyzer(AnalysisHelper.scala:354) at org.apache.spark.sql.execution.QueryExecution.eagerlyExecuteCommands(QueryExecution.scala:220) at org.apache.spark.sql.execution.QueryExecution.commandExecuted$lzycompute(QueryExecution.scala:174) at org.apache.spark.sql.execution.QueryExecution.commandExecuted(QueryExecution.scala:165) at org.apache.spark.sql.Dataset.(Dataset.scala:238) at org.apache.spark.sql.hive.thriftserver.SparkExecuteStatementOperation.$anonfun$analyzeQuery$2(SparkExecuteStatementOperation.scala:404) at org.apache.spark.sql.SparkSession.withActive(SparkSession.scala:985) at org.apache.spark.sql.hive.thriftserver.SparkExecuteStatementOperation.$anonfun$analyzeQuery$1(SparkExecuteStatementOperation.scala:397) at org.apache.spark.sql.hive.thriftserver.SparkExecuteStatementOperation.getOrCreateDF(SparkExecuteStatementOperation.scala:387) at org.apache.spark.sql.hive.thriftserver.SparkExecuteStatementOperation.analyzeQuery(SparkExecuteStatementOperation.scala:397) at org.apache.spark.sql.hive.thriftserver.SparkExecuteStatementOperation.$anonfun$execute$5(SparkExecuteStatementOperation.scala:446) at org.apache.spark.util.Utils$.timeTakenMs(Utils.scala:692) at org.apache.spark.sql.execution.qrc.CacheEventLogger.recordLatency(QueryResultCache.scala:149) at org.apache.spark.sql.execution.qrc.CacheEventLogger.recordLatency$(QueryResultCache.scala:145) at org.apache.spark.sql.hive.thriftserver.SparkExecuteStatementOperation.recordLatency(SparkExecuteStatementOperation.scala:54) at org.apache.spark.sql.hive.thriftserver.SparkExecuteStatementOperation.$anonfun$execute$1(SparkExecuteStatementOperation.scala:446) ... 19 more Caused by: org.apache.spark.SparkException: Job aborted due to stage failure: Task 0 in stage 92321.0 failed 4 times, most recent failure: Lost task 0.3 in stage 92321.0 (TID 140794) (10.22.225.217 executor 262): org.apache.spark.SparkNumberFormatException: [CAST_INVALID_INPUT] The value 'Monthly' of the type "STRING" cannot be cast to "INT" because it is malformed. Correct the value as per the syntax, or change its target type. Use `try_cast` to tolerate malformed input and return NULL instead. If necessary set "spark.sql.ansi.enabled" to "false" to bypass this error. == SQL(line 5, position 1) == INSERT OVERWRITE TABLE demo_cdm_ach_res.heracles_periods SELECT * FROM insertion_temp ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^ at org.apache.spark.sql.errors.QueryExecutionErrors$.invalidInputInCastToNumberError(QueryExecutionErrors.scala:256) at org.apache.spark.sql.catalyst.util.UTF8StringUtils$.withException(UTF8StringUtils.scala:51) at org.apache.spark.sql.catalyst.util.UTF8StringUtils$.toIntExact(UTF8StringUtils.scala:34) at org.apache.spark.sql.catalyst.util.UTF8StringUtils.toIntExact(UTF8StringUtils.scala) at org.apache.spark.sql.catalyst.expressions.GeneratedClass$GeneratedIteratorForCodegenStage2.runningwindowfunction_doConsume_0$(Unknown Source) at org.apache.spark.sql.catalyst.expressions.GeneratedClass$GeneratedIteratorForCodegenStage2.processNext(Unknown Source) at org.apache.spark.sql.execution.BufferedRowIterator.hasNext(BufferedRowIterator.java:43) at org.apache.spark.sql.execution.WholeStageCodegenExec$$anon$1.hasNext(WholeStageCodegenExec.scala:761) at org.apache.spark.sql.execution.datasources.FileFormatWriter$.executeTask(FileFormatWriter.scala:384) at org.apache.spark.sql.execution.datasources.FileFormatWriter$.$anonfun$write$12(FileFormatWriter.scala:310) at org.apache.spark.scheduler.ResultTask.$anonfun$runTask$3(ResultTask.scala:75) at com.databricks.spark.util.ExecutorFrameProfiler$.record(ExecutorFrameProfiler.scala:110) at org.apache.spark.scheduler.ResultTask.$anonfun$runTask$1(ResultTask.scala:75) at com.databricks.spark.util.ExecutorFrameProfiler$.record(ExecutorFrameProfiler.scala:110) at org.apache.spark.scheduler.ResultTask.runTask(ResultTask.scala:55) at org.apache.spark.scheduler.Task.doRunTask(Task.scala:169) at org.apache.spark.scheduler.Task.$anonfun$run$4(Task.scala:137) at com.databricks.unity.EmptyHandle$.runWithAndClose(UCSHandle.scala:125) at org.apache.spark.scheduler.Task.$anonfun$run$1(Task.scala:137) at com.databricks.spark.util.ExecutorFrameProfiler$.record(ExecutorFrameProfiler.scala:110) at org.apache.spark.scheduler.Task.run(Task.scala:96) at org.apache.spark.executor.Executor$TaskRunner.$anonfun$run$13(Executor.scala:902) at org.apache.spark.util.Utils$.tryWithSafeFinally(Utils.scala:1697) at org.apache.spark.executor.Executor$TaskRunner.$anonfun$run$4(Executor.scala:905) at scala.runtime.java8.JFunction0$mcV$sp.apply(JFunction0$mcV$sp.java:23) at com.databricks.spark.util.ExecutorFrameProfiler$.record(ExecutorFrameProfiler.scala:110) at org.apache.spark.executor.Executor$TaskRunner.run(Executor.scala:760) at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149) at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624) at java.lang.Thread.run(Thread.java:750) Driver stacktrace: at org.apache.spark.scheduler.DAGScheduler.failJobAndIndependentStages(DAGScheduler.scala:3376) at org.apache.spark.scheduler.DAGScheduler.$anonfun$abortStage$2(DAGScheduler.scala:3308) at org.apache.spark.scheduler.DAGScheduler.$anonfun$abortStage$2$adapted(DAGScheduler.scala:3299) at scala.collection.mutable.ResizableArray.foreach(ResizableArray.scala:62) at scala.collection.mutable.ResizableArray.foreach$(ResizableArray.scala:55) at scala.collection.mutable.ArrayBuffer.foreach(ArrayBuffer.scala:49) at org.apache.spark.scheduler.DAGScheduler.abortStage(DAGScheduler.scala:3299) at org.apache.spark.scheduler.DAGScheduler.$anonfun$handleTaskSetFailed$1(DAGScheduler.scala:1428) at org.apache.spark.scheduler.DAGScheduler.$anonfun$handleTaskSetFailed$1$adapted(DAGScheduler.scala:1428) at scala.Option.foreach(Option.scala:407) at org.apache.spark.scheduler.DAGScheduler.handleTaskSetFailed(DAGScheduler.scala:1428) at org.apache.spark.scheduler.DAGSchedulerEventProcessLoop.doOnReceive(DAGScheduler.scala:3588) at org.apache.spark.scheduler.DAGSchedulerEventProcessLoop.onReceive(DAGScheduler.scala:3526) at org.apache.spark.scheduler.DAGSchedulerEventProcessLoop.onReceive(DAGScheduler.scala:3514) at org.apache.spark.util.EventLoop$$anon$1.run(EventLoop.scala:51) at org.apache.spark.scheduler.DAGScheduler.$anonfun$runJob$1(DAGScheduler.scala:1177) at scala.runtime.java8.JFunction0$mcV$sp.apply(JFunction0$mcV$sp.java:23) at com.databricks.spark.util.FrameProfiler$.record(FrameProfiler.scala:80) at org.apache.spark.scheduler.DAGScheduler.runJob(DAGScheduler.scala:1165) at org.apache.spark.SparkContext.runJobInternal(SparkContext.scala:2733) at org.apache.spark.SparkContext.runJob(SparkContext.scala:2716) at org.apache.spark.sql.execution.datasources.FileFormatWriter$.$anonfun$write$1(FileFormatWriter.scala:299) ... 135 more Caused by: org.apache.spark.SparkNumberFormatException: [CAST_INVALID_INPUT] The value 'Monthly' of the type "STRING" cannot be cast to "INT" because it is malformed. Correct the value as per the syntax, or change its target type. Use `try_cast` to tolerate malformed input and return NULL instead. If necessary set "spark.sql.ansi.enabled" to "false" to bypass this error. == SQL(line 5, position 1) == INSERT OVERWRITE TABLE demo_cdm_ach_res.heracles_periods SELECT * FROM insertion_temp ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^ at org.apache.spark.sql.errors.QueryExecutionErrors$.invalidInputInCastToNumberError(QueryExecutionErrors.scala:256) at org.apache.spark.sql.catalyst.util.UTF8StringUtils$.withException(UTF8StringUtils.scala:51) at org.apache.spark.sql.catalyst.util.UTF8StringUtils$.toIntExact(UTF8StringUtils.scala:34) at org.apache.spark.sql.catalyst.util.UTF8StringUtils.toIntExact(UTF8StringUtils.scala) at org.apache.spark.sql.catalyst.expressions.GeneratedClass$GeneratedIteratorForCodegenStage2.runningwindowfunction_doConsume_0$(Unknown Source) at org.apache.spark.sql.catalyst.expressions.GeneratedClass$GeneratedIteratorForCodegenStage2.processNext(Unknown Source) at org.apache.spark.sql.execution.BufferedRowIterator.hasNext(BufferedRowIterator.java:43) at org.apache.spark.sql.execution.WholeStageCodegenExec$$anon$1.hasNext(WholeStageCodegenExec.scala:761) at org.apache.spark.sql.execution.datasources.FileFormatWriter$.executeTask(FileFormatWriter.scala:384) at org.apache.spark.sql.execution.datasources.FileFormatWriter$.$anonfun$write$12(FileFormatWriter.scala:310) at org.apache.spark.scheduler.ResultTask.$anonfun$runTask$3(ResultTask.scala:75) at com.databricks.spark.util.ExecutorFrameProfiler$.record(ExecutorFrameProfiler.scala:110) at org.apache.spark.scheduler.ResultTask.$anonfun$runTask$1(ResultTask.scala:75) at com.databricks.spark.util.ExecutorFrameProfiler$.record(ExecutorFrameProfiler.scala:110) at org.apache.spark.scheduler.ResultTask.runTask(ResultTask.scala:55) at org.apache.spark.scheduler.Task.doRunTask(Task.scala:169) at org.apache.spark.scheduler.Task.$anonfun$run$4(Task.scala:137) at com.databricks.unity.EmptyHandle$.runWithAndClose(UCSHandle.scala:125) at org.apache.spark.scheduler.Task.$anonfun$run$1(Task.scala:137) at com.databricks.spark.util.ExecutorFrameProfiler$.record(ExecutorFrameProfiler.scala:110) at org.apache.spark.scheduler.Task.run(Task.scala:96) at org.apache.spark.executor.Executor$TaskRunner.$anonfun$run$13(Executor.scala:902) at org.apache.spark.util.Utils$.tryWithSafeFinally(Utils.scala:1697) at org.apache.spark.executor.Executor$TaskRunner.$anonfun$run$4(Executor.scala:905) at scala.runtime.java8.JFunction0$mcV$sp.apply(JFunction0$mcV$sp.java:23) at com.databricks.spark.util.ExecutorFrameProfiler$.record(ExecutorFrameProfiler.scala:110) at org.apache.spark.executor.Executor$TaskRunner.run(Executor.scala:760) ... 3 more , Query: WITH insertion_temp AS ( (SELECT CAST(row_number() over (order by period_order, period_start_date) AS INT) as period_id , period_name, period_order, period_type, period_start_date, period_end_date from demo_cdm_ach_tmp.f6rqimkutemp_period) UNION ALL (SELECT period_id, period_name, period_order, period_type, period_start_date, period_end_date FROM demo_cdm_ach_res.heracles_periods )) INSERT OVERWRITE TABLE demo_cdm_ach_res.heracles_periods SELECT * FROM insertion_temp. at org.apache.ibatis.jdbc.ScriptRunner.executeLineByLine(ScriptRunner.java:109) at org.apache.ibatis.jdbc.ScriptRunner.runScript(ScriptRunner.java:71) at org.yaorma.database.Database.executeSqlScript(Database.java:342) ... 3 more Caused by: java.sql.SQLException: [Simba][SparkJDBCDriver](500051) ERROR processing query/statement. Error Code: 0, SQL state: org.apache.hive.service.cli.HiveSQLException: Error running query: org.apache.spark.SparkException: Job aborted. at org.apache.spark.sql.hive.thriftserver.HiveThriftServerErrors$.runningQueryError(HiveThriftServerErrors.scala:48) at org.apache.spark.sql.hive.thriftserver.SparkExecuteStatementOperation.$anonfun$execute$1(SparkExecuteStatementOperation.scala:498) at scala.runtime.java8.JFunction0$mcV$sp.apply(JFunction0$mcV$sp.java:23) at com.databricks.unity.EmptyHandle$.runWith(UCSHandle.scala:124) at org.apache.spark.sql.hive.thriftserver.SparkExecuteStatementOperation.org$apache$spark$sql$hive$thriftserver$SparkExecuteStatementOperation$$execute(SparkExecuteStatementOperation.scala:410) at org.apache.spark.sql.hive.thriftserver.SparkExecuteStatementOperation$$anon$2$$anon$3.$anonfun$run$2(SparkExecuteStatementOperation.scala:321) at scala.runtime.java8.JFunction0$mcV$sp.apply(JFunction0$mcV$sp.java:23) at org.apache.spark.sql.hive.thriftserver.ThriftLocalProperties.withLocalProperties(ThriftLocalProperties.scala:149) at org.apache.spark.sql.hive.thriftserver.ThriftLocalProperties.withLocalProperties$(ThriftLocalProperties.scala:49) at org.apache.spark.sql.hive.thriftserver.SparkExecuteStatementOperation.withLocalProperties(SparkExecuteStatementOperation.scala:54) at org.apache.spark.sql.hive.thriftserver.SparkExecuteStatementOperation$$anon$2$$anon$3.run(SparkExecuteStatementOperation.scala:299) at org.apache.spark.sql.hive.thriftserver.SparkExecuteStatementOperation$$anon$2$$anon$3.run(SparkExecuteStatementOperation.scala:284) at java.security.AccessController.doPrivileged(Native Method) at javax.security.auth.Subject.doAs(Subject.java:422) at org.apache.hadoop.security.UserGroupInformation.doAs(UserGroupInformation.java:1878) at org.apache.spark.sql.hive.thriftserver.SparkExecuteStatementOperation$$anon$2.run(SparkExecuteStatementOperation.scala:333) at java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:511) at java.util.concurrent.FutureTask.run(FutureTask.java:266) at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149) at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624) at java.lang.Thread.run(Thread.java:750) Caused by: org.apache.spark.SparkException: Job aborted. at org.apache.spark.sql.errors.QueryExecutionErrors$.jobAbortedError(QueryExecutionErrors.scala:882) at org.apache.spark.sql.execution.datasources.FileFormatWriter$.$anonfun$write$1(FileFormatWriter.scala:334) at com.databricks.spark.util.FrameProfiler$.record(FrameProfiler.scala:80) at org.apache.spark.sql.execution.datasources.FileFormatWriter$.write(FileFormatWriter.scala:154) at com.databricks.sql.transaction.tahoe.commands.WriteIntoDeltaCommand.run(WriteIntoDeltaCommand.scala:70) at com.databricks.sql.transaction.tahoe.files.TransactionalWriteEdge.$anonfun$writeFiles$11(TransactionalWriteEdge.scala:564) at org.apache.spark.sql.execution.SQLExecution$.$anonfun$withCustomExecutionEnv$8(SQLExecution.scala:243) at org.apache.spark.sql.execution.SQLExecution$.withSQLConfPropagated(SQLExecution.scala:392) at org.apache.spark.sql.execution.SQLExecution$.$anonfun$withCustomExecutionEnv$1(SQLExecution.scala:188) at org.apache.spark.sql.SparkSession.withActive(SparkSession.scala:985) at org.apache.spark.sql.execution.SQLExecution$.withCustomExecutionEnv(SQLExecution.scala:142) at org.apache.spark.sql.execution.SQLExecution$.withNewExecutionId(SQLExecution.scala:342) at com.databricks.sql.transaction.tahoe.files.TransactionalWriteEdge.$anonfun$writeFiles$1(TransactionalWriteEdge.scala:564) at com.databricks.sql.transaction.tahoe.metering.DeltaLogging.withOperationTypeTag(DeltaLogging.scala:193) at com.databricks.sql.transaction.tahoe.metering.DeltaLogging.withOperationTypeTag$(DeltaLogging.scala:180) at com.databricks.sql.transaction.tahoe.OptimisticTransaction.withOperationTypeTag(OptimisticTransaction.scala:112) at com.databricks.sql.transaction.tahoe.metering.DeltaLogging.$anonfun$recordDeltaOperationInternal$2(DeltaLogging.scala:157) at com.databricks.spark.util.FrameProfiler$.record(FrameProfiler.scala:80) at com.databricks.sql.transaction.tahoe.metering.DeltaLogging.recordFrameProfile(DeltaLogging.scala:262) at com.databricks.sql.transaction.tahoe.metering.DeltaLogging.recordFrameProfile$(DeltaLogging.scala:260) at com.databricks.sql.transaction.tahoe.OptimisticTransaction.recordFrameProfile(OptimisticTransaction.scala:112) at com.databricks.sql.transaction.tahoe.metering.DeltaLogging.$anonfun$recordDeltaOperationInternal$1(DeltaLogging.scala:156) at com.databricks.logging.UsageLogging.$anonfun$recordOperation$1(UsageLogging.scala:541) at com.databricks.logging.UsageLogging.executeThunkAndCaptureResultTags$1(UsageLogging.scala:636) at com.databricks.logging.UsageLogging.$anonfun$recordOperationWithResultTags$4(UsageLogging.scala:657) at com.databricks.logging.UsageLogging.$anonfun$withAttributionContext$1(UsageLogging.scala:398) at scala.util.DynamicVariable.withValue(DynamicVariable.scala:62) at com.databricks.logging.AttributionContext$.withValue(AttributionContext.scala:147) at com.databricks.logging.UsageLogging.withAttributionContext(UsageLogging.scala:396) at com.databricks.logging.UsageLogging.withAttributionContext$(UsageLogging.scala:393) at com.databricks.spark.util.PublicDBLogging.withAttributionContext(DatabricksSparkUsageLogger.scala:22) at com.databricks.logging.UsageLogging.withAttributionTags(UsageLogging.scala:441) at com.databricks.logging.UsageLogging.withAttributionTags$(UsageLogging.scala:426) at com.databricks.spark.util.PublicDBLogging.withAttributionTags(DatabricksSparkUsageLogger.scala:22) at com.databricks.logging.UsageLogging.recordOperationWithResultTags(UsageLogging.scala:631) at com.databricks.logging.UsageLogging.recordOperationWithResultTags$(UsageLogging.scala:550) at com.databricks.spark.util.PublicDBLogging.recordOperationWithResultTags(DatabricksSparkUsageLogger.scala:22) at com.databricks.logging.UsageLogging.recordOperation(UsageLogging.scala:541) at com.databricks.logging.UsageLogging.recordOperation$(UsageLogging.scala:511) at com.databricks.spark.util.PublicDBLogging.recordOperation(DatabricksSparkUsageLogger.scala:22) at com.databricks.spark.util.PublicDBLogging.recordOperation0(DatabricksSparkUsageLogger.scala:62) at com.databricks.spark.util.DatabricksSparkUsageLogger.recordOperation(DatabricksSparkUsageLogger.scala:144) at com.databricks.spark.util.UsageLogger.recordOperation(UsageLogger.scala:72) at com.databricks.spark.util.UsageLogger.recordOperation$(UsageLogger.scala:59) at com.databricks.spark.util.DatabricksSparkUsageLogger.recordOperation(DatabricksSparkUsageLogger.scala:103) at com.databricks.spark.util.UsageLogging.recordOperation(UsageLogger.scala:433) at com.databricks.spark.util.UsageLogging.recordOperation$(UsageLogger.scala:412) at com.databricks.sql.transaction.tahoe.OptimisticTransaction.recordOperation(OptimisticTransaction.scala:112) at com.databricks.sql.transaction.tahoe.metering.DeltaLogging.recordDeltaOperationInternal(DeltaLogging.scala:155) at com.databricks.sql.transaction.tahoe.metering.DeltaLogging.recordDeltaOperation(DeltaLogging.scala:145) at com.databricks.sql.transaction.tahoe.metering.DeltaLogging.recordDeltaOperation$(DeltaLogging.scala:133) at com.databricks.sql.transaction.tahoe.OptimisticTransaction.recordDeltaOperation(OptimisticTransaction.scala:112) at com.databricks.sql.transaction.tahoe.files.TransactionalWriteEdge.$anonfun$recordWriteFilesOperation$1(TransactionalWriteEdge.scala:305) at com.databricks.sql.acl.CheckPermissions$.trusted(CheckPermissions.scala:1816) at com.databricks.sql.transaction.tahoe.files.TransactionalWriteEdge.recordWriteFilesOperation(TransactionalWriteEdge.scala:304) at com.databricks.sql.transaction.tahoe.files.TransactionalWriteEdge.writeFiles(TransactionalWriteEdge.scala:337) at com.databricks.sql.transaction.tahoe.files.TransactionalWriteEdge.writeFiles$(TransactionalWriteEdge.scala:331) at com.databricks.sql.transaction.tahoe.OptimisticTransaction.writeFiles(OptimisticTransaction.scala:112) at com.databricks.sql.transaction.tahoe.files.TransactionalWriteEdge.writeFiles(TransactionalWriteEdge.scala:604) at com.databricks.sql.transaction.tahoe.files.TransactionalWriteEdge.writeFiles$(TransactionalWriteEdge.scala:594) at com.databricks.sql.transaction.tahoe.OptimisticTransaction.writeFiles(OptimisticTransaction.scala:112) at com.databricks.sql.transaction.tahoe.files.TransactionalWrite.writeFiles(TransactionalWrite.scala:222) at com.databricks.sql.transaction.tahoe.files.TransactionalWrite.writeFiles$(TransactionalWrite.scala:219) at com.databricks.sql.transaction.tahoe.OptimisticTransaction.writeFiles(OptimisticTransaction.scala:112) at com.databricks.sql.transaction.tahoe.commands.WriteIntoDelta.write(WriteIntoDelta.scala:366) at com.databricks.sql.transaction.tahoe.commands.WriteIntoDelta.$anonfun$run$2(WriteIntoDelta.scala:110) at com.databricks.sql.transaction.tahoe.commands.WriteIntoDelta.$anonfun$run$2$adapted(WriteIntoDelta.scala:99) at com.databricks.sql.transaction.tahoe.DeltaLog.withNewTransaction(DeltaLog.scala:306) at com.databricks.sql.transaction.tahoe.commands.WriteIntoDelta.$anonfun$run$1(WriteIntoDelta.scala:99) at com.databricks.sql.acl.CheckPermissions$.trusted(CheckPermissions.scala:1816) at com.databricks.sql.transaction.tahoe.commands.WriteIntoDelta.run(WriteIntoDelta.scala:98) at com.databricks.sql.transaction.tahoe.catalog.WriteIntoDeltaBuilder$$anon$1$$anon$2.insert(DeltaTableV2.scala:342) at org.apache.spark.sql.execution.datasources.v2.SupportsV1Write.writeWithV1(V1FallbackWriters.scala:91) at org.apache.spark.sql.execution.datasources.v2.SupportsV1Write.writeWithV1$(V1FallbackWriters.scala:89) at org.apache.spark.sql.execution.datasources.v2.OverwriteByExpressionExecV1.writeWithV1(V1FallbackWriters.scala:53) at org.apache.spark.sql.execution.datasources.v2.V1FallbackWriters.run(V1FallbackWriters.scala:77) at org.apache.spark.sql.execution.datasources.v2.V1FallbackWriters.run$(V1FallbackWriters.scala:76) at org.apache.spark.sql.execution.datasources.v2.OverwriteByExpressionExecV1.run(V1FallbackWriters.scala:53) at org.apache.spark.sql.execution.datasources.v2.V2CommandExec.result$lzycompute(V2CommandExec.scala:43) at org.apache.spark.sql.execution.datasources.v2.V2CommandExec.result(V2CommandExec.scala:43) at org.apache.spark.sql.execution.datasources.v2.V2CommandExec.executeCollect(V2CommandExec.scala:49) at org.apache.spark.sql.execution.QueryExecution$$anonfun$$nestedInanonfun$eagerlyExecuteCommands$1$1.$anonfun$applyOrElse$2(QueryExecution.scala:229) at org.apache.spark.sql.execution.SQLExecution$.$anonfun$withCustomExecutionEnv$8(SQLExecution.scala:243) at org.apache.spark.sql.execution.SQLExecution$.withSQLConfPropagated(SQLExecution.scala:392) at org.apache.spark.sql.execution.SQLExecution$.$anonfun$withCustomExecutionEnv$1(SQLExecution.scala:188) at org.apache.spark.sql.SparkSession.withActive(SparkSession.scala:985) at org.apache.spark.sql.execution.SQLExecution$.withCustomExecutionEnv(SQLExecution.scala:142) at org.apache.spark.sql.execution.SQLExecution$.withNewExecutionId(SQLExecution.scala:342) at org.apache.spark.sql.execution.QueryExecution$$anonfun$$nestedInanonfun$eagerlyExecuteCommands$1$1.$anonfun$applyOrElse$1(QueryExecution.scala:229) at org.apache.spark.sql.execution.QueryExecution.org$apache$spark$sql$execution$QueryExecution$$withMVTagsIfNecessary(QueryExecution.scala:214) at org.apache.spark.sql.execution.QueryExecution$$anonfun$$nestedInanonfun$eagerlyExecuteCommands$1$1.applyOrElse(QueryExecution.scala:227) at org.apache.spark.sql.execution.QueryExecution$$anonfun$$nestedInanonfun$eagerlyExecuteCommands$1$1.applyOrElse(QueryExecution.scala:220) at org.apache.spark.sql.catalyst.trees.TreeNode.$anonfun$transformDownWithPruning$1(TreeNode.scala:512) at org.apache.spark.sql.catalyst.trees.CurrentOrigin$.withOrigin(TreeNode.scala:99) at org.apache.spark.sql.catalyst.trees.TreeNode.transformDownWithPruning(TreeNode.scala:512) at org.apache.spark.sql.catalyst.plans.logical.LogicalPlan.org$apache$spark$sql$catalyst$plans$logical$AnalysisHelper$$super$transformDownWithPruning(LogicalPlan.scala:31) at org.apache.spark.sql.catalyst.plans.logical.AnalysisHelper.transformDownWithPruning(AnalysisHelper.scala:298) at org.apache.spark.sql.catalyst.plans.logical.AnalysisHelper.transformDownWithPruning$(AnalysisHelper.scala:294) at org.apache.spark.sql.catalyst.plans.logical.LogicalPlan.transformDownWithPruning(LogicalPlan.scala:31) at org.apache.spark.sql.catalyst.plans.logical.LogicalPlan.transformDownWithPruning(LogicalPlan.scala:31) at org.apache.spark.sql.catalyst.trees.TreeNode.transformDown(TreeNode.scala:488) at org.apache.spark.sql.execution.QueryExecution.$anonfun$eagerlyExecuteCommands$1(QueryExecution.scala:220) at org.apache.spark.sql.catalyst.plans.logical.AnalysisHelper$.allowInvokingTransformsInAnalyzer(AnalysisHelper.scala:354) at org.apache.spark.sql.execution.QueryExecution.eagerlyExecuteCommands(QueryExecution.scala:220) at org.apache.spark.sql.execution.QueryExecution.commandExecuted$lzycompute(QueryExecution.scala:174) at org.apache.spark.sql.execution.QueryExecution.commandExecuted(QueryExecution.scala:165) at org.apache.spark.sql.Dataset.(Dataset.scala:238) at org.apache.spark.sql.hive.thriftserver.SparkExecuteStatementOperation.$anonfun$analyzeQuery$2(SparkExecuteStatementOperation.scala:404) at org.apache.spark.sql.SparkSession.withActive(SparkSession.scala:985) at org.apache.spark.sql.hive.thriftserver.SparkExecuteStatementOperation.$anonfun$analyzeQuery$1(SparkExecuteStatementOperation.scala:397) at org.apache.spark.sql.hive.thriftserver.SparkExecuteStatementOperation.getOrCreateDF(SparkExecuteStatementOperation.scala:387) at org.apache.spark.sql.hive.thriftserver.SparkExecuteStatementOperation.analyzeQuery(SparkExecuteStatementOperation.scala:397) at org.apache.spark.sql.hive.thriftserver.SparkExecuteStatementOperation.$anonfun$execute$5(SparkExecuteStatementOperation.scala:446) at org.apache.spark.util.Utils$.timeTakenMs(Utils.scala:692) at org.apache.spark.sql.execution.qrc.CacheEventLogger.recordLatency(QueryResultCache.scala:149) at org.apache.spark.sql.execution.qrc.CacheEventLogger.recordLatency$(QueryResultCache.scala:145) at org.apache.spark.sql.hive.thriftserver.SparkExecuteStatementOperation.recordLatency(SparkExecuteStatementOperation.scala:54) at org.apache.spark.sql.hive.thriftserver.SparkExecuteStatementOperation.$anonfun$execute$1(SparkExecuteStatementOperation.scala:446) ... 19 more Caused by: org.apache.spark.SparkException: Job aborted due to stage failure: Task 0 in stage 92321.0 failed 4 times, most recent failure: Lost task 0.3 in stage 92321.0 (TID 140794) (10.22.225.217 executor 262): org.apache.spark.SparkNumberFormatException: [CAST_INVALID_INPUT] The value 'Monthly' of the type "STRING" cannot be cast to "INT" because it is malformed. Correct the value as per the syntax, or change its target type. Use `try_cast` to tolerate malformed input and return NULL instead. If necessary set "spark.sql.ansi.enabled" to "false" to bypass this error. == SQL(line 5, position 1) == INSERT OVERWRITE TABLE demo_cdm_ach_res.heracles_periods SELECT * FROM insertion_temp ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^ at org.apache.spark.sql.errors.QueryExecutionErrors$.invalidInputInCastToNumberError(QueryExecutionErrors.scala:256) at org.apache.spark.sql.catalyst.util.UTF8StringUtils$.withException(UTF8StringUtils.scala:51) at org.apache.spark.sql.catalyst.util.UTF8StringUtils$.toIntExact(UTF8StringUtils.scala:34) at org.apache.spark.sql.catalyst.util.UTF8StringUtils.toIntExact(UTF8StringUtils.scala) at org.apache.spark.sql.catalyst.expressions.GeneratedClass$GeneratedIteratorForCodegenStage2.runningwindowfunction_doConsume_0$(Unknown Source) at org.apache.spark.sql.catalyst.expressions.GeneratedClass$GeneratedIteratorForCodegenStage2.processNext(Unknown Source) at org.apache.spark.sql.execution.BufferedRowIterator.hasNext(BufferedRowIterator.java:43) at org.apache.spark.sql.execution.WholeStageCodegenExec$$anon$1.hasNext(WholeStageCodegenExec.scala:761) at org.apache.spark.sql.execution.datasources.FileFormatWriter$.executeTask(FileFormatWriter.scala:384) at org.apache.spark.sql.execution.datasources.FileFormatWriter$.$anonfun$write$12(FileFormatWriter.scala:310) at org.apache.spark.scheduler.ResultTask.$anonfun$runTask$3(ResultTask.scala:75) at com.databricks.spark.util.ExecutorFrameProfiler$.record(ExecutorFrameProfiler.scala:110) at org.apache.spark.scheduler.ResultTask.$anonfun$runTask$1(ResultTask.scala:75) at com.databricks.spark.util.ExecutorFrameProfiler$.record(ExecutorFrameProfiler.scala:110) at org.apache.spark.scheduler.ResultTask.runTask(ResultTask.scala:55) at org.apache.spark.scheduler.Task.doRunTask(Task.scala:169) at org.apache.spark.scheduler.Task.$anonfun$run$4(Task.scala:137) at com.databricks.unity.EmptyHandle$.runWithAndClose(UCSHandle.scala:125) at org.apache.spark.scheduler.Task.$anonfun$run$1(Task.scala:137) at com.databricks.spark.util.ExecutorFrameProfiler$.record(ExecutorFrameProfiler.scala:110) at org.apache.spark.scheduler.Task.run(Task.scala:96) at org.apache.spark.executor.Executor$TaskRunner.$anonfun$run$13(Executor.scala:902) at org.apache.spark.util.Utils$.tryWithSafeFinally(Utils.scala:1697) at org.apache.spark.executor.Executor$TaskRunner.$anonfun$run$4(Executor.scala:905) at scala.runtime.java8.JFunction0$mcV$sp.apply(JFunction0$mcV$sp.java:23) at com.databricks.spark.util.ExecutorFrameProfiler$.record(ExecutorFrameProfiler.scala:110) at org.apache.spark.executor.Executor$TaskRunner.run(Executor.scala:760) at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149) at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624) at java.lang.Thread.run(Thread.java:750) Driver stacktrace: at org.apache.spark.scheduler.DAGScheduler.failJobAndIndependentStages(DAGScheduler.scala:3376) at org.apache.spark.scheduler.DAGScheduler.$anonfun$abortStage$2(DAGScheduler.scala:3308) at org.apache.spark.scheduler.DAGScheduler.$anonfun$abortStage$2$adapted(DAGScheduler.scala:3299) at scala.collection.mutable.ResizableArray.foreach(ResizableArray.scala:62) at scala.collection.mutable.ResizableArray.foreach$(ResizableArray.scala:55) at scala.collection.mutable.ArrayBuffer.foreach(ArrayBuffer.scala:49) at org.apache.spark.scheduler.DAGScheduler.abortStage(DAGScheduler.scala:3299) at org.apache.spark.scheduler.DAGScheduler.$anonfun$handleTaskSetFailed$1(DAGScheduler.scala:1428) at org.apache.spark.scheduler.DAGScheduler.$anonfun$handleTaskSetFailed$1$adapted(DAGScheduler.scala:1428) at scala.Option.foreach(Option.scala:407) at org.apache.spark.scheduler.DAGScheduler.handleTaskSetFailed(DAGScheduler.scala:1428) at org.apache.spark.scheduler.DAGSchedulerEventProcessLoop.doOnReceive(DAGScheduler.scala:3588) at org.apache.spark.scheduler.DAGSchedulerEventProcessLoop.onReceive(DAGScheduler.scala:3526) at org.apache.spark.scheduler.DAGSchedulerEventProcessLoop.onReceive(DAGScheduler.scala:3514) at org.apache.spark.util.EventLoop$$anon$1.run(EventLoop.scala:51) at org.apache.spark.scheduler.DAGScheduler.$anonfun$runJob$1(DAGScheduler.scala:1177) at scala.runtime.java8.JFunction0$mcV$sp.apply(JFunction0$mcV$sp.java:23) at com.databricks.spark.util.FrameProfiler$.record(FrameProfiler.scala:80) at org.apache.spark.scheduler.DAGScheduler.runJob(DAGScheduler.scala:1165) at org.apache.spark.SparkContext.runJobInternal(SparkContext.scala:2733) at org.apache.spark.SparkContext.runJob(SparkContext.scala:2716) at org.apache.spark.sql.execution.datasources.FileFormatWriter$.$anonfun$write$1(FileFormatWriter.scala:299) ... 135 more Caused by: org.apache.spark.SparkNumberFormatException: [CAST_INVALID_INPUT] The value 'Monthly' of the type "STRING" cannot be cast to "INT" because it is malformed. Correct the value as per the syntax, or change its target type. Use `try_cast` to tolerate malformed input and return NULL instead. If necessary set "spark.sql.ansi.enabled" to "false" to bypass this error. == SQL(line 5, position 1) == INSERT OVERWRITE TABLE demo_cdm_ach_res.heracles_periods SELECT * FROM insertion_temp ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^ at org.apache.spark.sql.errors.QueryExecutionErrors$.invalidInputInCastToNumberError(QueryExecutionErrors.scala:256) at org.apache.spark.sql.catalyst.util.UTF8StringUtils$.withException(UTF8StringUtils.scala:51) at org.apache.spark.sql.catalyst.util.UTF8StringUtils$.toIntExact(UTF8StringUtils.scala:34) at org.apache.spark.sql.catalyst.util.UTF8StringUtils.toIntExact(UTF8StringUtils.scala) at org.apache.spark.sql.catalyst.expressions.GeneratedClass$GeneratedIteratorForCodegenStage2.runningwindowfunction_doConsume_0$(Unknown Source) at org.apache.spark.sql.catalyst.expressions.GeneratedClass$GeneratedIteratorForCodegenStage2.processNext(Unknown Source) at org.apache.spark.sql.execution.BufferedRowIterator.hasNext(BufferedRowIterator.java:43) at org.apache.spark.sql.execution.WholeStageCodegenExec$$anon$1.hasNext(WholeStageCodegenExec.scala:761) at org.apache.spark.sql.execution.datasources.FileFormatWriter$.executeTask(FileFormatWriter.scala:384) at org.apache.spark.sql.execution.datasources.FileFormatWriter$.$anonfun$write$12(FileFormatWriter.scala:310) at org.apache.spark.scheduler.ResultTask.$anonfun$runTask$3(ResultTask.scala:75) at com.databricks.spark.util.ExecutorFrameProfiler$.record(ExecutorFrameProfiler.scala:110) at org.apache.spark.scheduler.ResultTask.$anonfun$runTask$1(ResultTask.scala:75) at com.databricks.spark.util.ExecutorFrameProfiler$.record(ExecutorFrameProfiler.scala:110) at org.apache.spark.scheduler.ResultTask.runTask(ResultTask.scala:55) at org.apache.spark.scheduler.Task.doRunTask(Task.scala:169) at org.apache.spark.scheduler.Task.$anonfun$run$4(Task.scala:137) at com.databricks.unity.EmptyHandle$.runWithAndClose(UCSHandle.scala:125) at org.apache.spark.scheduler.Task.$anonfun$run$1(Task.scala:137) at com.databricks.spark.util.ExecutorFrameProfiler$.record(ExecutorFrameProfiler.scala:110) at org.apache.spark.scheduler.Task.run(Task.scala:96) at org.apache.spark.executor.Executor$TaskRunner.$anonfun$run$13(Executor.scala:902) at org.apache.spark.util.Utils$.tryWithSafeFinally(Utils.scala:1697) at org.apache.spark.executor.Executor$TaskRunner.$anonfun$run$4(Executor.scala:905) at scala.runtime.java8.JFunction0$mcV$sp.apply(JFunction0$mcV$sp.java:23) at com.databricks.spark.util.ExecutorFrameProfiler$.record(ExecutorFrameProfiler.scala:110) at org.apache.spark.executor.Executor$TaskRunner.run(Executor.scala:760) ... 3 more , Query: WITH insertion_temp AS ( (SELECT CAST(row_number() over (order by period_order, period_start_date) AS INT) as period_id , period_name, period_order, period_type, period_start_date, period_end_date from demo_cdm_ach_tmp.f6rqimkutemp_period) UNION ALL (SELECT period_id, period_name, period_order, period_type, period_start_date, period_end_date FROM demo_cdm_ach_res.heracles_periods )) INSERT OVERWRITE TABLE demo_cdm_ach_res.heracles_periods SELECT * FROM insertion_temp. at com.simba.spark.hivecommon.api.HS2Client.pollForOperationCompletion(Unknown Source) at com.simba.spark.hivecommon.api.HS2Client.executeStatementInternal(Unknown Source) at com.simba.spark.hivecommon.api.HS2Client.executeStatement(Unknown Source) at com.simba.spark.hivecommon.dataengine.HiveJDBCNativeQueryExecutor.executeQuery(Unknown Source) at com.simba.spark.hivecommon.dataengine.HiveJDBCNativeQueryExecutor.(Unknown Source) at com.simba.spark.hivecommon.dataengine.HiveJDBCDataEngine.prepare(Unknown Source) at com.simba.spark.jdbc.common.SStatement.executeNoParams(Unknown Source) at com.simba.spark.jdbc.common.SStatement.execute(Unknown Source) at org.apache.ibatis.jdbc.ScriptRunner.executeStatement(ScriptRunner.java:190) at org.apache.ibatis.jdbc.ScriptRunner.handleLine(ScriptRunner.java:165) at org.apache.ibatis.jdbc.ScriptRunner.executeLineByLine(ScriptRunner.java:102) at org.apache.ibatis.jdbc.ScriptRunner.runScript(ScriptRunner.java:71) at org.yaorma.database.Database.executeSqlScript(Database.java:342) at org.yaorma.database.Database.executeSqlScript(Database.java:332) at org.nachc.tools.fhirtoomop.tools.databricks.build.A05_CreateAchillesDatabaseObjectsDatabricks.exec(A05_CreateAchillesDatabaseObjectsDatabricks.java:66) Caused by: com.simba.spark.support.exceptions.GeneralException: [Simba][SparkJDBCDriver](500051) ERROR processing query/statement. Error Code: 0, SQL state: org.apache.hive.service.cli.HiveSQLException: Error running query: org.apache.spark.SparkException: Job aborted. at org.apache.spark.sql.hive.thriftserver.HiveThriftServerErrors$.runningQueryError(HiveThriftServerErrors.scala:48) at org.apache.spark.sql.hive.thriftserver.SparkExecuteStatementOperation.$anonfun$execute$1(SparkExecuteStatementOperation.scala:498) at scala.runtime.java8.JFunction0$mcV$sp.apply(JFunction0$mcV$sp.java:23) at com.databricks.unity.EmptyHandle$.runWith(UCSHandle.scala:124) at org.apache.spark.sql.hive.thriftserver.SparkExecuteStatementOperation.org$apache$spark$sql$hive$thriftserver$SparkExecuteStatementOperation$$execute(SparkExecuteStatementOperation.scala:410) at org.apache.spark.sql.hive.thriftserver.SparkExecuteStatementOperation$$anon$2$$anon$3.$anonfun$run$2(SparkExecuteStatementOperation.scala:321) at scala.runtime.java8.JFunction0$mcV$sp.apply(JFunction0$mcV$sp.java:23) at org.apache.spark.sql.hive.thriftserver.ThriftLocalProperties.withLocalProperties(ThriftLocalProperties.scala:149) at org.apache.spark.sql.hive.thriftserver.ThriftLocalProperties.withLocalProperties$(ThriftLocalProperties.scala:49) at org.apache.spark.sql.hive.thriftserver.SparkExecuteStatementOperation.withLocalProperties(SparkExecuteStatementOperation.scala:54) at org.apache.spark.sql.hive.thriftserver.SparkExecuteStatementOperation$$anon$2$$anon$3.run(SparkExecuteStatementOperation.scala:299) at org.apache.spark.sql.hive.thriftserver.SparkExecuteStatementOperation$$anon$2$$anon$3.run(SparkExecuteStatementOperation.scala:284) at java.security.AccessController.doPrivileged(Native Method) at javax.security.auth.Subject.doAs(Subject.java:422) at org.apache.hadoop.security.UserGroupInformation.doAs(UserGroupInformation.java:1878) at org.apache.spark.sql.hive.thriftserver.SparkExecuteStatementOperation$$anon$2.run(SparkExecuteStatementOperation.scala:333) at java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:511) at java.util.concurrent.FutureTask.run(FutureTask.java:266) at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149) at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624) at java.lang.Thread.run(Thread.java:750) Caused by: org.apache.spark.SparkException: Job aborted. at org.apache.spark.sql.errors.QueryExecutionErrors$.jobAbortedError(QueryExecutionErrors.scala:882) at org.apache.spark.sql.execution.datasources.FileFormatWriter$.$anonfun$write$1(FileFormatWriter.scala:334) at com.databricks.spark.util.FrameProfiler$.record(FrameProfiler.scala:80) at org.apache.spark.sql.execution.datasources.FileFormatWriter$.write(FileFormatWriter.scala:154) at com.databricks.sql.transaction.tahoe.commands.WriteIntoDeltaCommand.run(WriteIntoDeltaCommand.scala:70) at com.databricks.sql.transaction.tahoe.files.TransactionalWriteEdge.$anonfun$writeFiles$11(TransactionalWriteEdge.scala:564) at org.apache.spark.sql.execution.SQLExecution$.$anonfun$withCustomExecutionEnv$8(SQLExecution.scala:243) at org.apache.spark.sql.execution.SQLExecution$.withSQLConfPropagated(SQLExecution.scala:392) at org.apache.spark.sql.execution.SQLExecution$.$anonfun$withCustomExecutionEnv$1(SQLExecution.scala:188) at org.apache.spark.sql.SparkSession.withActive(SparkSession.scala:985) at org.apache.spark.sql.execution.SQLExecution$.withCustomExecutionEnv(SQLExecution.scala:142) at org.apache.spark.sql.execution.SQLExecution$.withNewExecutionId(SQLExecution.scala:342) at com.databricks.sql.transaction.tahoe.files.TransactionalWriteEdge.$anonfun$writeFiles$1(TransactionalWriteEdge.scala:564) at com.databricks.sql.transaction.tahoe.metering.DeltaLogging.withOperationTypeTag(DeltaLogging.scala:193) at com.databricks.sql.transaction.tahoe.metering.DeltaLogging.withOperationTypeTag$(DeltaLogging.scala:180) at com.databricks.sql.transaction.tahoe.OptimisticTransaction.withOperationTypeTag(OptimisticTransaction.scala:112) at com.databricks.sql.transaction.tahoe.metering.DeltaLogging.$anonfun$recordDeltaOperationInternal$2(DeltaLogging.scala:157) at com.databricks.spark.util.FrameProfiler$.record(FrameProfiler.scala:80) at com.databricks.sql.transaction.tahoe.metering.DeltaLogging.recordFrameProfile(DeltaLogging.scala:262) at com.databricks.sql.transaction.tahoe.metering.DeltaLogging.recordFrameProfile$(DeltaLogging.scala:260) at com.databricks.sql.transaction.tahoe.OptimisticTransaction.recordFrameProfile(OptimisticTransaction.scala:112) at com.databricks.sql.transaction.tahoe.metering.DeltaLogging.$anonfun$recordDeltaOperationInternal$1(DeltaLogging.scala:156) at com.databricks.logging.UsageLogging.$anonfun$recordOperation$1(UsageLogging.scala:541) at com.databricks.logging.UsageLogging.executeThunkAndCaptureResultTags$1(UsageLogging.scala:636) at com.databricks.logging.UsageLogging.$anonfun$recordOperationWithResultTags$4(UsageLogging.scala:657) at com.databricks.logging.UsageLogging.$anonfun$withAttributionContext$1(UsageLogging.scala:398) at scala.util.DynamicVariable.withValue(DynamicVariable.scala:62) at com.databricks.logging.AttributionContext$.withValue(AttributionContext.scala:147) at com.databricks.logging.UsageLogging.withAttributionContext(UsageLogging.scala:396) at com.databricks.logging.UsageLogging.withAttributionContext$(UsageLogging.scala:393) at com.databricks.spark.util.PublicDBLogging.withAttributionContext(DatabricksSparkUsageLogger.scala:22) at com.databricks.logging.UsageLogging.withAttributionTags(UsageLogging.scala:441) at com.databricks.logging.UsageLogging.withAttributionTags$(UsageLogging.scala:426) at com.databricks.spark.util.PublicDBLogging.withAttributionTags(DatabricksSparkUsageLogger.scala:22) at com.databricks.logging.UsageLogging.recordOperationWithResultTags(UsageLogging.scala:631) at com.databricks.logging.UsageLogging.recordOperationWithResultTags$(UsageLogging.scala:550) at com.databricks.spark.util.PublicDBLogging.recordOperationWithResultTags(DatabricksSparkUsageLogger.scala:22) at com.databricks.logging.UsageLogging.recordOperation(UsageLogging.scala:541) at com.databricks.logging.UsageLogging.recordOperation$(UsageLogging.scala:511) at com.databricks.spark.util.PublicDBLogging.recordOperation(DatabricksSparkUsageLogger.scala:22) at com.databricks.spark.util.PublicDBLogging.recordOperation0(DatabricksSparkUsageLogger.scala:62) at com.databricks.spark.util.DatabricksSparkUsageLogger.recordOperation(DatabricksSparkUsageLogger.scala:144) at com.databricks.spark.util.UsageLogger.recordOperation(UsageLogger.scala:72) at com.databricks.spark.util.UsageLogger.recordOperation$(UsageLogger.scala:59) at com.databricks.spark.util.DatabricksSparkUsageLogger.recordOperation(DatabricksSparkUsageLogger.scala:103) at com.databricks.spark.util.UsageLogging.recordOperation(UsageLogger.scala:433) at com.databricks.spark.util.UsageLogging.recordOperation$(UsageLogger.scala:412) at com.databricks.sql.transaction.tahoe.OptimisticTransaction.recordOperation(OptimisticTransaction.scala:112) at com.databricks.sql.transaction.tahoe.metering.DeltaLogging.recordDeltaOperationInternal(DeltaLogging.scala:155) at com.databricks.sql.transaction.tahoe.metering.DeltaLogging.recordDeltaOperation(DeltaLogging.scala:145) at com.databricks.sql.transaction.tahoe.metering.DeltaLogging.recordDeltaOperation$(DeltaLogging.scala:133) at com.databricks.sql.transaction.tahoe.OptimisticTransaction.recordDeltaOperation(OptimisticTransaction.scala:112) at com.databricks.sql.transaction.tahoe.files.TransactionalWriteEdge.$anonfun$recordWriteFilesOperation$1(TransactionalWriteEdge.scala:305) at com.databricks.sql.acl.CheckPermissions$.trusted(CheckPermissions.scala:1816) at com.databricks.sql.transaction.tahoe.files.TransactionalWriteEdge.recordWriteFilesOperation(TransactionalWriteEdge.scala:304) at com.databricks.sql.transaction.tahoe.files.TransactionalWriteEdge.writeFiles(TransactionalWriteEdge.scala:337) at com.databricks.sql.transaction.tahoe.files.TransactionalWriteEdge.writeFiles$(TransactionalWriteEdge.scala:331) at com.databricks.sql.transaction.tahoe.OptimisticTransaction.writeFiles(OptimisticTransaction.scala:112) at com.databricks.sql.transaction.tahoe.files.TransactionalWriteEdge.writeFiles(TransactionalWriteEdge.scala:604) at com.databricks.sql.transaction.tahoe.files.TransactionalWriteEdge.writeFiles$(TransactionalWriteEdge.scala:594) at com.databricks.sql.transaction.tahoe.OptimisticTransaction.writeFiles(OptimisticTransaction.scala:112) at com.databricks.sql.transaction.tahoe.files.TransactionalWrite.writeFiles(TransactionalWrite.scala:222) at com.databricks.sql.transaction.tahoe.files.TransactionalWrite.writeFiles$(TransactionalWrite.scala:219) at com.databricks.sql.transaction.tahoe.OptimisticTransaction.writeFiles(OptimisticTransaction.scala:112) at com.databricks.sql.transaction.tahoe.commands.WriteIntoDelta.write(WriteIntoDelta.scala:366) at com.databricks.sql.transaction.tahoe.commands.WriteIntoDelta.$anonfun$run$2(WriteIntoDelta.scala:110) at com.databricks.sql.transaction.tahoe.commands.WriteIntoDelta.$anonfun$run$2$adapted(WriteIntoDelta.scala:99) at com.databricks.sql.transaction.tahoe.DeltaLog.withNewTransaction(DeltaLog.scala:306) at com.databricks.sql.transaction.tahoe.commands.WriteIntoDelta.$anonfun$run$1(WriteIntoDelta.scala:99) at com.databricks.sql.acl.CheckPermissions$.trusted(CheckPermissions.scala:1816) at com.databricks.sql.transaction.tahoe.commands.WriteIntoDelta.run(WriteIntoDelta.scala:98) at com.databricks.sql.transaction.tahoe.catalog.WriteIntoDeltaBuilder$$anon$1$$anon$2.insert(DeltaTableV2.scala:342) at org.apache.spark.sql.execution.datasources.v2.SupportsV1Write.writeWithV1(V1FallbackWriters.scala:91) at org.apache.spark.sql.execution.datasources.v2.SupportsV1Write.writeWithV1$(V1FallbackWriters.scala:89) at org.apache.spark.sql.execution.datasources.v2.OverwriteByExpressionExecV1.writeWithV1(V1FallbackWriters.scala:53) at org.apache.spark.sql.execution.datasources.v2.V1FallbackWriters.run(V1FallbackWriters.scala:77) at org.apache.spark.sql.execution.datasources.v2.V1FallbackWriters.run$(V1FallbackWriters.scala:76) at org.apache.spark.sql.execution.datasources.v2.OverwriteByExpressionExecV1.run(V1FallbackWriters.scala:53) at org.apache.spark.sql.execution.datasources.v2.V2CommandExec.result$lzycompute(V2CommandExec.scala:43) at org.apache.spark.sql.execution.datasources.v2.V2CommandExec.result(V2CommandExec.scala:43) at org.apache.spark.sql.execution.datasources.v2.V2CommandExec.executeCollect(V2CommandExec.scala:49) at org.apache.spark.sql.execution.QueryExecution$$anonfun$$nestedInanonfun$eagerlyExecuteCommands$1$1.$anonfun$applyOrElse$2(QueryExecution.scala:229) at org.apache.spark.sql.execution.SQLExecution$.$anonfun$withCustomExecutionEnv$8(SQLExecution.scala:243) at org.apache.spark.sql.execution.SQLExecution$.withSQLConfPropagated(SQLExecution.scala:392) at org.apache.spark.sql.execution.SQLExecution$.$anonfun$withCustomExecutionEnv$1(SQLExecution.scala:188) at org.apache.spark.sql.SparkSession.withActive(SparkSession.scala:985) at org.apache.spark.sql.execution.SQLExecution$.withCustomExecutionEnv(SQLExecution.scala:142) at org.apache.spark.sql.execution.SQLExecution$.withNewExecutionId(SQLExecution.scala:342) at org.apache.spark.sql.execution.QueryExecution$$anonfun$$nestedInanonfun$eagerlyExecuteCommands$1$1.$anonfun$applyOrElse$1(QueryExecution.scala:229) at org.apache.spark.sql.execution.QueryExecution.org$apache$spark$sql$execution$QueryExecution$$withMVTagsIfNecessary(QueryExecution.scala:214) at org.apache.spark.sql.execution.QueryExecution$$anonfun$$nestedInanonfun$eagerlyExecuteCommands$1$1.applyOrElse(QueryExecution.scala:227) at org.apache.spark.sql.execution.QueryExecution$$anonfun$$nestedInanonfun$eagerlyExecuteCommands$1$1.applyOrElse(QueryExecution.scala:220) at org.apache.spark.sql.catalyst.trees.TreeNode.$anonfun$transformDownWithPruning$1(TreeNode.scala:512) at org.apache.spark.sql.catalyst.trees.CurrentOrigin$.withOrigin(TreeNode.scala:99) at org.apache.spark.sql.catalyst.trees.TreeNode.transformDownWithPruning(TreeNode.scala:512) at org.apache.spark.sql.catalyst.plans.logical.LogicalPlan.org$apache$spark$sql$catalyst$plans$logical$AnalysisHelper$$super$transformDownWithPruning(LogicalPlan.scala:31) at org.apache.spark.sql.catalyst.plans.logical.AnalysisHelper.transformDownWithPruning(AnalysisHelper.scala:298) at org.apache.spark.sql.catalyst.plans.logical.AnalysisHelper.transformDownWithPruning$(AnalysisHelper.scala:294) at org.apache.spark.sql.catalyst.plans.logical.LogicalPlan.transformDownWithPruning(LogicalPlan.scala:31) at org.apache.spark.sql.catalyst.plans.logical.LogicalPlan.transformDownWithPruning(LogicalPlan.scala:31) at org.apache.spark.sql.catalyst.trees.TreeNode.transformDown(TreeNode.scala:488) at org.apache.spark.sql.execution.QueryExecution.$anonfun$eagerlyExecuteCommands$1(QueryExecution.scala:220) at org.apache.spark.sql.catalyst.plans.logical.AnalysisHelper$.allowInvokingTransformsInAnalyzer(AnalysisHelper.scala:354) at org.apache.spark.sql.execution.QueryExecution.eagerlyExecuteCommands(QueryExecution.scala:220) at org.apache.spark.sql.execution.QueryExecution.commandExecuted$lzycompute(QueryExecution.scala:174) at org.apache.spark.sql.execution.QueryExecution.commandExecuted(QueryExecution.scala:165) at org.apache.spark.sql.Dataset.(Dataset.scala:238) at org.apache.spark.sql.hive.thriftserver.SparkExecuteStatementOperation.$anonfun$analyzeQuery$2(SparkExecuteStatementOperation.scala:404) at org.apache.spark.sql.SparkSession.withActive(SparkSession.scala:985) at org.apache.spark.sql.hive.thriftserver.SparkExecuteStatementOperation.$anonfun$analyzeQuery$1(SparkExecuteStatementOperation.scala:397) at org.apache.spark.sql.hive.thriftserver.SparkExecuteStatementOperation.getOrCreateDF(SparkExecuteStatementOperation.scala:387) at org.apache.spark.sql.hive.thriftserver.SparkExecuteStatementOperation.analyzeQuery(SparkExecuteStatementOperation.scala:397) at org.apache.spark.sql.hive.thriftserver.SparkExecuteStatementOperation.$anonfun$execute$5(SparkExecuteStatementOperation.scala:446) at org.apache.spark.util.Utils$.timeTakenMs(Utils.scala:692) at org.apache.spark.sql.execution.qrc.CacheEventLogger.recordLatency(QueryResultCache.scala:149) at org.apache.spark.sql.execution.qrc.CacheEventLogger.recordLatency$(QueryResultCache.scala:145) at org.apache.spark.sql.hive.thriftserver.SparkExecuteStatementOperation.recordLatency(SparkExecuteStatementOperation.scala:54) at org.apache.spark.sql.hive.thriftserver.SparkExecuteStatementOperation.$anonfun$execute$1(SparkExecuteStatementOperation.scala:446) ... 19 more Caused by: org.apache.spark.SparkException: Job aborted due to stage failure: Task 0 in stage 92321.0 failed 4 times, most recent failure: Lost task 0.3 in stage 92321.0 (TID 140794) (10.22.225.217 executor 262): org.apache.spark.SparkNumberFormatException: [CAST_INVALID_INPUT] The value 'Monthly' of the type "STRING" cannot be cast to "INT" because it is malformed. Correct the value as per the syntax, or change its target type. Use `try_cast` to tolerate malformed input and return NULL instead. If necessary set "spark.sql.ansi.enabled" to "false" to bypass this error. == SQL(line 5, position 1) == INSERT OVERWRITE TABLE demo_cdm_ach_res.heracles_periods SELECT * FROM insertion_temp ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^ at org.apache.spark.sql.errors.QueryExecutionErrors$.invalidInputInCastToNumberError(QueryExecutionErrors.scala:256) at org.apache.spark.sql.catalyst.util.UTF8StringUtils$.withException(UTF8StringUtils.scala:51) at org.apache.spark.sql.catalyst.util.UTF8StringUtils$.toIntExact(UTF8StringUtils.scala:34) at org.apache.spark.sql.catalyst.util.UTF8StringUtils.toIntExact(UTF8StringUtils.scala) at org.apache.spark.sql.catalyst.expressions.GeneratedClass$GeneratedIteratorForCodegenStage2.runningwindowfunction_doConsume_0$(Unknown Source) at org.apache.spark.sql.catalyst.expressions.GeneratedClass$GeneratedIteratorForCodegenStage2.processNext(Unknown Source) at org.apache.spark.sql.execution.BufferedRowIterator.hasNext(BufferedRowIterator.java:43) at org.apache.spark.sql.execution.WholeStageCodegenExec$$anon$1.hasNext(WholeStageCodegenExec.scala:761) at org.apache.spark.sql.execution.datasources.FileFormatWriter$.executeTask(FileFormatWriter.scala:384) at org.apache.spark.sql.execution.datasources.FileFormatWriter$.$anonfun$write$12(FileFormatWriter.scala:310) at org.apache.spark.scheduler.ResultTask.$anonfun$runTask$3(ResultTask.scala:75) at com.databricks.spark.util.ExecutorFrameProfiler$.record(ExecutorFrameProfiler.scala:110) at org.apache.spark.scheduler.ResultTask.$anonfun$runTask$1(ResultTask.scala:75) at com.databricks.spark.util.ExecutorFrameProfiler$.record(ExecutorFrameProfiler.scala:110) at org.apache.spark.scheduler.ResultTask.runTask(ResultTask.scala:55) at org.apache.spark.scheduler.Task.doRunTask(Task.scala:169) at org.apache.spark.scheduler.Task.$anonfun$run$4(Task.scala:137) at com.databricks.unity.EmptyHandle$.runWithAndClose(UCSHandle.scala:125) at org.apache.spark.scheduler.Task.$anonfun$run$1(Task.scala:137) at com.databricks.spark.util.ExecutorFrameProfiler$.record(ExecutorFrameProfiler.scala:110) at org.apache.spark.scheduler.Task.run(Task.scala:96) at org.apache.spark.executor.Executor$TaskRunner.$anonfun$run$13(Executor.scala:902) at org.apache.spark.util.Utils$.tryWithSafeFinally(Utils.scala:1697) at org.apache.spark.executor.Executor$TaskRunner.$anonfun$run$4(Executor.scala:905) at scala.runtime.java8.JFunction0$mcV$sp.apply(JFunction0$mcV$sp.java:23) at com.databricks.spark.util.ExecutorFrameProfiler$.record(ExecutorFrameProfiler.scala:110) at org.apache.spark.executor.Executor$TaskRunner.run(Executor.scala:760) at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149) at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624) at java.lang.Thread.run(Thread.java:750) Driver stacktrace: at org.apache.spark.scheduler.DAGScheduler.failJobAndIndependentStages(DAGScheduler.scala:3376) at org.apache.spark.scheduler.DAGScheduler.$anonfun$abortStage$2(DAGScheduler.scala:3308) at org.apache.spark.scheduler.DAGScheduler.$anonfun$abortStage$2$adapted(DAGScheduler.scala:3299) at scala.collection.mutable.ResizableArray.foreach(ResizableArray.scala:62) at scala.collection.mutable.ResizableArray.foreach$(ResizableArray.scala:55) at scala.collection.mutable.ArrayBuffer.foreach(ArrayBuffer.scala:49) at org.apache.spark.scheduler.DAGScheduler.abortStage(DAGScheduler.scala:3299) at org.apache.spark.scheduler.DAGScheduler.$anonfun$handleTaskSetFailed$1(DAGScheduler.scala:1428) at org.apache.spark.scheduler.DAGScheduler.$anonfun$handleTaskSetFailed$1$adapted(DAGScheduler.scala:1428) at scala.Option.foreach(Option.scala:407) at org.apache.spark.scheduler.DAGScheduler.handleTaskSetFailed(DAGScheduler.scala:1428) at org.apache.spark.scheduler.DAGSchedulerEventProcessLoop.doOnReceive(DAGScheduler.scala:3588) at org.apache.spark.scheduler.DAGSchedulerEventProcessLoop.onReceive(DAGScheduler.scala:3526) at org.apache.spark.scheduler.DAGSchedulerEventProcessLoop.onReceive(DAGScheduler.scala:3514) at org.apache.spark.util.EventLoop$$anon$1.run(EventLoop.scala:51) at org.apache.spark.scheduler.DAGScheduler.$anonfun$runJob$1(DAGScheduler.scala:1177) at scala.runtime.java8.JFunction0$mcV$sp.apply(JFunction0$mcV$sp.java:23) at com.databricks.spark.util.FrameProfiler$.record(FrameProfiler.scala:80) at org.apache.spark.scheduler.DAGScheduler.runJob(DAGScheduler.scala:1165) at org.apache.spark.SparkContext.runJobInternal(SparkContext.scala:2733) at org.apache.spark.SparkContext.runJob(SparkContext.scala:2716) at org.apache.spark.sql.execution.datasources.FileFormatWriter$.$anonfun$write$1(FileFormatWriter.scala:299) ... 135 more Caused by: org.apache.spark.SparkNumberFormatException: [CAST_INVALID_INPUT] The value 'Monthly' of the type "STRING" cannot be cast to "INT" because it is malformed. Correct the value as per the syntax, or change its target type. Use `try_cast` to tolerate malformed input and return NULL instead. If necessary set "spark.sql.ansi.enabled" to "false" to bypass this error. == SQL(line 5, position 1) == INSERT OVERWRITE TABLE demo_cdm_ach_res.heracles_periods SELECT * FROM insertion_temp ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^ at org.apache.spark.sql.errors.QueryExecutionErrors$.invalidInputInCastToNumberError(QueryExecutionErrors.scala:256) at org.apache.spark.sql.catalyst.util.UTF8StringUtils$.withException(UTF8StringUtils.scala:51) at org.apache.spark.sql.catalyst.util.UTF8StringUtils$.toIntExact(UTF8StringUtils.scala:34) at org.apache.spark.sql.catalyst.util.UTF8StringUtils.toIntExact(UTF8StringUtils.scala) at org.apache.spark.sql.catalyst.expressions.GeneratedClass$GeneratedIteratorForCodegenStage2.runningwindowfunction_doConsume_0$(Unknown Source) at org.apache.spark.sql.catalyst.expressions.GeneratedClass$GeneratedIteratorForCodegenStage2.processNext(Unknown Source) at org.apache.spark.sql.execution.BufferedRowIterator.hasNext(BufferedRowIterator.java:43) at org.apache.spark.sql.execution.WholeStageCodegenExec$$anon$1.hasNext(WholeStageCodegenExec.scala:761) at org.apache.spark.sql.execution.datasources.FileFormatWriter$.executeTask(FileFormatWriter.scala:384) at org.apache.spark.sql.execution.datasources.FileFormatWriter$.$anonfun$write$12(FileFormatWriter.scala:310) at org.apache.spark.scheduler.ResultTask.$anonfun$runTask$3(ResultTask.scala:75) at com.databricks.spark.util.ExecutorFrameProfiler$.record(ExecutorFrameProfiler.scala:110) at org.apache.spark.scheduler.ResultTask.$anonfun$runTask$1(ResultTask.scala:75) at com.databricks.spark.util.ExecutorFrameProfiler$.record(ExecutorFrameProfiler.scala:110) at org.apache.spark.scheduler.ResultTask.runTask(ResultTask.scala:55) at org.apache.spark.scheduler.Task.doRunTask(Task.scala:169) at org.apache.spark.scheduler.Task.$anonfun$run$4(Task.scala:137) at com.databricks.unity.EmptyHandle$.runWithAndClose(UCSHandle.scala:125) at org.apache.spark.scheduler.Task.$anonfun$run$1(Task.scala:137) at com.databricks.spark.util.ExecutorFrameProfiler$.record(ExecutorFrameProfiler.scala:110) at org.apache.spark.scheduler.Task.run(Task.scala:96) at org.apache.spark.executor.Executor$TaskRunner.$anonfun$run$13(Executor.scala:902) at org.apache.spark.util.Utils$.tryWithSafeFinally(Utils.scala:1697) at org.apache.spark.executor.Executor$TaskRunner.$anonfun$run$4(Executor.scala:905) at scala.runtime.java8.JFunction0$mcV$sp.apply(JFunction0$mcV$sp.java:23) at com.databricks.spark.util.ExecutorFrameProfiler$.record(ExecutorFrameProfiler.scala:110) at org.apache.spark.executor.Executor$TaskRunner.run(Executor.scala:760) ... 3 more , Query: WITH insertion_temp AS ( (SELECT CAST(row_number() over (order by period_order, period_start_date) AS INT) as period_id , period_name, period_order, period_type, period_start_date, period_end_date from demo_cdm_ach_tmp.f6rqimkutemp_period) UNION ALL (SELECT period_id, period_name, period_order, period_type, period_start_date, period_end_date FROM demo_cdm_ach_res.heracles_periods )) INSERT OVERWRITE TABLE demo_cdm_ach_res.heracles_periods SELECT * FROM insertion_temp. ... 15 more