Partitioning Tables¶
Here is the exercise to get comfort with partitioning. We will be using range partitioning.
- Use retail database. Make sure orders table already exists.
In [1]:
%%HTML
<iframe width="560" height="315" src="https://www.youtube.com/embed/uAkrpaJmbx0?rel=0&controls=1&showinfo=0" frameborder="0" allowfullscreen></iframe>
In [1]:
%load_ext sql
In [2]:
%env DATABASE_URL=postgresql://itversity_retail_user:retail_password@localhost:5432/itversity_retail_db
Exercise 1¶
Create table orders_part with the same columns as orders.
- Partition the table by month using range partitioning on order_date.
- Add 14 partitions – 13 based up on the data and 1 default. Here is the naming convention.
- Default – orders_part_default
- Partition for 2014 January – orders_part_201401
In [117]:
%sql DROP TABLE IF EXISTS orders_part
Out[117]:
In [118]:
%%sql
CREATE TABLE orders_part (
order_id INT,
order_date DATE,
order_customer_id INT,
order_status VARCHAR(45)
) PARTITION BY RANGE (order_date)
Out[118]:
In [119]:
%sql SELECT * FROM orders LIMIT 10
Out[119]:
In [120]:
%%sql
SELECT count(1)
FROM orders
WHERE order_date >= '2014-01-01' AND order_date < '2014-02-01'
Out[120]:
In [121]:
%%sql
CREATE TABLE orders_part_default
PARTITION OF orders_part DEFAULT
Out[121]:
In [122]:
%%sql
CREATE TABLE orders_part_201307
PARTITION OF orders_part
FOR VALUES FROM ('2013-07-01') TO ('2013-08-01')
Out[122]:
In [123]:
%%sql
CREATE TABLE orders_part_201308
PARTITION OF orders_part
FOR VALUES FROM ('2013-08-01') TO ('2013-09-01')
Out[123]:
In [124]:
%%sql
CREATE TABLE orders_part_201309
PARTITION OF orders_part
FOR VALUES FROM ('2013-09-01') TO ('2013-10-01')
Out[124]:
In [125]:
%%sql
CREATE TABLE orders_part_201310
PARTITION OF orders_part
FOR VALUES FROM ('2013-10-01') TO ('2013-11-01')
Out[125]:
In [126]:
%%sql
CREATE TABLE orders_part_201311
PARTITION OF orders_part
FOR VALUES FROM ('2013-11-01') TO ('2013-12-01')
Out[126]:
In [127]:
%%sql
CREATE TABLE orders_part_201312
PARTITION OF orders_part
FOR VALUES FROM ('2013-12-01') TO ('2014-01-01')
Out[127]:
In [128]:
%%sql
CREATE TABLE orders_part_201401
PARTITION OF orders_part
FOR VALUES FROM ('2014-01-01') TO ('2014-02-01')
Out[128]:
In [129]:
%%sql
CREATE TABLE orders_part_201402
PARTITION OF orders_part
FOR VALUES FROM ('2014-02-01') TO ('2014-03-01')
Out[129]:
In [130]:
%%sql
CREATE TABLE orders_part_201403
PARTITION OF orders_part
FOR VALUES FROM ('2014-03-01') TO ('2014-04-01')
Out[130]:
In [131]:
%%sql
CREATE TABLE orders_part_201404
PARTITION OF orders_part
FOR VALUES FROM ('2014-04-01') TO ('2014-05-01')
Out[131]:
In [132]:
%%sql
CREATE TABLE orders_part_201405
PARTITION OF orders_part
FOR VALUES FROM ('2014-05-01') TO ('2014-06-01')
Out[132]:
In [133]:
%%sql
CREATE TABLE orders_part_201406
PARTITION OF orders_part
FOR VALUES FROM ('2014-06-01') TO ('2014-07-01')
Out[133]:
In [134]:
%%sql
CREATE TABLE orders_part_201407
PARTITION OF orders_part
FOR VALUES FROM ('2014-07-01') TO ('2014-08-01')
Out[134]:
Exercise 2¶
Let us load and validate data in the partitioned table.
- Load the data from orders into orders_part.
- Get count on orders_part as well as all the 14 partitions. You should get 0 for default partition and all the records should be distributed using the other 13 partitions.
In [135]:
%%sql
INSERT INTO orders_part
SELECT * FROM orders
Out[135]:
In [136]:
%%sql
SELECT count(1) FROM orders
WHERE to_char(order_date, 'yyyy-MM') = '2014-01'
Out[136]:
In [137]:
%%sql
SELECT count(1) FROM orders_part
WHERE to_char(order_date, 'yyyy-MM') = '2014-01'
Out[137]:
In [138]:
%sql SELECT count(1) FROM orders
Out[138]:
In [139]:
%sql SELECT count(1) FROM orders_part
Out[139]:
In [140]:
%sql SELECT * FROM orders_part_default LIMIT 10
Out[140]:
In [141]:
%sql SELECT count(1) FROM orders_part_default
Out[141]:
In [142]:
%sql SELECT count(1) FROM orders_part_201307
Out[142]:
In [143]:
%sql SELECT count(1) FROM orders_part_201308
Out[143]:
In [144]:
%sql SELECT count(1) FROM orders_part_201309
Out[144]:
In [145]:
%sql SELECT count(1) FROM orders_part_201310
Out[145]:
In [146]:
%sql SELECT count(1) FROM orders_part_201311
Out[146]:
In [147]:
%sql SELECT count(1) FROM orders_part_201312
Out[147]:
In [148]:
%sql SELECT count(1) FROM orders_part_201401
Out[148]:
In [149]:
%sql SELECT count(1) FROM orders_part_201402
Out[149]:
In [150]:
%sql SELECT count(1) FROM orders_part_201403
Out[150]:
In [151]:
%sql SELECT count(1) FROM orders_part_201404
Out[151]:
In [152]:
%sql SELECT count(1) FROM orders_part_201405
Out[152]:
In [153]:
%sql SELECT count(1) FROM orders_part_201406
Out[153]:
In [154]:
%sql SELECT count(1) FROM orders_part_201407
Out[154]: