Issues with GCP Big Query

Data Access Google BigQuery/Cloud
1

Has anything changed? It appears that the queries are no longer working? It complains of no data in EU, and US dataset looks like its not updated since 2022?

2

I also am having issues. When I try this query in the console:

SELECT
  associations.targetId AS target_id,
  targets.approvedSymbol AS target_approved_symbol,
  associations.diseaseId AS disease_id,
  diseases.name AS disease_name,
  associations.score AS overall_association_score
FROM
  `open-targets-prod.platform.associationByOverallDirect` AS associations
JOIN
  `open-targets-prod.platform.diseases` AS diseases
ON
  associations.diseaseId = diseases.id
JOIN
  `open-targets-prod.platform.targets` AS targets
ON
  associations.targetId = targets.id
WHERE
  associations.targetId='ENSG00000129514'
ORDER BY
  associations.score DESC

I get an error: “Not found: Table open-targets-prod:platform.associationByOverallDirect was not found in location EU”

Thanks!

3

Hi, I’m really sorry to hear you are having issues with accessing our data via BigQuery. I took a look at the query and it looked OK, except I usually refer the datasets differently. Can you try giving this query a go:

SELECT
  associations.targetId AS target_id,
  targets.approvedSymbol AS target_approved_symbol,
  associations.diseaseId AS disease_id,
  diseases.name AS disease_name,
  associations.score AS overall_association_score
FROM
  `bigquery-public-data.open_targets_platform.associationByOverallDirect` AS associations
JOIN
  `bigquery-public-data.open_targets_platform.diseases` AS diseases
ON
  associations.diseaseId = diseases.id
JOIN
  `bigquery-public-data.open_targets_platform.targets` AS targets
ON
  associations.targetId = targets.id
WHERE
  associations.targetId='ENSG00000129514'
ORDER BY
  associations.score DESC

It runs for me, but please, let me know if you have issues.

4

That worked for me! Thanks so much for your quick response and help

5

Hi,

Thanks for the questions and apologies for the inconveniences. We have made a lot of exciting changes to the platform for 2503, some of which are breaking. Two important changes with regards to bigQuery are a) that we have included some new tables and b) that we have unified the table names with the folder names of the data downloads on our FTP/GCS. Although this causes some disruption initially, it should make navigating the data more logical. Below I have pasted the mappings so that you can lookup the pre-2503 bq table and replace it with the 2503 table name in any scripts.

The public bigquery-public-data.open_targets_platform instance still points to a pre-2503 version of our bigQuery, but it will be updated in a matter of weeks as well.

I hope this helps. Please get in touch if you have any questions.

bq_2503_table,bq_pre_2503_table
association_by_datasource_direct,associationByDatasourceDirect
association_by_datasource_indirect,associationByDatasourceIndirect
association_by_datatype_direct,associationByDatatypeDirect
association_by_datatype_indirect,associationByDatatypeIndirect
association_by_overall_indirect,associationByOverallIndirect
association_overall_direct,associationByOverallDirect
biosample,NA
colocalisation_coloc,NA
colocalisation_ecaviar,NA
credible_set,NA
disease,diseases
disease_hpo,hpo
disease_phenotype,diseaseToPhenotype
drug_indication,indication
drug_mechanism_of_action,mechanismOfAction
drug_molecule,molecule
drug_warning,drugWarnings
evidence,evidence
expression,baselineExpression
go,go
interaction,interaction
interaction_evidence,interactionEvidence
known_drug,knownDrugsAggregated
l2g_prediction,NA
literature,NA
literature_vector,NA
mouse_phenotype,mousePhenotypes
openfda_significant_adverse_drug_reactions,significantAdverseDrugReactions
openfda_significant_adverse_target_reactions,NA
pharmacogenomics,pharmacogenomics
reactome,reactome
so,NA
study,NA
target,targets
target_essentiality,targetEssentiality
target_prioritisation,targetPrioritisation
variant,NA