Guangning Yu's Blog

SQL Server Queries

2020-11-18 14:26:27  |  sql_server

Check currently running queries

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

  1. SELECT SPID = er.session_id
  2. ,STATUS = ses.STATUS
  3. ,[Login] = ses.login_name
  4. ,Host = ses.host_name
  5. ,BlkBy = er.blocking_session_id
  6. ,DBName = DB_Name(er.database_id)
  7. ,CommandType = er.command
  8. ,ObjectName = OBJECT_NAME(st.objectid)
  9. ,CPUTime = er.cpu_time
  10. ,StartTime = er.start_time
  11. ,TimeElapsed = CAST(GETDATE() - er.start_time AS TIME)
  12. ,SQLStatement = st.text
  13. FROM sys.dm_exec_requests er
  14. OUTER APPLY sys.dm_exec_sql_text(er.sql_handle) st
  15. LEFT JOIN sys.dm_exec_sessions ses
  16. ON ses.session_id = er.session_id
  17. LEFT JOIN sys.dm_exec_connections con
  18. ON con.session_id = ses.session_id
  19. WHERE st.text IS NOT NULL

Check currently running jobs

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

  1. WITH
  2. CTE_Sysession (AgentStartDate)
  3. AS

Mount the Amazon EFS File System on the EC2 Instance

2020-08-08 09:53:34
  1. Create EFS on AWS web portal

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

  3. Mount EFS on EC2

    1. sudo mkdir efs

    title

    1. sudo chmod 777 /efs
  4. Install amazon-efs-utils for auto-remount

    1. git clone https://github.com/aws/efs-utils
    2. cd efs-utils/
    3. ./build-deb.sh
    4. sudo apt-get -y install ./build/amazon-efs-utils*deb
  5. Configure IAM role in EC2 (already done)

  6. Edit /etc/fstab

    1. fs-xxxxxxxx:/ /efs efs _netdev,tls,iam 0 0
  7. Test mount

    1. sudo mount -fav
  8. Add Linux user in the other EC2's group to avoid readonly issue

    1. sudo usermod -a -G ubuntu guangningyu
    1. 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 PySpark max()/min() function

2020-07-13 09:22:44  |  Spark

test.csv

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

test.py

  1. from pyspark.sql import SparkSession
  2. from pyspark.sql import functions as F
  3. spark = SparkSession \
  4. .builder \
  5. .appName("spark-app") \
  6. .getOrCreate()
  7. spark.sparkContext.setLogLevel("WARN")
  8. df = spark.read.csv("test.csv", header=True)
  9. res = df.groupBy(["key"]).agg(*[
  10. F.max("a"),
  11. F.max("b"),
  12. F.max("c"),
  13. F.min("a"),
  14. F.min("b"),
  15. F.min("c"),
  16. ])
  17. print (res.toPandas())

spark-submit test.py

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

Install Azure Cli on Mac

2020-02-25 15:13:21
  1. brew update && brew install azure-cli
  2. az login
  1. brew tap azure/functions
  2. brew install azure-functions-core-tools@2

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

Create User in Windows Server 2016

2019-12-16 14:31:22
  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

AWS Certified Solutions Architect Associate Notes

2019-12-05 17:59:42  |  AWS

Compute

EC2

  • Billing for interrupted Spot Instance
    title
    title
  • 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:
  • 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

Mount S3 bucket on EC2 Linux Instance

2019-09-16 10:51:31  |  AWS
  1. Install dependencies

    1. sudo apt-get update
    2. 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

    1. git clone https://github.com/s3fs-fuse/s3fs-fuse.git
    2. cd s3fs-fuse
    3. ./autogen.sh
    4. ./configure --prefix=/usr --with-openssl
    5. make
    6. sudo make install
    7. which s3fs
  3. Config credentials

    1. echo "Your_accesskey:Your_secretkey" >> /etc/passwd-s3fs
    2. sudo chmod 640 /etc/passwd-s3fs
  4. Create mounting point

    1. mkdir /mys3bucket
    2. 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:

    1. /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

Setup Nextcloud on Ubuntu

