Skip to content

bigfunctions > send_sms

send_sms

Call or Deploy send_sms ?

✅ You can call this send_sms bigfunction directly from your Google Cloud Project (no install required).

  • This send_sms function is deployed in bigfunctions GCP project in 39 datasets for all of the 39 BigQuery regions. You need to use the dataset in the same region as your datasets (otherwise you may have a function not found error).
  • Function is public, so it can be called by anyone. Just copy / paste examples below in your BigQuery console. It just works!
  • You may prefer to deploy the BigFunction in your own project if you want to build and manage your own catalog of functions --> Read Getting Started. This is particularly useful if you want to create private functions (for example calling your internal APIs).
  • For any question or difficulties, please read Getting Started.
  • Found a bug? Please raise an issue here

Public BigFunctions Datasets are like:

Region Dataset
eu bigfunctions.eu
us bigfunctions.us
europe-west1 bigfunctions.europe_west1
asia-east1 bigfunctions.asia_east1
... ...

Description

Signature

send_sms(message, phone_number)

Description

Sends message via SMS to phone_number

Examples

To a French phone number

select bigfunctions.eu.send_sms("Hello 👋 from bigfunctions!", "+33xxxxxxxxx")

select bigfunctions.us.send_sms("Hello 👋 from bigfunctions!", "+33xxxxxxxxx")

select bigfunctions.europe_west1.send_sms("Hello 👋 from bigfunctions!", "+33xxxxxxxxx")

+----------------------------------------------------+
| response                                           |
+----------------------------------------------------+
| {
  "body": "Hello 👋 from bigfunctions!",
  ...
}
 |
+----------------------------------------------------+

Use cases

A use case for the send_sms BigQuery function would be sending SMS notifications based on data changes or thresholds within BigQuery.

Scenario: An e-commerce company uses BigQuery to store order data. They want to be notified via SMS when a high-value order is placed.

Implementation:

  1. BigQuery Table: The company has a table called orders with columns like order_id, order_total, customer_phone.

  2. Scheduled Query: They create a scheduled query that runs every hour, checking for orders exceeding a certain value (e.g., $1000).

  3. send_sms Integration: Within the scheduled query, they incorporate the send_sms function. The query would look something like this (using the us region as an example, adjust according to your location):

SELECT
    bigfunctions.us.send_sms(
        FORMAT("High-value order placed! Order ID: %s, Total: $%f", order_id, order_total),
        customer_phone
    )
  FROM
    `your_project.your_dataset.orders`
  WHERE order_total > 1000
   AND order_placed_at > TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 1 HOUR)  -- Only check last hour's orders

How it works:

  • The scheduled query runs hourly.
  • It filters for new orders placed in the last hour exceeding $1000.
  • For each matching order, it calls the send_sms function.
  • The function sends an SMS message to the customer_phone number with the order details.

Other use cases:

  • Fraud detection: Send an SMS alert to a security team when unusual activity is detected.
  • Appointment reminders: Send SMS reminders to customers about upcoming appointments.
  • Low stock alerts: Send an SMS to inventory managers when product stock falls below a threshold.
  • Service outages: Notify relevant personnel via SMS when a service outage is detected.
  • Two-factor authentication: Send a verification code via SMS for user login.

Important considerations:

  • Cost: Be mindful of the cost of sending SMS messages, especially for high-volume scenarios.
  • Privacy: Ensure you comply with data privacy regulations related to phone numbers and user consent.
  • Error handling: Implement error handling within your queries to manage situations where sending SMS messages fails (e.g., invalid phone numbers). The provided documentation doesn't show the full response structure, but you should check for error codes/messages within the returned JSON.
  • Rate limiting: Be aware of any rate limits imposed by the SMS provider used by the send_sms function. You might need to implement logic to handle these limits.
  • Phone number format: Ensure phone numbers are in the correct international format (e.g., +1 for US, +44 for UK, etc.).

By combining BigQuery's powerful data processing capabilities with the send_sms function, you can create real-time notification systems directly within your data warehouse.