phone_number_info¶
phone_number_info(phone_number, options)
Description¶
Get phone_number info
such as:
country,isValid,- etc
using libphonenumber-js library.
Argument options can be null or must be a json with the following keys:
defaultCountry, defaultCallingCode and extract as described in the
library documentation.
Usage¶
Call or Deploy phone_number_info ?
Call phone_number_info directly
The easiest way to use bigfunctions
phone_number_infofunction is deployed in 39 public datasets for all of the 39 BigQuery regions.- It can be called by anyone. Just copy / paste examples below in your BigQuery console. It just works!
- (You need to use the dataset in the same region as your datasets otherwise you may have a function not found error)
Public BigFunctions Datasets
| Region | Dataset |
|---|---|
eu |
bigfunctions.eu |
us |
bigfunctions.us |
europe-west1 |
bigfunctions.europe_west1 |
asia-east1 |
bigfunctions.asia_east1 |
| ... | ... |
Deploy phone_number_info in your project
Why deploy?
- You may prefer to deploy
phone_number_infoin your own project to build and manage your own catalog of functions. - This is particularly useful if you want to create private functions (for example calling your internal APIs).
- Get started by reading the framework page
Deployment
phone_number_info function can be deployed with:
pip install bigfunctions
bigfun get phone_number_info
bigfun deploy phone_number_info
Examples¶
1. Get info about an international phone_number (starting with +)
select bigfunctions.eu.phone_number_info("+33123456789", null)
select bigfunctions.us.phone_number_info("+33123456789", null)
select bigfunctions.europe_west1.phone_number_info("+33123456789", null)
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| info |
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| {
"isPossible": true,
"isValid": true,
"parseError": null,
"country": "FR",
"countryCallingCode": "33",
"formattedInternational": "+33 1 23 45 67 89",
"formattedNational": "01 23 45 67 89",
"isNonGeographic": false,
"nationalNumber": "123456789",
"number": "+33123456789",
"possibleCountries": ["FR"],
"type": "FIXED_LINE",
"uri": "tel:+33123456789"
}
|
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
2. Get info about a national phone_number
select bigfunctions.eu.phone_number_info("0123456789", json '{"defaultCountry": "FR"}')
select bigfunctions.us.phone_number_info("0123456789", json '{"defaultCountry": "FR"}')
select bigfunctions.europe_west1.phone_number_info("0123456789", json '{"defaultCountry": "FR"}')
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| info |
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| {
"isPossible": true,
"isValid": true,
"parseError": null,
"country": "FR",
"countryCallingCode": "33",
"formattedInternational": "+33 1 23 45 67 89",
"formattedNational": "01 23 45 67 89",
"isNonGeographic": false,
"nationalNumber": "123456789",
"number": "+33123456789",
"possibleCountries": ["FR"],
"type": "FIXED_LINE",
"uri": "tel:+33123456789"
}
|
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
3. If no phone number is found in phone_number argument, a reason in given in parseError
select bigfunctions.eu.phone_number_info("Hello!", null)
select bigfunctions.us.phone_number_info("Hello!", null)
select bigfunctions.europe_west1.phone_number_info("Hello!", null)
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| info |
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| {
"isPossible": false,
"isValid": false,
"parseError": "NOT_A_NUMBER",
"country": null,
"countryCallingCode": null,
"formattedInternational": null,
"formattedNational": null,
"isNonGeographic": null,
"nationalNumber": null,
"number": null,
"possibleCountries": null,
"type": null,
"uri": null,
}
|
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
4. By default, if the given phone_number text contains a phone number among other text, it will be extracted.
select bigfunctions.eu.phone_number_info("Hello +33123456789 !", null)
select bigfunctions.us.phone_number_info("Hello +33123456789 !", null)
select bigfunctions.europe_west1.phone_number_info("Hello +33123456789 !", null)
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| info |
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| {
"isPossible": true,
"isValid": true,
"parseError": null,
"country": "FR",
"countryCallingCode": "33",
"formattedInternational": "+33 1 23 45 67 89",
"formattedNational": "01 23 45 67 89",
"isNonGeographic": false,
"nationalNumber": "123456789",
"number": "+33123456789",
"possibleCountries": ["FR"],
"type": "FIXED_LINE",
"uri": "tel:+33123456789"
}
|
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
5. To consider that phone_number cannot have additional text use extract: false as option
select bigfunctions.eu.phone_number_info("Hello +33123456789 !", json '{"extract": false}')
select bigfunctions.us.phone_number_info("Hello +33123456789 !", json '{"extract": false}')
select bigfunctions.europe_west1.phone_number_info("Hello +33123456789 !", json '{"extract": false}')
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| info |
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| {
"isPossible": false,
"isValid": false,
"parseError": "NOT_A_NUMBER",
"country": null,
"countryCallingCode": null,
"formattedInternational": null,
"formattedNational": null,
"isNonGeographic": null,
"nationalNumber": null,
"number": null,
"possibleCountries": null,
"type": null,
"uri": null,
}
|
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
Use cases¶
A customer service department stores customer phone numbers in a BigQuery table. They want to clean up the data and enrich it with location information. The phone_number_info function can be used to accomplish this.
Use Case Scenario:
The table customer_data contains a column phone with various formats of phone numbers, including some with extra characters or missing country codes.
Example BigQuery SQL:
SELECT
phone,
bigfunctions.us.phone_number_info(phone, JSON '{"defaultCountry": "US"}') AS phone_info
FROM
`project_id.dataset_id.customer_data`;
Explanation:
-
bigfunctions.us.phone_number_info(phone, JSON '{"defaultCountry": "US"}'): This calls thephone_number_infofunction.- We're using the
usdataset because our project is in the US multi-region. Choose the appropriate regional or multi-regional dataset for your project's location. phoneis the column containing the phone number string.JSON '{"defaultCountry": "US"}'provides the optionaldefaultCountryparameter. This is important for correctly interpreting phone numbers that don't start with a "+" and country code. It assumes any number without a "+" is a US number. You would change this to match the expected default country for your data.
- We're using the
-
AS phone_info: This assigns the output of the function to a new column namedphone_info. The output is a JSON structure.
Benefits:
- Standardization: The function parses and standardizes the phone numbers into a consistent international format (
numberfield in the JSON output), even if the original data was messy. - Validation: The
isValidfield in the JSON output indicates whether the phone number is valid according to international standards. This allows for identifying and correcting invalid numbers. - Enrichment: The function provides additional information like
countryandtype(e.g., mobile, fixed line). This data can be used for segmentation, analytics, and reporting. - Data Cleaning: You can use the output to filter out invalid numbers:
SELECT
phone
FROM
`project_id.dataset_id.customer_data`,
UNNEST(bigfunctions.us.phone_number_info(phone, JSON '{"defaultCountry": "US"}')) AS phone_info
WHERE phone_info.isValid = TRUE;
This example demonstrates how to use the phone_number_info function to clean, validate, and standardize phone number data in BigQuery, enabling better data quality and more insightful analysis. Remember to adjust the dataset and defaultCountry parameter based on your project's location and the characteristics of your data.
Need help or Found a bug?
Get help using phone_number_info
The community can help! Engage the conversation on Slack
We also provide professional suppport.
Report a bug about phone_number_info
If the function does not work as expected, please
- report a bug so that it can be improved.
- or open the discussion with the community on Slack.
We also provide professional suppport.
by adding a 
on