St_Hakky’s blog

心理学/人事/就活/留学/データサイエンス/バイオインフォマティクス/日頃思ったことについて書きます。

SSHポートフォワーディングでMySQLに接続する方法

SSHポートフォワーディングでMySQLに接続する方法を調べたので、まとめておきます。

SSHポートフォワーディングでMySQLに接続する方法

まず、ポートフォワーディングを実行します。

ssh -f -N -C -L 10000:localhost:3306 user@nantoka.com -p 22

以下、説明になります。

コマンド 説明
-L 10000:localhost:3306 ローカルの10000ポートを接続先サーバーの3306ポートにマッピングするという指定
user@nantoka.com -p 22 nantoka.comというサーバーの22ポートにユーザ名userで接続するという指定
-fオプション このポートフォワーディングをバックグラウンドで実行する。
-Nオプション SSHトンネリングのみに使用する。
-Cオプション データを圧縮して送る。

また、ポートフォワーディングを停止したい場合は、ps aux | grep sshコマンドで当該プロセスを探し、killすればいい。

外部サーバーのMySQLに接続するには、先程のポートフォワーディングが行われているので、10000番のポートを指定すれば外部サーバーで稼働しているMySQLに接続することができます。

mysql -u root -p -h 127.0.0.1 --port=10000

「日本の人事を科学する 因果推論に基づくデータ活用」を読んだ

こんにちは。

お盆を利用して、前から気になっていた「日本の人事を科学する」という本を読んでみましたので、その感想とかを書いておきたいと思います。

◯読んだ本

今回読んだのは以下の本です。

この本は前から気になっていて、というか僕自身も人事はもっとデータ活用とかしていくべきだなぁと思っていたんですがいまいちどんな風にやれば効果的かのイメージがわかなかったので、読んでみることにしました。

◯本の概要

■1,2章

この本ではまず現在の日本企業においてデータの活用が行われていないことを指摘し、その後回帰分析等といった簡単な統計やデータ分析の基礎について、実際に人事の分析の例を上げながら具体的に触れています。

紙面の都合上かと思いますが、統計などの詳細については触れず、「データを使ったらどんな分析ができて、どんなことがわかり、それを基にどう改善すればいいか」に焦点を当てて内容がかかれています。

■3章

ここでは女性活躍推進についてデータ分析を通じてその効果などを測る方法などに言及しています。

少子高齢化により働き手が不足すると言われている中で、そもそもなぜ女性活躍が進まないかということについても言及していて、この本では以下の3点があげられていました。これは凄くなるほどとなりました(詳細は本をどうぞ)。

こういった定性的な分析による課題感を、人事データを使って明確にし、それを解決していくために分析の例が以下のようにいくつか挙げられていました。

  • 男女賃金格差 => ダミー変数を用いた回帰分析(ミンサー型賃金関数)
  • 女性の配置部署の偏り => ローレンツ曲線を用いた分析
  • 目標難易度の男女差 => プロビットモデルを用いた分析

このような分析の例をいくつかあげ、自社でどのような現状があるかをしっかり把握した上で、施策とデータを紐付けなければならないとしていました。

■4章

働き方改革の必要性とその効果測定をどうするかについて書かれていました。ここで、働き方改革には長い年月が必要だということを示した上で、3章で主張した女性活躍推進に必要な改善するべきポイントに触れつつも、働き方改革には男性がターゲットであると主張する内容が続き、最後に評価をデータを用いてどうやってやるかについて言及されていました。

こういった話を実際の海外の事例をデータを用いていい感じに示してくれるのが凄くありがたいなぁと思いながら読みました(笑)。

■5章

この章では採用施策がうまく行っているかをデータを用いて見ようというのを、回帰分析を中心に議論しています。また、相関関係が出なかったからと行って、有用ではなかったというわけではないことを、サンプルセレクション問題に触れつつ指摘しています。サンプルセレクションによるバイアスの補正にはヘックマンの2段階推定法があると指摘している部分とかもあり、そこらへんは特に楽しく読みました笑。

