bigfunctions > phone_number_info
phone_number_info¶
Call or Deploy phone_number_info
?
✅ You can call this phone_number_info
bigfunction directly from your Google Cloud Project (no install required).
- This
phone_number_info
function is deployed inbigfunctions
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. This is particularly useful if you want to create private functions (for example calling your internal APIs). Discover the framework
Public BigFunctions Datasets:
Region | Dataset |
---|---|
eu |
bigfunctions.eu |
us |
bigfunctions.us |
europe-west1 |
bigfunctions.europe_west1 |
asia-east1 |
bigfunctions.asia_east1 |
... | ... |
Description¶
Signature
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.
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,
}
|
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
Need help using phone_number_info
?
The community can help! Engage the conversation on Slack
For professional suppport, don't hesitate to chat with us.
Found a bug using 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.
For professional suppport, don't hesitate to chat with us.
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_info
function.- We're using the
us
dataset because our project is in the US multi-region. Choose the appropriate regional or multi-regional dataset for your project's location. phone
is the column containing the phone number string.JSON '{"defaultCountry": "US"}'
provides the optionaldefaultCountry
parameter. 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 (
number
field in the JSON output), even if the original data was messy. - Validation: The
isValid
field 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
country
andtype
(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.
Spread the word¶
BigFunctions is fully open-source. Help make it a success by spreading the word!