So a 1 TB slice contains 1,000,000 blocks, a table might consume 2000 blocks, each block fitting in as much data as it can. To recap, a slice contains blocks that are 1 MB big. What's actually happening at the slice level, is the blocks themselves are being re-arranged in the order specified. This results is the compute node skipping data it knows it doesn't need to look out, for example you may only want cars made before 2000, and so Redshift knows that the values after 2000 start 35% up the slice, so it goes from 0-34%. This is because the Compute Node now has all the dates in order and knows where certain date values start and stop. Got a table of cars and it's sorted by manufacture date? Then a query which contains manufacture date in it's 'where' clause will finish faster than if the table was unsorted. A sort key determines in what order a column, or multiple get stored on the slice. Just like how you can determine the Dist Key at the time of table creation, you can also determine a sort key. Sorting, along with something called Zone Mapping, is how you speed up Redshift at a node level.ĭiagram of a Redshift Cluster (to remind you) Once you have loaded the data you can run sample queries like below in your SQL Workbench.While Distribution determines what node the data gets stored on, Sorting determines how it gets stored on the Node. Ensure that in below query you replace "" with your ARN.Ĭopy users from 's3://awssampledbuswest2/tickit/allusers_pipe.txt'Ĭredentials 'aws_iam_role=arn:aws:iam::123456789123:role/redshiftrole'ĭelimiter '|' region 'us-west-2' copy users from 's3://awssampledbuswest2/tickit/allusers_pipe.txt'Ĭopy venue from 's3://awssampledbuswest2/tickit/venue_pipe.txt'Ĭopy category from 's3://awssampledbuswest2/tickit/category_pipe.txt'Ĭopy date from 's3://awssampledbuswest2/tickit/date2008_pipe.txt'Ĭopy event from 's3://awssampledbuswest2/tickit/allevents_pipe.txt'ĭelimiter '|' timeformat 'YYYY-MM-DD HH:MI:SS' region 'us-west-2' Ĭopy listing from 's3://awssampledbuswest2/tickit/listings_pipe.txt'Ĭopy sales from 's3://awssampledbuswest2/tickit/sales_tab.txt'ĭelimiter '\t' timeformat 'MM/DD/YYYY HH:MI:SS' region 'us-west-2' Venueid smallint not null distkey sortkey,ĭateid smallint not null distkey sortkey, In the SQL Workbench copy/paste the below query and execute to create a table. Now we will load some sample data which is provided by AWS and kept on S3.If you connection is successful you will see results in the window.If everything is configured correctly.Password:- Enter password of the DB user. Username:- DB username you mentioned during cluster creation. URL:- Mention the JDBC URL you saved earlier. Now in the "Select Connection Profile" windowįill details as below. You can also refer the image below for this.ĭriver:- Select the Redshift driver you added.In the "Manage Drivers" window click on the folder icon, browse to the location of the Redshift driver you downloaded earlier and select it.įill other details in "Manage Drivers" Window as below.Ĭlassname:- .Driver.In the bottom left of the "Select Connection Profile" window click on "Manage Drivers".In my case I am using Windows 10 64-bit so the exe name is SQLWorkbench64. You just have to click on 32-bit or 64-bit exe as per your OS version. Copy the JDBC URL and save it in notepad.In the "Configuration" tab look for JDBC URL.If you have followed the last post it will be "testdw".
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |