Home | Archives |
|
Create1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19DROP 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 data1
2
3
4
5
6
7INSERT 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
Query1
2
3
4
5
6select * 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 column1
2ALTER TABLE wifidumper_result_iceberg.client_iceberg_table DROP COLUMN raw_bssid
ALTER TABLE wifidumper_result_iceberg.client_iceberg_table DROP COLUMN raw_probedssids
Add column1
ALTER TABLE wifidumper_result_iceberg.client_iceberg_table ADD COLUMNS (client_name string)
Rename and reorder column1
2
3ALTER TABLE wifidumper_result_iceberg.client_iceberg_table
CHANGE client_name clientname string
AFTER clientmac
Update data1
2
3
4
5
6
7UPDATE 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 query1
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).
NanoPi Neo2 with LED hat in my home office, running AWS ECS Anywhere.
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:
And SSM works regardless if the EC2 instance is in public or private (NAT or Endpoint) subnet.
Requirements for SSM working:
In this case, EC2 instances have no public IP, but they can still talk to internet via NAT.
In this case, the EC2 instance (no public IP) won´t have access internet via NAT but VPC endpoints, some extra works are required
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,
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.
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
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
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.
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