BigQuery 筆記1 – 如何使用 bq 指令工具設定資料集(表)的到期時間

筆記：如何使用 Google Cloud BiqQuery 的指令用法

一、 設定資料集的效期

語法:
bq update --default_table_expiration ttl_seconds project_id:dataset

說明:

• ttl_seconds 最小可用值為 3600 秒 (即最短效期為1小時, 參閱範例 1)若要取消資料集預設效期僅需將 ttl_seconds 設定為0 即可 (範例 2)
• default table expiration 設定僅適用於新建立的資料表，已存在的資料表會保持原設定狀態。若要修改已存資料表效期，請參閱＂二、設定資料表的效期＂

範例 1: set expiration 1 hours later to myDataset

    bq update --default_table_expiration 3600 myProject:myDataset

範例 2: disable expiration to myDataset

    bq update --default_table_expiration 0 myProject:myDataset

二、設定資料表的效期

語法:
    bq update --expiration=SECONS PROJECT:DATASET.TABLE

範例 3: set expiration 2 hours later to myTable

    bq update --expiration 7200 myProject:myDataset.myTable

範例 4: disable expiration to myTable

    bq update --expiration 0 myProject:myDataset.myTable

參考資料

• bq command-line tool reference

Typescript beginning with ts-node

Install nvm first

https://github.com/nvm-sh/nvm#installing-and-updating

curl -o- https://raw.githubusercontent.com/nvm-sh/nvm/v0.39.3/install.sh | bash

Example of install nvm

~$ curl -o- https://raw.githubusercontent.com/nvm-sh/nvm/v0.39.3/install.sh | bash
  % Total    % Received % Xferd  Average Speed   Time    Time     Time  Current
                                 Dload  Upload   Total   Spent    Left  Speed
100 15916  100 15916    0     0  32283      0 --:--:-- --:--:-- --:--:-- 32283
=> Downloading nvm from git to '/home/ci/.nvm'
=> Cloning into '/home/ci/.nvm'...
remote: Enumerating objects: 358, done.
remote: Counting objects: 100% (358/358), done.
remote: Compressing objects: 100% (304/304), done.
remote: Total 358 (delta 41), reused 164 (delta 28), pack-reused 0
Receiving objects: 100% (358/358), 218.72 KiB | 1.40 MiB/s, done.
Resolving deltas: 100% (41/41), done.
* (HEAD detached at FETCH_HEAD)
  master
=> Compressing and cleaning up git repository

=> Appending nvm source string to /home/ci/.bashrc
=> Appending bash_completion source string to /home/ci/.bashrc
=> Close and reopen your terminal to start using nvm or run the following to use it now:

export NVM_DIR="$HOME/.nvm"
[ -s "$NVM_DIR/nvm.sh" ] && \. "$NVM_DIR/nvm.sh"  # This loads nvm
[ -s "$NVM_DIR/bash_completion" ] && \. "$NVM_DIR/bash_completion"  # This loads nvm bash_completion

Install and select Node.js version by nvm command line

nvm install v18

Please logout and login again to activate nvm.sh shell environment

~$ nvm ls
            N/A
