Shantanu's Blog

I have DynamoDB table called "autocorrect" and I need to stop all traffic to/ from this table. I can use resouce-based policy like this...

{
  "Version": "2012-10-17",
  "Id": "PolicyId",
  "Statement": [
    {
      "Sid": "AccessDisabledTemporary",
      "Effect": "Deny",
      "Principal": "*",
      "Action": "dynamodb:*",
      "Resource": "arn:aws:dynamodb:us-east-1:XXXX85053566:table/autocorrect"
    }
  ]
}

Labels: aws

1) add access key and secret key of a read-only user

aws configure

2) I need to install amazon Q using the instructions found on this page...

https://docs.aws.amazon.com/amazonq/latest/qdeveloper-ug/command-line-installing-ssh-setup-autocomplete.html

3) I can now start the q service using the command...

./q/bin/q

Use natural language instructions like "list all dynamoDB tables" or "list all S3 buckets"

4) For advance users, I can create MCP server and save database credentials like username and password so that Q can query database and return results.

https://awslabs.github.io/mcp/servers/dynamodb-mcp-server/

Labels: aws, usability

Let's assume I uploaded a file to S3:

aws s3 cp dictionaries.xcu s3://cf-templates-us-east-1/

I need to update that file only if it does not already exist. In that case I use --if-none-match parameter as shown below:

aws s3api put-object --bucket cf-templates-us-east-1 --key dictionaries.xcu --body dictionaries.xcu --if-none-match "*"   

Returns "An error occurred (PreconditionFailed)"

This will help me while trying to upload a large file again.

_____

Following features are available for S3 Express One Zone:

1) In directory buckets, clients can perform conditional delete checks on an object’s last modified time, size, and Etag using the x-amz-if-match-last-modified-time, x-amz-if-match-size, and HTTP if-match headers. 

2) Append data to a file:

aws s3api put-object --bucket cf-templates-us-east-1 --key dictionaries.xcu --body dictionaries.xcu --write-offset-bytes file001.bin

Or use python:

s3.put_object(Bucket='amzn-s3-demo-bucket--use2-az2--x-s3', Key='2024-11-05-sdk-test', Body=b'123456789', WriteOffsetBytes=9)

It can not replace your database or Messaging Queues because only a few thousand updates are possible for each object.

3) You can configure S3 Lifecycle rules for S3 Express One Zone to expire objects on your behalf. For example, you can create an S3 Lifecycle rule that expires all objects smaller than 512 KB after 3 days and another rule that expires all objects in a prefix after 10 days.

Labels: aws

Kasm VNC is a modern open source VNC server.

Quickly connect to your Linux server's desktop from any web browser.
No client software install required.

1) Firefox using VNC

docker run -d \
--name=firefox \
-e PUID=1000 \
-e PGID=1000 \
-e TZ=Etc/UTC \
-p 3000:3000 \
-p 3001:3001 \
-v /path/to/config2:/config \
--shm-size="1gb" \
--restart unless-stopped \
lscr.io/linuxserver/firefox:latest

2) Libreoffice using VNC

docker run -d \
  --name=libreoffice \
  --security-opt seccomp=unconfined `#optional` \
  -e PUID=1000 \
  -e PGID=1000 \
  -e TZ=Etc/UTC \
  -p 3000:3000 \
  -p 3001:3001 \
  -v /path/to/config:/config \
  --restart unless-stopped \
  lscr.io/linuxserver/libreoffice:latest

Labels: aws, docker, firefox, libreoffice, linux tips, usability

I can disable all access to a dynamoDB table using resource based policy. Here is an example:

{
  "Version": "2012-10-17",
  "Statement": [
    {
      "Effect": "Deny",
      "Principal": {
        "AWS": "*"
      },
      "Action": "dynamodb:*",
      "Resource": "arn:aws:dynamodb:us-east-1:XXX885053566:table/sandhiDupe"
    }
  ]
}

Labels: aws, aws_lambda

Let's assume we need to optimize this query. The data is stored in S3 (around a few TB)

SELECT * FROM "bucketing_blog"."noaa_remote_original"
WHERE
    report_type = 'CRN05'
    AND ( station = '99999904237'
        OR station = '99999953132'
        OR station = '99999903061'
        OR station = '99999963856'
        OR station = '99999994644'
    );

