AWS RedShift

RedShift

• Data Warehouse
• Meant to support OLAP (not OLTP), column oriented and massively parallel scale-out architecture
• OLTP meant to Analytics, aggregation of data
• Master & slave nodes
• It does not require/create indexes, materialised views, thereby faster & uses less data than traditional relational databases
• Supports columnar storage like Parquet, ORC
• But it has dist_key and sort_key
• dist_key
• It is the column on which its distributed on each node
• Rows with same value of this column are guaranteed to be on the same node
• sort_key
• It is the column on which data is sorted on each node
• Only one sort_key is permitted
• RedShift doesn't complain on duplicate data even on primary key
• Advantages
• Faster, since it need not check if primary key already exists or not
• Performance, query optimization
• Disadvantages
• Chances of improper data (duplicate data)
• Its upto the user to send proper data to RedShit, user has to handle the improper data before sending it to RedShift Cluster

1) Create a RefShift cluster

2) Connect to cluster and Create tables 

   (Using SQL Workbench - recommended or any DB visualizer)

   If not use Redshitf Query Editor it self

3) Create an IAM role for Redshift with S3 Read only access

4) Attach IAM S3 role to RedShift

5) Suppose your data is in S3, load your data from S3

  copy dimproduct    #<table_name_from_redshift>

  from 's3://redshift-load-queue-test/dimproduct.csv' 

  iam_role '<IAM Role created in step-3>/RedShift-S3-Role' 

  region 'us-east-1'

  format csv

  delimiter ','

6) Upload huge data as gz files

   sales1.txt.gz, sales2.txt.gz, sales3.txt.gz, sale4.txt.gz

   Instead of creating single files, create one manifest file 

   manifest.txt

         {

          "entries": [

              {"url":"s3://redshift-load-queue-test/sales1.txt.gz", "mandatory":true},

              {"url":"s3://redshift-load-queue-test/sales2.txt.gz", "mandatory":true},

              {"url":"s3://redshift-load-queue-test/sales3.txt.gz", "mandatory":true},

              {"url":"s3://redshift-load-queue-test/sales4.txt.gz", "mandatory":true}

          ]   

         }

  copy factsales    

  from 's3://redshift-load-queue-test/manifest.txt' 

  iam_role '<IAM Role created in step-3>' 

  region 'us-east-1'

  GZIP

  delimiter '|'

  manifest

7) Copy JSON data

   # Json data should not be in a list

   # It should in individual elements

   copy dimdate

   from 's3://redshift-load-queue-prabhath/dimdate.json'

   region 'us-east-1'

   iam_role '<IAM Role created in step-3>/RedShift-S3-Role'

   json as 'auto'

8) Find out any load errors

   select * from stl_load_errors

9) Integrate Kinesis Streaming FireHose to destination as RedShift

    Given the details, Kinesis form this query for you

      COPY firehose_test_table (ticker_symbol, sector, change, price) 

      FROM 's3://redshift-load-queue-<>/stream2020/05/22/04/test-stream-4-2020-05-22-04-19-21-db425054-695f-4fd1-8721-fc07cdeea369.gz' 

      CREDENTIALS 'aws_iam_role='<IAM Role created in step-3>/RedShift-S3-Role'

      JSON 'auto' gzip;

Boto S3 How to access buckets other than us-east-1

Boto S3 - How to access buckets other than us-east-1?

Trying to connect mumbai region (ap-south-1) bucket, but not able to connect.
Esp in Boto 2 versions, there is some issue, getting below errors:

• boto.exception.S3ResponseError: S3ResponseError: 301 Moved Permanently
• boto.exception.S3ResponseError: S3ResponseError: 400 Bad Request

Solution:

You need to mark S3_USE_SIGV4 flag to True, else it will throw you error.

from boto.s3.connection import S3Connection, Location
import os

os.environ['S3_USE_SIGV4'] = 'True'
conn = S3Connection(S3_KEY, S3_SECRET, host='s3.ap-south-1.amazonaws.com', calling_format=boto.s3.connection.OrdinaryCallingFormat())
print conn
client_buk_obj = conn.get_bucket('test-mumbai-region-test')
print client_buk_obj
os.environ['S3_USE_SIGV4'] = 'False'

Boto - Uploading file to a specific location on Amazon S3

When I upload a file from my local system to S3

• Say media/downloads/logo.png it writes to <bucket>/media/downloads/logo.png
• Suppose if I want to write to <bucket>/logo.png instead of media/downloads, please find the below script

