Questions tagged [legacy-sql]

Questions related to using "Legacy SQL" for Google BigQuery

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