AWS RedShift

Columnar storage, Parallel & distributed queries across multiple nodes, can use existing BI tools ( standard SQL  , JDBC, ODBC support) ,  scale-in/out, scale up/down, column level compression , automated admin activities like snapshots/backups etc.
--
Sample URLs

HOST :  lab.cwfgigx176xf.us-west-2.redshift.amazonaws.com
Endpoint : lab.cwfgigx176xf.us-west-2.redshift.amazonaws.com:5439/lab
JDBC :  jdbc:redshift://lab.cwfgigx176xf.us-west-2.redshift.amazonaws.com:5439/lab
ODBC : Driver={Amazon Redshift (x64)}; Server=lab.cwfgigx176xf.us-west-2.redshift.amazonaws.com; Database=lab
--
Does not use indexes for sequential scan like traditional db. 
--
To see disk capacity in cluster nodes: 

SELECT
  owner AS node,
  diskno,
  used,
  capacity,
  used/capacity::numeric * 100 as percent_used
FROM stv_partitions
WHERE host = node
ORDER BY 1, 2;

--
To see table usage ( blocks of 1MB each ) :

SELECT name, count(*) FROM stv_blocklist JOIN (SELECT DISTINCT name, id as tbl from stv_tbl_perm) USING (tbl) GROUP BY name;

--

pgweb- browser based PostgreSQL client. You can use pgweb for connecting to AWS Redshift. You can download it from https://github.com/sosedoff/pgweb/releases

--
The user '.....'  can not assume IAM role '........

You get this error when you try to execute a command with credentials which includes the arn for given IAM role and that iam role is not  attached to the cluster.  A IAM role which is attached to the cluster with appear "in-sync" status on "Manage IAM Roles" window which opens up when you click "see IAM roles" under cluster properties.
--
To keep Redshift costs low

*) Encode your columns to compress columns according to data types   
*) vacuum the tables to reclaim space for deleted data.

 "When you use Amazon Redshift Enhanced VPC Routing, Amazon Redshift forces all COPY and UNLOAD traffic between your cluster and your data repositories through your Amazon VPC. By using Enhanced VPC Routing, you can use standard VPC features, such as VPC security groups, network access control lists (ACLs), VPC endpoints, VPC endpoint policies, internet gateways, and Domain Name System (DNS) servers, as described in the Amazon VPC User Guide. "   AWS documentation.

 Redshift will not be able to access S3 VPC endpoint without enabling Enhanced VPC Routing.

 https://aws.amazon.com/blogs/big-data/10-best-practices-for-amazon-redshift-spectrum/

--
 You can enable Cross-Region Snapshots for Redshift cluster.
--
For Redshift , you can enable encryption ( using SSE-KMS ) even after launching the cluster and it automatically encrypts the cluster after that. You can also encrypt Redshift cluster using the top level keys from HSM.
--
Scalability : You can easily scale Redshift cluster up or down. Resizing the cluster involves replacing the old cluster with new cluster. The old cluster goes into read-only mode until new cluster is up and running and then it replaces the old one. 
--

Comments

Post a Comment

Popular posts from this blog

SQL

Joins - Not always very obvious ( Following example uses Hive ). create table x_temp ( name string, p_date int  ) insert into x_temp(name,p_date) values ('x1',20181025),('x2', 20181026), ('x3',20181027) create table y_temp ( name string, s_date int  ) insert into y_temp(name,s_date) values ('x1',20181025),('x2', 20181026), ('x3',20181027) create table z_temp ( name string, p_date int, s_date int ) insert into z_temp(name,p_date,s_date) values ('z1','20181025','20181025') select x.*, y.* from x_temp x, y_temp y, z_temp z  where x.p_date != z.p_date and  y.s_date != z.s_date) generates 4 records... x.name,x.p_date,y.name,y.s_date x2, 20181026, x2, 20181026 x3, 20181027, x2, 20181026 x2, 20181026, x3, 20181027 x3, 20181027, x3, 20181027 select x.*, y.* from x_temp x, y_temp y, z_temp z  where x.p_date != z.p_date or  y.s_date != z.s_date) generates 8 records x.name,x.p_date,y....
Read more

Analytics

Types of Analytics ( Notes from AWS Course )  Descriptive - What happened ?  hindsight  Diagnostic - Why did it  happen?  hindsight & insight  Predictive  - What will happen ? insight & foresight  Prescriptive - What should I do ? foresight  Cognitive & AI  - What are recommended actions ? foresight & hypothesis input  Popular BI Tools:  https://www.cio.com/article/3284415/10-bi-tools-for-data-visualization.html
Read more

DBeaver

Recently I used this tool for connecting to Hive via Knox gateway.  Good thing about this tool is that it automatically downloads the driver and configures it. That  is a pain in tools like Squirrel.  It also makes a JDBC connection to Hive.  Below is how the HOST and JDBC URL settings look like : HOST : <KNOX_HOST>:8443/;ssl=true;transportMode=http;httpPath=gateway/<topology>/hive JDBC URL :  jdbc:hive2://<KNOX_HOST>:8443/;ssl=true;transportMode=http;httpPath=gateway/<topology>/hive What I noticed on my setup was that if you keep the default port which 10000 and you set it to 8443 , it does not work. So you have to keep it blank.  That may be specific to my setup. Also you might notice that ssl=true but there are  no  sslTrustStore= < Path to the “.jks” file in the NiFi node > ;trustStorePassword= < TrustStore Password>  parameters. The reason is if you import the gateway cert  into the trus...
Read more