将BigQuery的输出从Python保存为JSON。

huangapple
go评论112阅读模式
英文:

Save the output of bigquery in JSON from python

问题

以下是修改后的代码，以将结果保存为JSON格式：

  1. from google.cloud import bigquery
  2. import json
  4. def query_stackoverflow(project_id="gwas-386212"):
  5.     client = bigquery.Client()
  6.     query = """
  7.     WITH SNP_info AS (
  8.       SELECT
  9.         CONCAT(CAST(rs_id AS string)) AS identifier
  10.       FROM
  11.         `gwas-386212.gwas_dataset_1.SNPs_intergenic_vep_pha005199`
  12.     )
  13.     SELECT
  14.       SNP_info.identifier AS identifier,
  15.       variants.identifier AS identifier_1,
  16.       variants.chr_id AS chr_id,
  17.       variants.position AS position,
  18.       variants.ref AS ref,
  19.       variants.alt AS alt,
  20.       variants.most_severe_consequence AS most_severe_consequence,
  21.       variants.gene_id_any_distance AS gene_id_any_distance,
  22.       variants.gene_id_any AS gene_id_any,
  23.       variants.gene_id_prot_coding_distance AS gene_id_prot_coding_distance,
  24.       variants.gene_id_prot_coding AS gene_id_prot_coding
  25.     FROM SNP_info
  26.     JOIN (
  27.       SELECT
  28.         CONCAT(CAST(rs_id AS string)) AS identifier,
  29.         chr_id AS chr_id,
  30.         position AS position,
  31.         ref_allele AS ref,
  32.         alt_allele AS alt,
  33.         most_severe_consequence AS most_severe_consequence,
  34.         gene_id_any_distance AS gene_id_any_distance,
  35.         gene_id_any AS gene_id_any,
  36.         gene_id_prot_coding_distance AS gene_id_prot_coding_distance,
  37.         gene_id_prot_coding AS gene_id_prot_coding
  38.       FROM
  39.         `bigquery-public-data.open_targets_genetics.variants`
  40.     ) variants
  41.     ON SNP_info.identifier = variants.identifier
  42.     """
  43.     
  44.     results = client.query(query)
  46.     # Convert the results to a list of dictionaries
  47.     result_list = [dict(row) for row in results]
  49.     # Save the results as JSON
  50.     with open('output.json', 'w') as json_file:
  51.         json.dump(result_list, json_file)
  53. # Call the function to execute the query and save the results as JSON
  54. query_stackoverflow()

这段代码会执行查询并将结果保存为名为"output.json"的JSON文件。

英文:

How can I modify this script to be able to see/print some of the results and write the output in JSON :

  1. from google.cloud import bigquery
  4. def query_stackoverflow(project_id="gwas-386212"):
  5.     client = bigquery.Client()
  6. query_job = client.query(
  7.         """
  8.         WITH
  9.   SNP_info AS (
  10.   SELECT
  11.     CONCAT(CAST(rs_id AS string)) AS identifier
  12.   FROM
  13.   `gwas-386212.gwas_dataset_1.SNPs_intergenic_vep_pha005199`)
  14. SELECT
  15.   *
  16. FROM
  17.   SNP_info
  18. JOIN (
  19.   SELECT
  20.     CONCAT(CAST(rs_id AS string)) AS identifier,
  21. chr_id AS chr_id,
  22. position AS position, 
  23. ref_allele AS ref,
  24. alt_allele AS alt,
  25. most_severe_consequence AS most_severe_consequence, 
  26. gene_id_any_distance AS gene_id_any_distance,
  27. gene_id_any AS gene_id_any,
  28. gene_id_prot_coding_distance AS gene_id_prot_coding_distance, 
  29. gene_id_prot_coding AS gene_id_prot_coding
  30.  FROM
  31.     `bigquery-public-data.open_targets_genetics.variants`) variants
  32. ON
  33.   SNP_info.identifier = variants.identifier""" 
  34.   )
  35. results = client.query(query)
  37. for row in results:
  38.     title = row['identifier']
  39.     identifier = row['identifier']
  40.     #print(f'{identifier}')

This is just printing a column the intentifier. i want to save the resulted table in JSON format. The JSOn from the google cloud platform should look something like this:

  1. [{
  2.   "identifier": "rs62063022",
  3.   "identifier_1": "rs62063022",
  4.   "chr_id": "17",
  5.   "position": "51134537",
  6.   "ref": "T",
  7.   "alt": "G",
  8.   "most_severe_consequence": "intergenic_variant",
  9.   "gene_id_any_distance": "13669",
  10.   "gene_id_any": "ENSG00000008294",
  11.   "gene_id_prot_coding_distance": "13669",
  12.   "gene_id_prot_coding": "ENSG00000008294"
  13. }, {
  14.   "identifier": "rs12944420",
  15.   "identifier_1": "rs12944420",
  16.   "chr_id": "17",
  17.   "position": "42640692",
  18.   "ref": "T",
  19.   "alt": "C",
  20.   "most_severe_consequence": "intergenic_variant",
  21.   "gene_id_any_distance": "18592",
  22.   "gene_id_any": "ENSG00000037042",
  23.   "gene_id_prot_coding_distance": "18592",
  24.   "gene_id_prot_coding": "ENSG00000037042"
  25. },

答案1

得分: 0

查看 json 文档以获取更多信息。

  1. records = [dict(row) for row in results]
  2. out_file = open("bigquery_response.json", "w")
  3. json.dump(records, out_file, indent=6)
  4. out_file.close()
英文:

Check out json documentation for further information.

  1. records = [dict(row) for row in results]
  2. out_file = open("bigquery_response.json", "w")
  3. json.dump(records , out_file, indent = 6)
  4. out_file.close()

huangapple
  • 本文由 发表于 2023年6月1日 21:44:35
  • 转载请务必保留本文链接：https://go.coder-hub.com/76382547.html
  • google-bigquery
  • python
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定