採用は重要な部分と認識しつつも、しっかりとデータの管理がされていないなど、ここでしっかりデータを保持しておくことへの重要性も指摘していました。

本の内容からは若干それますが、採用力にはこの著者は次の3つがあると指摘していました。

これは以前読んだ服部先生の本:採用学でも同じような話があがっていて、やっぱり大事なんだなと思いました。

st-hakky.hatenablog.com

■6,7,8章

ここでは、優秀な社員の定着率、中間管理職の貢献の評価をどうしたらいいか、高齢化に対応した長期施策についての3点について書かれています。

特に高齢化に対応した長期施策は、今の元気な60代の方々を無駄にせず、且つ有効に活用するための給与計算などの部分は凄く面白いです。

■9章

ここではまとめとして、人事におけるデータ活用はどう発展するかについて書かれていました。ここで挙げられていたポイントは以下の点です。

  • AIやITの発展により、利用可能な人事データが増える
  • 人事の分権化により、人事の仕事のあり方が変わり、意思決定のサポートになることが多くなる
  • 労働者のキャリアの多様性やニーズに答える必要性が増えた

データの活用や蓄積により、こういった状況に対処することが的確にできるとしていました。

◯感想とか考えたこととか

■読んで感じたこの本の対象者

この本の対象者なのですが、「人事についてある一定の知識とか課題意識があり、マーケティング活動で統計を使っている人や大学の基礎レベルの統計を履修した人」とかかなぁと。

ちょっとデータ解析だけやってきた人には「人事」のテーマが強いので(当たり前ですが)、よほど興味がなければ最後まで読みきれないんじゃないかなと感じました。それくらい内容としては濃かったです。

逆に、統計とかの知識のレベル感としてはそれほど高くないので、そういった知識を普通に持っていて、人事に興味がある人は読み切れる内容かなと思います。

また、本にも書いてありますが、人事活動においてPDCAやデータによる定量的な解析を行っていない中にいて、これから活用していく課題感を持っている人とかにはかなりいい内容だと思います。

あとは、以下でも触れますが、ある程度会社の規模が大きく、年数も経過している企業の人事担当者で、現在定性的な分析しかできておらず、何か突破口が欲しい人とかにも凄くいい本だと思います。

■比較的長い視点が必要

この本で取り上げられていた分析では、時間軸が数年とかの規模なので、結構長く辛抱強くやらないといけないんだなというのが、まず一番感じたことでした(この本では何年間かのデータを用いて解析する例が多かったのでそう感じただけかもしれませんが)。

施策の効果がでるのが何年後かわからない中で、そのデータを蓄積したり、施策を回してPDCAをやることというのはかなり辛抱強くみなければならないことだなと思いました。

しかし一方で、施策の効果がでるのが時間がかかるデータである、つまりデータの取得スパンが長い(一年とか)場合であるから、短期的な成果が見込めないのであって、IoTなどを活用したオフィスや社員の活動データの蓄積、メールやチャットの会話の解析などを行えば、比較的短いスパンでも成果を見込めるものもあるのではないかなと思いました。

今現在そういったIoTなどを活用した働き方のサポートを行っている企業もあるようなので、そういった施策を回しつつ、データの蓄積に長い時間がかかるものについてはじっくり回すという感じでできると一番バランスも良くていいんじゃないかなと読んでいて思いました。

■考えるべき変数が多い=専門性が必要

読んでいて思ったのが、データ分析のスペシャリストがいても、人事のことについてまず理解が深くなければこれはなかなか難しいんじゃないかなと思いました(これはどの分析においても同じですが、レベル感が高い気がします)。

人事の情報に日常的に触れながら、あらゆる人材についての課題意識を持ち、且つ統計やエンジニアリングのスキルを有する人達がこの人事系のデータ解析をしなければ、なかなか難しそうだなぁと思いました。

