Loading JSON files into BigQuery
I am trying to load a JSON file into BigQuery using the bq load command
bq load --autodetect --source_format=NEWLINE_DELIMITED_JSON project_abd:ds.online_data gs://online_data/file.json
One of the key:value pair in the JSON file looks like -
"taxIdentifier":"T"
The bq load fails with the message - Error while reading data, error message: JSON parsing error in row
starting at position 713452: Could not convert value to boolean.
Field: taxIdentifier; Value: T (The JSON is really huge, hence cant paste it here)
I am really confused as to why the autodetect is treating the value T as boolean. I have tried all combinations of creating the table with STRING datatype and then load the table, but due to autodetect, it errors out mentioning - changed type from STRING to BOOLEAN, if I do not use the autodetect the load succeeds.
I have to use the "autodetect" feature, since the JSON is a result of an API call and the columns may increase or decrease.
Any idea why the value T is behaving weird, and how to get around this ?
json google-cloud-platform google-bigquery
asked Nov 13 '18 at 8:33
vp1008vp1008
206
add a comment |
I am trying to load a JSON file into BigQuery using the bq load command
bq load --autodetect --source_format=NEWLINE_DELIMITED_JSON project_abd:ds.online_data gs://online_data/file.json
One of the key:value pair in the JSON file looks like -
"taxIdentifier":"T"
The bq load fails with the message - Error while reading data, error message: JSON parsing error in row
starting at position 713452: Could not convert value to boolean.
Field: taxIdentifier; Value: T (The JSON is really huge, hence cant paste it here)
I am really confused as to why the autodetect is treating the value T as boolean. I have tried all combinations of creating the table with STRING datatype and then load the table, but due to autodetect, it errors out mentioning - changed type from STRING to BOOLEAN, if I do not use the autodetect the load succeeds.
I have to use the "autodetect" feature, since the JSON is a result of an API call and the columns may increase or decrease.
Any idea why the value T is behaving weird, and how to get around this ?
json google-cloud-platform google-bigquery
asked Nov 13 '18 at 8:33
vp1008vp1008
206
1
Can you just load first row of your json, than export schema, adjust field types and load the full json with your adjusted schema instead of autodetect? it's much more reliable solution
– AlienDeg
Nov 13 '18 at 8:55
@AlienDeg- As i mentioned above, when i adjust schema and do not use autodetect, the load succeeds, but for the current requirement, i need to use the autodetect, since the schema of exported JSON may vary.
– vp1008
Nov 13 '18 at 14:36
This sounds like a bug, that auto-detect accepts "T" as boolean while the JSON reader doesn't. Please file a bug at issuetracker.google.com/issues/…. Thanks.
– Hua Zhang
Nov 19 '18 at 23:06
I have raised a bug with Google. Thanks Hua Zhang
– vp1008
Nov 21 '18 at 4:07
add a comment |
I am trying to load a JSON file into BigQuery using the bq load command
bq load --autodetect --source_format=NEWLINE_DELIMITED_JSON project_abd:ds.online_data gs://online_data/file.json
One of the key:value pair in the JSON file looks like -
"taxIdentifier":"T"
The bq load fails with the message - Error while reading data, error message: JSON parsing error in row
starting at position 713452: Could not convert value to boolean.
Field: taxIdentifier; Value: T (The JSON is really huge, hence cant paste it here)
I am really confused as to why the autodetect is treating the value T as boolean. I have tried all combinations of creating the table with STRING datatype and then load the table, but due to autodetect, it errors out mentioning - changed type from STRING to BOOLEAN, if I do not use the autodetect the load succeeds.
I have to use the "autodetect" feature, since the JSON is a result of an API call and the columns may increase or decrease.
Any idea why the value T is behaving weird, and how to get around this ?
json google-cloud-platform google-bigquery
asked Nov 13 '18 at 8:33
vp1008vp1008
206
I am trying to load a JSON file into BigQuery using the bq load command
bq load --autodetect --source_format=NEWLINE_DELIMITED_JSON project_abd:ds.online_data gs://online_data/file.json
One of the key:value pair in the JSON file looks like -
"taxIdentifier":"T"
The bq load fails with the message - Error while reading data, error message: JSON parsing error in row
starting at position 713452: Could not convert value to boolean.
Field: taxIdentifier; Value: T (The JSON is really huge, hence cant paste it here)
I am really confused as to why the autodetect is treating the value T as boolean. I have tried all combinations of creating the table with STRING datatype and then load the table, but due to autodetect, it errors out mentioning - changed type from STRING to BOOLEAN, if I do not use the autodetect the load succeeds.
I have to use the "autodetect" feature, since the JSON is a result of an API call and the columns may increase or decrease.
Any idea why the value T is behaving weird, and how to get around this ?
json google-cloud-platform google-bigquery
json google-cloud-platform google-bigquery
asked Nov 13 '18 at 8:33
vp1008vp1008
206
asked Nov 13 '18 at 8:33
vp1008vp1008
206
asked Nov 13 '18 at 8:33
vp1008vp1008
206
asked Nov 13 '18 at 8:33
vp1008vp1008
206
asked Nov 13 '18 at 8:33
vp1008vp1008
206
206
1
Can you just load first row of your json, than export schema, adjust field types and load the full json with your adjusted schema instead of autodetect? it's much more reliable solution
– AlienDeg
Nov 13 '18 at 8:55
@AlienDeg- As i mentioned above, when i adjust schema and do not use autodetect, the load succeeds, but for the current requirement, i need to use the autodetect, since the schema of exported JSON may vary.
– vp1008
Nov 13 '18 at 14:36
This sounds like a bug, that auto-detect accepts "T" as boolean while the JSON reader doesn't. Please file a bug at issuetracker.google.com/issues/…. Thanks.
– Hua Zhang
Nov 19 '18 at 23:06
I have raised a bug with Google. Thanks Hua Zhang
– vp1008
Nov 21 '18 at 4:07
add a comment |
1
Can you just load first row of your json, than export schema, adjust field types and load the full json with your adjusted schema instead of autodetect? it's much more reliable solution
– AlienDeg
Nov 13 '18 at 8:55
@AlienDeg- As i mentioned above, when i adjust schema and do not use autodetect, the load succeeds, but for the current requirement, i need to use the autodetect, since the schema of exported JSON may vary.
– vp1008
Nov 13 '18 at 14:36
This sounds like a bug, that auto-detect accepts "T" as boolean while the JSON reader doesn't. Please file a bug at issuetracker.google.com/issues/…. Thanks.
– Hua Zhang
Nov 19 '18 at 23:06
I have raised a bug with Google. Thanks Hua Zhang
– vp1008
Nov 21 '18 at 4:07
1
1
Can you just load first row of your json, than export schema, adjust field types and load the full json with your adjusted schema instead of autodetect? it's much more reliable solution
– AlienDeg
Nov 13 '18 at 8:55
Can you just load first row of your json, than export schema, adjust field types and load the full json with your adjusted schema instead of autodetect? it's much more reliable solution
– AlienDeg
Nov 13 '18 at 8:55
@AlienDeg- As i mentioned above, when i adjust schema and do not use autodetect, the load succeeds, but for the current requirement, i need to use the autodetect, since the schema of exported JSON may vary.
– vp1008
Nov 13 '18 at 14:36
@AlienDeg- As i mentioned above, when i adjust schema and do not use autodetect, the load succeeds, but for the current requirement, i need to use the autodetect, since the schema of exported JSON may vary.
– vp1008
Nov 13 '18 at 14:36
This sounds like a bug, that auto-detect accepts "T" as boolean while the JSON reader doesn't. Please file a bug at issuetracker.google.com/issues/…. Thanks.
– Hua Zhang
Nov 19 '18 at 23:06
This sounds like a bug, that auto-detect accepts "T" as boolean while the JSON reader doesn't. Please file a bug at issuetracker.google.com/issues/…. Thanks.
– Hua Zhang
Nov 19 '18 at 23:06
I have raised a bug with Google. Thanks Hua Zhang
– vp1008
Nov 21 '18 at 4:07
I have raised a bug with Google. Thanks Hua Zhang
– vp1008
Nov 21 '18 at 4:07
add a comment |
0
active
oldest
votes
Your Answer
StackExchange.ifUsing("editor", function ()
StackExchange.using("externalEditor", function ()
StackExchange.using("snippets", function ()
StackExchange.snippets.init();
);
);
, "code-snippets");
StackExchange.ready(function()
var channelOptions =
tags: "".split(" "),
id: "1"
;
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function()
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled)
StackExchange.using("snippets", function()
createEditor();
);
else
createEditor();
);
function createEditor()
StackExchange.prepareEditor(
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader:
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
,
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
);
);
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function ()
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53276819%2floading-json-files-into-bigquery%23new-answer', 'question_page');
);
Post as a guest
Required, but never shown
0
active
oldest
votes
0
active
oldest
votes
active
oldest
votes
active
oldest
votes
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function ()
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53276819%2floading-json-files-into-bigquery%23new-answer', 'question_page');
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
1
Can you just load first row of your json, than export schema, adjust field types and load the full json with your adjusted schema instead of autodetect? it's much more reliable solution
– AlienDeg
Nov 13 '18 at 8:55
@AlienDeg- As i mentioned above, when i adjust schema and do not use autodetect, the load succeeds, but for the current requirement, i need to use the autodetect, since the schema of exported JSON may vary.
– vp1008
Nov 13 '18 at 14:36
This sounds like a bug, that auto-detect accepts "T" as boolean while the JSON reader doesn't. Please file a bug at issuetracker.google.com/issues/…. Thanks.
– Hua Zhang
Nov 19 '18 at 23:06
I have raised a bug with Google. Thanks Hua Zhang
– vp1008
Nov 21 '18 at 4:07