Hitachi Vantara Pentaho Community Forums
Results 1 to 12 of 12

Thread: phone number dimension with google libphonenumber

  1. #1
    Join Date
    Apr 2011
    Posts
    159

    Default phone number dimension with google libphonenumber

    Greetings all.

    I would like to build a phone number dimension (for use in a data model) where I will have a list of phone numbers and I'd like to add columns such as city/state/country data, isValid, etc.

    Google Code has a project: https://code.google.com/p/libphonenumber/

    but I don't see a readily available API for it (that is, no hosting service I see that I can, say, send JSON to).

    So, I'm not sure 'how' I can leverage the available *.jar files they have and KETTLE to do this - any leads I can read/research/test? I am a moderate kettle user, but never attempted anything this 'interesting' if you will.

    My thinking is there has to be a way to pass this list of numbers to 'something' that returns all the parms, but the 'how the something works' is something my rookie mind is lost at right now.

    Again, looking for some conversation/direction/ideas.

    Many many thanks!

  2. #2
    Join Date
    Nov 2008
    Posts
    777

    Default

    You should be able to place the .jar file in Kettle's /libext folder and then call the parsing methods in a JavaScript step. Let us know if you need help with that.

    I'm curious, though, how you are going to get the city/state from the phone number. It seems to me that the API provides only the country (code).
    pdi-ce-4.4.0-stable
    Java 1.7 (64 bit)
    MySQL 5.6 (64 bit)
    Windows 7 (64 bit)

  3. #3
    Join Date
    Apr 2011
    Posts
    159

    Default

    this is a GREAT start, thank you! I had no idea I could toss it in there and 'get access' to those methods. I will have to research next the JavaScript step and read more about the libphonenumber project.

    I haven't fully thought though the city/state data, but I know it's something I want to add/build into the dimension table. I'll start with CC and then try to solve that. I believe you are correct and I will not be able to get that from that project. i may have access to a CCMI (http://www.ccmi.com/) table I can use, but baby steps!

  4. #4
    Join Date
    Nov 2008
    Posts
    777

    Default

    Here's a post from some time ago that demonstrates using Java methods in a JavaScript step. If you use the "var" qualifier on the variables you want to output to the next step, then the "Get variables" button will pick them up and fill them into the table at the bottom of the step configuration screen.

    http://forums.pentaho.com/showthread...977#post322977

    I'm sure there are many other examples in this forum as well.
    pdi-ce-4.4.0-stable
    Java 1.7 (64 bit)
    MySQL 5.6 (64 bit)
    Windows 7 (64 bit)

  5. #5
    Join Date
    Apr 2011
    Posts
    159

    Default

    Thanks.

    Do I have to 'load' the classes (the jar file) in some way? I noticed in your example thread there is inflator = 'new java.util.zip.Inflater()' but I am lost on how I do something similar with this jar file.

    For perspective, my first step is grapping a single record (a raw number) from a table - might as well be "SELECT '15551234567' as myStr FROM DUAL" for this example's sake.

    My next step is the Modified Java Script Value.

    From there I tried to establish the example Java section of the code.google.com home page for libphonenumber:

    String swissNumberStr = myStr
    PhoneNumberUtil phoneUtil = PhoneNumberUtil.getInstance();
    try {
    PhoneNumber swissNumberProto = phoneUtil.parse(swissNumberStr, "CH");
    } catch (NumberParseException e) {
    System.err.println("NumberParseException was thrown: " + e.toString());
    }

    it balked at the first line.

    I am guessing that's because it's a java example (not javascript), but I thought that is what you were saying about using Java methods in a Javascript step.

    In the end, I'm obviously rookie-level here, but I think I'm missing something with my environment - how to establish that step should be using the objects/methods available in that jar file.

    Does that make sense? Or, then, again, I may be way off!!!!

    I really appreciate the guidance.

  6. #6
    Join Date
    Nov 2008
    Posts
    777

    Default

    No loading of the jar file is required. You really don't have to import the classes either.

    Indeed, JavaScript is not Java! Here is how I would get going with the Google API:

    Code:
    /* Grab the parser. */
    phoneUtil = com.google.i18n.phonenumbers.PhoneNumberUtil.getInstance();
    
    /* Parse the phone number. */
    var proto = null;
    try {
        proto = phoneUtil.parse(myStr, "US");
    } catch (error) {
        proto = error.toString();
    }
    Attached Files Attached Files
    pdi-ce-4.4.0-stable
    Java 1.7 (64 bit)
    MySQL 5.6 (64 bit)
    Windows 7 (64 bit)

  7. #7
    Join Date
    Apr 2011
    Posts
    159

    Default

    Thank you! That was a HUGE help! From that I was able to use several of the methods available and feel confident I can use the rest of the library.

    My question (now) was how did YOU know where to find the javascript way of doing what you did!?

    The main site appears to be slanted towards java and it was not obvious to me to do, for example, the first line you did, what you called 'grab the parser':

    /* Grab the parser. */
    phoneUtil = com.google.i18n.phonenumbers.PhoneNumberUtil.getInstance();

    For posterity, the two main hurdles I had was :
    1. Not knowing how to use the available *.jar file (toss it in libext)
    2. Not knowing how to begin using the objects/methods/properties, but because I could not find javascript documentation. I have NOW found plenty of web examples (googled 'libphonenumber javascript') but did I miss a document or wiki or other starting point?

    Regardless, thank you, thank you, thank you. Big step forward today in my data-integration abilities.

  8. #8
    Join Date
    Nov 2008
    Posts
    777

    Default

    Quote Originally Posted by loganseth View Post
    My question (now) was how did YOU know where to find the javascript way of doing what you did!?
    As you may know, Java is a compiled and heavily "typed" language. All variables and objects must be declared or the code will not compile. JavaScript, on the other hand, is an interpreted and minimally "typed" language. You do not need to declare variables, and objects can be assigned to variables pretty much willy-nilly (hence the first statement in my example code which gets an instance of the PhoneNumberUtil class and assigns it to an un-typed, un-declared JavaScript variable.)

    Here is one good source of information for programming in JavaScript: https://developer.mozilla.org/en-US/..._documentation

    P.S. There is also a way (in Kettle) to write your own Java code snippet and run it. This is called the User Defined Java Class step, or UDJC for short. I've used it occasionally and it is pure Java code but there is a little more boilerplate code to deal with. That's why I chose JavaScript even though it is much slower in execution (re: interpreted vs. compiled).

    Other useful links:
    http://wiki.pentaho.com/display/EAI/...a+Script+Value
    http://type-exit.org/adventures-with...nd-javascript/
    Last edited by darrell.nelson; 12-06-2013 at 12:45 PM. Reason: Other useful links
    pdi-ce-4.4.0-stable
    Java 1.7 (64 bit)
    MySQL 5.6 (64 bit)
    Windows 7 (64 bit)

  9. #9
    Join Date
    Apr 2011
    Posts
    159

    Default

    Again, many thanks!

    One more item maybe I could steal your advice with.

    There is an example on the libphonenumber google page under 'Geocoding Phone Numbers Offline'

    To use it, I downloaded the geocoder2.0.jar file, tossed it in libext, but when I add this line (similar to your first line) it balks at me with a general error:

    /* Grab the parser. */
    phoneUtil = com.google.i18n.phonenumbers.PhoneNumberUtil.getInstance();
    /* Grab the geocoder */
    geoCoder = com.google.i18n.phonenumbers.PhoneNumberOfflineGeocoder.getInstance();
    From what I have read this
    geoCoder.getDescriptionForNumber(pn, Locale.ENGLISH));
    Will return the city/state information! (your earlier comment!!)

    But I cannot get it to work!

    I have found plenty of java examples but no js ones...

    (NOTE - I also tried: geoCoder = com.google.i18n.phonenumbers.geocoding.PhoneNumberOfflineGeocoder.getInstance()

  10. #10
    Join Date
    Nov 2008
    Posts
    777

    Default

    Did you read the fine print when you downloaded geocoder-2.10.jar? It says that the prefixmapper jar is also required! I downloaded prefixmapper-2.10.jar and also placed it in the /libext folder. Note that you may have to restart Spoon in order for it to pick up the new jar.

    Also, the java snippet on the Google website doesn't show you all the imports. You can get past that by including the full package name for the Locale. Thus, here is my example with the geocoder bit included.

    Code:
    /* Grab the phone number utility and the geocoder. */
    phoneUtil = com.google.i18n.phonenumbers.PhoneNumberUtil.getInstance();
    geoCoder = com.google.i18n.phonenumbers.geocoding.PhoneNumberOfflineGeocoder.getInstance();
    
    /* Parse the phone number. */
    var proto = null;
    var city = null;
    try {
        proto = phoneUtil.parse(myStr, "US");
        city = geoCoder.getDescriptionForNumber(proto, java.util.Locale.ENGLISH);
    
    } catch (error) {
        proto = error.toString();
    
    }
    And it seems to work! Using the popular number of 12024561111, results in a city of "Washington D.C.".
    pdi-ce-4.4.0-stable
    Java 1.7 (64 bit)
    MySQL 5.6 (64 bit)
    Windows 7 (64 bit)

  11. #11
    Join Date
    Apr 2011
    Posts
    159

    Default

    BAH!! NO, I totally missed that...fail on my part. Hopefully that is something I only have to learn once (reading the fine print!)...

    Thank you, so much.

    For posterity, here are a few other lessons learned with this API:
    Code:
    /* if you add a '+' to the front of the numbers, you do not need to pass the two char country code */
    var myStr = '+' + myStr;
    proto = phoneUtil.parse(myStr, "");
    
    /* checks for valid formatting */
    var isNumberValid = phoneUtil.isValidNumber(proto);
    
    /* checks for FIXED_LINE or MOBILE or a few others... */
    var numberType    = phoneUtil.getNumberType(proto);
    
    /* get country code, 2 digits like US */
    var region = phoneUtil.getRegionCodeForCountryCode(proto.getCountryCode());
    I did notice on the list of numbers I ran that the 'city' information returned appears to be 'best effort' - so in some cases it is showing 'Georgia' (for example) and also showing, 'Atlanta, GA' for others. So it's not truly 'city' and not truly 'state' but both.

    I suppose if I really want to get after it, next may actually be the geocoding coordinates! But I have PLENTY now to move forward.

    Many, many, many thanks.

  12. #12
    Join Date
    Aug 2017
    Posts
    1

    Default

    Hi,

    So I tried to do almost the same thing:
    phoneUtil = com.google.i18n.phonenumbers.PhoneNumberUtil.getInstance();
    proto = phoneUtil.parse(myStr, "US");phone_number.ktrphone_number.ktrphone_number.ktr

    But I'm getting an error:
    2017/08/25 16:25:48 - Modified Java Script Value 2.0 - Javascript error:
    2017/08/25 16:25:48 - Modified Java Script Value 2.0 - Cannot convert 044 668 18 00 to java.lang.CharSequence (script#2)

    Then I have switched to:
    proto = phoneUtil.parse(myStr.toString(), "US");

    And it works. But the source field is already set as String, so why the conversion to string is still required?

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Privacy Policy | Legal Notices | Safe Harbor Privacy Policy

Copyright © 2005 - 2019 Hitachi Vantara Corporation. All Rights Reserved.