2019-09-09 23:06:06
  • Install Nextcloud
  1. # Install Nextcloud stack
  2. sudo snap install nextcloud
  3. # Create administrator account
  4. sudo nextcloud.manual-install <admin_username> <admin_password>
  5. # Configure trusted domains (only localhost by default)
  6. sudo nextcloud.occ config:system:get trusted_domains
  7. sudo nextcloud.occ config:system:set trusted_domains 1 --value=<dns-domain>
  8. # Set 512M as PHP memory limit
  9. sudo snap get nextcloud php.memory-limit # Should be 512M
  10. sudo snap set nextcloud php.memory-limit=512M
  11. # Set background jobs interval (e.g. checking for new emails, update RSS feeds, ...)
  12. sudo snap set nextcloud nextcloud.cron-interval=10m # Default: 15m
  • Set reverse proxy
  1. sudo snap set nextcloud ports.http=81 ports.https=444

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

Kubeless Basics

2019-05-14 10:09:51  |  Kubernetes

Deploy kubeless to a Kubernetes cluster

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

Deploy sample function

  1. def hello(event, context):
  2. print event
  3. return event['data']
  1. $ kubeless function deploy hello --runtime python2.7 \
  2. --from-file test.py \
  3. --handler test.hello
  1. $ kubectl get functions
  2. $ kubeless function ls
  1. $ kubeless function call hello --data 'Hello world!'

Windows cmd

2019-03-19 18:31:36  |  Windows
  • create a file

    1. echo This is a sample text file > sample.txt
  • delete a file

    1. del file_name
  • move a file

    1. move stats.doc c:\statistics
  • combine files

    1. copy /b file1 + file2 file3

Load Excel file into SQL Server

2019-03-07 15:57:56  |  Python
  1. import pandas as pd
  2. import pyodbc
  3. import sqlalchemy
  4. import urllib
  5. def get_sqlalchemy_engine(driver, server, uid, pwd, database):
  6. conn_str = 'DRIVER={};SERVER={};UID={};PWD={};DATABASE={}'.format(driver, server, uid, pwd, database)
  7. quoted = urllib.parse.quote_plus(conn_str)
  8. engine = sqlalchemy.create_engine('mssql+pyodbc:///?odbc_connect={}'.format(quoted))
  9. return engine
  10. if __name__ == '__main__':
  11. # create engine
  12. driver = 'ODBC Driver 17 for SQL Server'
  13. server = 'xxx'
  14. uid = 'xxx'
  15. pwd = 'xxx'
  16. database = 'xxx'
  17. engine = get_sqlalchemy_engine(driver, server, uid, pwd, database)
  18. # read excel
  19. file_path = 'xxx'
  20. df = pd.read_excel(file_path)
  21. # load into SQL Server
  22. schema_name = 'xxx'
  23. table_name = 'xxx'
  24. df.to_sql(table_name, schema=schema_name, con=engine, index=False, if_exists='replace')

Setup SQL Server and pyodbc

2019-03-05 10:59:13

Setup SQL Server

Connect pyodbc to SQL Server

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

Access another container in Docker

2019-02-26 14:28:04  |  Docker

Steps

  1. Create a network named "test"

    1. docker network create test
  2. Create two containers using the network

    1. docker run --name c1 --network "test" --rm --entrypoint tail mongo -f
    2. docker run --name c2 --network "test" --rm --entrypoint tail mongo -f
  3. Enter one container to ping the other and it will work

    1. docker exec -it c1 bash
    1. apt-get install iputils-ping # install command ping
    1. root@79568c5ce391:/usr/src/app# ping c2
    2. PING c2 (172.18.0.3) 56(84) bytes of data.
    3. 64 bytes from c2.test (172.18.0.3): icmp_seq=1 ttl=64 time=0.137 ms
    4. 64 bytes from c2.test (172.18.0.3): icmp_seq=2 ttl=64 time=0.221 ms
    5. 64 bytes from c2.test (172.18.0.3): icmp_seq=3 ttl=64 time=0.232 ms
    6. ...

