BigQuery Python UDFで電話番号正規化にチャレンジ

2026/01/12 しゃち.py みーてぃんぐ 14th. 新年LT大会

Transcript

1. BigQuery Python UDF BigQuery Python UDFß ÷/ÿÿû÷kW{ñóüó¸
2. 2024 4 Python Preview
3. BigQuery User-defined functions in Python
4. UDF UDF (User-Defined Function) x, ÷ü¿ùü¹~}¿· wßÿ|÷wt ¯~ zý¸ó¿´{Û~ó¸ý¹‰þ²SQL w߯y»±~ýwy. BigQueryw, s¼~wJavaScriptwUDF²šÿw, {þSQL¿¸ú{¹öç|sûy sx|w}~w. CREATE FUNCTION mydataset.AddFourAndDivide(x INT64, y INT64) RETURNS FLOAT64 AS ( (x + 4) / y ); SELECT val, AddFourAndDivide(val, 2) as rusult FROM UNNEST([2,3,5,8]) AS val; µó÷û³üù: Google Cloud~}_ù½õñóø¸º
5. JS UDF ; ; BigQuery~JavaScript UDF~tu¹tùxwv, _øJavaScriptù´öùú~{o y»{, ¹¿ú÷ø²GCS{²ó÷ýüùy»ß‰|rº~w CREATE TEMP FUNCTION myFunc(a FLOAT64, b STRING) RETURNS STRING LANGUAGE js OPTIONS ( library=['gs://my-bucket/path/to/lib1.js', 'gs://my-bucket/path/to/lib2.js']) AS r""" // Assumes 'doInterestingStuff' is defined in one of the library files. return doInterestingStuff(a, b); """; µó÷û³üù: Google Cloud~}_ù½õñóø¸º
6. Python UDF OPTIONS (packages='...') {, {owtù´öùúx}~ðü¸÷󲚏y»€qw, _øù´öùú²ow}~y. CREATE FUNCTION `PROJECT_ID.DATASET_ID`.area(radius FLOAT64) RETURNS FLOAT64 LANGUAGE python OPTIONS (entry_point='area_handler', runtime_version='python-3.11', // packages=['scipy==1.15.3']) AS r""" import scipy def area_handler(radius): return scipy.constants.pi*radius*radius """; SELECT `PROJECT_ID.DATASET_ID`.area(4.5); µó÷û³üù: Google Cloud}_ù½õñóø¸º
7. vs BigQuery NoteBook BigQuery~÷ü¿² vy»ÿöxwv, BigQuery NoteBook (colabtz²~) |rº, Û߯y»sx²w}~y. BigQuery NoteBookxwv, BigQuery Python UDF{ïO~~ß?|rº~y. 1 BigQuery NoteBookwßÍu¼»~¹¿ú÷øwy|, BigQuery Python UDF· wy. }~±, UDF~unittest~ß}²otv _ö ¹ø²ßÍw}~y(v³) 2 BigQuery NoteBookwßÍu¼»~¹¿ú÷ø~±, ýþ߉zý¸ ó¿²ßÍy»ß‰|rº~y. BigQuery Python UDF, ߯kÖuxu ¼€, BigQueryNw¿¸ú²þ¸v{ys{¹w²|sûysx|w} ~y
8. BigQuery Python UDF
9. s¼~wkèÿ²JS UDF{öçšÿy»sxw, »ó³wšu¼ÿû÷²ÿ´õówšu¼ÿû÷{ ûwvt~w. ÿ óx»ºÜ kèÿ²ÿòy»s, Îówztÿ|rsºy» ÿ´õó|zt÷ü¿{ttv, ÿ´õó²ÿòw}zt î~þ´º|rsx}{ßwzt}ÿ{z» _ÿû÷~kWw}vtzt ³ î®: Google libphonenumber ²t{x€, ¸ºùW{kWw}}v
10. Google libphonenumber libphonenumber , Google|Ÿ~wºü÷ó½ü¹ù´öùúwy. ÿŸözÿû÷~ñü¹ÿë÷
    ·õ¹üþóøÿu
    · ¾·¿´÷vÛzy² _ßµýüøwvt~y. s~ù´öùú²{oy»sxw,  zÿû÷~kW²û÷ö{tkú{¯v sx|w}~y.
11. Python import phonenumbers from phonenumbers import PhoneNumberFormat from phonenumbers.phonenumberutil import NumberParseException def main(phone_number: str | None) -> dict | None: if phone_number is None or not isinstance(phone_number, str): return None phone_number = phone_number.strip() if not phone_number: return None try: parsed = phonenumbers.parse(phone_number, "JP") return { "is_valid": phonenumbers.is_valid_number(parsed), "national_format": phonenumbers.format_number(parsed, PhoneNumberFormat.NATIONAL), "international_format": phonenumbers.format_number(parsed, PhoneNumberFormat.INTERNATIONAL), "country_code": strparsed.country_code, "national_number": strparsed.national_number, "extension": strparsed.extension, "italian_leading_zero": strparsed.italian_leading_zero, "number_of_leading_zeros": strparsed.number_of_leading_zeros, "raw_input": strparsed.raw_input, "country_code_source": parsed.country_code_source, "preferred_domestic_carrier_code": parsed.preferred_domestic_carrier_code, } except NumberParseException: return None
12. BigQuery Python UDF create or replace function `sandbox.normalize_phone_number`(phone_number string) // NOTE: https://github.com/daviddrysdale/python-phonenumbers/blob/dev/python/phonenumbers/phonenumber.pyi returns struct< is_valid bool, national_format string, international_format string, country_code int, national_number int, extension string, italian_leading_zero bool, number_of_leading_zeros int, raw_input string, country_code_source int, preferred_domestic_carrier_code string > language python options ( runtime_version = "python-3.11", entry_point = "main", packages = ["phonenumbers"] ) AS /* python */""" // python """
13. Python UDF Tips Python BigQuery Python UDF_Þ߯{1min{y{{º~w. BigQuery UDFxwv÷÷ý´y»_{, Jupyter Notebook{w, Python³üù ~ý¸ó¿²ö¹øz¸s ¾y»sxw®²¿ŸWw}~y. s¼{¸º, Ÿ~µ´¿û²ÿ~w, BigQueryNw~÷ðóÀ_m²¹ysx |w}~y. Python Python UDF´JS UDF, BigQuery~¿¸úñõ¹üþó¹{ Wu{»ÿ ²Nx»ÿýg|rº~y. ²w{þSQL· w‰þ²{»~wr¼€, {þSQL²vsxw¿Ÿz÷ü¿u²ßÿw}~y.
14. ¾ 0120-XX-XYYY 0120-XXX-YYY õúüð´ôûzy~ÿ´õóO|kw }/u¼~w. +81 XX-XXX-XXXX 0XX-XXX-XXXX +XX{ÿu}, y~ÿ~ÿû÷{vÛw}~ w. 050XXXXYYY 050-XXXX-YYYY ÿ´õó|z{s÷{, ùWz_wÿ´õó|ÞNu¼~w.
15. ¾ t/ø~÷ü¿w, Google libphonenumber{¸»kW|w}zzs±ü¹|rº~w. €w, s¼¹~±ü¹, Îß~_ vw ÿýxtx}vww. y¿ÿ´õó<ÿ=(U+FF ), OÞ}þ´ú¹<«=(U+ B), NÞ}þ´ú¹<{=(U+ B) |~¼»ÿû÷{ztv, kWw}~{³ww. ÿû÷{O‰zš÷´o_W (Ï: <TEL:=zy)|ÿ/wvt»|\, ÿû÷~²kwÿûw}~{³ww.
16. ¾ ߯f—}11]. ³¹ø{ö}y»í¿¹ýóøf—²8]{ òw~w. o (JS UDF) ÎÞ (Python UDF) ߯f— 6.76û 1V16.36û (}11.3]) í¿¹ýóøf— 6.61û 53.15û (}8.0]) ³ 0.1×¹t zz, \{~Õî²ßÿy»?ÿxwv, CloudRun²qNrv, Remote Function{¹API²ûxtv²÷ýüñx~¯svtzt|, \ x¹t~߯f—xí¿¹ýóøf—z |w~w. CloudRun + Remote Function~ërq»CloudRun~_Só²qNr»³¹ø|, Previewz{ztvPython UDF{ztv, }~³¹ø|ÿ ~owzt±, ||~Ûuxtx}vx}t~w.
17. 1 BigQuery Python UDF, Previewz~ ±, qýw{ow}~y. GA{rsv, ý¯|y~¸vz¯Ûu ¼»{woôÛw{oy»{~݉ zö/{zº}vwy. 2 TUDFwPython~ðü¸÷óxù´ö ùú~ðü¸÷ó²öçgÛwvt~ y. unittest~ßÍ / CIwñ·ó¿ Ai {¸»ðü¸÷ó²ó÷ {¸º, ħ ö{ñóöúó¹ÿýw ÿx}svt~y. 3 <Þ~ß͸ùü|~owx}, NULL²ßwvt~y. Ûo²ƒowx}, ¸ùüñó»ü¸ ²ü¹ýó¹{±»sxw, ĉÛg² r¹¼}vx}svt~y.
18. BigQuery Python UDF{¸º, phonenumbers~¸vz?z_øù´öù ú²BigQuery wöç{ow},  z÷ü¿ v|ûw{zº~ w. ETL ߯f—xí¿¹ýóø òw~w|, ¯~{O³¹øw¿Õ îzkW²ßÿ·Þ~½úõü·÷óxwv², ¿o ûÿ {{¼vt»sx|Wu¼~w.{