Catalog of public BigFunctions¶

Perform any task from SQL

Functions are Ready to Use!

✅ You can call ANY public BigFunction without install
The functions are deployed in 39 public datasets (one dataset per region) so that you can call them without install.

✅ You can also deploy the public BigFunctions
You can deploy public BigFunctions (or your own) in your GCP project with a single command using BigFunctions framework.

⬆️ Load Data (23 functions)¶

There are 2 types pof load functions:

get functions enable to select external data in a select statement.

load functions enable to load data into a destination table.

exchange_rate: Get exchange_rate
faker: Generates fake data
get: Request url
get_appstore_reviews: GET Apple App Store Reviews of an app
get_data_from_apify: Get data using an apify actor.
get_github_data: Get data from public_repo into destination_dataset
get_json: GET json data from url
get_meteo: Get meteo
get_playstore_reviews: GET Google Play Store Reviews of an app
get_transport_emissions: Get the transport CO2 emissions given the distance_km
get_webpage_data: Extract data from url using prompt
get_webpage_metadata: Get webpage metadata
get_webpage_structured_data: Get webpage Structured Data
list_public_datasets: Returns list of BigQuery public_datasets
load_api_data: Load data from 250+ sources using Airbyte Python Connectors
load_duckdb_database: Download duckdb database into destination_dataset
load_file: Download web file into destination_table
load_google_trends: Load daily Google Trends
load_saas_data: Load SAAS data from 250+ sources using Airbyte Python Connectors

🔥 Transform (101 functions)¶

[...] Transform Array

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

Transform Ip

ip2asn: Get asn of ip
ip2continent: Get continent_code of ip
ip2continent_name: Get continent of ip
ip2country: Get country_code of ip
ip2country_name: Get country_name of ip
ip_range2ip_networks: Convert an IP range into a json list of IP networks in CIDR notation

📶 GA4

create_ga4_views: Create views to help querying GA4 Data

🧠 Machine Learning

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

{...} Transform Json

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

✨ Transform String

camel2snake: Convert string from camelCase to snake_case
convert_non_ascii_characters_to_unicode_escape_sequences: Replace all non ASCII characters with escape unicode
deidentify: Masks sensitive information of type info_types in text
detect_language: Detects text language
detect_sensitive_info: Detect sensitive information in text
is_email_valid: Return true if email is valid
is_phone_number_valid: Return if phone_number is valid
markdown2html: Convert markdown to html
ngram_frequency_similarity: Calculates n-gram similarity between two strings
parse_url: Return url parts
parse_user_agent: Parses User Agent strings into several components
phone_number_info: Get phone_number info
remove_accents: Remove accents
remove_extra_whitespaces: Remove unwanted whitespaces
remove_strings: Remove any string of strings_to_remove from string
remove_words: Remove any word of words_to_remove from string
render_handlebars_template: Render template with context using handlebars.js templating library
render_template: Render template with context using nunjucks.js templating library
replace_special_characters: Replace most common special characters in a string with replacement
translate: Translate text into target_language
url_decode: Decode url_encoded_string
xml_extract: Returns content extracted from XML from given XPATH

🌐 Transform Geo Data

geocode: Get address details from Google Maps
geocode_fr: Get address details from api-adresse.data.gouv.fr
h3: Wrapper around Uber H3
reverse_geocode: Get address details at latitude, longitude
validate_address: Validate address using Google Maps

🌐 Graph

connected_components: Compute the connected components of a non-directed graph.

🔨 Convert Data Format

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

📆 Transform Date

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

1️⃣ Transform Numeric

compute_linear_regression_coefficients: Returns linear regression coefficients from x, y columns.
format_percentage: Return first_number / second_number as a formatted percentage
nps: Returns the NPS (Net Promoter Score)
quantize_into_bins: Get the bin_range in which belongs value
quantize_into_bins_with_labels: Get the label of the bin in which belongs value
quantize_into_fixed_width_bins: Get the bin_range in which belongs value
weighted_average: Returns the weigthed average elements.

🧠 AI

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

🚀 Take Actions (19 functions)¶

🚀 Export

export_table_to_datastore: Export fully_qualified_table to datastore
export_to_datastore: Exports data to Datastore
export_to_pubsub: Exports data and attributes to Pub/Sub topic.
export_to_storage: Exports content to Cloud Storage
post: POST data to url.
upload_table_to_gsheet: Upload data from table_or_view_or_query to Google Sheet
upload_to_gsheet: Upload data (a json array of objects) to a Google Sheet

💬 Notify

send_google_chat_message: Sends message to google chat space
send_mail: Sends an email
send_mail_with_excel: Sends an email with table_or_view_or_query data attached as excel file
send_mail_with_gmail: Sends an email using your gmail account
send_slack_message: Sends message to a slack channel.
send_sms: Sends message via SMS to phone_number
send_teams_adaptive_card: Sends Adaptive Card to a Microsoft Teams channel.
send_teams_message: Sends message to a Microsoft Teams channel.

💨 Trigger Event

refresh_powerbi: Refresh a Power BI dataset (semantic model)
refresh_tableau: Refresh a tableau datasource or workbook
trigger_dataform: Triggers a Dataform workflow in the specified project, location, repository and workspace.

🔨 Utils (10 functions)¶

deduplicate_rows: Returns the deduplicated rows of query_or_table_or_view
get_latest_partition_timestamp: Return the maximum of the partition column of fully_qualified_table
get_table_columns: Get the column information of the given table from INFORMATION_SCHEMA.COLUMNS
get_view_history: Get BigQuery View history
list_scheduled_queries: Returnsscheduled_queries of project project.
run_python: Run any python_code.
sleep: Sleep during seconds seconds
timestamp_from_unix_date_time: Interprets unix_date_time as the number of date_time_part since 1970-01-01 00:00:00 UTC.
timestamp_to_unix_date_time: Returns the number of date_time_part since 1970-01-01 00:00:00 UTC.
upsert: Merges query_or_table_or_view into the destination_table.