BigQuery: Can a query job run on a table while another query job manipulates the table?
I update a table in regular intervals by overwriting the whole table. However, I also run SELECT queries on that table often. My question is what if I am currently running the query overwriting the table, and I also attempt to run a SELECT query on the table while it is being updated/overwritten? What about the other way around, I am SELECTing from that table but the regular job overwriting the table starts at the same time. Is there any point in time when the table cannot be read/only partially read, or where the table cannot be overwritten? I need to be 100% sure that the SELECT query will always work correctly.
google-bigquery
asked Nov 13 '18 at 6:43
emleml
204
add a comment |
I update a table in regular intervals by overwriting the whole table. However, I also run SELECT queries on that table often. My question is what if I am currently running the query overwriting the table, and I also attempt to run a SELECT query on the table while it is being updated/overwritten? What about the other way around, I am SELECTing from that table but the regular job overwriting the table starts at the same time. Is there any point in time when the table cannot be read/only partially read, or where the table cannot be overwritten? I need to be 100% sure that the SELECT query will always work correctly.
google-bigquery
asked Nov 13 '18 at 6:43
emleml
204
add a comment |
I update a table in regular intervals by overwriting the whole table. However, I also run SELECT queries on that table often. My question is what if I am currently running the query overwriting the table, and I also attempt to run a SELECT query on the table while it is being updated/overwritten? What about the other way around, I am SELECTing from that table but the regular job overwriting the table starts at the same time. Is there any point in time when the table cannot be read/only partially read, or where the table cannot be overwritten? I need to be 100% sure that the SELECT query will always work correctly.
google-bigquery
asked Nov 13 '18 at 6:43
emleml
204
I update a table in regular intervals by overwriting the whole table. However, I also run SELECT queries on that table often. My question is what if I am currently running the query overwriting the table, and I also attempt to run a SELECT query on the table while it is being updated/overwritten? What about the other way around, I am SELECTing from that table but the regular job overwriting the table starts at the same time. Is there any point in time when the table cannot be read/only partially read, or where the table cannot be overwritten? I need to be 100% sure that the SELECT query will always work correctly.
google-bigquery
google-bigquery
asked Nov 13 '18 at 6:43
emleml
204
asked Nov 13 '18 at 6:43
emleml
204
asked Nov 13 '18 at 6:43
emleml
204
asked Nov 13 '18 at 6:43
emleml
204
asked Nov 13 '18 at 6:43
emleml
204
204
add a comment |
add a comment |
1 Answer
1
active
oldest
votes
You can always run a SELECT statement over a table, and it will never reflect a partially-modified state. SELECTing from a table does not prevent running queries that modify it, either. If you use DML to modify a table, note the limitations from the documentation:
Only the following combinations of DML statements are allowed to run concurrently on a table:
- UPDATE and INSERT
- DELETE and INSERT
- INSERT and INSERT
Otherwise, one of the DML statements will be aborted. For example, if two UPDATE statements execute simultaneously against the table then only one of them will succeed.
answered Nov 13 '18 at 7:04
Elliott BrossardElliott Brossard
16.4k21035
Thank you. Just to clarify, if I start the SELECT statement before starting to overwrite the table, it will SELECT from the 'old' table? And if I start the SELECT statement while the table is in the process of being overwritten, it will also SELECT from the 'old' table?
– eml
Nov 13 '18 at 11:56
Yes, that's right.
– Elliott Brossard
Nov 13 '18 at 12:05
add a comment |
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%2f53275241%2fbigquery-can-a-query-job-run-on-a-table-while-another-query-job-manipulates-the%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
You can always run a SELECT statement over a table, and it will never reflect a partially-modified state. SELECTing from a table does not prevent running queries that modify it, either. If you use DML to modify a table, note the limitations from the documentation:
Only the following combinations of DML statements are allowed to run concurrently on a table:
- UPDATE and INSERT
- DELETE and INSERT
- INSERT and INSERT
Otherwise, one of the DML statements will be aborted. For example, if two UPDATE statements execute simultaneously against the table then only one of them will succeed.
answered Nov 13 '18 at 7:04
Elliott BrossardElliott Brossard
16.4k21035
Thank you. Just to clarify, if I start the SELECT statement before starting to overwrite the table, it will SELECT from the 'old' table? And if I start the SELECT statement while the table is in the process of being overwritten, it will also SELECT from the 'old' table?
– eml
Nov 13 '18 at 11:56
Yes, that's right.
– Elliott Brossard
Nov 13 '18 at 12:05
add a comment |
You can always run a SELECT statement over a table, and it will never reflect a partially-modified state. SELECTing from a table does not prevent running queries that modify it, either. If you use DML to modify a table, note the limitations from the documentation:
Only the following combinations of DML statements are allowed to run concurrently on a table:
- UPDATE and INSERT
- DELETE and INSERT
- INSERT and INSERT
Otherwise, one of the DML statements will be aborted. For example, if two UPDATE statements execute simultaneously against the table then only one of them will succeed.
answered Nov 13 '18 at 7:04
Elliott BrossardElliott Brossard
16.4k21035
Thank you. Just to clarify, if I start the SELECT statement before starting to overwrite the table, it will SELECT from the 'old' table? And if I start the SELECT statement while the table is in the process of being overwritten, it will also SELECT from the 'old' table?
– eml
Nov 13 '18 at 11:56
Yes, that's right.
– Elliott Brossard
Nov 13 '18 at 12:05
add a comment |
You can always run a SELECT statement over a table, and it will never reflect a partially-modified state. SELECTing from a table does not prevent running queries that modify it, either. If you use DML to modify a table, note the limitations from the documentation:
Only the following combinations of DML statements are allowed to run concurrently on a table:
- UPDATE and INSERT
- DELETE and INSERT
- INSERT and INSERT
Otherwise, one of the DML statements will be aborted. For example, if two UPDATE statements execute simultaneously against the table then only one of them will succeed.
answered Nov 13 '18 at 7:04
Elliott BrossardElliott Brossard
16.4k21035
You can always run a SELECT statement over a table, and it will never reflect a partially-modified state. SELECTing from a table does not prevent running queries that modify it, either. If you use DML to modify a table, note the limitations from the documentation:
Only the following combinations of DML statements are allowed to run concurrently on a table:
- UPDATE and INSERT
- DELETE and INSERT
- INSERT and INSERT
Otherwise, one of the DML statements will be aborted. For example, if two UPDATE statements execute simultaneously against the table then only one of them will succeed.
answered Nov 13 '18 at 7:04
Elliott BrossardElliott Brossard
16.4k21035
answered Nov 13 '18 at 7:04
Elliott BrossardElliott Brossard
16.4k21035
answered Nov 13 '18 at 7:04
Elliott BrossardElliott Brossard
16.4k21035
answered Nov 13 '18 at 7:04
Elliott BrossardElliott Brossard
16.4k21035
16.4k21035
Thank you. Just to clarify, if I start the SELECT statement before starting to overwrite the table, it will SELECT from the 'old' table? And if I start the SELECT statement while the table is in the process of being overwritten, it will also SELECT from the 'old' table?
– eml
Nov 13 '18 at 11:56
Yes, that's right.
– Elliott Brossard
Nov 13 '18 at 12:05
add a comment |
Thank you. Just to clarify, if I start the SELECT statement before starting to overwrite the table, it will SELECT from the 'old' table? And if I start the SELECT statement while the table is in the process of being overwritten, it will also SELECT from the 'old' table?
– eml
Nov 13 '18 at 11:56
Yes, that's right.
– Elliott Brossard
Nov 13 '18 at 12:05
Thank you. Just to clarify, if I start the SELECT statement before starting to overwrite the table, it will SELECT from the 'old' table? And if I start the SELECT statement while the table is in the process of being overwritten, it will also SELECT from the 'old' table?
– eml
Nov 13 '18 at 11:56
Thank you. Just to clarify, if I start the SELECT statement before starting to overwrite the table, it will SELECT from the 'old' table? And if I start the SELECT statement while the table is in the process of being overwritten, it will also SELECT from the 'old' table?
– eml
Nov 13 '18 at 11:56
Yes, that's right.
– Elliott Brossard
Nov 13 '18 at 12:05
Yes, that's right.
– Elliott Brossard
Nov 13 '18 at 12:05
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%2f53275241%2fbigquery-can-a-query-job-run-on-a-table-while-another-query-job-manipulates-the%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
