BigQuery で主成分分析

BigQuery で主成分分析を行う方法のメモ。 ここでは、5次元のデータを2次元に次元圧縮します。

■テーブル作成・データ登録

drop table if exists dataset.test_data;

create table dataset.test_data (
  id      string,
  values  array
);

insert into dataset.test_data values ('id_1', [1.0, 0.0, 1.0, 3.0, 0.0]);
insert into dataset.test_data values ('id_2', [1.0, 2.0, 1.0, 1.0, 1.0]);
insert into dataset.test_data values ('id_3', [0.0, 2.0, 0.0, 1.0, 1.0]);
insert into dataset.test_data values ('id_4', [1.0, 0.0, 3.0, 2.0, 3.0]);
insert into dataset.test_data values ('id_5', [0.0, 0.0, 0.0, 2.0, 0.0]);
insert into dataset.test_data values ('id_6', [0.0, 0.0, 2.0, 2.0, 4.0]);
insert into dataset.test_data values ('id_7', [1.0, 2.0, 1.0, 1.0, 0.0]);
insert into dataset.test_data values ('id_8', [0.0, 2.0, 3.0, 2.0, 2.0]);
insert into dataset.test_data values ('id_9', [1.0, 2.0, 0.0, 2.0, 0.0]);
insert into dataset.test_data values ('id_10', [1.0, 0.0, 3.0, 1.0, 0.0]);

■モデル作成

create or replace model dataset.test_model
options (
  model_type = 'pca'
  , num_principal_components = 2
  , scale_features = false
  , pca_solver = 'full'
)
as (
  select
    values[0] as f0
    , values[1] as f1
    , values[2] as f2
    , values[3] as f3
    , values[4] as f4
    , values[5] as f5
  from
    dataset.test_data
);

■適用

select
  *
from
  ml.predict(model dataset.test_model,
  (
    select
      id
      , values[0] as f0
      , values[1] as f1
      , values[2] as f2
      , values[3] as f3
      , values[4] as f4
      , values[5] as f5
      , values[6] as f6
    from
      dataset.test_data
  ),
  struct(true as keep_original_columns)
);

[{
  "principal_component_1": "-0.69271374229229665",
  "principal_component_2": "1.6535767369786081",
  "id": "id_1",
  "f0": "1.0",
  "f1": "0.0",
  "f2": "1.0",
  "f3": "3.0",
  "f4": "0.0"
}, {
  "principal_component_1": "0.31854554694562942",
  "principal_component_2": "1.5448567976194651",
  "id": "id_10",
  "f0": "1.0",
  "f1": "0.0",
  "f2": "3.0",
  "f3": "1.0",
  "f4": "0.0"
}, {
  "principal_component_1": "-0.67889365782004729",
  "principal_component_2": "-0.94631539186280522",
  "id": "id_2",
  "f0": "1.0",
  "f1": "2.0",
  "f2": "1.0",
  "f3": "1.0",
  "f4": "1.0"
}, {
  "principal_component_1": "-1.2041817682899503",
  "principal_component_2": "-1.3374039692570403",
  "id": "id_3",
  "f0": "0.0",
  "f1": "2.0",
  "f2": "0.0",
  "f3": "1.0",
  "f4": "1.0"
}, {
  "principal_component_1": "2.6487614707519027",
  "principal_component_2": "0.36207867152786921",
  "id": "id_4",
  "f0": "1.0",
  "f1": "0.0",
  "f2": "3.0",
  "f3": "2.0",
  "f4": "3.0"
}, {
  "principal_component_1": "-1.3025753320545592",
  "principal_component_2": "0.98632696171180989",
  "id": "id_5",
  "f0": "0.0",
  "f1": "0.0",
  "f2": "0.0",
  "f3": "2.0",
  "f4": "0.0"
}, {
  "principal_component_1": "2.8720208417866377",
  "principal_component_2": "-0.5153230138544187",
  "id": "id_6",
  "f0": "0.0",
  "f1": "0.0",
  "f2": "2.0",
  "f3": "2.0",
  "f4": "4.0"
}, {
  "principal_component_1": "-1.4274411393246851",
  "principal_component_2": "-0.46000228387475217",
  "id": "id_7",
  "f0": "1.0",
  "f1": "2.0",
  "f2": "1.0",
  "f3": "1.0",
  "f4": "0.0"
}, {
  "principal_component_1": "1.3995485642410159",
  "principal_component_2": "-0.88215219479355511",
  "id": "id_8",
  "f0": "0.0",
  "f1": "2.0",
  "f2": "3.0",
  "f3": "2.0",
  "f4": "2.0"
}, {
  "principal_component_1": "-1.9330707839436485",
  "principal_component_2": "-0.40564231419518071",
  "id": "id_9",
  "f0": "1.0",
  "f1": "2.0",
  "f2": "0.0",
  "f3": "2.0",
  "f4": "0.0"
}]

