1
00:00:00,150 --> 00:00:07,590
‫Hey, guys, some of you asked a fantastic question regarding the politicians topic that I made in my

2
00:00:08,010 --> 00:00:14,820
‫introduction to database engineering course, and the question was, are you saying I have a big table

3
00:00:15,120 --> 00:00:19,410
‫of customers and I'm on a partition by idea?

4
00:00:19,620 --> 00:00:23,730
‫How do I dynamically horizontally partition those?

5
00:00:24,060 --> 00:00:28,080
‫I'm going to create so many politicians and he show us a way to automate that.

6
00:00:28,500 --> 00:00:35,340
‫I wrote you guys a script in order to do the partitions in an automatic fashion without you actually

7
00:00:35,340 --> 00:00:38,350
‫manually typing that because it gets really tedious.

8
00:00:38,370 --> 00:00:47,400
‫How about we jump into what we have here is a customer table and let's say I'm going to support one

9
00:00:47,400 --> 00:00:48,390
‫billion customers.

10
00:00:48,990 --> 00:00:55,890
‫The default integer field in the ID can support up to four billion, which is due to the bar thirty

11
00:00:55,940 --> 00:00:56,190
‫two.

12
00:00:56,830 --> 00:01:02,630
‫But for some reason, let's say it's a billion, a billion customers, a lot of users, obviously this

13
00:01:02,640 --> 00:01:05,430
‫looks like it's a seventh of the world's population.

14
00:01:06,090 --> 00:01:06,480
‫All right.

15
00:01:06,480 --> 00:01:12,660
‫So what are we want to do is we want to create multiple partitions and each partition we're going to

16
00:01:12,660 --> 00:01:14,850
‫have, let's say, 10 million each.

17
00:01:15,120 --> 00:01:19,590
‫OK, and as a result, we we will end up with how many partitions?

18
00:01:19,590 --> 00:01:24,720
‫If you do the math, it's one hundred partitions about we go to the code, show you how to do it.

19
00:01:24,720 --> 00:01:28,560
‫I wrote it in JavaScript, but you can do it in any other languages.

20
00:01:28,770 --> 00:01:31,860
‫You can do it all, even in school.

21
00:01:32,580 --> 00:01:37,910
‫But I didn't want to introduce you with the new language that we don't have to learn.

22
00:01:38,220 --> 00:01:39,960
‫So I'm going to do with the JavaScript.

23
00:01:40,650 --> 00:01:42,900
‫It's a very with no jasso.

24
00:01:43,230 --> 00:01:45,130
‫It's a very readable.

25
00:01:45,150 --> 00:01:51,240
‫So the point and you get to write the client side code, which is like it's going to be computer back

26
00:01:51,240 --> 00:01:51,810
‫in this case.

27
00:01:51,810 --> 00:01:52,750
‫How about we jump into it?

28
00:01:52,770 --> 00:01:57,120
‫So in this script, guys, I'm going to share with you guys.

29
00:01:57,540 --> 00:02:03,440
‫I have already spun up a docker container and I showed you a monitor.

30
00:02:03,530 --> 00:02:05,040
‫How to do that is very easy.

31
00:02:05,040 --> 00:02:11,100
‫Is just a one line of code, literally just it out here so you guys can have it in case of kind of share

32
00:02:11,100 --> 00:02:11,490
‫the script.

33
00:02:11,490 --> 00:02:19,620
‫So all you have to do is just when you have a docker run dash dash named PGT, give it a name and then

34
00:02:19,620 --> 00:02:24,930
‫do dash e postscripts underscore password then equal.

35
00:02:25,080 --> 00:02:26,040
‫This is the password.

36
00:02:26,040 --> 00:02:26,850
‫That's the password.

37
00:02:27,330 --> 00:02:32,190
‫And then expose the port five, four, three, two, because we're going to need to connect to it.

38
00:02:32,190 --> 00:02:32,440
‫Right.

