unytics / bigfunctions Goto Github PK

Takes a json_string as input which has flat (no nested) key values and returns an array<struct<key string, value string>>

Check the idea has not already been suggested

• I could not find my idea in existing issues

Edit the title above with self-explanatory function name and argument names

• The function name and the argument names I entered in the title above seems self explanatory to me.

BigFunction Description as it would appear in the documentation

Extract part of xml using xpath.

Inspired from https://github.com/stankiewicz/bigquery-xml-parser-udf

Examples of (arguments, expected output) as they would appear in the documentation

xml_extract("<customer><name>Paul</name></customer>", "/customer/name")
--> Paul

Check the idea has not already been suggested

• I could not find my idea in existing issues

Edit the title above with self-explanatory function name and argument names

• The function name and the argument names I entered in the title above seems self explanatory to me.

BigFunction Description as it would appear in the documentation

Use https://faker.readthedocs.io/ to generate fake data.

Code should look something like:

import json
from faker import Faker
fake = Faker(localization)
kwargs = json.loads(options)
return getattr(fake, what)(**kwargs)

Examples of (arguments, expected output) as they would appear in the documentation

• what='name', localization='it_IT', options=null --> Elda Palumbo

Check the bug has not already been reported

• I could not find the bug in existing issues

Edit the short error description in title above

• I wrote a clear short error description in the title above

What happened and what did you expect?

quotas or cloud run instance config should be configurable

Let's talk on bigfunctions slack to get a solution

use google maps api

Then write how to call the function here: https://stackoverflow.com/questions/70425871/use-geocoding-service-in-bigquery-udfs

Check the idea has not already been suggested

• I could not find my idea in existing issues

Edit the title above with self-explanatory function name and argument names

• The function name and the argument names I entered in the title above seems self explanatory to me.

BigFunction Description as it would appear in the documentation

Returns NULL after input time (ms)

Examples of (arguments, expected output) as they would appear in the documentation

• sleep(1000) --> Returns NULL after 1 seconds
• sleep(60000) --> Returns NULL after 60 seconds

show table differences (schema diff, rows in 1 but not in 2 and vice versa) and return result as a html report

I'm thinking about queries like these ones I've contributed to my pet Super Store project.

WDYT?

Check the idea has not already been suggested

• I could not find my idea in existing issues

Edit the title above with self-explanatory function name and argument names

• The function name and the argument names I entered in the title above seems self explanatory to me.

BigFunction Description as it would appear in the documentation

Find shortest path between two points.

Inspired from https://github.com/francois-baptiste/bigquery-routing

Examples of (arguments, expected output) as they would appear in the documentation

see with Francois Baptiste?

Check your idea has not already been reported

• I could not find the bug in existing issues

Edit the title above

• I wrote clear short description of my idea in the title above

Tell us everything

the function should deployed in a test dataset and examples should be tested against the deployed function

Check the idea has not already been suggested

• I could not find my idea in existing issues

Edit the title above with self-explanatory function name and argument names

• The function name and the argument names I entered in the title above seems self explanatory to me.

BigFunction Description as it would appear in the documentation

Return value at key key from key_value_items array

Examples of (arguments, expected output) as they would appear in the documentation

• get_value([struct("k" as key, 1 as value)], "k") --> 1
• get_value([struct("k" as key, 1 as value)], "a") --> null

Check your idea has not already been reported

• I could not find the bug in existing issues

Edit the title above

• I wrote clear short description of my idea in the title above

Tell us everything

add command lint to lower case the types of arguments, function name, etc
and lint the code using sqlfluff for sql and black for python

Check the idea has not already been suggested

• I could not find my idea in existing issues

Edit the title above with self-explanatory function name and argument names

• The function name and the argument names I entered in the title above seems self explanatory to me.

BigFunction Description as it would appear in the documentation

get inspired from

    execute immediate 'create or replace temp table tables as (select table_name as name from `' || dataset || '`.INFORMATION_SCHEMA.TABLES where table_type != "VIEW")';
    execute immediate 'create or replace temp table views as (select table_name as name from `' || dataset || '`.INFORMATION_SCHEMA.TABLES where table_type = "VIEW")';
    execute immediate 'create or replace temp table routines as (select routine_name, routine_type from `' || dataset || '`.INFORMATION_SCHEMA.ROUTINES)';

    for record in (select * from tables) do
        execute immediate 'drop table `' || dataset || '.' || record.name || '`';
    end for;

    for record in (select * from views) do
        execute immediate 'drop view `' || dataset || '.' || record.name || '`';
    end for;

    for record in (select * from routines) do
        execute immediate 'drop ' || record.routine_type || ' `' || dataset || '.' || record.routine_name || '`';
    end for;

