Schema conflict when storing dataframes with datetime objects using load_table_from_dataframe()
I'm trying to load data from a Pandas DataFrames into a BigQuery table. The DataFrame has a column of dtype datetime64[ns], and when I try to store the df using load_table_from_dataframe(), I get
google.api_core.exceptions.BadRequest: 400 Provided Schema does not match Table [table name]. Field computation_triggered_time has changed type from DATETIME to TIMESTAMP.
The table has a schema which reads
CREATE TABLE `[table name]` (
...
computation_triggered_time DATETIME NOT NULL,
...
)
In the DataFrame, computation_triggered_time is a datetime64[ns] column. When I read the original DataFrame from CSV, I convert it from text to datetime like so:
df['computation_triggered_time'] =
df.to_datetime(df['computation_triggered_time']).values.astype('datetime64[ms]')
Note:
The .values.astype('datetime64[ms]') part is necessary because load_table_from_dataframe() uses PyArrow to serialize the df and that fails if the data has nanosecond-precision. The error is something like
[...] Casting from timestamp[ns] to timestamp[ms] would lose data
pandas google-cloud-platform google-bigquery pyarrow
asked Nov 16 '18 at 8:06
Johannes BauerJohannes Bauer
205210
add a comment |
I'm trying to load data from a Pandas DataFrames into a BigQuery table. The DataFrame has a column of dtype datetime64[ns], and when I try to store the df using load_table_from_dataframe(), I get
google.api_core.exceptions.BadRequest: 400 Provided Schema does not match Table [table name]. Field computation_triggered_time has changed type from DATETIME to TIMESTAMP.
The table has a schema which reads
CREATE TABLE `[table name]` (
...
computation_triggered_time DATETIME NOT NULL,
...
)
In the DataFrame, computation_triggered_time is a datetime64[ns] column. When I read the original DataFrame from CSV, I convert it from text to datetime like so:
df['computation_triggered_time'] =
df.to_datetime(df['computation_triggered_time']).values.astype('datetime64[ms]')
Note:
The .values.astype('datetime64[ms]') part is necessary because load_table_from_dataframe() uses PyArrow to serialize the df and that fails if the data has nanosecond-precision. The error is something like
[...] Casting from timestamp[ns] to timestamp[ms] would lose data
pandas google-cloud-platform google-bigquery pyarrow
asked Nov 16 '18 at 8:06
Johannes BauerJohannes Bauer
205210
Can you give an example of the format of 'computation_triggered_time' in the dataframe?
– Bobbylank
Nov 16 '18 at 9:38
@Bobbylank: see Issue github.com/googleapis/google-cloud-python/issues/6542
– Johannes Bauer
Nov 16 '18 at 20:14
add a comment |
I'm trying to load data from a Pandas DataFrames into a BigQuery table. The DataFrame has a column of dtype datetime64[ns], and when I try to store the df using load_table_from_dataframe(), I get
google.api_core.exceptions.BadRequest: 400 Provided Schema does not match Table [table name]. Field computation_triggered_time has changed type from DATETIME to TIMESTAMP.
The table has a schema which reads
CREATE TABLE `[table name]` (
...
computation_triggered_time DATETIME NOT NULL,
...
)
In the DataFrame, computation_triggered_time is a datetime64[ns] column. When I read the original DataFrame from CSV, I convert it from text to datetime like so:
df['computation_triggered_time'] =
df.to_datetime(df['computation_triggered_time']).values.astype('datetime64[ms]')
Note:
The .values.astype('datetime64[ms]') part is necessary because load_table_from_dataframe() uses PyArrow to serialize the df and that fails if the data has nanosecond-precision. The error is something like
[...] Casting from timestamp[ns] to timestamp[ms] would lose data
pandas google-cloud-platform google-bigquery pyarrow
asked Nov 16 '18 at 8:06
Johannes BauerJohannes Bauer
205210
I'm trying to load data from a Pandas DataFrames into a BigQuery table. The DataFrame has a column of dtype datetime64[ns], and when I try to store the df using load_table_from_dataframe(), I get
google.api_core.exceptions.BadRequest: 400 Provided Schema does not match Table [table name]. Field computation_triggered_time has changed type from DATETIME to TIMESTAMP.
The table has a schema which reads
CREATE TABLE `[table name]` (
...
computation_triggered_time DATETIME NOT NULL,
...
)
In the DataFrame, computation_triggered_time is a datetime64[ns] column. When I read the original DataFrame from CSV, I convert it from text to datetime like so:
df['computation_triggered_time'] =
df.to_datetime(df['computation_triggered_time']).values.astype('datetime64[ms]')
Note:
The .values.astype('datetime64[ms]') part is necessary because load_table_from_dataframe() uses PyArrow to serialize the df and that fails if the data has nanosecond-precision. The error is something like
[...] Casting from timestamp[ns] to timestamp[ms] would lose data
pandas google-cloud-platform google-bigquery pyarrow
pandas google-cloud-platform google-bigquery pyarrow
asked Nov 16 '18 at 8:06
Johannes BauerJohannes Bauer
205210
asked Nov 16 '18 at 8:06
Johannes BauerJohannes Bauer
205210
asked Nov 16 '18 at 8:06
Johannes BauerJohannes Bauer
205210
asked Nov 16 '18 at 8:06
Johannes BauerJohannes Bauer
205210
asked Nov 16 '18 at 8:06
Johannes BauerJohannes Bauer
205210
205210
Can you give an example of the format of 'computation_triggered_time' in the dataframe?
– Bobbylank
Nov 16 '18 at 9:38
@Bobbylank: see Issue github.com/googleapis/google-cloud-python/issues/6542
– Johannes Bauer
Nov 16 '18 at 20:14
add a comment |
Can you give an example of the format of 'computation_triggered_time' in the dataframe?
– Bobbylank
Nov 16 '18 at 9:38
@Bobbylank: see Issue github.com/googleapis/google-cloud-python/issues/6542
– Johannes Bauer
Nov 16 '18 at 20:14
Can you give an example of the format of 'computation_triggered_time' in the dataframe?
– Bobbylank
Nov 16 '18 at 9:38
Can you give an example of the format of 'computation_triggered_time' in the dataframe?
– Bobbylank
Nov 16 '18 at 9:38
@Bobbylank: see Issue github.com/googleapis/google-cloud-python/issues/6542
– Johannes Bauer
Nov 16 '18 at 20:14
@Bobbylank: see Issue github.com/googleapis/google-cloud-python/issues/6542
– Johannes Bauer
Nov 16 '18 at 20:14
add a comment |
1 Answer
1
active
oldest
votes
This looks like a problem with Google's google-cloud-python package, can you report the bug there? https://github.com/googleapis/google-cloud-python
answered Nov 16 '18 at 14:44
Wes McKinneyWes McKinney
56.7k2011594
Done: github.com/googleapis/google-cloud-python/issues/6542
– Johannes Bauer
Nov 16 '18 at 20:14
add a comment |
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%2f53333742%2fschema-conflict-when-storing-dataframes-with-datetime-objects-using-load-table-f%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
This looks like a problem with Google's google-cloud-python package, can you report the bug there? https://github.com/googleapis/google-cloud-python
answered Nov 16 '18 at 14:44
Wes McKinneyWes McKinney
56.7k2011594
Done: github.com/googleapis/google-cloud-python/issues/6542
– Johannes Bauer
Nov 16 '18 at 20:14
add a comment |
This looks like a problem with Google's google-cloud-python package, can you report the bug there? https://github.com/googleapis/google-cloud-python
answered Nov 16 '18 at 14:44
Wes McKinneyWes McKinney
56.7k2011594
Done: github.com/googleapis/google-cloud-python/issues/6542
– Johannes Bauer
Nov 16 '18 at 20:14
add a comment |
This looks like a problem with Google's google-cloud-python package, can you report the bug there? https://github.com/googleapis/google-cloud-python
answered Nov 16 '18 at 14:44
Wes McKinneyWes McKinney
56.7k2011594
This looks like a problem with Google's google-cloud-python package, can you report the bug there? https://github.com/googleapis/google-cloud-python
answered Nov 16 '18 at 14:44
Wes McKinneyWes McKinney
56.7k2011594
answered Nov 16 '18 at 14:44
Wes McKinneyWes McKinney
56.7k2011594
answered Nov 16 '18 at 14:44
Wes McKinneyWes McKinney
56.7k2011594
answered Nov 16 '18 at 14:44
Wes McKinneyWes McKinney
56.7k2011594
56.7k2011594
Done: github.com/googleapis/google-cloud-python/issues/6542
– Johannes Bauer
Nov 16 '18 at 20:14
add a comment |
Done: github.com/googleapis/google-cloud-python/issues/6542
– Johannes Bauer
Nov 16 '18 at 20:14
Done: github.com/googleapis/google-cloud-python/issues/6542
– Johannes Bauer
Nov 16 '18 at 20:14
Done: github.com/googleapis/google-cloud-python/issues/6542
– Johannes Bauer
Nov 16 '18 at 20:14
add a comment |
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%2f53333742%2fschema-conflict-when-storing-dataframes-with-datetime-objects-using-load-table-f%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
Can you give an example of the format of 'computation_triggered_time' in the dataframe?
– Bobbylank
Nov 16 '18 at 9:38
@Bobbylank: see Issue github.com/googleapis/google-cloud-python/issues/6542
– Johannes Bauer
Nov 16 '18 at 20:14