IBM® Netezza® Analytics Release 3.0.2.0 User-Defined Analytic Process Developer's Guide Revised: June 30, 2014 Part Number 00J2009-04 Rev. 3 Note: Before using this information and the product that it supports, read the information in Notices and Trademark, on page 367. © Copyright IBM Corporation 2011, 2014. US Government Users Restricted Rights – Use, duplication or disclosure restricted by GSA ADP Schedule Contract with IBM Corp. Contents Preface Audience for This Guide..................................................................................................................xix Purpose of This Guide.....................................................................................................................xix Symbols and Conventions...............................................................................................................xix If You Need Help..............................................................................................................................xx Comments on the Documentation..................................................................................................xx 1 Introduction Sample Dataset Configuration...............................................................................................................21 SQL Functions.........................................................................................................................................21 User Defined Process Overview.............................................................................................................21 UDXs................................................................................................................................................22 Analytic Executables (AEs)...............................................................................................................23 Programming Model..............................................................................................................................26 Callback Programming Model.........................................................................................................26 Choosing between AEs and UDXs..........................................................................................................26 SQL Invocation of AEs......................................................................................................................28 2 Developer Principles Understanding the AE Runtime System.................................................................................................29 Marshaling Data..............................................................................................................................29 Accessing the AE Export Directory Tree.................................................................................................30 Utilities Directory............................................................................................................................30 Applications Directory.....................................................................................................................30 Products Directory...........................................................................................................................31 Workspace Directory.......................................................................................................................31 Adapters Directory..........................................................................................................................31 Languages Directory........................................................................................................................31 Core Directory.................................................................................................................................31 Sysroot Directory.............................................................................................................................32 Compilation and Registration Overview................................................................................................33 User Definition................................................................................................................................33 Extending compile_ae and register_ae...........................................................................................33 iii Compiling and Deploying Analytic Executables.....................................................................................33 User Configuration before Compilation..........................................................................................34 File Locations...................................................................................................................................34 Common Options............................................................................................................................34 Complete List of compile_ae Options.............................................................................................37 R AE Compilation.............................................................................................................................39 Registering Analytic Executables............................................................................................................40 Common Options............................................................................................................................40 Options for Remote AEs..................................................................................................................43 AE Environment Variables and register_ae ....................................................................................44 Table Function Options....................................................................................................................47 Aggregate Function Options............................................................................................................47 NPS System Shared Library Dependencies......................................................................................48 Dynamic AE Environment Variables................................................................................................48 Optimization....................................................................................................................................48 Complete List of register_ae Options..............................................................................................48 R Language Output Modes..............................................................................................................52 Accessing the Local Drive Space on the Machine...........................................................................53 3 Running Function Analytic Executables Calling an AE from SQL...........................................................................................................................55 Calling a Scalar Function..................................................................................................................55 Calling a Table Function...................................................................................................................55 Calling an Aggregate Function.........................................................................................................56 Using AEs to Exceed NPS SQL Function Argument Limits......................................................................56 Understanding Locus Control and Analytic Executables........................................................................57 Scalar Functions...............................................................................................................................57 Table Functions................................................................................................................................58 Aggregate Functions........................................................................................................................58 Introduction to Shapers and Sizers........................................................................................................58 Shapers............................................................................................................................................58 Sizers................................................................................................................................................59 Introduction to AE Language APIs .........................................................................................................59 Low-level API...................................................................................................................................59 Initialization API...............................................................................................................................60 Data Connections............................................................................................................................60 Record and Data Type Support........................................................................................................61 Advanced NPS Features ..................................................................................................................61 The API Roadmap............................................................................................................................61 4 Working with Examples Assumptions for Working with Examples........................................................................................63 Editing Files......................................................................................................................................63 iv 5 Scalar Function Examples C Language Scalar Function...................................................................................................................65 Code................................................................................................................................................65 Compilation.....................................................................................................................................69 Registration.....................................................................................................................................70 Running...........................................................................................................................................70 C++ Language Scalar Function...............................................................................................................70 Code................................................................................................................................................70 Compilation.....................................................................................................................................76 Registration.....................................................................................................................................76 Running...........................................................................................................................................76 Java Language Scalar Function...............................................................................................................76 Code................................................................................................................................................76 Compilation.....................................................................................................................................81 Registration.....................................................................................................................................82 Running...........................................................................................................................................82 Fortran Language Scalar Function..........................................................................................................82 Code................................................................................................................................................82 Compilation.....................................................................................................................................84 Registration.....................................................................................................................................84 Running...........................................................................................................................................85 Python Language Scalar Function..........................................................................................................85 Code................................................................................................................................................85 Compilation.....................................................................................................................................86 Deployment.....................................................................................................................................86 Registration.....................................................................................................................................86 Running...........................................................................................................................................87 Perl Language Scalar Function...............................................................................................................87 Code................................................................................................................................................87 Compilation.....................................................................................................................................89 Deployment.....................................................................................................................................89 Registration.....................................................................................................................................89 Running...........................................................................................................................................89 R Language Scalar Function 1................................................................................................................90 Code................................................................................................................................................90 Compilation.....................................................................................................................................90 Registration.....................................................................................................................................91 Running...........................................................................................................................................91 R Language Scalar Function 2................................................................................................................91 Code................................................................................................................................................91 Compilation.....................................................................................................................................92 6 Converting to a Simple Table Function Examples v C Language Conversion..........................................................................................................................93 Code................................................................................................................................................93 Compilation.....................................................................................................................................93 Registration.....................................................................................................................................93 Running...........................................................................................................................................93 C++ Language Conversion......................................................................................................................94 Code................................................................................................................................................94 Compilation.....................................................................................................................................94 Registration.....................................................................................................................................94 Running...........................................................................................................................................94 Java Language Conversion.....................................................................................................................94 Code................................................................................................................................................95 Compilation.....................................................................................................................................95 Registration.....................................................................................................................................95 Running...........................................................................................................................................95 Fortran Language Conversion................................................................................................................95 Code................................................................................................................................................95 Compilation.....................................................................................................................................95 Registration.....................................................................................................................................95 Running...........................................................................................................................................96 Python Language Conversion.................................................................................................................96 Code................................................................................................................................................96 Deployment.....................................................................................................................................96 Registration.....................................................................................................................................96 Running...........................................................................................................................................96 Perl Language Conversion......................................................................................................................97 Code................................................................................................................................................97 Deployment.....................................................................................................................................97 Registration.....................................................................................................................................97 Running...........................................................................................................................................97 R Language Conversion..........................................................................................................................97 Code................................................................................................................................................97 Compilation.....................................................................................................................................97 Registration.....................................................................................................................................98 Running...........................................................................................................................................98 7 Table Function (Simulated Row Function) Examples C Language Simulated Row Function.....................................................................................................99 Code................................................................................................................................................99 Compilation...................................................................................................................................102 Registration...................................................................................................................................102 Running.........................................................................................................................................102 vi C++ Language Simulated Row Function...............................................................................................102 Code..............................................................................................................................................102 Compilation...................................................................................................................................104 Registration...................................................................................................................................104 Running.........................................................................................................................................104 Java Language Simulated Row Function..............................................................................................104 Code..............................................................................................................................................104 Compilation...................................................................................................................................106 Registration...................................................................................................................................106 Running.........................................................................................................................................106 Fortran Language Simulated Row Function.........................................................................................107 Code..............................................................................................................................................107 Compilation...................................................................................................................................107 Registration...................................................................................................................................107 Running.........................................................................................................................................108 Python Language Simulated Row Function.........................................................................................108 Code..............................................................................................................................................108 Deployment...................................................................................................................................108 Registration...................................................................................................................................108 Running.........................................................................................................................................108 Perl Language Simulated Row Function...............................................................................................109 Code..............................................................................................................................................109 Deployment...................................................................................................................................109 Registration...................................................................................................................................109 Running.........................................................................................................................................109 R Language Simulated Row Function...................................................................................................110 Code..............................................................................................................................................110 Compilation...................................................................................................................................110 Registration...................................................................................................................................110 Running.........................................................................................................................................110 8 Simple Table Function Examples C Language Table Function..................................................................................................................112 Code..............................................................................................................................................112 Compilation...................................................................................................................................115 Registration...................................................................................................................................115 Running.........................................................................................................................................115 C++ Language Table Function..............................................................................................................116 Code..............................................................................................................................................116 Compilation...................................................................................................................................117 Registration...................................................................................................................................118 Running.........................................................................................................................................118 Java Language Table Function..............................................................................................................118 vii Code..............................................................................................................................................118 Compilation...................................................................................................................................120 Registration...................................................................................................................................120 Running.........................................................................................................................................120 Fortran Language Table Function ........................................................................................................121 Code..............................................................................................................................................121 Compilation...................................................................................................................................122 Registration...................................................................................................................................122 Running.........................................................................................................................................122 Python Language Table Function.........................................................................................................122 Code..............................................................................................................................................122 Deployment...................................................................................................................................123 Registration...................................................................................................................................123 Running.........................................................................................................................................123 Perl Language Table Function..............................................................................................................123 Code..............................................................................................................................................123 Deployment...................................................................................................................................124 Registration...................................................................................................................................124 Running.........................................................................................................................................124 R Language Table Function..................................................................................................................125 Code..............................................................................................................................................125 Compilation...................................................................................................................................125 Registration...................................................................................................................................125 Running.........................................................................................................................................125 Additional R Language Table Functions...............................................................................................126 Table Function Example 1..............................................................................................................126 Table Function Example 2..............................................................................................................127 Table Function Example 3..............................................................................................................127 9 Shapers and Sizers Examples C Language Shapers and Sizers............................................................................................................129 Code..............................................................................................................................................129 Compilation...................................................................................................................................132 Registration...................................................................................................................................132 Running.........................................................................................................................................133 C++ Language Shapers and Sizers........................................................................................................133 Code..............................................................................................................................................133 Compilation...................................................................................................................................135 Registration...................................................................................................................................135 Running.........................................................................................................................................136 Java Language Shapers and Sizers........................................................................................................136 Code..............................................................................................................................................136 Compilation...................................................................................................................................138 viii Registration...................................................................................................................................138 Running.........................................................................................................................................138 Fortran Language Shapers and Sizers..................................................................................................139 Code..............................................................................................................................................139 Compilation...................................................................................................................................140 Registration...................................................................................................................................140 Running.........................................................................................................................................141 Python Language Shapers and Sizers...................................................................................................141 Code..............................................................................................................................................141 Deployment...................................................................................................................................142 Registration...................................................................................................................................142 Running.........................................................................................................................................142 Perl Language Shapers and Sizers........................................................................................................142 Code..............................................................................................................................................143 Deployment...................................................................................................................................143 Registration...................................................................................................................................144 R Language Shapers and Sizers............................................................................................................144 Using a Shaper...............................................................................................................................144 Code..............................................................................................................................................144 Compilation...................................................................................................................................145 Registration...................................................................................................................................145 Running.........................................................................................................................................145 Another Example...........................................................................................................................145 Compilation...................................................................................................................................146 Registration...................................................................................................................................146 Running.........................................................................................................................................147 10 Aggregate AE Examples C Language Aggregates........................................................................................................................149 Code..............................................................................................................................................149 Compilation...................................................................................................................................152 Registration...................................................................................................................................152 Running.........................................................................................................................................152 C++ Language Aggregates....................................................................................................................153 Code..............................................................................................................................................153 Compilation...................................................................................................................................158 Registration...................................................................................................................................158 Running.........................................................................................................................................159 Java Language Aggregates....................................................................................................................159 Code..............................................................................................................................................159 Compilation...................................................................................................................................161 Registration...................................................................................................................................161 Running.........................................................................................................................................161 ix Fortran Language Aggregates..............................................................................................................162 Code..............................................................................................................................................162 Compilation...................................................................................................................................164 Registration...................................................................................................................................164 Running.........................................................................................................................................164 Python Language Aggregates...............................................................................................................164 Code..............................................................................................................................................165 Deployment...................................................................................................................................166 Registration...................................................................................................................................166 Running.........................................................................................................................................166 Perl Language Aggregates....................................................................................................................167 Code..............................................................................................................................................167 Deployment...................................................................................................................................170 Registration...................................................................................................................................170 Running.........................................................................................................................................170 R Language Aggregates 1.....................................................................................................................171 Concepts........................................................................................................................................171 Code..............................................................................................................................................172 Compilation...................................................................................................................................172 Registration...................................................................................................................................172 Running.........................................................................................................................................173 R Language Aggregates 2.....................................................................................................................173 Code..............................................................................................................................................173 Compilation...................................................................................................................................174 Registration...................................................................................................................................174 Running.........................................................................................................................................174 11 Advanced Developer Principles Introducing AE Environment Variables................................................................................................177 Commonly Used AE Environment Variable .........................................................................................178 Adding Application-Specific Environment Variables.....................................................................178 Command Line Arguments............................................................................................................178 General Common Variables...........................................................................................................178 Compressed Columns....................................................................................................................179 Row Buffering................................................................................................................................179 Debugging.....................................................................................................................................179 Remote AE.....................................................................................................................................180 AE Environment Variable Prefixes........................................................................................................181 Setting Dynamic AE Environment Variables.........................................................................................182 More about Dynamic AE Environment Variables..........................................................................183 Order of Variable Parsing.....................................................................................................................184 Working with AE Shared Libraries........................................................................................................185 Understanding Row Buffering..............................................................................................................187 x
Description: