Q (sonnet): Which studies have RNA expression for renal cancer?

▶ list_studies
{
 "search": "renal",
 "verbose": false,
 "limit": 40
}

◀ result
{"result":[{"cancer_study_identifier":"ccrcc_sjuh_2023","name":"Renal Cell Carcinoma (St. James, Clin Cancer Res 2023)","type_of_cancer_id":"ccrcc","sample_count":943,"has_guide":false,"url":"https://www.cbioportal.org/study/summary?id=ccrcc_sjuh_2023"},{"cancer_study_identifier":"kirc_tcga","name":"Kidney Renal Clear Cell Carcinoma (TCGA, Firehose Legacy)","type_of_cancer_id":"ccrcc","sample_count":538,"has_guide":false,"url":"https://www.cbioportal.org/study/summary?id=kirc_tcga"},{"cancer_study_identifier":"ccrcc_tcga_gdc","name":"Renal Clear Cell Carcinoma (TCGA GDC, 2025)","type_of_cancer_id":"ccrcc","sample_count":537,"has_guide":false,"url":"https://www.cbioportal.org/study/summary?id=ccrcc_tcga_gdc"},{"cancer_study_identifier":"kirc_tcga_pan_can_atlas_2018","name":"Kidney Renal Clear Cell Carcinoma (TCGA, PanCancer Atlas)","type_of_cancer_id":"ccrcc","sample_count":512,"has_guide":false,"url":"https://www.cbioportal.org/study/summary?id=kirc_tcga_pan_can_atlas_2018"},{"cancer_study_identifier":"kirc_tcga_pub","name":"Kidney Renal Clear Cell Carcinoma (TCGA, Nature 2013)","type_of_cancer_id":"ccrcc","sample_count":446,"has_guide":false,"url":"https://www.cbioportal.org/study/summary?id=kirc_tcga_pub"},{"cancer_study_identifier":"rcc_cptac_gdc","name":"Renal Cell Carcinoma (CPTAC GDC, 2025)","type_of_cancer_id":"rcc","sample_count":354,"has_guide":false,"url":"https://www.cbioportal.org/study/summary?id=rcc_cptac_gdc"},{"cancer_study_identifier":"kirp_tcga","name":"Kidney Renal Papillary Cell Carcinoma (TCGA, Firehose Legacy)","type_of_cancer_id":"prcc","sample_count":293,"has_guide":false,"url":"https://www.cbioportal.org/study/summary?id=kirp_tcga"},{"cancer_study_identifier":"prcc_tcga_gdc","name":"Papillary Renal Cell Carcinoma (TCGA GDC, 2025)","type_of_cancer_id":"prcc","sample_count":292,"has_guide":false,"url":"https://www.cbioportal.org/study/summary?id=prcc_tcga_gdc"},{"cancer_study_identifier":"kirp_tcga_pan_can_atlas_2018","name":"Kidney Renal Papillary Cell Carcinoma (TCGA, PanCancer Atlas)","type_of_cancer_id":"prcc","sample_count":283,"has_guide":false,"url":"https://www.cbioportal.org/study/summary?id=kirp_tcga_pan_can_atlas_2018"},{"cancer_study_identifier":"rcc_iatlas_immotion150_2018","name":"Renal Cell Carcinoma (IMmotion150 Clinical Trial, Nat Med. 2018) - iAtlas Harmonized","type_of_cancer_id":"rcc","sample_count":263,"has_guide":false,"url":"https://www.cbioportal.org/study/summary?id=rcc_iatlas_immotion150_2018"},{"cancer_study_identifier":"nccrcc_genentech_2014","name":"Renal Non-Clear Cell Carcinoma (Genentech, Nat Genet 2014)","type_of_cancer_id":"nccrcc","sample_count":146,"has_guide":false,"url":"https://www.cbioportal.org/study/summary?id=nccrcc_genentech_2014"},{"cancer_study_identifier":"ccrcc_utokyo_2013","name":"Renal Clear Cell Carcinoma (UTokyo, Nat Genet 2013)","type_of_cancer_id":"ccrcc","sample_count":106,"has_guide":false,"url":"https://www.cbioportal.org/study/summary?id=ccrcc_utokyo_2013"},{"cancer_ … (1719 more chars)

▶ clickhouse_list_tables
{}

◀ result
{"tables":[{"name":"allele_specific_copy_number"},{"name":"alteration_driver_annotation"},{"name":"cancer_study"},{"name":"cancer_study_query_preferences"},{"name":"cancer_study_tags"},{"name":"cbioportal_sequence_state"},{"name":"clinical_attribute_counts"},{"name":"clinical_attribute_meta"},{"name":"clinical_data_derived"},{"name":"clinical_event"},{"name":"clinical_event_data"},{"name":"clinical_event_data_derived"},{"name":"clinical_event_derived"},{"name":"clinical_patient"},{"name":"clinical_sample"},{"name":"cna_event"},{"name":"cna_panel_gene_coverage"},{"name":"cna_wes_coverage"},{"name":"co_altered_genes_in_study"},{"name":"copy_number_seg"},{"name":"copy_number_seg_file"},{"name":"gene"},{"name":"gene_alias"},{"name":"gene_alteration_frequency_by_cancer_type"},{"name":"gene_cna_distribution_in_study"},{"name":"gene_mutation_frequency_by_cancer_type"},{"name":"gene_mutation_frequency_in_studies"},{"name":"gene_mutation_frequency_in_study"},{"name":"gene_mutation_variants_in_study"},{"name":"gene_pair_coexpression"},{"name":"gene_panel"},{"name":"gene_panel_list"},{"name":"gene_panel_to_gene_derived"},{"name":"generic_assay_data_derived"},{"name":"generic_assay_meta_derived"},{"name":"generic_assay_profile_entity_derived"},{"name":"generic_entity_properties"},{"name":"geneset"},{"name":"geneset_gene"},{"name":"geneset_hierarchy_leaf"},{"name":"geneset_hierarchy_node"},{"name":"genetic_alteration"},{"name":"genetic_alteration_derived"},{"name":"genetic_entity"},{"name":"genetic_profile"},{"name":"genetic_profile_link"},{"name":"genetic_profile_samples"},{"name":"genomic_event_derived"},{"name":"gistic"},{"name":"gistic_to_gene"},{"name":"info"},{"name":"mut_sig"},{"name":"mutation"},{"name":"mutation_count_by_keyword"},{"name":"mutation_derived"},{"name":"mutation_event"},{"name":"mutation_panel_gene_coverage"},{"name":"mutation_wes_coverage"},{"name":"patient"},{"name":"reference_genome"},{"name":"reference_genome_gene"},{"name":"resource_definition"},{"name":"resource_patient"},{"name":"resource_sample"},{"name":"resource_study"},{"name":"sample"},{"name":"sample_cna_event"},{"name":"sample_derived"},{"name":"sample_list"},{"name":"sample_list_list"},{"name":"sample_profile"},{"name":"sample_to_gene_panel_derived"},{"name":"structural_variant"},{"name":"sv_panel_gene_coverage"},{"name":"sv_wes_coverage"},{"name":"top_cna_genes_in_study"},{"name":"top_mutated_genes_in_cohort"},{"name":"top_mutated_genes_in_study"},{"name":"top_sv_genes_in_study"},{"name":"treatment_counts_in_study"},{"name":"treatment_regimens_in_study"},{"name":"type_of_cancer"}]}

▶ clickhouse_run_select_query
{
 "query": "SELECT cancer_study_identifier, stable_id, name, genetic_alteration_type, datatype
FROM genetic_profile
WHERE cancer_study_identifier IN (
  'ccrcc_sjuh_2023','kirc_tcga','ccrcc_tcga_gdc','kirc_tcga_pan_can_atlas_2018','kirc_tcga_pub',
  'rcc_cptac_gdc','kirp_tcga','prcc_tcga_gdc','kirp_tcga_pan_can_atlas_2018','rcc_iatlas_immotion150_2018',
  'nccrcc_genentech_2014','ccrcc_utokyo_2013','kirc_bgi','ccrcc_irc_2014','chrcc_tcga_gdc','kich_tcga_pub',
  'urcc_mskcc_2016','ccrcc_dfci_2019','ccrcc_iatlas_choueiri_2016'
)
AND genetic_alteration_type = 'MRNA_EXPRESSION'
ORDER BY cancer_study_identifier",
 "max_rows": 100
}

◀ result
{"error_message":"Query execution failed: HTTPDriver for https://dl96orhu96.us-east-1.aws.clickhouse.cloud:8443 received ClickHouse error code 47\n Code: 47. DB::Exception: Unknown expression identifier `cancer_study_identifier` in scope SELECT cancer_study_identifier, stable_id, name, genetic_alteration_type, datatype FROM genetic_profile WHERE (cancer_study_identifier IN ('ccrcc_sjuh_2023', 'kirc_tcga', 'ccrcc_tcga_gdc', 'kirc_tcga_pan_can_atlas_2018', 'kirc_tcga_pub', 'rcc_cptac_gdc', 'kirp_tcga', 'prcc_tcga_gdc', 'kirp_tcga_pan_can_atlas_2018', 'rcc_iatlas_immotion150_2018', 'nccrcc_genentech_2014', 'ccrcc_utokyo_2013', 'kirc_bgi', 'ccrcc_irc_2014', 'chrcc_tcga_gdc', 'kich_tcga_pub', 'urcc_mskcc_2016', 'ccrcc_dfci_2019', 'ccrcc_iatlas_choueiri_2016')) AND (genetic_alteration_type = 'MRNA_EXPRESSION') ORDER BY cancer_study_identifier ASC. (UNKNOWN_IDENTIFIER) (version 26.4.1.2359 (official build))\n"}

▶ clickhouse_list_table_columns
{
 "table": "genetic_profile"
}

▶ clickhouse_list_table_columns
{
 "table": "cancer_study"
}

◀ result
{"columns":[{"name":"genetic_profile_id","type":"Int64"},{"name":"stable_id","type":"String"},{"name":"cancer_study_id","type":"Int64"},{"name":"genetic_alteration_type","type":"String"},{"name":"generic_assay_type","type":"Nullable(String)"},{"name":"datatype","type":"String"},{"name":"name","type":"String"},{"name":"description","type":"Nullable(String)"},{"name":"show_profile_in_analysis_tab","type":"Int32"},{"name":"pivot_threshold","type":"Nullable(Float64)"},{"name":"sort_order","type":"Nullable(String)"},{"name":"patient_level","type":"Nullable(Int32)"}]}

◀ result
{"columns":[{"name":"cancer_study_id","type":"Int64","comment":"Internal numeric ID. Prefer cancer_study_identifier for queries as it is more readable and stable."},{"name":"cancer_study_identifier","type":"Nullable(String)","comment":"Stable string identifier for the study (e.g., \"msk_chord_2024\", \"brca_tcga\"). Use this for filtering, not cancer_study_id."},{"name":"type_of_cancer_id","type":"String"},{"name":"name","type":"String","comment":"Full descriptive name of the study (e.g., \"MSK-CHORD (MSK, Nature 2024)\")."},{"name":"description","type":"String"},{"name":"public","type":"Int32"},{"name":"pmid","type":"Nullable(String)"},{"name":"citation","type":"Nullable(String)"},{"name":"groups","type":"Nullable(String)"},{"name":"status","type":"Nullable(Int64)"},{"name":"import_date","type":"Nullable(DateTime64(6))"},{"name":"reference_genome_id","type":"Nullable(Int64)"},{"name":"sample_count","type":"UInt32","comment":"Samples in the study (members of <study>_all), as shown in the portal study list. Precomputed daily at LLM-prep time."},{"name":"mutation_sample_count","type":"UInt32","comment":"Samples profiled for mutations (<study>_sequenced) — portal \"Data type\" filter: \"Mutations\". 0 = no mutation data."},{"name":"cna_sample_count","type":"UInt32","comment":"Samples profiled for copy-number alterations (<study>_cna) — \"CNA\". 0 = no CNA data."},{"name":"structural_variant_sample_count","type":"UInt32","comment":"Distinct samples with at least one structural variant (fusions etc.). 0 = none."},{"name":"rna_seq_sample_count","type":"UInt32","comment":"Samples with RNA-Seq expression (<study>_rna_seq_v2_mrna) — \"RNA-Seq\"."},{"name":"mrna_microarray_sample_count","type":"UInt32","comment":"Samples with microarray mRNA expression (<study>_mrna) — \"RNA (microarray)\"."},{"name":"mirna_sample_count","type":"UInt32","comment":"Samples with microRNA expression (<study>_microrna) — \"miRNA\"."},{"name":"rppa_sample_count","type":"UInt32","comment":"Samples with RPPA protein levels (<study>_rppa) — \"RPPA\"."},{"name":"mass_spectrometry_sample_count","type":"UInt32","comment":"Samples with mass-spectrometry protein quantification (<study>_protein_quantification) — \"Protein Mass-Spectrometry\"."},{"name":"treatment_patient_count","type":"UInt32","comment":"PATIENTS (not samples) with treatment clinical events — \"Treatment\". 0 = no treatment data."},{"name":"resource_sample_counts","type":"Map(String, UInt32)","comment":"Samples with each linked resource, keyed by display name: imaging and pathology such as 'Slide Microscopy', 'Computed Tomography', 'Magnetic Resonance', 'H&E Slide', 'MxIF Image'. Query with mapKeys(resource_sample_counts) or resource_sample_counts['Slide Microscopy'] > 0."},{"name":"mrna_expression_sample_count","type":"UInt32","comment":"Samples in any mRNA expression profile (RNA-Seq, microarray, TPM, z-scores; from sample_profile). Use this for \"does the study have expression data\" — rna_seq_sample_count and mrna_m … (136 more chars)

▶ clickhouse_run_select_query
{
 "query": "SELECT cancer_study_identifier, name, sample_count, mrna_expression_sample_count, rna_seq_sample_count, mrna_microarray_sample_count
FROM cancer_study
WHERE cancer_study_identifier IN (
  'ccrcc_sjuh_2023','kirc_tcga','ccrcc_tcga_gdc','kirc_tcga_pan_can_atlas_2018','kirc_tcga_pub',
  'rcc_cptac_gdc','kirp_tcga','prcc_tcga_gdc','kirp_tcga_pan_can_atlas_2018','rcc_iatlas_immotion150_2018',
  'nccrcc_genentech_2014','ccrcc_utokyo_2013','kirc_bgi','ccrcc_irc_2014','chrcc_tcga_gdc','kich_tcga_pub',
  'urcc_mskcc_2016','ccrcc_dfci_2019','ccrcc_iatlas_choueiri_2016'
)
ORDER BY mrna_expression_sample_count DESC",
 "max_rows": 30
}

◀ result
{"rows":[{"cancer_study_identifier":"kirc_tcga","name":"Kidney Renal Clear Cell Carcinoma (TCGA, Firehose Legacy)","sample_count":538,"mrna_expression_sample_count":535,"rna_seq_sample_count":534,"mrna_microarray_sample_count":72},{"cancer_study_identifier":"ccrcc_tcga_gdc","name":"Renal Clear Cell Carcinoma (TCGA GDC, 2025)","sample_count":537,"mrna_expression_sample_count":534,"rna_seq_sample_count":0,"mrna_microarray_sample_count":0},{"cancer_study_identifier":"kirc_tcga_pan_can_atlas_2018","name":"Kidney Renal Clear Cell Carcinoma (TCGA, PanCancer Atlas)","sample_count":512,"mrna_expression_sample_count":510,"rna_seq_sample_count":510,"mrna_microarray_sample_count":0},{"cancer_study_identifier":"kirc_tcga_pub","name":"Kidney Renal Clear Cell Carcinoma (TCGA, Nature 2013)","sample_count":446,"mrna_expression_sample_count":426,"rna_seq_sample_count":417,"mrna_microarray_sample_count":56},{"cancer_study_identifier":"rcc_cptac_gdc","name":"Renal Cell Carcinoma (CPTAC GDC, 2025)","sample_count":354,"mrna_expression_sample_count":353,"rna_seq_sample_count":0,"mrna_microarray_sample_count":0},{"cancer_study_identifier":"kirp_tcga","name":"Kidney Renal Papillary Cell Carcinoma (TCGA, Firehose Legacy)","sample_count":293,"mrna_expression_sample_count":291,"rna_seq_sample_count":291,"mrna_microarray_sample_count":16},{"cancer_study_identifier":"prcc_tcga_gdc","name":"Papillary Renal Cell Carcinoma (TCGA GDC, 2025)","sample_count":292,"mrna_expression_sample_count":291,"rna_seq_sample_count":0,"mrna_microarray_sample_count":0},{"cancer_study_identifier":"kirp_tcga_pan_can_atlas_2018","name":"Kidney Renal Papillary Cell Carcinoma (TCGA, PanCancer Atlas)","sample_count":283,"mrna_expression_sample_count":283,"rna_seq_sample_count":283,"mrna_microarray_sample_count":0},{"cancer_study_identifier":"rcc_iatlas_immotion150_2018","name":"Renal Cell Carcinoma (IMmotion150 Clinical Trial, Nat Med. 2018) - iAtlas Harmonized","sample_count":263,"mrna_expression_sample_count":263,"rna_seq_sample_count":0,"mrna_microarray_sample_count":0},{"cancer_study_identifier":"kich_tcga_pub","name":"Kidney Chromophobe (TCGA, Cancer Cell 2014)","sample_count":66,"mrna_expression_sample_count":66,"rna_seq_sample_count":66,"mrna_microarray_sample_count":0},{"cancer_study_identifier":"chrcc_tcga_gdc","name":"Chromophobe Renal Cell Carcinoma (TCGA GDC, 2025)","sample_count":66,"mrna_expression_sample_count":66,"rna_seq_sample_count":0,"mrna_microarray_sample_count":0},{"cancer_study_identifier":"ccrcc_iatlas_choueiri_2016","name":"Metastatic Renal Clear Cell Carcinoma (Phase 1 Biomarker Trial, Clin Cancer Res. 2016) - iAtlas Harmonized","sample_count":16,"mrna_expression_sample_count":16,"rna_seq_sample_count":0,"mrna_microarray_sample_count":0},{"cancer_study_identifier":"ccrcc_irc_2014","name":"Kidney Renal Clear Cell Carcinoma (IRC, Nat Genet 2014)","sample_count":79,"mrna_expression_sample_count":0,"rna_seq_sample_count":0,"mrna_microarray_sample_count":0},{"cancer_study_identif … (1295 more chars)

▶ resolve_and_route
{
 "studyIds": [
  "kirc_tcga",
  "ccrcc_tcga_gdc",
  "kirc_tcga_pan_can_atlas_2018",
  "kirc_tcga_pub",
  "rcc_cptac_gdc",
  "kirp_tcga",
  "prcc_tcga_gdc",
  "kirp_tcga_pan_can_atlas_2018",
  "rcc_iatlas_immotion150_2018",
  "kich_tcga_pub",
  "chrcc_tcga_gdc",
  "ccrcc_iatlas_choueiri_2016"
 ]
}

◀ result
{"success":true,"message":"Found 12 matching studies. Pick the best match (prefer TCGA → prefer PanCancer Atlas), use its metadata to call the appropriate navigation tool(s) and generate URLs immediately. Present other studies as alternatives.","data":{"totalCount":12,"studiesWithMetadata":[{"studyId":"kirc_tcga","name":"Kidney Renal Clear Cell Carcinoma (TCGA, Firehose Legacy)","sampleCount":538,"studyViewUrl":"https://www.cbioportal.org/study?id=kirc_tcga","metadata":{"clinicalAttributeIds":["AGE","AJCC_METASTASIS_CLINICAL_CM","AJCC_METASTASIS_PATHOLOGIC_PM","AJCC_NODES_PATHOLOGIC_PN","AJCC_PATHOLOGIC_TUMOR_STAGE","AJCC_STAGING_EDITION","AJCC_TUMOR_PATHOLOGIC_PT","CANCER_TYPE","CANCER_TYPE_DETAILED","CLINICAL_STAGE","CLIN_N_STAGE","CLIN_T_STAGE","DAYS_TO_COLLECTION","DAYS_TO_INITIAL_PATHOLOGIC_DIAGNOSIS","DAYS_TO_SPECIMEN_COLLECTION","DFS_MONTHS","DFS_STATUS","DISEASE_CODE","ECOG_SCORE","ERYTHROCYTE_SEDIMENTATION_RATE","ETHNICITY","EXTRANODAL_INVOLVEMENT","FORM_COMPLETION_DATE","FRACTION_GENOME_ALTERED","GRADE","HEMOGLOBIN_LEVEL","HISTOLOGICAL_DIAGNOSIS","HISTORY_NEOADJUVANT_TRTYN","HISTORY_OTHER_MALIGNANCY","ICD_10","ICD_O_3_HISTOLOGY","ICD_O_3_SITE","IDH_LEVEL","INFORMED_CONSENT_VERIFIED","INITIAL_PATHOLOGIC_DX_YEAR","IS_FFPE","KARNOFSKY_PERFORMANCE_SCORE","LATERALITY","LONGEST_DIMENSION","LYMPH_NODES_EXAMINED","LYMPH_NODES_EXAMINED_POSITIVE","LYMPH_NODE_EXAMINED_COUNT","METHOD_OF_SAMPLE_PROCUREMENT","MUTATION_COUNT","NEW_TUMOR_EVENT_AFTER_INITIAL_TREATMENT","OCT_EMBEDDED","ONCOTREE_CODE","OS_MONTHS","OS_STATUS","OTHER_METHOD_OF_SAMPLE_PROCUREMENT","OTHER_PATIENT_ID","OTHER_SAMPLE_ID","PATHOLOGY_REPORT_FILE_NAME","PATHOLOGY_REPORT_UUID","PERFORMANCE_STATUS_TIMING","PLATELET_COUNT","PROJECT_CODE","PROSPECTIVE_COLLECTION","RACE","RADIATION_TREATMENT_ADJUVANT","RETROSPECTIVE_COLLECTION","SAMPLE_COUNT","SAMPLE_INITIAL_WEIGHT","SAMPLE_TYPE","SAMPLE_TYPE_ID","SERUM_CALCIUM_LEVEL","SEX","SHORTEST_DIMENSION","SITE_OF_TUMOR_TISSUE","SMOKING_PACK_YEARS","SMOKING_YEAR_STARTED","SMOKING_YEAR_STOPPED","SOMATIC_STATUS","SPECIMEN_CURRENT_WEIGHT","SPECIMEN_FREEZING_METHOD","SPECIMEN_SECOND_LONGEST_DIMENSION","STAGE_OTHER","TARGETED_MOLECULAR_THERAPY","TIME_BETWEEN_CLAMPING_AND_FREEZING","TIME_BETWEEN_EXCISION_AND_FREEZING","TISSUE_SOURCE_SITE","TMB_NONSYNONYMOUS","TOBACCO_SMOKING_HISTORY_INDICATOR","TREATMENT_OUTCOME_FIRST_COURSE","TUMOR_STATUS","VIAL_NUMBER","WBC"],"molecularProfileIds":["kirc_tcga_gistic","kirc_tcga_linear_CNA","kirc_tcga_methylation_hm27","kirc_tcga_methylation_hm450","kirc_tcga_mrna","kirc_tcga_mrna_median_Zscores","kirc_tcga_mrna_median_all_sample_Zscores","kirc_tcga_mutations","kirc_tcga_rna_seq_v2_mrna","kirc_tcga_rna_seq_v2_mrna_median_Zscores","kirc_tcga_rna_seq_v2_mrna_median_all_sample_Zscores","kirc_tcga_rppa","kirc_tcga_rppa_Zscores"],"heatmapProfileIds":["kirc_tcga_methylation_hm27","kirc_tcga_methylation_hm450","kirc_tcga_mrna_median_Zscores","kirc_tcga_mrna_median_all_sample_Zscores","kirc_tcga_rna_seq_v2_mrna_median_Zscores" … (19814 more chars)

═ answer (success)
I recommend the **TCGA PanCancer Atlas** clear cell RCC study as the top pick (largest curated cohort with harmonized mRNA z-scores).

**[Explore Kidney Renal Clear Cell Carcinoma (TCGA, PanCancer Atlas) — Study View](https://www.cbioportal.org/study?id=kirc_tcga_pan_can_atlas_2018)**

This study offers 510 samples with RNA-Seq expression (`rna_seq_v2_mrna`), plus z-score profiles, CNA, mutations, RPPA protein, and methylation data.

**Other renal studies with mRNA expression** (all listed in the table above) are also browsable individually via their `studyId` at `https://www.cbioportal.org/study?id=<studyId>` — e.g. `kirc_tcga`, `ccrcc_tcga_gdc`, `rcc_cptac_gdc`, `kirp_tcga_pan_can_atlas_2018`, `kich_tcga_pub`, etc.