import subprocess
import mimetypes
from boto.s3.connection import S3Connection, Location
from boto.s3.key import Key
import boto
import os

S3_BUCKET = ''
S3_KEY = ''
S3_SECRET = ''
conn = S3Connection(S3_KEY, S3_SECRET, calling_format=boto.s3.connection.OrdinaryCallingFormat())
bucket = conn.get_bucket(S3_BUCKET)
print bucket

key_name = 'logo.png'
path = 'media/img/'
full_key_name = os.path.join(path, key_name)
new_key = Key(bucket)
new_key.key = 'logo.png'
ctype = mimetypes.guess_type(full_key_name)[0] or "application/x-octet-stream"
new_key.set_metadata('Content-Type', ctype)
new_key.set_contents_from_filename(full_key_name)
if new_key.exists() == True:
   bucket.set_acl("public-read",new_key.key)
   url = new_key.generate_url(0, 'GET', None, False)
   print url


Thanks for reading.

S3 Direct Upload Python Django

Using Pyhton Boto API, we can interact with Amazon S3 servers (for GET, PUT, POST etc)
We can also directly upload files to Amazon S3 from the client browser using Browser Uploads to S3 using HTML POST Forms.

Ref:
https://aws.amazon.com/articles/Java/1434

Prerequisites:
S3_BUCKET
S3_KEY
S3_SECRET
S3_URL

Note:
As per Amazon API, we need to encode policy format to base64 and further generate signature with SHA1
Both Policy and Signature need to be posted
Expiration : You can define the expiration time
acl: public-read/private
$key: upload path startes with
Check the POST S3 Url in HTML
Once the document is successfully uploaded to S3, S3 URL is shown on the page

Django Pyhton View:

import base64
import hmac, hashlib
import re

def direct_s3_upload(request):
  my_bucket = None
  policy_document = None
  policy_base_64 = signature = cors_xml = ""
  try:
    policy_document = '{"expiration": "2020-01-01T00:00:00Z", \
                       "conditions": [ \
                         {"bucket": "%s"}, \
                         {"acl": "public-read"}, \
                         ["starts-with", "$key", "uploads/"], \
                         ["content-length-range", 0, 524288000] \
                       ] \
                    }' % (<S3_BUCKET>)

    whitespace = re.compile(r'\s+')
    policy_document = whitespace.sub('', policy_document)
    policy_base_64 = base64.b64encode(whitespace.sub('', policy_document))
    signature = base64.b64encode(hmac.new(S3_SECRET, policy_base_64, hashlib.sha1).digest()) 
    dic1 = { 
           'MY_BUCKET_NAME': <S3_BUCKET>,
           'MY_AWS_KEY_ID': <S3_KEY>, 
           'MY_POLICY' : policy_base_64,
           'MY_SIGNATURE' : signature,
        }
  except:
    write_exception("direct_s3_upload")
  return render_to_response('test_s3_upload.html', context_instance=RequestContext(request, dic1))

HTML:  (test_s3_upload.html)