There are around 14325 unique stations and report_type column contain around 13 types.

1) The first thing that can be tried is to create a table with partitioning (using report_type)

CREATE TABLE "bucketing_blog"."athena_non_bucketed"
WITH (
    external_location = 's3://<your-s3-location>/athena-non-bucketed/',
    partitioned_by = ARRAY['report_type'],
    format = 'PARQUET',
    write_compression = 'SNAPPY'
)
AS
SELECT * FROM "bucketing_blog"."noaa_remote_original";

If you need faster and cheaper query, use buckets

CREATE TABLE "bucketing_blog"."athena_bucketed"
WITH (
    external_location = 's3://<your-s3-location>/athena-bucketed/',
    partitioned_by = ARRAY['report_type'],
    bucketed_by = ARRAY['station'],
    bucket_count = 16,
    format = 'PARQUET',
    write_compression = 'SNAPPY'
)
AS
SELECT * FROM "bucketing_blog"."noaa_remote_original"

I created 16 buckets because that is the maximum number of stations that may appear in the SQL query.

https://aws.amazon.com/blogs/big-data/optimize-data-layout-by-bucketing-with-amazon-athena-and-aws-glue-to-accelerate-downstream-queries/

Labels: athena, aws

It is highly recommended to enable versioning for all your S3 buckets. In case you delete a file by mistake, you can find the version ID of the older version and donload that copy.

1) Find the version ID:
aws s3api list-object-versions --bucket cdk-hnb  --prefix tt/archive.tar

2) Donload that version:
aws s3api get-object --bucket cdk-hnb --key tt/archive.tar archive.tar --version-id mqfya

3) List all versions:
aws s3api list-object-versions --bucket cdk-hnb

If versioning is enabled, you can not remove the bucket easily. You first need to "empty" the bucket and then delete it.

Labels: aws

This code will check if there is any port open and send an alert to the subscribers of SNS topic.

import boto3, json
ec2 = boto3.client('ec2' , region_name='us-east-1')
for security_group in ec2.describe_security_groups()['SecurityGroups']:
  for i in range(len(security_group['IpPermissions'][0]['IpRanges'])):
    for k,v in security_group['IpPermissions'][0]['IpRanges'][i].items():
        print (k, v)
        if '0.0.0.0' in v:
          message = {"alert": "open port found "}
          sns_client = boto3.client("sns", region_name="us-east-1")
          response = sns_client.publish(TargetArn='arn:aws:sns:us-east-1:102378362623:NotifyMe',
                                                                   Message=json.dumps({'default': json.dumps(message)}), MessageStructure='json')      

You may need to change the region name and SNS topic ARN address in the code mentioned above.
This code can be written as Lambda function and run every day.

Labels: aws, aws_lambda, boto

If you quickly ant to test data using athena query, use with syntax as shown below:

WITH countries(country_code) AS (VALUES 'pol', 'CAN', 'USA')
SELECT upper(country_code) AS country_code
    FROM countries
 
In this example aggregate expression uses "OVER" function.
 
WITH students_results(student_id, result) AS (VALUES
    ('student_1', 17),
    ('student_2', 16),
    ('student_3', 18),
    ('student_4', 18),
    ('student_5', 10),
    ('student_6', 20),
    ('student_7', 16))
SELECT
    student_id,
    result,
    count(*) OVER (
        ORDER BY result
 ) AS close_better_scores_count
FROM students_results

Labels: athena, aws

Athena supports unicode characters very well. For e.g. if the datafile looks like this...

"Root_word";"Word";"Primary";"Type";"Code";"Position";"Rule"
"अँटिबायोटिक","अँटिबायोटिक","अँटिबायोटिक","Primary","","",""
"अँटिबायोटिक","अँटिबायोटिकअंती","अँटिबायोटिक","Suffix","A","7293","001: 0 अंती ."
"अँटिबायोटिक","अँटिबायोटिकअर्थी","अँटिबायोटिक","Suffix","A","7293","002: 0 अर्थी ."
"अँटिबायोटिक","अँटिबायोटिकआतून","अँटिबायोटिक","Suffix","A","7293","003: 0 आतून ."
"अँटिबायोटिक","अँटिबायोटिकआतूनचा","अँटिबायोटिक","Suffix","A","7293","004: 0 आतूनचा ."
"अँटिबायोटिक","अँटिबायोटिकआतूनची","अँटिबायोटिक","Suffix","A","7293","005: 0 आतूनची ."
"अँटिबायोटिक","अँटिबायोटिकआतूनचे","अँटिबायोटिक","Suffix","A","7293","006: 0 आतूनचे ."
"अँटिबायोटिक","अँटिबायोटिकआतूनच्या","अँटिबायोटिक","Suffix","A","7293","007: 0 आतूनच्या ."
"अँटिबायोटिक","अँटिबायोटिकआतूनला","अँटिबायोटिक","Suffix","A","7293","008: 0 आतूनला ."