39
00:02:33,130 --> 00:02:39,780
‫And what else finally do detach and then Posterous that we'll just show you immediately.

40
00:02:40,060 --> 00:02:46,620
‫So I don't really include the detach unless I make sure that my syntax is correct.

41
00:02:46,620 --> 00:02:47,630
‫But never mind.

42
00:02:47,790 --> 00:02:48,380
‫Never mind.

43
00:02:48,900 --> 00:02:50,290
‫But we have now a container.

44
00:02:50,310 --> 00:02:54,620
‫You can just spin it up and now we have it exposed to the to the local machine.

45
00:02:54,960 --> 00:02:58,650
‫Now you can use this script to connect to that.

46
00:02:58,650 --> 00:02:58,870
‫Right.

47
00:02:58,900 --> 00:02:59,730
‫My machine name.

48
00:02:59,730 --> 00:03:05,670
‫Just change your host name instead of Hosain Mac to yours and voila, you should be able to access that

49
00:03:05,670 --> 00:03:05,960
‫stuff.

50
00:03:06,270 --> 00:03:07,080
‫There is the script.

51
00:03:07,080 --> 00:03:08,130
‫I have this note.

52
00:03:08,140 --> 00:03:12,270
‫Yes, we'll have to script, populate customers and create partitions.

53
00:03:12,660 --> 00:03:16,890
‫Great politician will first connect to the Postgres database.

54
00:03:17,040 --> 00:03:17,270
‫Right.

55
00:03:17,550 --> 00:03:24,540
‫So which already exist and the password of the progress using the credential postscripts users as we

56
00:03:24,540 --> 00:03:31,410
‫created right here and then will connect to that Postgres database through the client is going to create

57
00:03:31,410 --> 00:03:32,370
‫a new database.

58
00:03:32,580 --> 00:03:38,040
‫And if it isn't going to drop, it's so careful not to hit this script against an existing database

59
00:03:38,040 --> 00:03:39,710
‫that has a customer table.

60
00:03:39,750 --> 00:03:41,100
‫This is dangerous stuff.

61
00:03:41,910 --> 00:03:46,650
‫So no matter of fact, let's just commit that so you guys don't accidentally.

62
00:03:48,620 --> 00:03:54,080
‫Run this against the real life database, I just added it, so if you want to run it multiple times

63
00:03:54,350 --> 00:03:58,970
‫uncommon this good, if you want to do that, then the next step is to create the database customers.

64
00:03:59,600 --> 00:04:03,200
‫And then once we have the database customer, we don't we no longer need the connection.

65
00:04:03,200 --> 00:04:10,370
‫We can close the actually we now go and connect to the customers database that we just created and connect

66
00:04:10,370 --> 00:04:10,730
‫to it.

67
00:04:10,880 --> 00:04:12,320
‫And they will get to create a table.

68
00:04:13,670 --> 00:04:16,130
‫And this table is going to be called customers.

69
00:04:16,440 --> 00:04:22,340
‫It has to feel for simplicity, ID and name, and it's going to be partitioned by which I explain in

70
00:04:22,340 --> 00:04:23,780
‫the partitioning, of course.

71
00:04:25,950 --> 00:04:31,060
‫We can execute that right, but now this is not enough, we need to create the actual partitions, right?

72
00:04:31,320 --> 00:04:38,250
‫So if we did the math, we are assuming that we have one billion customers and each customer, each

73
00:04:38,250 --> 00:04:40,680
‫table, each participant should have 10 million.

74
00:04:41,030 --> 00:04:47,340
‫So I did a little bit of a quick loop here to loop to create a hundred partitions and each is going

75
00:04:47,340 --> 00:04:48,450
‫to have one million.

76
00:04:48,930 --> 00:04:49,800
‫Ten million rows.

77
00:04:49,800 --> 00:04:50,040
‫Right.

78
00:04:50,280 --> 00:04:52,290
‫So I created two variables here.

