Stack Overflow
Stack Overflow

Questions tagged [bigquery-udf]

BigQuery supports user-defined functions (UDFs). A UDF enables you to create a function using another SQL expression or JavaScript. These functions accept columns of input and perform actions, returning the result of those actions as a value.

128 questions
4
votes
0 answers

The query is too large. The maximum standard SQL query length is 1024.00K characters, including comments and white space characters

I am running this query in bigquery where I am selecting the data of mobile_ids which match in the array of strings. But when I increase the number of size of array of strings, it shows error: Error: The query is too large. The maximum standard SQL…
Divyanshu Sen
  • 71
  • 9
4
votes
1 answer

BigQuery Equivalent of Merge Statement

I am performing migration from teradata to Big query. I have encountered a merge statement having VALUES in USING clause. MERGE INTO department DL USING VALUES ( 2,'ABC' …
Dhirendra Gautam
  • 737
  • 8
  • 16
3
votes
1 answer

BigQuery UDF to remove accents/diacritics in a string

Using this javascript code we can remove accents/diacritics in a string. var originalText = "éàçèñ" var result = originalText.normalize('NFD').replace(/[\u0300-\u036f]/g, "") console.log(result) // eacen If we create a BigQuery UDF it does not…
Jason Tragakis
  • 83
  • 7
3
votes
1 answer

Does BigQuery support Analytic User-Defined Functions?

BigQuery supports: User Defined Functions (UDFs) in SQL and JavaScript. Analytic functions that compute values over a group of rows and return a single result for each row. These functions can be used with OVER clause. There is a predefined set of…
Oleg Lokshyn
  • 517
  • 7
  • 14
2
votes
1 answer

I am not able to create temporary table in Bigquery

got below error while trying to create temporary table in Bigquery . create or replace temporary table mss.Business.test2 as select * from mss.Business.registration Query error: Temporary tables may not be qualified at [2:36]
Nagendra
  • 171
  • 2
  • 12
2
votes
1 answer

BigQuery REST API - Unable to use functions

I'm trying to use the BigQuery REST API to execute some queries, but, for some reason, I can't use SQL functions. The endpoint I've been using is the following: POST https://bigquery.googleapis.com/bigquery/v2/projects/{project-id}/queries This…
João Azevedo
  • 29
  • 2
2
votes
2 answers

BigQuery - Picking latest not null value within 28 interval

I'm trying to add a column on this table and stuck for a little while ID Category…
minervatea
  • 27
  • 5
2
votes
0 answers

Why Big Query remote function do not activate more than 60 cloud function instances?

I've started to work with remote functions: https://cloud.google.com/bigquery/docs/reference/standard-sql/remote-functions I've been able to setup a cloud function & call it with BigQuery. But no more than 60 instances of this cloud function are…
RYegavian
  • 101
  • 6
2
votes
2 answers

Find overlapping time periods in BigQuery

Assume I have data structured as so in BigQuery: WITH session_log AS ( SELECT 'ABC' as site_id, 1234 user_id, 12 session_id, '2020-02-10 00:29:59.376000 UTC' start_time, '2020-02-10 01:13:02.817000 UTC' end_time UNION ALL SELECT 'ABC' as…
Rob E.
  • 194
  • 3
  • 17
2
votes
1 answer

Storing Variable in BigQuery SQL UDF

I am trying to create an SQL UDF in bigQuery to calculate week in month. I got the result that I'm expecting but my function looks super messy. create or replace function internal.week_in_month(my_date TIMESTAMP) returns FLOAT64 as ( case when …
Nadia Noversega
  • 59
  • 6
2
votes
1 answer

How to export a null field on BIGQUERY

When I try to export a JSON object through BigQuery, when there is a field with a "null" value, it disappears from the results' download. An example of downloaded query: EXPORT DATA OPTIONS( uri='gs://analytics-export/_*', format='JSON', …
Oded .S
  • 1,081
  • 2
  • 11
  • 18
2
votes
4 answers

BigQuery - unsupported subquery with table in join predicate

Recently I started to work on BigQuery and there's something that makes me still confused. What's the alternative for this query on Big Query? select a.abc, c.xyz from table1 as a left join table2 as c on a.abc = c.abc and c.date = (select t.date…
hushhush
  • 147
  • 2
  • 10
2
votes
1 answer

BigQuery scripting: running a SQL query based on each row included in a table column

My origin table includes url_id and SQL [standard sql, string] I would like to run each of the SQL statements in the string column "SQL" and join the outcome with the correspondent url_id expected outcome UPDATE to make this easy to reproduce I´ve…
Shahin Ghannadian
  • 275
  • 4
  • 13
2
votes
2 answers

JOIN data in event table with most recent data in changelog table before the time of the event

I'm looking to JOIN data from a change-log style table of users into a table of events which which have matching ids The tables are as follows: project_events schema timestamp TIMESTAMP event_id STRING user_id STRING data …
Scott
  • 1,575
  • 13
  • 17
2
votes
1 answer

Hitting Bigquery JS UDF limits with multiple rows

I'm having issues with BigQuery JS UDF limits. The only memory limit documented [1] is a approximately 5 MB or less limit to the amount of data output by the UDF so I've set up an internal check to return an error when the output buffer is over ~3MB…
Algunenano
  • 124
  • 4
1
2 3
…
8 9