• ChatGPT 来自 OpenAI 研究实验室，由 GPT-3.5 系列模型提供支持，包括 3.5 之前的模型版本，都使用 Azure AI 超级计算基础结构上的文本和代码数据进行训练。

• GPT-3.5 系列模型最重要的变化，是建立在人类真实反馈基础上的调校。这是一种新使用的 AI 训练方法，标记者会在模型中书写期待的回复，按照期待的回复为标记的答案排序，通过排序来奖励模型。在持续迭代的过程中，输入奖励模型，得到优化参数。

• 训练步骤
  

20210820 Update

20221205 Update

To-Do

• AZ-104
• AZ-204
• AZ-500
• AZ-305

References:

• https://learn.acloud.guru/series/certification-success-hub/view/azure-certification-guide
• https://learn.acloud.guru/series/certification-success-hub/view/azure-certification-guide-2021-eoy-update

The Azure AD Identity Platform

Managing Azure AD Tenant and Azure Subscription Associations

Azure AD Identities

Azure AD Groups

Azure AD Dynamic Groups

Azure AD Administrative Units

Hybrid Identity

Azure AD Connect and Hybrid Identities

Azure AD External Identities

User Flows with External Identities

Controlling Access

Securing Azure and Azure Active Directory (AD)

Azure Role-Based Access Control (RBAC)

Azure AD Roles

Custom Roles

Securing Identities and Access

Azure AD Privileged Identity Management (PIM)

Access Reviews

Azure AD Identity Protection

Conditional Access

Azure AD Passwordless Authentication

Securing Virtual Networks

Virtual Network Routing

Network S

sudo apt-get -y install openvpn

sudo touch /etc/openvpn/credentials
sudo printf '%s\n' 'username' 'password' > /etc/openvpn/credentials

sudo sed -i 's/auth-user-pass/auth-user-pass \/etc\/openvpn\/credentials/g' /etc/openvpn/US-East.ovpn

sudo openvpn --config /etc/openvpn/US-East.ovpn

Reference:
How to Setup OpenVPN Command Line on Linux (Ubuntu)

Best Practices

Import

1. Turn on Query Caching for imported tables​
2. Turn on Large Dataset Storage format​

DirectQuery

1. Use star-schema for data model​and ensure that your dimension tables contain proper keys and that those keys relate to a fact table​
2. Materialize all aggregations, transformations, and calculations in SQL Server​
3. Pre-filter large fact tables in SQL Server​and remove filters in visual
   
   • Adding filters will cause Power BI to generate multiple SQL queries
4. Use dynamic M query parameter to move measure logic from Power BI to SQL Server​
5. Optimize measure definition to generate efficient DAX queries​
   
   • Power BI will translate complex DAX into multiple SQL Server queries
6. Use dual mode for dim tables​
   
   • Otherwise Power BI will use import dim tables to generate long SQL Queries for filters
7. Refine table structure and indexing strategy​
   
   • Clustered rowstore (like by TimeId) + non-clustered columnstore is recommended
8. Only

Compute

Virtual Machine

Azure Functions

App Services

Azure Container Instances (ACI)

Azure Kubernetes Service (AKS)

Azure Container Registry (ACR)

Windows Virtual Desktop

Networking

Virtual Network (VNet)

Network Security Group (NSG)

Application Security Group

Load Balancer

VPN Gateway

Application Gateway

ExpressRoute

Paired Region

Storage

Storage Account

Blob

Disk

File

Archive

Database

Cosmos DB

Azure SQL

Azure Database for PostgreSQL

Database Migration Services

Identity and Access Management (IAM)

Azure Active Directory (AAD)

Group

Role

Scope

Role-Based Access Control (RBAC)

Azure AD Join

Azure AD Connect

Single Sign-On

Self-Service Password Reset (SSPR)

I

S3

• Maximum object size is 5TB; largest object in a single PUT is 5GB.
• Recommended to use multi-part uploads if larger than 100MB.
• Security: IAM policies -> Bucket policy -> Object ACL
• 
• Versioning cannot be enabled at the object level. It's a bucket-level feature.

Amazon Neptune

Amazon Redshift

Amazon Athena

Amazon Quantum Ledger Database

Amazon Managed Blockchain

Amazon Timestream Database

Amazon DocumentDB

Amazon ElasticSearch

Databases

S3 Select vs Athena vs Redshift Spectrum

S3 Select is focused on retrieving data from S3 using SQL:

S3 Select, enables applications to retrieve only a subset of data from an object by using simple SQL expressions. By using S3 Select to retrieve only the data needed by your application, you can achieve drastic performance increases – in many cases you can get as much as a 400% improvement compared with classic S3

Modify column type