execute immediate "drop schema " || dataset;

Examples of (arguments, expected output) as they would appear in the documentation

• my_dataset

Check the idea has not already been suggested

• I could not find my idea in existing issues

Edit the title above with self-explanatory function name and argument names

• The function name and the argument names I entered in the title above seems self explanatory to me.

BigFunction Description as it would appear in the documentation

wrap https://github.com/jmespath/jmespath.js

Examples of (arguments, expected output) as they would appear in the documentation

1. json_query('{"foo": [{"first": "a", "last": "b"}, {"first": "c", "last": "d"}]}', "foo[*].first")
   --> [ 'a', 'c' ]

json_query(
    '{"foo": [{"age": 20}, {"age": 25}, {"age": 30}, {"age": 35}, {"age": 40}]}',
    "foo[?age > `30`]"
)

--> [ { age: 35 }, { age: 40 } ]

return percentile of array with percentile a float in [0, 1].

Get inspired by https://unytics.io/bigfunctions/reference/#median_value

Upsert the result of a query in a table based on an id column and a timestamp column

Check the idea has not already been suggested

• I could not find my idea in existing issues

Edit the title above with self-explanatory function name and argument names

• The function name and the argument names I entered in the title above seems self explanatory to me.

BigFunction Description as it would appear in the documentation

Interprets unix_date_time as the number of date_time_part since 1970-01-01 00:00:00 UTC. Truncates higher levels of precision by rounding down to the beginning of the date_time_part.

Examples of (arguments, expected output) as they would appear in the documentation

select bigfunctions.us.timestamp_from_unix_date_time(31, "YEAR") as year_from_unix
+--------------------------+
|  year_from_unix          |
+--------------------------+
|  2001-01-01 00:00:00 UTC |
+--------------------------+

Check your idea has not already been reported

• I could not find the bug in existing issues

Edit the title above

• I wrote clear short description of my idea in the title above

Tell us everything

Let's talk on bigfunctions slack to get a solution

Check the bug has not already been reported

• I could not find the bug in existing issues

Edit function_name and the short error description in title above

• I wrote the correct function name and a short error description in the title above

What happened and what did you expect?

No matter what table I pass into this procedure, it shows me this error.

As you see this bigfunction_result variable is used without its declaration.

Undelete a table

https://stackoverflow.com/questions/27537720/how-can-i-undelete-a-bigquery-table

Check the idea has not already been suggested

• I could not find my idea in existing issues

Edit the title above with self-explanatory function name and argument names

• The function name and the argument names I entered in the title above seems self explanatory to me.

BigFunction Description as it would appear in the documentation

Redacting sensitive data from text

https://cloud.google.com/dlp/docs/redacting-sensitive-data#example_text_redaction

Examples of (arguments, expected output) as they would appear in the documentation

Check the idea has not already been suggested

• I could not find my idea in existing issues

Edit the title above with self-explanatory function name and argument names

• The function name and the argument names I entered in the title above seems self explanatory to me.

BigFunction Description as it would appear in the documentation

Returns the number of date_time_part since 1970-01-01 00:00:00 UTC. Truncates higher levels of precision by rounding down to the beginning of the date_time_part.

Examples of (arguments, expected output) as they would appear in the documentation

select bigfunctions.us.timestamp_to_unix_date_time(timestamp("2001-01-01"), "YEAR") as unix_year

+------------+
|  unix_year |
+------------+
|  31        |
+------------+

returns the table but with rows deduplicated

Check the idea has not already been suggested

• I could not find my idea in existing issues

Edit the title above with self-explanatory function name and argument names

• The function name and the argument names I entered in the title above seems self explanatory to me.

BigFunction Description as it would appear in the documentation

Return temporal anomalies of metric column in table

Get inspired by https://github.com/cuebook/CueObserve

Examples of (arguments, expected output) as they would appear in the documentation

a public table with anomalies --> detected anomalies

Check the idea has not already been suggested

• I could not find my idea in existing issues

Edit the title above with self-explanatory function name and argument names

• The function name and the argument names I entered in the title above seems self explanatory to me.

BigFunction Description as it would appear in the documentation

the function array_intersect returns the intersection of two arrays.

Examples of (arguments, expected output) as they would appear in the documentation

array_intersect([1, 2, 3], [2, 6, 7]) -> [2]

Check the bug has not already been reported

• I could not find the bug in existing issues

Edit function_name and the short error description in title above

