Using partitioning and clustering fields in Google BigQuery
I am working with some historic CRM data for multiple clients. I arrange my BigQuery schema to have the following format:
PARTITION_MONTH (DATE) | CLIENT_UUID (STR) | PDATE (Date) | ...
I make my partition field have month granularity via truncating PDATE to compensate for BQ's current 4k partition limit. In addition, I have CLIENT_UUID set as a clustered field to speed up client-specific queries. Doing some benchmarking, I noticed PARTITION_MONTH has no affect on how many bytes are scanned.
The client I tested with accounts for 37% of the records in my table. I have three tables (identical data) where one has partitioning/clustering, clustering with all partitions set to NULL, and no partitioning/clustering. A simple aggregate sum on the clustered tables show the CLIENT_UUID clustering is working:
SELECT SUM(amount) FROM dataset.table WHERE CLIENT_UUID='myclient'
Non-Partitioned: 513 MB processed
Clustered: 191 MB processed (~37% of non-partitioned)
Partitioned/Clustered: 191 MB processed
However, when I incorporate the date in the query, the partitioned field shows no performance gain:
SELECT SUM(amount) FROM dataset.table
WHERE CLIENT_UUID='myclient'
AND [PARTITION_MONTH/PDATE] > DATE(2000, 1, 1)
Non-Partitioned: 513 MB processed
Clustered: 191 MB processed (~37% of non-partitioned)
Partitioned/Clustered: 191 MB processed
I would assume underneath the hood, BigQuery would simply ignore all partitions prior to the specified date. However, it appears it's scanning all of the client's rows.
Can anyone explain this behavior? Is it a matter of BigQuery or perhaps the way I'm structuring my queries? Any help would be great, thanks.
database-design google-bigquery database-schema
asked Nov 13 '18 at 17:45
Frank MageFrank Mage
1
|
show 1 more comment
I am working with some historic CRM data for multiple clients. I arrange my BigQuery schema to have the following format:
PARTITION_MONTH (DATE) | CLIENT_UUID (STR) | PDATE (Date) | ...
I make my partition field have month granularity via truncating PDATE to compensate for BQ's current 4k partition limit. In addition, I have CLIENT_UUID set as a clustered field to speed up client-specific queries. Doing some benchmarking, I noticed PARTITION_MONTH has no affect on how many bytes are scanned.
The client I tested with accounts for 37% of the records in my table. I have three tables (identical data) where one has partitioning/clustering, clustering with all partitions set to NULL, and no partitioning/clustering. A simple aggregate sum on the clustered tables show the CLIENT_UUID clustering is working:
SELECT SUM(amount) FROM dataset.table WHERE CLIENT_UUID='myclient'
Non-Partitioned: 513 MB processed
Clustered: 191 MB processed (~37% of non-partitioned)
Partitioned/Clustered: 191 MB processed
However, when I incorporate the date in the query, the partitioned field shows no performance gain:
SELECT SUM(amount) FROM dataset.table
WHERE CLIENT_UUID='myclient'
AND [PARTITION_MONTH/PDATE] > DATE(2000, 1, 1)
Non-Partitioned: 513 MB processed
Clustered: 191 MB processed (~37% of non-partitioned)
Partitioned/Clustered: 191 MB processed
I would assume underneath the hood, BigQuery would simply ignore all partitions prior to the specified date. However, it appears it's scanning all of the client's rows.
Can anyone explain this behavior? Is it a matter of BigQuery or perhaps the way I'm structuring my queries? Any help would be great, thanks.
database-design google-bigquery database-schema
asked Nov 13 '18 at 17:45
Frank MageFrank Mage
1
Try this from manualExpress the predicate filter as closely as possible to the table identifier. Complex queries that require the evaluation of multiple stages of a query in order to resolve the predicate (such as inner queries or subqueries) will not prune partitions from the query.which means putting the partition filter first in the where clause.
– Pentium10
Nov 13 '18 at 18:20
Do you have data previous to the year 2000?
– Felipe Hoffa
Nov 13 '18 at 18:44
I do have data prior to 2000. Tried rearranging it so the date clause was first but still getting the same results.
– Frank Mage
Nov 15 '18 at 21:39
What is [PARTITION_MONTH/PDATE]? If you use some functions over the partitioning field then partition pruning might not work. We can look further if you can share the job id.
– Hua Zhang
Nov 19 '18 at 22:50
@HuaZhang They are both equivalent datetime fields with month granularity. PARTITION_MONTH being the partitioned field in the partition table, and PDATE being a non-partitioned field. There is no different in performance when using one or the other.
– Frank Mage
Nov 27 '18 at 21:38
|
show 1 more comment
I am working with some historic CRM data for multiple clients. I arrange my BigQuery schema to have the following format:
PARTITION_MONTH (DATE) | CLIENT_UUID (STR) | PDATE (Date) | ...
I make my partition field have month granularity via truncating PDATE to compensate for BQ's current 4k partition limit. In addition, I have CLIENT_UUID set as a clustered field to speed up client-specific queries. Doing some benchmarking, I noticed PARTITION_MONTH has no affect on how many bytes are scanned.
The client I tested with accounts for 37% of the records in my table. I have three tables (identical data) where one has partitioning/clustering, clustering with all partitions set to NULL, and no partitioning/clustering. A simple aggregate sum on the clustered tables show the CLIENT_UUID clustering is working:
SELECT SUM(amount) FROM dataset.table WHERE CLIENT_UUID='myclient'
Non-Partitioned: 513 MB processed
Clustered: 191 MB processed (~37% of non-partitioned)
Partitioned/Clustered: 191 MB processed
However, when I incorporate the date in the query, the partitioned field shows no performance gain:
SELECT SUM(amount) FROM dataset.table
WHERE CLIENT_UUID='myclient'
AND [PARTITION_MONTH/PDATE] > DATE(2000, 1, 1)
Non-Partitioned: 513 MB processed
Clustered: 191 MB processed (~37% of non-partitioned)
Partitioned/Clustered: 191 MB processed
I would assume underneath the hood, BigQuery would simply ignore all partitions prior to the specified date. However, it appears it's scanning all of the client's rows.
Can anyone explain this behavior? Is it a matter of BigQuery or perhaps the way I'm structuring my queries? Any help would be great, thanks.
database-design google-bigquery database-schema
asked Nov 13 '18 at 17:45
Frank MageFrank Mage
1
I am working with some historic CRM data for multiple clients. I arrange my BigQuery schema to have the following format:
PARTITION_MONTH (DATE) | CLIENT_UUID (STR) | PDATE (Date) | ...
I make my partition field have month granularity via truncating PDATE to compensate for BQ's current 4k partition limit. In addition, I have CLIENT_UUID set as a clustered field to speed up client-specific queries. Doing some benchmarking, I noticed PARTITION_MONTH has no affect on how many bytes are scanned.
The client I tested with accounts for 37% of the records in my table. I have three tables (identical data) where one has partitioning/clustering, clustering with all partitions set to NULL, and no partitioning/clustering. A simple aggregate sum on the clustered tables show the CLIENT_UUID clustering is working:
SELECT SUM(amount) FROM dataset.table WHERE CLIENT_UUID='myclient'
Non-Partitioned: 513 MB processed
Clustered: 191 MB processed (~37% of non-partitioned)
Partitioned/Clustered: 191 MB processed
However, when I incorporate the date in the query, the partitioned field shows no performance gain:
SELECT SUM(amount) FROM dataset.table
WHERE CLIENT_UUID='myclient'
AND [PARTITION_MONTH/PDATE] > DATE(2000, 1, 1)
Non-Partitioned: 513 MB processed
Clustered: 191 MB processed (~37% of non-partitioned)
Partitioned/Clustered: 191 MB processed
I would assume underneath the hood, BigQuery would simply ignore all partitions prior to the specified date. However, it appears it's scanning all of the client's rows.
Can anyone explain this behavior? Is it a matter of BigQuery or perhaps the way I'm structuring my queries? Any help would be great, thanks.
database-design google-bigquery database-schema
database-design google-bigquery database-schema
asked Nov 13 '18 at 17:45
Frank MageFrank Mage
1
asked Nov 13 '18 at 17:45
Frank MageFrank Mage
1
asked Nov 13 '18 at 17:45
Frank MageFrank Mage
1
asked Nov 13 '18 at 17:45
Frank MageFrank Mage
1
asked Nov 13 '18 at 17:45
Frank MageFrank Mage
1
1
Try this from manualExpress the predicate filter as closely as possible to the table identifier. Complex queries that require the evaluation of multiple stages of a query in order to resolve the predicate (such as inner queries or subqueries) will not prune partitions from the query.which means putting the partition filter first in the where clause.
– Pentium10
Nov 13 '18 at 18:20
Do you have data previous to the year 2000?
– Felipe Hoffa
Nov 13 '18 at 18:44
I do have data prior to 2000. Tried rearranging it so the date clause was first but still getting the same results.
– Frank Mage
Nov 15 '18 at 21:39
What is [PARTITION_MONTH/PDATE]? If you use some functions over the partitioning field then partition pruning might not work. We can look further if you can share the job id.
– Hua Zhang
Nov 19 '18 at 22:50
@HuaZhang They are both equivalent datetime fields with month granularity. PARTITION_MONTH being the partitioned field in the partition table, and PDATE being a non-partitioned field. There is no different in performance when using one or the other.
– Frank Mage
Nov 27 '18 at 21:38
|
show 1 more comment
Try this from manualExpress the predicate filter as closely as possible to the table identifier. Complex queries that require the evaluation of multiple stages of a query in order to resolve the predicate (such as inner queries or subqueries) will not prune partitions from the query.which means putting the partition filter first in the where clause.
– Pentium10
Nov 13 '18 at 18:20
Do you have data previous to the year 2000?
– Felipe Hoffa
Nov 13 '18 at 18:44
I do have data prior to 2000. Tried rearranging it so the date clause was first but still getting the same results.
– Frank Mage
Nov 15 '18 at 21:39
What is [PARTITION_MONTH/PDATE]? If you use some functions over the partitioning field then partition pruning might not work. We can look further if you can share the job id.
– Hua Zhang
Nov 19 '18 at 22:50
@HuaZhang They are both equivalent datetime fields with month granularity. PARTITION_MONTH being the partitioned field in the partition table, and PDATE being a non-partitioned field. There is no different in performance when using one or the other.
– Frank Mage
Nov 27 '18 at 21:38
Try this from manual
Express the predicate filter as closely as possible to the table identifier. Complex queries that require the evaluation of multiple stages of a query in order to resolve the predicate (such as inner queries or subqueries) will not prune partitions from the query. which means putting the partition filter first in the where clause.– Pentium10
Nov 13 '18 at 18:20
Try this from manual
Express the predicate filter as closely as possible to the table identifier. Complex queries that require the evaluation of multiple stages of a query in order to resolve the predicate (such as inner queries or subqueries) will not prune partitions from the query. which means putting the partition filter first in the where clause.– Pentium10
Nov 13 '18 at 18:20
Do you have data previous to the year 2000?
– Felipe Hoffa
Nov 13 '18 at 18:44
Do you have data previous to the year 2000?
– Felipe Hoffa
Nov 13 '18 at 18:44
I do have data prior to 2000. Tried rearranging it so the date clause was first but still getting the same results.
– Frank Mage
Nov 15 '18 at 21:39
I do have data prior to 2000. Tried rearranging it so the date clause was first but still getting the same results.
– Frank Mage
Nov 15 '18 at 21:39
What is [PARTITION_MONTH/PDATE]? If you use some functions over the partitioning field then partition pruning might not work. We can look further if you can share the job id.
– Hua Zhang
Nov 19 '18 at 22:50
What is [PARTITION_MONTH/PDATE]? If you use some functions over the partitioning field then partition pruning might not work. We can look further if you can share the job id.
– Hua Zhang
Nov 19 '18 at 22:50
@HuaZhang They are both equivalent datetime fields with month granularity. PARTITION_MONTH being the partitioned field in the partition table, and PDATE being a non-partitioned field. There is no different in performance when using one or the other.
– Frank Mage
Nov 27 '18 at 21:38
@HuaZhang They are both equivalent datetime fields with month granularity. PARTITION_MONTH being the partitioned field in the partition table, and PDATE being a non-partitioned field. There is no different in performance when using one or the other.
– Frank Mage
Nov 27 '18 at 21:38
|
show 1 more 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%2f53286769%2fusing-partitioning-and-clustering-fields-in-google-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%2f53286769%2fusing-partitioning-and-clustering-fields-in-google-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
Try this from manual
Express the predicate filter as closely as possible to the table identifier. Complex queries that require the evaluation of multiple stages of a query in order to resolve the predicate (such as inner queries or subqueries) will not prune partitions from the query.which means putting the partition filter first in the where clause.– Pentium10
Nov 13 '18 at 18:20
Do you have data previous to the year 2000?
– Felipe Hoffa
Nov 13 '18 at 18:44
I do have data prior to 2000. Tried rearranging it so the date clause was first but still getting the same results.
– Frank Mage
Nov 15 '18 at 21:39
What is [PARTITION_MONTH/PDATE]? If you use some functions over the partitioning field then partition pruning might not work. We can look further if you can share the job id.
– Hua Zhang
Nov 19 '18 at 22:50
@HuaZhang They are both equivalent datetime fields with month granularity. PARTITION_MONTH being the partitioned field in the partition table, and PDATE being a non-partitioned field. There is no different in performance when using one or the other.
– Frank Mage
Nov 27 '18 at 21:38