Google Git
Sign in
apache / madlib-site / 5455828f6b559fc1057ac4019a665185cc458fce / . / community-artifacts / Graph / Multicolumn-v1.ipynb
blob: 17bb852b306eb185fad6b6ce833038fb0688c024 [file] [log] [blame]
{
"cells": [
{
"cell_type": "markdown",
"metadata": {},
"source": [
"# Multicolumn Identifier Support for Graph functions\n",
"MADlib graph functions are designed to have a vertex and an edge table. Each vertex has an id in the vertex table and a pair of ids in the edge table define a directed edge. Starting with version 1.20.0, multiple columns can be used as the vertex identifier for select graph functions."
]
},
{
"cell_type": "code",
"execution_count": 1,
"metadata": {},
"outputs": [],
"source": [
"%load_ext sql"
]
},
{
"cell_type": "code",
"execution_count": 2,
"metadata": {},
"outputs": [
{
"data": {
"text/plain": [
"u'Connected: okislal@madlib'"
]
},
"execution_count": 2,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"%sql postgresql://okislal@localhost:6600/madlib\n",
" \n",
"# PostgreSQL local\n",
"#%sql postgresql://fmcquillan@localhost:5432/madlib"
]
},
{
"cell_type": "code",
"execution_count": 3,
"metadata": {},
"outputs": [
{
"name": "stdout",
"output_type": "stream",
"text": [
" * postgresql://okislal@localhost:6600/madlib\n",
"1 rows affected.\n"
]
},
{
"data": {
"text/html": [
"<table>\n",
" <tr>\n",
" <th>version</th>\n",
" </tr>\n",
" <tr>\n",
" <td>MADlib version: 1.21.0-dev, git revision: rel/v1.20.0-4-g195895cc, cmake configuration time: Thu Aug 18 13:37:23 UTC 2022, build type: RelWithDebInfo, build system: Darwin-20.6.0, C compiler: Clang, C++ compiler: Clang</td>\n",
" </tr>\n",
"</table>"
],
"text/plain": [
"[(u'MADlib version: 1.21.0-dev, git revision: rel/v1.20.0-4-g195895cc, cmake configuration time: Thu Aug 18 13:37:23 UTC 2022, build type: RelWithDebInfo, build system: Darwin-20.6.0, C compiler: Clang, C++ compiler: Clang',)]"
]
},
"execution_count": 3,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"%sql select madlib.version();\n",
"#%sql select version();"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"# 1. Create vertex and edge tables"
]
},
{
"cell_type": "code",
"execution_count": 4,
"metadata": {},
"outputs": [
{
"name": "stdout",
"output_type": "stream",
"text": [
" * postgresql://okislal@localhost:6600/madlib\n",
"Done.\n",
"Done.\n",
"Done.\n",
"7 rows affected.\n",
"22 rows affected.\n",
"22 rows affected.\n"
]
},
{
"data": {
"text/html": [
"<table>\n",
" <tr>\n",
" <th>conn_src_major</th>\n",
" <th>conn_dest_major</th>\n",
" <th>user_id_major</th>\n",
" <th>conn_src_minor</th>\n",
" <th>conn_dest_minor</th>\n",
" <th>user_id_minor</th>\n",
" </tr>\n",
" <tr>\n",
" <td>0</td>\n",
" <td>2</td>\n",
" <td>1</td>\n",
" <td>0</td>\n",
" <td>2</td>\n",
" <td>1</td>\n",
" </tr>\n",
" <tr>\n",
" <td>0</td>\n",
" <td>4</td>\n",
" <td>1</td>\n",
" <td>0</td>\n",
" <td>4</td>\n",
" <td>1</td>\n",
" </tr>\n",
" <tr>\n",
" <td>0</td>\n",
" <td>4</td>\n",
" <td>2</td>\n",
" <td>0</td>\n",
" <td>4</td>\n",
" <td>2</td>\n",
" </tr>\n",
" <tr>\n",
" <td>0</td>\n",
" <td>2</td>\n",
" <td>2</td>\n",
" <td>0</td>\n",
" <td>2</td>\n",
" <td>2</td>\n",
" </tr>\n",
" <tr>\n",
" <td>0</td>\n",
" <td>1</td>\n",
" <td>2</td>\n",
" <td>0</td>\n",
" <td>1</td>\n",
" <td>2</td>\n",
" </tr>\n",
" <tr>\n",
" <td>0</td>\n",
" <td>1</td>\n",
" <td>1</td>\n",
" <td>0</td>\n",
" <td>1</td>\n",
" <td>1</td>\n",
" </tr>\n",
" <tr>\n",
" <td>1</td>\n",
" <td>3</td>\n",
" <td>2</td>\n",
" <td>1</td>\n",
" <td>3</td>\n",
" <td>2</td>\n",
" </tr>\n",
" <tr>\n",
" <td>1</td>\n",
" <td>3</td>\n",
" <td>1</td>\n",
" <td>1</td>\n",
" <td>3</td>\n",
" <td>1</td>\n",
" </tr>\n",
" <tr>\n",
" <td>1</td>\n",
" <td>2</td>\n",
" <td>1</td>\n",
" <td>1</td>\n",
" <td>2</td>\n",
" <td>1</td>\n",
" </tr>\n",
" <tr>\n",
" <td>1</td>\n",
" <td>2</td>\n",
" <td>2</td>\n",
" <td>1</td>\n",
" <td>2</td>\n",
" <td>2</td>\n",
" </tr>\n",
" <tr>\n",
" <td>2</td>\n",
" <td>3</td>\n",
" <td>1</td>\n",
" <td>2</td>\n",
" <td>3</td>\n",
" <td>1</td>\n",
" </tr>\n",
" <tr>\n",
" <td>2</td>\n",
" <td>6</td>\n",
" <td>1</td>\n",
" <td>2</td>\n",
" <td>6</td>\n",
" <td>1</td>\n",
" </tr>\n",
" <tr>\n",
" <td>2</td>\n",
" <td>5</td>\n",
" <td>1</td>\n",
" <td>2</td>\n",
" <td>5</td>\n",
" <td>1</td>\n",
" </tr>\n",
" <tr>\n",
" <td>2</td>\n",
" <td>3</td>\n",
" <td>2</td>\n",
" <td>2</td>\n",
" <td>3</td>\n",
" <td>2</td>\n",
" </tr>\n",
" <tr>\n",
" <td>3</td>\n",
" <td>0</td>\n",
" <td>2</td>\n",
" <td>3</td>\n",
" <td>0</td>\n",
" <td>2</td>\n",
" </tr>\n",
" <tr>\n",
" <td>3</td>\n",
" <td>0</td>\n",
" <td>1</td>\n",
" <td>3</td>\n",
" <td>0</td>\n",
" <td>1</td>\n",
" </tr>\n",
" <tr>\n",
" <td>4</td>\n",
" <td>0</td>\n",
" <td>1</td>\n",
" <td>4</td>\n",
" <td>0</td>\n",
" <td>1</td>\n",
" </tr>\n",
" <tr>\n",
" <td>4</td>\n",
" <td>0</td>\n",
" <td>2</td>\n",
" <td>4</td>\n",
" <td>0</td>\n",
" <td>2</td>\n",
" </tr>\n",
" <tr>\n",
" <td>5</td>\n",
" <td>6</td>\n",
" <td>2</td>\n",
" <td>5</td>\n",
" <td>6</td>\n",
" <td>2</td>\n",
" </tr>\n",
" <tr>\n",
" <td>5</td>\n",
" <td>6</td>\n",
" <td>1</td>\n",
" <td>5</td>\n",
" <td>6</td>\n",
" <td>1</td>\n",
" </tr>\n",
" <tr>\n",
" <td>6</td>\n",
" <td>3</td>\n",
" <td>1</td>\n",
" <td>6</td>\n",
" <td>3</td>\n",
" <td>1</td>\n",
" </tr>\n",
" <tr>\n",
" <td>6</td>\n",
" <td>3</td>\n",
" <td>2</td>\n",
" <td>6</td>\n",
" <td>3</td>\n",
" <td>2</td>\n",
" </tr>\n",
"</table>"
],
"text/plain": [
"[(0L, 2L, 1L, 0L, 2L, 1L),\n",
" (0L, 4L, 1L, 0L, 4L, 1L),\n",
" (0L, 4L, 2L, 0L, 4L, 2L),\n",
" (0L, 2L, 2L, 0L, 2L, 2L),\n",
" (0L, 1L, 2L, 0L, 1L, 2L),\n",
" (0L, 1L, 1L, 0L, 1L, 1L),\n",
" (1L, 3L, 2L, 1L, 3L, 2L),\n",
" (1L, 3L, 1L, 1L, 3L, 1L),\n",
" (1L, 2L, 1L, 1L, 2L, 1L),\n",
" (1L, 2L, 2L, 1L, 2L, 2L),\n",
" (2L, 3L, 1L, 2L, 3L, 1L),\n",
" (2L, 6L, 1L, 2L, 6L, 1L),\n",
" (2L, 5L, 1L, 2L, 5L, 1L),\n",
" (2L, 3L, 2L, 2L, 3L, 2L),\n",
" (3L, 0L, 2L, 3L, 0L, 2L),\n",
" (3L, 0L, 1L, 3L, 0L, 1L),\n",
" (4L, 0L, 1L, 4L, 0L, 1L),\n",
" (4L, 0L, 2L, 4L, 0L, 2L),\n",
" (5L, 6L, 2L, 5L, 6L, 2L),\n",
" (5L, 6L, 1L, 5L, 6L, 1L),\n",
" (6L, 3L, 1L, 6L, 3L, 1L),\n",
" (6L, 3L, 2L, 6L, 3L, 2L)]"
]
},
"execution_count": 4,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"%%sql \n",
"DROP TABLE IF EXISTS vertex_multicol, edge_multicol;\n",
"CREATE TABLE vertex_multicol(\n",
" node_id_major BIGINT,\n",
" node_id_minor BIGINT\n",
");\n",
"CREATE TABLE edge_multicol(\n",
" conn_src_major BIGINT,\n",
" conn_dest_major BIGINT,\n",
" user_id_major BIGINT,\n",
" conn_src_minor BIGINT,\n",
" conn_dest_minor BIGINT,\n",
" user_id_minor BIGINT\n",
");\n",
"INSERT INTO vertex_multicol VALUES\n",
"(0, 0),\n",
"(1, 1),\n",
"(2, 2),\n",
"(3, 3),\n",
"(4, 4),\n",
"(5, 5),\n",
"(6, 6);\n",
"INSERT INTO edge_multicol VALUES\n",
"(0, 1, 1, 0, 1, 1),\n",
"(0, 2, 1, 0, 2, 1),\n",
"(0, 4, 1, 0, 4, 1),\n",
"(1, 2, 1, 1, 2, 1),\n",
"(1, 3, 1, 1, 3, 1),\n",
"(2, 3, 1, 2, 3, 1),\n",
"(2, 5, 1, 2, 5, 1),\n",
"(2, 6, 1, 2, 6, 1),\n",
"(3, 0, 1, 3, 0, 1),\n",
"(4, 0, 1, 4, 0, 1),\n",
"(5, 6, 1, 5, 6, 1),\n",
"(6, 3, 1, 6, 3, 1),\n",
"(0, 1, 2, 0, 1, 2),\n",
"(0, 2, 2, 0, 2, 2),\n",
"(0, 4, 2, 0, 4, 2),\n",
"(1, 2, 2, 1, 2, 2),\n",
"(1, 3, 2, 1, 3, 2),\n",
"(2, 3, 2, 2, 3, 2),\n",
"(3, 0, 2, 3, 0, 2),\n",
"(4, 0, 2, 4, 0, 2),\n",
"(5, 6, 2, 5, 6, 2),\n",
"(6, 3, 2, 6, 3, 2);\n",
"\n",
"SELECT * from edge_multicol ORDER BY conn_src_major;"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"# 2. PageRank"
]
},
{
"cell_type": "code",
"execution_count": 5,
"metadata": {},
"outputs": [
{
"name": "stdout",
"output_type": "stream",
"text": [
" * postgresql://okislal@localhost:6600/madlib\n",
"Done.\n",
"1 rows affected.\n",
"14 rows affected.\n"
]
},
{
"data": {
"text/html": [
"<table>\n",
" <tr>\n",
" <th>user_id_major</th>\n",
" <th>user_id_minor</th>\n",
" <th>id</th>\n",
" <th>pagerank</th>\n",
" </tr>\n",
" <tr>\n",
" <td>1</td>\n",
" <td>1</td>\n",
" <td>[0L, 0L]</td>\n",
" <td>0.270635964386</td>\n",
" </tr>\n",
" <tr>\n",
" <td>1</td>\n",
" <td>1</td>\n",
" <td>[2L, 2L]</td>\n",
" <td>0.184232398514</td>\n",
" </tr>\n",
" <tr>\n",
" <td>1</td>\n",
" <td>1</td>\n",
" <td>[3L, 3L]</td>\n",
" <td>0.166801820206</td>\n",
" </tr>\n",
" <tr>\n",
" <td>1</td>\n",
" <td>1</td>\n",
" <td>[4L, 4L]</td>\n",
" <td>0.151661035568</td>\n",
" </tr>\n",
" <tr>\n",
" <td>1</td>\n",
" <td>1</td>\n",
" <td>[6L, 6L]</td>\n",
" <td>0.0965411872855</td>\n",
" </tr>\n",
" <tr>\n",
" <td>1</td>\n",
" <td>1</td>\n",
" <td>[1L, 1L]</td>\n",
" <td>0.0766610355683</td>\n",
" </tr>\n",
" <tr>\n",
" <td>1</td>\n",
" <td>1</td>\n",
" <td>[5L, 5L]</td>\n",
" <td>0.0521896024087</td>\n",
" </tr>\n",
" <tr>\n",
" <td>2</td>\n",
" <td>2</td>\n",
" <td>[0L, 0L]</td>\n",
" <td>0.448826703441</td>\n",
" </tr>\n",
" <tr>\n",
" <td>2</td>\n",
" <td>2</td>\n",
" <td>[3L, 3L]</td>\n",
" <td>0.325943770128</td>\n",
" </tr>\n",
" <tr>\n",
" <td>2</td>\n",
" <td>2</td>\n",
" <td>[2L, 2L]</td>\n",
" <td>0.256179815391</td>\n",
" </tr>\n",
" <tr>\n",
" <td>2</td>\n",
" <td>2</td>\n",
" <td>[4L, 4L]</td>\n",
" <td>0.202149921236</td>\n",
" </tr>\n",
" <tr>\n",
" <td>2</td>\n",
" <td>2</td>\n",
" <td>[1L, 1L]</td>\n",
" <td>0.127149921236</td>\n",
" </tr>\n",
" <tr>\n",
" <td>2</td>\n",
" <td>2</td>\n",
" <td>[5L, 5L]</td>\n",
" <td>0.075</td>\n",
" </tr>\n",
" <tr>\n",
" <td>2</td>\n",
" <td>2</td>\n",
" <td>[6L, 6L]</td>\n",
" <td>0.06375</td>\n",
" </tr>\n",
"</table>"
],
"text/plain": [
"[(1L, 1L, [0L, 0L], 0.270635964385879),\n",
" (1L, 1L, [2L, 2L], 0.18423239851445),\n",
" (1L, 1L, [3L, 3L], 0.166801820206414),\n",
" (1L, 1L, [4L, 4L], 0.151661035568349),\n",
" (1L, 1L, [6L, 6L], 0.0965411872854988),\n",
" (1L, 1L, [1L, 1L], 0.0766610355683489),\n",
" (1L, 1L, [5L, 5L], 0.0521896024086525),\n",
" (2L, 2L, [0L, 0L], 0.448826703440932),\n",
" (2L, 2L, [3L, 3L], 0.325943770128465),\n",
" (2L, 2L, [2L, 2L], 0.256179815391031),\n",
" (2L, 2L, [4L, 4L], 0.202149921235622),\n",
" (2L, 2L, [1L, 1L], 0.127149921235622),\n",
" (2L, 2L, [5L, 5L], 0.075),\n",
" (2L, 2L, [6L, 6L], 0.06375)]"
]
},
"execution_count": 5,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"%%sql\n",
"\n",
"DROP TABLE IF EXISTS pagerank_multicol_out, pagerank_multicol_out_summary;\n",
"SELECT madlib.pagerank(\n",
" 'vertex_multicol', -- Vertex table\n",
" '[node_id_major,node_id_minor]', -- Vertex id column\n",
" 'edge_multicol', -- Edge table\n",
" 'src=[conn_src_major,conn_src_minor], dest=[conn_dest_major,conn_dest_minor]', -- Comma delimted string of edge arguments\n",
" 'pagerank_multicol_out', -- Output table of PageRank\n",
" NULL, -- Default damping factor (0.85)\n",
" NULL, -- Default max iters (100)\n",
" NULL, -- Default Threshold\n",
" 'user_id_major,user_id_minor', -- Grouping Columns\n",
" '{{2,2},{4,4}}'); -- Personalization vertices\n",
"SELECT * FROM pagerank_multicol_out ORDER BY user_id_major,user_id_minor,pagerank DESC;"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"Look at the summary table:"
]
},
{
"cell_type": "code",
"execution_count": 6,
"metadata": {},
"outputs": [
{
"name": "stdout",
"output_type": "stream",
"text": [
" * postgresql://okislal@localhost:6600/madlib\n",
"2 rows affected.\n"
]
},
{
"data": {
"text/html": [
"<table>\n",
" <tr>\n",
" <th>user_id_major</th>\n",
" <th>user_id_minor</th>\n",
" <th>__iterations__</th>\n",
" </tr>\n",
" <tr>\n",
" <td>2</td>\n",
" <td>2</td>\n",
" <td>45</td>\n",
" </tr>\n",
" <tr>\n",
" <td>1</td>\n",
" <td>1</td>\n",
" <td>41</td>\n",
" </tr>\n",
"</table>"
],
"text/plain": [
"[(2L, 2L, 45), (1L, 1L, 41)]"
]
},
"execution_count": 6,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"%%sql\n",
"SELECT * FROM pagerank_multicol_out_summary;"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"# 3. Weakly Connected Components"
]
},
{
"cell_type": "code",
"execution_count": 7,
"metadata": {},
"outputs": [
{
"name": "stdout",
"output_type": "stream",
"text": [
" * postgresql://okislal@localhost:6600/madlib\n",
"Done.\n",
"1 rows affected.\n",
"14 rows affected.\n"
]
},
{
"data": {
"text/html": [
"<table>\n",
" <tr>\n",
" <th>id</th>\n",
" <th>component_id</th>\n",
" <th>user_id_major</th>\n",
" <th>user_id_minor</th>\n",
" </tr>\n",
" <tr>\n",
" <td>[0L, 0L]</td>\n",
" <td>3</td>\n",
" <td>1</td>\n",
" <td>1</td>\n",
" </tr>\n",
" <tr>\n",
" <td>[1L, 1L]</td>\n",
" <td>3</td>\n",
" <td>1</td>\n",
" <td>1</td>\n",
" </tr>\n",
" <tr>\n",
" <td>[2L, 2L]</td>\n",
" <td>3</td>\n",
" <td>1</td>\n",
" <td>1</td>\n",
" </tr>\n",
" <tr>\n",
" <td>[3L, 3L]</td>\n",
" <td>3</td>\n",
" <td>1</td>\n",
" <td>1</td>\n",
" </tr>\n",
" <tr>\n",
" <td>[4L, 4L]</td>\n",
" <td>3</td>\n",
" <td>1</td>\n",
" <td>1</td>\n",
" </tr>\n",
" <tr>\n",
" <td>[5L, 5L]</td>\n",
" <td>3</td>\n",
" <td>1</td>\n",
" <td>1</td>\n",
" </tr>\n",
" <tr>\n",
" <td>[6L, 6L]</td>\n",
" <td>3</td>\n",
" <td>1</td>\n",
" <td>1</td>\n",
" </tr>\n",
" <tr>\n",
" <td>[0L, 0L]</td>\n",
" <td>3</td>\n",
" <td>2</td>\n",
" <td>2</td>\n",
" </tr>\n",
" <tr>\n",
" <td>[1L, 1L]</td>\n",
" <td>3</td>\n",
" <td>2</td>\n",
" <td>2</td>\n",
" </tr>\n",
" <tr>\n",
" <td>[2L, 2L]</td>\n",
" <td>3</td>\n",
" <td>2</td>\n",
" <td>2</td>\n",
" </tr>\n",
" <tr>\n",
" <td>[3L, 3L]</td>\n",
" <td>3</td>\n",
" <td>2</td>\n",
" <td>2</td>\n",
" </tr>\n",
" <tr>\n",
" <td>[4L, 4L]</td>\n",
" <td>3</td>\n",
" <td>2</td>\n",
" <td>2</td>\n",
" </tr>\n",
" <tr>\n",
" <td>[5L, 5L]</td>\n",
" <td>3</td>\n",
" <td>2</td>\n",
" <td>2</td>\n",
" </tr>\n",
" <tr>\n",
" <td>[6L, 6L]</td>\n",
" <td>3</td>\n",
" <td>2</td>\n",
" <td>2</td>\n",
" </tr>\n",
"</table>"
],
"text/plain": [
"[([0L, 0L], 3L, 1L, 1L),\n",
" ([1L, 1L], 3L, 1L, 1L),\n",
" ([2L, 2L], 3L, 1L, 1L),\n",
" ([3L, 3L], 3L, 1L, 1L),\n",
" ([4L, 4L], 3L, 1L, 1L),\n",
" ([5L, 5L], 3L, 1L, 1L),\n",
" ([6L, 6L], 3L, 1L, 1L),\n",
" ([0L, 0L], 3L, 2L, 2L),\n",
" ([1L, 1L], 3L, 2L, 2L),\n",
" ([2L, 2L], 3L, 2L, 2L),\n",
" ([3L, 3L], 3L, 2L, 2L),\n",
" ([4L, 4L], 3L, 2L, 2L),\n",
" ([5L, 5L], 3L, 2L, 2L),\n",
" ([6L, 6L], 3L, 2L, 2L)]"
]
},
"execution_count": 7,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"%%sql\n",
"DROP TABLE IF EXISTS wcc_multicol_out, wcc_multicol_out_summary;\n",
"SELECT madlib.weakly_connected_components(\n",
" 'vertex_multicol', -- Vertex table\n",
" '[node_id_major,node_id_minor]', -- Vertex id column\n",
" 'edge_multicol', -- Edge table\n",
" 'src=[conn_src_major,conn_src_minor], dest=[conn_dest_major,conn_dest_minor]', -- Comma delimted string of edge arguments\n",
" 'wcc_multicol_out', -- Output table of weakly connected components\n",
" 'user_id_major,user_id_minor'); -- Grouping column name\n",
"SELECT * FROM wcc_multicol_out ORDER BY user_id_major, user_id_minor, component_id, id;"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"# 4. WCC Helper Functions"
]
},
{
"cell_type": "code",
"execution_count": 10,
"metadata": {},
"outputs": [
{
"name": "stdout",
"output_type": "stream",
"text": [
" * postgresql://okislal@localhost:6600/madlib\n",
"Done.\n",
"1 rows affected.\n",
"2 rows affected.\n"
]
},
{
"data": {
"text/html": [
"<table>\n",
" <tr>\n",
" <th>user_id_major</th>\n",
" <th>user_id_minor</th>\n",
" <th>component_id</th>\n",
" </tr>\n",
" <tr>\n",
" <td>2</td>\n",
" <td>2</td>\n",
" <td>3</td>\n",
" </tr>\n",
" <tr>\n",
" <td>1</td>\n",
" <td>1</td>\n",
" <td>3</td>\n",
" </tr>\n",
"</table>"
],
"text/plain": [
"[(2L, 2L, 3L), (1L, 1L, 3L)]"
]
},
"execution_count": 10,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"%%sql\n",
"DROP TABLE IF EXISTS vc_table;\n",
"SELECT madlib.graph_wcc_vertex_check(\n",
" 'wcc_multicol_out', -- WCC's output table\n",
" '{{4,4},{5,5}}', -- Pair of vertex IDs\n",
" 'vc_table'); -- output table\n",
"SELECT * FROM vc_table;"
]
},
{
"cell_type": "code",
"execution_count": 14,
"metadata": {},
"outputs": [
{
"name": "stdout",
"output_type": "stream",
"text": [
" * postgresql://okislal@localhost:6600/madlib\n",
"Done.\n",
"1 rows affected.\n",
"12 rows affected.\n"
]
},
{
"data": {
"text/html": [
"<table>\n",
" <tr>\n",
" <th>user_id_major</th>\n",
" <th>user_id_minor</th>\n",
" <th>component_id</th>\n",
" <th>dest</th>\n",
" </tr>\n",
" <tr>\n",
" <td>1</td>\n",
" <td>1</td>\n",
" <td>3</td>\n",
" <td>[5L, 5L]</td>\n",
" </tr>\n",
" <tr>\n",
" <td>1</td>\n",
" <td>1</td>\n",
" <td>3</td>\n",
" <td>[4L, 4L]</td>\n",
" </tr>\n",
" <tr>\n",
" <td>1</td>\n",
" <td>1</td>\n",
" <td>3</td>\n",
" <td>[2L, 2L]</td>\n",
" </tr>\n",
" <tr>\n",
" <td>1</td>\n",
" <td>1</td>\n",
" <td>3</td>\n",
" <td>[6L, 6L]</td>\n",
" </tr>\n",
" <tr>\n",
" <td>1</td>\n",
" <td>1</td>\n",
" <td>3</td>\n",
" <td>[3L, 3L]</td>\n",
" </tr>\n",
" <tr>\n",
" <td>1</td>\n",
" <td>1</td>\n",
" <td>3</td>\n",
" <td>[1L, 1L]</td>\n",
" </tr>\n",
" <tr>\n",
" <td>2</td>\n",
" <td>2</td>\n",
" <td>3</td>\n",
" <td>[2L, 2L]</td>\n",
" </tr>\n",
" <tr>\n",
" <td>2</td>\n",
" <td>2</td>\n",
" <td>3</td>\n",
" <td>[1L, 1L]</td>\n",
" </tr>\n",
" <tr>\n",
" <td>2</td>\n",
" <td>2</td>\n",
" <td>3</td>\n",
" <td>[6L, 6L]</td>\n",
" </tr>\n",
" <tr>\n",
" <td>2</td>\n",
" <td>2</td>\n",
" <td>3</td>\n",
" <td>[4L, 4L]</td>\n",
" </tr>\n",
" <tr>\n",
" <td>2</td>\n",
" <td>2</td>\n",
" <td>3</td>\n",
" <td>[3L, 3L]</td>\n",
" </tr>\n",
" <tr>\n",
" <td>2</td>\n",
" <td>2</td>\n",
" <td>3</td>\n",
" <td>[5L, 5L]</td>\n",
" </tr>\n",
"</table>"
],
"text/plain": [
"[(1L, 1L, 3L, [5L, 5L]),\n",
" (1L, 1L, 3L, [4L, 4L]),\n",
" (1L, 1L, 3L, [2L, 2L]),\n",
" (1L, 1L, 3L, [6L, 6L]),\n",
" (1L, 1L, 3L, [3L, 3L]),\n",
" (1L, 1L, 3L, [1L, 1L]),\n",
" (2L, 2L, 3L, [2L, 2L]),\n",
" (2L, 2L, 3L, [1L, 1L]),\n",
" (2L, 2L, 3L, [6L, 6L]),\n",
" (2L, 2L, 3L, [4L, 4L]),\n",
" (2L, 2L, 3L, [3L, 3L]),\n",
" (2L, 2L, 3L, [5L, 5L])]"
]
},
"execution_count": 14,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"%%sql\n",
"DROP TABLE IF EXISTS reach_table;\n",
"SELECT madlib.graph_wcc_reachable_vertices(\n",
" 'wcc_multicol_out', -- WCC's output table\n",
" '{0,0}'::BIGINT[], -- source vertex\n",
" 'reach_table'); -- output table\n",
"SELECT * FROM reach_table ORDER BY user_id_major, user_id_minor;"
]
},
{
"cell_type": "code",
"execution_count": null,
"metadata": {},
"outputs": [],
"source": []
}
],
"metadata": {
"kernelspec": {
"display_name": "Python 2",
"language": "python",
"name": "python2"
},
"language_info": {
"codemirror_mode": {
"name": "ipython",
"version": 2
},
"file_extension": ".py",
"mimetype": "text/x-python",
"name": "python",
"nbconvert_exporter": "python",
"pygments_lexer": "ipython2",
"version": "2.7.17"
}
},
"nbformat": 4,
"nbformat_minor": 1
}