ALTER TABLE ZAWTH_Raw_Core_Branch.raw.POS_TRANSACTION ALTER COLUMN [transaction_line_id] VARCHAR(128) NULL;

Check currently running queries

reference: https://stackoverflow.com/a/29400789

SELECT   SPID       = er.session_id
    ,STATUS         = ses.STATUS
    ,[Login]        = ses.login_name
    ,Host           = ses.host_name
    ,BlkBy          = er.blocking_session_id
    ,DBName         = DB_Name(er.database_id)
    ,CommandType    = er.command
    ,ObjectName     = OBJECT_NAME(st.objectid)
    ,CPUTime        = er.cpu_time
    ,StartTime      = er.start_time
    ,TimeElapsed    = CAST(GETDATE() - er.start_time AS TIME)
    ,SQLStatement   = st.text
FROM    sys.dm_exec_requests er
    OUTER APPLY sys.dm_exec_sql_text(er.sql_handle) st
    LEFT JOIN sys.dm_exec_sessions ses
    ON ses.session_id = er.session_id
LEFT JOIN sys.dm_exec_connections con
    ON con.session_id = ses.session_id
WHERE   st.text IS NOT NULL

1. Create EFS on AWS web portal

2. Edit the security group of EFS to allow access from EC2 instances
   

3. Mount EFS on EC2

   sudo mkdir efs

   

   sudo chmod 777 /efs

4. Install amazon-efs-utils for auto-remount

   git clone https://github.com/aws/efs-utils
   cd efs-utils/
   ./build-deb.sh
   sudo apt-get -y install ./build/amazon-efs-utils*deb

5. Configure IAM role in EC2 (already done)

6. Edit /etc/fstab

   fs-xxxxxxxx:/ /efs efs _netdev,tls,iam 0 0

7. Test mount

   sudo mount -fav

8. Add Linux user in the other EC2's group to avoid readonly issue

   sudo usermod -a -G ubuntu guangningyu

   sudo usermod -a -G guangningyu ubuntu

Reference:
1. Mount the Amazon EFS File System on the EC2 Instance and Test
2. Mounting your Amazon EFS file system automatically
3. User and Group ID Permissions for Files and Directories Within a File System

test.csv

key,a,b,c
a,1,,-1
a,2,,
a,3,,4

test.py

from pyspark.sql import SparkSession
from pyspark.sql import functions as F
spark = SparkSession \
    .builder \
    .appName("spark-app") \
    .getOrCreate()
spark.sparkContext.setLogLevel("WARN")
df = spark.read.csv("test.csv", header=True)
res = df.groupBy(["key"]).agg(*[
    F.max("a"),
    F.max("b"),
    F.max("c"),
    F.min("a"),
    F.min("b"),
    F.min("c"),
])
print (res.toPandas())

spark-submit test.py

  key max(a) max(b) max(c) min(a) min(b) min(c)
0   a      3   None      4      1   None     -1

brew update && brew install azure-cli
az login

brew tap azure/functions
brew install azure-functions-core-tools@2

References:
Install Azure CLI on macOS
Azure/azure-functions-core-tools

1. Run [Server Manager] and Open [Tools] - [Computer Management].
2. Right-Click [Users] under the [Local Users and Groups] on the left pane and select [New User].
3. Input UserName and Password for a new user and click [Create] button. Other intems are optional to set.
4. After creating normally, New user is shown on the list like follows.
5. If you'd like to set administrative priviledge to the new user, Right-click the user and open [Properties].
6. Move to [Member of] tab and click [Add] button.
7. Specify [Administrators] group like follows.
8. Make sure [Administrators] group is added on the list and click [OK] button to finish settings.

Reference: Windows Server 2016 : Initial Settings : Add Local Users

Compute

EC2

• Billing for interrupted Spot Instance
  
  
• When you launch an instance from AMI, it uses either paravirtual (PV) or hardware virtual machine (HVM) virtualization. HVM virtualization uses hardware-assist technology provided by the AWS platform.
• The information about the instance can be retrieved from:
  
  • http://169.254.169.254/latest/meta-data/
  • http://169.254.169.254/latest/user-data/
• The underlying Hypervisor for EC2:
  
  • Xen
  • Nitro
• Standard Reserved Instances cannot be moved between regions. You can choose if a Reserved Instance applies to either a specific AZ or an entire region, but you cannot change the region.
• About EC2 Auto Scaling
  
  • Can span multi-AZ
• About Placement Group
  
  • Three types of Placement Groups
    
    • Clustered Placement Group
      
      • Within a single AZ
      • Used for applications that need low network latency, high network throughput, or both
      • Only certain instances can be launched into a Clustered Placement Group
      • AWS r

1. Install dependencies

   sudo apt-get update
   sudo apt-get install automake autotools-dev fuse g++ git libcurl4-gnutls-dev libfuse-dev libssl-dev libxml2-dev make pkg-config

