AWS

CloudFormation

• dynamodb 一次添加多個新表 >> ok
• dynamodb 一次添加多個舊表的索引 >> 不行，得一個個索引分次建立
• template 與實際資源不一致時，例如建表但表已存在 >> 需要從 AWS Console 匯入完整 template

# deploy
aws --profile dev cloudformation deploy \
  --template res.yml \
  --stack-name myproject-dev \
  --s3-bucket res-deploymentbucket # optional if template file is larger than 50k

• template example

---
AWSTemplateFormatVersion: "2010-09-09"
Description: Goldwallet Resource Definations
Parameters:
  Stage:
    Type: String
    AllowedValues:
      - prd
      - uat
      - dev
Resources:
  usersTable:
    Type: AWS::DynamoDB::Table
    DeletionPolicy: Retain
    Properties:
      AttributeDefinitions:
      - AttributeName: id
        AttributeType: S
      - AttributeName: create_date
        AttributeType: S
      KeySchema:
      - AttributeName: id
        KeyType: HASH
      - AttributeName: create_date
        KeyType: RANGE
      BillingMode: PAY_PER_REQUEST
      StreamSpecification:
        StreamViewType: NEW_IMAGE
      TableName: !Sub 'users-${Stage}'
      GlobalSecondaryIndexes:
      - IndexName: users-create_date-GSI
        KeySchema:
        - AttributeName: create_date
          KeyType: HASH
        - AttributeName: id
          KeyType: RANGE
        Projection:
          ProjectionType: ALL

CloudWatch Logs Insights

• count

stats count(*) by bin(min)

stats count(*) as cnt by fsym, tsym, exchange
| sort cnt desc

• filter

stats count(*) by bin(min)
| filter fsym="BNB"

stats count(tag ispresent), count(*) by bin(1h)

CloudWatch Logs

• filter syntax

{ $.status = 400 }
{ $.status = 4* }
{ $.request = "GET /myproject1/v1/storage/presignLogUrl*" }
{ $.request = "* /myproject1/*" }
{ ($.request = "* /myproject1/*") && ($.status = "4*") }

AWSLogs for docker

• Pass Credentials to the awslogs Docker Logging Driver on Ubuntu

Athena

• Settings

  • Query result location: s3://your-bucket-name/athena/query-results-bucket/

• Create Table

  • from S3 bucket data

• partition

• 可節省掃描的資料容量（省錢）

• s3 路徑和資料來源都必須要有 partition 的欄位

• reload: MSCK REPAIR TABLE table_name

CREATE EXTERNAL TABLE IF NOT EXISTS my_api_log (
  `datetime` timestamp,
  `name` string,
  `mcc` string,
  `locale` string,
  `client_version` string,
  `androidid` string,
  `region` string
) PARTITIONED BY (
  dt string
)
ROW FORMAT SERDE 'org.openx.data.jsonserde.JsonSerDe'
WITH SERDEPROPERTIES (
  'serialization.format' = '1'
) LOCATION 's3://my_api_logs/daily/'
TBLPROPERTIES ('has_encrypted_data'='false');

• struct data type

CREATE EXTERNAL TABLE IF NOT EXISTS default.structdata (
  name string, androidid int, mcc int, log_details struct<uid:string,invite_code:string,register_time:int,trial_quota:int,trial_time:int,gained_quota:int,gained_time:int,invited_user:int,invited_user_gained:int,last_signin_date:string,signin_days:int,signin_points:array<int>,longest_signin_days:int>
)
ROW FORMAT SERDE 'org.openx.data.jsonserde.JsonSerDe'
WITH SERDEPROPERTIES (
'serialization.format' = '1'
) LOCATION 's3://paramaplab1/json/'

SELECT * FROM structdata WHERE log_details.trial_time=20;

DynamoDB

• UnmarshalMap - (golang) 如果單一屬性型態不對，可能導致後面其他屬性就不轉了！
• 沒有 batch update。BatchWriteItem 只能 Put、Delete，且批次內的 key 不能重複
• Secondary index 非 unique

Local DynamoDB

docker pull amazon/dynamodb-local

• 執行時需要用 -shareDB，這樣才能不分 region 共用

docker run -p 8000:8000 amazon/dynamodb-local -jar DynamoDBLocal.jar -inMemory -sharedDb

• 3rd party GUI

npm install dynamodb-admin -g
export DYNAMO_ENDPOINT=http://localhost:8000
dynamodb-admin

• NodeJS

AWS.config.update({
    endpoint: 'http://localhost:8000',
});

Redshift

• COPY FROM S3
• target table 欄位可以比較多，給 FILLRECORD 參數可填入預設值
• source 欄位比較多則會出錯，只能先存到暫存表再複製到目的表

COPY public.test1(datetime,host,mcc,uuid,cnl,dt)
  FROM 's3://my-bucket/my/path/app.log'
  CREDENTIALS 'aws_access_key_id=A...Q;aws_secret_access_key=0...N'
  FILLRECORD TRUNCATECOLUMNS IGNOREBLANKLINES MAXERROR 100;

• UNLOAD TO S3

UNLOAD ('SELECT DISTINCT uuid FROM test1 WHERE uuid<>\\'\\' AND (mcc=\\'310\\' OR mcc=\\'311\\')')
  TO 's3://my-bucket/my/path/uuid.log'
  CREDENTIALS 'aws_access_key_id=A...Q;aws_secret_access_key=0...N'
  GZIP NULL '' ALLOWOVERWRITE;

• COPY FROM DynamoDB

COPY public.users
  FROM 'dynamodb://Users'
  CREDENTIALS 'aws_access_key_id=A...Q;aws_secret_access_key=0...N'
  readratio 90 MAXERROR 100;