1 00:00:01,080 --> 00:00:10,290 We know that Dynamo really is and no sequel database and it has several advantages over traditional 2 00:00:10,290 --> 00:00:19,890 relational databases how ever we might find ourselves in situations where we want to run complex queries 3 00:00:19,890 --> 00:00:29,250 like we do on Siku our relational databases and Dynamo is not the best way to perform these queries. 4 00:00:29,280 --> 00:00:37,230 We often perform these queries for the purpose of reporting our for the purpose of in politics. 5 00:00:37,380 --> 00:00:46,160 In this section we are going to learn how to load the dynamo data into Amazon red shift. 6 00:00:46,160 --> 00:00:55,440 Red shift is a data warehousing solution from AWOS and we can use it to perform sequel based queries 7 00:00:55,500 --> 00:00:59,690 on our dynamically table data. 8 00:00:59,680 --> 00:01:05,480 Now you don't have to do this hands on activity if you don't want to. 9 00:01:05,900 --> 00:01:11,130 You can just watch the videos as I demonstrate this to you. 10 00:01:11,180 --> 00:01:20,000 And even by just watching these videos you'll be able to appreciate the benefits of using a sequence 11 00:01:20,000 --> 00:01:27,920 like platform on top of Dynamo DV but equally you're welcome to build along with me and perform the 12 00:01:27,920 --> 00:01:30,540 demos within your environment. 13 00:01:30,560 --> 00:01:40,630 So let's begin refers to have to create a red shift cluster and then lo Dynamo the data into it and 14 00:01:40,630 --> 00:01:50,200 then we can connect to the cluster using a sequel to like sequela workbench and perform our sequel queries 15 00:01:50,710 --> 00:01:56,480 these queries are performed on the copy of Dynamo the data. 16 00:01:56,710 --> 00:02:06,330 They do not Kinsey's to the Dynamo the table capacity the operation of copying data or from dynamo Leavey. 17 00:02:06,460 --> 00:02:15,430 Well of course zeem appropriate table capacity and obviously there are certain differences in these 18 00:02:15,430 --> 00:02:25,120 key mine used by dynamo D.B and the one that is used by ratshit and we have to make sure that we make 19 00:02:25,120 --> 00:02:28,740 suitable adjustments for that in our leader. 20 00:02:28,990 --> 00:02:36,610 For example dynamo table names can have up to two hundred and fifty five characters why redshift only 21 00:02:36,610 --> 00:02:39,580 allows up to 127 characters. 22 00:02:39,580 --> 00:02:42,270 This may not be a major issue though. 23 00:02:42,940 --> 00:02:50,980 I like Danimal the readership does not allow us to use Dot and Dash characters in it's table names underscore 24 00:02:50,980 --> 00:02:59,710 characters are allowed in the and also we cannot use the redshift reserved words as table names. 25 00:03:00,860 --> 00:03:05,200 We know that Dynamo D-MI does not have now rallies. 26 00:03:05,420 --> 00:03:14,780 We have to specify how friendship and upbraids empty Raley's from dynamo D-B either as nulls are as 27 00:03:15,240 --> 00:03:26,120 antifreeze also recip uses different data than dynamo D.B and we have ensured that Dynamo D-B attributes 28 00:03:26,120 --> 00:03:38,580 can be properly accommodated in the redshift table fields. 29 00:03:38,590 --> 00:03:48,220 That said let's create a new redshift lustrum open the wretched console and launch MDO Blaster 30 00:03:52,180 --> 00:04:00,990 less galiot teeny tiny Maidie Blaster and database name as say D-B next. 31 00:04:01,180 --> 00:04:02,540 Well you the same. 32 00:04:02,540 --> 00:04:04,120 The forebode. 33 00:04:04,420 --> 00:04:14,690 Let's use AWOS user as Master username and give me the password and do make a note of the Basford. 34 00:04:18,700 --> 00:04:25,510 Continue on the screen we will simply accept the default and continue 35 00:04:28,130 --> 00:04:34,620 the next screen has a few more settings. 36 00:04:34,770 --> 00:04:38,740 We are not going to encrypt the database. 37 00:04:38,850 --> 00:04:44,240 I'm going to use the default BBC and default subnet group. 38 00:04:44,520 --> 00:04:51,710 We want the cluster to be accessible over the Internet are from outside the BBC 39 00:04:54,940 --> 00:04:57,210 for the security groups. 40 00:04:57,220 --> 00:05:05,950 I'm going to use the data for security through the security group should allow inbound connections from 41 00:05:05,950 --> 00:05:08,220 your IP address. 42 00:05:08,350 --> 00:05:16,650 So let's go over to easy to console click on Tea Party groups. 43 00:05:16,670 --> 00:05:26,960 I'm going to select the default one and under in-bound settings we need to add a room to allow connections 44 00:05:27,050 --> 00:05:41,210 on the redshift board so plague ended select redshift from the drop down so this could be either your 45 00:05:41,300 --> 00:05:43,750 IP or anywhere. 46 00:05:43,760 --> 00:05:49,750 I'm going to set it to anywhere and click save. 47 00:05:49,760 --> 00:05:50,580 All right. 48 00:05:50,660 --> 00:05:53,600 Back in the red shift console now 49 00:05:56,350 --> 00:06:05,820 we don't need Iglauer watch alarms and will need to create a new high end role to work with these clusters. 50 00:06:06,130 --> 00:06:07,030 Let's open. 51 00:06:07,030 --> 00:06:09,280 I am in a new tab 52 00:06:11,880 --> 00:06:14,830 and from Rose create a new role. 53 00:06:14,860 --> 00:06:25,590 We want this role to be able to access red shift as well as dynamo D-B select redshift customizable 54 00:06:25,590 --> 00:06:31,990 since we want to add dynamo divi permissions and click next 55 00:06:36,150 --> 00:06:46,890 search for red shift and select Red Shope full access and then search for Dynamo D.B and select dynamo 56 00:06:46,890 --> 00:06:49,810 D-B full access. 57 00:06:50,040 --> 00:07:04,570 Click next and let's name the role as a redshift Dynamo the role create role back and the redshift console 58 00:07:04,720 --> 00:07:11,500 refresh the roles dropdown and select the role that we just created. 59 00:07:11,720 --> 00:07:18,400 What kind of name reveal the configuration wants and launch the classroom. 60 00:07:20,740 --> 00:07:23,880 The plaster is being created. 61 00:07:24,260 --> 00:07:29,150 Please close and our cluster should be ready in a few minutes. 62 00:07:30,400 --> 00:07:40,420 While our cluster is getting created let's click on Connect client from the left side bar and this green 63 00:07:40,420 --> 00:07:46,040 Shade's steps to connect to red shift from our computer. 64 00:07:46,360 --> 00:07:56,330 So we need to install sequel workbench to sequel work bench is a free cross-platform sequel. 65 00:07:56,350 --> 00:07:59,680 What a tool to download this too. 66 00:07:59,680 --> 00:08:02,050 We can simply google for it. 67 00:08:02,080 --> 00:08:11,620 Our goal is to double double double our sequel haif when workbench workbench are you and click on downloads 68 00:08:11,770 --> 00:08:13,560 and download the package. 69 00:08:15,850 --> 00:08:23,560 And we simply extract it to a folder on our computer and we should be good to go. 70 00:08:23,560 --> 00:08:28,760 No special installation is needed here as a prerequisite. 71 00:08:28,840 --> 00:08:32,950 You must have the latest version of JR. 72 00:08:32,960 --> 00:08:39,210 We are JDK our own time and Wineman to our job. 73 00:08:39,210 --> 00:08:48,010 I developed a kit installed on your computer and if you don't have one you can download and install 74 00:08:48,010 --> 00:08:52,620 it from John dotcom website. 75 00:08:52,630 --> 00:09:00,800 I already have it installed on my computer back in the red shift console. 76 00:09:01,330 --> 00:09:06,490 Let's see if our cluster is ready. 77 00:09:06,490 --> 00:09:08,780 Antedates said. 78 00:09:08,980 --> 00:09:14,730 Let's click on Connect blind link. 79 00:09:15,010 --> 00:09:22,200 We have completed this step one step two is to download the red shift driver. 80 00:09:22,660 --> 00:09:26,920 So let's download TABC 4.2 driver 81 00:09:31,660 --> 00:09:44,600 and in step 3 select the cluster we created and all of us well show us the connection you are know this 82 00:09:44,750 --> 00:09:56,360 ABC you are at and let's launch the sequel workbench by launching the sequel we're bench Jarve. 83 00:09:59,670 --> 00:10:12,900 We first create a new roof line let's call it redshift then under driver like the Amazon redshift and 84 00:10:12,900 --> 00:10:17,080 we might need to specify the path to the driver. 85 00:10:18,290 --> 00:10:24,540 Play yes and then select the driver Jarre find that be downloaded 86 00:10:28,040 --> 00:10:33,780 then based on the JVC you Arel from the command line screen. 87 00:10:38,640 --> 00:10:48,520 And enter the cluster master username and password that we specify by creating the blusterer set order 88 00:10:48,640 --> 00:10:53,490 me to to and hit OK. 89 00:10:56,440 --> 00:11:01,480 We are now connected to test the connection. 90 00:11:01,510 --> 00:11:10,750 We could simply list the tables using a query like so select start from information underscores Kiem 91 00:11:10,880 --> 00:11:18,440 dart tables semi-colon and run it using the triangle eye on our control. 92 00:11:18,450 --> 00:11:27,610 The we can see the information about the tables here awesome. 93 00:11:28,100 --> 00:11:36,500 So now that we have created a chip cluster and we are able to connect to it from our computer in the 94 00:11:36,500 --> 00:11:44,930 next video I'll show you how to create a new redshift of table and copy data from dynamo Nimi table 95 00:11:44,930 --> 00:11:47,130 to this redshift table.