blob: 32088db7c307e425af9da4e4c714d65150f2edbc [file] [log] [blame]
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/xhtml;charset=UTF-8"/>
<title>MADlib: profile.sql_in Source File</title>
<link href="tabs.css" rel="stylesheet" type="text/css"/>
<link href="doxygen.css" rel="stylesheet" type="text/css" />
<link href="navtree.css" rel="stylesheet" type="text/css"/>
<script type="text/javascript" src="jquery.js"></script>
<script type="text/javascript" src="resize.js"></script>
<script type="text/javascript" src="navtree.js"></script>
<script type="text/javascript">
$(document).ready(initResizable);
</script>
<link href="search/search.css" rel="stylesheet" type="text/css"/>
<script type="text/javascript" src="search/search.js"></script>
<script type="text/javascript">
$(document).ready(function() { searchBox.OnSelectItem(0); });
</script>
<script src="../mathjax/MathJax.js">
MathJax.Hub.Config({
extensions: ["tex2jax.js", "TeX/AMSmath.js", "TeX/AMSsymbols.js"],
jax: ["input/TeX","output/HTML-CSS"],
});
</script>
</head>
<body>
<div id="top"><!-- do not remove this div! -->
<div id="titlearea">
<table cellspacing="0" cellpadding="0">
<tbody>
<tr style="height: 56px;">
<td style="padding-left: 0.5em;">
<div id="projectname">MADlib
&#160;<span id="projectnumber">0.6</span> <span style="font-size:10pt; font-style:italic"><a href="../latest/./profile_8sql__in_source.html"> A newer version is available</a></span>
</div>
<div id="projectbrief">User Documentation</div>
</td>
</tr>
</tbody>
</table>
</div>
<!-- Generated by Doxygen 1.7.5.1 -->
<script type="text/javascript">
var searchBox = new SearchBox("searchBox", "search",false,'Search');
</script>
<script type="text/javascript" src="dynsections.js"></script>
<div id="navrow1" class="tabs">
<ul class="tablist">
<li><a href="index.html"><span>Main&#160;Page</span></a></li>
<li><a href="modules.html"><span>Modules</span></a></li>
<li class="current"><a href="files.html"><span>Files</span></a></li>
<li>
<div id="MSearchBox" class="MSearchBoxInactive">
<span class="left">
<img id="MSearchSelect" src="search/mag_sel.png"
onmouseover="return searchBox.OnSearchSelectShow()"
onmouseout="return searchBox.OnSearchSelectHide()"
alt=""/>
<input type="text" id="MSearchField" value="Search" accesskey="S"
onfocus="searchBox.OnSearchFieldFocus(true)"
onblur="searchBox.OnSearchFieldFocus(false)"
onkeyup="searchBox.OnSearchFieldChange(event)"/>
</span><span class="right">
<a id="MSearchClose" href="javascript:searchBox.CloseResultsWindow()"><img id="MSearchCloseImg" border="0" src="search/close.png" alt=""/></a>
</span>
</div>
</li>
</ul>
</div>
<div id="navrow2" class="tabs2">
<ul class="tablist">
<li><a href="files.html"><span>File&#160;List</span></a></li>
<li><a href="globals.html"><span>File&#160;Members</span></a></li>
</ul>
</div>
</div>
<div id="side-nav" class="ui-resizable side-nav-resizable">
<div id="nav-tree">
<div id="nav-tree-contents">
</div>
</div>
<div id="splitbar" style="-moz-user-select:none;"
class="ui-resizable-handle">
</div>
</div>
<script type="text/javascript">
initNavTree('profile_8sql__in.html','');
</script>
<div id="doc-content">
<div class="header">
<div class="headertitle">
<div class="title">profile.sql_in</div> </div>
</div>
<div class="contents">
<a href="profile_8sql__in.html">Go to the documentation of this file.</a><div class="fragment"><pre class="fragment"><a name="l00001"></a>00001 <span class="comment">/* ----------------------------------------------------------------------- */</span><span class="comment">/** </span>
<a name="l00002"></a>00002 <span class="comment"> *</span>
<a name="l00003"></a>00003 <span class="comment"> * @file profile.sql_in</span>
<a name="l00004"></a>00004 <span class="comment"> *</span>
<a name="l00005"></a>00005 <span class="comment"> * @brief SQL function for single-pass table profiles</span>
<a name="l00006"></a>00006 <span class="comment"> * @date January 2011</span>
<a name="l00007"></a>00007 <span class="comment"> *</span>
<a name="l00008"></a>00008 <span class="comment"> * @sa For a brief introduction to &quot;profiles&quot;, see the module</span>
<a name="l00009"></a>00009 <span class="comment"> * description grp_profile. Cf. also the module grp_sketches.</span>
<a name="l00010"></a>00010 <span class="comment"> *</span>
<a name="l00011"></a>00011 <span class="comment"> */</span><span class="comment">/* ----------------------------------------------------------------------- */</span>
<a name="l00012"></a>00012
<a name="l00013"></a>00013 m4_include(`SQLCommon.m4<span class="stringliteral">&#39;)</span>
<a name="l00014"></a>00014 <span class="stringliteral"></span><span class="comment"></span>
<a name="l00015"></a>00015 <span class="comment">/**</span>
<a name="l00016"></a>00016 <span class="comment">@addtogroup grp_profile</span>
<a name="l00017"></a>00017 <span class="comment"></span>
<a name="l00018"></a>00018 <span class="comment"></span>
<a name="l00019"></a>00019 <span class="comment">@about</span>
<a name="l00020"></a>00020 <span class="comment">This module computes a &quot;profile&quot; of a table or view: a predefined set of </span>
<a name="l00021"></a>00021 <span class="comment">aggregates to be run on each column of a table.</span>
<a name="l00022"></a>00022 <span class="comment"></span>
<a name="l00023"></a>00023 <span class="comment">The following aggregates will be called on every integer column:</span>
<a name="l00024"></a>00024 <span class="comment">- min(), max(), avg()</span>
<a name="l00025"></a>00025 <span class="comment">- madlib.cmsketch_median()</span>
<a name="l00026"></a>00026 <span class="comment">- madlib.cmsketch_depth_histogram()</span>
<a name="l00027"></a>00027 <span class="comment">- madlib.cmsketch_width_histogram()</span>
<a name="l00028"></a>00028 <span class="comment"></span>
<a name="l00029"></a>00029 <span class="comment">And these on non-integer columns:</span>
<a name="l00030"></a>00030 <span class="comment">- madlib.fmsketch_dcount()</span>
<a name="l00031"></a>00031 <span class="comment">- madlib.mfvsketch_quick_histogram()</span>
<a name="l00032"></a>00032 <span class="comment">- madlib.mfvsketch_top_histogram()</span>
<a name="l00033"></a>00033 <span class="comment"></span>
<a name="l00034"></a>00034 <span class="comment">Because the input schema of the table or view is unknown, we need to synthesize </span>
<a name="l00035"></a>00035 <span class="comment">SQL to suit. This is done either via the &lt;c&gt;profile&lt;/c&gt; or &lt;c&gt;profile_full&lt;/c&gt;</span>
<a name="l00036"></a>00036 <span class="comment">user defined function. </span>
<a name="l00037"></a>00037 <span class="comment"></span>
<a name="l00038"></a>00038 <span class="comment">@usage</span>
<a name="l00039"></a>00039 <span class="comment"></span>
<a name="l00040"></a>00040 <span class="comment">- Generate a basic profile information (subset of predefined aggregate functions) </span>
<a name="l00041"></a>00041 <span class="comment"> for all columns of the input table.</span>
<a name="l00042"></a>00042 <span class="comment"> &lt;pre&gt;SELECT * FROM \ref profile( &#39;&lt;em&gt;input_table&lt;/em&gt;&#39;);&lt;/pre&gt;</span>
<a name="l00043"></a>00043 <span class="comment">- Generate a full profile information (all predefined aggregate functions) </span>
<a name="l00044"></a>00044 <span class="comment"> for all columns of the input table.</span>
<a name="l00045"></a>00045 <span class="comment"> &lt;pre&gt;SELECT * FROM \ref profile_full( &#39;&lt;em&gt;input_table&lt;/em&gt;&#39;, &lt;em&gt;buckets&lt;/em&gt;);&lt;/pre&gt;</span>
<a name="l00046"></a>00046 <span class="comment"></span>
<a name="l00047"></a>00047 <span class="comment">@examp</span>
<a name="l00048"></a>00048 <span class="comment"></span>
<a name="l00049"></a>00049 <span class="comment">- For basic profile run:</span>
<a name="l00050"></a>00050 <span class="comment">\verbatim</span>
<a name="l00051"></a>00051 <span class="comment">sql&gt; SELECT * FROM profile( &#39;pg_catalog.pg_tables&#39;);</span>
<a name="l00052"></a>00052 <span class="comment"></span>
<a name="l00053"></a>00053 <span class="comment"> schema_name | table_name | column_name | function | value </span>
<a name="l00054"></a>00054 <span class="comment">-------------+------------+-------------+-------------------+-------</span>
<a name="l00055"></a>00055 <span class="comment"> pg_catalog | pg_tables | * | COUNT() | 105</span>
<a name="l00056"></a>00056 <span class="comment"> pg_catalog | pg_tables | schemaname | fmsketch_dcount() | 6</span>
<a name="l00057"></a>00057 <span class="comment"> pg_catalog | pg_tables | tablename | fmsketch_dcount() | 104</span>
<a name="l00058"></a>00058 <span class="comment"> pg_catalog | pg_tables | tableowner | fmsketch_dcount() | 2</span>
<a name="l00059"></a>00059 <span class="comment"> pg_catalog | pg_tables | tablespace | fmsketch_dcount() | 1</span>
<a name="l00060"></a>00060 <span class="comment"> pg_catalog | pg_tables | hasindexes | fmsketch_dcount() | 2</span>
<a name="l00061"></a>00061 <span class="comment"> pg_catalog | pg_tables | hasrules | fmsketch_dcount() | 1</span>
<a name="l00062"></a>00062 <span class="comment"> pg_catalog | pg_tables | hastriggers | fmsketch_dcount() | 2</span>
<a name="l00063"></a>00063 <span class="comment">(8 rows)</span>
<a name="l00064"></a>00064 <span class="comment">\endverbatim</span>
<a name="l00065"></a>00065 <span class="comment"></span>
<a name="l00066"></a>00066 <span class="comment">- For full profile run: </span>
<a name="l00067"></a>00067 <span class="comment">\verbatim</span>
<a name="l00068"></a>00068 <span class="comment">sql&gt; SELECT * FROM profile_full( &#39;pg_catalog.pg_tables&#39;, 5);</span>
<a name="l00069"></a>00069 <span class="comment"></span>
<a name="l00070"></a>00070 <span class="comment"> schema_name | table_name | column_name | function | value </span>
<a name="l00071"></a>00071 <span class="comment">-------------+------------+-------------+-------------------------------------------------+----------------------------------------------------------------------------------------------------</span>
<a name="l00072"></a>00072 <span class="comment"> pg_catalog | pg_tables | * | COUNT() | 105</span>
<a name="l00073"></a>00073 <span class="comment"> pg_catalog | pg_tables | schemaname | fmsketch_dcount() | 6</span>
<a name="l00074"></a>00074 <span class="comment"> pg_catalog | pg_tables | schemaname | array_collapse(mfvsketch_quick_histogram((),5)) | [0:4]={pg_catalog:68,public:19,information_schema:7,gp_toolkit:5,maddy:5}</span>
<a name="l00075"></a>00075 <span class="comment"> pg_catalog | pg_tables | schemaname | array_collapse(mfvsketch_top_histogram((),5)) | [0:4]={pg_catalog:68,public:19,information_schema:7,gp_toolkit:5,maddy:5}</span>
<a name="l00076"></a>00076 <span class="comment"> pg_catalog | pg_tables | tablename | fmsketch_dcount() | 104</span>
<a name="l00077"></a>00077 <span class="comment"> pg_catalog | pg_tables | tablename | array_collapse(mfvsketch_quick_histogram((),5)) | [0:4]={migrationhistory:2,pg_statistic:1,sql_features:1,sql_implementation_info:1,sql_languages:1}</span>
<a name="l00078"></a>00078 <span class="comment"> pg_catalog | pg_tables | tablename | array_collapse(mfvsketch_top_histogram((),5)) | [0:4]={migrationhistory:2,pg_statistic:1,sql_features:1,sql_implementation_info:1,sql_languages:1}</span>
<a name="l00079"></a>00079 <span class="comment"> pg_catalog | pg_tables | tableowner | fmsketch_dcount() | 2</span>
<a name="l00080"></a>00080 <span class="comment"> pg_catalog | pg_tables | tableowner | array_collapse(mfvsketch_quick_histogram((),5)) | [0:1]={agorajek:104,alex:1}</span>
<a name="l00081"></a>00081 <span class="comment"> pg_catalog | pg_tables | tableowner | array_collapse(mfvsketch_top_histogram((),5)) | [0:1]={agorajek:104,alex:1}</span>
<a name="l00082"></a>00082 <span class="comment"> pg_catalog | pg_tables | tablespace | fmsketch_dcount() | 1</span>
<a name="l00083"></a>00083 <span class="comment"> pg_catalog | pg_tables | tablespace | array_collapse(mfvsketch_quick_histogram((),5)) | [0:0]={pg_global:28}</span>
<a name="l00084"></a>00084 <span class="comment"> pg_catalog | pg_tables | tablespace | array_collapse(mfvsketch_top_histogram((),5)) | [0:0]={pg_global:28}</span>
<a name="l00085"></a>00085 <span class="comment"> pg_catalog | pg_tables | hasindexes | fmsketch_dcount() | 2</span>
<a name="l00086"></a>00086 <span class="comment"> pg_catalog | pg_tables | hasindexes | array_collapse(mfvsketch_quick_histogram((),5)) | [0:1]={t:59,f:46}</span>
<a name="l00087"></a>00087 <span class="comment"> pg_catalog | pg_tables | hasindexes | array_collapse(mfvsketch_top_histogram((),5)) | [0:1]={t:59,f:46}</span>
<a name="l00088"></a>00088 <span class="comment"> pg_catalog | pg_tables | hasrules | fmsketch_dcount() | 1</span>
<a name="l00089"></a>00089 <span class="comment"> pg_catalog | pg_tables | hasrules | array_collapse(mfvsketch_quick_histogram((),5)) | [0:0]={f:105}</span>
<a name="l00090"></a>00090 <span class="comment"> pg_catalog | pg_tables | hasrules | array_collapse(mfvsketch_top_histogram((),5)) | [0:0]={f:105}</span>
<a name="l00091"></a>00091 <span class="comment"> pg_catalog | pg_tables | hastriggers | fmsketch_dcount() | 2</span>
<a name="l00092"></a>00092 <span class="comment"> pg_catalog | pg_tables | hastriggers | array_collapse(mfvsketch_quick_histogram((),5)) | [0:1]={f:102,t:3}</span>
<a name="l00093"></a>00093 <span class="comment"> pg_catalog | pg_tables | hastriggers | array_collapse(mfvsketch_top_histogram((),5)) | [0:1]={f:102,t:3}</span>
<a name="l00094"></a>00094 <span class="comment">(22 rows)</span>
<a name="l00095"></a>00095 <span class="comment">\endverbatim</span>
<a name="l00096"></a>00096 <span class="comment"></span>
<a name="l00097"></a>00097 <span class="comment">@implementation</span>
<a name="l00098"></a>00098 <span class="comment"></span>
<a name="l00099"></a>00099 <span class="comment">Because some of the aggregate functions used in profile return multi-dimensional </span>
<a name="l00100"></a>00100 <span class="comment">arrays, which are not easily handled in pl/python, we are using </span>
<a name="l00101"></a>00101 <span class="comment">&lt;c&gt;array_collapse&lt;/c&gt; function to collaps the n-dim arrays to 1-dim arrays. </span>
<a name="l00102"></a>00102 <span class="comment">All values of 2 and upper dimensions are separated with &#39;:&#39; character.</span>
<a name="l00103"></a>00103 <span class="comment"></span>
<a name="l00104"></a>00104 <span class="comment">@sa File profile.sql_in documenting SQL functions.</span>
<a name="l00105"></a>00105 <span class="comment">*/</span>
<a name="l00106"></a>00106
<a name="l00107"></a>00107 CREATE TYPE MADLIB_SCHEMA.profile_result AS (
<a name="l00108"></a>00108 schema_name TEXT
<a name="l00109"></a>00109 , table_name TEXT
<a name="l00110"></a>00110 , column_name TEXT
<a name="l00111"></a>00111 , function TEXT
<a name="l00112"></a>00112 , value TEXT
<a name="l00113"></a>00113 );
<a name="l00114"></a>00114 <span class="comment"></span>
<a name="l00115"></a>00115 <span class="comment">/**</span>
<a name="l00116"></a>00116 <span class="comment"> * @brief Compute a simple &quot;profile&quot; of a table or view</span>
<a name="l00117"></a>00117 <span class="comment"> *</span>
<a name="l00118"></a>00118 <span class="comment"> * @param input_table table name to analyze</span>
<a name="l00119"></a>00119 <span class="comment"> * @return Set of PROFILE_RESULT type</span>
<a name="l00120"></a>00120 <span class="comment"> */</span>
<a name="l00121"></a>00121 CREATE OR REPLACE FUNCTION MADLIB_SCHEMA.profile( input_table text)
<a name="l00122"></a>00122 RETURNS SETOF MADLIB_SCHEMA.profile_result
<a name="l00123"></a>00123 AS $$
<a name="l00124"></a>00124
<a name="l00125"></a>00125 PythonFunctionBodyOnly(`data_profile&#39;, `<a class="code" href="profile_8sql__in.html#a22f0136bb3d7740262b13030a2925a80" title="Compute a simple &quot;profile&quot; of a table or view.">profile</a><span class="stringliteral">&#39;)</span>
<a name="l00126"></a>00126 <span class="stringliteral"> </span>
<a name="l00127"></a>00127 <span class="stringliteral"> # schema_madlib comes from PythonFunctionBodyOnly</span>
<a name="l00128"></a>00128 <span class="stringliteral"> return profile.profile( schema_madlib, input_table, &#39;</span>bas<span class="stringliteral">&#39;, None);</span>
<a name="l00129"></a>00129 <span class="stringliteral"></span>
<a name="l00130"></a>00130 <span class="stringliteral">$$ LANGUAGE plpythonu;</span>
<a name="l00131"></a>00131 <span class="stringliteral"></span><span class="comment"></span>
<a name="l00132"></a>00132 <span class="comment">/**</span>
<a name="l00133"></a>00133 <span class="comment"> * @brief Compute a full &quot;profile&quot; of a table or view</span>
<a name="l00134"></a>00134 <span class="comment"> *</span>
<a name="l00135"></a>00135 <span class="comment"> * @param input_table table name to analyze</span>
<a name="l00136"></a>00136 <span class="comment"> * @param buckets number of buckets for histogram functions</span>
<a name="l00137"></a>00137 <span class="comment"> * @return Set of PROFILE_RESULT type </span>
<a name="l00138"></a>00138 <span class="comment"> */</span>
<a name="l00139"></a>00139 CREATE OR REPLACE FUNCTION MADLIB_SCHEMA.profile_full( input_table text, buckets integer)
<a name="l00140"></a>00140 RETURNS SETOF MADLIB_SCHEMA.profile_result
<a name="l00141"></a>00141 AS $$
<a name="l00142"></a>00142
<a name="l00143"></a><a class="code" href="profile_8sql__in.html#a22f0136bb3d7740262b13030a2925a80">00143</a> PythonFunctionBodyOnly(`data_profile&#39;, `<a class="code" href="profile_8sql__in.html#a22f0136bb3d7740262b13030a2925a80" title="Compute a simple &quot;profile&quot; of a table or view.">profile</a><span class="stringliteral">&#39;)</span>
<a name="l00144"></a>00144 <span class="stringliteral"> </span>
<a name="l00145"></a>00145 <span class="stringliteral"> # schema_madlib comes from PythonFunctionBodyOnly</span>
<a name="l00146"></a>00146 <span class="stringliteral"> return profile.profile( schema_madlib, input_table, &#39;</span>all<span class="stringliteral">&#39;, buckets);</span>
<a name="l00147"></a>00147 <span class="stringliteral"></span>
<a name="l00148"></a>00148 <span class="stringliteral">$$ LANGUAGE plpythonu;</span>
<a name="l00149"></a>00149 <span class="stringliteral"></span><span class="comment"></span>
<a name="l00150"></a>00150 <span class="comment">/**</span>
<a name="l00151"></a>00151 <span class="comment"> * @brief Collapses n-dimensional Arrays to 1-dim Array, so they can be read in</span>
<a name="l00152"></a>00152 <span class="comment"> * pl/python can read it. Otherwise we would get </span>
<a name="l00153"></a>00153 <span class="comment"> * ERROR: cannot convert multidimensional array to Python list</span>
<a name="l00154"></a>00154 <span class="comment"> *</span>
<a name="l00155"></a>00155 <span class="comment"> * @param input Mult-dim array</span>
<a name="l00156"></a>00156 <span class="comment"> * @return One dimensional anyarray</span>
<a name="l00157"></a>00157 <span class="comment"> */</span>
<a name="l00158"></a>00158 CREATE OR REPLACE FUNCTION MADLIB_SCHEMA.array_collapse( input anyarray)
<a name="l00159"></a>00159 RETURNS anyarray
<a name="l00160"></a>00160 AS $$
<a name="l00161"></a><a class="code" href="profile_8sql__in.html#a879e445d74412b093d411ed9a7180a1e">00161</a> DECLARE
<a name="l00162"></a>00162 x TEXT[];
<a name="l00163"></a>00163 i INTEGER;
<a name="l00164"></a>00164 BEGIN
<a name="l00165"></a>00165 IF array_lower( input, 1) is NULL OR array_upper( input, 1) is NULL THEN
<a name="l00166"></a>00166 x[1] = NULL;
<a name="l00167"></a>00167 RETURN x;
<a name="l00168"></a>00168 END IF;
<a name="l00169"></a>00169 FOR i IN array_lower( input, 1)..array_upper( input, 1) LOOP
<a name="l00170"></a>00170 x[i] := array_to_string( input[i:i][array_lower( input, 2):array_upper( input, 2)], &#39;:<span class="stringliteral">&#39;);</span>
<a name="l00171"></a>00171 <span class="stringliteral"> END LOOP;</span>
<a name="l00172"></a>00172 <span class="stringliteral"> RETURN x;</span>
<a name="l00173"></a>00173 <span class="stringliteral">END;</span>
<a name="l00174"></a>00174 <span class="stringliteral">$$ LANGUAGE plpgsql;</span>
</pre></div></div>
</div>
<div id="nav-path" class="navpath">
<ul>
<li class="navelem"><a class="el" href="profile_8sql__in.html">profile.sql_in</a> </li>
<!-- window showing the filter options -->
<div id="MSearchSelectWindow"
onmouseover="return searchBox.OnSearchSelectShow()"
onmouseout="return searchBox.OnSearchSelectHide()"
onkeydown="return searchBox.OnSearchSelectKey(event)">
<a class="SelectItem" href="javascript:void(0)" onclick="searchBox.OnSelectItem(0)"><span class="SelectionMark">&#160;</span>All</a><a class="SelectItem" href="javascript:void(0)" onclick="searchBox.OnSelectItem(1)"><span class="SelectionMark">&#160;</span>Files</a><a class="SelectItem" href="javascript:void(0)" onclick="searchBox.OnSelectItem(2)"><span class="SelectionMark">&#160;</span>Functions</a></div>
<!-- iframe showing the search results (closed by default) -->
<div id="MSearchResultsWindow">
<iframe src="javascript:void(0)" frameborder="0"
name="MSearchResults" id="MSearchResults">
</iframe>
</div>
<li class="footer">Generated on Tue Apr 2 2013 14:57:03 for MADlib by
<a href="http://www.doxygen.org/index.html">
<img class="footer" src="doxygen.png" alt="doxygen"/></a> 1.7.5.1 </li>
</ul>
</div>
</body>
</html>