This create table statement is all I need...

create external table myptg (
root_word varchar(255),
derived_word varchar(255),
stemmed_word varchar(255),
type varchar(255),
code varchar(255),
position varchar(255),
rule varchar(255)
)
ROW FORMAT SERDE 'org.apache.hadoop.hive.serde2.OpenCSVSerde'
WITH SERDEPROPERTIES (
  'separatorChar' = '\;',
  'quoteChar' = '\"',
  'escapeChar' = '\\'
)
LOCATION 's3://ptg1/mc/'
TBLPROPERTIES ("skip.header.line.count"="1");

I can create a supporting table like this...

create external table gamabhana (derived_word varchar(255))
LOCATION 's3://ptg1/mc2/'
TBLPROPERTIES ("skip.header.line.count"="1");

A new table can be created using the syntax something like this...
 
create external table anoop (
serial_number int,
root_word varchar(255),
stem1_word varchar(255),
stem2_word varchar(255),
stem3_word varchar(255),
stem4_word varchar(255),
stem5_word varchar(255),
stem6_word varchar(255),
stem7_word varchar(255)
)
ROW FORMAT SERDE 'org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe'
WITH SERDEPROPERTIES (
  'field.delim' = ',',
  'escape.delim' = '\\',
  'line.delim' = '\n'
)
LOCATION 's3://ptg1/mc3/'
TBLPROPERTIES ("skip.header.line.count"="1");

And then run a join statement like this...

create table gamabhana_match as
select a.derived_word, b.root_word, b.stemmed_word, b.type, b.code, b.position, b.rule, c.stem1_word, c.stem2_word, c.stem3_word, c.stem4_word, c.stem5_word, c.stem6_word, c.stem7_word
from gamabhana as a left join myptg as b
on b.derived_word = a.derived_word
left join anoop as c
on c.derived_word = a.derived_word

It will scan around 2 GB data (in this case) and the cost will be around 1 cent per query. This can also be done in MySQL. But importing data and building indexes is not easy. Unlike Athena, MySQL allows unlimited queries for free!

Athena is good for data that is important and accessed rarely.

Labels: athena, aws, usability

CommonCrawl builds and maintains an open repository of web crawl data that can be accessed and analyzed by anyone.

https://commoncrawl.org/

Here are 3 easy steps to download the data in any language, for e.g. Marathi or Gujarati

1) Clone the repo git clone https://github.com/qburst/common-crawl-malayalam.git

2) Change language code from mal to mar at the line -
AND content_languages LIKE 'mal%'" 

The other codes are: hin (Hindi), mal (Malayalam), mar (Marathi), guj (Gujarati)

Run this shell script after replacing XXX with your AWS access and security key.

./extract_malayalam_warcs.sh XXX XXX 2021-10 s3://nlp-malayalam

3) Change unicode range from mal: range(3328, 3456) to your own desired language range, for e.g. devnagari: range(2304, 2432) Gujarati: range(2688, 2816)

Run this shell script after replacing XXX with your AWS access and security key.

./filter_malayalam.sh XXX XXX s3://nlp-malayalam/2021-10/warcs s3://nlp-malayalam/2021-10/output2

Filtered and unfiltered data will be available in output2 sub-folder of s3 bucket.

_____

Update:

The good French guys from Sorbonne University and inria have extracted sentences from common crawler for all languages and made them available at 

https://oscar-corpus.com/

I have no words to thank them. The paper is available here... 

http://corpora.ids-mannheim.de/CMLC7-final/CMLC-7_2019-Oritz_et_al.pdf

