google-bigquery - 使用命令行 BQ 命令从 JSON 文件导入数据时将空字符串 ("") 转换为 Double 数据类型

Question

哪些步骤会重现问题？1.我正在运行命令: ./bq load --source_format=NEWLINE_DELIMITED_JSON --schema=lifeSchema.json dataset_test1.table_test_3 lifeData.json2.我附上了数据源文件和架构文件。3. 它抛出一个错误——文件​​中从位置 0 开始的行中的 JSON 解析错误:文件-00000000。无法将值转换为 double 值。 field :计算结果A；值:

预期的输出是什么？你看到了什么？我想将空字符串转换为 NULL 或 0

您使用的是什么版本的产品？在什么操作系统上？我正在使用 MAC OSX YOSEMITE

来源JSON lifeData.json {“架构”:{“供应商”:“com.bd.snowplow”，“名称”:“in_life”，“格式”:“jsonschema”，“版本”:“1-0-2”}，“数据” :{"step":0,"info_userId":"53493764","info_campaignCity":"","info_self_currentAge":45,"info_self_gender":"male","info_self_retirementAge":60,"info_self_married":false,"info_self_lifeExpectancy":0,"info_dependantChildren":0,"info_dependantAdults":0,"info_spouse_working":true,"info_spouse_currentAge":33,"info_spouse_retirementAge":60,"info_spouse_monthlyIncome":0,"info_spouse_incomeInflation":5,"info_spouse_lifeExpectancy":0，“info_finances_sumInsured”:0，“info_finances_expectedReturns”:6，“info_finances_loanAmount”:0，“info_finances_liquidateSavings”:true，“info_finances_savingsAmount”:0，“info_finances_monthlyExpense”:0，“info_finances_expenseInflation”:6，“info_finances_expenseReduction”:10 “info_finances_monthlyIncome”:0，“info_finances_incomeInflation”:5，“computed_results_A”:“”，“computed_results_B”:null，“computed_results_C”:null，“computed_results_D”:null，“uid_epoch”:“53493764_1466504541604”，“state":"init","campaign_id":"","campaign_link":"","tool_version":"20150701-lfi-v1"},"hierarchy":{"rootId":"94583157-af34-4ecb- 8024-b9af7c9e54fa","rootTstamp":"2016-06-21 10:22:24.000","re​​fRoot":"events","re​​fTree":["events","in_life"],"refParent":"events "}}

架构 JSON lifeSchema.json { "name": "computed_results_A", “类型”:“ float ”， “模式”:“可为空”

Answer 1

尝试将 JSON 文件加载为一列 CSV 文件。

bq load --field_delimiter='|' proj:set.table file.json json:string

将文件加载到 BigQuery 后，您可以使用 JSON_EXTRACT_SCALAR 或 JavaScript UDF 完全自由地解析 JSON。