技术备忘录

工作中乃至生活中解决了一些或大或小，或简单或复杂的技术问题。 很多都因为没有好好的记录，所以之后需要再解决一遍。 特地记录在此，以便后续使用。

2025-10-15 Spark的时区设置

在使用一些Spark SQL函数的时候，会需要准确的设置时区，否则执行的结果就会有误差。

比如以UTC时区执行如下SQL：

spark.sql("select from_unixtime(1760362193,'yyyy-MM-dd HH:mm:ss') as dt").show()

会得到：

+-------------------+
|                 dt|
+-------------------+
|2025-10-13 13:29:53|
+-------------------+

但是如果是东京时区，则是：

+-------------------+
|                 dt|
+-------------------+
|2025-10-13 22:29:53|
+-------------------+

获取时区设置：

spark.conf.get("spark.sql.session.timeZone")

设置时区（以东京为例）：

spark.conf.set("spark.sql.session.timeZone", "Asia/Tokyo")

批量杀线程

#!/bin/bash
ps -ef|grep $1|grep -v grep|awk '{print $2}'|xargs -r kill -9

MySQL

创建用户并且授权读写Schema

create user ${USERNAME} IDENTIFIED by '${PASSWORD}';
show grants for ${USERNAME};
grant all on ${SCHEMA}.* to ${USERNAME};
flush privileges;

MongoDB 权限配置

管理员权限配置

db.createUser({user:"root",pwd:"123456",roles: [{ role:"root", db:"admin"}]})

用户配置样例

db.createUser({ 
    user: "someuser",
    pwd: "somepassword",
    roles:[
        { role: "readWrite", db: "somedb" }
    ]
})

删除用户

db.dropUser("username")

MongoDB 慢查询相关

查找当前大于10.0秒的、且最慢的一个查询的详情

mongo --quiet --eval "let limit_time = 10.0;" slow_query.js

统计大于10秒的慢查数量

mongo --quiet --eval "db.currentOp().inprog.filter(x=>x['secs_running']>10).length"

一般统计大于5秒，15秒，30秒,60秒，300秒的慢查数量。

查询Running总时间

这个指标可以从侧面反应数据库的操作体验和压力。

mongo --quiet --eval "db.currentOp().inprog.map(x=>x.microsecs_running/1000000.0).reduce((a,b)=>a+b)"

// 检查大于 limit_time 秒的慢查询数据
// let limit_time = 10.0;
// 上一句在eval里面执行来达到动态设置的效果
db.currentOp().inprog.filter(
    x=>x["microsecs_running"]>(limit_time*1000000)
).sort(
    // 取最慢的一条进行展示
    (a,b)=>{
        const va = a["microsecs_running"];
        const vb = b["microsecs_running"];
        if(a==b){
            return 0;
        }else{
            if(a>b){
                return -1;
            }else{
                return 1;
            }
        }
    }
)

杀死超时任务

let over_time = 60.0;// Over 1 min
db.currentOp().inprog.forEach(
    opInfo=>{
        try{
            const opId = opInfo["opid"];
            const opDuration = opInfo["secs_running"];
            if(opDuration>over_time){
                db.killOp(opId);
                console.log(`Operation ${opId} killed, details: ${JSON.stringify(opInfo)}`)
            }
        }catch(err){
            console.error("Error while executing killing filter.")
            console.error(err)
        }
    }
)

Python Tricks

Lazy Evaluation

From the blog Python 延迟初始化（lazy property）

class lazy(object):
    def __init__(self, func):
        self.func = func

    def __get__(self, instance, cls):
        val = self.func(instance)
        setattr(instance, self.func.__name__, val)
        return val

def lazy_property(func):
    attr_name = "_lazy_" + func.__name__

    @property
    def _lazy_property(self):
        if not hasattr(self, attr_name):
            setattr(self, attr_name, func(self))
        return getattr(self, attr_name)

    return _lazy_property

Comparing with the lazy, it more like cache the result, maybe can also simply replaced by lru_cache. We have to ensure the return value won't change while using it.

Example of Async wait

import asyncio
from asyncio import Future
from typing import Dict

futures: Dict[str, Future] = {}


@app.get("/test/lock/{task_id}")
async def test_lock(task_id: str):
    result = asyncio.get_event_loop().create_future()
    # Put the task in Queue
    futures[task_id] = result
    return {
        "result": await result
    }


@app.get("/test/release/{task_id}")
async def test_release(task_id: str):
    if task_id not in futures:
        raise Exception("Task not found")
    # Set result while the task finished
    futures[task_id].set_result(f"{task_id} is done")
    return {
        "task": task_id
    }

FFMPEG

Convert MP4 File to MP3 File

ffmpeg -i ${MP4_FILE} -vn \
       -acodec libmp3lame -ac 2 -ab 320k -ar 48000 \
        ${MP3_FILE}

Replace -ab {bitrate} to -ac 2 -qscale:a 4 to use VBR

Convert to H264 mp4 file

ffmpeg -vcodec h264 -acodec copy -movflags +faststart -pix_fmt yuv420p -crf 23 -i input.mp4 output.mp4

