Create

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19

DROP TABLE IF EXISTS client_iceberg_table

CREATE TABLE IF NOT EXISTS client_iceberg_table (
  check_time timestamp, 
  clientmac string, 
  firstseen timestamp, 
  lastseen timestamp, 
  power bigint, 
  numpkts bigint, 
  raw_bssid string, 
  raw_probedssids double, 
  fixed_bssid string, 
  fixed_probedssid string, 
  duration double, 
  check_time_date date)
LOCATION 's3://wifidumper-result-iceberg-dev/client_iceberg_table/'
TBLPROPERTIES (
  'table_type'='ICEBERG'
)

PARTITIONED BY (check_time_date, day(check_time_date))

GENERIC_USER_ERROR: Exceeded limit of 100 open partitions when writing data. Sorting data based on the table’s partition keys is recommended to produce more optimized tables and reduce the likelihood of such error. If the error persists, please contact AWS support for further assistance. If a data manifest file was generated at ‘s3://athena-query-results-lf/8b6714cf-e18e-49e7-bf39-38a08fedf7a6-manifest.csv’, you may need to manually clean the data from locations specified in the manifest. Athena will not delete data in your account.
This query ran against the “wifidumper_result_iceberg” database, unless qualified by the query. Please post the error message on our forum or contact customer support with Query Id: 8b6714cf-e18e-49e7-bf39-38a08fedf7a6

Load data

1
2
3
4
5
6
7

INSERT INTO wifidumper_result_iceberg.client_iceberg_table
SELECT * FROM wifidumper_result_db.client_parquet 

--compare
select count(*) from wifidumper_result_db.client_parquet  
select count(*) from wifidumper_result_iceberg.client_iceberg_table 

Query

1
2
3
4
5
6

select * from wifidumper_result_db.client_parquet
where clientmac= '00:F6:20:9C:B8:27' and check_time = timestamp '2022-02-27 09:20:00.000'

select * from wifidumper_result_iceberg.client_iceberg_table 
where check_time = timestamp '2022-02-27 09:20:00.000'

Drop column

1
2

ALTER TABLE wifidumper_result_iceberg.client_iceberg_table DROP COLUMN raw_bssid
ALTER TABLE wifidumper_result_iceberg.client_iceberg_table DROP COLUMN raw_probedssids

Add column

1

ALTER TABLE wifidumper_result_iceberg.client_iceberg_table ADD COLUMNS (client_name string)

Rename and reorder column

1
2
3

ALTER TABLE wifidumper_result_iceberg.client_iceberg_table
  CHANGE client_name clientname string 
  AFTER clientmac

Update data

1
2
3
4
5
6
7

UPDATE wifidumper_result_iceberg.client_iceberg_table 
SET clientname='Raspberry Pi'
WHERE clientmac='B8:27:EB:1E:47:14'

--verify
select * from wifidumper_result_iceberg.client_iceberg_table 
WHERE clientmac='B8:27:EB:1E:47:14'

Time travel query

1
2
3
4
5
6
7
8
9
10
11
12

-- by timestamp
SELECT * FROM wifidumper_result_iceberg.client_iceberg_table  FOR SYSTEM_TIME AS OF (current_timestamp - interval '3' minute)
WHERE clientmac='B8:27:EB:1E:47:14'

-- by system version (new version when data is updated)
-- 1. get versions
select * from wifidumper_result_iceberg."client_iceberg_table$iceberg_history"

-- 2. query
SELECT * FROM wifidumper_result_iceberg.client_iceberg_table FOR SYSTEM_VERSION AS OF <version_number>
WHERE clientmac='B8:27:EB:1E:47:14'

This is a demo solution that is using AWS Step Functions and ECS Anywhere to complete a simple data processing task by using cloud orchestration (Step Functions) and local computing resources (a NanoPi).

Data flow

1. User upload a file to a s3 bucket
2. S3 triggers step functions via cloudtrail and event bridge
3. Event bridge triggers a step function state machine
4. State machine triggers a ECS Anywhere task to download the file from s3 to local (to do some processing), if file name matches condition

Architecture

NanoPi that runs ECS Anywhere

NanoPi Neo2 with LED hat in my home office, running AWS ECS Anywhere.

Read More

1. Benefits of using for connecting EC2 instances

AWS Systems Manager (SSM) is an AWS service that you can use to view and control your infrastructure on AWS. It can securely connect to a managed node. The SSM Agent is installed in EC2 OS. It is pre-installed on many amazon Machine Images (AMIs).

With SSM:

• No need to open SSH port in security group for EC2
• No need to create and manage SSH keys

And SSM works regardless if the EC2 instance is in public or private (NAT or Endpoint) subnet.

Requirements for SSM working:

