BigFunctions

BigFunctions are open-source BigQuery routines that give you SQL-superpowers in BigQuery 💪.

✅ You can call ANY of the following public BigFunctions from your Google Cloud Project (no install).

• The functions are deployed in bigfunctions GCP project in 39 datasets for all of the 39 BigQuery regions.
• They are public, so they can be called by anyone.
• For any question or difficulties, please read Getting Started.
• If you prefer to deploy the BigFunction in your own project, read Getting Started.
• Found a bug? Please raise an issue here

All BigFunctions Datasets >

Region	Dataset
eu	bigfunctions.eu
us	bigfunctions.us
europe-west1	bigfunctions.europe_west1
asia-east1	bigfunctions.asia_east1
asia-east2	bigfunctions.asia_east2
asia-northeast1	bigfunctions.asia_northeast1
asia-northeast2	bigfunctions.asia_northeast2
asia-northeast3	bigfunctions.asia_northeast3
asia-south1	bigfunctions.asia_south1
asia-south2	bigfunctions.asia_south2
asia-southeast1	bigfunctions.asia_southeast1
asia-southeast2	bigfunctions.asia_southeast2
australia-southeast1	bigfunctions.australia_southeast1
australia-southeast2	bigfunctions.australia_southeast2
europe-central2	bigfunctions.europe_central2
europe-north1	bigfunctions.europe_north1
europe-southwest1	bigfunctions.europe_southwest1
europe-west2	bigfunctions.europe_west2
europe-west3	bigfunctions.europe_west3
europe-west4	bigfunctions.europe_west4
europe-west6	bigfunctions.europe_west6
europe-west8	bigfunctions.europe_west8
europe-west9	bigfunctions.europe_west9
europe-west12	bigfunctions.europe_west12
me-central1	bigfunctions.me_central1
me-west1	bigfunctions.me_west1
northamerica-northeast1	bigfunctions.northamerica_northeast1
northamerica-northeast2	bigfunctions.northamerica_northeast2
southamerica-east1	bigfunctions.southamerica_east1
southamerica-west1	bigfunctions.southamerica_west1
us-central1	bigfunctions.us_central1
us-east1	bigfunctions.us_east1
us-east4	bigfunctions.us_east4
us-east5	bigfunctions.us_east5
us-south1	bigfunctions.us_south1
us-west1	bigfunctions.us_west1
us-west2	bigfunctions.us_west2
us-west3	bigfunctions.us_west3
us-west4	bigfunctions.us_west4

👀 Explore

• explore_table(fully_qualified_table): Show table infos and column statistics
• sankey_chart(data): Return html with a Sankey Google chart
• explore_dataset(fully_qualified_dataset): Show infos about dataset tables
• chart(data, chart_type, options): Return html with a chartjs chart
• explore_column(fully_qualified_column): Show column statistics

🧠 AI

• ask_appstore_reviews(prompt, app_url_in_appstore): Ask AI what your app users think.
• generate_sql(question, fully_qualified_table): Transform question to a SQL query.
• ask_my_data(question, fully_qualified_table): Ask your data any question in natural language.
• ask_ai(prompt, model): Ask Anything!

🛢 Get data

• get_webpage_metadata(url): Get webpage metadata
• get_appstore_reviews(url): GET Apple App Store Reviews of an app
• get_meteo(latitude, longitude, date): Get meteo
• get_webpage_structured_data(url): Get webpage Structured Data
• get_json(url, headers): GET json data from url
• exchange_rate(base, to): Get exchange_rate
• get(url, headers): Request url

💬 Notify

• send_mail_with_excel(to, subject, content, excel_filename, table_or_view_or_query): Sends an email with table_or_view_or_query data attached as excel file
• send_sms(message, phone_number): Sends message via SMS to phone_number
• send_teams_message(message, webhook_url): Sends message to a Microsoft Teams channel.
• send_slack_message(message, webhook_url): Sends message to a slack channel.
• send_google_chat_message(message, webhook_url): Sends message to google chat space
• send_mail(to, subject, content, attachment_filename, attachment_content): Sends an email

🚀 Export

• upload_table_to_gsheet(table_or_view_or_query, max_rows, spreadsheet_url, worksheet_name, write_mode): Upload data from table_or_view_or_query to Google Sheet
• post(url, data, headers): POST data to url.
• export_to_pubsub(project, topic, data, attributes): Exports data and attributes to Pub/Sub topic.
• export_to_datastore(project, namespace, kind, key, data): Exports data to Datastore
• upload_to_gsheet(data, spreadsheet_url, worksheet_name, write_mode): Upload data (a json array of objects) to a Google Sheet

1️⃣ Transform numeric

• format_percentage(first_number, second_number, nb_decimals): Return first_number / second_number as a formatted percentage
• quantize_into_fixed_width_bins(value, min_bound, max_bound, nb_bins): Get the bin_range in which belongs value
• quantize_into_bins(value, bin_bounds): Get the bin_range in which belongs value

✨ Transform string

• phone_number_info(phone_number, options): Get phone_number info
• remove_strings(string, strings_to_remove): Remove any string of strings_to_remove from string
• translate(text, target_language): Translate text into target_language
• deidentify(text, info_types): Masks sensitive information of type info_types in text
• parse_url(url): Return url parts
• levenshtein(string1, string2): Compute levenshtein distance between string1 and string2
• convert_non_ascii_characters_to_unicode_escape_sequences(text): Replace all non ASCII characters with escape unicode
• faker(what, locale): Generates fake data
• remove_words(string, words_to_remove): Remove any word of words_to_remove from string
• is_phone_number_valid(phone_number, options): Return if phone_number is valid
• ip2country_name(ip): Get country_name of ip
• xml_extract(xml, x_path): Returns content extracted from XML from given XPATH
• render_template(template, context): Render template with context using nunjucks.js templating library
• replace_special_characters(string, replacement): Replace most common special characters in a string with replacement
• remove_accents(str): Remove accents
• parse_user_agent(user_agent_string): Parses User Agent strings into several components
• ip_range2ip_networks(first_ip, last_ip): Convert an IP range into a json list of IP networks in CIDR notation
• url_decode(url_encoded_string): Decode url_encoded_string
• detect_sensitive_info(text): Detect sensitive information in text
• ip2asn(ip): Get asn of ip
• is_email_valid(email): Return true if email is valid
• ip2continent_name(ip): Get continent of ip
• ip2country(ip): Get country_code of ip
• render_string(template, context): Render template with context using nunjucks.js templating library
• ip2continent(ip): Get continent_code of ip
• remove_extra_whitespaces(str): Remove unwanted whitespaces

🌐 Transform geo data

• validate_address(address): Validate address using Google Maps
• reverse_geocode(latitude, longitude): Get address details at latitude, longitude
• h3(function_name, arguments): Wrapper around Uber H3
• geocode(address): Get address details from Google Maps

📆 Transform date

• gregorian2hijri(gregorian_date): Convert Gregorian Date to Hijri Date (taken from here)
• is_public_holiday(date, country_code): Return true if date corresponds to a public holiday in country_code
• date_sub_isoyear(date, years): Returns same day years before
• translated_weekday_name(date, language): Get translated_weekday_name
• translated_month_name(date, language): Get translated_month_name
• generate_dates(start_date, end_date): Generate a table of dates
• parse_date(date_string): Parse date with automatic format detection

{...} Transform json

• items2json(key_value_items): Returns json object from array of key_value_items
• json_values(json_string): Extract values from json_string
• json_schema(json_string): Return the schema of a json string as [{path, type}]
• json_keys(json_string): Extract keys from json_string
• json_query(json_string, query): Extract data from json_string using advanced json querying
• json_merge(json_string1, json_string2): Merge json_string1 and json_string2
• json_items(json_string): Extract key_value_items from json_string

[...] Transform array

• remove_value(arr, value): Return an array with all values except value.
• distinct_values(arr): Return distinct values
• median_value(arr): Return median value of array
• percentile_value(arr, percentile): Returns percentile of an array with percentile a float in range [0, 1].
• sort_values_desc(arr): Return sorted array (descending)
• last_value(arr): Return last value of array
• find_value(arr, value): Return the first offset (zero-based index) of value in array arr
• find_lower_value(arr, x): Return the offset (zero-based index) of the first value in arr where value <= x
• get_value(key_value_items, search_key): Return the first value with a key search_key from key_value_items
• min_value(arr): Return min value of array
• array_intersect(array1, array2): Returns the intersection of two arrays.
• sum_values(arr): Return the sum of array values
• array_union(array1, array2): Returns the union of two arrays.
• find_greater_value(arr, x): Return the offset (zero-based index) of the first value in arr where value >= x
• sort_values(arr): Return sorted array (ascending)
• max_value(arr): Return max value of array
• are_arrays_equal(array1, array2): Return true if array1 = array2

🧠 Machine learning

• roc_auc(predictions): Returns the Area Under the Receiver Operating Characteristic Curve (a.k.a. ROC AUC)
• precision_recall_auc(predictions): Returns the Area Under the Precision Recall Curve (a.k.a. AUC PR)
• prophet(records, periods, kwargs): Forecast time-series using prophet
• roc_curve(predictions): Returns the Receiver Operating Characteristic Curve (a.k.a. ROC Curve)
• sentiment_score(content): Compute sentiment score of content
• precision_recall_curve(predictions): Returns the Precision-Recall Curve

🌐 Graph

• connected_components(fully_qualified_table): Compute the connected components of a non-directed graph.

🔨 Convert data format

• json2excel(data): Dump data to excel file
• json2xml(json): Returns XML for given JSON string
• html2pdf(html): Convert html to pdf
• xml2json(xml): Returns JSON as a string for given XML string

🔨 Utils

• deduplicate_rows(query_or_table_or_view): Returns the deduplicated rows of query_or_table_or_view
• get_table_columns(fully_qualified_table): Get the column information of the given table from INFORMATION_SCHEMA.COLUMNS
• get_view_history(fully_qualified_view): Get BigQuery View history
• sleep(seconds): Sleep during seconds seconds
• get_latest_partition_timestamp(fully_qualified_table): Return the maximum of the partition column of fully_qualified_table
• upsert(query_or_table_or_view, destination_table, insertion_mode, primary_keys, recency_field): Merges query_or_table_or_view into the destination_table.
• run_python(python_code, requirements, kwargs): Run any python_code.
• timestamp_to_unix_date_time(timestamp_expression, date_time_part): Returns the number of date_time_part since 1970-01-01 00:00:00 UTC.
• timestamp_from_unix_date_time(unix_date_time, date_time_part): Interprets unix_date_time as the number of date_time_part since 1970-01-01 00:00:00 UTC.