もちろん、それほど個々のスキルセットは高くなくてもいいのかもしれないです。しかし、これを行うのは結構大きめの会社かなと思うのですが、それでもこのタイプの案件であれば少数でやることになると思います。そうすると、それなりにそれぞれの分野が1人でできる必要があるんじゃないかなと思いました。

■まずはデータを蓄えるということ

この本にかかれていましたが、ぶっちゃけた話、この手のデータをしっかり何年も蓄えている会社って全国、いや世界にどれくらいあるんだろうと疑問に思いました(笑)。

正直にいって、応募者の選考記録の蓄積とかってかなりめんどくさいので(笑)、こういうのはそもそもシステムを導入してそのシステム上で記録・管理・分析を一気通貫で行わなければ絶対に正確にはやらないだろうし、そもそもめんどくさくて社員が気乗りしないだろうなと思いました。

とはいえ、各会社で人事を一括でデータ管理できるシステムを導入することはかなり至難の技だなぁと…。理由はいくつかありますが、社内のシステムをいくら入れても、例えば採用のサービスを外部でつかってしまうと、そこのデータが埋もれてしまう可能性があるからです。

こういった外部サービスとの連携も含めてデータの蓄積と考えると、何を蓄積するべきかというのも重要になってくるなと思いました。

■女性活躍推進は男性の改革

この本で取り上げられている、「女性活躍推進は男性の改革」といった趣旨の内容が凄くなるほどとなりました。家庭内分業が不平等になっているというのはなんとなく想像がついていましたが、長時間労働や遅い昇進といった点はあまり考えたことがなかったので。こういった要素が絡んでくると女性活躍推進は「男性と女性が平等なフィールドで戦えるような環境づくり」となり、「女性活躍推進=男性の改革」となるのは凄く納得が行きました。

一方で、「モーレツに働きたい」という人たちも含めた多様性の担保というのはどうすればいいのかというのがここで疑問になりました。

つまりは、横一列の労働環境ではなくて、あらゆる人間がベストになるような労働環境を目指すといえど、全体を考えるとどうしても「横一列な環境」というのがベストになってくるのではないかなとなると、なかなか難しいなぁと思いました。

■優秀な社員

本の「優秀な社員の定着率」のところで挙げられていた「会社特有のスキル」よりも「市場全体で通用するスキル」の向上させることの意味については、どこか忘れましたがそういった基準で給与を決めている会社もあったので、なるほどこうなのかぁとなりました。

転職することができるスキルを身につけられる環境であるということと、外部から採用して人を入れるときも、この「市場全体で通用する」という視点を持って社内のスキルセットや研修の管理などを行えると、採用から自社で活躍するまでのタイムラグとかも減らせるのではないかなと思いました。

■データはあらゆる場面で使える

人事のタスクのあらゆる場面でデータが使えて、それが会社や社会への貢献になるということがこの本を通じてよりわかりました。

人事系のデータの活用もそうですが、ITの活用だけでなくAIやIoTなどの活用も含めて、様々な形で「働き方」を変えていければいいなと思いました。

データ欲しいし、こういう事例でデータ分析とかやってみたいなぁと思いましたが、今の会社の規模では無理だなぁとこの本を読んでまた思いました。笑(誰か紹介して欲しいというか誰かいないかしら…笑)

長くなりましたが、めちゃめちゃ楽しい本でしたし、内容も濃かったので、凄く良かったです。

それでは。

sqlalchemyのインストール

こんにちは。

sqlalchemyをつかうことになったので、その環境のセットアップなどについてまとめました。

◯環境

  • python3.5以上
  • Windows10 64bit / Centos7

◯インストール

■pipからインストール
pip install sqlalchemy
■condaからインストール
conda install sqlalchemy

◯Versionの確認

import sqlalchemy
print(sqlalchemy.__version__)

SQLAlchemyを使っていて勉強したこととか便利なTipsとかをまとめた

