Questions related to using "Legacy SQL" for Google BigQuery
Questions tagged [legacy-sql]
78 questions
0
votes
1 answer
Encountered " "MERGE" "MERGE "" at line 1, column 1. Was expecting:
We must need to use Legacy SQL in BigQuery. But, Merge is not working in Legacy SQL. How we write below query in Legacy SQL?
MERGE [ABC:xyz.tmp_cards] AS target_tbl
USING [ABC:xyz.tmp_cards_1533188902] AS source_tbl
ON target_tbl.id =…
Jimesh Gajera
- 612
- 1
- 11
- 32
0
votes
2 answers
Big query replace a letter in a string by position
I would to like to replace a letter in a string by position in big query.
for example XXXXX, YYYYY, ZZZZZ the 5th letter in the string to 0
I've tried to use the Stuff function, but big query doesn't find the function
Stuff(XXXXX, 5, 1, '0')
Shany H.
- 113
- 1
- 6
0
votes
2 answers
Google Big Query charges for querying full table if subquery used
I have a partitioned table and am trying to limit my search to a few partitions. To do this I am running a query (using legacy SQL) that looks like the following:
SELECT
*
FROM
[project:dataset.table]
WHERE
_PARTITIONTIME >= "2018-07-10…
hamdog
- 991
- 2
- 10
- 24
0
votes
2 answers
bigquery, is a "subtable" possible?
In bigquery using legacy sql I have created a monstrous query that returns the following display of visits per day for a site that I released 2018-02-26:
Row date name release_date visits_count
1 20180226 a_name 20180226 2179
2…
theresemoreau
- 109
- 2
- 8
0
votes
1 answer
Bigquery SQL: Convert table values using a key-value table
I have two tables.
The first table contains the key-value data:
Answer_Code |Fruit
1 |Apple
2 |Orange
3 |Pear
4 |Watermelon
The second table contains the data collected
Participant|Fruit
…
cimmeria luz
- 13
- 4
0
votes
1 answer
BiqQuery Error: Cannot join on repeated field 'customDimensions.value'
I have the following SQL statement, in short, table contains a list of ids that I need to get the website behavior for the past year. These ids should match customDimensions.value
SELECT fullVisitorId, visitNumber, totals.pageviews,…
GRS
- 2,807
- 4
- 34
- 72
0
votes
1 answer
BigQuery SQL: Cannot query the cross product of repeated fields /
My goal is to filter from all visitors, to only analyse customers (which are in the customDimension.index =2 and then further filter only specific types of pageviews for the customers.
SELECT customDimensions.value AS…
GRS
- 2,807
- 4
- 34
- 72
0
votes
1 answer
Filtering sessions by hit-scoped custom dimensions in BigQuery
I am working with GA data in BigQuery (ga360 export).
I have a GA Resource working on a lot of domains. Each has its own Custom Dimension (hit-level scope, index=9) with a value of project name: project1, project2, etc (several domains could be…
Greenleaf
- 3
- 1
0
votes
1 answer
NTH in Legacy SQL in BigQuery doesn't work as expected
I have this query written in Legacy SQL:
select
nth(1, a) first_a,
nth(1, b) first_b
from (
select *
from
(select 12 a, null b),
(select null a, 54 b)
)
As a result I was expecting one row with values (12, null),…
Luka
- 2,779
- 3
- 17
- 32
0
votes
1 answer
How to paginate results in Legacy SQL
We are using Legacy SQL on a specific request. We can't use standard SQL for some internal reasons.
We would like to paginate our results, because we have a lots of rows. Like that :
SELECT ... FROM ... LIMIT 10000 30000 // In standard SQL
But in…
jeremieca
- 1,156
- 2
- 13
- 38
0
votes
1 answer
BigQuery - Legacy SQL: If today is monday lookback 3 if not lookback 1
Using BigQuery and Legacy SQL.
I am trying to modify my WHERE statement to look back 3 days if current date is a Monday, otherwise look back 1.
I cannot figure out how to do this.
Currently I am just looking to see if the date is current date…
Toddbg
- 1
- 1
0
votes
0 answers
Difference between outputs of standard and legacy SQL in Google BigQuery
I have a query written in legacy SQL mode which I want to rewrite into StandardSQL. It has a few inner joins, and standardSQL has a few places where I have to unnest data using UNNEST functions (arrays of product IDs inside a row of transactionID).…
Azamat
- 25
- 7
0
votes
1 answer
BigQuery documentation - Why does flatten remove repetition
I'm learning BigQuery standard SQL at the moment.
I've followed the examples of how you flatten data with legacy SQL, here and here.
It makes sense that flattening data generates stacked data with similar rows.
However, in the documentation which…
Dominic Woodman
- 719
- 2
- 8
- 18
0
votes
1 answer
Count users from multiple tables in the same dataset
I have the following four data tables in the same dataset at Google Bigquery:
I need to count users from these four tables, and organize the information into a table like this:
The following query returns the :. path…
Mario M.
- 802
- 11
- 26
0
votes
1 answer
automatic way to write monthly queries without specifying init and end date?
I need to automate the date range of my monthly queries using Legacy SQL language.
With this code, Google Bigquery doesn't storage data tables from start to the end of the month (1st May to 31th May).
FROM TABLE_DATE_RANGE([99999999.ga_sessions_],
…
Mario M.
- 802
- 11
- 26