• I wrote the correct function name and a short error description in the title above

What happened and what did you expect?

The function remove_strings does not work with some strings :
When used to remove brackets, it does not do it.

To be precise, here is what happens :
select bigfunctions.eu.remove_strings('test_-test test()', ['_', '-',' ','(',')']) as cleaned_string
will result in :
testtesttest()
And
select bigfunctions.eu.remove_strings('test_-test test()', ['(',')','_', '-',' ']) as cleaned_string
will result in :
test_-test test()

Check your idea has not already been reported

• I could not find the bug in existing issues

Edit the title above

• I wrote clear short description of my idea in the title above

Tell us everything

with github actions.
deploy functions in a temporary dataset to check the deployment is working

Check the bug has not already been reported

• I could not find the bug in existing issues

Edit function_name and the short error description in title above

• I wrote the correct function name and a short error description in the title above

What happened and what did you expect?

When I created a new procedure and deployed it into my project. I see there is an insert statement that is concatenated in each new procedure, but my procedure is unable to find the logs table.

Link: https://stackoverflow.com/a/43148949/1986681

When default GCP project is not set in gcloud config, use GCP project given as input of bigfun deploy (or the GCP project used at bigfun initialization when it is not provided when calling bigfun deploy).

Check the idea has not already been suggested

• I could not find my idea in existing issues

Edit the title above with self-explanatory function name and argument names

• The function name and the argument names I entered in the title above seems self explanatory to me.

BigFunction Description as it would appear in the documentation

duplicate send_slack_message.yaml.
Change doc and assertion in code.

Teams incoming webhook doc:
https://learn.microsoft.com/en-us/microsoftteams/platform/webhooks-and-connectors/how-to/add-incoming-webhook?tabs=javascript

Examples of (arguments, expected output) as they would appear in the documentation

Check the idea has not already been suggested

• I could not find my idea in existing issues

Edit the title above with self-explanatory function name and argument names

• The function name and the argument names I entered in the title above seems self explanatory to me.

BigFunction Description as it would appear in the documentation

Objective: simplify the following:
https://developers.google.com/analytics/bigquery/basic-queries#values_for_a_specific_event_name

Alejandro Zielinsky proposed the following.

• https://zielinsky.alejand.ro/querying-ga4-event-parameters-in-bigquery-with-function-9f75eb0a2f89
• https://gist.github.com/ZordnajelA/b3c519559a1524d420237c63d5228bc5

From my point of view, we could return the value directly and not a struct with type and value.

Examples of (arguments, expected output) as they would appear in the documentation

Check the idea has not already been suggested

• I could not find my idea in existing issues

Edit the title above with self-explanatory function name and argument names

• The function name and the argument names I entered in the title above seems self explanatory to me.

BigFunction Description as it would appear in the documentation