■固有値など

select
  *
from
  ml.principal_component_info(model dataset.test_model)
;

[{
  "principal_component_id": "0",
  "eigenvalue": "3.020722723318388",
  "explained_variance_ratio": "0.545913745178022",
  "cumulative_explained_variance_ratio": "0.545913745178022"
}, {
  "principal_component_id": "1",
  "eigenvalue": "1.147655631054062",
  "explained_variance_ratio": "0.20740764416639679",
  "cumulative_explained_variance_ratio": "0.75332138934441883"
}]

TypeScript で Cloud Storage のファイルをダウンロード

TypeScript で Cloud Storage のファイルをダウンロードする方法のメモ。

■ライブラリのインストール

npm install @google-cloud/storage

以下のプログラムで cloud-storage-foo/bar/baz.html をダウンロード。

■プログラム

import { Bucket, Storage } from '@google-cloud/storage';

(async () => {
  const target_storage = 'cloud-storage-foo';
  const target_file = 'bar/baz.html';

  const storage = new Storage();
  const bucket = storage.bucket(target_storage);
  const file = bucket.file(target_file);

  const contents = await file.download();
  for (const content of contents) {
    console.log(content.toString('utf-8'));
  }
})();

TypeScript で avif ファイルを jpeg に変換

TypeScript で avif ファイルを jpeg に変換する方法のメモ。

■sharp のインストール

npm install sharp

■プログラム

import { readFileSync, writeFileSync } from 'fs';
import sharp from 'sharp';

(async () => {
  const inFile = process.argv[2];
  const outFile = process.argv[3];

  await sharp(inFile).jpeg().toFile(outFile);
})();

heic 形式の画像ファイルを jpeg に変換

heic 形式の画像ファイルを jpeg に変換する方法のメモ。

■heic-convert のインストール

npm install heic-convert
npm install --save-dev @types/heic-convert

■プログラム

import { readFileSync, writeFileSync } from 'fs'
import convert from 'heic-convert'

(async () => {
const inFile = process.argv[2];
const outFile = process.argv[3];

const inBuf = readFileSync(inFile);
const outBuf = await convert({
buffer: inBuf,
format: 'PNG',
quality: 1,
});
const outArr = new Uint8Array(outBuf);
writeFileSync(outFile, outArr);
})();

上記のプログラムで heic を jpeg に変換します。

$ ts-node heic_to_heic.ts img.heic img.png

■heic 形式のファイルのマジックナンバーを確認

$ cat img.heic | od -tx1z | head -1
0000000 00 00 00 1c 66 74 79 70 68 65 69 63 00 00 00 00 >....ftypheic....<

マジックナンバーは5バイト目以降が ftypheic のため、HEIC であることが確認できます。

■jpeg 形式のファイルのマジックナンバーを確認

$ cat img.jpg | od -tx1z | head -1
0000000 ff d8 ff e0 00 10 4a 46 49 46 00 01 01 00 00 01  >......JFIF......<

jpeg のマジックナンバーは 0xff 0xd8 のため、jpeg に変換されていることがわかります。

WordPress.com のアプリケーションパスワード

WordPress.com の ASP サービスでのアプリケーションパスワードの設定方法のメモ。

WordPress.com では、２段階認証の設定を行うとアプリケーションパスワードを設定することができます。 しかし、ここで作成したアプリケーションパスワードでは、REST API の認証がとおりません。

REST API の認証がとおるアプリケーションパスワードは、サイトのドメインが xxx.com であれば、

https://xxx.com/wp-admin/profile.php

にブラウザで直接アクセスします。

「アプリケーションパスワード」欄で「新しいアプリケーションパスワード名」にアプリケーションパスワード名を入力し、 「新しいアプリケーションパスワードを追加」をクリックして、表示されたアプリケーションパスワードを使います。

TypeScript では、以下のようなプログラムで投稿することができます。

imoprt WPAPI from 'wpapi';
(async () => {
  const config = {
    endpoint: 'https://xxx.yyy.zzz/wp-json',
    username: '{user name}',
    password: '{application password}', // uuuu vvvv wwww xxxx yyyy zzzz 形式
  };
  const wpapi = new WPAPI(config);

  const article = {
    title: 'application password test'
  };
  const res = await wapapi.posts().create(article);
	console.log(res);
})();

