SQL-Basics 8(Partitions)
-----Partitions----
create database ineuron_partition;
use ineuron_partition;
select database();
Flat table(Table without partitions):-
create table ineuron_courses(
course_name varchar(50),
course_id int(10),
course_title varchar(50),
course_desc varchar(50),
launch_date date,
course_fee int,
course_mentor varchar(60),
course_launch_year int);
select * from ineuron_courses;
insert into ineuron_courses values('machine_learning',101,'ML','this is ML course','2019-07-07',3540,'Sudhansu',2019)
select * from ineuron_courses;
insert into ineuron_courses values('ALOPS',101,'ML','this is ML course','2019-07-07',3540,'Sudhansu',2019),
('DLCVNLP',101,'ML','this is ML course','2019-07-07',3540,'Sudhansu',2019),
('AWS CLOUD',101,'ML','this is ML course','2020-07-07',3540,'Sudhansu',2020),
('BLOCK CHAIN',101,'ML','this is ML course','2021-07-07',3540,'Sudhansu',2021),
('RL',101,'ML','this is ML course','2022-07-07',3540,'Sudhansu',2022),
('DL',101,'ML','this is ML course','2020-07-07',3540,'Sudhansu',2020),
('INTERVIEW PREP',101,'ML','this is ML course','2021-07-07',3540,'Sudhansu',2021),
('BIG DATA',101,'ML','this is ML course','2022-07-07',3540,'Sudhansu',2022),
('DATA ANALYTICS',101,'ML','this is ML course','2019-07-07',3540,'Sudhansu',2019),
('FSDS',101,'ML','this is ML course','2020-07-07',3540,'Sudhansu',2020),
('FSDA',101,'ML','this is ML course','2021-07-07',3540,'Sudhansu',2021),
('FSBE',101,'ML','this is ML course','2022-07-07',3540,'Sudhansu',2022),
('JAVA',101,'ML','this is ML course','2019-07-07',3540,'Sudhansu',2019),
('CYBER SECURITY',101,'ML','this is ML course','2020-07-07',3540,'Sudhansu',2020),
('DSA',101,'ML','this is ML course','2021-07-07',3540,'Sudhansu',2021)
Q)Can you please give the data for the courses that was launched in 2020?
select * from ineuron_courses where course_launch_year=2020;
----->Whenever we are trying to execute the above stmnt it will try to go to the entire table and it is
trying to check each and every record and each and every time where the year is matched our required
one.
Partition By:
Partition:-
------>It means creating a separate file (or) creating a separate space for a separate partition So that our
queries are much more optimized.
----->All partitions maintained by information_schema system table.
----->Based on the column value try to separate the complete rows.
----->We can create the partitions based on the columns, keys, and hash.
---->There is no limit to creating the partition(we can create multiple partitions).
Ex:-
--->I have 10 years of data(since last 10 years we have records), If we want to put all these records
inside your database?
----->Now which option we can choose to search the record entire table.
----->Searching the record of that specific year.
----->If we go to the entire table and search for the year record it will take more time to respond.
----->But if we create the table partition year wise then fetching the record for the year is very easy,
------>Here queries will be faster.
----->Based on the column value try to separate the complete rows.
Note:-
----->We create any partition the objective is the same, whatever type of partition we are going to use
just divides the dataset, and it never tries to do anything except divide the data in any situation.
------>We have different types of partitions like.
1)Range Partition
2)Hash Partition
3)Key Partition
4)List Partition
5)List column Partition
6)Range column Partition
1)Range Partition
------->In the range partition values are stored based on the ranges as per the partition.
create table ineuron_courses1(
course_name varchar(50),
course_id int(10),
course_title varchar(50),
course_desc varchar(50),
launch_date date,
course_fee int,
course_mentor varchar(60),
course_launch_year int)
partition by range (course_launch_year)(
partition p0 values less than(2019),
partition p1 values less than(2020),
partition p2 values less than (2021),
partition p3 values less than (2022))
select * from ineuron_courses1;
insert into ineuron_courses1 values('ALOPS',101,'ML','this is ML course','2019-07-07',3540,'Sudhansu',2019),
('DLCVNLP',101,'ML','this is ML course','2019-07-07',3540,'Sudhansu',2019),
('AWS CLOUD',101,'ML','this is ML course','2020-07-07',3540,'Sudhansu',2020),
('BLOCK CHAIN',101,'ML','this is ML course','2021-07-07',3540,'Sudhansu',2021),
('RL',101,'ML','this is ML course','2022-07-07',3540,'Sudhansu',2022),
('DL',101,'ML','this is ML course','2020-07-07',3540,'Sudhansu',2020),
('INTERVIEW PREP',101,'ML','this is ML course','2021-07-07',3540,'Sudhansu',2021),
('BIG DATA',101,'ML','this is ML course','2022-07-07',3540,'Sudhansu',2022),
('DATA ANALYTICS',101,'ML','this is ML course','2019-07-07',3540,'Sudhansu',2019),
('FSDS',101,'ML','this is ML course','2020-07-07',3540,'Sudhansu',2020),
('FSDA',101,'ML','this is ML course','2021-07-07',3540,'Sudhansu',2021),
('FSBE',101,'ML','this is ML course','2022-07-07',3540,'Sudhansu',2022),
('JAVA',101,'ML','this is ML course','2019-07-07',3540,'Sudhansu',2019),
('CYBER SECURITY',101,'ML','this is ML course','2020-07-07',3540,'Sudhansu',2020),
('DSA',101,'ML','this is ML course','2021-07-07',3540,'Sudhansu',2021)
Error Code: 1526. Table has no partition for value 2022
Reason:-
------>Here am trying to insert the value like 2022 for the partition column (course_launch_year) but
it don't have partition for that value 2022.
-------------Again am trying to create the table with Range Parition--------------------------
create table ineuron_courses1(
course_name varchar(50),
course_id int(10),
course_title varchar(50),
course_desc varchar(50),
launch_date date,
course_fee int,
course_mentor varchar(60),
course_launch_year int)
partition by range (course_launch_year)(
partition p0 values less than(2019),
partition p1 values less than(2020),
partition p2 values less than (2021),
partition p3 values less than (2022),
partition p3 values less than (2023))
Error Code: 1517. Duplicate partition name p3.
Reason:-
------->I have mention p3 partition name twice.
create table ineuron_courses1(
course_name varchar(50),
course_id int(10),
course_title varchar(50),
course_desc varchar(50),
launch_date date,
course_fee int,
course_mentor varchar(60),
course_launch_year int)
partition by range (course_launch_year)(
partition p0 values less than(2019),
partition p1 values less than(2020),
partition p2 values less than (2021),
partition p3 values less than (2022),
partition p4 values less than (2023));
insert into ineuron_courses1 values('ALOPS',101,'ML','this is ML course','2019-07-07',3540,'Sudhansu',2019),
('DLCVNLP',101,'ML','this is ML course','2019-07-07',3540,'Sudhansu',2019),
('AWS CLOUD',101,'ML','this is ML course','2020-07-07',3540,'Sudhansu',2020),
('BLOCK CHAIN',101,'ML','this is ML course','2021-07-07',3540,'Sudhansu',2021),
('RL',101,'ML','this is ML course','2022-07-07',3540,'Sudhansu',2022),
('DL',101,'ML','this is ML course','2020-07-07',3540,'Sudhansu',2020),
('INTERVIEW PREP',101,'ML','this is ML course','2021-07-07',3540,'Sudhansu',2021),
('BIG DATA',101,'ML','this is ML course','2022-07-07',3540,'Sudhansu',2022),
('DATA ANALYTICS',101,'ML','this is ML course','2019-07-07',3540,'Sudhansu',2019),
('FSDS',101,'ML','this is ML course','2020-07-07',3540,'Sudhansu',2020),
('FSDA',101,'ML','this is ML course','2021-07-07',3540,'Sudhansu',2021),
('FSBE',101,'ML','this is ML course','2022-07-07',3540,'Sudhansu',2022),
('JAVA',101,'ML','this is ML course','2019-07-07',3540,'Sudhansu',2019),
('CYBER SECURITY',101,'ML','this is ML course','2020-07-07',3540,'Sudhansu',2020),
('DSA',101,'ML','this is ML course','2021-07-07',3540,'Sudhansu',2021)
select * from ineuron_courses1 where course_launch_year=2020;
select * from ineuron_courses where course_launch_year=2020;
select partition_name,table_name,table_rows from information_schema.partitions where table_name='ineuron_courses1'
insert into ineuron_courses1 values ('DBA',101,'ML','this is ML course','2021-07-07',3540,'Sudhansu',2018)
----------How to check partitions of a table along with the records-----------
select partition_name,table_name,table_rows from information_schema.partitions where table_name='ineuron_courses1';
----->Here we did the partition based on ranges(ranges nothing but ranges less than 2019).
information_schema.partitions:-
----->It is the table it is available inside the system itself(it is maintained by the system).
Hash Partition:-
---->In this Hash Partition whatever column we are taking as the hash partition that column value is divided by the number of partitions and then what reminder value we received as per that value of the dataset will be stored.
course_name varchar(50),
course_id int(10),
course_title varchar(50),
course_desc varchar(50),
launch_date date,
course_fee int,
course_mentor varchar(60),
course_launch_year int)
partition by hash(course_launch_year)
partitions 5;
----------How to check partitions of a table along with the records-----------
select partition_name,table_name,table_rows from information_schema.partitions where table_name='ineuron_courses2';
create table ineuron_courses3(
course_name varchar(50),
course_id int(10),
course_title varchar(50),
course_desc varchar(50),
launch_date date,
course_fee int,
course_mentor varchar(60),
course_launch_year int)
partition by hash(course_launch_year)
partitions 10;
----------How to check partitions of a table along with the records-----------
select partition_name,table_name,table_rows from information_schema.partitions where table_name='ineuron_courses3';
insert into ineuron_courses3 values('ALOPS',101,'ML','this is ML course','2019-07-07',3540,'Sudhansu',2019),
('DLCVNLP',101,'ML','this is ML course','2019-07-07',3540,'Sudhansu',2019),
('AWS CLOUD',101,'ML','this is ML course','2020-07-07',3540,'Sudhansu',2020),
('BLOCK CHAIN',101,'ML','this is ML course','2021-07-07',3540,'Sudhansu',2021),
('RL',101,'ML','this is ML course','2022-07-07',3540,'Sudhansu',2022),
('DL',101,'ML','this is ML course','2020-07-07',3540,'Sudhansu',2020),
('INTERVIEW PREP',101,'ML','this is ML course','2021-07-07',3540,'Sudhansu',2021),
('BIG DATA',101,'ML','this is ML course','2022-07-07',3540,'Sudhansu',2022),
('DATA ANALYTICS',101,'ML','this is ML course','2019-07-07',3540,'Sudhansu',2019),
('FSDS',101,'ML','this is ML course','2020-07-07',3540,'Sudhansu',2020),
('FSDA',101,'ML','this is ML course','2021-07-07',3540,'Sudhansu',2021),
('FSBE',101,'ML','this is ML course','2022-07-07',3540,'Sudhansu',2022),
('JAVA',101,'ML','this is ML course','2019-07-07',3540,'Sudhansu',2019),
('CYBER SECURITY',101,'ML','this is ML course','2020-07-07',3540,'Sudhansu',2020),
('DSA',101,'ML','this is ML course','2021-07-07',3540,'Sudhansu',2021)
----------How to check partitions of a table along with the records-----------
select partition_name,table_name,table_rows from information_schema.partitions where table_name='ineuron_courses3';
Key partition
----->In this Key partition whatever values we have by default it is trying to use
hashing algorithm.
------>Different kind of hashing algorithm which is available inside sql and server.
------->In both the places it is using the hashing algorithm,but this algorithm is very
more complex as compared to the other simple hash algorithm.
------->There something called as MD5,
------->It will always try to use MD5 based on MD5 algorithm it is trying to generate the
alphbate,numbers combination based on this it is try to perform the hashing operations.
------->That is the only diffrence B/w hash and Key Partition.
-------->MD5 is only applicable for the key partition.
MD5:-
---->It is an algorithm it is trying to use for the encryption.
---->With the help of md5 algorithm key partitioning is going to happen.
Ex:-
select md5('testing');
ae2b1fca515949e5d54fb22b8ed95575
create table ineuron_courses4(
course_name varchar(50) primary key,
course_id int(10),
course_title varchar(50),
course_desc varchar(50),
launch_date date,
course_fee int,
course_mentor varchar(60),
course_launch_year int)
partition by key()
partitions 10;
----------How to check partitions of a table along with the records-----------
select partition_name,table_name,table_rows from information_schema.partitions where table_name='ineuron_courses4'
insert into ineuron_courses4 values('ALOPS',101,'ML','this is ML course','2019-07-07',3540,'Sudhansu',2019),
('DLCVNLP',102,'ML','this is ML course','2019-07-07',3540,'Sudhansu',2019),
('AWS CLOUD',103,'ML','this is ML course','2020-07-07',3540,'Sudhansu',2020),
('BLOCK CHAIN',104,'ML','this is ML course','2021-07-07',3540,'Sudhansu',2021),
('RL',105,'ML','this is ML course','2022-07-07',3540,'Sudhansu',2022),
('DL',106,'ML','this is ML course','2020-07-07',3540,'Sudhansu',2020),
('INTERVIEW PREP',107,'ML','this is ML course','2021-07-07',3540,'Sudhansu',2021),
('BIG DATA',108,'ML','this is ML course','2022-07-07',3540,'Sudhansu',2022),
('DATA ANALYTICS',109,'ML','this is ML course','2019-07-07',3540,'Sudhansu',2019),
('FSDS',110,'ML','this is ML course','2020-07-07',3540,'Sudhansu',2020),
('FSDA',111,'ML','this is ML course','2021-07-07',3540,'Sudhansu',2021),
('FSBE',112,'ML','this is ML course','2022-07-07',3540,'Sudhansu',2022),
('JAVA',113,'ML','this is ML course','2019-07-07',3540,'Sudhansu',2019),
('CYBER SECURITY',114,'ML','this is ML course','2020-07-07',3540,'Sudhansu',2020),
('DSA',115,'ML','this is ML course','2021-07-07',3540,'Sudhansu',2021)
select * from ineuron_courses4;
----------How to check partitions of a table along with the records-----------
select partition_name,table_name,table_rows from information_schema.partitions where table_name='ineuron_courses4';
create table ineuron_courses5(
course_name varchar(50),
course_id int(10) primary key,
course_title varchar(50),
course_desc varchar(50),
launch_date date,
course_fee int,
course_mentor varchar(60),
course_launch_year int)
partition by key()
partitions 10;
insert into ineuron_courses5 values('ALOPS',101,'ML','this is ML course','2019-07-07',3540,'Sudhansu',2019),
('DLCVNLP',102,'ML','this is ML course','2019-07-07',3540,'Sudhansu',2019),
('AWS CLOUD',103,'ML','this is ML course','2020-07-07',3540,'Sudhansu',2020),
('BLOCK CHAIN',104,'ML','this is ML course','2021-07-07',3540,'Sudhansu',2021),
('RL',105,'ML','this is ML course','2022-07-07',3540,'Sudhansu',2022),
('DL',106,'ML','this is ML course','2020-07-07',3540,'Sudhansu',2020),
('INTERVIEW PREP',107,'ML','this is ML course','2021-07-07',3540,'Sudhansu',2021),
('BIG DATA',108,'ML','this is ML course','2022-07-07',3540,'Sudhansu',2022),
('DATA ANALYTICS',109,'ML','this is ML course','2019-07-07',3540,'Sudhansu',2019),
('FSDS',110,'ML','this is ML course','2020-07-07',3540,'Sudhansu',2020),
('FSDA',111,'ML','this is ML course','2021-07-07',3540,'Sudhansu',2021),
('FSBE',112,'ML','this is ML course','2022-07-07',3540,'Sudhansu',2022),
('JAVA',113,'ML','this is ML course','2019-07-07',3540,'Sudhansu',2019),
('CYBER SECURITY',114,'ML','this is ML course','2020-07-07',3540,'Sudhansu',2020),
('DSA',115,'ML','this is ML course','2021-07-07',3540,'Sudhansu',2021)
select * from ineuron_courses5;
----------How to check partitions of a table along with the records-----------
select partition_name,table_name,table_rows from information_schema.partitions where table_name='ineuron_courses5';
Q)What about the situation where we have two keys on the table?
---->For that, we need to try to specify that key name then we try to do the partition that is possible.
create table ineuron_courses6(
course_name varchar(50) unique key,
course_id int(10) primary key,
course_title varchar(50),
course_desc varchar(50),
launch_date date,
course_fee int,
course_mentor varchar(60),
course_launch_year int)
partition by key(course_name)
partitions 10;
Error Code: 1503. A PRIMARY KEY must include all columns in the table's partitioning function (prefixed columns are not considered).
create table ineuron_courses6(
course_name varchar(50) unique key,
course_id int(10) primary key,
course_title varchar(50),
course_desc varchar(50),
launch_date date,
course_fee int,
course_mentor varchar(60),
course_launch_year int)
partition by key(course_id)
partitions 10;
Error Code: 1503. A UNIQUE INDEX must include all columns in the table's partitioning function (prefixed columns are not considered).
Reason:-
----->Both the primary key and unique key are conflicts.
create table ineuron_courses6(
course_name varchar(50) unique key,
course_id int(10) ,
course_title varchar(50),
course_desc varchar(50),
launch_date date,
course_fee int,
course_mentor varchar(60),
course_launch_year int)
partition by key(course_name)
partitions 10;
List partition:-
----->In this list partition whatever the values are mentioned in the patition that values are seperate
in each partition.
---->In the list partition, we always mentioned the integer value, It is not accepting any other datatype.
create table ineuron_courses7(
course_name varchar(50),
course_id int(10) ,
course_title varchar(50),
course_desc varchar(50),
launch_date date,
course_fee int,
course_mentor varchar(60),
course_launch_year int)
partition by list(course_name)(
partition p0 values in ('aiops','fsds','java'),
partition p1 values in ('fsda','mern','dl','dsa'),
partition p2 values in ('INTERVIEW PREP','BIG DATA','dl','CYBER SECURITY'))
Error Code: 1697. VALUES value for partition 'p0' must have type INT
create table ineuron_courses7(
course_name varchar(50),
course_id int(10) ,
course_title varchar(50),
course_desc varchar(50),
launch_date date,
course_fee int,
course_mentor varchar(60),
course_launch_year int)
partition by list(course_launch_year)(
partition p0 values in (2019,2021),
partition p1 values in (2020,2022));
insert into ineuron_courses7 values('ALOPS',101,'ML','this is ML course','2019-07-07',3540,'Sudhansu',2019),
('DLCVNLP',102,'ML','this is ML course','2019-07-07',3540,'Sudhansu',2019),
('AWS CLOUD',103,'ML','this is ML course','2020-07-07',3540,'Sudhansu',2020),
('BLOCK CHAIN',104,'ML','this is ML course','2021-07-07',3540,'Sudhansu',2021),
('RL',105,'ML','this is ML course','2022-07-07',3540,'Sudhansu',2022),
('DL',106,'ML','this is ML course','2020-07-07',3540,'Sudhansu',2020),
('INTERVIEW PREP',107,'ML','this is ML course','2021-07-07',3540,'Sudhansu',2021),
('BIG DATA',108,'ML','this is ML course','2022-07-07',3540,'Sudhansu',2022),
('DATA ANALYTICS',109,'ML','this is ML course','2019-07-07',3540,'Sudhansu',2019),
('FSDS',110,'ML','this is ML course','2020-07-07',3540,'Sudhansu',2020),
('FSDA',111,'ML','this is ML course','2021-07-07',3540,'Sudhansu',2021),
('FSBE',112,'ML','this is ML course','2022-07-07',3540,'Sudhansu',2022),
('JAVA',113,'ML','this is ML course','2019-07-07',3540,'Sudhansu',2019),
('CYBER SECURITY',114,'ML','this is ML course','2020-07-07',3540,'Sudhansu',2020),
('DSA',115,'ML','this is ML course','2021-07-07',3540,'Sudhansu',2021)
----------How to check partitions of a table along with the records-----------
select partition_name,table_name,table_rows from information_schema.partitions where table_name='ineuron_courses7';
Q)What if we are trying to insert the record but that is not part of partition?
---->In that case it is giving an error that is the reason we are not just using the insert function, we can use insert with ignore so that whatever data which is not a partition will not be inserted at all.
Range column partition
---->Here we can take multiple columns.
----->In the case of range we have taken only one column.
create table ineuron_courses8(
course_name varchar(50),
course_id int(10),
course_title varchar(50),
course_desc varchar(50),
launch_date date,
course_fee int,
course_mentor varchar(60),
course_launch_year int)
partition by range columns(course_name,course_id,course_launch_year)(
partition p0 values less than('ALOPS',105,2019),
partition p1 values less than('FSDS',110,2021),
partition p2 values less than('BIG DATA',116,2023))
Error Code: 1493. VALUES LESS THAN value must be strictly increasing for each partition
create table ineuron_courses8(
course_name varchar(50),
course_id int(10) ,
course_title varchar(50),
course_desc varchar(50),
launch_date date,
course_fee int,
course_mentor varchar(60),
course_launch_year int)
partition by range columns(course_name,course_id,course_launch_year)(
partition p0 values less than('ALOPS',101,2019),
partition p1 values less than('FSDS',106,2021),
partition p2 values less than ('BIG',114,2023))
Error Code: 1493. VALUES LESS THAN value must be strictly increasing for each partition
create table ineuron_courses8(
course_name varchar(50),
course_id int(10) ,
course_title varchar(50),
course_desc varchar(50),
launch_date date,
course_fee int,
course_mentor varchar(60),
course_launch_year int)
partition by range columns(course_name,course_id,course_launch_year)(
partition p0 values less than('ALOPS',101,2019),
partition p1 values less than('FSDS',106,2021),
partition p2 values less than ('DATA ANALYTICS',114,2023))
Error Code: 1493. VALUES LESS THAN value must be strictly increasing for each partition
Reason:-
---->In this case the reason for the above errors was looking the column data name character must be increased the previous value(am saying about alphabets) for example in the above we mentioned column data like 'ALOPS','FSDS','BIG', But for integer column data it will allow.
---------------------------------Test cases like all the table below----------------------
create table ineuron_courses00(
course_name varchar(50),
course_id int(10) ,
course_title varchar(50),
course_desc varchar(50),
launch_date date,
course_fee int,
course_mentor varchar(60),
course_launch_year int)
partition by range columns(course_name,course_id,course_launch_year)(
partition p0 values less than('ALOPS',101,2019),
partition p1 values less than('FSDS',106,2021),
partition p2 values less than ('G',116,2023))
---->The above query working fine
create table ineuron_courses01(
course_name varchar(50),
course_id int(10) ,
course_title varchar(50),
course_desc varchar(50),
launch_date date,
course_fee int,
course_mentor varchar(60),
course_launch_year int)
partition by range columns(course_name,course_id,course_launch_year)(
partition p0 values less than('ALOPS',101,2019),
partition p1 values less than('FSDS',106,2021),
partition p2 values less than ('G',107,2023))
---->The above query working fine
create table ineuron_courses02(
course_name varchar(50),
course_id int(10) ,
course_title varchar(50),
course_desc varchar(50),
launch_date date,
course_fee int,
course_mentor varchar(60),
course_launch_year int)
partition by range columns(course_name,course_id,course_launch_year)(
partition p0 values less than('ALOPS',101,2019),
partition p1 values less than('FSDS',106,2021),
partition p2 values less than ('G',104,2023))
---->The above query working fine.
create table ineuron_courses03(
course_name varchar(50),
course_id int(10) ,
course_title varchar(50),
course_desc varchar(50),
launch_date date,
course_fee int,
course_mentor varchar(60),
course_launch_year int)
partition by range columns(course_name,course_id,course_launch_year)(
partition p0 values less than('ALOPS',101,2019),
partition p1 values less than('FSDS',106,2021),
partition p2 values less than ('G',104,2018))
---->The above query working fine.
create table ineuron_courses8(
course_name varchar(50),
course_id int(10),
course_title varchar(50),
course_desc varchar(50),
launch_date date,
course_fee int,
course_mentor varchar(60),
course_launch_year int)
partition by range columns(course_name,course_id,course_launch_year)(
partition p0 values less than('ALOPS',105,2019),
partition p1 values less than('FSDS',110,2021),
partition p2 values less than('JAVA',116,2023))
----------How to check partitions of a table along with the records-----------
select partition_name,table_name,table_rows from information_schema.partitions where table_name='ineuron_courses8';
insert into ineuron_courses8 values('ALOPS',101,'ML','this is ML course','2019-07-07',3540,'Sudhansu',2019),
('DLCVNLP',102,'ML','this is ML course','2019-07-07',3540,'Sudhansu',2019),
('AWS CLOUD',103,'ML','this is ML course','2020-07-07',3540,'Sudhansu',2020),
('BLOCK CHAIN',104,'ML','this is ML course','2021-07-07',3540,'Sudhansu',2021),
('RL',105,'ML','this is ML course','2022-07-07',3540,'Sudhansu',2022),
('DL',106,'ML','this is ML course','2020-07-07',3540,'Sudhansu',2020),
('INTERVIEW PREP',107,'ML','this is ML course','2021-07-07',3540,'Sudhansu',2021),
('BIG DATA',108,'ML','this is ML course','2022-07-07',3540,'Sudhansu',2022),
('DATA ANALYTICS',109,'ML','this is ML course','2019-07-07',3540,'Sudhansu',2019),
('FSDS',110,'ML','this is ML course','2020-07-07',3540,'Sudhansu',2020),
('FSDA',111,'ML','this is ML course','2021-07-07',3540,'Sudhansu',2021),
('FSBE',112,'ML','this is ML course','2022-07-07',3540,'Sudhansu',2022),
('JAVA',113,'ML','this is ML course','2019-07-07',3540,'Sudhansu',2019),
('CYBER SECURITY',114,'ML','this is ML course','2020-07-07',3540,'Sudhansu',2020),
('DSA',115,'ML','this is ML course','2021-07-07',3540,'Sudhansu',2021)
Error Code: 1526. Table has no partition for value from column_list
Reason:-
------->In this case this error is given because some of the data is not there partitioned for that we can usethe insert ignores option like below.
insert ignore into ineuron_courses8 values('ALOPS',101,'ML','this is ML course','2019-07-07',3540,'Sudhansu',2019),
('DLCVNLP',102,'ML','this is ML course','2019-07-07',3540,'Sudhansu',2019),
('AWS CLOUD',103,'ML','this is ML course','2020-07-07',3540,'Sudhansu',2020),
('BLOCK CHAIN',104,'ML','this is ML course','2021-07-07',3540,'Sudhansu',2021),
('RL',105,'ML','this is ML course','2022-07-07',3540,'Sudhansu',2022),
('DL',106,'ML','this is ML course','2020-07-07',3540,'Sudhansu',2020),
('INTERVIEW PREP',107,'ML','this is ML course','2021-07-07',3540,'Sudhansu',2021),
('BIG DATA',108,'ML','this is ML course','2022-07-07',3540,'Sudhansu',2022),
('DATA ANALYTICS',109,'ML','this is ML course','2019-07-07',3540,'Sudhansu',2019),
('FSDS',110,'ML','this is ML course','2020-07-07',3540,'Sudhansu',2020),
('FSDA',111,'ML','this is ML course','2021-07-07',3540,'Sudhansu',2021),
('FSBE',112,'ML','this is ML course','2022-07-07',3540,'Sudhansu',2022),
('JAVA',113,'ML','this is ML course','2019-07-07',3540,'Sudhansu',2019),
('CYBER SECURITY',114,'ML','this is ML course','2020-07-07',3540,'Sudhansu',2020),
('DSA',115,'ML','this is ML course','2021-07-07',3540,'Sudhansu',2021)
----------How to check partitions of a table along with the records-----------
select partition_name,table_name,table_rows from information_schema.partitions where table_name='ineuron_courses8';
-------How the range column partition inserts the data we can crosscheck like below-------
(comparison cross-check)
select ('ALOPS',105,2019)>('FSDS',110,2021)-----it given the result 0 means flase
select ('ALOPS',105,2019)<('FSDS',110,2021)----it given the result 1 means true
select ('a')<('b')--------------------
----->In this case it will crosscheck each and every combination like above if the result is 1 it will insert datato that exact matching partition otherwise it will not insert.
List column partition
Difference b/w list and list column partitions:-
List partition:-
---->In the case of list, it is trying to consider integer(numerical values).
List column partition:-
------>In the case of the list column it is trying to consider categorical values.
create table ineuron_courses9(
course_name varchar(50),
course_id int(10),
course_title varchar(50),
course_desc varchar(50),
launch_date date,
course_fee int,
course_mentor varchar(60),
course_launch_year int)
partition by list columns(course_name)(
partition p0 values in('ALOPS','DATA ANALYTICS','BLOCK CHAIN'),
partition p1 values in('FSDS','CYBER SECURITY','INTERVIEW PREP'),
partition p2 values in('JAVA','FSBE','BIG DATA','FSBE'))
Error Code: 1495. Multiple definition of same constant in list partitioning
Reason:-
---->In the above, I have mentioned the same column data value for the same partition.
create table ineuron_courses9(
course_name varchar(50),
course_id int(10),
course_title varchar(50),
course_desc varchar(50),
launch_date date,
course_fee int,
course_mentor varchar(60),
course_launch_year int)
partition by list columns(course_name)(
partition p0 values in('ALOPS','DATA ANALYTICS','BLOCK CHAIN'),
partition p1 values in('FSDS','CYBER SECURITY','INTERVIEW PREP'),
partition p2 values in('JAVA','FSBE','BIG DATA'));
insert ignore into ineuron_courses9 values('ALOPS',101,'ML','this is ML course','2019-07-07',3540,'Sudhansu',2019),
('DLCVNLP',102,'ML','this is ML course','2019-07-07',3540,'Sudhansu',2019),
('AWS CLOUD',103,'ML','this is ML course','2020-07-07',3540,'Sudhansu',2020),
('BLOCK CHAIN',104,'ML','this is ML course','2021-07-07',3540,'Sudhansu',2021),
('RL',105,'ML','this is ML course','2022-07-07',3540,'Sudhansu',2022),
('DL',106,'ML','this is ML course','2020-07-07',3540,'Sudhansu',2020),
('INTERVIEW PREP',107,'ML','this is ML course','2021-07-07',3540,'Sudhansu',2021),
('BIG DATA',108,'ML','this is ML course','2022-07-07',3540,'Sudhansu',2022),
('DATA ANALYTICS',109,'ML','this is ML course','2019-07-07',3540,'Sudhansu',2019),
('FSDS',110,'ML','this is ML course','2020-07-07',3540,'Sudhansu',2020),
('FSDA',111,'ML','this is ML course','2021-07-07',3540,'Sudhansu',2021),
('FSBE',112,'ML','this is ML course','2022-07-07',3540,'Sudhansu',2022),
('JAVA',113,'ML','this is ML course','2019-07-07',3540,'Sudhansu',2019),
('CYBER SECURITY',114,'ML','this is ML course','2020-07-07',3540,'Sudhansu',2020),
('DSA',115,'ML','this is ML course','2021-07-07',3540,'Sudhansu',2021)
----------How to check partitions of a table along with the records-----------
select partition_name,table_name,table_rows from information_schema.partitions where table_name='ineuron_courses9';
Sub Partition
------>Partition inside partition is called sub partition.(we can take any of the partition)
------>we can create the more number of partition as the complexity of my data we can increase the partitionthere is no issue.
create table ineuron_courses10(
course_name varchar(50),
course_id int(10),
course_title varchar(50),
course_desc varchar(50),
launch_date date,
course_fee int,
course_mentor varchar(60),
course_launch_year int)
partition by range (course_launch_year)
subpartition by hash(course_launch_year)
subpartitions 5 (
partition p0 values less than(2019),
partition p1 values less than(2020),
partition p2 values less than(2021),
partition p3 values less than(2022));
----------How to check partitions of a table along with the records-----------
select partition_name,table_name,table_rows from information_schema.partitions where table_name='ineuron_courses10';
---------Difference b/w windowing function and partition-----------------
Windowing function:-
---->We used to apply a windowing function on the data storage we have done.
Partition
---->We can try to apply at the time of storage itself on the base level.
Comments
Post a Comment