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
{% for dataset in dataset.split(',') -%}
{{ dataset.replace('_', '-') }}	{{ project }}.{{ dataset }}
{% endfor %}

🧠 AI

• ask_ai(prompt, model): Ask Anything!
• ask_appstore_reviews(prompt, app_url_in_appstore): Ask AI what your app users think.
• ask_my_data(question, fully_qualified_table): Ask your data any question in natural language.
• categorize(items): Categorize items in categories and subcategories.
• classify_text(text, candidate_labels): Classify text among candidate_labels
• generate_categories(items): Return categories of items.
• generate_face_embedding(image_url): Detect Face on image and Generate its Embedding
• generate_sql(question, fully_qualified_table): Transform question to a SQL query.

💬 Notify

• 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
• 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_slack_message(message, webhook_url): Sends message to a slack channel.
• 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.

🛢 Get Data

• exchange_rate(base, to): Get exchange_rate
• faker(what, locale): Generates fake data
• get(url, headers): Request url
• get_appstore_reviews(url): GET Apple App Store Reviews of an app
• get_github_data(public_repo, destination_dataset, streams): Get data from public_repo into destination_dataset
• get_google_trends(keywords, category, timeframe, geo, gprop): GET Google Trends data
• get_json(url, headers): GET json data from url
• get_meteo(latitude, longitude, date): Get meteo
• get_playstore_reviews(app_id, country, language): GET Google Play Store Reviews of an app
• get_transport_emissions(distance_km): Get the transport CO2 emissions given the distance_km
• get_webpage_data(prompt, url): Extract data from url using prompt
• get_webpage_metadata(url): Get webpage metadata
• get_webpage_structured_data(url): Get webpage Structured Data
• list_public_datasets(): Returns list of BigQuery public_datasets
• load_api_data(source, source_config, streams, destination_dataset): Load data from 250+ sources using Airbyte Python Connectors
• load_api_data_into_temp_dataset(source, source_config, streams, state): Load data from 250+ sources using Airbyte Python Connectors
• load_duckdb_database(duckdb_file_url, destination_dataset): Download duckdb database into destination_dataset
• load_duckdb_database_into_temp_dataset(duckdb_file_url): Download duckdb database into a temp dataset
• load_file(url, file_type, destination_table, options): Download web file into destination_table
• load_file_into_temp_dataset(url, file_type, options): Download web file into a temp dataset

🚀 Export

• export_table_to_datastore(fully_qualified_table, key_column, datastore_path): Export fully_qualified_table to datastore
• export_to_datastore(datastore_path, key, data): Exports data to Datastore
• export_to_pubsub(project, topic, data, attributes): Exports data and attributes to Pub/Sub topic.
• post(url, data, headers): POST data to url.
• 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
• upload_to_gsheet(data, spreadsheet_url, worksheet_name, write_mode): Upload data (a json array of objects) to a Google Sheet

💨 Trigger Event

• refresh_powerbi(dataset_id, workspace_id, tenant_id, app_id, token_secret, custom_refresh_param): Refresh a Power BI dataset (semantic model)
• refresh_tableau(workbook_or_datasource_title, site, server, token_name, token_secret): Refresh a tableau datasource or workbook

1️⃣ Transform Numeric

• format_percentage(first_number, second_number, nb_decimals): Return first_number / second_number as a formatted percentage
• quantize_into_bins(value, bin_bounds): Get the bin_range in which belongs value
• quantize_into_bins_with_labels(value, bin_bounds, labels): Get the label of the bin in which belongs value
• quantize_into_fixed_width_bins(value, min_bound, max_bound, nb_bins): Get the bin_range in which belongs value
• weighted_average(element, weight): Returns the weigthed average elements.

✨ Transform String

• camel2snake(camelCaseString): Convert string from camelCase to snake_case
• convert_non_ascii_characters_to_unicode_escape_sequences(text): Replace all non ASCII characters with escape unicode
• deidentify(text, info_types): Masks sensitive information of type info_types in text
• detect_sensitive_info(text): Detect sensitive information in text
• ip2asn(ip): Get asn of ip
• ip2continent(ip): Get continent_code of ip
• ip2continent_name(ip): Get continent of ip
• ip2country(ip): Get country_code of ip
• ip2country_name(ip): Get country_name of ip
• ip_range2ip_networks(first_ip, last_ip): Convert an IP range into a json list of IP networks in CIDR notation
• is_email_valid(email): Return true if email is valid
• is_phone_number_valid(phone_number, options): Return if phone_number is valid
• markdown2html(markdown): Convert markdown to html
• ngram_frequency_similarity(string1, string2, n): Calculates n-gram similarity between two strings
• parse_url(url): Return url parts
• parse_user_agent(user_agent_string): Parses User Agent strings into several components
• phone_number_info(phone_number, options): Get phone_number info
• remove_accents(str): Remove accents
• remove_extra_whitespaces(str): Remove unwanted whitespaces
• remove_strings(string, strings_to_remove): Remove any string of strings_to_remove from string
• remove_words(string, words_to_remove): Remove any word of words_to_remove from string
• render_handlebars_template(template, context): Render template with context using handlebars.js templating library
• 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
• translate(text, target_language): Translate text into target_language
• url_decode(url_encoded_string): Decode url_encoded_string
• xml_extract(xml, x_path): Returns content extracted from XML from given XPATH

🌐 Transform Geo Data

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

📆 Transform Date

• date_sub_isoyear(date, years): Returns same day years before
• generate_dates(start_date, end_date): Generate a table of dates
• 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
• parse_date(date_string): Parse date with automatic format detection
• translated_month_name(date, language): Get translated_month_name
• translated_weekday_name(date, language): Get translated_weekday_name

{...} Transform Json

• create_materialized_view_w_flattened_json_column(fully_qualified_table, fully_qualified_materialized_view, json_column): Create a Materialized view of a table with json_column flattened
• items2json(key_value_items): Returns json object from array of key_value_items
• json_column_schema(data): Returns the schema of a json column
• json_items(json_string): Extract key_value_items from json_string
• json_keys(json_string): Extract keys from json_string
• json_merge(json_string1, json_string2): Merge json_string1 and json_string2
• json_query(json_string, query): Extract data from json_string using advanced json querying
• json_schema(data): Returns the schema of data
• json_values(json_string): Extract values from json_string
• sql_to_flatten_json_column(data, fully_qualified_column): Generate the SQL to flatten a json column

[...] Transform Array

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

🧠 Machine Learning

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

🌐 Graph

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

🔨 Convert Data Format

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

👀 Explore

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

🔨 Utils

• deduplicate_rows(query_or_table_or_view): Returns the deduplicated rows of query_or_table_or_view
• get_latest_partition_timestamp(fully_qualified_table): Return the maximum of the partition column of fully_qualified_table
• 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
• list_scheduled_queries(project): Returnsscheduled_queries of project project.
• run_python(python_code, requirements, kwargs): Run any python_code.
• sleep(seconds): Sleep during seconds seconds
• 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.
• 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.
• upsert(query_or_table_or_view, destination_table, insertion_mode, primary_keys, recency_field): Merges query_or_table_or_view into the destination_table.