Questions related to using "Legacy SQL" for Google BigQuery
Questions tagged [legacy-sql]
78 questions
0
votes
1 answer
SQL grouping/counting on a string split function
Ok so my original is this
select people, count(*)
from table
group by people
but some of the people have multiple people so this aggregation will not give you pure counts for A, B, C but also each iteration
A 10
B 5
A, B 1
A, C 2
C 15
A, B, C…

J. G.
- 1,922
- 1
- 11
- 21
0
votes
0 answers
Is Standard SQL from GBQ comparable to Microsoft SQL and/or MySQL?
I know there are variations between different SQL types, but I'm trying to understand what is the current standard that everyone is moving to.
For that being said, If I know Standard SQL from GBQ, will I be able to use Microsoft SQL Server and/or…

cheklapkok
- 439
- 1
- 5
- 11
0
votes
1 answer
Bigquery query fails “Resources exceeded during query execution: Table metadata used for the query is too large”
I am hitting the error
"Resources exceeded during query execution: Table metadata used for
the query is too large"
when executing this query:
SELECT
EXACT_COUNT_DISTINCT(a.id)
FROM (
SELECT
id
FROM (TABLE_DATE_RANGE([*****],
…

Evan.Chen
- 69
- 1
- 6
0
votes
1 answer
Translation table_date_range to table_suffix does not work because response is too large
I have the following query in BigQuery Legacy SQL. This query runs without problems so far.
#legacySQL
SELECT
Var1 AS Var1,
Var2 AS Var2,
Var3 AS Var3,
Var4 AS Var4,
FROM
TABLE_DATE_RANGE([xxx.yyy_], DATE_ADD(CURRENT_TIMESTAMP(), -33,…

Pat
- 11
- 3
0
votes
1 answer
Google Big Query SPLIT() function using #standardsql
I am trying to use the SPLIT() function to convert an array of strings into individual rows.
The data looks something like this:
id ticket
1 1,2,3,4,5,6,7,8,9...etc.
2 11,12,13,14,15,16,17,18,19...etc.
3 …
0
votes
1 answer
Legacy UDF - Determine largest value in a dictionary/map
I have a JSON map/dictionary where I want to find the largest value in it, but I’m having issues writing a UDF (using Legacy SQL syntax). I couldn’t find many resources or examples of this online, but I did see many for Standard SQL. Could anyone…
user7281939
0
votes
1 answer
BigQuery GA Open Funnel Legacy SQL: Exclude Sessions that have viewed certain pages
I am trying to recreate GA funnel in BigQuery, this open funnel would exclude sessions that have viewed certain pages, I have tried using the following: AND NOT REGEXP_MATCH, NOT IN, but it still not working as I expect, I am still getting sessions…

rayhunt01
- 27
- 7
0
votes
1 answer
BigQuery create Table differences between standard and legacy sql
I have a few questions around the create table syntax in standard and legacy sql
The new BigQueryUI doesn't show standard sql types and shows only legacy types. I understand they are mapped one to one with the legacy types but the examples in…

PK109
- 53
- 8
0
votes
1 answer
Alternative to using dryRun to differentiate a Standard and Legacy SQL query in BigQuery?
The What
Does anyone know a better way of identifying/differentiating between a view or query written in Legacy or Standard SQL, using the BigQuery API?
The only method that came to mind was to run the SQL with the dryRun property set to true (this…

FreeZey
- 2,382
- 3
- 11
- 23
0
votes
0 answers
UPDATE/MERGE must match at most one source row for each target row Bigquery
trying to update table in bigquery but got an error message stating
"UPDATE/MERGE must match at most one source row for each target row"
update Data_Layer.ClassCategories cc
set cc.MovmentTypeId = t.MovementTypeId,
cc.IntensityId =…

Akki
- 59
- 1
- 7
0
votes
1 answer
Exporting custom dimensions from BigQuery
I'm trying to export a Google Anlaytics custom dimension on hit level and some events from BigQuery. So far, the custom dimension export isn't working.
I'm using LegacySQL on that. Cause this is part of a bigger Query, I don't want to switch to…

Julez2000
- 1
- 1
- 1
0
votes
1 answer
BigQuery - Exporting different Events from Google Analytics
Probably a beginner question, but I just couldn't find a soultion here.
I want to export different events (only the total events metric) in BigQuery from my google analytics account
So f.e. I have a download event and a search event, and I want to…

Julez2000
- 1
- 1
- 1
0
votes
3 answers
How can i split column and group by in bigquery?
I has a SQL code in Legacy SQL was worked,
but in Standard SQL was wrong,
got the response:
Grouping by expressions of type ARRAY is not allowed
Is there any way can resolve ?
Here's my SQL code:
select tag
from
(
select…

Allen
- 29
- 2
0
votes
1 answer
Extracting hour by using coalese in SQL on a timestamp
I am trying to update a query to extract the hour from a timestamp and I keep getting an error. The error I get is due to the FROM clause I was using.
SELECT
analytics_platform_data_type
, activity_date_pt
, activity_timestamp_pt
,…

desky
- 13
- 3
0
votes
1 answer
How to convert legacy SQL BigQuery to standard SQL?
I have been trying to convert a legacy SQL BigQuery code to Standard SQL, but I keep getting loads of errors.
Here is the original Legacy SQL:
SELECT t.page_path,
t.second_page_path,
t.third_page_path,
…

rayhunt01
- 27
- 7