Notes

Using default network or "bridge" network does not work:

  1. docker run --name c1 --rm --entrypoint tail web_scraper:v1 -f
  2. docker run --name c2 --rm --entrypoint tail web_scraper:v1 -f
  1. docker run --name c1 --network "bridge" --rm --entrypoint tail web_scraper:v1

Publish or expose port (-p, --expose)

2019-02-26 13:42:45  |  Docker
  1. $ docker run -p 127.0.0.1:80:8080/tcp ubuntu bash

This binds port 8080 of the container to TCP port 80 on 127.0.0.1 of the host machine. You can also specify udp and sctp ports.

  1. $ docker run --expose 80 ubuntu bash

This exposes port 80 of the container without publishing the port to the host system’s interfaces.

Fix Chinese characters won't display in SSH

2019-02-24 23:29:55

Solution

Set up the same locale in both of the local laptop and remoter server:

  1. export LC_ALL=en_US.UTF-8
  2. export LANG=en_US.UTF-8

Expand the EBS root volume of EC2 Linux instance

2019-02-24 15:10:42  |  AWS
  1. Modify the EBS Volume from the console https://console.aws.amazon.com/ec2/
  2. Use the lsblk command to list the block devices attached to the instance

    1. $ lsblk
    2. NAME MAJ:MIN RM SIZE RO TYPE MOUNTPOINT
    3. xvda 202:0 0 30G 0 disk
    4. `-xvda1 202:1 0 8G 0 part /
    5. loop0 7:0 0 91M 1 loop /snap/core/6405
    6. loop1 7:1 0 87.9M 1 loop /snap/core/5742
    7. loop2 7:2 0 17.9M 1 loop /snap/amazon-ssm-agent/1068
    8. loop3 7:3 0 16.5M 1 loop /snap/amazon-ssm-agent/784
    9. loop4 7:4 0 18M 1 loop /snap/amazon-ssm-agent/930
  3. Use the df -h command to report the existing disk space usage on the file system

    1. $ sudo df -h /dev/xvd*
    2. Filesystem Size Used Avail Use% Mounted on
    3. udev 488M 0 488M 0% /dev
    4. /dev/xvda1 7.7G 7.4G 370M 96% /
  4. Expand the modified partition using growpart

    1. $ sudo growpart /dev/xvda 1
    2. CHANGED: partition=1 start=2048 old: size=16775135 end=16777183 new: size=62912479,end=62914

Create Free SSL/TLS Certificates using Certbot

2019-02-24 12:50:01

Install

  1. sudo add-apt-repository ppa:certbot/certbot
  2. sudo apt-get update
  3. sudo apt-get install certbot python-certbot-nginx

Setup

Running this command will get a certificate for you and have Certbot edit your Nginx configuration automatically to serve it.

  1. sudo certbot --nginx
  1. # for a specific name
  2. sudo certbot --nginx -d example.com

Renew

The Certbot packages on your system come with a cron job that will renew your certificates automatically before they expire.
You can test automatic renewal for your certificates by running this command:

  1. sudo certbot renew --dry-run

Docker Basics

2019-02-20 22:04:31  |  Docker

Images

  1. docker images
  2. docker build -t image_name .
  3. docker rmi $(docker images | grep "^<none>" | awk "{print $3}") # remove all untagged images
  4. docker save image_name > image_name.tar # save image as a tar file
  5. docker load < busybox.tar.gz # load image

Containers

  1. docker run -p 27017:27017 -v mongodbdata:/data/db mongo
  2. docker ps -a
  3. docker exec -it ubuntu_bash bash
  4. docker rm container_name
  5. docker rm $(docker ps -a -q) # remove all stopped containers

Volumes

  1. docker volume create mongodbdata
  2. docker volume ls
  3. docker volume inspect mongodbdata

Networks

  1. docker network ls
  2. docker network create network_name
  3. docker network inspect network_name
  4. docker network rm network_name

Webpack4 Setup

2019-02-12 23:42:38  |  Javascript

Kubernetes Basics

2019-02-17 01:40:29  |  Kubernetes