Intro to Apache Madlib

Jowanza Joseph

@jowanza

Extensions

What can you extend?

  • Type System and Operators
  • User-defined functions and aggregates
  • Storage system and indexes
  • Write-ahead logging and replication
  • Transaction Engine
  • Background worker process
  • Query planner and the query executor
  • Configuration and database metadata

UDF

UDAF

Example

# base36 extension
comment = 'base36 datatype'
default_version = '0.0.1'
relocatable = true

-- complain if script is sourced in psql, rather than via CREATE EXTENSION
\echo Use "CREATE EXTENSION base36" to load this file. \quit
CREATE FUNCTION base36_encode(digits int)
RETURNS text
LANGUAGE plpgsql IMMUTABLE STRICT
  AS $$
    DECLARE
      chars char[];
      ret varchar;
      val int;
    BEGIN
      chars := ARRAY[
                '0','1','2','3','4','5','6','7','8','9','a','b','c','d','e','f','g','h',
                'i','j','k','l','m','n','o','p','q','r','s','t', 'u','v','w','x','y','z'
              ];

      val := digits;
      ret := '';

    WHILE val != 0 LOOP
      ret := chars[(val % 36)+1] || ret;
      val := val / 36;
    END LOOP;

    RETURN(ret);
    END;
  $$;


EXTENSION = base36        # the extensions name
DATA = base36--0.0.1.sql  # script files to install

# postgres build stuff
PG_CONFIG = pg_config
PGXS := $(shell $(PG_CONFIG) --pgxs)
include $(PGXS)
SELECT madlib.logregr_train(
    'patients',                                 -- source table
    'patients_logregr',                         -- output table
    'second_attack',                            -- labels
    'ARRAY[1, treatment, trait_anxiety]',       -- features
    NULL,                                       -- grouping columns
    20,                                         -- max number of iteration
    'irls'                                      -- optimizer
    );

Demo

Resources

  • http://madlib.apache.org
  • Blog post coming soon
  • Jupyter Notebooks

Thanks

Intro to Apache Madlib

By Jowanza Joseph

Intro to Apache Madlib

  • 895