こんにちは。SQLAlchemyを使っていてわかったこととか勉強になったことをまとめました。

◯環境構築

以下の記事にまとめました。

st-hakky.hatenablog.com

◯DatabaseのURLの指定方法

Databaseに接続するためにURLを指定する必要があるのですが、そのURLの指定方法は以下のとおりです。

<dialect>+<driver>://<username>:<password>@<host>:<port>/<database>?charset=<charset_type>

ちなみに実際に書くときは<>は外して書きます。また「?」以降は無くても動作します。

各要素についての説明は以下のとおりです。

要素 意味
dialect mysqlとかpostgresqlとかを指定します。ここはDBの種類を指定するイメージ
driver DBに接続するためのドライバーの指定をします。pymysqlとか
username DBに接続することができるユーザー名
password DBに接続するためのパスワード
host ホスト名。localhostとかIPで指定します
port ポート番号
database 接続するデータベース名
charset_type DBに接続する際の文字コード。utf8とかですね

実際の具体例としては、例えばMySQLに接続する際は以下のような感じで接続できます。

mysql+mysqldb://scott:tiger@localhost/foo

他の例についてはこちらの公式ドキュメントで挙げられているので、参考になります。

◯Connectiong : create_engine

上記のような形で指定できるURLを用いてデータベースに接続するためのEngineのインスタンスを生成します。

from sqlalchemy import create_engine
engine = create_engine('postgresql://scott:tiger@localhost:5432/mydatabase')

create_engineでは、引数にいろんな指定をすることができて、例えば文字コードの指定(encoding)やデバッグ用に出力を吐かせたり(echo)など、そういうことができます。具体例は以下の通り。

from sqlalchemy import create_engine
engine = create_engine("mysql://scott:tiger@hostname/dbname",
                            encoding='latin1', echo=True)

◯Declare a Mapping

pythonでテーブルを記述したclassとデータベースをMappingするための処理が用意されています。以下のようにして記述し、ここで生成したBaseのインスタンスを用いてclassを作成することで実現できます。

from sqlalchemy.ext.declarative import declarative_base
Base = declarative_base()

class User(Base):
  __tablename__ = "users"
  #...略...

◯定義したTableのclassを見る方法

以下のコマンドでアクセスできる。

User.__table__

◯Sessionの作成

データベースに対するORMの操作はSessionを通じて行われます。

from sqlalchemy.orm import sessionmaker
from sqlalchemy import create_engine
engine = create_engine("mysql://scott:tiger@hostname/dbname",
                            encoding='latin1', echo=True)
Session = sessionmaker(bind=engine)

もし上記のようにcreate_engineを事前に行っていなかった場合は、以下のようにできます。

# Sessionのオブジェクトを作る
Session = sessionmaker()

# create_engineをした段階で、Sessionに代入する
Session.configure(bind=engine)

◯挿入・更新・削除・読み込み

ここまででテーブルの定義も一応でき、sessionもできたので、ここでテーブルのデータへの操作を行うことを考える。ここでは、こちらのTutorialで使われている例を参考にした。

■今回の例でつかうテーブル

挿入などの例を見るために使用するテーブルは以下のとおりである。

class User(Base):
    __tablename__ = 'users'
    id = Column(Integer, Sequence('user_id_seq'), primary_key=True)
    name = Column(String(50))
    fullname = Column(String(50))
    password = Column(String(12))

    def __repr__(self):
        return "<User(name='%s', fullname='%s', password='%s')>" % (
                                self.name, self.fullname, self.password)
■挿入:insert
ed_user = User(name='ed', fullname='Ed Jones', password='edspassword')
session.add(ed_user)  # ここではまだDBに登録されていない
session.commit()  # ここで登録の処理が実行される

addした段階ではまだDBに登録する処理は行われていない。しかし、addまでをした段階で「name="ed"」の条件のもとqueryをなげると、結果が正常に返ってくる。

■更新:update