iojs -> N/A (default)
node -> stable (-> N/A) (default)
unstable -> N/A (default)
~$ nvm install v18
Downloading and installing node v18.12.1...
Downloading https://nodejs.org/dist/v18.12.1/node-v18.12.1-linux-x64.tar.xz...
################################################################################################################################ 100.0%
Computing checksum with sha256sum
Checksums matched!
Now using node v18.12.1 (npm v8.19.2)
Creating default alias: default -> v18 (-> v18.12.1)
~$ nvm ls
->     v18.12.1
default -> v18 (-> v18.12.1)
iojs -> N/A (default)
unstable -> N/A (default)
node -> stable (-> v18.12.1) (default)
stable -> 18.12 (-> v18.12.1) (default)
lts/* -> lts/hydrogen (-> v18.12.1)
lts/argon -> v4.9.1 (-> N/A)
lts/boron -> v6.17.1 (-> N/A)
lts/carbon -> v8.17.0 (-> N/A)
lts/dubnium -> v10.24.1 (-> N/A)
lts/erbium -> v12.22.12 (-> N/A)
lts/fermium -> v14.21.2 (-> N/A)
lts/gallium -> v16.19.0 (-> N/A)
lts/hydrogen -> v18.1

Install typescript

~$ npm install -g typescript

added 1 package, and audited 2 packages in 2s

found 0 vulnerabilities
npm notice
npm notice New major version of npm available! 8.19.2 -> 9.2.0
npm notice Changelog: https://github.com/npm/cli/releases/tag/v9.2.0
npm notice Run npm install -g npm@9.2.0 to update!
npm notice

install typescript with specific version

npm install -g typescript@4.6.4

Example of hello.ts

Create example hello.ts file

cat << EOF >> hello.ts
var name:string = "Coder of Typescript";
console.log(`Hello, ${name}`);
EOF

Results of hello.ts

~$ ts-node hello.ts
Hello, Coder

Conclusions

I considere that typescript is a good tool for nodejs to write more stable code. Enjoy The journal for Typescript writing!

ffmpeg 降低聲音品質來縮小 mp3 檔案大小

 

目的縮小MP3檔案大小

• 雙聲道改單聲道

• 範例為音訊位元速率(bitrate) 從 160 kb/s 降為 64 kb/s

指令語法

ffmpeg -i <160k stereo filename>.mp3 -ac 1 -b:a 64k <output 64k mono filename>.mp3

-ac <channels> 設定音訊頻道數(set number of audio channels)

• 1 表示單聲道(mono)

• 2 表示雙聲道(stereo)

-b:a <bitrate> 音訊位元速率(bitrate)

小結

檔案大小縮小約為原來的 39.88%

~$ python
Python 3.9.6 (default, Jun 29 2021, 04:45:03)
..
>>> round(80973 / 203040 * 100,2)
39.88

執行指令範例

$ ffmpeg -i 1_setup_160k_stereo.mp3 -ac 1 -b:a 64k 1_setup_64k_mono.mp3
...
$ ffmpeg -i 1_setup_160k_stereo.mp3
Input #0, mp3, from '1_setup_160k_stereo.mp3':
Duration: 00:00:10.12, start: 0.069063, bitrate: 160 kb/s
  Stream #0:0: Audio: mp3, 16000 Hz, stereo, fltp, 160 kb/s
  Metadata:
    encoder         : LAME3.98r
​
$ ffmpeg -i 1_setup_64k_mono.mp3
Input #0, mp3, from 1_setup_64k_mono.mp3':
Metadata:
  encoder         : Lavf58.45.100
Duration: 00:00:10.08, start: 0.069063, bitrate: 64 kb/s
  Stream #0:0: Audio: mp3, 16000 Hz, mono, fltp, 64 kb/s
​
​
$ ls -la 1_setup*
-rw-r--r--@ 1 tester staff  203040 Jul 24 18:38 1_setup_160k_stereo.mp3
-rw-r--r--@ 1 tester staff   80973 Jul 24 18:43 1_setup_64k_mono.mp3

延伸閱讀

• 使用 ffmpeg 指令合併的多個MP4檔案

Dev Tips - command line snippets

 

Command - equal to ldd

objdump -p <path/to/program> | grep -i needed

Command - while loop with date and sleep

while [ 1 ] ; do date +"%M:%S" ; sleep 1 ; done

Command - cp

merge one folder to another folder

if file exists in dummy_tmp, it will replace by file in origin_tmp

$ cp -RT origin_tmp/ dummy_tmp/
$ diff -ur dummy_tmp origin_tmp

Bash Tip - date 日期指令轉換 unix epoch time 範例

 

Command - date

Example 1 date - convert unix epoch timestamp to date string

$ date  --date='@1617694020'
Tue 06 Apr 2021 07:27:00 AM UTC

Example 2 date - convert unix timestamp to custom formatted date string

$ date +"%Y-%m-%d %H:%m" --date='@1617694020'
2021-04-06 07:04
$ date  --date='@1617694020.904'
Tue 06 Apr 2021 07:27:00 AM UTC

Example 3 date -- MacOS Mojave convert timestamp to date string

## MacOS Mojave not support --date
$ date  --date='@1617694020.904'
date: illegal option -- -
usage: date [-jnRu] [-d dst] [-r seconds] [-t west] [-v[+|-]val[ymwdHMS]] ...
          [-f fmt date | [[[mm]dd]HH]MM[[cc]yy][.ss]] [+format]
## use -r to instead
$ date -r 1625043610 +"%Y%m%d %H:%M:%S"
20210630 17:00:10

bash tips - 使用部分執行檔名來發出終止程式指令 terminate processes by partial process name

在 Linux 系統中，時常會遇到需要終止執行中的程式，如果只有單一執行檔程式執行多個 Instances 時，可簡單使用下列指令例如：

killall <process name>

但若是有多個 prefix 相同的程式就要分別指定

killall test_a test_b test_c

為了方便使用，初步寫一版可使用部分執行檔來終止程式的腳本，下例腳本是使用以下 /bin/sh 測試：

• busybox v1.31
• dash 0.5.8-2.10

Script example

#!/bin/sh
pname="$1"
if [ "x$pname" = x ]; then
echo "syntax: <partial process name>"； exit 1 ； fi
ps -a | grep "$pname" | \
grep -v grep | \
sed -e "s/^[ ]*\([0-9][0-9]*\) .*$/\1/g" | \
while read pid ; do kill $pid ; echo "terminate $pid" ; done

 Test output

~$ sleep 86400 &
[1] 2824
~$ sleep 86402 &
[2] 2832
​

sh 加上 -x 可除錯執行

~$ sh -x terminate_pname.sh sleep
+ pname=sleep
+ [ xsleep = x ]
+ ps -a
+ grep sleep
+ grep -v grep
+ sed -e s/^[ ]*\([0-9][0-9]*\) .*$/\1/g
+ read pid
+ kill 2824
+ echo terminate 2824
terminate 2824
+ read pid
+ kill 2832
+ echo terminate 2832
terminate 2832
+ read pid
[1]- Terminated              sleep 86400
[2]+ Terminated              sleep 86402
​

參考文章

• 阿班 Bash 常用指令範例

• Bash Tip - Use bash to implement like C's argc and argv

• Bash Tip - Run Command with multiple arguments with ssh

• Bash Tip - 單行取得目前腳本檔絕對路徑的方法

VMware Player 與Windows 10裝置及Credential Guard不相容問題

最近試玩  WSL2 之後，發現了 VM Player 無法正常啟動，發生下列錯誤：

VMware Player and Device/Credential Guard are not compatible. VMware Player can be run after disabling Device/Credential Guard. Please visit http://www.vmware.com/go/turnoff_CG_DG for mor details.

看了該 VMware 文件 VMware Workstation and Device/Credential Guard are not compatible  error in VMware Workstation on Windows 10 host (2146361)

小結

這個問題主因是Windows 10 該功能有增加存取限制，在 Workstation 15.5.6 以上版本已修正該問題。

可到此連結下載15.5.7 版本 https://my.vmware.com/en/web/vmware/downloads/details?downloadGroup=PLAYER-1557&productId=800&rPId=47861