# 技术备忘录

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

## 2025-10-15 Spark的时区设置

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

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

```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|
+-------------------+
```

获取时区设置：

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

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

```python3
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

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

## MongoDB 权限配置

### 管理员权限配置

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

### 用户配置样例

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

### 删除用户

```javascript
db.dropUser("username")
```

## MongoDB 慢查询相关

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

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

### 统计大于10秒的慢查数量

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

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

### 查询Running总时间

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

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

```javascript
// 检查大于 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;
            }
        }
    }
)
```

### 杀死超时任务

```javascript
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）](https://segmentfault.com/a/1190000005818249)

```python
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

```python
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

```bash
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

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

### Merge subtitle (srt) file and video

```bash
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://www.youtube.com/watch?v=CakU8hIaP7c)
* <https://github.com/ogra1/picamera-streaming-demo>

### RPI3 Wifi

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

```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
```

```bash
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?](https://raspberrypi.stackexchange.com/questions/98598/how-to-setup-the-raspberry-pi-3-onboard-wifi-for-ubuntu-server-18-04-with-netpla)

## Encryption

### SAML2 key/pem generator

```bash
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

```yaml
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：

```bash
kubectl describe secret <Token Secret Name>
```

```yaml
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`.

```markup
<?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`加入这一段：

```ini
[url "ssh://git@github.com/"]
insteadOf = https://github.com/
```

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


---

# Agent Instructions: Querying This Documentation

If you need additional information that is not directly available in this page, you can query the documentation dynamically by asking a question.

Perform an HTTP GET request on the current page URL with the `ask` query parameter:

```
GET https://blog.tsingjyujing.com/other-tech/note.md?ask=<question>
```

The question should be specific, self-contained, and written in natural language.
The response will contain a direct answer to the question and relevant excerpts and sources from the documentation.

Use this mechanism when the answer is not explicitly present in the current page, you need clarification or additional context, or you want to retrieve related documentation sections.