Send ˋmessageˋ to google chat channel using incoming webhook ˋurl`

see documentation here: https://developers.google.com/chat/how-tos/webhooks

Examples of (arguments, expected output) as they would appear in the documentation

Check the idea has not already been suggested

• I could not find my idea in existing issues

Edit the title above with self-explanatory function name and argument names

• The function name and the argument names I entered in the title above seems self explanatory to me.

BigFunction Description as it would appear in the documentation

Examples of (arguments, expected output) as they would appear in the documentation

array_concat([1], [2, 3]) --> [1, 2, 3]

Check the idea has not already been suggested

• I could not find my idea in existing issues

Edit the title above with self-explanatory function name and argument names

• The function name and the argument names I entered in the title above seems self explanatory to me.

BigFunction Description as it would appear in the documentation

inpired from https://github.com/djdhyun/bigquery-typescript-udf

Examples of (arguments, expected output) as they would appear in the documentation

json_merge('{"k1": "v1"}', '{"k2": "v2"}')
--> {"k1":"v1","k2":"v2"}

return the date (or integer) of the latest partition.

Check the idea has not already been suggested

• I could not find my idea in existing issues

Edit the title above with self-explanatory function name and argument names

• The function name and the argument names I entered in the title above seems self explanatory to me.

BigFunction Description as it would appear in the documentation

Get table row count for the last 7 days (using time travelling feature of BigQuery) and check that current row count is not unusual compared to yesterday row count and regarding the evolution of the 7 latest days

Examples of (arguments, expected output) as they would appear in the documentation

one_public_table_name --> false

Check the idea has not already been suggested

• I could not find my idea in existing issues

Edit the title above with self-explanatory function name and argument names

• The function name and the argument names I entered in the title above seems self explanatory to me.

BigFunction Description as it would appear in the documentation

returns array of detected PII data with confidence

Examples of (arguments, expected output) as they would appear in the documentation

text="Write to [email protected] for any question"
--> ([email protected], email, confidence=0.999)

(inspired from https://github.com/GoogleCloudPlatform/bigquery-dlp-remote-function)

Check the idea has not already been suggested

• I could not find my idea in existing issues

Edit the title above with self-explanatory function name and argument names

• The function name and the argument names I entered in the title above seems self explanatory to me.

BigFunction Description as it would appear in the documentation

convert xml to json string using a js library

Inspired from https://github.com/salrashid123/bq-udf-xml

Examples of (arguments, expected output) as they would appear in the documentation

<a><b>foo</b></a> --> {"a": {"b": "foo"}}

Hi everyone!

Here a few cases, I've tested:

• *@hotmail.fr => TRUE
• [email protected] => TRUE
• ***[email protected] => TRUE

To be honest, I don't see how we can solve this. This should be done at application level before being stored in BQ.

Check the idea has not already been suggested

• I could not find my idea in existing issues

Edit the title above with self-explanatory function name and argument names

• The function name and the argument names I entered in the title above seems self explanatory to me.

BigFunction Description as it would appear in the documentation

Takes in JSON as a string and returns parsed XML from JSON.

Examples of (arguments, expected output) as they would appear in the documentation

const json = JSON.stringify({
    "breakfast_menu": {
        "food": [
            {
                "name": "Belgian Waffles",
                "price": "$5.95",
                "description": "Two of our famous Belgian Waffles with plenty of real maple syrup",
                "calories": 650
            },
            {
                "name": "Strawberry Belgian Waffles",
                "price": "$7.95",
                "description": "Light Belgian waffles covered with strawberries and whipped cream",
                "calories": 900
            },
            {
                "name": "Berry-Berry Belgian Waffles",
                "price": "$8.95",
                "description": "Light Belgian waffles covered with an assortment of fresh berries and whipped cream",
                "calories": 900
            },
            {
                "name": "French Toast",
                "price": "$4.50",
                "description": "Thick slices made from our homemade sourdough bread",
                "calories": 600
            },
            {
                "name": "Homestyle Breakfast",
                "price": "$6.95",
                "description": "Two eggs, bacon or sausage, toast, and our ever-popular hash browns",
                "calories": 950
            }
        ]
    }
})

json2xml(json) -> "<breakfast_menu><food><name>Belgian Waffles</name><price>$5.95</price><description>Two of our famous Belgian Waffles with plenty of real maple syrup</description><calories>650</calories></food><food><name>Strawberry Belgian Waffles</name><price>$7.95</price><description>Light Belgian waffles covered with strawberries and whipped cream</description><calories>900</calories></food><food><name>Berry-Berry Belgian Waffles</name><price>$8.95</price><description>Light Belgian waffles covered with an assortment of fresh berries and whipped cream</description><calories>900</calories></food><food><name>French Toast</name><price>$4.50</price><description>Thick slices made from our homemade sourdough bread</description><calories>600</calories></food><food><name>Homestyle Breakfast</name><price>$6.95</price><description>Two eggs, bacon or sausage, toast, and our ever-popular hash browns</description><calories>950</calories></food></breakfast_menu>"

returns 0 indexed index of value in array.
If not found, return null

get inspired from https://stackoverflow.com/questions/70885708/how-to-find-the-index-of-an-element-in-an-array-in-bigquery

Check the idea has not already been suggested

• I could not find my idea in existing issues

Edit the title above with self-explanatory function name and argument names

• The function name and the argument names I entered in the title above seems self explanatory to me.

BigFunction Description as it would appear in the documentation

Quantize value into a bin.

Examples of (arguments, expected output) as they would appear in the documentation

quantize_into_fixed_width_bins(-4, 0, 100, 10)
--> ]-∞, 0[

quantize_into_fixed_width_bins(5, 0, 100, 10)
--> [0, 10[

quantize_into_fixed_width_bins(97, 0, 100, 10)
--> [90, 100]

quantize_into_fixed_width_bins(123, 0, 100, 10)
--> [100, + ∞[

convert arrays to json_string before comparing them as indicated here
https://stackoverflow.com/questions/68398759/check-if-two-arrays-are-exactly-the-same-in-bigquery-merge-statement

get inspired and cite: https://github.com/salrashid123/bq-udf-protobuf

returns an array with all values except value.

Get inspired from https://stackoverflow.com/questions/68580397/bigquery-udf-remove-from-array-how-do-i-anonymously-reference-an-element-in-a-s