Merge subtitle (srt) file and video

ffmpeg -i ${INPUT_VIDEO} -f srt -i ${SUBTITLE_FILE} -c:v copy -c:a copy -c:s srt ${OUTPUT_VIDEO}
# -c:s can also be copy

树莓派

RPI3 Ubuntu 摄像头

1. 只能使用32位的系统

2. 在/boot/firmware/config.txt里增加start_x=1

3. 执行 sudo snap install picamera-streaming-demo以后重启系统

4. 打开 http://:8000/ 即可查看

参考资料：

• Enable Raspberry Pi Camera in Ubuntu 20.04 MATE on Raspberry Pi 4 8GB RAM

• https://github.com/ogra1/picamera-streaming-demo

RPI3 Wifi

1. Edit /etc/netplan/xxxxxx.yaml:

# This file is generated from information provided by
# the datasource.  Changes to it will not persist across an instance.
# To disable cloud-init's network configuration capabilities, write a file
# /etc/cloud/cloud.cfg.d/99-disable-network-config.cfg with the following:
# network: {config: disabled}
network:
    version: 2
    ethernets:
        eth0:
            optional: true
            dhcp4: true
    # add wifi setup information here ...
    wifis:
        wlan0:
            optional: true
            access-points:
                "YOUR-SSID-NAME":
                    password: "YOUR-NETWORK-PASSWORD"
            dhcp4: true

sudo netplan --debug try 
# (continue even if there are errors)
sudo netplan --debug generate 
# (provides more details in case of issues with the previous command)
sudo netplan --debug apply 
# (if no issues during the previous commands)

Reference: How to setup the Raspberry Pi 3 onboard WiFi for Ubuntu Server 18.04 with netplan?

Encryption

SAML2 key/pem generator

openssl req -x509 -sha256 -nodes -days 3650 -newkey rsa:2048 -keyout encryption.key -out encryption.pem
openssl req -x509 -sha256 -nodes -days 3650 -newkey rsa:2048 -keyout signing.key -out signing.pem

K8S

创建一个管理员账户的配置文件

首先创建一个Service Account

apiVersion: v1
kind: ServiceAccount
metadata:
  name: <Account Name>
  namespace: default
---
apiVersion: rbac.authorization.k8s.io/v1
kind: ClusterRoleBinding
metadata:
  name: admin-user
roleRef:
  apiGroup: rbac.authorization.k8s.io
  kind: ClusterRole
  name: cluster-admin
subjects:
- kind: ServiceAccount
  name: <Account Name>
  namespace: default

获取账户详情 kubectl describe serviceaccount <Account Name>

随后获取secret的名字，格式一般是 <Account Name>-token-<随机字符串>

最后获取Token：

kubectl describe secret <Token Secret Name>

apiVersion: v1
kind: Config
users:
- name: <Account Name>
  user:
    token: "<TOKEN>"
clusters:
- name: <Cluster Name>
  cluster:
    api-version: v1
    server: <API ENDPOINT>
contexts:
- name: <Cluster Name>
  context:
    cluster: <Cluster Name>
    user: <Account Name>
current-context: <Cluster Name>

Java/Scala/Kotlin

Maven Settings

Mainly ~/.m2/settings.xml.

<?xml version="1.0" encoding="UTF-8"?>
<settings xmlns="http://maven.apache.org/SETTINGS/1.1.0"
  xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
  xsi:schemaLocation="http://maven.apache.org/SETTINGS/1.1.0 http://maven.apache.org/xsd/settings-1.1.0.xsd">

  <servers>
    <server>
      <id>nexus-snapshots</id>
      <username>{username}</username>
      <password>{password}</password>
    </server>
    <server>
      <id>nexus-releases</id>
      <username>{username}</username>
      <password>{password}</password>
    </server>
  </servers>

  <mirrors>
    <mirror>
      <id>central</id>
      <name>central</name>
      <url>https://nexus.xxxx.com/repository/maven/</url>
      <mirrorOf>*</mirrorOf>
    </mirror>
  </mirrors>

</settings>

Glances

屏蔽多余的设备

在使用snap的时候经常会有一些奇怪的设备，包括loop{x}或者是/snap/之类的，这个时候只要在配置文件里加上

[fs]
hide=/boot.*,/snap.*
[diskio]
hide=loop.*
[network]
hide=docker.*,lo,veth.*

就可以屏蔽这些设备。

官方文档：

1. https://glances.readthedocs.io/en/latest/aoa/diskio.html

2. https://glances.readthedocs.io/en/latest/aoa/fs.html

3. 配置文件的位置 https://glances.readthedocs.io/en/latest/config.html

Git

自动使用SSH代替HTTP

来源：https://www.digitalocean.com/community/tutorials/how-to-use-a-private-go-module-in-your-own-project

在~/.gitconfig加入这一段：

[url "ssh://[email protected]/"]
insteadOf = https://github.com/

不仅可以用于导入私有的Go模块，还可以用于Brew安装的时候自动拉取Repo。