{
"cells": [
{
"cell_type": "markdown",
"metadata": {},
"source": [
"## Range Partitioning\n",
"\n",
"Let us understand how we can take care of range partitioning of tables."
]
},
{
"cell_type": "code",
"execution_count": 56,
"metadata": {
"tags": [
"remove-cell"
]
},
"outputs": [
{
"data": {
"text/html": [
"\n"
],
"text/plain": [
""
]
},
"metadata": {},
"output_type": "display_data"
}
],
"source": [
"%%HTML\n",
""
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"* It is primarily used to create partitions based up on a given range of values.\n",
"* Here are the steps involved in creating table using range partitioning strategy.\n",
" * Create table using `PARTITION BY RANGE`\n",
" * Add default and range specific partitions\n",
" * Validate by inserting data into the table\n",
"* We can detach as well as drop the partitions from the table.\n",
"\n",
"\n",
"### Create Partitioned Table\n",
"\n",
"Let us create partitioned table with name `users_range_part`.\n",
"* It contains same columns as `users`.\n",
"* We will partition the table based up on `created_dt` field.\n",
"* We will create one partition per year with naming convention **users_range_part_yyyy** (users_range_part_2016)."
]
},
{
"cell_type": "code",
"execution_count": 57,
"metadata": {},
"outputs": [
{
"name": "stdout",
"output_type": "stream",
"text": [
"The sql extension is already loaded. To reload it, use:\n",
" %reload_ext sql\n"
]
}
],
"source": [
"%load_ext sql"
]
},
{
"cell_type": "code",
"execution_count": 58,
"metadata": {},
"outputs": [
{
"name": "stdout",
"output_type": "stream",
"text": [
"env: DATABASE_URL=postgresql://itversity_sms_user:sms_password@localhost:5432/itversity_sms_db\n"
]
}
],
"source": [
"%env DATABASE_URL=postgresql://itversity_sms_user:sms_password@localhost:5432/itversity_sms_db"
]
},
{
"cell_type": "code",
"execution_count": 59,
"metadata": {},
"outputs": [
{
"name": "stdout",
"output_type": "stream",
"text": [
" * postgresql://itversity_sms_user:***@localhost:5432/itversity_sms_db\n",
"Done.\n"
]
},
{
"data": {
"text/plain": [
"[]"
]
},
"execution_count": 59,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"%sql DROP TABLE IF EXISTS users_range_part"
]
},
{
"cell_type": "code",
"execution_count": 60,
"metadata": {},
"outputs": [
{
"name": "stdout",
"output_type": "stream",
"text": [
" * postgresql://itversity_sms_user:***@localhost:5432/itversity_sms_db\n",
"Done.\n"
]
},
{
"data": {
"text/plain": [
"[]"
]
},
"execution_count": 60,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"%%sql\n",
"\n",
"CREATE TABLE users_range_part (\n",
" user_id SERIAL,\n",
" user_first_name VARCHAR(30) NOT NULL,\n",
" user_last_name VARCHAR(30) NOT NULL,\n",
" user_email_id VARCHAR(50) NOT NULL,\n",
" user_email_validated BOOLEAN DEFAULT FALSE,\n",
" user_password VARCHAR(200),\n",
" user_role VARCHAR(1) NOT NULL DEFAULT 'U', --U and A\n",
" is_active BOOLEAN DEFAULT FALSE,\n",
" created_dt DATE DEFAULT CURRENT_DATE,\n",
" last_updated_ts TIMESTAMP DEFAULT CURRENT_TIMESTAMP,\n",
" PRIMARY KEY (created_dt, user_id)\n",
") PARTITION BY RANGE(created_dt)"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"```{note}\n",
"We will not be able to insert the data until we add at least one partition.\n",
"```"
]
}
],
"metadata": {
"kernelspec": {
"display_name": "Python 3",
"language": "python",
"name": "python3"
},
"language_info": {
"codemirror_mode": {
"name": "ipython",
"version": 3
},
"file_extension": ".py",
"mimetype": "text/x-python",
"name": "python",
"nbconvert_exporter": "python",
"pygments_lexer": "ipython3",
"version": "3.6.12"
}
},
"nbformat": 4,
"nbformat_minor": 4
}