Takes too long to export data from bigquery into Jupyter notebook
In the Jupyter Notebook, I am trying to import data from BigQuery using an sql-like query on the BigQuery server. I then store the data in a dataframe:
import os
os.environ["GOOGLE_APPLICATION_CREDENTIALS"]="credentials.json"
from google.cloud import bigquery
sql = """
SELECT * FROM dataset.table
"""
client = bigquery.Client()
df_bq = client.query(sql).to_dataframe()
The data has the shape (6000000, 8) and uses about 350MB of memory once stored in the dataframe.
The query sql, if executed directly in BQ, takes about 2 seconds.
However, it usually takes about 30-40 minutes to execute the code above, and more often than not the code fails to execute raising the following error:
ConnectionError: ('Connection aborted.', OSError("(10060, 'WSAETIMEDOUT')",))
All in all, there could be three reasons for the error:
- It takes the BigQuery server a long time to execute the query
- It takes a long time to transfer data (I don't understand why a 350MB file should take 30min to be sent over the network. I tried using a LAN connection to eliminate server cuts and maximize throughput, which didn't help)
- It takes a long time to set a dataframe with the data from BigQuery
Would be happy to gain any insight into the problem, thanks in advance!
python dataframe
google-bigquery jupyter-notebook jupyter asked Nov 22 at 14:22
Max Sfnv
211
add a comment |
In the Jupyter Notebook, I am trying to import data from BigQuery using an sql-like query on the BigQuery server. I then store the data in a dataframe:
import os
os.environ["GOOGLE_APPLICATION_CREDENTIALS"]="credentials.json"
from google.cloud import bigquery
sql = """
SELECT * FROM dataset.table
"""
client = bigquery.Client()
df_bq = client.query(sql).to_dataframe()
The data has the shape (6000000, 8) and uses about 350MB of memory once stored in the dataframe.
The query sql, if executed directly in BQ, takes about 2 seconds.
However, it usually takes about 30-40 minutes to execute the code above, and more often than not the code fails to execute raising the following error:
ConnectionError: ('Connection aborted.', OSError("(10060, 'WSAETIMEDOUT')",))
All in all, there could be three reasons for the error:
- It takes the BigQuery server a long time to execute the query
- It takes a long time to transfer data (I don't understand why a 350MB file should take 30min to be sent over the network. I tried using a LAN connection to eliminate server cuts and maximize throughput, which didn't help)
- It takes a long time to set a dataframe with the data from BigQuery
Would be happy to gain any insight into the problem, thanks in advance!
python dataframe
google-bigquery jupyter-notebook jupyter asked Nov 22 at 14:22
Max Sfnv
211
40 mins seems a lot indeed...maybe it's the http transport layer. I wonder if you can make two tests: first, what happens if you set a limit to 10000 rows for instance in your query? Is it still slow? Also, can you test first exporting this data to GCS gizzed and then bring it to host to finally read as dataframe, is it still slow?
– Willian Fuks
Nov 22 at 17:59
add a comment |
In the Jupyter Notebook, I am trying to import data from BigQuery using an sql-like query on the BigQuery server. I then store the data in a dataframe:
import os
os.environ["GOOGLE_APPLICATION_CREDENTIALS"]="credentials.json"
from google.cloud import bigquery
sql = """
SELECT * FROM dataset.table
"""
client = bigquery.Client()
df_bq = client.query(sql).to_dataframe()
The data has the shape (6000000, 8) and uses about 350MB of memory once stored in the dataframe.
The query sql, if executed directly in BQ, takes about 2 seconds.
However, it usually takes about 30-40 minutes to execute the code above, and more often than not the code fails to execute raising the following error:
ConnectionError: ('Connection aborted.', OSError("(10060, 'WSAETIMEDOUT')",))
All in all, there could be three reasons for the error:
- It takes the BigQuery server a long time to execute the query
- It takes a long time to transfer data (I don't understand why a 350MB file should take 30min to be sent over the network. I tried using a LAN connection to eliminate server cuts and maximize throughput, which didn't help)
- It takes a long time to set a dataframe with the data from BigQuery
Would be happy to gain any insight into the problem, thanks in advance!
python dataframe
google-bigquery jupyter-notebook jupyter asked Nov 22 at 14:22
Max Sfnv
211
In the Jupyter Notebook, I am trying to import data from BigQuery using an sql-like query on the BigQuery server. I then store the data in a dataframe:
import os
os.environ["GOOGLE_APPLICATION_CREDENTIALS"]="credentials.json"
from google.cloud import bigquery
sql = """
SELECT * FROM dataset.table
"""
client = bigquery.Client()
df_bq = client.query(sql).to_dataframe()
The data has the shape (6000000, 8) and uses about 350MB of memory once stored in the dataframe.
The query sql, if executed directly in BQ, takes about 2 seconds.
However, it usually takes about 30-40 minutes to execute the code above, and more often than not the code fails to execute raising the following error:
ConnectionError: ('Connection aborted.', OSError("(10060, 'WSAETIMEDOUT')",))
All in all, there could be three reasons for the error:
- It takes the BigQuery server a long time to execute the query
- It takes a long time to transfer data (I don't understand why a 350MB file should take 30min to be sent over the network. I tried using a LAN connection to eliminate server cuts and maximize throughput, which didn't help)
- It takes a long time to set a dataframe with the data from BigQuery
Would be happy to gain any insight into the problem, thanks in advance!
python dataframe
google-bigquery jupyter-notebook jupyter python dataframe
google-bigquery jupyter-notebook jupyter asked Nov 22 at 14:22
Max Sfnv
211
asked Nov 22 at 14:22
Max Sfnv
211
edited Nov 22 at 15:48
asked Nov 22 at 14:22
Max Sfnv
211
asked Nov 22 at 14:22
Max Sfnv
211
asked Nov 22 at 14:22
Max Sfnv
211
211
40 mins seems a lot indeed...maybe it's the http transport layer. I wonder if you can make two tests: first, what happens if you set a limit to 10000 rows for instance in your query? Is it still slow? Also, can you test first exporting this data to GCS gizzed and then bring it to host to finally read as dataframe, is it still slow?
– Willian Fuks
Nov 22 at 17:59
add a comment |
40 mins seems a lot indeed...maybe it's the http transport layer. I wonder if you can make two tests: first, what happens if you set a limit to 10000 rows for instance in your query? Is it still slow? Also, can you test first exporting this data to GCS gizzed and then bring it to host to finally read as dataframe, is it still slow?
– Willian Fuks
Nov 22 at 17:59
40 mins seems a lot indeed...maybe it's the http transport layer. I wonder if you can make two tests: first, what happens if you set a limit to 10000 rows for instance in your query? Is it still slow? Also, can you test first exporting this data to GCS gizzed and then bring it to host to finally read as dataframe, is it still slow?
– Willian Fuks
Nov 22 at 17:59
40 mins seems a lot indeed...maybe it's the http transport layer. I wonder if you can make two tests: first, what happens if you set a limit to 10000 rows for instance in your query? Is it still slow? Also, can you test first exporting this data to GCS gizzed and then bring it to host to finally read as dataframe, is it still slow?
– Willian Fuks
Nov 22 at 17:59
add a comment |
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%2f53432996%2ftakes-too-long-to-export-data-from-bigquery-into-jupyter-notebook%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
active
oldest
votes
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.
Some of your past answers have not been well-received, and you're in danger of being blocked from answering.
Please pay close attention to the following guidance:
- 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%2f53432996%2ftakes-too-long-to-export-data-from-bigquery-into-jupyter-notebook%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
40 mins seems a lot indeed...maybe it's the http transport layer. I wonder if you can make two tests: first, what happens if you set a limit to 10000 rows for instance in your query? Is it still slow? Also, can you test first exporting this data to GCS gizzed and then bring it to host to finally read as dataframe, is it still slow?
– Willian Fuks
Nov 22 at 17:59