How to query multiple partitions of a Firebase Analytics events table in Big Query
I am querying from the Firebase Analytics tables.
The table I am using has a partition for each day. Here are some examples of the various daily partitions:
arm_studio.analytics_177892322.events_20180628
rm_studio.analytics_177892322.events_20180627
arm_studio.analytics_177892322.events_20180629
Essentially I am asking how to query the .all (every partition) of the table. Ie the partition from every single date recorded. Would also be great to query n days ago.
I have gone through BQ documentation on partitioned tables and the regular syntax seems to not work as intended. I have also noticed that normal partitioned tables have just the date after the table name as opposed to ".events_[date]" as shown in the example above.
Currently I am just unioning every partition which is extremely long and obviously unscalable. Anyone have experience with querying these tables?
firebase google-bigquery firebase-analytics
edited Dec 29 '18 at 5:50
Frank van Puffelen
230k28377401
asked Dec 29 '18 at 0:22
codeisfuntodocodeisfuntodo
41
add a comment |
I am querying from the Firebase Analytics tables.
The table I am using has a partition for each day. Here are some examples of the various daily partitions:
arm_studio.analytics_177892322.events_20180628
rm_studio.analytics_177892322.events_20180627
arm_studio.analytics_177892322.events_20180629
Essentially I am asking how to query the .all (every partition) of the table. Ie the partition from every single date recorded. Would also be great to query n days ago.
I have gone through BQ documentation on partitioned tables and the regular syntax seems to not work as intended. I have also noticed that normal partitioned tables have just the date after the table name as opposed to ".events_[date]" as shown in the example above.
Currently I am just unioning every partition which is extremely long and obviously unscalable. Anyone have experience with querying these tables?
firebase google-bigquery firebase-analytics
edited Dec 29 '18 at 5:50
Frank van Puffelen
230k28377401
asked Dec 29 '18 at 0:22
codeisfuntodocodeisfuntodo
41
cloud.google.com/bigquery/docs/querying-wildcard-tables
– Elliott Brossard
Dec 29 '18 at 0:29
add a comment |
I am querying from the Firebase Analytics tables.
The table I am using has a partition for each day. Here are some examples of the various daily partitions:
arm_studio.analytics_177892322.events_20180628
rm_studio.analytics_177892322.events_20180627
arm_studio.analytics_177892322.events_20180629
Essentially I am asking how to query the .all (every partition) of the table. Ie the partition from every single date recorded. Would also be great to query n days ago.
I have gone through BQ documentation on partitioned tables and the regular syntax seems to not work as intended. I have also noticed that normal partitioned tables have just the date after the table name as opposed to ".events_[date]" as shown in the example above.
Currently I am just unioning every partition which is extremely long and obviously unscalable. Anyone have experience with querying these tables?
firebase google-bigquery firebase-analytics
edited Dec 29 '18 at 5:50
Frank van Puffelen
230k28377401
asked Dec 29 '18 at 0:22
codeisfuntodocodeisfuntodo
41
I am querying from the Firebase Analytics tables.
The table I am using has a partition for each day. Here are some examples of the various daily partitions:
arm_studio.analytics_177892322.events_20180628
rm_studio.analytics_177892322.events_20180627
arm_studio.analytics_177892322.events_20180629
Essentially I am asking how to query the .all (every partition) of the table. Ie the partition from every single date recorded. Would also be great to query n days ago.
I have gone through BQ documentation on partitioned tables and the regular syntax seems to not work as intended. I have also noticed that normal partitioned tables have just the date after the table name as opposed to ".events_[date]" as shown in the example above.
Currently I am just unioning every partition which is extremely long and obviously unscalable. Anyone have experience with querying these tables?
firebase google-bigquery firebase-analytics
firebase google-bigquery firebase-analytics
edited Dec 29 '18 at 5:50
Frank van Puffelen
230k28377401
asked Dec 29 '18 at 0:22
codeisfuntodocodeisfuntodo
41
edited Dec 29 '18 at 5:50
Frank van Puffelen
230k28377401
asked Dec 29 '18 at 0:22
codeisfuntodocodeisfuntodo
41
edited Dec 29 '18 at 5:50
Frank van Puffelen
230k28377401
edited Dec 29 '18 at 5:50
Frank van Puffelen
230k28377401
edited Dec 29 '18 at 5:50
Frank van Puffelen
230k28377401
230k28377401
asked Dec 29 '18 at 0:22
codeisfuntodocodeisfuntodo
41
asked Dec 29 '18 at 0:22
codeisfuntodocodeisfuntodo
41
asked Dec 29 '18 at 0:22
codeisfuntodocodeisfuntodo
41
41
cloud.google.com/bigquery/docs/querying-wildcard-tables
– Elliott Brossard
Dec 29 '18 at 0:29
add a comment |
cloud.google.com/bigquery/docs/querying-wildcard-tables
– Elliott Brossard
Dec 29 '18 at 0:29
cloud.google.com/bigquery/docs/querying-wildcard-tables
– Elliott Brossard
Dec 29 '18 at 0:29
cloud.google.com/bigquery/docs/querying-wildcard-tables
– Elliott Brossard
Dec 29 '18 at 0:29
add a comment |
2 Answers
2
active
oldest
votes
To query events for all days use a wildcard:
FROM arm_studio.analytics_177892322.events_*
To query events for a particular month or year use wildcards and leave the month or year prefix, e.g.:
FROM arm_studio.analytics_177892322.events_201806*
or
FROM arm_studio.analytics_177892322.events_2018*
To set an arbitrary partition filter use _TABLE_SUFFIX column:
FROM arm_studio.analytics_177892322.events_*
WHERE _TABLE_SUFFIX BETWEEN '20180627' and '20180630'
answered Dec 29 '18 at 7:34
medvedev1088medvedev1088
2,4751333
add a comment |
If possible for you I suggest using BigQuery new partition pseudo field _partitionTime or _partitionDate (You can also set your own custom partition field for more advanced scenarios)
These 2 links provide good details about how and why to use this:
Querying partitioned tables
Scanning a range of partitioned tables using _PARTITIONTIME.
answered Dec 29 '18 at 7:48
Tamir KleinTamir Klein
440516
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%2f53965670%2fhow-to-query-multiple-partitions-of-a-firebase-analytics-events-table-in-big-que%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
2 Answers
2
active
oldest
votes
2 Answers
2
active
oldest
votes
active
oldest
votes
active
oldest
votes
To query events for all days use a wildcard:
FROM arm_studio.analytics_177892322.events_*
To query events for a particular month or year use wildcards and leave the month or year prefix, e.g.:
FROM arm_studio.analytics_177892322.events_201806*
or
FROM arm_studio.analytics_177892322.events_2018*
To set an arbitrary partition filter use _TABLE_SUFFIX column:
FROM arm_studio.analytics_177892322.events_*
WHERE _TABLE_SUFFIX BETWEEN '20180627' and '20180630'
answered Dec 29 '18 at 7:34
medvedev1088medvedev1088
2,4751333
add a comment |
To query events for all days use a wildcard:
FROM arm_studio.analytics_177892322.events_*
To query events for a particular month or year use wildcards and leave the month or year prefix, e.g.:
FROM arm_studio.analytics_177892322.events_201806*
or
FROM arm_studio.analytics_177892322.events_2018*
To set an arbitrary partition filter use _TABLE_SUFFIX column:
FROM arm_studio.analytics_177892322.events_*
WHERE _TABLE_SUFFIX BETWEEN '20180627' and '20180630'
answered Dec 29 '18 at 7:34
medvedev1088medvedev1088
2,4751333
add a comment |
To query events for all days use a wildcard:
FROM arm_studio.analytics_177892322.events_*
To query events for a particular month or year use wildcards and leave the month or year prefix, e.g.:
FROM arm_studio.analytics_177892322.events_201806*
or
FROM arm_studio.analytics_177892322.events_2018*
To set an arbitrary partition filter use _TABLE_SUFFIX column:
FROM arm_studio.analytics_177892322.events_*
WHERE _TABLE_SUFFIX BETWEEN '20180627' and '20180630'
answered Dec 29 '18 at 7:34
medvedev1088medvedev1088
2,4751333
To query events for all days use a wildcard:
FROM arm_studio.analytics_177892322.events_*
To query events for a particular month or year use wildcards and leave the month or year prefix, e.g.:
FROM arm_studio.analytics_177892322.events_201806*
or
FROM arm_studio.analytics_177892322.events_2018*
To set an arbitrary partition filter use _TABLE_SUFFIX column:
FROM arm_studio.analytics_177892322.events_*
WHERE _TABLE_SUFFIX BETWEEN '20180627' and '20180630'
answered Dec 29 '18 at 7:34
medvedev1088medvedev1088
2,4751333
answered Dec 29 '18 at 7:34
medvedev1088medvedev1088
2,4751333
answered Dec 29 '18 at 7:34
medvedev1088medvedev1088
2,4751333
answered Dec 29 '18 at 7:34
medvedev1088medvedev1088
2,4751333
2,4751333
add a comment |
add a comment |
If possible for you I suggest using BigQuery new partition pseudo field _partitionTime or _partitionDate (You can also set your own custom partition field for more advanced scenarios)
These 2 links provide good details about how and why to use this:
Querying partitioned tables
Scanning a range of partitioned tables using _PARTITIONTIME.
answered Dec 29 '18 at 7:48
Tamir KleinTamir Klein
440516
add a comment |
If possible for you I suggest using BigQuery new partition pseudo field _partitionTime or _partitionDate (You can also set your own custom partition field for more advanced scenarios)
These 2 links provide good details about how and why to use this:
Querying partitioned tables
Scanning a range of partitioned tables using _PARTITIONTIME.
answered Dec 29 '18 at 7:48
Tamir KleinTamir Klein
440516
add a comment |
If possible for you I suggest using BigQuery new partition pseudo field _partitionTime or _partitionDate (You can also set your own custom partition field for more advanced scenarios)
These 2 links provide good details about how and why to use this:
Querying partitioned tables
Scanning a range of partitioned tables using _PARTITIONTIME.
answered Dec 29 '18 at 7:48
Tamir KleinTamir Klein
440516
If possible for you I suggest using BigQuery new partition pseudo field _partitionTime or _partitionDate (You can also set your own custom partition field for more advanced scenarios)
These 2 links provide good details about how and why to use this:
Querying partitioned tables
Scanning a range of partitioned tables using _PARTITIONTIME.
answered Dec 29 '18 at 7:48
Tamir KleinTamir Klein
440516
answered Dec 29 '18 at 7:48
Tamir KleinTamir Klein
440516
answered Dec 29 '18 at 7:48
Tamir KleinTamir Klein
440516
answered Dec 29 '18 at 7:48
Tamir KleinTamir Klein
440516
440516
add a comment |
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%2f53965670%2fhow-to-query-multiple-partitions-of-a-firebase-analytics-events-table-in-big-que%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
cloud.google.com/bigquery/docs/querying-wildcard-tables
– Elliott Brossard
Dec 29 '18 at 0:29