Parsing HTTP User-Agents with Teradata

Pre-requisites

C:\Program Files\Java\jdk1.8.0_231
%JAVA_HOME%\bin
CREATE DATABASE MYPROJ FROM DBC AS PERM=200E6;
GRANT EXECUTE FUNCTION ON MYPROJ TO <username>;
GRANT CREATE FUNCTION ON MYPROJ TO <username>;
GRANT EXECUTE PROCEDURE ON SQLJ TO <username>;
GRANT CREATE EXTERNAL PROCEDURE ON MYPROJ TO <username>;

JAVA UDF Source and Packaging

import ua_parser.Parser;
import ua_parser.Client;
import java.io.IOException;import com.google.gson.Gson;public class parseUA {public static String uaJson (String raw_ua) throws IOException {

Parser newParser = new Parser();
Client agent = newParser.parse(raw_ua.trim());
Gson gson = new Gson();
String returnValue = gson.toJson(agent);
if (raw_ua.trim().isEmpty()) {
returnValue = "";
}

return returnValue;
}

}
PS C:\Apps\Judf> javac -classpath `
";.\gson-2.8.6.jar;.\snakeyaml-1.20.jar;.\uap-java-1.4.0.jar" `
parseUA.java
PS C:\Apps\Judf> jar.exe -cf ua-udf.jar parseUA.class

Deploy JAR Files to Teradata

DATABASE MYPROJ;
call sqlj.install_jar('cj!gson-2.8.6.jar','gson-2.8.6',0);
call sqlj.install_jar('cj!snakeyaml-1.20.jar','snakeyaml-1.20',0);
call sqlj.install_jar('cj!uap-java-1.4.0.jar','uap-java-1.4.0',0);
call sqlj.install_jar('cj!ua-udf.jar','ua-udf',0);
C:\Apps\Judf>bteqEnter your logon or BTEQ command:
.logon localtd/<your_username>;
Password:<your_password>
DATABASE MYPROJ;*** New default database accepted.
*** Total elapsed time was 1 second.

call sqlj.install_jar('cj!gson-2.8.6.jar','gson-2.8.6',0);
*** Procedure has been executed.

call sqlj.install_jar('cj!snakeyaml-1.20.jar','snakeyaml-1.20',0);
*** Procedure has been executed.call sqlj.install_jar('cj!uap-java-1.4.0.jar','uap-java-1.4.0',0);*** Procedure has been executed.

call sqlj.install_jar('cj!ua-udf.jar','ua-udf',0);
*** Procedure has been executed.
DATABASE MYPROJ;CALL SQLJ.ALTER_JAVA_PATH('ua-udf'
,'(*,SNAKEYAML-1.20)(*,GSON-2.8.6)(*,UAP-JAVA-1.4.0)');
select distinct b.databasename,
a.JarName,
a.PathJarName
from DBC.JAR_JAR_USAGE a , DBC.DBASE b ,DBC.TVM c
where a.DatabaseId =b.DatabaseId
and b.DatabaseId=c.DatabaseId
and c.TableKind='D'
and b.DatabaseNameI='MYPROJ'
;
DATABASE MYPROJ;sel distinct database,a.jarname,b.pathjarname
from SQLJ.JARS a
join SQLJ.JAR_JAR_USAGE b
on a.databaseid=b.databaseid
and a.jarname=b.jarname
;

Install Java UDF to Target Database

DATABASE MYPROJ;REPLACE FUNCTION MYPROJ.UAJSON 
(raw_ua VARCHAR(1024) CHARACTER SET UNICODE)
RETURNS VARCHAR(2000) CHARACTER SET UNICODE
SPECIFIC uajson
LANGUAGE JAVA
NO SQL
NO EXTERNAL DATA
PARAMETER STYLE JAVA
RETURNS NULL ON NULL INPUT
EXTERNAL NAME 'ua-udf:parseUA.uaJson'
;

Test UDF

Mozilla/5.0 (Macintosh; Intel Mac OS X 10_10_5) 
AppleWebKit/537.36 (KHTML, like Gecko) Chrome/48.0.2564.116 Safari/537.36
SEL 
MYPROJ.UAJSON('Mozilla/5.0 (Macintosh; Intel Mac OS X 10_10_5)
AppleWebKit/537.36 (KHTML, like Gecko) Chrome/48.0.2564.116 Safari/537.36');
{
"userAgent": {
"family": "Chrome",
"major": "48",
"minor": "0",
"patch": "2564"
},
"os": {
"family": "Mac OS X",
"major": "10",
"minor": "10",
"patch": "5"
},
"device": {
"family": "Other"
}
}
SEL 
cast(
MYPROJ.UAJSON(
'Mozilla/5.0 (Macintosh; Intel Mac OS X 10_10_5)
AppleWebKit/537.36 (KHTML, like Gecko) Chrome/48.0.2564.116 Safari/537.36'
)
AS JSON
).JsonExtractValue('$.userAgent.family')(VARCHAR(50))
AS User_Agent_Family

Uninstall/Remove Jars & UDF

DATABASE MYPROJ;DROP FUNCTION MYPROJ.UAJSON;call sqlj.alter_java_path('ua-udf','')call sqlj.remove_jar('ua-udf',0)
call sqlj.remove_jar('gson-2.8.6',0);
call sqlj.remove_jar('snakeyaml-1.20',0);
call sqlj.remove_jar('uap-java-1.4.0',0);

Closing Points

--

--

Learner. Interests include Cloud and Devops technologies.

Love podcasts or audiobooks? Learn on the go with our new app.

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store