queryなどで取ってきたデータの情報を更新する処理。sessionに登録されているクラスの情報を更新して、commitすると情報が更新される。

# ここはqueryで取ってきたデータでもよい。
ed_user = User(name='ed', fullname='Ed Jones', password='edspassword')
session.add(ed_user)

# パスワードの変更
ed_user.password = 'f8s7ccs'

# パスワードの更新
session.commit()
■削除:delete

これは簡単。ふつうにqueryで投げた結果に対して、sessionからdeleteメソッドを使用すればいいです、

session.delete(user)
■読み込み:select

queryはsessionのquery()メソッドで実現される。

query_result = session.query(User).all()

ちなみに、通常のSQLのqueryみたく、条件によるフィルター(filter())やソート(order_by())などもできる。これについては、以下のサイトに沢山記載されている。


また、いつも混乱するのだが、「.all()」メソッドはlist形式で、「.first()」メソッドはqueryの結果の最初の値をscalarで取ってくる。これは良く勘違いするので、注意。queryの結果については、各カラムの値を、属性(例えばnameなど)で指定することで、参照することができる。

また、queryの結果を辞書式にする方法は、以下の通り。

# queryを投げてデータを取得。.all()なので、結果はlistで取得される。
users = session.query(User.id, User.user_name).all()

# 辞書式に変換
dict_result = [user._asdict() for user in users]

queryを投げる時に取得するカラム名を指定することで、辞書式にする「_asdict()」メソッドが使えます。

◯sessionの管理

sessionは使ったらきちんと閉じないといけないのですが、これがどうにも忘れがちになるので、なんかうまいこと管理する方法がないかなーと探していたところ、sessionをwithで管理する方法を実現しているコードがありました。以下の記事で紹介されています。

Python の with 文で SQLAlchemy のセッションを管理する | CUBE SUGAR STORAGE

これはめちゃめちゃ便利でした。

◯データベースエンジンの指定

InnoDBを指定したいときとかは、以下のような感じでできる。

__table_args__ = {'mysql_engine': 'InnoDB'}

これをテーブルの定義の際にいれればおけ。

◯UNIQUE制約

UNIQUE制約を書ける時は、普通にテーブル定義の際に、Columnの中で指定をすればいい。また複数のカラムに対してUNIQUE制約を書けたい場合も、以下のようにすることで実現できる。コードはこちらのものを参考にした。

class Example(Base):
    __tablename__ = "example"
    __table_args__ = UniqueConstraint('col2', 'col3', name='uix_1')


    # per-column anonymous unique constraint
    col1 = Column('col1', Integer, unique=True)

    col2 = Column('col2', Integer)
    col3 = Column('col3', Integer)
    
    def __init__(self...)
    # ...略

ちなみに、InnoDBと組み合わせる際には、()でくくる必要がある。

__table_args__ = (UniqueConstraint('col2', 'col3', name='uix_1'), {'mysql_engine': 'InnoDB'})

◯外部キー

sqlalchemyの外部キーに関する記事を見ていたら外部キー制約をつけない方法もあるみたいなのをみて、なぬーんってなったけど一旦無視する笑。

SQLiteをつかう場合

あと、僕は今SQLiteは使っていないですが、SQLiteでは外部キー制約が無効になっているらしく、それを解決する必要があるみたいですね。

CASCADEでUPDATE/DELETEを行う

[工事中]

◯sqlalchemyでEnumを使う

sqlalchemyを使っていた時、「デフォルトでEnumってないんかーい」ってまずなりました笑。別に文字列で指定したりとかして値の入力に制限を設ければいいんだろうけどなんかムズムズするので、Enumをつかう方法を結局頑張って探すことにしました。

Python3.4からは標準でEnum型を扱う方法が導入されており、これを使ってsqlalchemyからEnumを使えるように自作します。

僕が改めて書く必要がないくらい以下の記事にまとまっていますので、これにて終了とします笑

qiita.com