<html> 
  <head>
    <title>S3 POST Form</title> 
    <meta http-equiv="Content-Type" content="text/html; charset=UTF-8" />
    <script>
      var bucketName = '{{MY_BUCKET_NAME}}';
      var AWSKeyId   = '{{MY_AWS_KEY_ID}}';
      var policy     = '{{MY_POLICY}}';
      var signature  = '{{MY_SIGNATURE}}';

      function S3ToolsClass() {
        var _handle_progress = null;
        var _handle_success  = null;
        var _handle_error    = null;
        var _file_name       = null;

        this.uploadFile = function(file, progress, success, error) {
          _handle_progress = progress;
          _handle_success  = success;
          _handle_error    = error;
          _file_name       = file.name;

          console.log(file.name)
          var fd = new FormData();
          fd.append('key', "uploads/" + file.name);
          fd.append('AWSAccessKeyId', AWSKeyId);
          fd.append('acl', 'public-read');
          fd.append('policy', policy);
          fd.append('signature', signature);
          fd.append("file",file);

          var xhr = new XMLHttpRequest({mozSystem: true});
          xhr.upload.addEventListener("progress", uploadProgress, false);
          xhr.addEventListener("load", uploadComplete, false);
          xhr.addEventListener("error", uploadFailed, false);
          xhr.addEventListener("abort", uploadCanceled, false);
          xhr.open('POST', 'https://s3.amazonaws.com/' + bucketName + '/');

          xhr.send(fd);
        }

        function uploadProgress(evt) {
          if (evt.lengthComputable) {
            var percentComplete = Math.round(evt.loaded * 100 / evt.total);
            _handle_progress(percentComplete);
          }
        }

        function uploadComplete(evt) {
          if (evt.target.responseText == "") {
            console.log("Upload complete - success") 
            _handle_success(_file_name);
          } else {
            console.log("Upload complete - not success") 
            _handle_error(evt.target.responseText);
          }
        }

        function uploadFailed(evt) {
          console.log("upload Failed")
          _handle_error("There was an error attempting to upload the file." + evt);
        }

        function uploadCanceled(evt) {
          console.log("upload cancelled")
          _handle_error("The upload has been canceled by the user or the browser dropped the connection.");
        }
      }
      var S3Tools = new S3ToolsClass();
      
      function uploadFile() {
        var file = document.getElementById('file').files[0];
        S3Tools.uploadFile(file, handleProgress, handleSuccess, handleError);
      }

      function handleProgress(percentComplete) {
        document.getElementById('progressNumber').innerHTML = percentComplete.toString() + '%';
      }

      function handleSuccess(fileName) {
        document.getElementById('progressNumber').innerHTML = 'Done!';
        document.getElementById('resultant_s3_url').innerHTML = 'https://s3.amazonaws.com/' + bucketName + '/uploads/' + fileName;
      }

      function handleError(message) {
        document.getElementById('progressNumber').innerHTML = 'Error: ' + message;
      }
    </script>
  </head>
  <body>
    <form id="form" enctype="multipart/form-data" method="post">
      <div class="row">
        1. Select a File<br>
        <input type="file" name="file" id="file"/>
      </div>
      <br>
      <div class="row">
        2. Upload File<br>
        <input type="button" onclick="uploadFile()" value="Upload" />
        <br/>
        <span id="progressNumber"></span>
      </div>
    </div>
    <br>
    <div class="row">
      3. Result S3 Url
      <br>
      <div id="resultant_s3_url"> </div>

    </div>
  </body>
</html>

Thank you for reading this article.

AWS S3 Bucket action doesn't apply to any resources

I am trying to add a bucket policy to avoid bucket deletion & avoid deletion of objects in the bucket as well.

So I need to add a bucket policy to achieve my requirement mentioned above.

The following bucket policy giving error like
'Action does not apply to any resource(s) in statement'
{
  "Id": "Policy1527043264306",
  "Version": "2012-10-17",
  "Statement": [
    {
      "Sid": "Stmt1527043262106",
      "Action": [
        "s3:DeleteBucket",
        "s3:DeleteBucketPolicy",
        "s3:DeleteObject"
      ],
      "Effect": "Deny",
      "Resource": "arn:aws:s3:::prabhath-delete1",
      "Principal": {
        "AWS": [
          "XXXXXXXX"
        ]
      }
    }
  ]
}

We will discuss what caused the issue & how to resolve this.

Reason:

The following will apply on the bucket level only, so you need to define Resource as arn:aws:s3:::prabhath-delete-test
s3:DeleteBucket
s3:DeleteBucketPolicy

The following will apply on the bucket object level, so you need to define Resource as arn:aws:s3:::prabhath-delete-test/*
s3:DeleteObject

Solution:
You need to create two statements to cater the different types of actions as mentioned below:
One statement defines the following

• s3:DeleteBucket
• s3:DeleteBucketPolicy

Another statement defines the following

• s3:DeleteBucketPolicy

 Correct version of bucket policy looks like below:

{
    "Version": "2012-10-17",
    "Id": "Policy1526996283460",
    "Statement": [
        {
            "Sid": "Stmt1526996142070",
            "Effect": "Deny",
            "Principal": {
                "AWS": "arn:aws:iam::XXXXXXX:root"
            },
            "Action": [
                "s3:DeleteBucket",
                "s3:DeleteBucketPolicy"
            ],
            "Resource": "arn:aws:s3:::prabhath-delete-test"
        },
        {
            "Sid": "Stmt1526996279916",
            "Effect": "Deny",
            "Principal": {
                "AWS": "arn:aws:iam::XXXXXXXXX:root"
            },
            "Action": "s3:DeleteObject",
            "Resource": "arn:aws:s3:::prabhath-delete-test/*"
        }
    ]
}