-
Notifications
You must be signed in to change notification settings - Fork 0
SQL API Guide
Created on Feb-18-2010.
The purpose of this document is to define the SQL Interface for MADlib algorithms.
All database objects should be created in the default MADlib
schema. Use MADLIB_SCHEMA as the schema prefix for your tables/views/functions etc.
This literal will be replaced during the installation with the target schema name
(configured by the user in Config.yml). Below code examples use prefix madlib
for illustration purposes only.
This rule applies to tables, views, functions, function parameters, datatypes, operators, etc.
Names should use lower case characters separated with underscores.
A single pass UDF is either an aggregate function or a UDF which does not dispatch any SQL statements.
A multi-pass UDF is a function that reads (and/or modifies) data multiple times using static or dynamic SQL statements.
Module specific routines ideally should have a common prefix based on the module they belong to, for example:
- Multi-Linear Regression function starting with
mregr_:
madlib.mregr_coef(...)- Naive-Bayes classification function starting with
nb_classify_:
madlib.nb_classify_create_view(...)General purpose routines should be named without a reference to any module and should be written inside one of the MADlib support modules (TO DO), for example:
- Function returning the key of the row for which value is maximal:
madlib.argmax (integer key, float8 value)Applies to both single pass user defined functions (UDAs, UDFs) as well as the multi-pass UDFs (driver functions).
Function overloading can be used for different argument data types. For example, function F1 from module M1 can have the following versions:
- TEXT data type example:
madlib.m1_f1( argset1 TEXT)- NUMERIC data type example:
madlib.m1_f1( argset1 BIGINT/FLOAT/etc.)NOTE: If function name overloading is not available on a particular DB platform you can provide conversion code/instructions for other data types or use different function names to perform the same operations on other data types.
Overloading mechanism should also be used for different sets of parameters.
For example, if (reqarg1, ..., reqargN) is a set of required parameters for
function F1 from module M1, then the following definitions would be correct:
- A version for required arguments only:
madlib.m1_f1( reqarg1, ..., reqargN)- A version for both required and optional arguments:
madlib.m1_f1( reqarg1, ..., reqargN, optarg1, ..., optargN)NOTE: If function name overloading is not available on a particular DB platform you can use default argument values (if available) or different function names to perform the same operations on other argument sets.
- Should follow the naming conventions described in section 2.
- Should follow the function overloading rules as described in section 3. On Greenplum and PostgreSQL this can be achieved via pl/Python wrapper UDFs based on the same main Python code.
Arguments should be supplied individually in the function calls, for example:
SELECT madlib.m1_f1( par1 TEXT/INT/etc, par2 TEXT[]/INT[]/etc, ...)Data should be passed to the function by reference to a user table or a view, which:
- Can be located in any schema as long as database user executing the function has the read permissions.
- Should be defined in the method documentation, for example:
TABLE|VIEW (
col_x INT,
col_y FLOAT,
col_z TEXT
)- All columns of the input view/table which are used by the function should be validated inside the function. See section 4.4. for more information.
Execution Summary
Each multi-pass UDF should return a summary TEXT value in the following format:
[MODULE/METHOD/FUCTION NAME] has completed.
Parameters:
- [parameter_name] = [parameter_value] (explanation if needed)
- ...
Results:
- [critical information point]
- ...
- [output table(s) name]
Time elapsed: X minutes Y seconds.
NOTE: it may be useful to add an option of generating this summary in XML or YML format for easy integration.
Output Structure for Small Data Set
When dealing with small output it is recommended to store it in a single predefined table located in madlib schema.
This table should be created during the installation of the module and should have a Primary Key column set
to make the retrieval of the results possible and easy. See section 4.5 for more details.
Example summary:
SV Classification has completed.
Parameters:
- input_table = my_schema.sv_train_data
- model_id / run_id = test1
- parallel = True
Results:
- 320 data points in the model
- table: madlib.sv_model WHERE id = 'test1'
- table: madlib.sv_results WHERE id between 'test10' and 'test11'
Time elapsed: 0 minutes 5 seconds.
Output Structure for Large Data Set
When dealing with large output it's better to direct it into a separate user defined table. This table should be created during the execution of the function and it's name should be returned in the output summary for easy retrieval. This table (or set of tables) must have a unique name in order to avoid collisions with existing database tables. See section 4.5 for more details.
Example summary:
K-Means Clustering has completed.
Parameters:
- k = 10 (number of centroids)
- input_table = my_schema.data_set_1
- goodness = 1 (GOF test on)
- run_id = test1
- output_schema = my_schema
Results:
- analysis based on a sample (9200 out of 10000 points)
- generated 10 centroids (goodness of fit = 2.9787570001)
- table: my_schema.kmeans_out_centroids_test1
- table: my_schema.kmeans_out_points_test1
Time elapsed: 0 minutes 10 seconds.
- ERROR
If the function encounters a problem it should raise an error using plpy.error( message) function. This will ensure
the proper end of the execution and error propagation to the calling environment.
- INFO
During SQL command line execution of long running functions it may be useful to generate some log output to indicate
the current state of the process. This log information could be potentially lost if the SQL calls are generated from
automated scripts or GUI tools, so it should not carry any critical information which is not included in execution summary.
Use plpy.info( message) function to properly generate information log. Example log output:
SQL> select madlib.kmeans_run( 'my_schema.data_set_1', 10, 1, 'run1', 'my_schema');
INFO: Parameters:
INFO: * k = 10 (number of centroids)
INFO: * input_table = my_schema.data_set_1
INFO: * goodness = 1 (GOF test on)
INFO: * run_id = run1
INFO: * output_schema = my_schema
INFO: Seeding 10 centroids...
INFO: Using sample data set for analysis... (9200 out of 10000 points)
INFO: ...Iteration 1
INFO: ...Iteration 2
INFO: Exit reason: fraction of reassigned nodes is smaller than the limit: 0.001
INFO: Expanding cluster assignment to all points...
INFO: Calculating goodness of fit...
...Parameter validation should be performed in each MADlib function with the help of support module (TO DO). List of common validation tests (TO DO):
For simple arguments (scalar, array):
- data type check
def Boolean check_type( arg_value, expected_type) - value range check
def Boolean check_value_range( arg_value, expected_type, min, max) For table/view reference arguments:
- existence check (including schema)
def Boolean check_rel_existence( relation_name) - data size sanity check
def Boolean check_rel_datasize( relation_name, min, max) - expected structure check
def Boolean check_rel_column( relation_name, column_name, data_type)In order to avoid unpleasant situations of overwriting or loosing results MADlib functions should be ready for execution in multi-session or multi-user environment. Hence the following requirements should be met:
-
Input relations (tables or views) should be used for read only purposes.
-
Common output tables should have a PK column set to ensure data safety. The unique ID can be supplied by the user as an argument or, if nor provided, should be auto-generated based on a sequence. Separate sequence should be created for each table during module installation, for example to create a sequence for
madlib.sv_modeltable:
CREATE SEQUENCE madlib_sv_model START 1;Then the unique ID can be generated in the following way:
SELECT nextval('madlib_sv_model');
nextval
---------
1- Individual output tables should have a unique name. It can be achieved by adding a unique ID at the end of the table name. This way output tables from the same module will be named with a similar prefix, but will have a unique suffix to separate different runs.
The unique ID may be validated or auto-generated by a helper function of the following signature (TO DO):
def TEXT get_run_id( run_id)