79
00:04:52,320 --> 00:04:55,760
‫I times 10 million.

80
00:04:55,800 --> 00:04:56,160
‫Right.

81
00:04:56,190 --> 00:05:01,740
‫So if the first line was this is going to be zero and the second this is going to be 10 million because

82
00:05:01,740 --> 00:05:05,130
‫I plus one zero plus one thousand and then you can apply these lives.

83
00:05:05,180 --> 00:05:07,320
‫This is very simple programming.

84
00:05:07,470 --> 00:05:09,160
‫Nothing fancy really here.

85
00:05:09,450 --> 00:05:15,650
‫And then the partition name is going to be called Customers underscore ID from Android two.

86
00:05:15,690 --> 00:05:17,910
‫So that's the that's the customer.

87
00:05:18,030 --> 00:05:19,350
‫That's the partition name.

88
00:05:19,470 --> 00:05:25,290
‫And every loop you going to be looping through each of them, then creating all this stuff.

89
00:05:25,320 --> 00:05:29,850
‫So now we're going to actually create the table that's called partition name just named here.

90
00:05:30,180 --> 00:05:35,460
‫And then we're going to make it exactly look like the customers in terms of indexing the tables.

91
00:05:36,210 --> 00:05:41,340
‫You can just play with this, really add more fields and then this code doesn't change because we're

92
00:05:41,340 --> 00:05:44,790
‫going to like really inherit from the table structure.

93
00:05:45,790 --> 00:05:53,260
‫And then that's the beauty, that's what we need, we need to attach that partition that we just created

94
00:05:53,710 --> 00:06:01,420
‫to the customer stable and this is the range, the ranges from this and to and just pay attention to

95
00:06:01,420 --> 00:06:03,870
‫the two is actually minus one.

96
00:06:03,880 --> 00:06:12,460
‫So if it's like up until 10 million from zero to 10 million, that means it's from zero to ninety nine

97
00:06:12,460 --> 00:06:12,850
‫million.

98
00:06:12,850 --> 00:06:13,070
‫Nine.

99
00:06:13,100 --> 00:06:16,450
‫And no, no, that's just something I learned the hard way.

100
00:06:17,780 --> 00:06:23,550
‫And then I said it's going to do the second loop, execute the two queries because these are the first

101
00:06:23,560 --> 00:06:23,770
‫quarter.

102
00:06:23,770 --> 00:06:24,600
‫That's the second quarter.

103
00:06:24,610 --> 00:06:29,060
‫These are just now get actually create the partition and then we're going to close that.

104
00:06:29,230 --> 00:06:34,030
‫How are we actually run this where you guys can see it on this very simple stuff?

105
00:06:35,060 --> 00:06:36,320
‫Once you clone your.

106
00:06:38,130 --> 00:06:45,120
‫You can you can clone this reaper, you have this code, just do npm install it, which I already did

107
00:06:45,240 --> 00:06:51,240
‫to install the PJI library, which will allow you to connect to postscripts and then do this zoom in

108
00:06:51,240 --> 00:06:57,940
‫so you guys can see a node index, not the schools, create partitions, dot MDGs.

109
00:06:58,260 --> 00:06:58,700
‫That's it.

110
00:06:59,520 --> 00:07:05,490
‫Once you do that, we have an error, I think, because I'm already connected to the device.

111
00:07:05,830 --> 00:07:09,750
‫Let's just go ahead and sit here and let's run it again.

112
00:07:10,350 --> 00:07:15,410
‫I need to commend this out because I already have the database posterous that has got.

113
00:07:15,420 --> 00:07:15,700
‫All right.

114
00:07:15,840 --> 00:07:16,860
‫So now we get a drop.

115
00:07:16,860 --> 00:07:18,150
‫And as you can see.

116
00:07:19,160 --> 00:07:25,340
‫Look at that, it's creating all this beautiful partitions and that said, we're done and we're going

117
00:07:25,340 --> 00:07:28,630
‫to before we push it, just come in this out for you.

118
00:07:29,530 --> 00:07:38,440
‫And that's it now you will need to populate this thing, right, and it's very simple as you start inserting

119
00:07:38,440 --> 00:07:43,830
‫instant customers that you only need to target the customer stable, that's it.

120
00:07:43,960 --> 00:07:49,210
‫And that database will do its magic based on the idea, will try to insert to the right database.

121
00:07:49,210 --> 00:07:55,120
‫So I don't hear a small script that creates a 10 million customers in one shot.

122
00:07:55,120 --> 00:07:56,530
‫Right in a loop.

123
00:07:56,540 --> 00:08:00,790
‫So again, for each one hundred times we're going to do this 100 times.

124
00:08:01,210 --> 00:08:02,890
‫Let's do this again.

125
00:08:04,390 --> 00:08:08,280
‫No capewell age group.

126
00:08:09,850 --> 00:08:18,040
‫Customers all of a sudden, as it were, just starting those customers that said, I'm not going to

127
00:08:18,040 --> 00:08:22,660
‫go through all of them and wait because that's just going to take a long time to complete.

128
00:08:22,660 --> 00:08:26,970
‫But I'm just going to wait for maybe one or two amyloids while this runs.

129
00:08:27,010 --> 00:08:30,800
‫Let's go ahead and show you how to connect to the actual database at.

130
00:08:32,590 --> 00:08:39,850
‫This is how but let's do it again, so a Delcourt executed it PGT part, that's my my container name,

131
00:08:40,150 --> 00:08:46,880
‫bicycle Nasho, they use it as Postgres and the database dashboard is customers that boom.

132
00:08:46,880 --> 00:08:54,850
‫And just like that, you're in the database now you can do like a select max I.D. from customer stable

133
00:08:55,030 --> 00:09:01,270
‫and tells you what is the maximum ID so we can show you the customer and all that stuff like all.

134
00:09:01,780 --> 00:09:02,800
‫So it looks like we're OK.

135
00:09:02,800 --> 00:09:04,480
‫We have started 10 million customers.

136
00:09:04,900 --> 00:09:07,390
‫No, let's describe the stable that we just created.

137
00:09:08,010 --> 00:09:14,020
‫If you few customers, you can see that we have an ID, we have the name and we also have a partition

138
00:09:14,020 --> 00:09:14,710
‫range.

139
00:09:14,710 --> 00:09:21,580
‫ID says I have 100 partitions, but it's not going to show you because there is a lot of partitions

140
00:09:21,970 --> 00:09:26,500
‫you can use Dasch slash D plus to show all of the partitions.

141
00:09:26,500 --> 00:09:29,500
‫So old Dashty plus customers.

142
00:09:30,700 --> 00:09:31,750
‫Customers.

143
00:09:33,030 --> 00:09:38,400
‫And as that is going to show you all the beautiful partitions and you can just start querying all of

144
00:09:38,400 --> 00:09:40,320
‫them, so that's query the first one.

145
00:09:41,340 --> 00:09:43,200
‫These are just normal tables at this point, right?

146
00:09:43,620 --> 00:09:44,640
‫They can do select.

147
00:09:45,840 --> 00:09:49,110
‫I don't know, count star from Bo.

148
00:09:50,370 --> 00:09:56,280
‫And is going to to the stable normally, like just over and he goes, that's the number I talked to.

149
00:09:56,280 --> 00:09:57,780
‫I told you about our guys.

150
00:09:58,080 --> 00:10:01,060
‫That was a quick video showing you how to automate this stuff.

151
00:10:01,080 --> 00:10:03,230
‫I'm going to share this code with you guys.

152
00:10:03,240 --> 00:10:04,370
‫I'm going to see you in the next one.

153
00:10:04,380 --> 00:10:05,850
‫You guys say goodbye.