2. Install s3fs

   git clone https://github.com/s3fs-fuse/s3fs-fuse.git
   cd s3fs-fuse
   ./autogen.sh
   ./configure --prefix=/usr --with-openssl
   make
   sudo make install
   which s3fs

3. Config credentials

   echo "Your_accesskey:Your_secretkey" >> /etc/passwd-s3fs
   sudo chmod 640 /etc/passwd-s3fs

4. Create mounting point

   mkdir /mys3bucket
   s3fs your_bucketname -o use_cache=/tmp -o allow_other -o uid=1001 -o mp_umask=002 -o multireq_max=5 /mys3bucket

5. Config mount after reboot

   Add the following command in /etc/rc.local:

   /usr/local/bin/s3fs your_bucketname -o use_cache=/tmp -o allow_other -o uid=1001 -o mp_umask=002 -o multireq_max=5 /mys3bucket

Reference:
How to Mount S3 bucket on EC2 Linux Instance

• Install Nextcloud

# Install Nextcloud stack
sudo snap install nextcloud
# Create administrator account
sudo nextcloud.manual-install <admin_username> <admin_password>
# Configure trusted domains (only localhost by default)
sudo nextcloud.occ config:system:get trusted_domains
sudo nextcloud.occ config:system:set trusted_domains 1 --value=<dns-domain>
# Set 512M as PHP memory limit 
sudo snap get nextcloud php.memory-limit # Should be 512M
sudo snap set nextcloud php.memory-limit=512M
# Set background jobs interval (e.g. checking for new emails, update RSS feeds, ...)
sudo snap set nextcloud nextcloud.cron-interval=10m # Default: 15m

• Set reverse proxy

sudo snap set nextcloud ports.http=81 ports.https=444

Reference:
Nextcloud on AWS
Putting the snap behind a reverse proxy

Deploy kubeless to a Kubernetes cluster

$ export RELEASE=$(curl -s https://api.github.com/repos/kubeless/kubeless/releases/latest | grep tag_name | cut -d '"' -f 4)
$ kubectl create ns kubeless
$ kubectl create -f https://github.com/kubeless/kubeless/releases/download/$RELEASE/kubeless-$RELEASE.yaml

$ kubectl get pods -n kubeless
$ kubectl get deployment -n kubeless
$ kubectl get customresourcedefinition

Deploy sample function

def hello(event, context):
  print event
  return event['data']

$ kubeless function deploy hello --runtime python2.7 \
                                --from-file test.py \
                                --handler test.hello

$ kubectl get functions
$ kubeless function ls

$ kubeless function call hello --data 'Hello world!'

• create a file

  echo This is a sample text file > sample.txt

• delete a file

  del file_name

• move a file

  move stats.doc c:\statistics

• combine files

  copy /b file1 + file2 file3

import pandas as pd
import pyodbc
import sqlalchemy
import urllib
def get_sqlalchemy_engine(driver, server, uid, pwd, database):
    conn_str = 'DRIVER={};SERVER={};UID={};PWD={};DATABASE={}'.format(driver, server, uid, pwd, database)
    quoted = urllib.parse.quote_plus(conn_str)
    engine = sqlalchemy.create_engine('mssql+pyodbc:///?odbc_connect={}'.format(quoted))
    return engine
if __name__ == '__main__':
    # create engine
    driver = 'ODBC Driver 17 for SQL Server'
    server = 'xxx'
    uid = 'xxx'
    pwd = 'xxx'
    database = 'xxx'
    engine = get_sqlalchemy_engine(driver, server, uid, pwd, database)
    # read excel
    file_path = 'xxx'
    df = pd.read_excel(file_path)
    # load into SQL Server
    schema_name = 'xxx'
    table_name = 'xxx'
    df.to_sql(table_name, schema=schema_name, con=engine, index=False, if_exists='replace')

Installation

• Microsoft ODBC Driver 17 for SQL Server
  
  • need admin permission
• SQL Server Management Studio (SSMS)
  
  • need admin permission
• Microsoft SQL Server 2008 Express
  
  • need admin permission
  • issue: The specified credentials that were provided for the SQL Server service are not valid
• pyodbc
  
  • pip install pyodbc

Setup SQL Server

• Set "mixed mode authentication"
• Create a database
• Create and Configure a User in MSSQL
  
  • uncheck "User must change password at next login"
• Create a table

Connect pyodbc to SQL Server

import pyodbc
conn = pyodbc.connect('DRIVER={ODBC Driver 17 for SQL Server};SERVER=test;DATABASE=test;UID=user;PWD=password')
cursor = conn.cursor()
for row in cursor.tables():
    print(row.table_name)