Questions tagged [google-bigquery]

Google BigQuery is a web service that lets you do interactive analysis of massive datasets—analyzing billions of rows in seconds. Scalable and easy to use, BigQuery lets developers and businesses tap into powerful data analytics on demand.

0
votes
1answer
12 views

Restructure create and delete events on rows into single table

Similar to this problem: Using start and end event logs to create a table/view containing spans between the times of each log. I have table that logs 'create' and 'delete' events for a resource that ...
0
votes
1answer
15 views

Retrieving executed query list in BigQuery via SQL

Is there a table similar to v$sql in Oracle where I can retrieve data associated with a particular query that was run in BigQuery using SQL?
0
votes
1answer
28 views

Merge Overlapping intervals in Bigquery

I have a bigquery table with following columns: user_id, unique_id, start_timestamp(UTC), end_timestamp unique_id is always unique, no repeating values. currently data is grouped by user_id. and then ...
0
votes
0answers
2 views

How to use the equivalent of postgres mode() within Google's Bigquery

Postgres has a useful mode() function for finding the most frequent value. Is there something equivalent within Google's Bigquery? How should be written a query like this in Bigquery? select count(*)...
0
votes
2answers
53 views

Select first and last row based on each user id mysql

I want to select every first and last event of every user id, if no first event exists,then just the last event instead . tried using partition over , but i am getting first 2 events instead. Input: ...
0
votes
0answers
30 views

Create atomic queries in BigQuery

I have one partitioned table with more than 2500 partitioned tables. So when I try to update schema for such table I get following error, Too many partitions produced by query, allowed 2000, query ...
0
votes
1answer
48 views

How to convert timestamp to date & time

I am currently working on bigquery(standard sql), where I have a timestamp field which I want to convert to date and time in 2 separate columns. I tried doing this select EXTRACT(DATE FROM timestamp) ...
0
votes
1answer
75 views

Select only those rows based on specific condition of user id

Below is an example of child's activity table. I only want to see all rows after 1st Tv activity for each user id. I tried grouping but it is not working for me Table ID Timestamp ...
2
votes
1answer
28 views

Can you use BigQuery to run on top of Bigtable

I need to run BigQuery on top of Bigtable live, not as an export. I have found the information stating it was in beta but only as an export function. I would like to run BigQuery against Bigtable data ...
0
votes
1answer
407 views

Counting uniques on a week by week basis

I've managed to find the unique # of session_ids within the first week of the the year with this query. How do I get each successive week's unique count up to the last week of June? SELECT COUNT (...
-2
votes
1answer
56 views

Which database is suitable for serving big timeseries metrics with rollups? [closed]

I have big mysql table containing daily metrics for large number of subjects. Here is the hypotetical schema: day DATE subject_id INT metric1 metric2 metric3 What I want is to find top X subjects (...
3
votes
1answer
175 views

Equivalent idioms in Oracle?

I was reading this article that is about Google BigQuery: Exploring a powerful SQL pattern: ARRAY_AGG, STRUCT and UNNEST They use a couple of functions that I am trying to figure out what the ...
1
vote
1answer
194 views

How to query for certain text in log messages from Google App Engine that are exported to BigQuery?

I have used the 'Create Export' in the Logging section of Google Cloud Platform to export log entries into Big Query for analysis Here is part of the structure of the table in BigQuery that logs the ...
0
votes
2answers
695 views

Google Big Query Invalid time zone: +0000

Getting the error Query Failed Error: Invalid time zone: +0000 Is there a way to ignore time zones or default everything to UCT This is my where condition WHERE TIMESTAMP(FORMAT_TIMESTAMP("%Y-%m-%...
3
votes
1answer
591 views

Reference outer variables in nested select in bigquery vs mysql

I run following request in MySQL and it works, but results in an error with bigquery standard SQL. $sql = <<<EOT SELECT ads.ad_id, (SELECT count(*) FROM `PRFXvisits` AS v ...
0
votes
1answer
594 views

Best way to automate the loading of data daily into google big query from SQL Server

Best way to load/copy/insert data from a SQL Server database into a table in Google Big Query on a daily schedule. I.E data from a table in a SQL Server database to a table in Google Big Query I ...
2
votes
2answers
469 views

How we can do full text search and facets stuffs on BigQuery?

In the future, we will have millions ofs record in our web product. So we had used BigQuery for data storage and analysis. We have to build filters on our search page like Flipkart and Amazon provide ...
0
votes
1answer
233 views

Importing CouchBase dump to BigQuery

After creating a CouchBase dump and copying it to a Google Storage bucket I tried importing the data to BigQuery. I failed miserably, apparently the design.json or the files that appear in the same ...
3
votes
1answer
372 views

Replacing an aggregation system of statistics

We currently have a database under MySQL, storing aggregated statistics in different tables (recent hours, hours, days, months). The tables are updated by workers running at different rates depending ...
0
votes
1answer
39 views

How to obtain the most recent row per type and perform calculations, depending on the row type?

I posted the same question on SO, but thought I might give it a go here as well, as any other kind of optimization and advice is greatly appreciated :) Anyway, here is my post, word for word: I need ...
1
vote
1answer
1k views

Using DISTINCT and COUNT in a query

I'm having issues using DISTINCT and COUNT in the same query. I would like to get unique items then get a total count for each month. (I've done this in two queries but I know there's a better way.) ...
0
votes
2answers
3k views

Counting unique records across 2 columns and computing a score based on boolean values

I have a table that looks like this in Google Big Query: custID InteractionDate Purchased? Sales 1 20150312 F 0 1 20150312 T 200 1 20150314 ...
2
votes
0answers
229 views

Alternatives for storing 365TB of info

I need to upload daily CSV dumps to a blackbox of my choice, and it should be possible to run queries or create filters over the uploaded data. Daily dumps weight around 1TB and are basically id, ...
1
vote
1answer
106 views

Aggregating two queries and returning a COUNT of some permutations

Hopefully the title can make sense to somebody, so I can get somebody working with a database (BigQuery in my case) to look into into. Given two quarters, I need to find out the # of orders placed by ...