BigQuery で重複するレコードを削除

BigQuery で重複するレコードを削除する方法のメモ。
BigQuery で primary key の設定を行わないと、意図せずレコードが重複する場合があります。
そのため、各レコードに uuid を付与し、primary key 相当の id と uuid を使って重複するレコードを削除します。

■テーブル作成・データ登録
drop table if exists dataset.test_dup;

create table dataset.test_dup (
  id  string
);
insert into dataset.test_dup values ('123');
insert into dataset.test_dup values ('456');
insert into dataset.test_dup values ('456');
insert into dataset.test_dup values ('789');
insert into dataset.test_dup values ('789');
insert into dataset.test_dup values ('789');

■カラムを追加
alter table dataset.test_dup add column uuid string;
update dataset.test_dup set uuid = generate_uuid();

alter table dataset.test_dup add column min_uuid string;
update
dataset.test_dup as td
set
min_uuid = mr.min_uuid
from
(select
id
, min(uuid) as min_uuid
from
dataset.test_dup
group by
id
) as mu
where
td.id = mu.id
;

■テーブル
select
*
from
dataset.test_dup
order by
id asc
;

■レコード
id uuid min_uuid
123 133... 133...
456 1a8... 1a8...
456 f11... 1a8...
789 902... 902...
789 d68... 902...
789 cdb... 902...

■重複レコード削除
delete from
  dataset.test_dup
where
  uuid > min_uuid
;

■重複レコード削除後
id uuid min_uuid
123 133... 133...
456 1a8... 1a8...
789 902... 902...

curl で elastic cloud にアクセス

curl で Elastic Cloud にアクセスする方法のメモ。

#!/bin/sh

NODE_URL='{ノード (https://...)}'
INDEX='{インデックス}'
ENC_API_KEY='{エンコード済 API Key}'

curl \
    -X GET \
    -H "Content-Type: application/json" \
    -H "Authorization:ApiKey ${ENC_API_KEY}" \
    "${NODE_URL}/${INDEX}/_search?pretty" \
    -d '
{
  "query": {
    "match_all": {}
  },
  "size": 10
}'

TypeScript で elastic cloud に接続

TypeScript で elastic cloud に接続する方法のメモ。

import { Client } from '@elastic/elasticsearch';

(async () => {
  const client = new Client({
    node: '{ノード (https://...)}',
    cloud: {
      id: '{Cloud ID}'
    },
    auth: {
      username: '{ユーザ名}',
      password: '{パスワード}',
    }
  });

  const cond = {
    index: '...',
    query: {
      match_all: {}
    },
    size: 10
  };

  const res = await client.search(cond);
  console.log(res);
})();

MySQL の check 制約

MySQL の check によるデータの制約を確認します。
以下は test_check_01 テーブルで、num が 1 <= num <= 10、str の文字列長 <= 5 の制約を設定しています。

■テーブル定義・データ登録

create table test_check_01 (
  id        varchar(32) not null,
  num       integer not null,
  str       varchar(8),

  primary key(id),
  check(num >= 1 and num <= 10),
  check(char_length(str) <= 5)
);

insert into test_check_01 (id, num, str) values ('id_1', 1, 'abc');
insert into test_check_01 (id, num, str) values ('id_2', 2, 'def');
insert into test_check_01 (id, num, str) values ('id_3', 3, 'ghi');

■check の条件の確認

mysql> select * from test_check_01;
+------+-----+------+
| id   | num | str  |
+------+-----+------+
| id_1 |   1 | abc  |
| id_2 |   2 | def  |
| id_3 |   3 | ghi  |
+------+-----+------+

mysql> update test_check_01 set num = num * 4;;
ERROR 3819 (HY000): Check constraint 'test_check_01_chk_1' is violated.

mysql> select * from test_check_01;
+------+-----+------+
| id   | num | str  |
+------+-----+------+
| id_1 |   1 | abc  |
| id_2 |   2 | def  |
| id_3 |   3 | ghi  |
+------+-----+------+

mysql> update test_check_01 set str = concat(str, '___');
ERROR 3819 (HY000): Check constraint 'test_check_01_chk_2' is violated.

mysql> select * from test_check_01;
+------+-----+------+
| id   | num | str  |
+------+-----+------+
| id_1 |   1 | abc  |
| id_2 |   2 | def  |
| id_3 |   3 | ghi  |
+------+-----+------+
3 rows in set (0.00 sec)