Enumついでに書いておきますと、python3.6からEnumに自動で値をつけるautoが実装されたので、値の種類が多くなっても楽勝です。使い方としては、以下のような感じ。

from enum import IntEnum, auto

class Field(IntEnum):

   OrderNo = 1
   CustomerNo = auto()
   PurchaseDate = auto()

こうすると、一つ前のメンバにプラス1した値が自動で代入されます。便利ですね。

◯NULLに関する制約

NULLに関する制約はColumnの引数で行うことができる。

Column("column_name", nullable=False)

デフォルトはTrueで、NULLの値を許可している。FalseでNULLの値を許可しないようにできる。

◯デーブル定義のテンプレ

こちらの記事にある通り、一つテーブル定義のテンプレを作って置いて、それを使い回せばかなり色々楽になりそう。よくやる処理は決まっているし笑。

MySQL:文字コードについて

こんにちは。

MySQLで取り扱う文字コードについて色々調べたこととかについてまとめたので、以下備忘録として残しておきたいと思います。

文字コードの変更

以下の様な感じのエラーが出た時の対処方法

sqlalchemy.exc.InternalError: (pymysql.err.InternalError) (1366, "Incorrect string value: '\\xE9\\x98\\xBF\\xE4\\xBA\\x95...' for column 'name' at row 1")
<||

まず、何の文字コードを使っているかどうかを確認する。

>||
show variables like "chara%";

ちなみに、使用可能な文字コードは以下のコマンドで確認することができる。

show character set;

次に、文字コードを全てutf-8にするために、以下のコードをmy.cnfに追加。centosだと、/etc/my.cnfにある。

[mysqld]
character-set-server=utf8

[client]
default-character-set=utf8

その後、再起動をかける。

sudo service mysqld restart

これでもう一度文字コードを確認してutf-8になっていたら勝ち。

○絵文字への対応

普通のutf8だと、1文字あたり1〜3バイトなんですが、一部の漢字とか絵文字が4バイトなんですね。なので、普通にブログとかのサービスを作ったとして、普通に絵文字をぶち込まれると普通に死にます。

これは、「寿司ビール問題」とかっていうこともあるそうです(utf-8絵文字の中でも寿司とビールのアイコンが同値判定されてしまう問題のことをさすそうです)。

これに対応するのが、4バイトにも対応するutf8mb4で、設定をすれば普通のutf8も4バイトのutf8も扱えるようになります。これは、MySQL 5.5から対応しているようです。

設定方法なのですが、さっきと同じくだりになります。こちらの資料によると、今から始めるならutf8mb4らしいっす。確かに、、、

文字コードを全てutf8mb4にするために、以下のコードをmy.cnfに追加。centosだと、/etc/my.cnfにある。

[mysqld]
character-set-server=utf8mb4

[client]
default-character-set=utf8mb4

その後、再起動をかける。

sudo service mysqld restart

これで、文字コードを確認してちゃんと設定されていればオッケー。

ちなみに、既存のDBなどの文字コードを変更する方法もあります。

まず、データベースのcharsetを変える方法は、以下のコマンドになります。

ALTER DATABASE <database_name> CHARSET utf8mb4;

これは、実行しても既存のテーブルのCharsetは変更されません。以後で作成するテーブルが全てutf8mb4になるだけです。

そのため、既存のテーブルの文字コードを変更したい場合は、以下のコマンドを実行します。

ALTER TABLE <table_name> CHARSET utf8mb4;

上のコマンドも、テーブル属性だけ変換します。なので、もしテーブル内の全てのカラムとデータの変換を行いたい場合は、以下のコマンドを行います。

ALTER TABLE <table_name> CONVERT TO CHARSET utf8mb4;

カラム名文字コードを変更したい場合は、以下のコマンドを実行する。

ALTER TABLE <table_name> MODIFY <column_name> VARCHAR(100) CHARSAET utf8mb4;

ちなみに、character_set_systemだけutf8になっていると思いますが、これはデフォルトで変更されないようになっているようです。