Without this data, it would have been very difficult to extract reliable data for languages like Marathi, Gujarati.

Labels: athena, aws, python, shell script

Let's assume I have a list of client_codes saved in a redshift table and I need to find the details from an API.

# select client_code from some_table limit 10;
  client_code   |
--------------+
 1001 |  
 2002 |  
 9009 |  
 1009 |  
 1898 |  
 5465 |  
 3244 |  
 5576 |  
 4389 |  
 8756 |  
(10 rows)

I need to get the client addresses from a website. For e.g. the first client code is 1001 and address should come from
http://some_site.com/Details?dest=1001

This can not be done at SQL query level. You need to loop through an array using Python, PHP, Java etc. You can also write your scripts in AWS Lambda and use them as UDF (User Defined Functions) in Redshift. For e.g.

# select client_code, client_details(client_code) as c_address from some_table limit 10;
  client_code   |                  c_address
--------------+---------------------------------------------
 1001 | 21,Tamilnadu,
 2002 | 14,Madhya Pradesh & Chattisgarh,
 9009 | 7,Gujarat,
 1009 | 23,Uttar Pradesh (W) & Uttarakhand
 1898 | 11,Karnataka
 5465 | 3,Bihar & Jharkhand
 3244 | 11,Karnataka
 5576 | 6,Delhi
 4389 | 13,Kolkata
 8756 | 11,Karnataka
(10 rows)

The code of "client_details" Lambda function will look something like this...

import json
import requests
myurl = 'http://some_site.com/Details?dest='

def lambda_handler(event, context):
  ret = dict()
  res = list()
  for argument in event['arguments']:
      try:
        number = str(argument[0])
        page = requests.get(myurl+number[-10:])
        res.append((page.content).decode('utf-8'))
        ret['success'] = True
      except Exception as e:
        res.append(None)
        ret['success'] = False
        ret['error_msg'] = str(e)
      ret['results'] = res
  return json.dumps(ret)

Notes:
1) We are using "requests" module in this code. Since it is not available in AWS Lambda environment, I have added it using this layer...
# Layer: arn:aws:lambda:us-east-1:770693421928:layer:Klayers-python38-requests:9

2) You will also need to increase the timeout of Lambda upto 15 minutes. The API may take more than 3 seconds (default) to respond.

3) You will also have to update the IAM role associated with your Redshift cluster. (Actions - Manage Role) You can add the policy called "AWSLambdaFullAccess" or grant access to a single function as explained in the documentation.

Labels: aws, aws_lambda, redshift

To reduce the data scan cost, AWS Athena provides an option to bucket your data. This optimization technique can perform wonders on reducing cost.

Labels: athena, aws

# create a boto session

import boto3
my_session = boto3.Session(
    aws_access_key_id="xxx",
    aws_secret_access_key="xxx",
    region_name="us-east-1",
)

# use wrangler to upload parquet files to S3
# use the database created in glue

import awswrangler as wr
wr.s3.to_parquet(
    df=df,
    path="s3://test1623/noaa/",
    dataset=True,
    database="awswrangler_test",
    table="noaa",
    partition_cols=["year"],
    boto3_session=my_session,
)

# Read Athena query into pandas dataframe
df = wr.athena.read_sql_query(
    "select * from noaa limit 10", database="awswrangler_test", boto3_session=my_session
)

https://aws.amazon.com/blogs/big-data/optimize-python-etl-by-extending-pandas-with-aws-data-wrangler/

Labels: athena, aws

Labels: athena, aws

Labels: aws, aws_cloudformation, boto, usability

Once the resources are created, you can see a URL in output section something like this...

https://ie5n05bqo0.execute-api.us-east-1.amazonaws.com/mycall

Now building the javaScript bookmarklet is easy.

javascript:(function(){location.href='https://ie5n05bqo0.execute-api.us-east-1.amazonaws.com/mycall?email=shantanu.oak@gmail.com&title=emailThis&url='+encodeURIComponent(location.href);})();

Right click on any bookmark and then copy-paste the above link. Make sure that you have changed the URL and email address to your own. Now click on this bookmarklet while you are on an important web page that you need to send to your inbox. Enjoy!

Labels: api_gateway, aws, aws_lambda, usability

Labels: aws, elastic

Labels: aws, elastic

Labels: athena, aws