• AWS instances:
  • SSM agent installed in instance (pre-installed in many AMIs already)
  • Connectivity to the AWS public zone endpoint of SSM (IGW, NAT or VPCE)
  • IAM role providing permissions
• On-Prem instances:
  • SSM agent installed in instance
  • Connectivity to the AWS public zone endpoint of SSM (Access to public internet)
  • Activation (Activation Code and Actuation ID)
  • IAM role providing permissions

2. EC2 Instance in public subnet

• 2.1. Make sure the EC2 instance has a public IP. It could be the public IP assigned during creation, or an Elastic IP.
• 2.2. EC2 instance should have Internet access (for calling SSM endpoint). In public subnet it is done via Internet Gateways. See details from Session Manager prerequisites, in “Connectivity to endpoints” section.
• 2.3. You can use VPC Reachability Analyzer to troubleshoot the connectivity between your EC2 and Internet gateway.
• 2.4. Create an EC2 Instance profile has IAM policy AmazonSSMManagedInstanceCore. Read the details from Step 4: Create an IAM instance profile for Systems Manager
• 2.5 Attach the EC2 Instance profile to your instance.
• 2.6 Reboot the EC2 instances.

3. EC2 instance in private subnet, with NAT connectivity

In this case, EC2 instances have no public IP, but they can still talk to internet via NAT.

• 3.1. Make sure EC2 instances in private subnet can access internet, via a NAT Gateway or NAT instance.
• 3.2. The rest will be the same as EC2 instances in public subnet, starting from 2.2

4. EC2 instance in private subnet, without NAT connectivity but VPC endpoints

In this case, the EC2 instance (no public IP) won´t have access internet via NAT but VPC endpoints, some extra works are required

• 4.1 Create VPC endpoints for System Manager. Remember to allow HTTPS (port 443) outbound traffic in security group for your endpoint (ssm, ssmmessages and ec2messages)
• 4.2. Create an IAM Role as EC2 profile that contains at least the following 2 policies
  • aws managed policy AmazonSSMManagedInstanceCore
  • a custom policy for accessing an AWS owned S3 buckets.
• 4.3 Attach this instance profile to your EC2 instance
• 4.4 Make sure enable “DNS resolution” and “DNS hostnames” for you VPC
• 4.5 In addition, if your EC2 instance need to access other AWS services such as S3, remember to create needed endpoints for them as well. (For S3 you can choose either Gateway or Endpoint. At this moment Gateway is free.) Note that you need to add the endpoint into the private subnet route table. The following screenshot shows the route table entity of a S3 Gateway endpoint, which is using prefix lists.

5. Verification

Once the SSM is fully up-and-running, the EC2 instance (either in public/private subnet) will appear in Fleet Manager in SSM web console.

Inspired by Bryan Boyer and Tom Whitwell, I am building a Very Slow Movie Player (VSMP).

With VSMP,

• Kiki’s Delivery Service (running time 1h42m): takes 7 days to play (with 1 frame per 20 seconds, as in above demo)
• Laputa: Castle in the Sky (running time 2h4m): takes 2 months to play (with 1 frame per 120 seconds, as default setting)

Read More

This is a happy path demo of setting up Okta as the Idp for AWS Control Tower (via AWS SSO).
Goal: To utilize users and groups in Okta to manage AWS control tower.

1. Create a brand new Control Tower instance

In this demo, we create the AWS Control Tower instance in a brand new AWS account. During this process, control tower creates several services/components, such as AWS Organizations, AWS SSO, default organizations unit (OU) “Security” and 2 AWS accounts “Log Archive” and “Audit”.

In the AWS SSO, some default SSO user groups are created for managing Control Tower:

The default admin user for organization management account is “AWS Control Tower Admin”.

Detailed user info

And it belongs to 2 groups: AWSAccountFactory and AWSControlTowerAdmins

Read More

In Part 1, we have talked about the hardware/software running on the edge (the car) for collecting data.

Now we have the data, and how to gain some insights by doing data analytics? I have been using the following products, and would like to share my quick thoughts

• Azure Time Series Insight (TSI)
• Azure Databricks
• Azure Data Explorer (ADX)
• PowerBI
• Grafana

Read More

Previously I wrote a blog about how to measure hamster via IoT wheel. This reminds me another personal project I did back to the winter of 2018/2019, for measuring car performance.

Read More

We recently welcomed our new family member Qiuqiu (球球) (a girl Syrian/Golden hamster) home. She seems to enjoy the new environment fairly well, but she is a quiet girl - does not show much activities during the day time.

Of course we understand hamsters are nocturnal animals, which means they are sleeping in day time and become more active at night. But I started wondering how she was doing during the nights, especially how much she ran on the hamster wheel.

Let’s do something about it.

Picture: Qiuqiu with her wheel

Read More